You think doing code review is boring? Not when you have our secret weapon: the all mighty ‘Tips & tricks’! Then everything becomes a challenge. And if you play nicely, we’ll share with you, in the following minutes, some of the tips and tricks discovered during our MSSQL part of the code review sections.

Did you know..?

The scope of the variables are not the same like in a procedural language like C#?

The following code will not return an error like you may be expected. (If you write the same instructions in C# for example you will receive an error)

if 1=0

begin

declare @r int;

set @r=1;

end

select @r;

declare @r int;

Did you know.. ?

They are scenarios when you can count the number of rows without accessing the table again. One scenario is just when you are inserting rows in a table. The simple method is just to do select @@rowcount(system function that returns the number of rows affected by the last statement. If the number of rows is more than 2 billion, use ROWCOUNT_BIG).  An alternative will be to count the rows in the table: select count(*) from Table.

There is one practice that make this technic not usable:

declare @i int;

–@t is a temporary variable table

select @I=(select Id from @t where id=0);

No matter if one or zero rows were selected this will return 1. Why? Because the last instruction is the attribution of the (select Id from @t where id=0) to @I variable.

Did you know..?

Even coalesce(ANSI standard SQL expression; we are considering the usage of coalesce with 2 parameters) and isnull(MSSQL proprietary specific function) should return the same value they are many difference?

One of the differences is that when they are used with subquery isnull make a better choice in term of performance.

Example:

declare @t table ( id int )

insert into @t select 1 union all select 2 union all select 3

select coalesce((select sum(id) from @t where id%2=0), 0)

select isnull((select sum(id) from @t where id%2=0), 0)

Like you can see below in the coalesce case another table scan was made. For the Isnull function only one time the scan is done.

Did you know.. ?

The default length for variable of type varchar, nvarchar is 1 and when doing a cast or convert is 31.

declare @r varchar;

select @r=’Better a good word than a battle’;

select cast(‘To be, or not to be, that is the question’ as varchar);

Did you know..?

The Case operator and coalesce used the data type precedence to choose the datatype of the result.

The following example will generate an error because not all the case branches are returning the same data type. When they are more than 1 data type in the case branches the result data type is choose by data type precedence. Here int has greater precedence than varchar type:

Example:

Declare @Data VarChar(20)

Set @Data = ”

Select Case When @Data Is NULL Then ”

When @Data = ”    Then ‘Data is empty’

When 0=1           Then 1

End as A

To correct this we just need to replace 1 by ‘1’ in the last when of the case

Did you know..?

Literal numbers are always of data type int. If you need float add the “.” after the literal number

Example:

DECLARE @x int = 3, @y int = 4

,@Rate decimal(18,2);

SET @Rate = 2 * @x / @y;

SELECT 1000 * @Rate;

This will return 1000.00 but if you just add “.” after 2, the result will be consider float and the final result will be 1500.00.

Did you know..?

When you are using the exists operator with subquery it doesn’t matter what condition you are putting in the subquery because it’s not evaluated. You can put also something that will generate an error normally:

Example:

if exists (select 1/0 from sys.objects where name=’Test’)

select 1;

if exists (select DATEFROMPARTS(2015,30,30) from sys.objects where name=’Test’)

select 1;

We’ll be greedy and won’t disclose all our tips & tricks here, we’ll let you discover the rest of them. But we’ve been generous enough to show you the most interesting ones. And even if some of them are already deeply explained on the internet, we had a great time analyzing them together with you.