Nov 12

SQL Triggers

A SQL Trigger is a special type of stored procedure that automatically executes when an event occurs in the database server.

In my previous post we created employee table. So here I will use same table to create history of employee. This post explains how to create history on performing Insert, update or delete operation on employee table.

So let’s create EmployeeHistory table first,

CREATE TABLE [dbo].[EmployeeHistory](
	[EmployeeHistoryId] [bigint] IDENTITY(1,1) NOT NULL,
	[EmployeeId] [int] NOT NULL,
	[EmployeeName] [varchar](100) NULL,
	[DepartmentId] [int] NULL,
	[ReportTo] [int] NULL,
	[Salary] [int] NOT NULL,
	[Action] [varchar](20) NOT NULL,
 CONSTRAINT [PK_EmployeeHistory] PRIMARY KEY CLUSTERED 
(
	[EmployeeHistoryId] ASC
)
) ON [PRIMARY]

Type of Trigger

There are two types of triggers in SQL Server database

  1. DML Trigger
    1. After trigger
    2. Instead of trigger
  2. DDL Trigger

DML Trigger

  1. DML triggers execute when a user tries to modify data through a data manipulation language (DML)
  2. DML events are INSERT, UPDATE, or DELETE statements on a table or view
  3. These are frequently used for enforcing business rules and data integrity
  4. Example: Creating audit log history for employee table

1. AFTER TRIGGER

  • AFTER trigger is fired only when all operations specified in the triggering SQL statement have executed successfully.
  • AFTER triggers cannot be defined on views.

Here we can create separate after triggers for INSERT, UPDATE,  DELETE as well as single trigger too. I will show you how to write single trigger to perform all operations.

CREATE TRIGGER [dbo].[AfterTriggerEmployee] ON [dbo].[Employee]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
	IF EXISTS (SELECT 1 FROM inserted)
	BEGIN
		IF EXISTS (SELECT 1 FROM deleted)
		BEGIN
			INSERT INTO EmployeeHistory(EmployeeId, EmployeeName, DepartmentId, ReportTo, Salary, [Action])
			SELECT EmployeeId, EmployeeName, DepartmentId, ReportTo, Salary, 'Update' FROM inserted u;
		END
		ELSE
		BEGIN
			INSERT INTO EmployeeHistory(EmployeeId, EmployeeName, DepartmentId, ReportTo, Salary, [Action])
			SELECT EmployeeId, EmployeeName, DepartmentId, ReportTo, Salary, 'Insert' FROM inserted i;
		END
	END 
	ELSE
	BEGIN
		INSERT INTO EmployeeHistory(EmployeeId, EmployeeName, DepartmentId, ReportTo, Salary, [Action])
		SELECT EmployeeId, EmployeeName, DepartmentId, ReportTo, Salary, 'Delete' FROM deleted d;
	END
END

Now, Perform INSERT, UPDATE and DELETE Operation one by one and finally check the EmployeeHistory table

INSERT INTO Employee(EmployeeName, DepartmentId, ReportTo, Salary)
VALUES('Trigger Test Employee', 3, 3, 23000)

UPDATE Employee
SET EmployeeName = 'Trigger Test Employee 1',
Salary = 26000
WHERE EmployeeName = 'Trigger Test Employee'

DELETE FROM  Employee WHERE EmployeeName = 'Trigger Test Employee 1'

SELECT * FROM EmployeeHistory

It will display following output on SQL console window.

DML After Trigger

DML After Trigger

2. INSTEAD OF TRIGGER

  • This trigger is executed before the triggering SQL statement, therefore, overriding the actions of the triggering statements
  • INSTEAD OF cannot be specified for DDL or logon triggers
  • Only one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement can be defined on a table or view
  • Actual insert, delete or update operation do not occur at all
  • INSTEAD OF triggers are not allowed on updatable views that use WITH CHECK OPTION
  • INSTEAD OF TRIGGER can be defined on views

Suppose I want to give limited access to my application, to manage this I will create view so that developers do not get access to the tables  for DML operation. In sql server we can achieve this through the INSTEAD OF triggers.

