Title

NULL, NULL, NULL and nothing but NULL

Mon, 21 May 2012 00:00:00 GMT

NULL. This is a magical word in Database programming. Here are some interesting facts about NULL in SQL Server.

1 NULL can be defined as absense of value, undefined, or the value which is unknown at this point of time.
2 All datatypes can be defined with NULL constraint
3 Direct usage of artimetic or logical operations on NULL will not work as expected
4 The system functions ISNULL, COALESE and NULLIF are used only to deal with NULL
5 NOT NULL constraint cannot be defined for a computed column until it is also PERSISTED
6 The only datatypes that will interpret NULL differently are rowversion and timestamp

Run this code and see the output

declare @rv rowversion , @ts timestamp 
select @rv=null, @ts=null
select @rv as rowversion,@ts as timestamp

Output is

rowversion         timestamp
------------------ ------------------
0x                 0x

7 NULL=NULL will never be true unless SET ANSI_NULLS is OFF

While the first query returns nothing, the second will return 6

--Query 1
set ansi_nulls on
select 6
where null=null

--Query 2
set ansi_nulls off
select 6
where null=null

The condition WHERE col IS NULL will not be affected by the above setting

8 The default datatype of NULL is INT. Refer Default datatype of NULL for more information
9 Column with UNIQUE constraint will allow only one NULL value in SQL Server (But not true in other RDBMSs)
10 NULL will make SQL Server to use short circuit logic in some cases

Consider this example

select 6/0/null

select null/6/0

While the first query throws an error the second query returns NULL

11 The value NULL is not equal to string value 'NULL'
12 By default NULL values come first when a column is ordered in ascending order and come last when ordered in descending order
13 If you dont use GROUP BY clause, the aggregate functions will always return single value (NULL) when the condition is false

select sum(col) as col from
(
	select 45 as col
) as t
where 1=0

The above returns NULL

14 NULL values are by default omitted in all aggregate functions

Consider the following example

select sum(col) as col_cum,count(col) as col_count,avg(col*1.0) as col_avg from
(
	select 1 as col union all
	select null as col union all
	select 2 as col union all
	select 3 as col 
) as t

The output is

col_cum     col_count   col_avg
----------- ----------- -----------
6           3           2.000000

15 Agrregate functions cannot be directly applied over NULL value

This code

select sum(null) as null_sum

returns the following error

Msg 8117, Level 16, State 1, Line 1
Operand data type NULL is invalid for sum operator.

You can wonder whether NULL is a datatype

Use system functions cleverly

Thu, 17 May 2012 00:00:00 GMT

My Co-worker complained me that AVG function is not properly working in SQL Server whereas it works correctly in mysql. I immediately told him that AVG does the implicit convertion by default and the result may be wrong (based on datatype).

Consider the following set of data

select avg(col) from
(
	select 1 as col union all
	select 2
) as t

The result is

average
-----------
1

As you see, the result is not 1.5 but it is 1 becuase the return type of AVG function is same as that of column it is applied for. The datatype of col is INT and AVG function CASTs 1.5 into 1. To avoid this you need to CAST the datatype into DECIMAL (or multiply column by 1.0)

select avg(col*1.0) as average,avg(cast(col as decimal(12,2))) as average from
(
	select 1 as col union all
	select 2
) as t

The result is

average                                 average
--------------------------------------- ---------------------------------------
1.500000                                1.500000

So you need to aware of this when using system functions whose datatype depends on the datatype of the column

Compare numbers stored as characters seperated by space

Tue, 15 May 2012 00:00:00 GMT

In SQL forums, one of the memebers asked this question.

"In the table a varchar column has values like '2.2020 30 4.0000', '2.20200 30 4.00', etc. When I pass a parameter with the value '2.202 30 4.0' , the above value should be returned as output. How do I do it?"

(Note that 2.2020 and 2.20200 from column value and 2.202 from parameter value are same when compared as Numeric values)

