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

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

PAGE – 2

PREV

NEXT

Question 11. What do you mean by Cardinality in SQL Server?

Answer-

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?

Answer- 

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?

Answer –

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?

Answer –

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?

Answer-

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

Answer-

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?

Answer-

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?

Answer-

No you can’t. If you want a procedure that can be executed manually, then you should just create a stored procedure.

PREV

NEXT