Minette Steynberg

5 handy Transact-SQL tips you may (or may not) know

September 23, 2016 by

Introduction

Even if you are a SQL veteran, actually especially if you are, you sometimes get really stuck doing something in the old way not realizing that in newer versions of SQL Server some handy functions have been added that can make your life easier.

In this article, I will mention a couple of these which appeared in SQL Server 2008 or later versions or might have even been there before but most people just didn’t know about.

Logical condition

In regular programming languages, such as VB or C#, you have long since been able to use the IIF statement. You are even able to use this in Excel. But in SQL you have always had to use IF ..ELSE or a CASE to perform a logical condition operation.

You can now use this in SQL Server. For example:

The first parameter is the condition, the second parameter is what will be returned if the condition is true and the third parameter is what will be returned if the condition is false.

In this case the result will be as follows:

Concatenation

Ok so many of you may think this is not a big deal, you can already do string concatenation by using this syntax here.

But of course there is a bug here.

To get this to work you will have to perform an explicit conversion of the date such as:

Which will return:

What’s nice about the CONCAT function is that it does the implicit conversion. So you don’t have to convert or cast to a string before adding it.

The CONCAT function can concatenate up to 255 characters. Null is converted to empty string.

Compound Operators

You have been able to use compound operators in other languages forever. Where you can just declare the parameter and increment or decrement by a number in a single command.

But in SQL you had to assign the variable and its increment back to the variable.

But now from SQL Server 2008 onwards you are able to use compound operators to set an original value to the result of the expression

The following compound operators are available:

+= Adds the value to the original value and sets the new value to the result.
-= Subtracts the original value and sets the new value by subtracting original with the new one.
*= Multiplies by an amount and sets the original value to the result.
/= Divides by an amount and sets the original value to the result.
%= Divides by an amount and sets the original value to the modulo.
&= Performs a bitwise AND and sets the original value to the result.
^= Performs a bitwise exclusive OR and sets the original value to the result.
|= Performs a bitwise OR and sets the original value to the result.

Insert multiple rows with one INSERT command

The Table Value Constructor

For ages you had to create one command for each row you wanted to insert unless you were using a SELECT INTO your command typically had to look something like this:

This has now been made somewhat easier, allowing you to not have to repeat the INSERT INTO portion of the command, but only specify the values you’d like to insert by using the Table Value Constructor. Such as this:

You can also specify a query as a value, but it has to return a scalar value such as this:

The table value constructor is limited to 1000 rows.

The Batch Replicator

You also have the option to insert the same row multiple times by using the batch replicator option of the batch separator.

The batch separator is GO by default. The purpose of this command is to separate the code in a script into batches. Such as:

In this example, the USE Adventureworks2012 is the first batch and the INSERT command is the second batch.

So, now if you wanted to add the same row to the table multiple times you can specify a number of times that it should be executed such as:

In this case, the same row will be inserted 10 times. It may not seem very useful, but it is very handy if you have to generate large amounts of data for performance testing etc. Of course, your batch doesn’t need to be as simple as this example, you can modify it to insert data with different data types and derived values to create good quality sample data.

It’s worth noting that the batch separate is in actual fact NOT transact-SQL, it is a command which gets interpreted by sqlcmd, osql or SQL Server Management Studio.

When SQL Server Management Studio encounters a GO, it knows that it needs to send the preceding batch of code to the SQL Server instance. Using the replicator, simply tells the utility to send the batch multiple times.

Template explorer

SQL Server ships with a whole bunch of templates for common commands, which allows you to get the correct syntax to perform a certain task without having to google it. This is built into SQL Server Management Studio and can be viewed in the template explorer.

You can access the Template Explorer from the View Menu:

Which will open the template browser window, which displays all the available templates

It also allows you to create your own templates, which is really handy if you have scripts that you use frequently. You can create your own templates by right clicking on the SQL Server Templates Node in the Template Browser and selecting the new option. You can then create a new folder or a new template.

Conclusion

SQL Server has a myriad of tools and functions that most people are not aware of. These are just a couple of things I came across, but there are sure to be tons more. It is definitely worthwhile to sometimes just poke around and see what pops up that you haven’t seen before, especially for newer versions as SQL Server and the Transact-SQL language is ever improving.

References


Minette Steynberg

Minette Steynberg

Minette Steynberg has over 15 years’ experience in working with data in different IT roles including SQL developer and SQL Server DBA to name but a few. Minette enjoys being an active member of the SQL Server community by writing articles and the occasional talk at SQL user groups.

Minette currently works as a Data Platform Solution Architect at Microsoft South Africa.

View all posts by Minette Steynberg
Minette Steynberg
SQL Database development

About Minette Steynberg

Minette Steynberg has over 15 years’ experience in working with data in different IT roles including SQL developer and SQL Server DBA to name but a few. Minette enjoys being an active member of the SQL Server community by writing articles and the occasional talk at SQL user groups. Minette currently works as a Data Platform Solution Architect at Microsoft South Africa. View all posts by Minette Steynberg

4,407 Views