Well. My first advice is not to store multiple values as characters in the single column. Normalization should be applied. If there is no chance to modify the existing structure, there are two ways to compare

1 Split the column value and parameter value by space and compare each number (This whill be most suggested as usual)

2 Do not use any split function. Make some changes in the column and parameter to effectively compare the value.

Ok. We will see how to implement Method 2

Consider the following set of data

declare @t table(col varchar(100))
insert into @t
select	'2.2020 30 4.0000' union all
select	'2.2020 30 4.05000' union all
select	'2.20200 30 4.00200' union all
select	'2.2020 300 4.00' union all
select	'2.202000 30 4.0000' 


declare @s varchar(100),@sql varchar(max)
set @s= '2.202 30 4.0' 
set @sql=''
select 
	@sql=@sql+
		'select '''+col+''' as original_col,sum('+replace(col,' ','+')+') as col1,sum('+replace(@s,' ','+')+') as col2,'''+@s+''' as variable_val union all ' 
from @t
select @sql='select original_col as col_val,variable_val from ('+left(@sql,len(@sql)-11)+') as t where col1=col2 and replace(replace(original_col,''0'','' ''),'' '','''')=replace(replace(variable_val,''0'','' ''),'' '','''')'
exec(@sql)

Now look at the result

col_val            variable_val
------------------ ------------
2.2020 30 4.0000   2.202 30 4.0
2.20200 30 4.00    2.202 30 4.0

If you have any other method please post it in comment's section

Question of the month May 2012 - How does direct usage of column alias work in ORDER BY clause?

Wed, 09 May 2012 00:00:00 GMT

Consider the following two statements

--Query 1

select 
	name, 
	dateadd(day,datediff(day,0,create_date),0) as date_only 
from 
	sys.objects
where 
	date_only>getdate()-100

--Query 2

select 
	name, 
	dateadd(day,datediff(day,0,create_date),0) as date_only 
from 
	sys.objects
order by 
	date_only

While the first query throws an error Invalid column name 'date_only'. ,How does the second query work?

Sum up digits of a number

Fri, 04 May 2012 00:00:00 GMT

My friend told me that in an interview he was asked to write a code that sums the digits of the number. He asked me if there is a way of doing it without using a WHILE loop. Here are two methods

Method 1 : Dynamic SQL

declare @i int, @sql varchar(8000)
set @i=798273
set @sql=''
select 
	@sql=@sql+substring(ltrim(@i),number,1)+'+' 
from 
	master..spt_values 
where 
	type='p' and number between 1 and len(@i)
select @sql='select '+left(@sql,len(@sql)-1)

exec(@sql+' as sum_of_digits')

Method 2 : Static SQL

declare @i int, @n int
set @i=798273
set @n=0
select 
	@n=@n+substring(ltrim(@i),number,1) 
from 
	master..spt_values 
where 
	type='p' and number between 1 and len(@i)

select @n as sum_of_digits

Method 3 : Without using any variables

declare @i int
set @i=798273
select 
	sum(substring(ltrim(@i),number,1)*1)
from 
	master..spt_values 
where 
	type='p' and number between 1 and len(@i)

All the methods would return the following result

sum_of_digits
-------------
36

Here master..spt_values is a system table which is used as a tally number table. You can use your own table too.

Temporary table and dynamic sql

Wed, 25 Apr 2012 00:00:00 GMT

One of my collegues told me that while we cannot use a temporary table created inside dynamic sql out of it, in a similar way it is not possible inside dynamic sql to access a temporary table created out of dynamic sql. How many of you think this is true? I immediately showed him the following code to prove him wrong.

create table #t (i int)
GO
exec ('insert into #t(i) select 100')
GO
select i from #t

The result is 100. As you see it is possible to access the temporary table via dynamic sql in the current session. Ok now the question is "How do I know what objects I can access via dynamic sql?". You can access all the objects that you see from sys.objects. You need to aware of this when using temporary table and dynamic sql.

You may also be interested to read this post Scope of temporary tables across procedures

Computed parameters in Stored Procedure

Fri, 20 Apr 2012 00:00:00 GMT

Well. You are aware of Computed Columns in SQL Server. In a similar way you can also use Computed Parameters. Consider the following procedure.

Create procedure test
(
	@datetime datetime,
	@date date = @datetime
)
as
	select @datetime as date_with_time, @date as date_only

When you execute the procedure you get this result

EXEC '2001-10-18 12:45:22.000'

date_with_time          date_only
----------------------- ----------
2001-10-18 12:45:22.000 2001-10-18

As you see the parameter accepts the value supplied for @datetime and convert it to date datatype. 
When you supply value for @date, it will ignore the value of @datetime, the default value for it

EXEC '2001-10-18 12:45:22.000','2012-01-22 19:15:12.000'

date_with_time          date_only
----------------------- ----------
2001-10-18 12:45:22.000 2012-01-22

However it is not possible to use an expression for the computed paramters 
ie @date date=dateadd(day,10,@datetime) will not work. You should be aware of this when you use paramers
this way.

Removing unwanted characters - Part 3

Mon, 16 Apr 2012 01:03:00 GMT

In this series at Part 1 Removing unwanted characters , I posted a method that used a function. In part 2 I posted a method that used Dynamic SQL.

Here is another improved method that uses Dynamic SQL

--Create test data

create table #data (data varchar(100))

insert #data 
select 'tes^@&t %stri)-n!g' data union all
select '))aaer***********)' union all
select '&^&&hsdf()' union all
select 'jj&wk' union all
select ')hw*pa' union all
select 'No&@#$53*24,Mai()$n R--!oad'

--Create table that has all set of characters to be removed

create table clean_chars (char_id int identity(1,1),chars char(1))

insert into clean_chars (chars)
select '^' as repl union all 
select ')' union all
select '(' union all
select '&' union all
select '*' union all
select '%' union all
select '@' union all
select '-' union all
select ',' union all
select '#' union all
select '$' union all
select '#' union all
select '!' 

 declare @sql varchar(max), @select varchar(max)
 select @sql='',@select=''
 select @select=@select+'replace(', @sql=@sql+','''+chars+''','''')' from clean_chars

 select @select='select data,'+@select+'data'+@sql+' as cleaned_data from #data' 
 exec(@select)

