SQL SERVER Interview Questions & Answers – SET 1 (50 Questions & Answers)

Download – Pawan Kumar Khowal – SQL SERVER Interview Questions with Answers

PAGE – 3

PREV

NEXT

Question 21. What are the magic tables? Do we have “Updated” magic table?

Answer-

No we don’t have UPDATED magic table.
The ‘magic tables’ are the INSERTED and DELETED tables, as well as the update() and columns_updated() functions, and are used to determine the changes resulting from DML statements.
• For an INSERT statement, the INSERTED table will contain the inserted rows.
• For an UPDATE statement, the INSERTED table will contain the rows after an update, and the DELETED table will contain the rows before an update.
• For a DELETE statement, the DELETED table will contain the rows to be deleted.

Question 22. What is a filtered index?

Answer-

Consider filter index as — > INDEX with a Where Clause

It uses a filter predicate to index a portion of rows in the table. A filtered index can improve query performance as well as reduce index maintenance and storage costs compared with full-table indexes.

Important points about filtered indexes are –
• They can be created only as NonClustered Index
• They can be used on Views only if they are persisted views.
• They cannot be created on full-text Indexes.

Please check out the sample example below –

CREATE NONCLUSTERED INDEX Ix_NCI ON TestRIDInxs(ID)
WHERE Title= 20

Question 23. Basic difference between stored procedure and user defined function?

Answer-

There are many differences between functions and stored procedures. Some of them are given below-

Functions will allow only Select statement, it will not allow us to use insert, update and delete statement.
Procedures can have select statements as well as DML statements such as insert, update, delete.

Transactions are not allowed within functions. Can use transactions within Stored procedures.

For details please visit –

DIFFERENCES BETWEEN STORED PROCEDURE AND FUNCTION IN SQL SERVER

Question 24. See we have a simple query that’s calling a static function, like “Select * from employee where joiningdate < getstaticdate()”? Does it call function for every time or only for matched rows? How you tune this query?

Answer-

We can do something like below-

DECLARE @t AS DATETIME = GETDATE()
SELECT * FROM TestRIDInxs WHERE joiningDate <= @t

Question 25. Why should we use CTE?

Answer-

A CTE can be used:
• For recursion
• Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
• Reference the resulting non large table multiple times in the same statement.

Question 26. What is the difference between sub query and correlated query

Answer-

Subquery: – The inner query is executed only once. The inner query will get executed first and the output of the inner query used by the outer query. The inner query is not dependent on outer query.

Please check out the example below-

SELECT * FROM Visits WHERE CustomerId IN (SELECT CustomerId FROM Visits WHERE VisitDate = '2013-09-03 00:00:00.000')

Correlated subquery: – The outer query will get executed first and for every row of outer query, inner query will get executed. So the inner query will get executed as many times as number of rows in result of the outer query. The outer query output can use the inner query output for comparison. This means inner query and outer query dependent on each other

Please check out the famous example below- ( 2nd Highest Salary )

SELECT * FROM NthHighest N WHERE 1 = (SELECT DISTINCT(COUNT(*)) FROM NthHighest m WHERE n.Salary < m.Salary )

Question 27 – How do you retrieve random 5 rows from a table

Answer –

SELECT TOP 5 * FROM [Table_Name] ORDER BY NEWID()

Question 28 – What exactly you check in the query execution plan window?

Answer –

Query Execution Plans describe the steps and the order used to access data in the database. Normally we check the execution plan for missing indexes, if any. We also check the execution plans if the query or stored procedure is working slowly. If working slowly we need to check for Red flags and see how we can remove them.

You can use execution plan to check out following things –

• Missing Indexes
• Missing Statistics
• Red Flags
• How data is being retrieved (Which path , what all indexes are used)

Question 29 – What is the output of SELECT Len(1234.56)

Answer – 7 (In this case it will consider all the characters including.)

Question 30 – Where in MS SQL Server is ’100’ equal to ‘0’?

Ans – Fill Factor is the answer. Fill-factor settings of 0 and 100 are equal. If fill-factor is set to 100 or 0, the SQL Server Engine fills pages to their capacity while creating indexes.
Server default Value = 0.

PREV

NEXT