Interview Questions.

Q1. What Authentication Modes Does Sql Server Support?

SQL Server supports Windows Authentication and blended-mode. Mixed-mode permits you to use each Windows Authentication and SQL Server Authentication to log into your SQL Server. It’s essential to observe that if you use Windows Authentication, you may no longer be able to log in as sa.

Q2. When Setting Replication, Is It Possible To Have A Publisher As 64 Bit Sql Server And Distributor Or Subscribers As A 32 Bit Sql Server.

Yes it's far possible to have numerous configurations in a Replication environment.

Q3. Does Transparent Data Encryption Provide Encryption When Transmitting Data Across Network?

No, Transparent Data Encryption (TDE) does not encrypt the records during switch over a communication channel.

Q4. What Are The High-availability Solutions In Sql Server And Differentiate Them Briefly?

Failover Clustering, Database Mirroring, Log Shipping and Replication are the High-Availability functions to be had in SQL Server. I could advocate studying this weblog of mine which explains the differences between these four functions.

Q5. What Is The Difference Between Clustered And Non-clustered Index?

In a clustered index, the leaf stage pages are the actual information pages of the table. When a clustered index is created on a desk, the statistics pages are arranged for that reason based on the clustered index key. There can best be one Clustered index on a desk.

In a Non-Clustered index, the leaf stage pages does not contain information pages alternatively it includes recommendations to the facts pages. There can multiple non-clustered indexes on a unmarried table.

Q6. What Happens On Checkpoint?

Checkpoints, whether scheduled or manually performed, cause the transaction log to be truncated up to the beginning of the oldest open transaction (the lively part of the log). That is, the grimy pages from the buffer cache are written to disk. Storing dedicated transactions within the cache affords a performance benefit for SQL Server. However, you do no longer want the transaction log to get too large due to the fact it might eat too many assets and, have to your database fail, take too long to system to get better the database.

One crucial factor to be aware here is that SQL Server can simplest truncate up to the oldest open transaction. Therefore, if you aren't seeing the expected comfort from a checkpoint, it could very well be that a person forgot to commit or rollback their transaction. It could be very important to finalize all transactions as soon as possible.

Q7. What Are The Different Types Of Upgrades That Can Be Performed In Sql Server?

In-location improve and Side-via-Side Upgrade.

Q8. What Is Fill Factor?

Fill Factor is a placing that is applicable to Indexes in SQL Server. The fill issue price determines how plenty statistics is written to an index page whilst it's far created / rebuilt.

Q9. What Is Transparent Data Encryption?

Introduced in SQL Server 2008 Transparent Data Encryption (TDE) is a mechanism via which you can defend the SQL Server Database files from unauthorized get entry to through encryption. Also, TDE can guard the database backups of the example on which TDE turned into setup.

Q10. What Are The New Features In Sql Server 2005 When Compared To Sql Server 2000?

There are quite a whole lot of changes and improvements in SQL Server 2 hundred@Few of them are listed right here :

Database Partitioning

Dynamic Management Views

System Catalog Views

Resource Database

Database Snapshots

SQL Server Integration Services

Support for Analysis Services on a a Failover Cluster.

Profiler being capable of hint the MDX queries of the Analysis Server.

Peer-toPeer Replication

Database Mirroring

Q11. Where Do You Find The Default Index Fill Factor And How To Change It?

The easiest way to discover and exchange the default fill element cost is from Management Studio, right-click on the SQL Server and choose houses. In the Server Properties, select Database Settings, you ought to see the default fill aspect value within the pinnacle phase. You can exchange to a preferred price there and click OK to shop the changes. The different option of viewing and changing this cost is the use of

Q12. What Are The Commands Used In Dcl?

GRANT, DENY and REVOKE.

Q13. List Out Some Of The Requirements To Setup A Sql Server Failover Cluster.?

Virtual community name for the SQL Server, Virtual IP deal with for SQL Server, IP addresses for the Public Network and Private Network(additionally referred as Hearbeat) for every node in the failover cluster, shared drives for SQL Server Data and Log documents, Quorum Disk and MSDTC Disk.

