Generate Sequence numbers on the fly

/* Generate sequence nos on the fly without using any tables
sql 2005 you can select from cte into #temp table and
use this result set
OR
you can use this with other tables in cross join or join .
100 is kept as the limit , since max recursion cannot exceed this value .
you can expand on this idea to generate higher value limit .
*/ -- upto 100 numbers

with cte (nos) as
( select 1 as nos union all select cte.nos + 1
from cte where nos < 100 )
select nos from cte order by nos -- upto 200 numbers
with cte (nos) as
( select 1 as nos union all select cte.nos + 1 from cte where nos < 100 )
select nos
from cte
union all
select nos + 100
from cte order by nos ;

-- upto 199 numbers -- alternative way

with cte (nos)
as
( select 1 as nos union all select cte.nos + 1
from cte where nos < 100
union all
select cte.nos + 100 from cte where nos < 100 )
select nos from cte order by nos ;

/* you can use select nos into #temp from cte to port into a temp table */

SQL Server Backups in brief

SQL Server backups in brief
----- Full Backup ----
Full Backup can be taken in all the recovery modes simple,bulk logged, full. .
Full Backup will not truncate the log.
 Full Back up is essential for all restorations.
 Full Backup is not enough for point-in-restoration.
Log backup is required.
 For database restore, if there are differential/log backups , restore of fullbackup should be done with NORECOVERY.

---------Differential Backup --------
 Differential Backup can be taken in all the recovery modes simple,bulk logged, full. . Differential Backup will have data after the last full backup and are cumulative.
ie If a full back up is taken at 2 AM.
differnetial back up taken at 3 AM -- This will have changed data between 2 AM to 3AM. differnetial back up taken at 4 AM -- This will have changed between 2 AM to 4 AM. . Differential Backup will not truncate the log.
 For database restore, if there are log backups ,
restore of differential backup should be done with NORECOVERY.

--------Log Backup -----------
Log Backup can be taken if the database recovery modes are bulk logged or full. .
Log backup will truncate the log after backup log command . (See exceptions in BOL) . Log backup will not truncate when no_truncate, copy_only.
When replication is enabled on the database,it will truncate the log after inactive portion is replicated. 
Truncating the log means providing the avialable space for reuse.
This will not reduce the size of log file. .
To reduce the log size DBCC SHRINKFILE should be used
Avoid shrinking log file which may create performance problems and fragmentation. .
Log backups are essential for point-in-time recovery .
In case of database corruption, try to take the current log backup to recover.
If this is possible , this log file will become the last log file for recovery.
 For restore of database , restore full backup, subsequent differential backup and all the logs WITH NORECOVERY except the last log backup .
Last log back up should be restored WITH RECOVERY
-------Other Information --------------

 It may be necessary to issue CHECKPOINT before any of the backups taken. CHECKPOINT is esential for log truncation .
set recovery interval option with sp_configure indirectly sets CHECKPOINT interval .
If log backup sequence of files are used for restoration with last full backup ,
log backup can be taken just before fullbackup and this can be discarded .
This will truncate the logfile. . shrinking file will not invalidate log sequence restoration. . Keep higher log and data file size and avoid autogrow as much as possible.
Check dbcc opentran . Long running transactions will fill the log file.
 Fullbackup with differential backup can be used in simple recovery mode.
But this is not point-in-time recovery.

Validate for allowed characters in a sting

-- Validate for allowed characters in a sting
-- If the sting contains even one character which is not in the list, returns 1 else 0
declare
@string varchar(20)
set @string ='abcd01234'
select case when @string like '%[^a-zA-Z0-9!@~#$\^&*()\_+-;:",.{}]%' escape '\' then 1 else 0 end

-- To check only alphacharacters in the sting ( a-z , A-Z)

declare @string varchar(20) s
et @string ='azAZ'
select case when @string like '%[^a-zA-Z]%' then 1 else 0 end

unknown date time functions

-- unknown date time functions

select {fn extract(minute from getdate())}
select {fn dayname( getdate()) }
select {fn current_date()}
select {fn current_time()}

Try Catch Demo

- Try Catch Demo
-- Note: Severity greater than 10 only is caught in Catch block
create procedure dbo.ErrorDetails
as
select
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
GO

-- Execute the following

begin try
select getdate()
select 1/0
end try
begin catch
select error_message()
exec ErrorDetails
end catch;

