Set Cascade DELETE/UPDATE in SQL Server

Try to set up cascading delete/update in your database through SSMS. Here is the steps:

You can set the Delete/Update Rule to Cascade for the foreign key. In SQL Server Management Studio, Click on your table name and find the Keys and expand i;, right click on the foreignkey name in your key collection and click Modify>> under INSERT and UPDATE specification to see the Delete Rule and Update Rule. The default value is No Action for DELETE Rule or UPDATE Rule. You need to change the value to Cascade.

You can use T-SQL script to set these:

 
ALTER TABLE [dbo].[ChildTable]  WITH CHECK ADD  CONSTRAINT [FK_ChildTable_ParentTable] FOREIGN KEY([aid])
REFERENCES [dbo].[ParentTable] ([AId])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

Restore Database with New Names (T-SQL)

USE [master]
GO



BEGIN


DECLARE @Destinationdb    AS NVARCHAR(255)= 'myDB', --Database Name to be restored
        @fullbackup       AS NVARCHAR(2000),
        @diffbackup       AS NVARCHAR(2000)=NULL,
        @logbackup1       AS NVARCHAR(2000)=NULL,
        @logbackup2       AS NVARCHAR(2000)=NULL,
	@logbackup3       AS NVARCHAR(2000)=NULL,
        @desPathData      AS NVARCHAR(2000) ='C:\MSSQL\Data\',
        @desPathLog        AS NVARCHAR(2000) ='C:\MSSQL\Log\' ,
        @backupSourcePath AS NVARCHAR(2000) ='C:\Backup\',
	--Check filestream feature
	@includeFilestream bit=0

----1.Full backup file
SET @fullbackup = N'MyDB_FULL.bak'

----2.Most recent Diff backup since Full backup (Optional)
--SET @diffbackup = N'MyDB_DIFF_20120712_080511.bak'
----3. Moste recent Log backups since Diff backup (Optional and upto three log files) 
--SET @logbackup1 = N'MyDB_LOG_20120712_083107.trn' 
----SET @logbackup2 = N'MyDB_LOG_20120711_144502.trn' 
----SET @logbackup3 = N'MyDB_LOG_20120711_150002.trn' 


/***************************************************************/


  --Full path to backup files

SET @fullbackup = @backupSourcePath +  @fullbackup
SET @diffbackup = @backupSourcePath +  @diffbackup
SET @logbackup1 = @backupSourcePath +  @logbackup1
SET @logbackup2 = @backupSourcePath +  @logbackup2
SET @logbackup3 = @backupSourcePath +  @logbackup3




DECLARE @CreateNewDatabaseSQL AS NVARCHAR(4000),
        @cmdFull              AS NVARCHAR(2000),
        @cmdDiff              AS NVARCHAR(2000),
        @cmdLog1              AS NVARCHAR(2000),
        @cmdLog2              AS NVARCHAR(2000),
	@cmdLog3              AS NVARCHAR(2000),
        @cmdFinal             AS NVARCHAR(2000),
        @cmdGetFileNames      AS NVARCHAR(1000),
        @cmdUpdateLogicalName NVARCHAR(2000),
        @s                    VARCHAR(128) 



--Check if the Destination Database exists
IF NOT EXISTS (SELECT 1
               FROM   sys.sysdatabases
               WHERE  name = @destinationdb)
  BEGIN
      SET @CreateNewDatabaseSQL='CREATE DATABASE [' + @destinationdb
               + '] ON  PRIMARY ( NAME = N'''
               + @destinationdb + ''', FILENAME = N'''
               + @desPathData + @destinationdb
               + '.mdf'')'
             If  (@includeFilestream<>0)  
               begin
               SET @CreateNewDatabaseSQL=@CreateNewDatabaseSQL+', FILEGROUP  ['
               + @destinationdb + '] CONTAINS FILESTREAM  DEFAULT (NAME = N'''  + @destinationdb + '_filestream'', FILENAME = N''' + @desPathData + @destinationdb + '_filegroup'')'
               end
               
         SET @CreateNewDatabaseSQL=@CreateNewDatabaseSQL+' LOG ON ( NAME = N''' + @destinationdb
               + '_log'', FILENAME =  N''' + @desPathLog
               + @destinationdb + '_log.ldf'')'