Q14. What Are The Different Authentication Modes In Sql Server And How Can You Change Authentication Mode?

SQL Server has 2 Authentication modes: Windows Authentication and SQL Server and Windows Authentication mode also referred as Mixed Mode.

Q15. Due To Some Maintenance Being Done, The Sql Server On A Failover Cluster Needs To Be Brought Down. How Do You Bring The Sql Server Down?

In the Cluster Administrator, rick click on at the SQL Server Group and from the popup menu object choose Take Offline.

Q16. How Do You Open A Cluster Administrator?

From Start -> Run and type CluAdmin (case insensitive) and the Cluster Administrator console is displayed OR you could also go to Start -> All programs -> Administrative Tools -> Cluster Administrator.

Q17. What Are The Recovery Models For A Database?

There are 3 healing fashions available for a database. Full, Bulk-Logged and Simple are the 3 healing models available.

Q18. What Is The Default Port No On Which Sql Server Listens?

1433

Q19. What The Different Components In Replication And What Is Their Use?

The three predominant additives in Replication are Publisher, Distributor and Subscriber. Publisher is the records supply of a book. Distributor is accountable for dispensing the database objects to 1 or more destinations. Subscriber is the vacation spot where the publishers records is copied / replicated.

Q20. How Can You Control The Amount Of Free Space In Your Index Pages?

You can set the fill aspect for your indexes. This tells SQL Server how plenty unfastened space to depart within the index pages while re-indexing. The overall performance gain right here is fewer page splits (where SQL Server has to duplicate rows from one index web page to any other to make room for an inserted row) due to the fact there's room for boom constructed in to the index.

Q21. What Is A System Database And What Is A User Database?

System databases are the default databases which might be hooked up when the SQL Server is set up. Basically there are 4 system databases: Master, MSDB, TempDB and Model. It is noticeably encouraged that these databases are not changed or altered for smooth functioning of the SQL System.

A consumer database is a database that we create to keep facts and start operating with  the information.

Q22. What Is The Difference Between The 2 Operating Modes Of Database Mirroring?

High-Safety Mode is to ensure that the Principal and Mirrored database are synchronized kingdom, this is the transactions are dedicated at the equal time on both servers to make certain consistency, however there is/is probably a time lag.

High-Performance Mode is to ensure that the Principal database run quicker, by using now not looking ahead to the Mirrored database to dedicate the transactions. There is a moderate hazard of data loss and also the Mirrored database may be lagging behind (in phrases being up to date with Principal database) if there may be a heavy load on the Mirrored Server.

Q23. If You Are Given Access To A Sql Server, How Do You Find If The Sql Instance Is A Named Instance Or A Default Instance?

I would visit the SQL Server Configuration Manager.In the left pane of the tool, I would pick SQL Server Services, the right facet pane displays all of the SQL Server Services / components which are installed on that device. If the Service is displayed as (MSSQLSERVER), then it indicates it's miles a default example, else there can be the Instance name displayed.

Q24. What Are The Different Ways You Can Create Databases In Sql Server?

T-SQL; Create Database command.

Using Management Studio

Restoring a database backup

Copy Database wizard

Q25. What Are The Different Types Of Database Compression Introduced In Sql Server 2008?

Row compression and Page compression.

Q26. What Are The Different Types Of Indexes Available In Sql Server?

The most effective solution to that is “Clustered and Non-Clustered Indexes”. There are different forms of Indexes what may be mentioned which include Unique, XML, Spatial and Filtered Indexes. More on these Indexes later.

Q27. What Is The Importance Of A Recovery Model?

Primarily, recuperation model is selected keeping in view the quantity of records loss possible manage to pay for to. If one expects to have minimal or no statistics loss, choosing the Full healing model is a good desire. Depending on the restoration model of a database, the behavior of database log report changes. I would propose you examine extra fabric on log backups and log record behavior and so on to apprehend extensive.

Q28. What Is Dcl?

DCL stands for Data Control Language.

Q29. What Is The Default Fill Factor Value?