Let’s create SQL View on Employee and Department table by joining tables 

CREATE VIEW [dbo].[VW_Employee_GetAll]
AS
SELECT e.EmployeeName, e.ReportTo, e.Salary, d.Name AS Designation
FROM Employee e INNER JOIN Department d ON e.DepartmentId = d.Id

Now, create INSTEAD OF trigger for insert, update and delete operation on view VW_Employee_GetAll

 

DDL Trigger

  1. DDL triggers execute in response to a variety of data definition language (DDL)
  2. DDL events are CREATE, ALTER, DROP, GRANT, DENY, REVOKE and UPDATE STATISTICS 
  3. Logon triggers fires in response to LOGON event that raised when a user session is being established
  4. DDL triggers can be DATABASE scoped or SERVER scoped.

SQL Server itself provides report to see the schema changes. However, if you wish you can write your own trigger to track developers who have changed the schema.

Here I am creating database level DDL Trigger which will track information of procedures, tables and functions which are created, altered or dropped. For this I will use SQL Server EVENTDATA()  object and will read the xml object.

EVENTDATA() – captures all the necessary information on the lines, what/who/when in the form of an XML string. 
/EVENT_INSTANCE/DatabaseName – Returns the databasename on which the DDL operation was performed. 
/EVENT_INSTANCE/EventType – Returns the DDL operation, Create/Alter/Drop 
/EVENT_INSTANCE/ObjectName – Returns name of a table/stored procedure/index etc., 
/EVENT_INSTANCE/ObjectType – Returns object type. Ex. StoredProcedure, Table, Index etc., 
/EVENT_INSTANCE/TSQLCommand – Returns complete T-SQL command that made the modification. 
/EVENT_INSTANCE/LoginName – Returns the login name which made the modification. 

To keep track of these object we first need to create table i.e. DDLSchemaChangesHistory so let’s  create it.

CREATE TABLE [dbo].[DDLSchemaChangesHistory](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[ObjectName] [varchar](255) NOT NULL,
	[ObjectType] [varchar](255) NOT NULL,
	[OperationType] [varchar](255) NOT NULL,
	[SQLCommand] [varchar](8000) NOT NULL,
	[Time] [datetime] NOT NULL,
	[LoginName] [varchar](255) NOT NULL,
 CONSTRAINT [PK_DDLSchemaChangesHistory] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)
) ON [PRIMARY]

Now create DDL Trigger 

CREATE TRIGGER DDLTriggerCsharpDocs
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
	CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
	CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION
AS
BEGIN
	DECLARE @EventData XML
	SET @EventData = EVENTDATA()

	INSERT INTO DDLSchemaChangesHistory(ObjectName, ObjectType, OperationType, SQLCommand, [Time], LoginName)
	VALUES
	(
		@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'), 
		@EventData.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'), 
		@EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),
		@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'), 
		GETDATE(),
		@EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
	)
END

Now let’s test it using below scripts

ALTER TABLE DDLSchemaChangesHistory
ADD ServerUser VARCHAR(50)
GO

ALTER TABLE DDLSchemaChangesHistory
DROP COLUMN ServerUser
GO

SELECT * FROM DDLSchemaChangesHistory
GO

After successful DDL trigger execution it showing me following output

"SQL

Enable and Disable Trigger

To disable the trigger use below query

DISABLE TRIGGER [AfterTriggerEmployee] ON Employee;

To enable trigger use below query

ENABLE TRIGGER [AfterTriggerEmployee] ON Employee;

Limitations of Triggers

  1. TRUNCATE commands does not activate triggers to execute
  2. It is easy to view table and their relationship, constraints, indexes, stored procedures and views but triggers are difficult to view.
  3. Triggers are automatically executing hence they are not visible from application and can be traced through debugging mechanism only.
  4. Easy to forget about triggers if there is no documentation hence it is difficult to figure out for new developers.
  5. Triggers automatically runs every time when database field updated which leads to run system slower

