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)

How to Generate Script along with Data?

One of the new features available in SQL Server 2008 is scripting the tables along with data (in the form of INSERT statements). Here are the useful links that can help you generating script with data

SQL Server 2008
http://www.kodyaz.com/articles/sql-server-script-data-with-generate-script-wizard.aspx

SQL Server 2000/2005
 http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

 

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

How to display detailed data below each header data?

One of my friends asked me if it possible to generate Detailed data for each Header data without using a Cursor or while loop. (The output is something like the one usually seen via reporting tool). He wanted the result to be output to text file

Consider the jobs and employee tables from pubs database

select job_id, job_desc from jobs

select job_id,emp_id,fname,lname from employee

The task is to show employee details under each job description

The required output is

New Hire - Job not specified                                           
Chief Executive Officer                                                
        PTC11962M                                  Philip               Cramer
Business Operations Manager                                            
        AMD15433F                                  Ann                  Devon
Chief Financial Officier                                               
        F-C16315M                                  Francisco            Chang
Publisher                                                              
        PXH22250M                                  Paul                 Henriot
        CFH28514M                                  Carlos               Hernadez
        JYL26161F                                  Janine               Labrune
        LAL21447M                                  Laurence             Lebihan
        RBM23061F                                  Rita                 Muller
        SKO22412M                                  Sven                 Ottlieb
        MJP25939M                                  Maria                Pontes
Managing Editor                                                        
        VPA30890F                                  Victoria             Ashworth
        MGK44605M                                  Matti                Karttunen
        DWR65030M                                  Diego                Roel
        A-R89858F                                  Annette              Roulet
Marketing Manager                                                      
        L-B31947F                                  Lesley               Brown
        PDI47470M                                  Palle                Ibsen
        HAN90777M                                  Helvetius            Nagy
        M-L67958F                                  Maria                Larsson
Public Relations Manager                                               
        ARD36773F                                  Anabela              Domingues
        MMS49649F                                  Mary                 Saveley
        PSP68661F                                  Paula                Parente
        M-P91209M                                  Manuel               Pereira
Acquisitions Manager                                                   
        KJJ92907F                                  Karla                Jablonski
        M-R38834F                                  Martine              Rance
        MAS70474F                                  Margaret             Smith
        GHT50241M                                  Gary                 Thomas
Productions Manager                                                    
        A-C71970F                                  Aria                 Cruz
        PHF38899M                                  Peter                Franken
        POK93028M                                  Pirkko               Koskitalo
        MFS52347M                                  Martin               Sommer
Operations Manager                                                     
        PCM98509F                                  Patricia             McKenna
        R-M53550M                                  Roland               Mendel
        DBT39435M                                  Daniel               Tonini
        MAP77183M                                  Miguel               Paolino
Editor                                                                 
        H-B39728F                                  Helen                Bennett
        Y-L77953M                                  Yoshi                Latimer
        HAS54740M                                  Howard               Snyder
Sales Representative                                                   
        PMA42628M                                  Paolo                Accorti
        TPO55093M                                  Timothy              O'Rourke
        CGS88322F                                  Carine               Schmitt
Designer                                                               
        PSA89086M                                  Pedro                Afonso
        ENL44273F                                  Elizabeth            Lincoln
        KFJ64308F                                  Karin                Josephs

Here is the query that does it

select job_desc,first_name,last_name from
(
    select job_id, job_desc,'' as first_name,''as last_name,1 as sorder from jobs 
    union all
    select job_id,'        '+emp_id,fname,lname ,2 as sorder from employee
) as t
order by job_id,sorder

Note that the above query should be used only if you have no option to use any front end application. Otherwise it should be done in front end application

Posted by Madhivanan with 1 comment(s)

Regular expression

One of the posters asked a question in the forums about finding a number in an expression consists of alphabets and number if a particular digit is provided

Consider the following set of data

this is test place located at no 123456 test
foo 35234
bar 126831
72348707 foo
foo 987654 bar


If the digit provided is 7, then the numbers 72348707 and 987654 should be extracted from the data

One of the methods is using a regular expression

declare @sample table
(
        data varchar(200)
)

insert @sample

select 'this is test place located at no 123456 test' union all
select 'foo 35234' union all
select 'bar 126831' union all
select '72348707 foo ' union all
select
'foo 987654 bar'

declare @digits int

set @digits = 7

 

select
    data
,@digits as expression,
    right(substring(' '+data+' ', 1,pos),charindex(' ',reverse(substring(' '+data+' ', 1,pos))))+
    substring
    (
        substring(' '+data+' ', pos+1,len(' '+data+' ')),1,charindex(' ',substring(' '+data+' ', pos+1,len(' '+data+' ')))
    ) 
    as number
from
(
    select 
        data
,
        patindex('%' + cast(@digits as varchar(10)) + '%', data) as pos 
    from 
        @sample
    where 
        data
like '%'+CAST(@digits as varchar(10))+'%'
) as d

Command Prompt shortcuts to open SSMS

Here are the Command Prompt shortcuts to open Enterprise Manager/Management Studio

