T-SQL Miscellaneous 1

1. Employee table has "X" no of records and Dept table has "Y" no of records . What is the max number of records obtained on joins?

Ans : X*Y number of records

2. Consider Employee table having 3 columns EmpID, EmpName, Salary. Get the 3^rd highest salaried employee.
Ans : 

 

3. Difference between Delete and Truncate.

• The TRUNCATE command in SQL removes all rows from a table without logging the individual row deletions in the transaction log. The DELETE command in SQL also removes rows from a table, but it logs the individual row deletions in the transaction log. 
• TRUNCATE will reset the identity on a column whereas DELETE will not reset the identity on a column.
• DELETE is a logged operation on a per row basis. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. So, in case if you are deleting a huge number of records then it can cause your transaction log to grow. This means the deletion of a huge number of records will use more server resources as it logs each and every row that is deleted. That is why your transaction log will grow very rapidly. Since the delete statement records each deleted row it is also slow. Some people ask that if this is done for each row then why does not Microsoft modify the delete statement to not record each deleted row??? The answer is when you run your databases in full recovery mode, detailed logging is necessary for SQL Server to be able to recover your database to the most recent state.
• TRUNCATE logs the deallocation of the data pages in which the data exists. TRUNCATE is faster than DELETE due to the way TRUNCATE "removes" rows from the table. It won't log the deletion of each row; instead it logs the deallocation of the data pages of the table. The TRUNCATE statement removes the data by deallocating the data pages used to store the table data and records only the page deallocation in the transaction log. Actually, TRUNCATE does not remove data, but rather deallocates whole data pages and removes pointers to indexes. The data still exists until it is overwritten or the database is shrunk. This action does not require a lot of resources and is therefore very fast. It is a common mistake to think that TRUNCATE is not logged. This is wrong. The deallocation of the data pages is recorded in the log file. Therefore, "Books Online (BOL)" refers to TRUNCATE operations as "minimally logged" operations. You can use TRUNCATE within a transaction, and when this transaction is rolled-back, the data pages are reallocated again and the database is again in its original, consistent state.
• You can restore the Truncate statements for that particular session but if you close the connection then I think it will not able to restore your data. BUT Delete will be able to restore your data later also.
• While database is in full recovery mode, it can rollback any changes done by DELETE using Log files. TRUNCATE cannot be rolled back using log files in full recovery mode.
• DELETE and TRUNCATE both can be rolled back when surrounded by TRANSACTION if the current session is not closed. If TRUNCATE is written in Query Editor surrounded by TRANSACTION and if session is closed, it cannot be rolled back but DELETE can be rolled back.
• If you use TRANSACTIONS in your code, TRUNCATE can be rolled back. If there is no transaction is used and TRUNCATE operation is committed, it cannot be retrieved from log file. TRUNCATE is DDL operation and it is not logged in log file.

4. Write a Stored procedure to accept comma separated EmpID and return the Employee details

Ans :  

The below example is for the EmpID as integer column

CREATE PROC dbo.Employee_Sp @empid VARCHAR(MAX)

AS

DECLARE @sql VARCHAR(MAX)

SET @sql='SELECT * FROM dbo.Employee WHERE EmpID IN ('+@empid+')'

PRINT(@sql)

  

EXEC Employee_Sp '1,2,3'

Output:

SELECT * FROM dbo.Employee WHERE EmpID IN (1,2,3)

  

CREATE PROC dbo.Employee_Sp @empid VARCHAR(MAX)

AS

DECLARE @sql VARCHAR(MAX)

SET @sql='SELECT * FROM dbo.Employee WHERE EmpID IN ('+@empid+')'

EXEC(@sql)

  

EXEC Employee_Sp '1,2,3'

Suppose EmpID is VARCHAR column

CREATE FUNCTION dbo.Split

(

@str VARCHAR(MAX),

@del VARCHAR(1)

)

RETURNS VARCHAR(MAX)

AS

