Top 20 SQL Server Interview Questions & Answers

Ques: 1. What is SQL server agent?

Ans: The SQL Server agent plays a vital role in day to day tasks of SQL server administrator (DBA). Server agent's purpose is to implement the tasks easily with the scheduler engine which allows our jobs to run at scheduled date and time.

Ques: 2. What is a Trigger?

Ans: Triggers are used to execute a batch of SQL code when insert or update or delete commands are executed against a table. Triggers are automatically triggered or executed when the data is modified. It can be executed automatically on insert, delete and update operations.

Ques: 3. What is the use of SET NOCOUNT ON/OFF statement?

Ans: By default, NOCOUNT is set to OFF and it returns number of records got affected whenever the command is getting executed. If the user doesn't want to display the number of records affected, it can be explicitly set to ON- (SET NOCOUNT ON).

Ques: 4. What is SQL injection?

Ans: SQL injection is an attack by malicious users in which malicious code can be inserted into strings that can be passed to an instance of SQL server for parsing and execution. All statements have to checked for vulnerabilities as it executes all syntactically valid queries that it receives.

Ques: 5. What will be the maximum number of index per table?

Ans: For SQL Server 2008 100 Index can be used as maximum number per table. 1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.

1000 Index can be used as maximum number per table. 

1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.

Ques: 6. What is Filtered Index?

Ans: Filtered Index is used to filter some portion of rows in a table to improve query performance, index maintenance and reduces index storage costs. When the index is created with WHERE clause, then it is called Filtered Index

Ques: 7. List the different index configurations possible for a table?

Ans: A table can have one of the following index configurations:

• No indexes 
• A clustered index 
• A clustered index and many non-clustered indexes 
• A non-clustered index 
• Many non-clustered indexes

Ques: 8. What is sub query and its properties?

Ans: A sub-query is a query which can be nested inside a main query like Select, Update, Insert or Delete statements. This can be used when expression is allowed. Properties of sub query can be defined as

• A sub query should not have order by clause.
• A sub query should be placed in the right hand side of the comparison operator of the main query. 
• A sub query should be enclosed in parenthesis because it needs to be executed first before the main query. 
• More than one sub query can be included.

Ques: 9. What is Mirroring?

Ans: Mirroring is a high availability solution. It is designed to maintain a hot standby server which is consistent with the primary server in terms of a transaction. Transaction Log records are sent directly from the principal server to a secondary server which keeps a secondary server up to date with the principal server.

Ques: 10. What is an execution plan?

Ans: An execution plan is a graphical or textual way of showing how the SQL server breaks down a query to get the required result. It helps a user to determine why queries are taking more time to execute and based on the investigation user can update their queries for the maximum result.

In Query Analyzer is an option called “Show Execution Plan” (located on the Query drop-down menu). If this option is turned on it will display a query execution plan in a separate window when a query is run again.

Ques: 11. What is a performance monitor?

Ans: Windows performance monitor is a tool to capture metrics for the entire server. We can use this tool for capturing events of the SQL server also.
Some useful counters are – Disks, Memory, Processors, Network, etc.

Ques: 12. What is the difference between a Local and a Global temporary table?

Ans: If defined inside a compound statement a local temporary table exists only for the duration of that statement but a global temporary table exists permanently in the database but its rows disappear when the connection is closed.

Ques: 13. What is the SQL Profiler?

Ans: SQL Profiler provides a graphical representation of events in an instance of SQL Server for monitoring and investment purpose. We can capture and save the data for further analysis. We can put filters as well to captures the specific data we want.

Ques: 14. What are the properties of the Relational tables?

Ans: Relational tables have six properties:

1. Values are atomic. 
2. Column values are of the same kind. 
3. Each row is unique. 
4. The sequence of columns is insignificant. 
5. The sequence of rows is insignificant. 
6. Each column must have a unique name.

Ques: 15. What is View?

Ans: A view is a virtual table that contains data from one or more tables. Views restrict data access of the table by selecting only required values and make complex queries easy.

Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database

Ques: 16. Why is replication required on the SQL Server?

Ans: Replication is the mechanism that is used to synchronize the data among the multiple servers with the help of a replica set.

This is mainly used to increase the capacity of the reading and to provide an option to its users to select among various servers to perform the read/write operations.

Ques: 17. What part does database design have to play in the performance of a SQL Server-based application?

Ans. It plays a very major part. When building a new system, or adding to an existing system, it is crucial that the design is correct. Ensuring that the correct data is captured and is placed in the appropriate tables, that the right relationships exist between the tables and that data redundancy is eliminated is an ultimate goal when considering performance. Planning a design should be an iterative process, and constantly reviewed as an application is developed. It is rare, although it should be the point that everyone tries to achieve, when the initial design and system goals are not altered, no matter how slightly. Therefore, a designer has to be on top of this and ensure that the design of the database remains efficient.

Ques: 18. What command is used to create a database in the SQL Server and how?

Ans: CREATEDATABASE Command is used to create any database in the SQL Server. Following is the way to use this command:

CREATEDATABASE Name of the Database

Example: If the name of a database is “employee” then create command to create this database that can be written as CREATEDATABASE employee.

Ques: 19. What is an extended stored procedure? Can you instantiate a COM object 

by using T-SQL?

Ans: An extended stored procedure is a function within a DLL (written in a programming language like C, C++ using Open Data Services (ODS) API) that can be called from T-SQL, just the way we call normal stored procedures using the EXEC statement.
 

Yes, you can instantiate a COM (written in languages like VB, VC++) object from T-SQL by using sp_OACreate stored procedure. 

Ques: 20. When should SQL Server-based cursors be used, and not be used?

Ans: SQL Server cursors are perfect when you want to work one record at a time, rather than taking all the data from a table as a single bulk. However, they should be used with care as they can affect performance, especially when the volume of data increases. 

From a beginner’s viewpoint, I really do feel that cursors should be avoided every time because if they are badly written, or deal with too much data, they really will impact a system’s performance. There will be times when it is not possible to avoid cursors, and I doubt if many systems exist without them. If you do find you need to use them, try to reduce the number of records to process by using a temporary table first, and then building the cursor from this. The lower the number of records to process, the faster the cursor will finish. Always try to think “out of the envelope”.