October 2007 - Posts

Write Function in SQL Server 2005

One of the functions available in SQL Server 2005 that allows you to update a character column of size max is WRITE Function. In fact we can consider this as alternate to STUFF function which wont work for columns with Max length

 

declare @t table(v nvarchar(max))

insert into @t select 'test is test'

update @t set v=stuff(v,1,4,'This')

select v from @t

 GO

declare @t table(v nvarchar(max))

insert into @t select 'test is test'

update @t set v.write('This',0,4)

select v from @t

PS

You can use STUFF function in both SELECT and UPDATE Statement but WRITE function can be used only in UPDAET statement preceded by Column name.

Posted by Madhivanan with no comments
Filed under: , ,

Behaviour Changes in SQL Server 2005

If you run the following query in both SQL Server 2000 and 2005 , you can see the difference


select random,random,random
from
(
         select rand() as random
) as t

Those behaviour changes are documented here

http://msdn2.microsoft.com/en-us/library/ms143359.aspx

 

Posted by Madhivanan with 3 comment(s)

Generating Random Numbers - Part II

There are many methods to generate random numbers. Here are some of them


select number from

(

select top 10000 abs(checksum(newid())) as number

from sysobjects s1 cross join sysobjects s2

) as T

select number from

(

select top 1000 abs(cast(cast(newid() as varbinary(10)) as int)) as number

from sysobjects s1 cross join sysobjects s2

) as T

 

If you want to generate 4 digits random number, then use

 

select number/power(10,len(number)-4) as number from

(

select top 10000 abs(checksum(newid())) as number

from sysobjects s1 cross join sysobjects s2

) as T

If you want to generate 2 digits random number, then use

select number/power(10,len(number)-2) as number from

(

select top 10000 abs(checksum(newid())) as number

from sysobjects s1 cross join sysobjects s2

) as T

If you use SQL Server 2005, you can also apply the same approach on the following query

 

select row_number() over (order by s1.name) as number

from sysobjects s1 cross join sysobjects s2

 

Posted by Madhivanan with 2 comment(s)

ISNULL() or COALESCE()?


Both the functions are used to show/assign different value if the parameter is null.

select isnull(null,'test'),coalesce(null,'test')

But there are diiferences between the two

isnull() is sql server specific
The datatype/length of the result depends on the parameter

ex

declare @s varchar(1)
Select isnull(@s,'test')

Note that the result is t and not test
The name isnull sounds that it should return 0 or 1 like other functions isdate and isnumeric

But coalesce has advantages over isnull

It is defined by ANSI standard
It will accept more than one parameters
The result doesnt directly depend on the parameter

ex

declare @s varchar(1)
Select coalesce(@s,'test')

and note that the result is test

Although it is proved that isnull is faster than coalesce here (http://sqljunkies.com/WebLog/amachanic/archive/2004/11/30/5311.aspx), for the above reasons I prefer using coalesce

Posted by Madhivanan with no comments
Filed under: , ,