The result is

data                                cleaned_data
--------------------------------------------------------
tes^@&t %stri)-n!g                  test string
))aaer***********)                  aaer
&^&&hsdf()                          hsdf
jj&wk                               jjwk
)hw*pa                              hwpa
No&@#$53*24,Mai()$n R--!oad         No5324Main Road

Question of the month Apr 2012 - Why does SQL Server allow only one NULL value on unique constraint?

Wed, 11 Apr 2012 00:00:00 GMT

Run the following code

	declare @t table(i int unique)
	insert into @t 
	select 1 union all
	select null union all
	select null

You will get the following error

Msg 2627, Level 14, State 1, Line 3
Violation of UNIQUE KEY constraint 'UQ__#A989971__3BD019979D26A3BB'. Cannot insert duplicate key in object 'dbo.@t'. The duplicate key value is ().
The statement has been terminated.

But other RDBMSs like ORACLE, MySQL, etc will allow multiple NULL values on a column defined as Unique key. It is because that NULL values cannot be equal to each other

Why does SQL Server allow only one NULL value on a column that has unique constraint?

Storage of table variable

Mon, 09 Apr 2012 01:27:00 GMT

Did you know that table variables are stored in the tempdb database for execution scope only?. Ok. Let us run this code

declare @t table(i int)
select * from tempdb.INFORMATION_SCHEMA.TABLES

The result is

TABLE_CATALOG       TABLE_SCHEMA   TABLE_NAME       TABLE_TYPE
---------------------------------------------------------------------
tempdb               dbo           #BAB3F665        BASE TABLE

