Advanced SQL interview questions, Advanced SQL Queries, Advanced SQL tutorial, Advanced SQL tutorial pdf, Can we fire a trigger manually?, Can we use more than one CTE in a single select query?, 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, I have created a table variable can we use it in a nested stored procedure? If not what is the scope of a table variable?, Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins Puzzle, Puzzles, Queries for SQL Interview, 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 Histogram and Density Vector?, What do you mean by Cardinality in SQL Server?, What is a bitmap index, What is a cross apply and how to use this?, Why we can’t put Order by inside the view?
SQL SERVER Interview Questions & Answers – SET 1 (50 Questions & Answers)
PAGE – 2
Question 11. What do you mean by Cardinality in SQL Server?
Cardinality refers to the uniqueness of data values contained in a particular column (attribute) of a database table. The lower the cardinality, the more duplicated elements in a column
There are 3 types of cardinality:
High-cardinality, Normal-cardinality, and Low-cardinality
For details please refer – https://msbiskills.com/2015/04/08/cardinality-in-sql/
Question 12. What are Histogram and Density Vector?
Density is the ratio of unique values with in the given column or a set of columns. Density measure the uniqueness of column or selectivity of column. Density can have value between 0 and 1. If the column has density value 1, it means all the records have same value in that column and less selectivity. Higher the density lowers the selectivity. If the column has density value 0.003, that means there are 1/0.003=333 distinct values in that column.
Density = 1 / (Number of distinct values for a column or set of column)
-- DBCC SHOW_STATISTICS('TestRIDInxs','Ix_Index') WITH DENSITY_VECTOR --
Below information is self-explanatory.
-- DBCC SHOW_STATISTICS('TestRIDInxs','Ix_Index') WITH HISTOGRAM --
Question 13. Why we can’t put Order by inside the view?
There is no need of using ORDER BY in the View. Order by in a View is pointless.
If you try to include ORDER BY in View, it will throw the following error
Msg 1033, Level 15, State 1, Procedure vw_ViewLimit1, Line 5
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
You can do below like-
-- SELECT * FROM [View_Name] ORDER By [Column_Name] --
Question 14. What is a cross apply and how to use this?
The APPLY operator comes in two variants, CROSS APPLY and OUTER APPLY. It is useful for joining two SQL tables or XML expressions. CROSS APPLY is equivalent to an INNER JOIN expression and OUTER APPLY is equivalent to a LEFT OUTER JOIN expression. E.g. below-
-- SELECT f.*, ROW_NUMBER() OVER(ORDER BY file_id, page_id, slot_id) AS Row FROM flag f CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) ORDER BY Row --
Question 15. Can we use more than one CTE in a single select query?
Answer – Yes we can. Please check out the example below.
-- ;WITH CTE1 AS ( SELECT 10 Col1 ), CTE2 AS ( SELECT 20 Col2 ) SELECT a.Col1,b.Col2 FROM CTE1 a FULL OUTER JOIN CTE2 b ON a.Col1 = b.Col2 --
Question 16. How do you know the total transaction count?
Answer – Please check out the query below-
-- SELECT * FROM sys.dm_os_performance_counters WHERE 1=1 AND counter_name = 'Transactions/sec' AND instance_name = '_Total'; --
Question 17. I have created a table variable can we use it in a nested stored procedure? If not what is the scope of a table variable?
Scope of the Table variable is the Batch or Stored Procedure in which it is declared. And they can’t be dropped explicitly, they are dropped automatically when batch execution completes or the Stored Procedure execution completes.
-- DECLARE @temp TABLE ( col1 INT ) INSERT @temp VALUES(1) SELECT * FROM @TEMP GO SELECT * FROM @temp --
After executing above script you will get below error.
(1 row(s) affected)
(1 row(s) affected)
Msg 1087, Level 15, State 2, Line 11
Must declare the table variable “@temp”.
Question 18. Top performance tuning tools
Query Analyzer, SQL Profiler, Index Wizard, Performance Monitor are some Microsoft tools. Some other tools are given below-
• System Stored Procedures (Transact-SQL)
• Red-Gate SQL Monitor
• Trace Flags (Transact-SQL)
• Database Engine Tuning Advisor
• SQL Sentry Performance Advisor
Question 19. What is a bitmap index?
A bitmap index is a special type of structure used by most high-end database management systems to optimize search and retrieval for low-variability data.
Question 20. Can we fire a trigger manually?
No you can’t. If you want a procedure that can be executed manually, then you should just create a stored procedure.