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:
|
1 2 3 4 5 |
DECLARE @SON varchar(20) = 'LUKE' SELECT IIF(@SON = 'LUKE', 'I AM YOUR FATHER', 'I AM NOT YOUR FATHER') |
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.
|
1 2 3 |
Select name + '''s birthday was ' + birthdate from person |
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:
|
1 2 3 |
Select name + '''s birthday was ' + CONVERT(VARCHAR(12),birthdate,113) from person |
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.
|
1 2 3 |
SELECT CONCAT(name, '''s birthday was ', birthdate) FROM person |
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.
|
1 2 3 4 5 |
int cnt = 0; cnt += 2; Console.WriteLine("The count is: {0}",cnt); |
But in SQL you had to assign the variable and its increment back to the variable.
|
1 2 3 4 5 6 7 8 9 10 |
DECLARE @cnt int = 0 WHILE @cnt < 10 BEGIN PRINT @cnt SELECT @cnt = @cnt + 1 END GO |
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
|
1 2 3 4 5 6 7 8 9 10 |
DECLARE @cnt int = 0 WHILE @cnt < 10 BEGIN PRINT @cnt SELECT @cnt+=2; END GO |
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:
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE items (item_id int, item_name varchar(10)) INSERT INTO items VALUES (1,'Hat') INSERT INTO items VALUES (2,'Sock') INSERT INTO items VALUES (3,'Shoe') INSERT INTO items VALUES (4,'Coat') INSERT INTO items VALUES (5,'Shirt') |
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:
|
1 2 3 4 5 6 7 8 |
INSERT INTO items VALUES (1,'Hat'), (2,'Sock'), (3,'Shoe'), (4,'Coat'), (5,'Shirt') |
You can also specify a query as a value, but it has to return a scalar value such as this:
|
1 2 3 4 5 6 7 8 |
INSERT INTO items VALUES (1,'Hat'), (2,'Sock'), (3,'Coat'), (4, (select name from product where product_id = 10)) GO |
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:
|
1 2 3 4 5 6 |
USE AdventureWorks2012 GO INSERT INTO items VALUES (1,'Hat') GO |
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:
|
1 2 3 4 5 |
--Insert 1 row 10 times INSERT INTO items VALUES (1,'Hat') GO 10 |
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.
Consider these free tools for SQL Server that improve database developer productivity.
References
Minette currently works as a Data Platform Solution Architect at Microsoft South Africa.
View all posts by Minette Steynberg
- The end is nigh! (For SQL Server 2008 and SQL Server 2008 R2) - April 4, 2018
- 8 things to know about Azure Cosmos DB (formerly DocumentDB) - September 4, 2017
- Introduction to Azure SQL Data Warehouse - August 29, 2017