Personally, I would recommend to avoid triggers and use stored procedures. In one on my project I used triggers to keep track of audit history which was a separate database. To avoid code duplication (stored procedures to INSERT, UPDATE and DELETE) and stored procedure dependency I used triggers.

Nov 11

SQL Functions

User Defined Functions/SQL Functions

A user defined function or SQL Function is a reusable routine that accepts parameters, performs an action, such as a complex calculation, and returns the result of that action as a value. The return value can either be a single value or a table data.

There are two type of user defined SQL functions i.e. Scalar Functions and Table Value Functions

Scalar Functions – These type of function used for complex logic processing or complex calculations and returns the scalar (single) value of any type i.e. string, integer etc.

CREATE FUNCTION [dbo].[fnGetEmployeeCount]()  
RETURNS INT   
AS   
BEGIN  
    DECLARE @EmployeeCount INT;  
    SET @EmployeeCount = 0; 

    SELECT @EmployeeCount = COUNT(EmployeeId)   
    FROM Employee 

    RETURN @EmployeeCount;  
END;

To select employee count from database

SELECT dbo.fnGetEmployeeCount() AS EmployeeCount

Table Value Functions – These type of functions generally used for complex query processing and returns the data in tabular form. For example –  Getting employees for a department in tabular form.

CREATE FUNCTION [dbo].[fnGetEmployeeForDepartment](@DepartmentId int)  
RETURNS @ReturnEmployeeInformation TABLE 
(
    EmpID int NOT NULL, 
    EmployeeName varchar(50) NULL, 
    Department varchar(50) NULL
)
AS   
BEGIN  
        DECLARE @EmpID INT
        DECLARE @EmployeeName VARCHAR(50) 
        DECLARE @Department VARCHAR(50) 

	INSERT INTO @ReturnEmployeeInformation(EmpID, EmployeeName, Department)
	SELECT e.EmployeeId, E.EmployeeName, d.Name
	FROM Employee e INNER JOIN Department d ON e.DepartmentId = d.Id
	WHERE DepartmentId = @DepartmentId
	
	RETURN;
END

To select employees from testing department

SELECT * FROM dbo.fnGetEmployeeForDepartment(3)
System Provided Functions

SQL Server has many built-in defined functions. These functions are generally used to cast, converting values, dates, aggregating values in group by clause.

In this post I will include most frequently used built-in functions in SQL Server which are categorized into Aggregate, Date and Time, Mathematical, String, Rowset and Other functions.

  • Aggregate Functions

    Aggregate functions  frequently used with the GROUP BY clause  perform a calculation on a set of values and return a single value.

Function  Meaning
AVG Returns the average of the values in a group. Null values are ignored.
COUNT Returns the number of items in a group. COUNT always returns an int data type value.
MAX Returns the maximum value in the expression.
MIN Returns the minimum value in the expression.
SUM Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only. Null values are ignored.
  • Date and Time Functions

    All system date and time values are derived from the operating system of the computer on which the instance of SQL Server is running

Function  Meaning
CURRENT_TIMESTAMP Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running. This function is the ANSI SQL equivalent to GETDATE.
GETDATE Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running.
DATEADD Returns a specified date with the specified number interval (signed integer) added to a specified datepart of that date.
DATEDIFF Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate
DATEPART Returns an integer that represents the specified datepart of the specified date.
DAY Returns an integer representing the day (day of the month) of the specified date.
MONTH Returns an integer that represents the month of the specified date.
YEAR Returns an integer that represents the year of the specified date.
GETUTCDATE Returns the current database system timestamp as a datetime value. The database time zone offset is not included. This value represents the current UTC time (Coordinated Universal Time). This value is derived from the operating system of the computer on which the instance of SQL Server is running
ISDATE Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.
  • Mathematical Functions

    These are scalar functions perform a calculation, usually based on input values that are provided as arguments, and return a numeric value.

Function  Meaning
ABS A mathematical function that returns the absolute (positive) value of the specified numeric expression.
CEILING Returns the smallest integer greater than, or equal to, the specified numeric expression.
FLOOR Returns the largest integer less than or equal to the specified numeric expression.
RAND Returns a pseudo-random float value from 0 through 1, exclusive.
ROUND Returns a numeric value, rounded to the specified length or precision
  • String Functions

    These are scalar functions perform an operation on a string input value and return a string or numeric value. When string functions are passed arguments that are not string values, the input type is implicitly converted to a text data type

