Category Archives: T-SQL
Understanding the numerous types of variable SQL Server has to offer, and their appropriate usage is one of the cornerstones of developing effective database code. I recently helped a co-worker fix an error message in his code.
-- Error Msg 137, Level 15, State 2, Line 3 Must declare the scalar variable "@l_INT".
The code snippet looked something like this, which led to a discussion about scope of T-SQL variables and an interesting way to fix this problem.
CREATE TABLE #scope_test1 (col1 INT); CREATE TABLE #scope_test2 (col1 INT); GO DECLARE @l_INT INT = 42; INSERT INTO #scope_test1 (col1) SELECT @l_INT; GO INSERT INTO #scope_test2 (col1) SELECT @l_INT; GO
T-SQL language supports local variables (their names begin with a single @). The scope of a variable is the range of Transact-SQL statements that can reference the variable. The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared. A T-SQL batch is a group of statements that SQL Server parses as a single unit. A batch can be delimited by the BEGIN..END statements, or a client tool like SQL Server Management Studio (SSMS) can specify the end of a batch with a GO command (you can set any word to be a batch separator in SSMS, but we will leave that discussion for another time). The names of some Transact-SQL system functions begin with two at signs (@@). They are commonly referred to as Global variables. Global variable names begin with a @@ prefix. You do not need to declare them, since the server constantly maintains them. They are system-defined functions not variables and do not have the same behaviors as variables. All the global variables represent information specific to the server or a current user sessions. Some of the commonly used ones are @@ERROR, @@IDENTITY, @@VERSION. Traditionally, DECLARE command is used to declare a local variable and SET or SELECT used to initialize its value. With SQL Server 2012, both of these tasks can be accomplished in a single statement ;
DECLARE @MyCounter INT = 12 ;
The variables we discussed so far are scalar variables, which can hold a single data value of a specific type. A table variable is a special data type that can be used to store a result set for processing at a later time. table is primarily used for temporary storage of a set of rows returned as the result set of a table-valued function. Functions and variables can be declared to be of type table. table variables can be used in functions, stored procedures, and batches. A DECLARE statement (similar to local scalar variables) is used to DECLARE a table variable. While they behave exactly like local variables, with a well defined scope,they can be thought of as being similar to temporary table, but with several limitations.
The SQLCMD utility lets you enter Transact-SQL statements, system procedures, and script files at the command prompt, in Query Editor in SQLCMD mode, in a Windows script file or in an operating system (Cmd.exe) job step of a SQL Server Agent job. This utility uses ODBC to execute Transact-SQL batches. Scripting variables can be used in SQLCMD scripts. Scripting variables enable one script to be used in multiple scenarios. The setvar command is used to define scripting variables. Variables that are defined by using the setvar command are stored internally. Scripting variables should not be confused with environment variables that are defined at the command prompt by using SET.
The scope of a SQLCMD scripting variable can span several batches, which can be used to implement variables that don’t go out of scope even when a batch ends. The example below is a simplified demo of the same ;
:setvar l_int "42" CREATE TABLE #scope_test1 (col1 INT); CREATE TABLE #scope_test2 (col1 INT); GO INSERT INTO #scope_test1 (col1) SELECT $(l_int); GO INSERT INTO #scope_test2 (col1) SELECT $(l_int); GO -- Results (1 row(s) affected) (1 row(s) affected)
A review of the types of variables the T-SQL language has to offer, has helped refresh my understanding of their appropriate usage.
- Table Variables – http://msdn.microsoft.com/en-us/library/ms175010.aspx
- T-SQL Variables – http://msdn.microsoft.com/en-us/library/ms187953(v=sql.105).aspx
- Global Variables – http://www.codeproject.com/Articles/39131/Global-Variables-in-SQL-Server
- Scripting variables with SQLCMD – http://msdn.microsoft.com/en-us/library/ms188714.aspx
- SQLCMD utility – http://msdn.microsoft.com/en-us/library/ms162773.aspx
I recently had the privilege of learning Advanced T-SQL with Itzik Ben-Gan (Blog|Twitter), a mentor and one of the founders of SolidQ. This 2 day Advanced T-SQL course spanned across a range of interesting topics that a Database Developer would find extremely valuable on a day to day basis ! Not only does Itzik deliver a remarkable lecture, but also encourages questions and discussions, which adds a great deal of value to the learning experience. Some of the topics we covered in detail are;
- SQL Server Internals & Index Tuning – This was my very first deep dive into SQL Server Internals pertinent to Indexes. The multitude of Index access methods that the Optimizer can choose from, and their impact on Query performance has opened a whole new world of indexing strategies for me. Filtered Indexes and Columnstore Indexes some of the newer features I am looking forward to try out.
- Temporary Tables – Never before has the distinction between Temporary tables, Table variables and Common Table Expressions (CTEs) been presented with so much clarity and lucidity. I now have a solid understanding of which to use in any given scenario.
- Sets vs Cursors – Again, a classic topic. Never hurts to reinforce the advantages of a Set based solution versus cursors.
- Query Tuning has never been so interesting with a real life example of dramatic performance gains between various revisions of the same query.
- Apply – I cannot believe I had used the apply operator only for XML Queries so for. I have now learned its uses for unpivoting, aggregating and reusing column aliases.
- Grouping Sets – I had learned this one while studying for my MCTS certification, but had never got around to making much use of it. This gave me the chance to understand its full potential and applicability.
- Sequences – I had done some research on this topic before to my blog post about the same, but I found out soon that there is still so much more to it, especially when comparing it with Identity.
- The enhancements to Window Functions and the world of Partitioning, Ordering and Framing offers a whole new paradigm for OLAP Queries used in Reporting.
Over the next few weeks, I intend to take the time to absorb all the new concepts and ideas I learned in this 2 day course, and find areas to implement these ideas.