If your server does not use any temporary tables, you will get the above result only. As you see a table variable is stored in the tempdb database for the execution scope only just like a temporary table. The name is prefixed by #. But unlike a temporary table, the name does not contain underscores. Becauase they are stored in execution scope only, you cannot query the INFORMATION_SCHEMA.TABLES alone to get the table variable information. The following code will not show you any table vaiables

select * from tempdb.INFORMATION_SCHEMA.TABLES

How many of you know this?

Exploring SSMS - Display line numbers in Query Window

Fri, 30 Mar 2012 02:06:00 GMT

As you know I have started a new series on "Exploring SSMS" by which I will be posting some features that are available in SSMS which will be very helping during the development. In this post we will see how to display line number in query window.

When you write queries in Query window, the line number is not displayed at default. If you want to display line numbers, you can set the following option.

In the Toolbar, goto Tools-->Options-->Transact-SQL-->General. At the bottom of the window, check the ckeck box labelled Line Numbers and click Ok. You can see your query window with line numbers now.

Other posts that come under Exploring SSMS
Enabling Save Changes
Enable/disable intellisense
Connecting to msdn forums or submitting your feedback to connect
Filtering object names
Changing Top N rows values
Copy results with header
Hiding System databases
Inserting Code snippets for Objects
Open Query Window at Startup

Exploring SSMS - Open Query Window at Startup

Wed, 28 Mar 2012 02:50:00 GMT

When you start SSMS, by default it opens only Object Explorer. You have to open a Query window using a toolbar. If you want to open Query Window along with Object Explorer whenever SSMS is started, you can set the following option.

In the Toolbar, goto Tools-->Options-->Startup. On the rightside window, choose Open Object Explorer and Query window from the Dropdown and click Ok. Now close the SSMS and start it again. You can see that Query window is automatically opened along with Object Explorer

Other posts that come under Exploring SSMS
Enabling Save Changes
Enable/disable intellisense
Connecting to msdn forums or submitting your feedback to connect
Filtering object names
Changing Top N rows values
Copy results with header
Hiding System databases
Exploring SSMS - Inserting Code snippets for Objects

Preserving format of procedure when using sp_helptext

Wed, 21 Mar 2012 01:15:00 GMT

Often peple ask this question in the forum. "I created a procedure with proper alignment. But when I wanted to alter the procedure, result of sp_helptext did not keep the original alignment. How do I keep the original format?"

Well. One answer is to make use of Script Stored Procedure --> ALTER TO option from Management Studio. If you want to use sp_helptext system procedure to view and alter, set the result mode of the query window to Text (Press CTRL+T). The text result mode will keep the original alignment

Consider this simple procedure

create procedure test
as
	select 
		name,create_date
	from 
		sys.objects

Now set the result mode to Text and run this

EXEC sp_helptext test

The result will have the origninal formatting with proper alignment. So you need to aware of this when using sp_helptext system proceudre

Passing parameters in dynamic procedure

Mon, 19 Mar 2012 01:21:00 GMT

In this post Dynamic PIVOT in SQL Server 2005, I have shown a way to dynamically generate the column values with names. I used to get mails frequently from people on how to pass parameters when executing the procedure.

The following will find total sales made by each employee for each year(from Employees and Orders table from Northwind databases)

EXEC dynamic_pivot
'SELECT e.lastname, o.OrderDate FROM northwind..Employees as e
INNER JOIN northwind..Orders as o ON (e.EmployeeID=o.EmployeeID) ',
'Year(OrderDate)',
'Count(OrderDate)'

One user mailed me that the following did not work

DECLARE @year int
SET @year=1984
EXEC dynamic_pivot
'SELECT e.lastname, o.OrderDate FROM northwind..Employees as e
INNER JOIN northwind..Orders as o ON (e.EmployeeID=o.EmployeeID) 
WHERE YEAR(o.OrderDate)=@year',
'Year(OrderDate)',
'Count(OrderDate)'

In the above, @year will not be replaced with it's value. The correct method is to assign it to a variable and use like below

