Are you looking for real life interview questions? Here is a collection of SQL Server interview questions based on my experience. This collection may not contain expert level questions but it will surely help the beginners and intermediate.
Download complete set of 50 interview questions
1.) What is clustered, non-clustered and unique index. How many indexes can be created on a table?
Answer: 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 Non-Clustered Index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. We can have 249 non-clustered indexes per table.
2.) What are the different types of backups?
Answer: A database may be backed up to disk or to tape. SQL Server provides several different kinds of backups including Complete, Differential, Transaction Log, and File(s) and Filegroup(s) backup. A combination of these backups may be used to formulate a robust disaster recovery strategy.
3.) What is de-normalization? When would you use it?
Answer: De-normalization is the reverse process of normalization. It is the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced.
4.)How would you determine whether the service pack currently installed?
Answer: @@Version stores the build number of the sqlservr.exe, which is used to determine the service pack installed.
5.) What is SQL injection?
Answer: SQL injection is a technique used to take advantage of non-validated input vulnerabilities to pass SQL commands through a web application for execution by a backend database. Attackers take advantage of the fact that programmers often chain together SQL commands with user-provided parameters, and can therefore embed SQL commands inside these parameters. The result is that the attacker can execute arbitrary SQL queries and/or commands on the backend database server through the web application.
6.) Write a SQL statement that gives the 5th highest Rank from the Student Results table.
Answer:
SELECT TOP 1 Rank FROM (SELECT DISTINCT TOP 5 Rank FROM Results ORDER BY Rank DESC) ORDER BY Rank
7.) What is the Referential Integrity?
Answer: Referential integrity refers to the consistency that must be maintained between primary and foreign keys, i.e. every foreign key value must have a corresponding primary key value.
8.) What is the difference between UNION and JOIN?
Answer: A JOIN selects columns from 2 or more tables whereas UNION selects rows
9.) What is the row size in SQL Server 2000?
Answer: 8060 Bytes.
10.) How would you add or subtract days in a date?
Answer:
SELECT DATEADD(DD, -3, GETDATE()) AS [ThreeDaysAgo] SELECT DATEADD(DD, 3, GETDATE()) AS [ThreeDaysFromToday] D or DAY can be used instead of DD inside the function.
Download complete set of 50 interview questions
If you enjoyed this post, please consider sharing, leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.