SPs referring to a particular table and column

-- SPs referring to a particular table and column
-- Note Dynamic SQLs are skipped

select o.name, o2.name, c.name, d.selall
from sysobjects o
join sysdepends d on o.id = d.id
join sysobjects o2 on o2.id = d.depid
join syscolumns c on c.id = d.depid
and c.colid = d.depnumber
where o2.name = 'account' -- table
and c.name = 'accountid' -- column

Sort numerically when numbers are in the string

- Sort numerically when numbers are in the string

create table #test(col varchar(10)not null primary key)
insert into #test values ('a104')
insert into #test values ('a152')
insert into #test values ('a1010')
insert into #test values ('a15')
insert into #test values ('a17')
insert into #test values ('aa130')
insert into #test values ('b11')
insert into #test values ('b30')
-- usual sort
select * from #test order by col

-- sort on numeric numbers
select * from #test
order by substring (col, 0,patindex('%[0-9]%',col))+ right('00000' + substring (col,patindex('%[0-9]%',col) , datalength(col)),5)
drop table #test
go

select minimum of minimum

-- select minimum of minimum
-- select minimum of id3 , for that id3 select minimum of id2

create table #t ( ID1 int, ID2 int, ID3 int )
insert into #t select 1005, 1, 37
union
select 1005, 2, 0
union
select 1005, 3, 0
union
select 1005, 4, 54
union
select 1005, 5, 38
union
select 1007, 1, 2
union
select 1009, 1, 1
union
select 1009, 2, 3
union
select 1010, 1, 1
union
select 1010, 2, 1
union
select 1010, 3, 2
union select 1010, 4, 3
select * from #t

 -- SQL 2005