DECLARE @year int, @sql varchar(max)
SET @year=1984
SET @sql='SELECT e.lastname, o.OrderDate FROM northwind..Employees as e
INNER JOIN northwind..Orders as o ON (e.EmployeeID=o.EmployeeID) 
WHERE YEAR(o.OrderDate)='+cast(@year as varchar(4))

EXEC dynamic_pivot
@sql,
'Year(OrderDate)',
'Count(OrderDate)'

So you need to aware of this when passing parameters to dynamic sql

Question of the month March 2012 - Select 999'''45'

Wed, 14 Mar 2012 01:02:00 GMT

Without running this query, can you guess the output?

Query :

select 999'''45'

Output :

Result 1 : Incorrect syntax near '
Result 2 : 999
Result 3 : 999'45
Result 4 : invalid column name 45

Exploring SSMS - Inserting Code snippets for Objects

Wed, 29 Feb 2012 01:28:00 GMT

In the Tool bar, goto Edit-->IntelliSense-->Insert Snippet. You will get an option for Function, table, stored procedure etc. If you want to create a template for table, select table-->Create table.

The following code will be added in the query window

CREATE TABLE dbo.Sample_Table
(
    column_1 int NOT NULL,
    column_2 int NULL
);

If you want to create a code template for Stored procedure with Output parameter, choose Stored Procedure-->Create procedure with OUTPUT parameter. The following code will be added in the query window

CREATE PROCEDURE dbo.Sample_Procedure 
    @param1 int = 0,
    @param2 int OUTPUT 
AS
    SELECT @param2 = @param2 + @param1 
RETURN 0

Similarly you can create code templates for other available options.

Exploring SSMS - Hiding System databases

Mon, 27 Feb 2012 02:23:00 GMT

In the Tool bar, goto Tools-->Options. Then Under Environment click on Startup. On the right of the windows Check the option labelled as Hide system objects in Object Explorer. If you want to bring them back to object explorer, follow the same procedure and uncheck that option

Note: You have to restart the SSMS to see the changes.

Fun with Square braces

Mon, 20 Feb 2012 01:13:00 GMT

Square braces in SQL Server play a major role in T-SQL programming. When an object name contains a space, special character, etc, the only way to express them is to put them aroud squre braces. Consider that you want to create a table user master (with spaces between user and master), you can use [user master].

You can also wrap alias names in square braces

select 1[74]

The result is

74
-----------
1

Ok. Now I want to have [74 as column alias. What should I do? I can simply put one more opening square brace like below

select 1[[74]

which results to

[74
-----------
1

What if I want to have [74] as column alias? Simply use one more closing square brace. Isn't it? Well the following code

select 1[[74]]

throws this error

Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string '[74]'.

Ok. Now can you guess the output of the following statement?

select 1[[74]]],1[[[[[74]]]]]]]]],1[[[[74]]]]]]]

The output is

[74]        [[[[74]]]]  [[[74]]]
----------- ----------- -----------
1           1           1

Why are there more number of closing braces than the opening braces used in the statement to produce the output that has same number of opening and closing braces?

The answer is that the last sqaure brace indicates the end of the square braces wrapping. Other than the last square brace, every double closing braces is replaced as single closing brace in the output alias names. But for opening braces, the output is one less than the total number. The first opening braces indicates the beginning of the wrapping. So you need to be aware of these when using multiple square braces in the column alias.

Scope of permanent tables in tempdb database

Tue, 14 Feb 2012 01:04:00 GMT

As you all know tempdb is responsible of storing all the temporary objects created for a server. One of my co-workers asked me "what is the scope of permanent tables created in the tempdb database?". The answer is it can be accessed via tempdb from various connections just like objects are accessed in other databases. When the server is restarted, the tempdb is recreated and all the objects including permanent tables are dropped.

Let us analyse these with these examples

create the following table

use tempdb
Go
create table test(i int)

You can now access this table from any connection pointing to the server

Use master
GO
select * from tempdb..t