By default the fill issue value is set to 0.

Q30. Why Would You Call Update Statistics?

Update Statistics is used to pressure a recalculation of question optimization facts for a desk or listed view. Query optimization statistics are mechanically recomputed, however in some instances, a question can also advantage from updating those information extra regularly. Beware although that re-computing the question statistics reasons queries to be recompiled. This may also or might not negate all overall performance profits you might have done by means of calling update statistics. In reality, it may have a poor effect on performance depending at the characteristics of the system.

Q31. How Do You Troubleshoot Errors In A Sql Server Agent Job?

Inside SSMS, in Object explorer below SQL Server Agent look for Job Activity Monitor. The activity activity reveal presentations the modern status of all the jobs on the example. Choose the specific job which failed, proper click on and pick out view records from the drop down menu. The execution records of the task is displayed and you can choose the execution time (if the job failed more than one times during the same day). There could statistics such as the time it took to execute that Job and details about the error befell.

Q32. Explain About Your Sql Server Dba Experience?

This is a established query often requested by many interviewers. Explain what are the one of a kind SQL Server Versions you've got labored on, what sort of administration of these instances has been executed by way of you. Your position and responsibilities finished for your in advance initiatives that might be of importance to the potential organization. This is the answer that we could the interviewer recognize how appropriate are you for the location to which you are being interviewed.

Q33. Which Autogrowth Database Setting Is Good?

Setting an autogrowth in multiples of MB is a higher option than setting autogrowth in percentage (%).

Q34. What Are The Different Sql Server Versions You Have Worked On?

The answer might be relying on the versions you have labored on, I might say I even have enjoy working in SQL Server 7, SQL Server 2000, 2005 and two hundred@If you've got labored handiest the some version be honest in announcing that, don't forget, no one would be working on all versions, it varies from character to person.

Q35. What Is A Correlated Sub-query?

A correlated sub-query is a nested query this is related to the outer query. For example, say I wanted to find all of the personnel who've no longer entered their time for the week. I may want to query the Employee desk to get their first and closing call, however I want to study the TimeEntry table to see in the event that they’ve entered their time or now not. I can’t do a immediately join here due to the fact I’m looking for the absence of time information, so I’ll do a correlated sub-question similar to this:

SELECT FirstName, LastName

FROM EMPLOYEE e

WHERE NOT EXISTS (SELECT 1 FROM TimeEntry te

WHERE te.EmpID = e.EmpID

AND te.WeekID = 35)

Notice that the inner question relates to the outer question at the worker ID, as a result making it a correlated sub-query. The inner question will be evaluated as soon as in keeping with outer question row.

Q36. What The Different Types Of Replication And Why Are They Used?

There are essentially three forms of replication: Snapshot, Transactional and Merge Replication. The type of Replication you pick out, relies upon on the requirements and/or the desires one is making an attempt to attain.For example Snapshot Replication is useful simplest when the facts within the tables does not change regularly and the amount of data is not too big, along with a month-to-month precis table or a product listing desk and so forth. Transactional Replication could beneficial when retaining a replica of a transactional table inclusive of income order tables and so on. Merge Replication is extra useful in case of  far off / distributed structures where the statistics float can be from multiple web sites, as an instance sales completed at a promotional activities which won't be related to the vital servers usually.

Q37. What Are The Operating Modes In Which Database Mirroring Runs?

Database Mirroring runs in 2 working modes High-Safety Mode and High-Performance Mode.

Q38. What Is Dbcc?

DBCC statements are Database Console Commands and are available in 4 flavors:

Maintenance, Informational, Validation, and Miscellaneous. Maintenance commands are those commands that permit the DBA to perform maintenance sports at the database consisting of shrinking a report. Informational commands offer remarks regarding the database which includes offering records about the system cache. Validation instructions encompass instructions that validate the database including the ever-famous CHECKDB. Finally, miscellaneous instructions are those that manifestly don’t match inside the other three classes. This includes statements like DBCC HELP, which gives the syntax for a given DBCC command.

