SQL Server Database Administrator Interview Preparation Guide

A correlated sub-query is a nested query that is linked to the outer query. For instance, say I wanted to find all the employees who have not entered their time for the week. I could query the Employee table to get their first and last name, but I need to look at the TimeEntry table to see if they've entered their time or not. I can't do a straight join here because I'm looking for the absence of time data, so I'll do a correlated sub-query 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 query relates to the outer query on the employee ID, thus making it a correlated sub-query. The inner query will be evaluated once per outer query row.

► Maximum use of indexes, stored procures should be done.
► Avoid excessive use of complicated joins and cursors.
► Avoid using conditional operators using columns of different tables.
► Make use of computed columns and rewriting the query.

SQL Server supports Windows Authentication and mixed-mode. Mixed-mode allows you to use both Windows Authentication and SQL Server Authentication to log into your SQL Server. It's important to note that if you use Windows Authentication, you will not be able to log in as sa.

Blocking occurs when two or more rows are locked by one SQL connection and a second connection to the SQL server requires a conflicting on lock on those rows. This results in the second connection to wait until the first lock is released.
Troubleshooting blocking:
► SQL scripts can be written that constantly monitor the state of locking and blocking on SQL Server
► The common blocking scenarios must be identified and resolved.
► The scripts output must be checked constantly,
► The SQL profilers data must be examined regularly to detect blocking.

Update Statistics is used to force a recalculation of query optimization statistics for a table or indexed view. Query optimization statistics are automatically recomputed, but in some cases, a query may benefit from updating those statistics more frequently. Beware though that re-computing the query statistics causes queries to be recompiled. This may or may not negate all performance gains you might have achieved by calling update statistics. In fact, it could have a negative impact on performance depending on the characteristics of the system.

Isolation in database defines how and when changes made by one transaction can be visible to other transactions. Different isolation levels are:
► Serializable
► Repeatable read
► Read committed
► Read uncommitted

You can set the fill factor on your indexes. This tells SQL Server how much free space to leave in the index pages when re-indexing. The performance benefit here is fewer page splits (where SQL Server has to copy rows from one index page to another to make room for an inserted row) because there is room for growth built in to the index.

Using the SCROLL keyword while declaring a cursor allows fetching of rows in any sequence
Example:
DECLARE employee_curs SCROLL CURSOR FOR SELECT * FROM employee;
The active set of the cursor is stored can be accessed in any order without the need of opening and closing the cursor. The Scroll cursors can be set for select and function cursors but not insert or update statements.

DBCC statements are Database Console Commands and come in four flavors: Maintenance, Informational, Validation, and Miscellaneous. Maintenance commands are those commands that allow the DBA to perform maintenance activities on the database such as shrinking a file. Informational commands provide feedback regarding the database such as providing information about the procedure cache. Validation commands include commands that validate the database such as the ever-popular CHECKDB. Finally, miscellaneous commands are those that obviously don't fit in the other three categories. This includes statements like DBCC HELP, which provides the syntax for a given DBCC command.

SQL Server consists of a set of various components which fulfill data storage and data analysis needs for enterprise applications. Database architecture: All the data is stored in databases which is organized into logical components visible to the end users. It's only the administrator who needs to actually deal with the physical storage aspect of the databases, whereas users only deal with database tables.

Every SQL Server instance has primarily 4 system database i.e. master, model, tempdb and msdb. All other databases are user created databases as per their needs and requirements.

A single SQL Server instance is capable of handling thousands of users working on multiple databases.