Advanced SQL interview questions, Advanced SQL Queries, Advanced SQL tutorial, Advanced SQL tutorial pdf, Any alternative to triggers?, 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 to create linked server., How to find the query running on a given SPID?, How to find the statistics are outdated?, I have written a recursive CTE, Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Is “TRUNCATE” DDL or DML command?, it’s been running infinitely and failed with an error. The requirement is that the recursion required for 1000 times. How could you be able to handle the situation?, 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 Interview questions SQL questions SQL SERVER Interview questions SQL SERVER Interview questions Download SQL SERVER Interview questions Free Download SQL SERVER Interview questions SQL SERVER Tips, 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 is the best value for MAXDOP value?, Which is better a CTE or a subquery? Why?, Which is better “Left Outer” Or “NOT EXIST”?, Why resourceDB introduced?
SQL SERVER Interview Questions & Answers – SET 1 (50 Questions & Answers)
PAGE – 5
Question 41. What is the best value for MAXDOP value?
MAXDOP is Max degree of parallelism. This option controls the number of processors that are used for the execution of a query in a parallel plan.
One can use the max degree of parallelism option to limit the number of processors to use for parallel plan execution and to prevent run-away queries from impacting SQL Server performance by using all available CPUs.
The answer is: It depends. It depends on the hardware, the environment (OLTP vs. OLAP), and the load and so on.
The default value for MAXDOP is 0 (zero) and can be set or viewed using (sp_configure). A value of 0 means that SQL Server will use all processors if a query runs in parallel.
For details please visit –
Question 42. Which is better “Left Outer” Or “NOT EXIST”?
In SQL Server, NOT EXISTS and NOT IN predicates are the best ways to search for missing values, as long as both columns in question are NOT NULL. They produce the safe efficient plans with some kind of an Anti-Join.
LEFT JOIN is less efficient, since it makes no attempt to skip the already matched values in the right table, returning all results and filtering them out instead.
Question 43. How to find the statistics are outdated?
You can check statistics by using below command –
DBCC SHOW_STATISTICS('TestRIDInxs','Ix_Index') Table Name – TestRIDInxs Index Name - 'Ix_Index
Output of the above query is given below-
Question 44. How to find the query running on a given SPID?
You can use below query to find query running on an active SPID. Number (52) is the SPID showing in the below example.
DECLARE @sqltext VARBINARY(128) SELECT @sqltext = sql_handle FROM sys.dm_exec_requests WHERE session_id = 52 SELECT TEXT FROM sys.dm_exec_sql_text(@sqltext);
Question 45. Is “TRUNCATE” DDL or DML command?
Question 46. I have written a recursive CTE, it’s been running infinitely and failed with an error. The requirement is that the recursion required for 1000 times. How could you be able to handle the situation?
Well if you write a proper recursive CTE then it will not run indefinitely.
MAXRECURSION can be used to break the recursive CTE. MAXRECURSION can be used to prevent a poorly formed recursive CTE from entering into an infinite loop.
The following example intentionally creates an infinite loop and uses the MAXRECURSION hint to limit the number of recursion levels to ten.
;WITH CTE AS ( SELECT ParentID FROM Hierarchies WHERE ParentID = 1 UNION ALL SELECT C.ParentID FROM Hierarchies H INNER JOIN CTE C ON C.ParentID = H.ParentID ) SELECT * FROM CTE OPTION (MAXRECURSION 10); GO
You will also get this kind of message-
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 10 has been exhausted before statement completion.
Question 47. Which is better a CTE or a subquery? Why?
Both CTEs and Sub Queries have pretty much the same performance and function.
CTE’s have an advantage over using a subquery in that you can use recursion in a CTE.
The biggest advantage of using CTE is readability. CTEs can be reference multiple times in the same statement where as sub query cannot.
Question 48. Any alternative to triggers?
There are some options available depending exclusively on the requirement. They are
Change Data Capture
Using output clause (Write the data out to a specific Audit table )
Question 49. Why resourceDB introduced?
It is a read-only database that contains system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.
Also Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. In earlier versions of SQL Server, upgrading required dropping and creating system objects. Because the Resource database file contains all system objects, an upgrade is now accomplished simply by copying the single Resource database file to the local server.
For details please visit –
Question 50. How to create linked server.
Please use below T-SQL script or follow the screen shots
USE [master] GO EXEC MASTER.dbo.sp_addlinkedserver @server = N'SRVR002\ACCTG', @srvproduct=N'SQL Server' ; GO