Q39. What Types Of Replication Are Supported In Sql Server?

SQL Server has three types of replication: Snapshot, Merge, and Transaction. Snapshot replication creates a photograph of the information (factor-in-time photograph of the facts) to supply to the subscribers. This is a great kind to apply when the facts changes once in a while, there's a small amount of statistics to copy, or large changes arise over a small time frame.

Merge replication uses a photo to seed the replication. Changes on each facets of the guide are tracked so the subscriber can synchronize with the writer whilst related. A usual use for this type of replication is in a patron and server state of affairs. A server would act as a primary repository and a couple of customers would independently update their copies of the statistics till linked. At which time, they would all ship up their modifications to the primary store.

Transaction replication additionally begins with a photo most effective this time adjustments are tracked as transactions (as the call implies). Changes are replicated from writer to subscriber similar to they happened on the writer, within the same order as they happened, and in near real time. This type of replication is useful while the subscriber wishes to recognise each trade that happened to the records (no longer point-in-time), while the trade extent is high, and while the subscriber desires close to real-time access to the modifications.

Q40. What Is The Difference Between Dropping A Database And Taking A Database Offline?

Drop database deletes the database at the side of the physical files, it isn't viable to bring lower back the database except you've got a backup of the database. When you are taking a database offline, you the database is not to be had for customers, it isn't always deleted bodily, it may be introduced returned on-line.

Q41. What The Different Topologies In Which Replication Can Be Configured?

Replication can be configured in any topology relying retaining in view of the complexity and the workload of the entire Replication. It can be any of the following:

Publisher, Distributor and Subscriber at the equal SQL Instance.

Publisher and Distributor at the same SQL Instance and Subscriber on a separate Instance.

Publisher,Distributor and Subscriber on person SQL Instances.

Q42. How Many Files Can A Database Contain In Sql Server?How Many Types Of Data Files Exists In Sql Server? How Many Of Those Files Can Exist For A Single Database?

A Database can incorporate a most of 32,767 files.

There are Primarily 2 sorts of information documents Primary facts document and Secondary facts report(s)

There can be handiest one Primary facts report and a couple of secondary data files so long as thetotal # of files is less than 32,767 files

Q43. How Do You Trace The Traffic Hitting A Sql Server?

SQL profiler is the SQL Server utility you can use to hint the visitors on the SQL Server instance. Traces can be filtered to slender down the transactions which can be captured and decreasing the overhead incurred for the hint. The hint documents may be searched, stored off, or even replayed to facilitate troubleshooting.

Q44. What Is Replication?

Replication is a feature in SQL Server that facilitates us put up database items and data and duplicate (mirror) it to 1 or extra locations. It is often taken into consideration as one of the High-Availability alternatives. One of the advantages with Replication is that it may be configured on databases that are in easy recuperation version.

Q45. Why Would You Use Sql Agent?

SQL Agent is the job scheduling mechanism in SQL Server. Jobs can be scheduled to run at a hard and fast time or while a specific occasion takes place. Jobs can also be carried out on demand. SQL Agent is most often used to time table administrative jobs which include backups.

Q46. When Setting Replication, Can You Have Distributor On Sql Server 2005, Publisher On Sql Server 2008?

No you can not have a Distributor on a previous version than the Publisher.

Q47. What Are The Differences In Clustering In Sql Server 2005 And 2008 Or 2008 R2?

On SQL Server 2005, putting in SQL Server failover cluster is a single step manner while on SQL Server 2008 or above it's miles a multi-step system. That is, in SQL Server 2005, the Installation process itself installs on all the nodes (be it 2 nodes or 3 nodes). In 2008 or above this has changed, we might need to install separately on all of the nodes. 2 instances if it's miles a 2 node cluster or three instances in a 3 node cluster and so on.

Q48. What Purpose Does The Model Database Server?

The model database, as its call implies, serves as the version (or template) for all databases created at the same example. If the version database is changed, all next databases created on that example will pick up the ones modifications, however in advance created databases will no longer. Note that TEMPDB is also made out of model each time SQL Server begins up.