Five SQL Tips That You Must Know

If you are a veteran in SQL Server, there are 5 top SQL tips, which you may (or may not) know. Even if you are stuck somewhere, and follow the old approach ,there are some new features which have been added in SQL Server with the syntax. This can make your life easier.

Note - These features are available in SQL Server 2008 or higher versions.

Logical Condition

In our regular programming language, such as C# or any other programming language, many of us use IF...ELSE statement. In SQL, you have always have to use IF...ELSE or a CASE to perform a logical condition operation.

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

DECLARE @SON varchar(20)='AKSHAY'
SELECTIIF(@SON ='AKSHAY','I AM EMPLOYEE','I AM PROFESSOR')

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.



Concatenation

Here, many people use concatenation before the syntax, which is as follows,

Now, we will get the list of all Employees with DOB field, as given below-


To get this to work, you will have to perform an explicit conversion of the date, given below-
CONCAT function does the implicit conversion. Thus, you don’t have to convert or cast to a string before adding it.



Compound Operators

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

intcnt = 0;
cnt += 2;
Console.WriteLine("The count is: {0}",cnt);

In SQL, you had to assign the variable and its increment back to the variable.

DECLARE @cntint = 0

WHILE @cnt < 10
BEGIN
PRINT @cnt
SELECT @cnt = @cnt + 1
END
GO

From SQL Server 2008 onwards, you are able to use compound operators to set an original value to the result of the expression, as given below-

DECLARE @cntint = 0

WHILE @cnt < 10
BEGIN
PRINT @cnt
SELECT @cnt += 2;
END
GO

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. It looks like, as shown below-

CREATE TABLE items (item_idint, item_namevarchar(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 to repeat the INSERT INTO portion of the command, but only specify the values. If you like to insert, using the table value constructor, as shown below-

INSERT INTO items VALUES(1, 'Hat'),
    (2, 'Sock'),
    (3, 'Shoe'),
    (4, 'Coat'),
    (5, 'Shirt')

The Batch Replicator

You also have the option to insert the same row multiple times, 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 the batches such as-

INSERT INTO items VALUES (1,'Hat')
GO 10

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

It’s worth noting that the batch separation 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, it 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 Template Explorer.

You can access Template Explorer from the View menu,

 

It 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 SQL Server Templates Node in the Template Browser and selecting the new option. You can then create a new folder or a new template.