Everything you need to know about SQL variables

Expanding Your Knowledge with Variables 

Welcome to InsightNinja, your go-to destination for data-driven things in Data Analytics. In this post, we'll cover  methods used for creation and usage of variables in SQL Server.

I. What is variable in SQL?

 SQL Server variables are used to store and manipulate data within the context of a SQL Server query or stored procedure. Variables provide a way to store intermediate results, temporary values, or user-defined values that can be referenced and manipulated throughout the execution of a query or procedure.

We will refer following table 'STATS' for all examples in this blog.

Here's table creation script

CREATE TABLE STATS    (  

    Match_No        INT                 PRIMARY KEY,

    Opposition      VARCHAR(50)         Not null,

    RunsScored      INT                 DEFAULT 0,

    BallFaced       INT,

    StrikeRate      FLOAT,

    MatchDate       DATE,

    Venue           VARCHAR(100))

 

INSERT INTO STATS (Match_No,Opposition,RunsScored,BallFaced,StrikeRate,MatchDate,Venue)

    VALUES

    (1,'South Africa',18,34,52.94 ,'2019-06-05','Southampton'),

    (2,'Australia'   ,82,77,106.49,'2019-06-09','The Oval'),

    (3,'Pakistan'    ,77,65,118.46,'2019-06-16','Manchester'),

    (4,'Afghanistan' ,67,63,106.34,'2019-06-22','Southampton'),

    (5,'West Indies' ,72,82,87.80 ,'2019-06-27','Manchester'),

    (6,'England'     ,66,76,86.84 ,'2019-06-30','Birmingham'),

    (7,'Bangladesh'  ,26,27,96.29 ,'2019-07-02','Birmingham'),

    (8,'Sri Lanka'   ,34,41,82.93 ,'2019-07-06','Leeds'),

    (9,'New Zealand' ,01,06,16.16 ,'2019-07-09','Manchester')

Here are some key points to understand about SQL Server variables:

1. How to Declare Variable in SQL?: 

Variables in SQL Server are declared using the DECLARE keyword followed by the variable name and its data type. For example, to declare a variable named `@FirstVariable` of type integer, you would use the following syntax:

   DECLARE @FirstVariable INT;

2. Variable Assignment: 

After declaring a variable, you can assign a value to it using the SET keyword. For example, to assign a value of 10 to `@FirstVariable`, you would use the following syntax:

   SET @FirstVariable = 10;

You can also assign values to variables while declaration only, For example:

   DECLARE @Runs INT = 50;

To view varibale content simply type below statement.

   PRINT (@Runs)

    O/P: 

    50  

3. What is bind variable in SQL?: 

Once a variable is declared and assigned a value, you can reference and use it in SQL statements. Variables can be used in SELECT, INSERT, UPDATE, DELETE, and other SQL statements. For example, you can use a variable in a SELECT statement like this:

   SELECT Match_No,Opposition,RunsScored,BallFaced,StrikeRate,MatchDate,Venue

   FROM STATS

   WHERE RunsScored > @Runs;

 O/P:

4. What is scope of variable in SQL?: 

SQL Server variables have a scope that defines their visibility and lifespan. The scope of a variable determines where it can be referenced within a query or procedure. Variables declared within a batch, stored procedure, or function have a limited scope within that specific context.

II. How to modify variables in SQL?

1.Using SET :

In SQL Server, you can modify the values of variables using assignment statements. The most common way to modify a variable is by using the SET keyword followed by the assignment operator (=).

Here's an example of how you can modify the value of a variable:

       DECLARE @Runs INT;

    SET @Runs = 25;

Modifying the variable value

    SET @Runs = @Runs + 5;

In the example above, we declared a variable named `@Runs` and assigned an initial value of 25. Then, we modified the value of `@Runs` by adding 5 to its current value. After executing these statements, `@Runs` would have a value of 30.

You can also modify the value of a variable based on its current value or the result of an expression. Here's an example:

    DECLARE @Runs INT;

    SET @Runs = 25;

Modifying the variable value based on current value

   SET @Runs = @Runs * 2;

Modifying the variable value based on an expression

   SET @Runs = @Runs + (SELECT SUM(RunsScored) FROM STATS);

In the first modification, we doubled the current value of `@Runs` by multiplying it by 2. In the second modification, we added the summation of runs in `VK_Stats` to the current value of `@Runs`. Make sure that the data types match when modifying variables. For example, if you're modifying a variable of type INT, the result of the expression or the value assigned should also be an integer.

Additionally, you can modify variables within control flow statements like IF...ELSE or WHILE loops to conditionally change their values based on specific conditions or iterations.

2. Using  SELECT:

In SQL Server, you can change the value of a variable using the result of a SELECT statement. This is often referred to as assigning a variable value from a query.

Here's an example of how you can change the value of a variable using a SELECT statement:

    DECLARE @City VARCHAR(50);

Note: Here my city can have have maximum length of 50 characters.

Assigning variable value from a Select statement

     SELECT @City = Venue

    FROM STATS

    WHERE Opposition='Pakistan';

 Printing the updated variable value

    PRINT @City;

O/P:

    Manchester

In the example above, we declared a variable named `@City` of type Varchar(50). We then assigned the value of `Venue` from a SELECT statement to `@City` based on a specific condition.

Note that when assigning a variable from a SELECT statement, there are a few important considerations:

1. The SELECT statement should return a single value. If the SELECT statement returns multiple rows or columns, an error will occur.

 2. The data type of the variable should match the data type of the column being selected. For example, if `column` is of type INT, the variable should also be of type INT. 

3. The condition in the WHERE clause determines which row's value will be assigned to the variable. If the condition returns multiple rows, the value from the last row will be assigned. 

After assigning the value from the SELECT statement to the variable, you can use the variable in subsequent SQL statements or print its value using the PRINT statement.

 

III. What is Table Variable?: 

In SQL Server, a table variable is a variable that can hold a set of rows, similar to a temporary table. Table variables are declared and used within the scope of a query, stored procedure, or batch. They provide a way to store and manipulate data in a tabular form without the need to create a physical table in the database.

Here's an example of how to declare and use a table variable in SQL Server:

     DECLARE @TableVariable TABLE

   (ID INT, Opposition VARCHAR(50))  

-- Inserting data into the table variable

    INSERT INTO @TableVariable (ID, Opposition)

    SELECT Match_No, Opposition FROM STATS WHERE RunsScored>50;

Here we are using insert on basis of result of select statement.

-- Querying the table variable

      SELECT ID, Opposition

    FROM @TableVariable;

 O/P:

In the example above, we declared a table variable named `@TableVariable` with two columns: ID of type INT and Name of type VARCHAR(50). We then inserted three rows of data into the table variable using the Select statement.

After populating the table variable, we can query it using a SELECT statement just like we would with a regular table. In this case, we selected the ID and Opposition columns from `@TableVariable`.

 

What are advantages of table variables?:

1. Scope: Table variables have a limited scope within the query, stored procedure, or batch where they are declared. They are automatically dropped and their memory is released at the end of the scope, making them suitable for temporary data storage.

 2. Data Manipulation: You can insert, update, and delete data from a table variable using standard SQL statements, allowing you to manipulate the data within the variable. 

3. Performance: Table variables are stored in memory, making them faster to access compared to temporary tables, which are physically stored in the database.

4. Transactions: Table variables can participate in transactions, just like regular tables. Changes made to a table variable within a transaction can be rolled back if needed.

5. Portability: Table variables are portable across databases since they are not tied to a specific database. They can be easily used in different database environments without the need for physical table creation.

 However, there are also a few considerations to keep in mind:

 1. Size Limitations: Table variables have size limitations based on memory availability. If the data exceeds the memory limit, it may impact performance or cause an error.

 2. Query Optimization: Query performance for table variables might differ from temporary tables or regular tables. Depending on the scenario, the SQL Server query optimizer may make different choices when optimizing queries involving table variables. 

Table variables provide a convenient way to store and manipulate data in a tabular form within the context of a query, stored procedure, or batch. They are useful for temporary data storage, especially when you need to work with a small and defined dataset.

 IV. System Variables in SQL server: 

In SQL Server, system variables,  are predefined variables that provide information about the SQL Server instance or session. Here are some commonly used system variables in SQL Server:

1. @@SERVERNAME: Returns the name of the SQL Server instance.

2. @@VERSION: Returns the version and edition information of the SQL Server instance.

3. @@LANGUAGE: Returns the currently set language for the session.

4. @@ROWCOUNT: Returns the number of rows affected by the last SQL statement.

5. @@IDENTITY: Returns the last identity value generated on the current connection and within the                                   current scope.

6. @@SPID: Returns the session ID (SPID) of the current session.

7. @@CONNECTIONS: Returns the number of active connections on the SQL Server instance.

8. @@MAX_CONNECTIONS: Returns the maximum number of simultaneous connections allowed on                                the SQL Server instance.

9. @@NESTLEVEL: Returns the current level of nesting for stored procedures or triggers.

10. @@ERROR: Returns the error number of the last executed Transact-SQL statement.

It's important to note that the values of system variables are specific to the current session and can change based on the session context or server configuration.

V. Conclusion:

Hope you have enjoyed this blog. Feel free to share your feedback in comments section.

Happy Learning.