April 2008 - Posts

Populating sample data

Sometimes you may need some sample data for testing purpose

The following may help you in generating some sample data of different datatypes

 

select 

abs(checksum(newid()))%10000 as intcol,

abs(checksum(newid()))*rand()/100 as float_col,

dateadd(day,0,abs(checksum(newid()))%100000) as date_col,

substring(replace(cast(newid() as varchar(36)),'-',''),1,abs(checksum(newid()))%15) as varchar_col,

abs(checksum(newid()))%2 as bit_col

from

master..spt_values

where

type='p' and

number between 1 and 200

Posted by Madhivanan with no comments

Union Vs Union All

Well. Most of you know the diffrence between the two.

http://blog.sqlauthority.com/2007/03/10/sql-server-union-vs-union-all-which-is-better-for-performance/

http://www.codethinked.com/post/2007/11/UNION-versus-UNION-ALL2c-lessons-in-minutiae.aspx

Here is one of the differences that is not known by newbies

When you use UNION, you cant include any column of type TEXT and NTEXT. But it is possible if you use UNION ALL

Declare @test table(i int, text_col text)

insert into @test

select 1,'some test string'union all

select 2,'some other test string'

 

--Error

select
i,text_col from @test where i=1union

select i,text_col from @test where i=1

--No Error

 

select
i,text_col from @test where i=1union all

select i,text_col from @test where i=1

Posted by Madhivanan with no comments