Function  Meaning
CHAR Converts an int ASCII code to a character.
CHARINDEX Searches an expression for another expression and returns its starting position if found.
LEFT Returns the left part of a character string with the specified number of characters.
RIGHT Returns the right part of a character string with the specified number of characters.
LEN Returns the number of characters of the specified string expression, excluding trailing blanks.
REPLACE Replaces all occurrences of a specified string value with another string value.
CONCAT Returns a string that is the result of concatenating two or more string values.
REVERSE Returns the reverse order of a string value.
SUBSTRING Returns part of a character, binary, text, or image expression in SQL Server.
STUFF The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
TRIM Removes the space character char(32) or other specified characters from the start or end of a string.
LTRIM Returns a character expression after it removes leading blanks.
RTRIM Returns a character string after truncating all trailing spaces.
LOWER Returns a character expression after converting uppercase character data to lowercase.
UPPER Returns a character expression with lowercase character data converted to uppercase.
  • Other Functions
Function  Meaning
CAST Converts an expression of one data type to another
COALESCE Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL
CONVERT Converts an expression of one data type to another
@@IDENTITY Is a system function that returns the last-inserted identity value.
ISNULL Replaces NULL with the specified replacement value.
ISNUMERIC Determines whether an expression is a valid numeric type.
NEWID Creates a unique value of type uniqueidentifier.
NULLIF Returns a null value if the two specified expressions are equal
ROW_NUMBER Numbers the output of a result set. More specifically, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
@@ROWCOUNT Returns the number of rows affected by the last statement.
SCOPE_IDENTITY Returns the last identity value inserted into an identity column in the same scope

Microsoft has introduced new built-in functions like Security(encryption and decryption),  JSON, Cryptographic functions in SQL Server 2012 onward but may be part of professional and enterprise edition.  These functions generally used by senior developers and database expert. I use express edition and hence I will cover these part in my upcoming post.

Nov 05

SQL Query – Customer having maximum number of accounts

Today, we will discuss one interested and most frequently asked interview question in sql server on customer and account table. I asked this question to may candidates, some gave right answer and some failed to answer it. The question was “Write down sql query to retrieve the customer having maximum number of accounts”. The problem statement for that query was,

Problem Statement:

Statement 1: I have the customer and account details. Write down the query to retrieve the customer having maximum number of accounts or vice versa in descending order.

Statement 2: One customer can have multiple accounts and one account can have multiple customer.

Basically, idea behind this question is to select right candidate who has good knowledge(and hands on too) on design, SQL joins, clauses, database design, normalization of tables, inbuilt functions in SQL server and ability to convert business requirement into code. 

What I observed, candidates generally started to write query without understanding the requirement and discussing the approach they are going to use. Anyways…No issues at all !!

Candidates generally created only two tables and kept CustomerId in account table and tried to write query. 

Problem Solution:

The solution of above question is,

  1. Create Customer table with primary key
  2. Create Account table with primary key
  3. Create Mapping table for both accounts and add foreign keys by referring to customer and account tables
  4. Use inner join to join three tables
  5. Group the results using CustomerId or AccountId
  6. Apply order by clause 
  7. Write down select the statement

Once you create a physical tables in SQL Server then it will like below

Account and Customer Tables

Account and Customer Tables

I am not going in depth by creating physical tables and inserting the data. Rather, I would use the temporary table concept just to show case you, how the solution would be.

Create Customer table
DECLARE @Customer TABLE
(
	CustomerId INT,
	CustomerName VARCHAR(50)
)
Create Account Table
DECLARE @Account TABLE
(
	AccountId INT,
	AccountName VARCHAR(50)
)
Create CustomerAccount Table
DECLARE @CustomerAccount TABLE
(
	CustomerId INT,
	AccountId INT
)
Insert few records in Customer Table
INSERT INTO @Customer(CustomerId, CustomerName)
VALUES('1000', 'Customer 1')

