Explain about Clustered and non clustered index? How to choose between a Clustered Index and a Non-Clustered Index?

There are clustered and nonclustered indexes. A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A nonclustered index is a… Continue reading

What is Index Tuning?

One of the hardest tasks facing database administrators is the selection of appropriate columns for non-clustered indexes. You should consider creating non-clustered indexes on any columns that are frequently referenced in the WHERE clauses of SQL statements. Other good candidates are columns referenced by JOIN and GROUP BY operations. You… Continue reading

What are the basic functions of master, msdb, model, tempdb and resource databases in SQL Server?

The master database holds information for all databases located on the SQL Server instance and is theglue that holds the engine together. Because SQL Server cannot start without a functioning masterdatabase, you must administer this database with care. The msdb stores information regarding database backups, SQL Agent information, DTS packages,… Continue reading

What is an execution plan? When would you use it? How would you view the execution plan?

An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad-hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored… Continue reading

What is the difference between a Local and a Global temporary table?

A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement. A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data… Continue reading