Advanced SQL interview questions, Advanced SQL Queries, Advanced SQL tutorial, Advanced SQL tutorial pdf, Basic difference between stored procedure and user defined function?, Difficult SQL Interview Questions, Download SQL Questions, Download SQL Server Interview Question in PDF, Download SQL SERVER Interview questions, Download SQL Server Interview questions and answers, download sql server interview questions and answers pdf, download sql server interview questions by Pawan Khowal, download sql server interview questions by Pawan Kumar, download sql server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, Free Download SQL SERVER Interview questions, How do you retrieve random 5 rows from a table, Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins Puzzle, like “Select * from employee where joiningdate < getstaticdate()”? Does it call function for every time or only for matched rows? How you tune this query?, Puzzles, Queries for SQL Interview, See we have a simple query that’s calling a static function, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Common Interview Questions, SQL Common Interview Questions and answers, SQL FAQ, SQL FAQs, SQL Interview Q & A, SQL Interview Questions, SQL Joins, SQL Queries, SQL Queries asked in interviews, SQL Questions, SQL Server, SQL Server - General Interview Questions and Answers, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, SQL SERVER Interview questions and answers for experienced, sql server interview questions and answers for net developers, SQL SERVER Interview questions for experienced, SQL SERVER Interview questions pdf, SQL SERVER Tips, SQL SERVER Tricky questions, SQL Skills, SQL Tips & Tricks, SQL Tips and Tricks, SQL Tricks, SQL Tricky question, SQL Tutorial, SQLSERVER, T-SQL, T-SQL Interview questions, T-SQL Programming, T-SQL Server Interview Questions, T-SQL Tutorial, TOP 100 SQL SERVER INTERVIEW QUESTIONS, Top 50 SQL Server Questions & Answers, Top performance tuning tools, Tough PL SQL Interview Questions, Tough SQL Interview Questions, Tough SQL Queries, Tough SQL Queries Interview Questions, Tough SQL Questions, Tricky SQL Interview Questions, Tricky SQL Interview Questions and answers, Tricky SQL Queries for Interview, Tricky SQL SERVER Interview Questions and answers, TSQL, TSQL Interview questions, TSQL Queries, What are the magic tables? Do we have “Updated” magic table?, What is a filtered index?, What is the difference between sub query and correlated query, What is the output of SELECT Len(1234.56), Where in MS SQL Server is ’100’ equal to ‘0’?, Why should we use CTE?
SQL SERVER Interview Questions & Answers – SET 1 (50 Questions & Answers)
PAGE – 3
Question 21. What are the magic tables? Do we have “Updated” magic table?
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?
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?
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 –
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?
We can do something like below-
DECLARE @t AS DATETIME = GETDATE() SELECT * FROM TestRIDInxs WHERE joiningDate <= @t
Question 25. Why should we use CTE?
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
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
SELECT TOP 5 * FROM [Table_Name] ORDER BY NEWID()
Question 28 – What exactly you check in the query execution plan window?
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.