SQL Server Interview Questions

New Interview Question Based on SQL


How to find procedure and function update details :

SELECT name, create_date, modify_date FROM sys.objects WHERE type = 'P' or name='Tbl_Folder'

select * from sys.objects;

SELECT name, create_date, modify_date FROM sys.objects WHERE type = 'fn' AND name = 'fn_NAME'

  

SELECT hostname, net_library, net_address FROM sys.sysprocesses WHERE spid = @@SPID

SELECT  hostname, net_library,net_address, client_net_address FROM    sys.sysprocesses AS S

INNER JOIN    sys.dm_exec_connections AS decc ON S.spid = decc.session_id

WHERE   spid = @@SPID

Recursion:

A common table expression (CTE) provides the significant advantage of being able to reference itself, thereby creating a recursive CTE.

 A recursive CTE is one in which an initial CTE is repeatedly executed to return subsets of data until the complete result set is obtained

How to optimize queries :

1. Learn how to create indexes properly

2. Only retrieve the data you really need

3. Avoid functions on the left hand-side of the operator

4. Consider getting rid of correlated subqueries

5. Avoid wildcard characters at the beginning of a LIKE pattern