Основные принципы программирования на t-sql

Syntax

The following syntax describes how to declare a table variable:

If we want to declare a table variable, we have to start the DECLARE statement which is similar to local variables.
The name of the local variable must start with at(@) sign. The TABLE keyword specifies that this variable is a table variable. After the TABLE keyword, we have to define column names and datatypes of the table variable in SQL Server.

In the following example, we will declare a table variable and insert the days of the week and their abbreviations
to the table variable:

At the same time, we can update and delete the data contained in the table variables. The following query delete and
update rows:

Transactions and table variable in SQL Server

Transactions are the smallest logical unit that helps to manage the CRUD (insert, select, update and delete)
operations in the SQL Server. Explicit transactions are started with BEGIN TRAN statement and they can be completed
with COMMIT or ROLLBACK statements. Now we will execute the following query and then analyze the result:

Table variable CRUD operations do not manage by explicit transactions. As a result, ROLLBACK TRAN cannot erase the
modified data for the table variables.

Conclusions

In any shared playground, be very careful how you swing that bat. You’ll have realized, whilst reading this, that a lot of activity goes on in TempDB, and you can cause havoc to the whole SQL Server by using long-running processes that fill temporary tables, whatever type they are, with unnecessary quantities of data. In fact, I’ve given you clues in this article how to really, really, upset your DBA by inconsiderate use of that precious shared resource, the TempDB. (In the old days before S2005, using with a huge table was the great V-weapon (Vergeltungswaffe)

I’m always wary of providing over-generalized advice, but I always prefer my databases to use Table Variables, and TVPs wherever possible,  They require less resource, and you’re less likely to hold onto them when you’re finished with them.  I like to use them to the max, with column and table checks and constraints. You may find times when they run out of steam, especially when table sizes get larger. In cases like this, or where it isn’t practical to use table variables because of their restricted scope, then I’ll use local temporary tables. It takes a lot of pursed lips and shaking of heads before I’ll agree to a global temporary table or persistent temporary table. They have a few valid and perfectly reasonable uses, but they place reliance on the programmer to do the necessary housekeeping

Always bear in mind that misuse of temporary tables, such as unnecessarily large, or too long-lived,  can have effects on other processes, even on other databases on the server. You are, after all, using a shared resource, and you wouldn’t treat your bathroom  that way would you?

Note: This article was updated 28 August 2020.

Some useful tips for the table variables

TRUNCATE statement does not work for table variables

The TRUNCATE statement helps to delete all rows in the tables very quickly. However, this statement cannot be used
for table variables. For example, the following query will return an error:

The table variable structure cannot be changed after it has been declared

According to this tip interpretation, the following query has to return an error:

The table variable in SQL Server should use an alias with the join statements

If we want to join two or more table variables with each other or regular tables, we have to use an alias for the
table names. The usage of this looks like this:

The table variable does not allow to create an explicit index

Indexes help to improve the performance of the queries but the CREATE INDEX statement cannot be used to create an
index for the table variables. For example, the following query will return an error:

However, we can overcome this issue with the help of the implicit index definitions because the PRIMARY KEY
constraint or UNIQUE constraints definitions automatically create an index and we can use these INDEX statements in
order to create single or composite non-clustered indexes. When we execute the following query, we can figure out
the created index which belongs to @TestTable:

User Tables in TempDB

In normal use, you will create temporary tables, or table variables without thinking too deeply about it. However, it is interesting, though, that TempDB is there for any sort of sandbox activity. You can create ordinary base tables, views, or anything else you want. You can create schemas, stored procedures and so on. You’re unlikely to want to do this, but it is certainly possible since TempDB is just another database. I’ve just had to restart my development SQL Server after proving this to myself by installing AdventureWorks onto it. This means that it is possible to create a base table in TempDB, a sort of ..er… temporary permanent table. Unlike the global temporary table, you’d have to do all your own housekeeping on it: you’re on your own. The same is true of routines. The advantage of doing this is that any processing that you do uses TempDB’s simple recovery so that, if you fail to mop up, SQL Server acts as mother on the next startup: though this could be a very long time. The next stage is to have what I call a ‘persistent temporary’ table. In this table, the data itself is volatile when the server restarts, but the table itself persists. Probably the most common way to create a persistent Temporary table is to recreate on startup a global temporary table. This can be done automatically when all databases are recovered and the “Recovery is completed” message is logged. Even though this is a ‘global temporary’, it isn’t deleted when all connections using it have disappeared, because the process that runs it never disappears. Arguably, it is better to create this kind of  work table in the database that uses it, though, if you are using full recovery, the temporary work will remain in the log. You can, of course, just create an ordinary table in TempDB. You can create these ‘persistent’ tables on startup by defining a stored procedure in master that creates the global temporary table

Why use this sort of hybrid table? There are, for example, a number of techniques for passing tables between procedures via ‘persistent’ tables in a multiprocess-safe way, so as to do a series of processing to the data. These are referred to a Process-keyed tables (see ‘ by  Erland Sommarskog). They will initially raise the eyebrows of any seasoned DBA but they are an effective and safe solution to a perennial problem, when they are done properly.

As well as temporary tables, there are also a number of  table types that aren’t directly derived from base tables, such as ‘fake’ tables and derived tables: some of these are so fleeting that they are best thought of as ephemeral rather than temporary. The CTE uses ephemeral tables that are ‘inline’ or ‘derived’ and aren’t materialised. BOL refers to them as ‘temporary named result sets’. They exist only within the scope of the expression. In a CTE, they have the advantage over derived tables in that they can be accessed more than once.

How can we use constraints with the table variables?

Constraints are database objects that ensure data integrity. Table variables allow us to create the following
constraints:

• Primary Key
• Unique
• Null
• Check

In the following example, we will successfully use all types of constraints on the table variable seamlessly:

On the other hand, Foreign Key constraints cannot use for the table variables. The other restriction is, we have to
define the constraints when we are declaring the table variable otherwise, we experience an error. For example, the following query will return an error because of this restriction. We cannot alter the table structure after the
declaration of the table variable:

Local Temporary Table

With Local temporary table (names that begin with #), what goes on under the hood is surprisingly similar to table variables. As with Table Variables, Local Temporary tables are private to the process that created it. They cannot therefore be used in views and you cannot associate triggers with them.

They are handier than table variables if you like using to create them, but I’m slightly wary about using in a system that is likely to require modification, I’d much rather create my temporary tables explicitly, along with all the constraints that are needed.

 You cannot easily tell which session or procedure has created these tables. This is because, if the same stored procedure is executed simultaneously by several processes, the Database Engine needs to be able to distinguish the same tables created by the different processes. The Database Engine does this by internally appending a left-padded numeric suffix to each local temporary table name. The full name of a temporary table as stored in the sys.objects view in TempDB is made up of the table name specified in the statement and the system-generated numeric suffix. To allow for the suffix, the table name specified for a local temporary name must be less than 116 characters.

You get housekeeping with Local Temporary tables; they are automatically dropped when they go out of scope, unless explicitly dropped by using . Their scope is more generous than a table Variable so you don’t have problems referencing them within batches or in dynamic SQL. Local temporary tables are dropped automatically at the end of the current session or procedure. Dropping it at the end of the procedure that created it can cause head-scratching: a local temporary table that is created within a stored procedure or session is dropped when it is finished so it cannot be referenced by the process that called the stored procedure that created the table. It can, however, be referenced by any nested stored procedures executed by the stored procedure that created the table. If the nested procedure references a temporary table and two temporary tables with the same name exist at that time, which table is the query is resolved against?

As a curiosity, you can also create Local Temporary Stored Procedures with the same scope and lifetime as a local temporary table. You can’t do the same for other routines. 

Global Temporary Tables.

Like Local temporary tables, Global temporary tables (they begin with ##) are automatically dropped when the session that created the table ends: However, because global tables aren’t private to the process that created it, they must persist thereafter until the last Transact-SQL statement that was actively referencing the table at the time when the creating session ended has finished executing and the locks are dropped. Anyone who has access to TempDB at the time these Global Temporary tables exist can directly query, modify or drop these temporary objects.

You can associate rules, defaults, and indexes with temporary tables, but you cannot create views on temporary tables or associate triggers with them. You can use a user-defined datatype when creating a temporary table only if the datatype exists in TempDB

Stored procedures can reference temporary tables that are created during the current session. Within a stored procedure, you cannot create a temporary table, drop it, and then create a new temporary table with the same name.

Although this works….

…this doesn’t

You can, by using local temporary tables, unintentionally  force recompilation on the stored procedure every time it is used. This isn’t good because the stored procedure is unlikely to perform well. To avoid recompilation, avoid referring to a temporary table created in a calling or called stored procedure: If you can’t do so, then put the reference in a string that is then executed using the statement or stored procedure. Also, make sure that the temporary table is created in the stored procedure or trigger before it is referenced and dropped after these references. Don’t create a temporary table within a control-of-flow statement such as or .

You are allowed to create Global Temporary Stored Procedures, but I’ve yet to find a use for them. Global temporary functions aren’t permitted.

Table-Valued Parameters

The Table-Valued Parameter (TVP) is a special type of  table variable that extends its use. When table variables are passed as parameters, the table is materialized in the TempDB system database as a table variable and passed by reference,  a pointer to the table in the TempDB.

Table-valued parameters have been  used since SQL Server 2008 to send several rows of data to a Transact-SQL routine or to a batch via .. Their particular value to the programmer is that they can be used  within TSQL code as well as in the client application, so they are good for sending client tables to the server.  From TSQL, you can declare table-valued variables, insert data into them, and  pass these variables as table-valued parameters to stored procedures and functions.Their more general usefulness is limited by the fact that they are only passed as  read-only. You can’t do , , or statements on a table-valued parameter in the body of a routine.

You need to create a User-Defined Table Type and define a table structure to use them. Here is a simple example of their use in TSQL

As with Table Variables, the table-valued parameter ceases to exist once it is out of scope but the type definition remains until it is explicitly  dropped. Like Table Variables they do not acquire locks when the data is being populated from a client, and  statistics aren’t maintained  on columns of table-valued parameters. You cannot use a table-valued parameter as target of a or statement. As you’d expect, a table-valued parameter can be in the clause of or in the string or stored-procedure.

The TVP solves the common problem of wanting to pass a local variable to dynamic SQL that is then executed  by a . It is poorly documented by Microsoft, so I’ll show you a worked example to get you started

Before we move on to describe the more traditional  temporary tables and their use, we’ll need to delve into the place where temporary tables are held. TempDB.

What is the storage location of the table variables?

The answer to this question is – table variables are stored in the tempdb database. Why we underline this is because
sometimes the answer to this question is that the table variable is stored in the memory, but this is totally wrong.
Before proving the answer to this question, we should clarify one issue about the table variables. The lifecycle of
the table variables starts in the declaration point and ends at the end of the batch. As a result, the table
variable in SQL Server is automatically dropped at the end of the batch:

As you can see, the previous query returns two result sets. The ResultSet-1 contains column names and data types of the declared table variable and the ResultSet-2 does not contain any data. The reason for this case is, the first
INFORMATION_SCHEMA.COLUMNS view, and table variable executed in the same batch so we can get the information of the
@ExperiementTable table variable from the tempdb database. The second query could not return any data about the
@ExperiementTable because the GO statement ends the batch so the life-cycle of the @ExperiementTable table variable is terminated. In this section, we proved the storage location of the table variable in SQL Server.