--print @CreateNewDatabaseSQL
EXEC (@CreateNewDatabaseSQL)
  END





CREATE TABLE #backupdetails
  (
     LogicalName          NVARCHAR(255),
     PhysicalName         NVARCHAR(255),
     Type                 NVARCHAR(1),
     FileGroupName        NVARCHAR(255),
     Size                 BIGINT,
     MaxSize              BIGINT,
     FileId               INT NULL,
     CreateLSN            NUMERIC(25, 0) NULL,
     DropLSN              NUMERIC(25, 0) NULL,
     UniqueFileId         UNIQUEIDENTIFIER NULL,
     readonlyLSN          NUMERIC(25, 0) NULL,
     readwriteLSN         NUMERIC(25, 0) NULL,
     BackupSizeInBytes    BIGINT NULL,
     SourceBlkSize        INT NULL,
     FileGroupId          INT NULL,
     LogGroupGuid         UNIQUEIDENTIFIER NULL,
     DifferentialBaseLsn  NUMERIC(25, 0) NULL,
     DifferentialBaseGuid UNIQUEIDENTIFIER NULL,
     IsReadOnly           BIT NULL,
     IsPresent            BIT NULL,
     --SQL Server 2008 Only
     TDEThumbprint        VARBINARY(32) NULL
  ) 