SQL Server 2000
isqlw

SQL Server 2005
sqlwb

SQL Server 2008
ssms

Posted by Madhivanan with no comments

CTE in a View

It is possible to use Common Table Expression in a View. But newbies wonder why the following is not possible

create view numbers
as
with
numbers(n) as
(
select 1 as n union all select n+1 from numbers where n<10000
)

select n from numbers option(maxrecursion 0)

Which when created results to the error

Msg 156, Level 15, State 1, Procedure numbers, Line 15
Incorrect syntax near the keyword 'option'.

The correct way of doing it is to create a view without option(maxrecursion 0) and use it when querying a view

create view numbers
as
with numbers(n) as
(
select 1 as n union all select n+1 from numbers where n<10000
)

select n from numbers

GO

select * from numbers option (maxrecursion 0)

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

Export to Excel - data length exceeds 255 characters

 

If you use Data Transaformation Services to send data to EXCEL, you may get error about the size of the cell if length of data exceeds 255 characters. This KB from microsoft explains how to solve it

http://support.microsoft.com/default.aspx?scid=kb;en-us;281517

Posted by Madhivanan with no comments
Filed under: , ,

Quirky update in sql server

 

When
you use variables in update statement and update the columns with the values of variables which keep on changing for each row, you can simulate many things that are done using Loop or a Cursor

 

declare @temp table
(
product_name varchar(100),
Value int,
sequence int
)

insert into @temp values('product1' , 25 , 0)
insert into @temp values('product2' , 25 , 0)
insert into @temp values('product3' , 100 , 0)
insert into @temp values('product4' , 100 , 0)
insert into @temp values('product5' , 100 , 0)
insert into @temp values('product6' , 25 , 0)
insert into @temp values('product7' , 25 , 0)
insert into @temp values('product8' , 26 , 0)

declare @value int,@sequence int, @runningtotal int,@product_name varchar(100)

--Serial No for all values

set @sequence=0

update @temp
set @sequence=sequence =@sequence+1
select * from @temp

--Serial No for each value

set @sequence=0

update @temp
set sequence=@sequence ,
@sequence=case when @value=value then @sequence else @sequence+1 end,@value=value

select * from @temp

--Reset Serial No in each value

set @sequence=0

update @temp
set sequence=@sequence ,
@sequence=case when @value=value then @sequence+1 else 1 end,@value=value

select * from @temp

--Running total

set @sequence=0

update @temp
set @sequence=sequence =@sequence+value
select * from @temp

--Generate data in csv format

GO

 

declare @value int,@sequence varchar(100), @product_name varchar(100)

declare @temp table
(
product_name varchar(100),
Value int,
sequence varchar(100)
)

insert into @temp values('product1' , 25 , 0)
insert into @temp values('product2' , 25 , 0)
insert into @temp values('product3' , 100 , 0)
insert into @temp values('product4' , 100 , 0)
insert into @temp values('product5' , 100 , 0)
insert into @temp values('product6' , 26 , 0)

 

set @product_name=''

update @temp
set sequence=@product_name ,
@product_name=case when @value=value then @product_name+','+product_name else product_name end,@value=value

select value,max(sequence) as sequence from @temp
group by value

The term "Quirky update" is referred in these articles
http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-cursor-workbench/
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

 

Posted by Madhivanan with 2 comment(s)

Removing unwanted characters

 

Sometimes
when you import data from other system, you may want to clean up data by removing unwanted characters

You can use Replace function. Suppose you want to remove the character ^ from the string

declare @str varchar(100)
set @str='test^ string'
select @str=replace(@str,'^','')

Select @str

But what if you have lot of characters to be removed?
You can have a seperate
table that has the set of characters to be removed and use a function

--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
'!'

 

--Create a function that removes all those characters from data

create function clean_data
(
@data varchar(100)
)
returns varchar(1000)

as

begin
        declare @char_id varchar(1000),@clean_data varchar(1000)
        set @clean_data=@data
        select @char_id=min(char_id) from clean_chars

        while @char_id is not null
        begin
                select @clean_data=replace(@clean_data,chars,'') from clean_chars where char_id=@char_id
                select @char_id= min(char_id) from clean_chars where char_id>@char_id
        end

return @clean_data

end

--Run the query

select data,dbo.clean_data(d.data) as clean_data from #data as d

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

How to find size of backup files in a folder?

I was asked to findout total size of all the backup files in the folder
As I didnt have access to view the folder of the server, I used this.
I hope it may be helpful

create table #t(files varchar(1000))

Insert into #t
EXEC master..xp_cmdshell 'dir e:\backup'

select
       
substring(size_files,charindex(' ',size_files)+1 ,len(size_files))as backup_file,
       
cast(substring(size_files,1,charindex(' ',size_files)-1) as money)/1024/1024 as file_size_MB
from
(
        select files,ltrim(substring(files,patindex('%[ AP ]M%',files)+2,len(files))) as size_files 
        from #t where files like '%bak'
) as t

drop table #t

Posted by Madhivanan with no comments
More Posts « Previous page - Next page »