Ok. Next question is "Does this equal to global temporary table?". Yes. But there is a significant difference between a permanent table and global temporary tables created in the tempdb database. Global temporary table can be accessed from any database without qualifying the databasename whereas permanent table created in the tempdb should be accessed by qualifying the tempdb name when accessed via other database.

Create a global temporary table

create table ##test(i int)

You can access this from any database without qualifying the tempdb name as follows

Use master
GO
select * from ##t

But the following will fail

Use master
GO
select * from t

Because the object name is searched in the master database and not in tempdb database. But in the case of temporary tables, as long as SQL Server finds a table name prefixed with #, or ## it directly searches them in the tempdb database no matter which database the connection points to.

So you need to be aware of these points when using permanent table in the tempdb database

Question of the month February 2012 - Find out problamatic query

Wed, 08 Feb 2012 01:03:00 GMT

Without running these queries can you find out the query that would throw an error?

1 select 7*-2&.3
2 select 7|2&-3
3 select 7+-(.2)%(+3)
4 select ((7&2%3))

Identity column with decimal datatype

Mon, 30 Jan 2012 01:14:00 GMT

One of the members in the forum asked about using decimal datatype in identity column. The question was "Is decimal point allowed in the identity column?" Ok. The identity column can never have any decimal points. It is a whole number. The decimal datatype can be used to have a bigger number in the identity column as decimal can have maximum of 38 digits.

The following are the code to create an identity column with decimal datatype

1 Specify scale as 0

declare @t table(i decimal(38,0) identity(1,1))
insert into @t default values
select * from @t

2 Omit scale part

declare @t table(i decimal(38) identity(1,1))
insert into @t default values
select * from @t

Knowing minimum timestamp value for a database

Wed, 18 Jan 2012 04:13:00 GMT

When a datatype timestamp or rowversion is used, it generates unique value whenever a row is added or updated in a table. It is one of the ways to generate auto-generated values not only for insertion but for updation too. If you want to know the minimum active rowvertion number for a database, you can make use of system function MIN_ACTIVE_ROWVERSION()

select  MIN_ACTIVE_ROWVERSION()

The above query returns the minimum rowversion number available for the current database.

Question of the month January 2012 - Datatype of NULL

Wed, 11 Jan 2012 01:25:00 GMT

Without running the following code , can you guess the datatype of the column t?

select null+getdate()+772.67 as t into #t5

@@TRANCOUNT is always incremented in a trigger

Mon, 09 Jan 2012 01:24:00 GMT

The system variable @@TRANCOUNT is used to get the number of transactions that are active. Consider the following code

begin transaction
select 3
select @@TRANCOUNT 
commit transaction

The result of @@TRANCOUNT is 1. Consider the following code.

begin transaction
select 3
begin transaction
select 456
select @@TRANCOUNT 
commit transaction
commit transaction

The result of @@TRANCOUNT is 2.

So whenever there is a transaction the variable @@TRANCOUNT gets incrementedx by 1. In an Auto commit mode if you dont use a transaction, @@TRANCOUNT will never gets incremented. But this variable gets incremented always in a trigger.

This is what BOL specifies

@@TRANCOUNT is incremented by one when entering a trigger, even when in autocommit mode. (The system treats a trigger as an implied nested transaction.)

So you need to be aware of this and use @@TRANCOUNT effectively.

Just Learned posts for the month of December 2011

Mon, 02 Jan 2012 02:28:00 GMT

As I have informed you earlier in this post, there are some interesting tips I have contributed in December 2011. You may find them interesting if you follow them. The following are some of my tips I posted there

Most Learned Posts of all time

You can drop multiple tables using single DROP statement
Google is also a calculator
SSMS Allows you to ask a question in the MSDN forum directly from SSMS
Using Partition Switching you can move millions of rows from one table to another in less than a second
Semicolon can be used to execute a procedure

Just Learned Posts for the month of December 2011

Use Shift+DEL to delete entire line in Query window