1. What is a correlated sub-query?
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.
2. What are the steps to take to improve performance of a poor performing query?
► 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.
3. What authentication modes does SQL Server support?
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.
4. What is blocking and how would you troubleshoot it?
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.
5. Why would you call Update Statistics?
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.
6. What is database isolation in SQL Server?
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
7. How to control the amount of free space in your index pages?
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.
8. How to create a Scrollable Cursor with the SCROLL Option?
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.
10. Explain Databases and SQL Server Databases Architecture?
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.
11. What happens on checkpoint?
Checkpoints, whether scheduled or manually executed, cause the transaction log to be truncated up to the beginning of the oldest open transaction (the active portion of the log). That is, the dirty pages from the buffer cache are written to disk. Storing committed transactions in the cache provides a performance gain for SQL Server. However, you do not want the transaction log to get too big because it might consume too many resources and, should your database fail, take too long to process to recover the database.
One important thing to note here is that SQL Server can only truncate up to the oldest open transaction. Therefore, if you are not seeing the expected relief from a checkpoint, it could very well be that someone forgot to commit or rollback their transaction. It is very important to finalize all transactions as soon as possible.
12. How do you test proper TCP/IP configuration Windows machine?
Windows NT: IPCONFIG/ALL,
Windows 95: WINIPCFG,
Ping or ping ip.add.re.ss
13. Why would you use SQL Agent?
SQL Agent is the job scheduling mechanism in SQL Server. Jobs can be scheduled to run at a set time or when a specific event occurs. Jobs can also be executed on demand. SQL Agent is most often used to schedule administrative jobs such as backups.
14. How to define testing of network layers?
Reviewing with your developers to identify the layers of the Network layered architecture, your Web client and Web server application interact with. Determine the hardware and software configuration dependencies for the application under test.
15. What types of replication are supported in SQL Server?
SQL Server has three types of replication: Snapshot, Merge, and Transaction. Snapshot replication creates a snapshot of the data (point-in-time picture of the data) to deliver to the subscribers. This is a good type to use when the data changes infrequently, there is a small amount of data to replicate, or large changes occur over a small period of time.
Merge replication uses a snapshot to seed the replication. Changes on both sides of the publication are tracked so the subscriber can synchronize with the publisher when connected. A typical use for this type of replication is in a client and server scenario. A server would act as a central repository and multiple clients would independently update their copies of the data until connected. At which time, they would all send up their modifications to the central store.
Transaction replication also begins with a snapshot only this time changes are tracked as transactions (as the name implies). Changes are replicated from publisher to subscriber the same as they occurred on the publisher, in the same order as they occurred, and in near real time. This type of replication is useful when the subscriber needs to know every change that occurred to the data (not point-in-time), when the change volume is high, and when the subscriber needs near real-time access to the changes.
16. How many layers of TCP/IP protocol combined of?
Five. (Application, Transport, Internet, Data link, Physical).
17. How to trace the traffic hitting a SQL Server?
SQL profiler is the SQL Server utility you can use to trace the traffic on the SQL Server instance. Traces can be filtered to narrow down the transactions that are captured and reducing the overhead incurred for the trace. The trace files can be searched, saved off, and even replayed to facilitate troubleshooting.
18. How many bits IP Address consist of?
An IP Address is a 32-bit number.
19. What purpose does the model database serve?
The model database, as its name implies, serves as the model (or template) for all databases created on the same instance. If the model database is modified, all subsequent databases created on that instance will pick up those changes, but earlier created databases will not. Note that TEMPDB is also created from model every time SQL Server starts up.
20. Tell me what is blocking and how would you troubleshoot it?
Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first.
A deadlock occurs when two or more processes waits for a resource that is acquired by or is under the control of another process. A live lock is similar to a deadlock except the process states keeps changing. The result of such state is that none of the process will be complete.
Deadlock detection finds and resolves deadlocks. A WFG strategy is followed. WFG is wait for graph. In WFG, processes are represented by nodes while dependencies are represented by edges. Thus, if process A is waiting for a resource held by process B, there is an edge in the WFG from the node for process A to the node for process B. a cycle is this graph is a deadlock. WFG constantly checks for cycles or when a process is blocked and adds a new edge to the WFG. When a cycle is found, a victim is selected and aborted.
22. What is the different types of BACKUPs available in SQL Server?
Complete database backup: This type of backup will backup all the information in the database. Used most commonly for disaster recovery and takes the longest time to backup.
Differential databse backup: The database is divided into partitions that have been modified since last complete backup. Most suitable for large databases. The most recent differential backup contains the changes from previous backups.
Transaction log backups: Backups only the changes logged in the transaction log. The transaction log has all changes logged about a database. Once the changes are accommodated on the database, the log is truncated or backed up.
File/File Group backups: used to recover individual files or file groups. Each filegroup can be individually backed up. This helps in recovery only the required file or filegroup for disaster recovery.
DBCC CHECKDB,
DBCC CHECKTABLE,
DBCC CHECKCATALOG,
DBCC CHECKALLOC,
DBCC SHOWCONTIG,
DBCC SHRINKDATABASE,
DBCC SHRINKFILE etc.
But there are a whole load of DBCC commands which are very useful for DBAs.
24. Explain what is a Schema in SQL Server 2005? Explain how to create a new Schema in a Database?
A schema is used to create database objects. It can be created using CREATE SCHEMA statement. The objects created can be moved between schemas. Multiple database users can share a single default schema.
CREATE SCHEMA sample;
Table creation
Create table sample.sampleinfo
{
id int primary key,
name varchar(20)
}
25. What are database files and filegroups?
Database files are used for mapping the database over some operating system files. Data and log information are separate. SQL server database has three types of database files:
Primary: starting point of a database. It also points to other files in database. Extension: .mdf
Secondary: All data files except primary data file is a part of secondary files. Extension: .ndf
Log files: All log information used to recover database. Extension: .ldf