with rs as
(select id1,id2,id3
,row_number() over (partition by id1 order by id3,id2) as r from #t)
select id1,id2,id3
from rs where r = 1 ;

-- SQL 2005 and SQL 2000

select id1,min(id2) rr,id3
from #t
where id3 =
(select min(id3) from #t t where t.id1=#t.id1 )
group by id1,id3
drop table #t

Select random 6 numbers delimited with comma

-- Select random 6 numbers delimited with comma
-- SQL 2005 only .
-- Executing this query multiple times will give
-- different number combinations between 1 -49

with l0 as (   select 0 as c union all select 0 ),
l1 as (select 0 as c from l0 as a, l0 as b),
l2 as (select 0 as c from l1 as a, l1 as b),
l3 as (select 0 as c from l2 as a, l2 as b),
nums as (select top(49) row_number() over(order by c) as n from l3),
choice as (select top(6) n from nums order by checksum(newid()))

select stuff( (select ',' + cast(n as varchar(10)) as [text()]
from choice order by n for xml path('')), 1, 1, '' );

Pad zeros to a numeric number for a given length

-- Pad zeros to a numeric number for a given length
-- Note: total length specified includes decimal point and digits after the decimal point

declare @number numeric(10,4)
set @number = 1234.56
select replace(str(@number,10,4),' ','0') -- output: 01234.5600

-- You can try the following variations

select replace(str(@number,10,3),' ','0') -- output: 001234.560

select replace(str(@number,10,2),' ','0') -- output: 0001234.56

select replace(str(@number,10,1),' ','0') -- output: 00001234.6

select replace(str(@number,10,0),' ','0') -- output: 0000001235

ordering character year and month

-- If you have data in character format as below
-- and would like to sort on year and month

create table #t (c varchar(50))

insert into #t values ('Apr 2007')
insert into #t values ('June 2008')
insert into #t values ('Sep 2007')
insert into #t values ('Nov 2007')
insert into #t values ('Feb 2006')
select * from #t
order by convert(datetime,c,103) asc
drop table #t
go

object lastmodified date and time

select name as objectname,modify_date lastmodified ,
case when type_desc='SQL_INLINE_TABLE_VALUED_FUNCTION'
then 'inline udf' else type_desc
end as objecttype
from sys.objects
order by modify_date desc

To get the list of procedures that have a certain parameter

-- SQL 2005
-- To get the list of procedures that have a certain parameter

declare @parametername varchar(100)
set @parametername = '@userid'
select o.name as ObjectName, o.type as ObjectType, p.name as Parameter, t.name as DataType, p.max_length, p.[precision], p.scale
from sys.parameters p
inner join sys.types t
on p.user_type_id=t.user_type_id
inner join sys.objects o
on p.object_id=o.object_id
where p.name=@parametername

- To get the list of SPs or functions that depend on a certain function

select object_name(object_id)
from sys.sql_dependencies
where referenced_major_id=object_id('ufn_AlpIntlistToTbl')
-- function Name and class in (0,1)

To find gaps in numbers

-- To find gaps in numbers. works for negative numbers also
-- gaps are out put as startgap and endgap ( both inclusive)

declare @tempnum table (numbers int not null primary key)
insert into @tempnum values (1)
insert into @tempnum values (3)
insert into @tempnum values (7)
insert into @tempnum values (9)
insert into @tempnum values (1000)
insert into @tempnum values (-1)
insert into @tempnum values (-4)
insert into @tempnum values (-8)
insert into @tempnum values (-10)

select (numbers + 1) as startgap, (nextnumber - 1) as endgap
from
(
select numbers,
  (
    select min(numbers) from @tempnum as b
    where b.numbers > a.numbers) as nextnumber
    from @tempnum as a) as d
where (nextnumber - numbers) > 1

Date Without Time and other tricks

/*
date without time easiest way
no need to remember format as in convert and
no datatye conversions to varchar and back
*/

select dateadd(day,datediff(day,0,getdate()),0) as today

-- with convert and cast
select cast(convert(varchar(20),getdate(),112)as DATETIME) as today

-- First day of the month
select dateadd(month, datediff(month, 0, getdate()), 0)

Datetime rounded to next higher 30th second

/*
-- Datetime time rounded to next higher 30th second.
-- If seconds are between 0 and 29 it will be rounded to 30
-- If seconds are between 30 and 59 it will be rounded to next higher minute
-- can be used for grouping
*/

select distinct crdate, dateadd(ms,-(datepart(ms,crdate) + ((datepart(second,crdate) % 30) * 1000))+ 30000,crdate)as sec30interval
from sysobjects

Create a column with unique constraint where multiple nulls are allowed

/*
 Create a column with unique constraint where multiple nulls are allowed
 Requires PK in the table .
Trick is to create a computed column with primary key
 Example allows duplicate cusips even when they are null
*/
CREATE TABLE #security (
securityid int identity(1,1) primary key,cusip varchar(13) NULL,
cusipnull as (case when cusip is null then securityid else 0 end),
CONSTRAINT cusipnull_uinque UNIQUE (cusip,cusipnull))

INSERT INTO #security(cusip) VALUES ('abcd')
INSERT INTO #security(cusip) VALUES (NULL)
INSERT INTO #security(cusip) VALUES (NULL) -- no error
GO

SELECT securityid, cusip, cusipnull
FROM #security order by 1

UPDATE #security SET cusip = 'efgh'
WHERE securityid = 2
GO
SELECT securityid, cusip, cusipnull
FROM #security order by 1

UPDATE #security
SET cusip = 'efgh' WHERE securityid = 3 -- error SELECT securityid, cusip, cusipnull FROM #security
DROP TABLE #security

checking for % when it is part of data

select c1
from
(
    select 'universal%person' as c1 union all select 'universal'
 ) as t
 where c1 like '%\%%' escape '\'

select c1
from
 (
select 'universal%person' as c1 union all select 'universal'
) as t
where c1 like '%[%]%' escape '\'

How to find Check Constraint Definition

-- Plesae Note: Following code creates a table called TestCheckConstraint and drops it

create table TestCheckConstraint(col int,coldatetime datetime)
alter table TestCheckConstraint
add constraint coldatetime_checkcheck ( coldatetime > '20070101')
select text from syscommentswhere id = OBJECT_ID('coldatetime_check')
drop table TestCheckConstraint
go

SQL Server 2005 Change all objects to dbo schema

SELECT 'ALTER SCHEMA dbo TRANSFER ' + SCHEMA_NAME(schema_id) + '.' + name
FROM sys.tables
WHERE schema_id != SCHEMA_ID('dbo');

SELECT 'ALTER SCHEMA dbo TRANSFER ' + SCHEMA_NAME(schema_id) + '.' + name
FROM sys.views
WHERE schema_id != SCHEMA_ID('dbo');

SELECT 'ALTER SCHEMA dbo TRANSFER ' + SCHEMA_NAME(schema_id) + '.' + name
FROM sys.procedures
WHERE schema_id != SCHEMA_ID('dbo');