SET @cmdGetFileNames ='RESTORE FILELISTONLY FROM DISK='''
                      + @fullbackup + ''''

INSERT #backupdetails
EXEC(@cmdGetFileNames) 



-- build the restore command
SELECT @cmdFull = NULL,
       @cmdUpdateLogicalName = NULL,
       @s = ''

WHILE @s < (SELECT Max(LogicalName)
            FROM   #backupdetails)
  BEGIN
      BEGIN
          SELECT @s = Min(LogicalName)
          FROM   #backupdetails
          WHERE  LogicalName > @s

   
		      SELECT @cmdFull = COALESCE(@cmdFull + ', MOVE ', '') + '''' + @s
                        + ''' to ''' + CASE WHEN #backupdetails.type = 'D' THEN ''+ @desPathData+'' WHEN #backupdetails.type = 'L' THEN ''+ @desPathLog+'' WHEN #backupdetails.type = 'S' THEN ''+ @desPathData+''ELSE ''+ @desPathData+''  END +                        
                        @destinationdb + CASE WHEN #backupdetails.type = 'D' THEN '.mdf' WHEN #backupdetails.type = 'L' THEN '.ldf' ELSE'' +@s+''  END + ''''
          FROM   #backupdetails
          WHERE  LogicalName = @s
      END

      BEGIN
          SELECT @cmdUpdateLogicalName = COALESCE(@cmdUpdateLogicalName, '')
                                         + ' 
                                         ALTER DATABASE [' + @destinationdb
                                         + ']
                 MODIFY FILE (NAME= [' + @s + '], NEWNAME=[' + @destinationdb + ''+ CASE WHEN #backupdetails.type = 'D' THEN '] ' WHEN #backupdetails.type = 'L' THEN '_Log] '  WHEN #backupdetails.type = 'S' THEN '_FileGroup]' ELSE '-' END + ')'
          FROM   #backupdetails
          WHERE  LogicalName = @s
                 AND LogicalName <> @destinationdb + CASE WHEN #backupdetails.type = 'D' THEN '' WHEN #backupdetails.type = 'L' THEN '_Log' WHEN #backupdetails.type = 'S' THEN '_FileGroup'  ELSE '' END
      END
  END


  SELECT @cmdFull = 'Restore database ' + @destinationdb
                  + ' from disk = ''' + @fullbackup
                  + ''' WITH REPLACE,NORECOVERY,  MOVE '
                  + @cmdFull

IF ( @diffbackup IS NOT NULL )
  BEGIN
      SELECT @cmdDiff = 'Restore database ' + @destinationdb
                        + ' from disk = ''' + @diffbackup
                        + ''' WITH NORECOVERY'
  END

IF ( @logbackup1 IS NOT NULL )
  BEGIN
      SELECT @cmdLog1 = 'Restore database ' + @destinationdb
                           + ' from disk = ''' + @logbackup1
                           + ''' WITH NORECOVERY'
  END

IF ( @logbackup2 IS NOT NULL )
  BEGIN
      SELECT @cmdLog2 = 'Restore database ' + @destinationdb
                           + ' from disk = ''' + @logbackup2
                           + ''' WITH NORECOVERY'
  END

  IF ( @logbackup3 IS NOT NULL )
  BEGIN
      SELECT @cmdLog3 = 'Restore database ' + @destinationdb
                           + ' from disk = ''' + @logbackup3
                           + ''' WITH NORECOVERY'
  END

SELECT @cmdFinal = 'Restore database ' + @destinationdb
                   + ' WITH RECOVERY' 


DROP TABLE #backupdetails 


DECLARE @changeMode AS NVARCHAR(255)

--Change to Single-user mode
SET @changeMode='USE [' + @destinationdb
                + ']    ALTER DATABASE [' + @destinationdb
                + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE '

EXEC(@changeMode)
--Restore Full backup with NORecovery to continue
EXEC (@cmdFull)

--Restore Diff backup 
IF ( @cmdDiff IS NOT NULL )
  EXEC (@cmdDiff)


--Restore Log backup 
IF ( @logbackup1 IS NOT NULL )
  EXEC (@cmdLog1)
--Restore Log backup2 
  IF ( @logbackup2 IS NOT NULL )
  EXEC (@cmdLog2)
  --Restore Log backup3
IF ( @logbackup3 IS NOT NULL )
  EXEC (@cmdLog3)


--Restore with Recovery to finish restore process
EXEC (@cmdFinal) 



--Update Logical names for the new database
EXEC (@cmdUpdateLogicalName)

--Change to RECOVERY SIMPLE
SET @changeMode='USE [' + @destinationdb
                + ']    ALTER DATABASE [' + @destinationdb
                + '] SET RECOVERY SIMPLE'

EXEC(@changeMode)

--Change to Single-user mode
SET @changeMode='USE [' + @destinationdb
                + ']    ALTER DATABASE [' + @destinationdb
                + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE  CHECKPOINT  DBCC SHRINKFILE (2, 500);'

EXEC(@changeMode)

--Change back to multiple user mode
SET @changeMode='ALTER DATABASE [' + @destinationdb
                + '] SET MULTI_USER'

EXEC(@changeMode)

--Change to RECOVERY FULL
SET @changeMode='USE [' + @destinationdb
                + ']    ALTER DATABASE [' + @destinationdb
                + '] SET RECOVERY FULL'

--EXEC(@changeMode) 


END

Check Restore Process with sys.dm_exec_requests (T-SQL)

SELECT percent_complete , (estimated_completion_time/1000)/60 Estimated_completion_time_Mins,
    [spid] = r.session_id,
    [database] = DB_NAME(r.database_id),
    r.start_time,
    r.[status],
    r.command,
    /* add other interesting columns here */
    [obj] = QUOTENAME(OBJECT_SCHEMA_NAME(t.objectid, t.[dbid]))
    + '.' + QUOTENAME(OBJECT_NAME(t.objectid, t.[dbid])),
    t.[text]
FROM
    sys.dm_exec_requests AS r
CROSS APPLY
    sys.dm_exec_sql_text(r.[sql_handle]) AS t
WHERE
    r.session_id <> @@SPID
    AND r.session_id > 50

http://sqlblog.com/blogs/aaron_bertrand/archive/2008/07/01/sys-dm-exec-requests.aspx

A Usage of Row_Number Function (T-SQL)

An example to use Row_Number function to solve an complex problem on MSDN:
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/84a30edf-67a9-4598-8aef-cc6594135113

create table #temp
(rownum int,
id int,
r_id varchar(10),
ord int,
w_no int,
r_no varchar(10),
dt_Frm datetime,
tr_time datetime
)
insert into #temp values ('2','101','101||3','3','37','925','2012-09-27','1900-01-01 14:30:00.000')
insert into #temp values ('4','101','101||8','8','55','978','2012-10-18','1900-01-01 08:51:00.000')
insert into #temp values ('5','101','101||9','9','55','976','2012-10-18','1900-01-01 14:44:00.000')
insert into #temp values ('6','101','101||12','12','42','1425','2013-01-01','1900-01-01 12:12:00.000')
insert into #temp values ('8','101','101||17','17','55','976','2013-01-07','1900-01-01 08:43:00.000')
insert into #temp values ('9','101','101||20','20','65','1876','2013-01-09','1900-01-01 09:34:00.000')
insert into #temp values ('2','102','102||3','3','37','1451','2012-08-18 00:00:00.000','1900-01-01 14:51:00.000')
insert into #temp values ('4','102','102||8','8','37','393','2012-08-30 00:00:00.000','1900-01-01 13:05:00.000')
insert into #temp values ('5','102','102||9','9','37','1451','2012-10-09 00:00:00.000','1900-01-01 22:36:00.000')
insert into #temp values ('6','102','102||12','12','42','276','2013-01-16 00:00:00.000','1900-01-01 00:18:00.000')
insert into #temp values ('8','102','102||17','17','42','276','2013-01-21 00:00:00.000','1900-01-01 01:03:00.000')
insert into #temp values ('8','102','102||17','17','42','1147','2013-01-26 00:00:00.000','1900-01-01 16:36:00.000')
insert into #temp values ('9','102','102||20','20','65','1876','2013-02-07 00:00:00.000','1900-01-01 11:00:00.000')

 ;with mycte as
 (
 select *,  row_number() over( order by  dt_Frm DESC) - row_number() over(partition by w_no order by dt_Frm DESC)  deltaRn from #temp
 
 )

 ,mycte1 as
 (
 Select *,  row_number() over(partition by id,w_no, deltaRn  order by id, dt_Frm DESC, r_no) rn from mycte)

 Select * from mycte1 WHERE rn=1
 order by id, rownum

/*********************************************************/

create table #result_temp
(rownum int,
id int,
r_id varchar(10),
ord int,
w_no int,
r_no varchar(10),
dt_Frm datetime,
tr_time datetime
)
insert into #result_temp values ('2','101','101||3','3','37','925','2012-09-27','1900-01-01 14:30:00.000')
insert into #result_temp values ('5','101','101||9','9','55','976','2012-10-18','1900-01-01 14:44:00.000')
insert into #result_temp values ('6','101','101||12','12','42','1425','2013-01-01','1900-01-01 12:12:00.000')
insert into #result_temp values ('8','101','101||17','17','55','976','2013-01-07','1900-01-01 08:43:00.000')
insert into #result_temp values ('9','101','101||20','20','65','1876','2013-01-09','1900-01-01 09:34:00.000')
insert into #result_temp values ('5','102','102||9','9','37','1451','2012-10-09 00:00:00.000','1900-01-01 22:36:00.000')
insert into #result_temp values ('8','102','102||17','17','42','1147','2013-01-26 00:00:00.000','1900-01-01 16:36:00.000')
insert into #result_temp values ('9','102','102||20','20','65','1876','2013-02-07 00:00:00.000','1900-01-01 11:00:00.000')


select * from #result_temp

drop table #temp
drop table #result_temp

An Approximate Pagination with Non-Break Group With T-SQL

I have been asked about this question with Oracle. I came across this discussion at this thread:
http://stackoverflow.com/questions/3438228/oracle-how-to-group-rows-for-pagination

I have modified the solution to work with SQL Server as below:

create table test (empno int, ename varchar(20), trandate date, amt int);
insert into test values (100, 'Alison'   ,  '21-MAR-1996' ,   45000);
insert into test values (100, 'Alison'   ,  '12-DEC-1978' ,   23000);
insert into test values (100, 'Alison'   ,  '24-OCT-1982' ,   11000);
insert into test values (101, 'Linda'    ,  '15-JAN-1984' ,   16000);
insert into test values (101, 'Linda'    , '30-JUL-1987' ,   17000);
insert into test values (101, 'Linda'    ,  '30-JUL-1987' ,   17000);
insert into test values (102, 'Celia'    ,  '31-DEC-1990' ,   78000);
insert into test values (102, 'Celia'    ,  '17-SEP-1996' ,   21000);
insert into test values (103, 'James'    ,  '21-MAR-1996' ,   45000);
insert into test values (103, 'James'    , '12-DEC-1978' ,   23000);
insert into test values (103, 'James'    , '12-DEC-1978' ,   23000);
insert into test values (100, 'Alison'   ,  '21-MAR-1996' ,   45000);
insert into test values (100, 'Alison'   ,  '12-DEC-1978' ,   23000);
insert into test values (100, 'Alison'   ,  '24-OCT-1982' ,   11000);
insert into test values (101, 'Linda'    ,  '15-JAN-1984' ,   16000);
insert into test values (101, 'Linda'    , '30-JUL-1987' ,   17000);
insert into test values (101, 'Linda'    ,  '30-JUL-1987' ,   17000);
insert into test values (102, 'Celia'    ,  '31-DEC-1990' ,   78000);
insert into test values (102, 'Celia'    ,  '17-SEP-1996' ,   21000);
insert into test values (103, 'James'    ,  '21-MAR-1996' ,   45000);
insert into test values (103, 'James'    , '12-DEC-1978' ,   23000);
insert into test values (103, 'James'    , '12-DEC-1978' ,   23000);
insert into test values (103, 'James'    , '12-DEC-1978' ,   23000);


select empno, ename,
       ceiling((rank() over (order by empno) +
        count(*) over (partition by empno))/8.) as chunk
,rank() over (order by empno) myRank
, count(*) over (partition by empno)myCnt
from test
order by empno;
/*
empno	ename	chunk	myRank	myCnt
100	Alison	1	1	6
100	Alison	1	1	6
100	Alison	1	1	6
100	Alison	1	1	6
100	Alison	1	1	6
100	Alison	1	1	6
101	Linda	2	7	6
101	Linda	2	7	6
101	Linda	2	7	6
101	Linda	2	7	6
101	Linda	2	7	6
101	Linda	2	7	6
102	Celia	3	13	4
102	Celia	3	13	4
102	Celia	3	13	4
102	Celia	3	13	4
103	James	3	17	7
103	James	3	17	7
103	James	3	17	7
103	James	3	17	7
103	James	3	17	7
103	James	3	17	7
103	James	3	17	7

*/

Combine Rows To Column—T-SQL Sample

CREATE TABLE ContractEvents (ProjectId      INT,
                             ContractNumber INT)

INSERT INTO ContractEvents
VALUES      (1,1), (1,2), (1,3), (2,1), (2,2)

DECLARE @Events TABLE ( ProjectId       INT,
               ContractNumbers VARCHAR(200)) 


INSERT INTO @Events  

Select Projectid,  ContractNumbers = STUFF(( Select ', ' + convert(varchar(100),c.ContractNumber) as [text()]
From ContractEvents c
Where c.ProjectId = b.ProjectId  
Order by ProjectId
For XML Path ('')), 1, 1, '')

From ContractEvents b
Group by ProjectId

--Select * from @Events
 

drop table ContractEvents

SQL Server Compatibility Level

I have come across this link from MSDN about Compatibility Level:
http://technet.microsoft.com/en-us/library/bb510680(v=sql.110).aspx

COMPATIBILITY_LEVEL { 90 | 100 | 110 }

90 = SQL Server 2005
100 = SQL Server 2008 and SQL Server 2008 R2
110 = SQL Server 2012

To run a database with a lower COMPATIBILITY LEVEL doe not guarantee everything will work as it did when the database was in lower version.

Dwain Camps posted an article about using CTE to limit Target table when we apply MERGE.

http://www.sqlservercentral.com/articles/MERGE/97867/

It is a very good article to raise the issue. There is another option to limit the target table for this condition: WHEN NOT MATCHED BY SOURCE with DELETE
with an AND condition to limit the target table rows and also we have another option and the last option to use the condition with UPDATE.
You can find original sample in the article from above link.

Here is the modified sample with the AND condition for DELETE and the operation of UPDATE for the second one:

CREATE TABLE #Test1 (ID INT, RowNo INT, Value decimal(16,2));
CREATE TABLE #Test2 (ID INT, RowNo INT, Value decimal(16,2));

-- target
INSERT INTO #Test1 (ID, RowNo, Value)
VALUES (1,1,25),(1,2,32),(2,1,38),(2,2,61),(2,4,43),(3,1,15),(3,2,99),(3,3,54);
-- source
INSERT INTO #Test2 (ID, RowNo, Value)
VALUES (2,1,45),(2,2,88),(2,3,28);


 BEGIN TRANSACTION T1;

MERGE #Test1 t       -- Target 
USING #Test2 s       -- Source 
ON t.ID = s.ID AND t.RowNo = s.RowNo
  WHEN MATCHED 
    THEN
      UPDATE SET Value = s.Value
  WHEN NOT MATCHED   -- Target
    THEN
       INSERT (ID, RowNo, Value)
         VALUES (s.ID, s.RowNo, s.Value)
WHEN NOT MATCHED BY SOURCE  And t.ID in (Select ID from #test2)
--You cannot access the source ID here and instead we use a subquery 
    THEN
      DELETE
WHEN NOT MATCHED BY SOURCE  
    THEN
      UPDATE SET Value = t.Value*100;

SELECT  *  FROM #Test1  ORDER BY ID, RowNo;

ROLLBACK TRANSACTION T1;


 drop table #Test1 
 drop table #Test2

Fill Value For Each Month (T-SQL Sample)

declare @PriceList table (Tradedate datetime, Price numeric(30,2),ID VARCHAR(10)) 
insert into @PriceList select '1/31/2013',40.00,'id'

union all select '3/31/2013',50.00,'id' 

union all select '3/31/2013',80.00,'id2' 
union all SELECT '5/31/2013',90.00,'id3' 
SELECT * FROM @PriceList 



;with mycte as 
(Select *,row_number() Over(partition by ID order by TradeDate) rn,  dateadd(month,datediff(month, 0,Tradedate)+num,-1) as newEnd from @pricelist 
Cross apply (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12) ) d(num)
)
,mycte1 as(
Select newEnd, Price, id,  row_number() Over(partition by ID,newEnd order by rn DESC) rn2 from mycte  WHERE newEnd<=(select max(Tradedate) from @pricelist)  
)

Select newEnd, Price, id from mycte1
WHERE rn2=1
order by id,newEnd
/*
newEnd	Price	id
2013-01-31 00:00:00.000	40.00	id
2013-02-28 00:00:00.000	40.00	id
2013-03-31 00:00:00.000	50.00	id
2013-04-30 00:00:00.000	50.00	id
2013-05-31 00:00:00.000	50.00	id
2013-03-31 00:00:00.000	80.00	id2
2013-04-30 00:00:00.000	80.00	id2
2013-05-31 00:00:00.000	80.00	id2
2013-05-31 00:00:00.000	90.00	id3

*/