SQL Server Brain Basher of the Week #026 - Variable types

Similar to other programming languages, SQL also offers a facility to create variables for holding values temporarily. Variables are created in the memory and can be accessed only within the batch in which they have been declared. Here is a simple question on variables;

What are the three types of variables can be created with SQL Server?

1. Local variable
2. Global variable
3. Temporary variable
4. Cursor variable
5. Table variable

Local variables are known to everyone and it is the type used widely. By default, NULL is assigned to all local variables if no default value is assigned. Values for local variables can be set at the time of declaration, using a SELECT statement or using the SET statement.

DECLARE @i int;

-- this returns NULL
SELECT @i;

DECLARE @x int = 100;

DECLARE @y int, @z int;

SET @y = 300;
SELECT @z = 400;

SELECT @X, @y, @z;

Cursor variables are still given with SQL Server mainly for backward compatibility. Since we can just name to cursor and do all our operations with its name, there is no necessity on declaring a cursor with a variable. Anyway here is an example;

DECLARE @cur CURSOR;
DECLARE @i int;

SET @cur = CURSOR FOR
 SELECT ProductID FROM Production.Product
 WHERE Color = 'Black';

OPEN @cur;
FETCH NEXT FROM @cur INTO @i
WHILE (@@FETCH_STATUS = 0)
BEGIN

 SELECT @i;
 FETCH NEXT FROM @cur INTO @i
END

CLOSE @cur;
DEALLOCATE @cur;

Table variables are useful when we need to maintain a small dataset for temporary operations. It works just like local variables and it holds data in the form of a table. Here is an example of it.

DECLARE @Product TABLE
(
 Id int PRIMARY KEY
 , Name varchar(100) NOT NULL
 , Color varchar(100) INDEX ix_Product NONCLUSTERED
);

INSERT INTO @Product
SELECT ProductID, Name, Color FROM Production.Product
WHERE Color IS NOT NULL OR Color != '';

SELECT * FROM @Product;

As you see, three types of variables that can be declared with SQL Server are Local variables, Cursor variables, and Table variables. There are no types called Global and Temporary variables.