CUBE operator is used with Group by clause. It use to get the subtotal and grand total of all permutation of columns provided by the CUBE operator.

ROLLUP operator is used with Group by clause. It use to get the subtotal and grand total of a set of columns provided by the ROLLUP operator.

For example,


Declare @tmpTable table(Product varchar(10), Shop varchar(10), Quantity int)
insert into @tmpTable (Product, Shop, Quantity) values ('Tea', 'Tea-Post',100)
insert into @tmpTable (Product, Shop, Quantity) values ('Tea', 'GH2',90)
insert into @tmpTable (Product, Shop, Quantity) values ('Puva', 'Tea-Post',10)
insert into @tmpTable (Product, Shop, Quantity) values ('Puva', 'GH2',25)
insert into @tmpTable (Product, Shop, Quantity) values ('Lassi', 'Anand',2)
insert into @tmpTable (Product, Shop, Quantity) values ('Lassi', 'Nadiad',20)
insert into @tmpTable (Product, Shop, Quantity) values ('Lassi', 'Khambhat',50)
select * from @tmpTable

SELECT Product,Shop,sum(Quantity) FROM @tmpTable GROUP BY CUBE (Product,Shop)


Result would be

Let me explain the case first, so you will get the idea when this is required to use. In the database, there is more than one tables which store the future values and will move on specific time to standard tables. For example,
Table “UserList2” contains the future values consider as temporary table. Table “UserList1” contains standard values consider as standard table. SQL SCRIPT as below:

