September 2009 - Posts

Dropping all temp tables

 In SQL Forum one of the posters asked a question about dropping all the temporary tables
There are many ways to do this
One of the ways to to run the following code

declare @sql varchar(8000)
set @sql=''
select @sql = @sql+' drop table '+name from tempdb..sysobjects where xtype='u'
exec(@sql)

Posted by Madhivanan with no comments
Filed under: , ,

Cursor statuses

Here are the queries that findout the cursors based on their statuses

1 Find out the cursors that are allocated but not opened or closed

--Method 1


select
name from sys.dm_exec_cursors(0) where is_open =0

--Method 2

select
        cur
.cursor_name
from 
       
sys.syscursorrefs as ref inner join sys.syscursors as cur on ref.cursor_handl=cur.cursor_handle
where
       
cur.open_status =0

2 Find out the cursors that are opened and not closed

--Method 1

select
name from sys.dm_exec_cursors(0) where is_open =1

--Method 2

select
        cur
.cursor_name
from 
       
sys.syscursorrefs as ref inner join sys.syscursors as cur on ref.cursor_handl=cur.cursor_handle
where
       
cur.open_status =1

3 Find out the cursors that are allocated but not deallocated

--Method 1

select name from sys.dm_exec_cursors(0)

--Method 2

select
        cur
.cursor_name
from 
       
sys.syscursorrefs as ref inner join sys.syscursors as cur on ref.cursor_handl=cur.cursor_handle

Posted by Madhivanan with 1 comment(s)
Filed under: , ,

Generate Fibonacci series - No loop, no recursion

Generating the Fibonnaci series generally involves in writing the code which has while loop or a recursive function

Peso posted the code using the Common table expression

Here are my methods that involve no loop or no recursion

1 Use Quirky update technique

declare @fib table(fib_id bigint,fib_val bigint)

declare @current_val bigint, @previous_val bigint,@new_val bigint

--Set how many rows you want to generate the series

insert into @fib(fib_id,fib_val )

select top 30 row_number() over(order by name),1 from sysobjects

select @current_val =0, @previous_val=-1,@new_val =1

 

update @fib
set
        fib_val
=@current_val,
        @current_val =@new_val +@previous_val,
        @previous_val=@new_val, 
        @new_val
=@current_val

select * from @fib

2 Use Generalised formula

Method 1

select
        ceiling((power(1.61803398874989,number )-POWER(-1/1.61803398874989,number))/SQRT(5)) as fib_number
from 
       
master..spt_values
where 
       
TYPE='p' and number between 0 and 100

Method 2

select 
       
round(((power((1+SQRT(5))/2.0,number )-POWER(-1/(1+SQRT(5))/2.0,number))/SQRT(5)),0) as fib_number
from 
       
master..spt_values
where 
       
TYPE='p' and number between 0 and 100

Method 3 (shortest method)

select 
       
floor(power((1+SQRT(5))/2.0,number )/sqrt(5)+0.5) as fib_number
from 
       
master..spt_values
where 
       
TYPE='p' and number between 0 and 100

Posted by Madhivanan with 1 comment(s)