INSERT INTO @Customer(CustomerId, CustomerName)
VALUES('1001', 'Customer 2')

INSERT INTO @Customer(CustomerId, CustomerName)
VALUES('1002', 'Customer 3')

INSERT INTO @Customer(CustomerId, CustomerName)
VALUES('1003', 'Customer 4')

INSERT INTO @Customer(CustomerId, CustomerName)
VALUES('1004', 'Customer 5')
Insert few records in Account Table
INSERT INTO @Account(AccountId, AccountName)
VALUES('2000', 'Account 1')

INSERT INTO @Account(AccountId, AccountName)
VALUES('2001', 'Account 2')

INSERT INTO @Account(AccountId, AccountName)
VALUES('2002', 'Account 3')

INSERT INTO @Account(AccountId, AccountName)
VALUES('2003', 'Account 4')

INSERT INTO @Account(AccountId, AccountName)
VALUES('2004', 'Account 5')

INSERT INTO @Account(AccountId, AccountName)
VALUES('2005', 'Account 6')

INSERT INTO @Account(AccountId, AccountName)
VALUES('2006', 'Account 7')

INSERT INTO @Account(AccountId, AccountName)
VALUES('2007', 'Account 8')

INSERT INTO @Account(AccountId, AccountName)
VALUES('2008', 'Account 9')

INSERT INTO @Account(AccountId, AccountName)
VALUES('2009', 'Account 10')

INSERT INTO @Account(AccountId, AccountName)
VALUES('2010', 'Account 11')

INSERT INTO @Account(AccountId, AccountName)
VALUES('2011', 'Account 12')

INSERT INTO @Account(AccountId, AccountName)
VALUES('2012', 'Account 13')

INSERT INTO @Account(AccountId, AccountName)
VALUES('2013', 'Account 14')

INSERT INTO @Account(AccountId, AccountName)
VALUES('2014', 'Account 15')
Now create dummy data in mapping table i.e. insert few records in CustomerAccount table
INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1000, 2000)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1000, 2008)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1001, 2009)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1001, 2000)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1001, 2008)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1001, 2009)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1001, 2012)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1003, 2008)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1003, 2009)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1003, 2010)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1003, 2011)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1003, 2012)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1003, 2013)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1003, 2014)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1004, 2000)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1004, 2008)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1004, 2009)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1004, 2010)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1002, 2011)

Now lets visualize the data in tabular form

CustomerId CustomerName
1000 Customer 1
1001 Customer 2
1002 Customer 3
1003 Customer 4
1004 Customer 5
AccountId AccountName
2000 Account 1
2001 Account 2
2002 Account 3
2003 Account 4
2004 Account 5
2005 Account 6
2006 Account 7
2007 Account 8
2008 Account 9
2009 Account 10
2010 Account 11
2011 Account 12
2012 Account 13
2013 Account 14
2014 Account 15
CustomerId AccountId
1000 2000
1000 2008
1001 2009
1001 2000
1001 2008
1001 2009
1001 2012
1003 2008
1003 2009
1003 2010
1003 2011
1003 2012
1003 2013
1003 2014
1004 2000
1004 2008
1004 2009
1004 2010
1002 2011
Query to display customer having maximum number of accounts
SELECT c.CustomerName, COUNT(ca.AccountId) AS AccountCount
FROM @Customer c 
INNER JOIN @CustomerAccount ca ON c.CustomerId = ca.CustomerId
INNER JOIN @Account a ON a.AccountId = ca.AccountId
GROUP BY ca.CustomerId, c.CustomerName
ORDER BY AccountCount DESC
Output Display
Customer with maximum accounts

Customer with maximum accounts

Query to display accounts having maximum number of customers
SELECT a.AccountName, COUNT(ca.CustomerId) AS CustomerCount
FROM @Customer c 
INNER JOIN @CustomerAccount ca ON c.CustomerId = ca.CustomerId
INNER JOIN @Account a ON a.AccountId = ca.AccountId
GROUP BY ca.AccountId, a.AccountName
ORDER BY CustomerCount DESC
Output Display
Accounts with maximum customer