GO
/****** Object: Table [dbo].[UserList2] Script Date: 11/26/2015 07:59:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[UserList2](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[City] [varchar](50) NULL,
CONSTRAINT [PK_UserList2] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[UserList2] ON
INSERT [dbo].[UserList2] ([Id], [Name], [City]) VALUES (1, N'Prakash', N'Anand')
INSERT [dbo].[UserList2] ([Id], [Name], [City]) VALUES (2, N'Rupal', N'Baroda')
INSERT [dbo].[UserList2] ([Id], [Name], [City]) VALUES (3, N'Arpit', N'Anand')
INSERT [dbo].[UserList2] ([Id], [Name], [City]) VALUES (4, N'Vishal', N'Baroda')
INSERT [dbo].[UserList2] ([Id], [Name], [City]) VALUES (5, N'Ajay', N'Gandhinagar')
INSERT [dbo].[UserList2] ([Id], [Name], [City]) VALUES (6, N'Kamal', N'Rajkot')
INSERT [dbo].[UserList2] ([Id], [Name], [City]) VALUES (7, N'Akshay', N'Rajkot')
INSERT [dbo].[UserList2] ([Id], [Name], [City]) VALUES (8, N'Khimjibhai', N'Gandhinagar')
SET IDENTITY_INSERT [dbo].[UserList2] OFF
/****** Object: Table [dbo].[UserList1] Script Date: 11/26/2015 07:59:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[UserList1](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[City] [varchar](50) NULL,
[UserList1Id] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[UserList1] ON
INSERT [dbo].[UserList1] ([Id], [Name], [City], [UserList1Id]) VALUES (1, N'Prakash', N'Anand', 1)
INSERT [dbo].[UserList1] ([Id], [Name], [City], [UserList1Id]) VALUES (2, N'Arpit', N'Anand', 3)
INSERT [dbo].[UserList1] ([Id], [Name], [City], [UserList1Id]) VALUES (3, N'Prakash', N'Anand', 1)
INSERT [dbo].[UserList1] ([Id], [Name], [City], [UserList1Id]) VALUES (4, N'Arpit', N'Anand', 3)
INSERT [dbo].[UserList1] ([Id], [Name], [City], [UserList1Id]) VALUES (5, N'Prakash', N'Anand', 1)
INSERT [dbo].[UserList1] ([Id], [Name], [City], [UserList1Id]) VALUES (6, N'Arpit', N'Anand', 3)
SET IDENTITY_INSERT [dbo].[UserList1] OFF

Want to move data from UserList2 to UserList1 and need to use UserList1 Identity column for further process, how to get all newly inserted which as below:


DECLARE @output TABLE (id int)

select * from @output


OUTPUT clause introduced in MS SQL Server 2005. Want to know more about OUTPUT clause refer this link

SAMPLE RECORDS

Query for getting top 10 records based on the company values with duplicate values

select T.Country 'Text', T.Company'Value'
from (
       select Country , Company,
              dense_rank() over(order by Companydesc) as rn
       from TableName      
     ) T
where T.rn <= 10

Sample data download: here

Declare @tempAttendance table(EmpID int, EmpName varchar(50), edate int, ecode varchar(5))

	insert into @tempAttendance 
	SELECT empid, empname, code as 'edate', value as 'ecode'
	FROM [dbo].[hattendance]
	UNPIVOT (
		value FOR code IN (
			 [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31]
		)
	) unpiv

	--select * from  @tempAttendance

	SELECT empid, empname, P , A, FH, NA
	FROM
	(SELECT empid, empname, ecode
	FROM @tempAttendance )  ps
	PIVOT
	(
	count (ecode)
	FOR Ecode IN
	( P , A, FH, NA )
	) AS pvt order by empid

download sql file from here

Sample Data download: http://goo.gl/lcOf8z

Generate excel report as below.

Username, Address, City, State, Zip, Question 1, Question 2, Question 3, …..
Prakash, test, anand, gujarat, 388001, Answer 1, Answer 2, Answer 3,..
Chirag, test, anand, gujarat, 388001, Answer 1, Answer 2, Answer 3,..

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Prakash Rathod
-- Create date: 18-Mar-2014
-- Description:	Generate dynamic pivot report of submited survey
-- Exec spSubmitedSurveyExcelReport 'FCDC5B31-58D6-4C25-92ED-74665E343141'
-- =============================================
ALTER PROCEDURE spSubmitedSurveyExcelReport
	@SurveyID uniqueidentifier
AS
BEGIN	
	SET NOCOUNT ON;
	
	create table #distictCaption (caption nvarchar(1000)) -- create temporary table for storing questions.

	insert into #distictCaption
	Select distinct caption From submitedsurvey s join users u on u.id = s.userid where [email protected]

	Create table #tempSurveyAnswer (userid uniqueidentifier, Name varchar(200), [Address] varchar(500), city varchar(200) , [state] varchar(200), zip varchar(10), dummy bit) -- create temp table for creating dynamic columns, questions become columns

	Declare @Script as Varchar(8000);
	Declare @Script_prepare as Varchar(8000);
	 
	Set @Script_prepare = 'Alter table #tempSurveyAnswer Add [?] varchar(100);'
	Set @Script = ''

	Select
				@Script = @Script + Replace(@Script_prepare, '?', caption)
	From #distictCaption 

	Exec (@Script)
	 
	Alter table #tempSurveyAnswer drop column dummy;

	insert into #tempSurveyAnswer(userid ,Name, [Address], City, [State], Zip) 
	select s.userid, u.firstname, u.[Address], u.City, u.[State], u.Zip from submitedsurvey s
	join users u on u.id = s.userid
	where [email protected] group by s.userid, u.firstname, u.[Address], u.City, u.[State], u.Zip
	 
-- update temp table
	DECLARE @sql NVARCHAR(MAX)
	SET @sql = ''

	select @sql = @sql + ' UPDATE #tempSurveyAnswer ' + CHAR(13) +
	' SET [' + c.name + '] = (SELECT value ' + CHAR(13) +
	' FROM submitedsurvey' + CHAR(13) +
	' where [email protected] and #tempSurveyAnswer.userid = submitedsurvey.userid and submitedsurvey.caption = ''' + c.name + ''') '+ CHAR(13) 
	from tempdb.sys.columns c
	where object_id = object_id('tempdb..#tempSurveyAnswer') and name'userid' and name'Name' and name  'Address' and name'city' and name'state' and
	name'zip';

	print(@sql)

	exec sp_executesql @sql,N'@I uniqueidentifier',@[email protected];

	Select * from #tempSurveyAnswer;

	drop table #distictCaption
	drop table #tempSurveyAnswer
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[unqSplit](@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (value nvarchar(4000))
AS

  --this function takes two parameters; the first is the delimited string, the second is the delimiter
    BEGIN
    DECLARE @INDEX INT
    DECLARE @SLICE nvarchar(4000)
    -- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
    --     ERO FIRST TIME IN LOOP
    SELECT @INDEX = 1
  
    IF @String IS NULL RETURN
    WHILE @INDEX !=0


        BEGIN    
            -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
            SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
            -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
            IF @INDEX !=0
                SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
            ELSE
                SELECT @SLICE = @STRING
            -- PUT THE ITEM INTO THE RESULTS SET
            INSERT INTO @Results(value) VALUES(@SLICE)
            -- CHOP THE ITEM REMOVED OFF THE MAIN STRING
            SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
            -- BREAK OUT IF WE ARE DONE
            IF LEN(@STRING) = 0 BREAK
    END

    RETURN
END

How to use the functions.

Declare @tempString nvarchar(max)
set @tempString = '''43C9D0A5-5FB7-45B9-BC2D-1D942B77A0CE'',''6c73f22f-7599-4e7a-bbdc-e71fe6e5f0f8'',''40d76edf-47d7-4e51-88fa-8bc3ae350d1c'',''3eece540-673e-42be-a123-17882e4e527f'''

-- '''6c73f22f-7599-4e7a-bbdc-e71fe6e5f0f8'',''40d76edf-47d7-4e51-88fa-8bc3ae350d1c'',''3eece540-673e-42be-a123-17882e4e527f'''


declare @tempTable table ( cid uniqueidentifier)
insert into @tempTable 
select convert(uniqueidentifier,  SUBSTRING(value,2,(LEN(value))))  from  dbo.unqSplit(@tempString, ',')

select * from  @tempTable

Create store procedure.

// Copyright © Microsoft Corporation.  All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)
USE [master] 
GO 
/****** Object:  StoredProcedure [dbo].[sp_BackupDatabases] ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
 
-- ============================================= 
-- Author: Microsoft 
-- Create date: 2010-02-06
-- Description: Backup Databases for SQLExpress
-- Parameter1: databaseName 
-- Parameter2: backupType F=full, D=differential, L=log
-- Parameter3: backup file location
-- =============================================
 
CREATE PROCEDURE [dbo].[sp_BackupDatabases]  
            @databaseName sysname = null,
            @backupType CHAR(1),
            @backupLocation nvarchar(200) 
AS 
 
       SET NOCOUNT ON; 
           
            DECLARE @DBs TABLE
            (
                  ID int IDENTITY PRIMARY KEY,
                  DBNAME nvarchar(500)
            )
           
             -- Pick out only databases which are online in case ALL databases are chosen to be backed up
             -- If specific database is chosen to be backed up only pick that out from @DBs
            INSERT INTO @DBs (DBNAME)
            SELECT Name FROM master.sys.databases
            where state=0
            AND [email protected]
            OR @DatabaseName IS NULL
            ORDER BY Name
           
            -- Filter out databases which do not need to backed up
            IF @backupType='F'
                  BEGIN
                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks')
                  END
            ELSE IF @backupType='D'
                  BEGIN
                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
                  END
            ELSE IF @backupType='L'
                  BEGIN
                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
                  END
            ELSE
                  BEGIN
                  RETURN
                  END
           
            -- Declare variables
            DECLARE @BackupName varchar(100)
            DECLARE @BackupFile varchar(100)
            DECLARE @DBNAME varchar(300)
            DECLARE @sqlCommand NVARCHAR(1000) 
        DECLARE @dateTime NVARCHAR(20)
            DECLARE @Loop int                  
                       
            -- Loop through the databases one by one
            SELECT @Loop = min(ID) FROM @DBs
 
      WHILE @Loop IS NOT NULL
      BEGIN
 
-- Database Names have to be in [dbname] format since some have - or _ in their name
      SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'
 
-- Set the current date and time n yyyyhhmmss format
      SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' +  REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')  
 
-- Create backup filename in path\filename.extension format for full,diff and log backups
      IF @backupType = 'F'
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'
      ELSE IF @backupType = 'D'
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK'
      ELSE IF @backupType = 'L'
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN'
 
-- Provide the backup a name for storing in the media
      IF @backupType = 'F'
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime
      IF @backupType = 'D'
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime
      IF @backupType = 'L'
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime
 
-- Generate the dynamic SQL command to be executed
 
       IF @backupType = 'F' 
                  BEGIN
               SET @sqlCommand = 'BACKUP DATABASE ' [email protected]+  ' TO DISK = '''[email protected]+ ''' WITH INIT, NAME= ''' [email protected]+''', NOSKIP, NOFORMAT'
                  END
       IF @backupType = 'D'
                  BEGIN
               SET @sqlCommand = 'BACKUP DATABASE ' [email protected]+  ' TO DISK = '''[email protected]+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' [email protected]+''', NOSKIP, NOFORMAT'        
                  END
       IF @backupType = 'L' 
                  BEGIN
               SET @sqlCommand = 'BACKUP LOG ' [email protected]+  ' TO DISK = '''[email protected]+ ''' WITH INIT, NAME= ''' [email protected]+''', NOSKIP, NOFORMAT'        
                  END
 
-- Execute the generated SQL command
       EXEC(@sqlCommand)
 
-- Goto the next database
SELECT @Loop = min(ID) FROM @DBs where ID>@Loop
 
END

How to execute store proc and create batch file for windows task scheduler or create a job in SQL server Agent.

Example1: Full backups of all databases in the local named instance of SQLEXPRESS by using Windows Authentication

// Sqlbackup.bat

sqlcmd -S .\EXPRESS –E -Q “EXEC sp_BackupDatabases @backupLocation=’D:\SQLBackups\’, @backupType=’F'”

Example2: Differential backups of all databases in the local named instance of SQLEXPRESS by using a SQLLogin and its password

// Sqlbackup.bat

sqlcmd -U SQLLogin -P password -S .\SQLEXPRESS -Q “EXEC sp_BackupDatabases @backupLocation =’D:\SQLBackups’, @BackupType=’D’”

Note: The SQLLogin shouldhave at least the Backup Operator role in SQL Server.

Example 3: Log backups of all databases in local named instance of SQLEXPRESS by using Windows Authentication

// Sqlbackup.bat

sqlcmd -S .\SQLEXPRESS -E -Q “EXEC sp_BackupDatabases @backupLocation=’D:\SQLBackups\’,@backupType=’L'”

Example 4: Full backups of the database USERDB in the local named instance of SQLEXPRESS by using Windows Authentication

// Sqlbackup.bat

sqlcmd -S .\SQLEXPRESS -E -Q “EXEC sp_BackupDatabases @backupLocation=’D:\SQLBackups\’, @databaseName=’USERDB’, @backupType=’F'”

Similarly, you can make a differential backup of USERDB by pasting in ‘D’ for the @backupType parameter and a log backup of USERDB by pasting in ‘L’ for the @backupType parameter.

@@@ 🙂

To find records which exist in source table but not in target table:

SELECT * FROM t1 WHERE NOT EXISTS (SELECT * FROM t2 WHERE t2.Id = t1.Id)

or

SELECT * FROM t1 LEFT OUTER JOIN T2 on t1.Id = t2.Id WHERE t2.Id IS NULL

If the primary key consists of more than one column, you can modify SQL statement:

SELECT Id, Col1 FROM t1 WHERE NOT EXISTS
(SELECT 1 FROM t2 WHERE t1.Id = t2.Id AND Col1.t1 = Col2.t2)

On SQL Server 2005 or newer you can use the EXCEPT operator:

SELECT Id, Col1 FROM t1 EXCEPT SELECT Id, Col1 FROM t2

To find records which exist in source table but not in target table, as well as records which exists in target table but not in source table:

SELECT * FROM (SELECT Id, Col1 FROM t1, ‘old’
UNION ALL
SELECT Id, Col1 FROM t2, ‘new’) t
ORDER BY Id

Note: For tables with large amounts of data UNION statement might be very slow.

Declare @SearchText nvarchar(max)
set @SearchText = ‘sah.CheckInCheckOutDateTime>=”2011-09-28 10:00:00.000”’

Declare @SchoolYearId uniqueidentifier
set @SchoolYearId=’25511F7F-8F87-4085-A242-2004F4419D36′

Declare @Str as nvarchar(max)
set @Str=’
select s.LastName +”, ” + s.FirstName as ”Name”,CheckInCheckOut,sah.CheckInCheckOutDateTime as ”ClockOut”
from StaffAttendenceHistory sah
join Staffschoolyear ssy on sah.staffschoolyearid = ssy.id
join Staff s on ssy.staffid = s.id
where [email protected]’

If(LEN(ISNULL(@SearchText,0))>0)
set @Str = @Str + ‘ and ( ‘+ @SearchText + ‘)’

set @Str = @Str + ‘ order by CheckInCheckOutDateTime asc ‘

Declare @tbGI table (Id int identity(1,1), Name nvarchar(50), CheckInCheckOut bit, CheckInCheckOutDateTime datetime)
insert into @tbGI(Name,CheckInCheckOut,CheckInCheckOutDateTime)
exec sp_executesql @Str,N’@i uniqueidentifier’,@[email protected]

select * from @tbGI