## 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

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
AMD15433F                                  Ann                  Devon
Chief Financial Officier
F-C16315M                                  Francisco            Chang
Publisher
PXH22250M                                  Paul                 Henriot
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
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

## 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

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/

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

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