Accounts with maximum customer

Interviewer Expectation 

Frankly speaking, interviewer never expecting that you should write complete compiled and syntactically correct query but expects the approach for the solution to solve the problem.

The above interview question judges your knowledge in terms of below points

  1. Candidates ability to design tables
  2. Normalization of database tables
  3. Knowledge on relationship between tables
  4. Understanding of SQL Joins
  5. Knowledge on inbuilt functions like Count, Max etc.
  6. Understanding of clauses in SQL
  7. Requirement understanding and converting business requirement to actual code

That’s It !!

I hope you found the right solution and idea behind the question. Don’t forget to give thumbs up, like and comment if you really find this solution helpful. 

Oct 29

SQL Cursor vs Temp Table

In last two post we discussed about SQL Cursor and SQL Server Temporary Tables along with their advantages and disadvantages.  Cursors do the row by row processing and are extremely poor in performance.

In this post we will see how temporary tables are useful for processing of result set instead of  cursors. Let’s take an example of tables we created in my previous post SQL Joins

In this example we will see how both approaches used to display records.

SQL Cursor

DECLARE @EmployeeId INT
DECLARE @EmployeeName VARCHAR(50)
DECLARE @Department VARCHAR(50)

DECLARE Employee_Cursor CURSOR FOR
SELECT e.EmployeeId, e.EmployeeName, d.Name 
FROM Employee e INNER JOIN Department d ON e.DepartmentId = d.Id

OPEN Employee_Cursor
FETCH NEXT FROM Employee_Cursor INTO @EmployeeId, @EmployeeName, @Department

WHILE @@FETCH_STATUS = 0   
BEGIN   
       PRINT 'EMP ID: ' + CAST(@EmployeeId as VARCHAR(5))+ ', Employee Name: ' + @EmployeeName + ', Department: ' + @Department
	   
       FETCH NEXT FROM Employee_Cursor INTO @EmployeeId, @EmployeeName, @Department   
END

CLOSE Employee_Cursor   
DEALLOCATE Employee_Cursor

Table Variable

DECLARE @EmployeeId INT
DECLARE @EmployeeName VARCHAR(50)
DECLARE @Department VARCHAR(50)

DECLARE @Employee TABLE
(
	EmployeeId INT,
	EmployeeName VARCHAR(50),
	Department VARCHAR(50)
)

INSERT INTO @Employee(EmployeeId, EmployeeName, Department)
SELECT e.EmployeeId, e.EmployeeName, d.Name 
FROM Employee e INNER JOIN Department d ON e.DepartmentId = d.Id

SELECT * FROM @Employee

WHILE(EXISTS(SELECT 1 FROM @Employee))
BEGIN
	SELECT TOP 1 @EmployeeId = EmployeeId, @EmployeeName = EmployeeName, @Department = Department
	FROM @Employee

	PRINT 'EMP ID: ' + CAST(@EmployeeId as VARCHAR(5))+ ', Employee Name: ' + @EmployeeName + ', Department: ' + @Department

	DELETE FROM @Employee WHERE EmployeeId = @EmployeeId
END

SELECT * FROM @Employee

Oct 29

SQL Server – Temporary Tables

In last post we discussed about SQL Cursor and it’s disadvantages. In this post we will see how to use SQL TempTables and it’s advantages over SQL Cursor. These tables are created at run-time and are created inside tempdb database.

Temporary tables are used by every smart developer rather using cursors. Click here to see syntactically how they are used to process number of records in result set

Characteristics of Temporary Tables

  1. Used to improved the code performance and maintainability.
  2. Generally used to process result set within the batch set or stored procedure.
  3. Pass a table from table-valued functions.
  4. Can be passed as a read-only table-valued parameter in stored procedure.

