SQL Server Interview Question 26 - 50

Q26. Explain with example Recursive Stored Procedure

SQL Server allows stored procedure to call it self. Restricted to max 32 recursive calls.
This can be used to implement solutions which reques same peace of code calling itself untill some boundary condition is met.

Ex: Lets try to write stored procedure which calculates factorial. 

For example Factorial(5) = 5 * 4 * 3 * 2 * 1 = 120

CREATE PROCEDURE [dbo].[Usp_getfactorial] (@Number    INTEGER,
                                           @Factorial INTEGER OUTPUT)
AS
    DECLARE @In INTEGER
    DECLARE @Out INTEGER
    IF @Number != 1
      BEGIN
          SELECT @In = @Number - 1
          EXEC [Usp_getfactorial]
            @In,
            @Out OUTPUT
          SELECT @Factorial = @Number * @Out
      END
    ELSE
      BEGIN
          SELECT @Factorial = 1
      END
    RETURN
GO 

DECLARE @Number INTEGER = 5
DECLARE @Factorial INTEGER
EXEC [dbo].[Usp_getfactorial] @Number, @Factorial OUTPUT
SELECT @Factorial

Q27. List down some of the important SQL Server global variables 

@@MAX_PRECISION 

Returns the precision level used by decimal and numeric data types as currently set in the server.

SELECT @@MAX_PRECISION AS '@@MAX_PRECISION'

@@TEXTSIZE 

Returns the current value of the TEXTSIZE option.

SELECT @@TEXTSIZE AS '@@TEXTSIZE'
SET TEXTSIZE 1678
SELECT @@TEXTSIZE AS '@@TEXTSIZE'

@@LANGID 

Returns the local language identifier (ID) of the language that is currently being used.

SELECT @@LANGID AS 'Language ID'
SET Language 'Italian'
SELECT @@LANGID AS 'Language ID'
SET Language 'English'

@@CONNECTIONS 

Returns the number of attempted connections - both successful and unsuccessful - since SQL Server was last started.

SELECT @@CONNECTIONS AS '@@CONNECTIONS'

@@ERROR 

Returns the error number for the last Transact-SQL statement executed.

@@ERROR Returns 0 if the previous Transact-SQL statement encountered no errors.
SELECT 1/0
SELECT @@ERROR AS '@@ERROR'

@@IDENTITY 

Returns the last-inserted identity value

IF OBJECT_ID('dbo.IdentityDemo') IS NOT NULL BEGIN DROP TABLE dbo.IdentityDemo END
CREATE TABLE IdentityDemo
(
ID INT Identity(1,1),
FullName NVARCHAR(50)
)
INSERT INTO IdentityDemo (FullName) VALUES ('A'), ('B'), ('C')
SELECT @@IDENTITY AS '@@IDENTITY'

@@IDLE 

Returns the time that SQL Server has been idle since it was last started. 

The result is in CPU time increments, or "ticks," and is cumulative for all CPUs, so it may exceed the actual elapsed time.
SELECT @@IDLE AS '@@IDLE'

@@CPU_BUSY 

Returns the amount of time that SQL Server has spent in active operation since its latest start.

SELECT @@CPU_BUSY AS '@@CPU_BUSY'

@@ROWCOUNT 

Returns the number of rows affected by the last statement. If the number of rows is more than 2 billion, use ROWCOUNT_BIG

IF OBJECT_ID('dbo.RowCountDemo') IS NOT NULL BEGIN DROP TABLE dbo.RowCountDemo END
CREATE TABLE RowCountDemo
(
ID INT Identity(1,1),
FullName NVARCHAR(50)
)
INSERT INTO RowCountDemo (FullName) VALUES ('A'), ('B'), ('C')
SELECT * FROM RowCountDemo
SELECT @@ROWCOUNT AS '@@ROWCOUNT'

@@TOTAL_ERRORS 

Returns the number of disk write errors encountered by SQL Server since SQL Server last started.

SELECT @@TOTAL_ERRORS AS '@@TOTAL_ERRORS'

@@SERVERNAME 

Returns the name of the local server that is running SQL Server.

SELECT @@SERVERNAME AS '@@SERVERNAME'

@@VERSION 

Returns system and build information for the current installation of SQL Server.

SELECT @@VERSION AS '@@VERSION'

@@SERVICENAME 

Returns the name of the registry key under which SQL Server is running.

SELECT @@SERVICENAME AS '@@SERVICENAME'

@@SPID

Returns the session ID of the current user process.

SELECT @@SPID AS 'Session_Id'

@@TOTAL_READ 

Returns the number of disk reads, not cache reads, by SQL Server since SQL Server was last started.

SELECT @@TOTAL_READ AS '@@TOTAL_READ'

@@TOTAL_WRITE

Returns the number of disk writes by SQL Server since SQL Server was last started.

SELECT @@TOTAL_WRITE AS '@@TOTAL_WRITE'

@@TRANCOUNT

Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection.

IF OBJECT_ID('dbo.TranCountDemo') IS NOT NULL BEGIN DROP TABLE dbo.TranCountDemo END
CREATE TABLE TranCountDemo
(
ID INT Identity(1,1),
FullName NVARCHAR(50)
)

BEGIN TRANSACTION T1 

INSERT INTO TranCountDemo (FullName) VALUES ('A')

SELECT @@TRANCOUNT AS '@@TRANCOUNT' 

BEGIN TRANSACTION T2

INSERT INTO TranCountDemo (FullName) VALUES ('B')

SELECT @@TRANCOUNT AS '@@TRANCOUNT' 

BEGIN TRANSACTION T3

SELECT @@TRANCOUNT AS '@@TRANCOUNT' 

INSERT INTO TranCountDemo (FullName) VALUES ('B') 

COMMIT TRANSACTION T1 

SELECT @@TRANCOUNT AS '@@TRANCOUNT' 

Q28. Disabling constraint on all tables

ALTER TABLE Table_Name NOCHECK CONSTRAINT ALL
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

Q29. Query to get free space on hard drives

EXEC master..xp_fixeddrives

Q30. Query to Enable/Disable triggers

ALTER TABLE Table_Name DISABLE TRIGGER Trigger_Name
ALTER TABLE Table_Name ENABLE TRIGGER Trigger_Name
ALTER TABLE Table_Name DISABLE TRIGGER ALL
ALTER TABLE Table_Name ENABLE TRIGGER ALL

Use Database_Name

Exec sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"
Exec sp_msforeachtable "ALTER TABLE ? ENABLE TRIGGER all"

 Q31. Query to list stored procedures modified in N days

SELECT name, modify_date 
FROM sys.objects 
WHERE type='P' 
AND DATEDIFF(D,modify_date,GETDATE()) < 5