BEGIN

            DECLARE @len INT

            DECLARE @i INT

            DECLARE @res VARCHAR(MAX)

            SET @res=''

            SET @str='E001,E002,E003'

            SET @del=','

            SET @len=LEN(@str)

            SET @i=CHARINDEX(@del,@str)

            WHILE @i>=1

            BEGIN

                  SET @res=@res+','''+LEFT(@str,@i-1)+''''

                  SET @str=SUBSTRING(@str,@i+1,@len)

                  SET @i=CHARINDEX(@del,@str)

            END

            SET @res=@res+','''+@str+''''

            SET @res=SUBSTRING(@res,2,LEN(@res))

            RETURN @res

END

  

ALTER PROC dbo.Employee_Sp @empid VARCHAR(MAX)

AS

DECLARE @sql VARCHAR(MAX)

SET @sql='SELECT * FROM dbo.Employee WHERE EmpID IN ('+dbo.Split(@empid,',')+')'

EXEC(@sql)

EXEC Employee_Sp 'E001,E002,E003'

5. Difference between temp table and table variable
Ans :
·         Both temp table and table variable are stored in the tempDB.

·         Scope of Temporary table is within the session. Scope of Table variable is within the batch.

·         Table variable can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement. In case of temp table indexes can be added after the table has been created.

·         On table variable Truncate is not allowed whereas it is allowed on temp table.

·         Temp table will be recompiled in the stored procedure, whereas table variable won’t get recompiled.

·         Temp tables are bound to transactions whereas table variables are not bound to transactions.

·         Temp tables created inside a stored procedure they are destroyed upon completion of the stored procedure.  

·         Table variables can be used in case of small data set. Temp table is used in case of large data set.

·         Table variables are not written to transaction log whereas temp table are written to transaction log.

·         If a table variable is declared in a stored procedure, it is local to that stored procedure and cannot be referenced in a nested procedure.

·         Temporary tables can also be referenced in nested stored procedures and may be the right fit if the object needs to be available for a longer duration (not just scoped to the batch like table variables).

·         Temporary Tables cannot be created within a function whereas table variables can be created within a function.

CREATE FUNCTION dbo.TestFn( @i int)

RETURNS INT

AS

BEGIN

CREATE TABLE #A( I INT)

RETURN 1

END

Msg 2772, Level 16, State 1, Procedure TestFn, Line 5

Cannot access temporary tables from within a function.

·         Table variables should be referenced with an alias when used in joins etc where as alias is not mandatory for Table Variables.

CREATE TABLE #Values1  (ID INT,VALUE VARCHAR(100))

CREATE TABLE #Values2  (ID INT,VALUE VARCHAR(100))

INSERT INTO #Values1 (ID, VALUE)

SELECT 1,'A'

UNION

SELECT 2,'B'

INSERT INTO #Values2 (ID, VALUE)

SELECT 1,'A'

UNION

SELECT 3,'C'

SELECT *

FROM #Values1

JOIN #Values2 ON #Values1.ID = #Values2.ID

Executes Successfully!!

DECLARE @Values1 TABLE (ID INT,VALUE VARCHAR(100))

DECLARE @Values2 TABLE (ID INT,VALUE VARCHAR(100))

INSERT INTO @Values1 (ID, VALUE)

SELECT 1,'A'

UNION

SELECT 2,'B'

INSERT INTO @Values2 (ID, VALUE)

SELECT 1,'A'

UNION

SELECT 3,'C'

SELECT *

FROM @Values1

      JOIN @Values2 ON @Values1.ID = @Values2.ID

Fails with an error:
Msg 137, Level 16, State 1, Line 15
Must declare the scalar variable “@Values1″.
Msg 137, Level 16, State 1, Line 15
Must declare the scalar variable “@Values2″.

DECLARE @Values1 TABLE (ID INT,VALUE VARCHAR(100))

DECLARE @Values2 TABLE (ID INT,VALUE VARCHAR(100))

INSERT INTO @Values1 (ID, VALUE)

SELECT 1,'A'

UNION

SELECT 2,'B'

INSERT INTO @Values2 (ID, VALUE)

SELECT 1,'A'

UNION

SELECT 3,'C'

SELECT *

FROM @Values1 a

      JOIN @Values2 b ON a.ID = b.ID

Executes Successfully!!

·         In nested procedures temporary tables are visible to the procedures called where as table variables are not.

CREATE TABLE #Values1  (ID INT,VALUE VARCHAR(100))

INSERT INTO #Values1 (ID, VALUE)

SELECT 1,'A'

UNION

SELECT 2,'B'

EXEC sp_executesql @statement = N'SELECT * FROM #Values1'

Executes Successfully!!

DECLARE @Values1 TABLE (ID INT,VALUE VARCHAR(100))

INSERT INTO @Values1 (ID, VALUE)

SELECT 1,'A'

UNION

SELECT 2,'B'

EXEC sp_executesql @statement = N'SELECT * FROM @Values1'

Fails as the table variable @Values1 is not visible to sp_executesql Procedure.
Error:
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable “@Values1″.

·         Procedures that use table variables can be pre-compiled where are those that use Temporary tables cannot be pre-compiled.

·         Indexes and Statistics can be created on temporary table, whereas it’s not possible on table variables.