Type of Temporary Tables 

  1.  Local Temporary Table (starting with #)
  2.  Global Temporary Table (starting with ##)
  3.  Table Variable (starting with @)
  4.  Common Table Expression (CTE)

Let’s see one by one in detail.

Local Temporary Table
  • These tables are start with #
  • Created in tempdb database
  • Local temp tables are local to your connection and are dropped when no longer in use. This means that they are available to session or current connection(current query window) for user and automatically disconnect once user disconnect from instance.
  • They work and act like regular tables
  • Can create index, constraints and carry statistics

Syntax


CREATE TABLE #Table_Name 
(    
	Column1 datatype NOT NULL,
	Column2 datatype NULL
) 

Example


CREATE TABLE #LocalTemp_Table
(
	EmployeeID INT,
	EmployeeName VARCHAR(50),
	Department VARCHAR(50)
)
	
INSERT INTO #LocalTemp_Table(EmployeeID, EmployeeName, Department)
VALUES(1, 'Rohit', 'Development')
	
INSERT INTO #LocalTemp_Table(EmployeeID, EmployeeName, Department)
VALUES(2, 'Rahul', 'Testing')
	
SELECT * FROM #LocalTemp_Table	

In above example the scope of local table exist in current session/instance of current user to current window only. In above example the scope of local table exist in current session/instance of current user to current window only.  This means if you open new query window it will gives you error.

Global Temporary Table
  • These tables are start with ##
  • Created in tempdb database
  • They work and act like regular tables
  • They are available to be shared across connections. This means that these tables available to all users and all sessions/instances. This means that these tables available to all users and all sessions/instances.    These are automatically deleted when all SQL connections have been closed
  • Can create index, constraints and carry statistics i.e. Can operate as DDL Operation

Syntax

CREATE TABLE ##Table_Name 
(    
	Column1 datatype NOT NULL,
	Column2 datatype NULL
) 

Example

CREATE TABLE ##GlobalTemp_Table
(
	EmployeeID INT,
	EmployeeName VARCHAR(50),
	Department VARCHAR(50)
)
	
INSERT INTO ##GlobalTemp_Table(EmployeeID, EmployeeName, Department)
VALUES(1, 'Rohit', 'Development')
	
INSERT INTO ##GlobalTemp_Table(EmployeeID, EmployeeName, Department)
VALUES(2, 'Rahul', 'Testing')
	
SELECT * FROM ##GlobalTemp_Table
Table Variable
  • These tables are start with @
  • Can not be indexed, truncated
  • Do not carry statistics
  • Acts like variable and exist in particular batch of query execution. It gets dropped after completion of batch execution.
  • It has been a common myth for some time that table variables only work in memory. However, Temp tables and table variables are instantiated in tempdb and SQL Server will try to keep them in memory
  • It also allows you to create primary key and identity but not allows to create non-clustered index

Syntax

CREATE TABLE @Table_Name 
(    
	Column1 datatype NOT NULL,
	Column2 datatype NULL
)

Example

DECLARE @TempTable TABLE
(
	EmployeeID INT IDENTITY(1,1),
	EmployeeName VARCHAR(50),
	Department VARCHAR(50)
)
		
INSERT INTO @TempTable(EmployeeName, Department)
VALUES('Rohit', 'Development')
		
INSERT INTO @TempTable(EmployeeName, Department)
VALUES('Rahul', 'Testing')
		
SELECT * FROM @TempTable
GO
	
-- starting Next batch it will gives you error
SELECT * FROM @TempTable
GO
Common Table Expression (CTE)
  • These are temporary result set of complex queries and sub-queries
  • Defined by using WITH statement
  • Scope is limited to current query only
  • Created in momory and not in tempdb
  • Cannot create index, constraints and carry statistics

Syntax

;With CTE_Result(Column1, Column2)
AS
(
	SELECT Column1, Column2
	FROM TABLE table_name
	WHERE condition
)

SELECT * FROM CTE_Result
WHERE condition

Example

;With CTE_Result(EmployeeId, EmployeeName, Department)
AS
(
	SELECT e.EmployeeId, e.EmployeeName, d.Name AS Department
	FROM Employee e INNER JOIN Department d ON e.DepartmentId = d.Id
)

SELECT * FROM CTE_Result
WHERE Department IN ('Development','Testing')