I’m going to discuss what variables are in TSQL and how to use them.
First of all, if you have ever programmed, then you already know what a variable is. It’s just anything that can store data for you. Assume you have to process lot of information and you want a way to carry this information one at a time and process it, you can simply use a variable to fetch an information at a time and go back and do it again after processing the first one until you are done processing everything. Simple enough, a variable is a declared container that we can be used to hold data to be executed on.
There are many data types in TSQL and it’s advisable you know the basic ones such as int, bigint, decimal, numeric, varchar, char, binary, varbinary, xml etc.
I’m not going into details describing all these data types, information about all the data types in TSQL can be found either in the msdn or just search for datatype in TSQL.
There are two types of variables in TSQL. System variables and user declared variables. System variables are read only variables that the SQL server use to store some information. The difference in the name is that, system variables have @@ at the beginning of the name and user defined variables have @ at the beginning of the name.
Examples of System variables are @@version—gives you the server version. so if you write the query
SELECT @@version
The output will be the version of the server you are running. There are many system variables and they are very important so l suggest reading the msdn for the meaning and using of these system variables. I’m just going to talk about the user defined variables in this tutorial. So, to use a variable, you first have to declare it. This is the declaration syntax for a variable in TSQL
DECLARE variablename data type.
To put data into the variable, you do it let this
SET variablename = data.
If the data is coming from a resultset such as a selected name from a table, you do it like this
SELECT @variablename = columnname FROM tablename.
So let’s look at some usage and implementation of variables.
DECLARE @a int, @b int, @c int
SET @a = 1
SET @b = 2
SET @C = @a+@b
PRINT @C
When you run this query, it will print 3 in the message tab of the server. Variables can help perform many tasks. When we come to control structures and dynamic tsql, the importance of using variables will become very clear. For now, l hope the basic idea of using variables is clear. Variables store just one value at a time, however table variables can store 2 D data. That is, the variable will have rows and columns and store records in it.If we want to store tabular data in the form of rows and columns into a variable then we use a table varible.
It is able to display 2D data (rows and coluns). it is batch bound just like other variables.
we cannot perform DDL (create, alter, drop) on table variables. Scope of table and user defined variable is batch bound. Advantages of Table variables is that, they can be faster than permanent tables. Table variables need less locking and logging resources .
Disadvantages of table variables is that the Scope of table variable is batch bound, table variables cannot have constraint -table variables cannot have indexes, table variables do not generate statistics and cannot alter once declared. That’s a lot of information to keep in our little head so let’s go ahead and see how to declare and use table variables.
DECLARE @mytable TABLE(id int, name varchar(50))
INSERT INTO @mytable
VALUES (1,’a’),(2,’b’)
SELECT *
FROM @mytable
The fuss about variables being batch bound means that, you have to run the query from the declaration point of the variable to the point where you access and use everything about the variable in other to see its work. For example, in the above query, you have to execute the code from the beginning of the DECLARE statement up to the end of the SELECT statement on @mytable.
The resut from this query will be a tabular data like below
id name
1 a
2 b
After the query ends, everything is lost.
I’ll be giving tutorials on Control structures and then Dynamic tutorials in then next two tutorials. Keep in touch. Peace.
