Deleting Duplicate Records

CREATE TABLE #Employee(
Employee_ID [int] Null,
Employee_Name varchar(20) Null,
Employee_Dept varchar(20) Null
) ;

Insert into #Employee VALUES (1, 'Mark Dunn', 'HR');
Insert into #Employee VALUES (1, 'Mark Dunn', 'HR');
Insert into #Employee VALUES (2, 'John Ciesel', 'Production');
Insert into #Employee VALUES (2, 'John Ciesel', 'Production');
Insert into #Employee VALUES (2, 'John Ciesel', 'Production');
Insert into #Employee VALUES (3, 'Anna Dolf', 'Manufacturing');


-- Selecting Distinct Records
With CTE_Employee
AS
(
Select Employee_ID, Employee_Name, Employee_Dept,
ROW_NUMBER()
OVER (PARTITION BY Employee_ID, Employee_Name, Employee_Dept
ORDER BY Employee_ID, Employee_Name, Employee_Dept) AS RowNumber
from #Employee
)

SELECT Employee_ID, Employee_Name, Employee_Dept,RowNumber
FROM CTE_Employee
WHERE RowNumber = 1;


-- Selecting Duplicate Records
With CTE_Employee
AS
(
Select Employee_ID, Employee_Name, Employee_Dept,
ROW_NUMBER()
OVER (PARTITION BY Employee_ID, Employee_Name, Employee_Dept
ORDER BY Employee_ID, Employee_Name, Employee_Dept) AS RowNumber
from #Employee
)

SELECT Employee_ID, Employee_Name, Employee_Dept,RowNumber
FROM CTE_Employee
WHERE RowNumber > 1;


-- Deleting Duplicate Records
With CTE_Employee
AS
(
Select Employee_ID, Employee_Name, Employee_Dept,
ROW_NUMBER()
OVER (PARTITION BY Employee_ID, Employee_Name, Employee_Dept
ORDER BY Employee_ID, Employee_Name, Employee_Dept) AS RowNumber
from #Employee
)

DELETE
FROM CTE_Employee
WHERE RowNumber > 1;

Monitoring Disk Space and Sending Alerts with TSQL

CREATE PROCEDURE usp_DiskFreeSpaceAlert
 @DriveCBenchmark int = 1024,
 @OtherDataDriveBenchmark int = 2048
AS
--By: Haidong "Alex" Ji  This procedure sends out an alert message when hard disk space is below a predefined value. This procedure can be scheduled to run daily so that DBA can act quickly to address this issue.
IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'[tempdb]..[#disk_free_space]'))
DROP TABLE #disk_free_space
CREATE TABLE #disk_free_space (
 DriveLetter CHAR(1) NOT NULL,
 FreeMB INTEGER NOT NULL)

DECLARE @DiskFreeSpace INT
DECLARE @DriveLetter CHAR(1)
DECLARE @AlertMessage VARCHAR(500)
DECLARE @MailSubject VARCHAR(100)

/* Populate #disk_free_space with data */
INSERT INTO #disk_free_space
 EXEC master..xp_fixeddrives

SELECT @DiskFreeSpace = FreeMB FROM #disk_free_space where DriveLetter = 'C'

IF @DiskFreeSpace < @DriveCBenchmark
Begin
SET @MailSubject = 'Drive C free space is low on ' + @@SERVERNAME
SET @AlertMessage = 'Drive C on ' + @@SERVERNAME + ' has only ' +  CAST(@DiskFreeSpace AS VARCHAR) + ' MB left. Please free up space on this drive. C drive usually has OS installed on it. Lower space on C could slow down performance of the server'
-- Send out email
EXEC master..xp_sendmail @recipients = 'MyEmail@MyCompany.com',
@subject = @MailSubject,
@message = @AlertMessage
End

DECLARE DriveSpace CURSOR FAST_FORWARD FOR
select DriveLetter, FreeMB from #disk_free_space where DriveLetter not in ('C')

open DriveSpace
fetch next from DriveSpace into @DriveLetter, @DiskFreeSpace

WHILE (@@FETCH_STATUS = 0)
Begin
if @DiskFreeSpace < @OtherDataDriveBenchmark
Begin
set @MailSubject = 'Drive ' + @DriveLetter + ' free space is low on ' + @@SERVERNAME
set @AlertMessage = @DriveLetter + ' has only ' + cast(@DiskFreeSpace as varchar) + ' MB left. Please increase free space for this drive immediately to avoid production issues'

-- Send out email
EXEC master..xp_sendmail @recipients = 'MyEmail@MyCompany.com',
@subject = @MailSubject,
@message = @AlertMessage
End
fetch next from DriveSpace into @DriveLetter, @DiskFreeSpace
End
close DriveSpace

deallocate DriveSpace

DROP TABLE #disk_free_space

GO

This demonstrates 2005's ability to bring back filegroups one at a time,

/* SQL Server 2005 Testing */

/* Create our test database */


CREATE DATABASE [MultipleFilesDB] ON  PRIMARY
( NAME = N'MultipleFilesDB_Primary', FILENAME = N'e:\mssql.1\MSSQL\data\MultipleFilesDB_Primary.mdf' , SIZE = 4096KB , FILEGROWTH = 10%),
FILEGROUP [SECONDARY]
( NAME = N'MultipleFilesDB_Secondary', FILENAME = N'e:\mssql.1\MSSQL\data\MultipleFilesDB_Secondary.ndf' , SIZE = 4096KB , FILEGROWTH = 10%)
LOG ON
( NAME = N'MultipleFilesDB_log', FILENAME = N'e:\mssql.1\MSSQL\data\MultipleFilesDB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'MultipleFilesDB', @new_cmptlevel=90
GO

ALTER DATABASE [MultipleFilesDB] SET RECOVERY FULL
GO



USE [MultipleFilesDB]
GO

IF NOT EXISTS (SELECT groupname FROM dbo.sysfilegroups WHERE (status & 0x10) != 0 AND groupname = N'PRIMARY') ALTER DATABASE [MultipleFilesDB] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

/* Create a test table on each filegroup */

USE [MultipleFilesDB]
GO

/****** Object:  Table [dbo].[Orders]    Script Date: 01/12/2009 19:47:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[OrdersPrimary](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[OrderDate] [datetime] NULL,
CONSTRAINT [PK_OrdersPrimary] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO



CREATE TABLE [dbo].[OrdersSecondary](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[OrderDate] [datetime] NULL,
CONSTRAINT [PK_OrdersSecondary] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
) ON [SECONDARY]
) ON [SECONDARY]
 GO



/* Populate the first order in each table */

INSERT INTO dbo.OrdersPrimary (OrderDate) VALUES (GETDATE())
INSERT INTO dbo.OrdersSecondary (OrderDate) VALUES (GETDATE())

/* Do a full backup */

BACKUP DATABASE [MultipleFilesDB] TO  DISK = N'e:\mssql.1\MSSQL\BACKUP\MultipleFilesDB.bak' WITH NOFORMAT, INIT,
NAME = N'MultipleFilesDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

/* Add another set of rows so we can tell if the t-logs have been applied */

INSERT INTO dbo.OrdersPrimary (OrderDate) VALUES (GETDATE())
INSERT INTO dbo.OrdersSecondary (OrderDate) VALUES (GETDATE())



/* Do a transaction log backup */

BACKUP LOG [MultipleFilesDB] TO  DISK = N'e:\mssql.1\MSSQL\BACKUP\MultipleFilesDB.trn' WITH NOFORMAT, INIT,
NAME = N'MultipleFilesDB-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO


/* Restore just the primary filegroup */

USE master
GO

RESTORE DATABASE [MultipleFilesDB]
FILE = N'MultipleFilesDB_Primary'
FROM  DISK = N'E:\MSSQL.1\MSSQL\Backup\MultipleFilesDB.bak'
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO

/* You'll get an error saying more data is required to come completely online. That's OK.  Now restore the second filegroup: */

RESTORE DATABASE [MultipleFilesDB]
FILE = N'MultipleFilesDB_Secondary'
FROM  DISK = N'E:\MSSQL.1\MSSQL\Backup\MultipleFilesDB.bak'
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO

/* Refresh your list of databases in SSMS, and presto, the database is online.  This is different than 2000 handled it. */

/* Now let's try it again, but we'll add the new "PARTIAL" parameter at the end telling SQL that we're doing a piecemeal restore: */

RESTORE DATABASE [MultipleFilesDB]
FILE = N'MultipleFilesDB_Primary'
FROM  DISK = N'E:\MSSQL.1\MSSQL\Backup\MultipleFilesDB.bak'
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10, PARTIAL
GO


/* Refresh your list of databases in SSMS, and the database is already online! Let's do a select; */

SELECT * FROM MultipleFilesDB.dbo.OrdersPrimary



/* It shows one row returned because we didn't get our transaction logs.  Now select from the secondary filegroup: */

SELECT * FROM MultipleFilesDB.dbo.OrdersSecondary



/* You'll get an error that the table is in a filegroup that isn't online yet. So let's bring it online. */

RESTORE DATABASE [MultipleFilesDB]
FILE = N'MultipleFilesDB_Secondary'
FROM  DISK = N'E:\MSSQL.1\MSSQL\Backup\MultipleFilesDB.bak'
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO


/* And do another select */
SELECT * FROM MultipleFilesDB.dbo.OrdersSecondary


/* Presto, we're back online. This demonstrates 2005's ability to bring back filegroups one at a time,

but it only works if you restore the primary first.  Watch what happens if we try to restore the second one by itself: */

RESTORE DATABASE [MultipleFilesDB]
FILE = N'MultipleFilesDB_Secondary'
FROM  DISK = N'E:\MSSQL.1\MSSQL\Backup\MultipleFilesDB.bak'
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO

/* Uh oh - it warns us that additional roll forwards are required, and sure enough, if we try a select - */

SELECT * FROM MultipleFilesDB.dbo.OrdersSecondary


/* It fails. The filegroup is offline until we bring it up to speed with matching transaction logs - even though we didn't change it!

So we can't just go restoring filegroups at random without doing some t-log syncing.  The database will be online, but the

restored filegroup may not be online yet. */

Last Accesses Stored Procedure

SELECT  a.execution_count ,
    OBJECT_NAME(objectid) Name,
    query_text = SUBSTRING(
    b.text,
    a.statement_start_offset/2,
    ( CASE WHEN a.statement_end_offset = -1
    THEN len(convert(nvarchar(max), b.text)) * 2
    ELSE a.statement_end_offset
    END - a.statement_start_offset)/2
    ) ,
    b.dbid ,
    dbname = db_name(b.dbid) ,
    b.objectid ,
    a.creation_time,
    a.last_execution_time,
    a.*
FROM     sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) as b
WHERE OBJECT_NAME(objectid) like 'sp%'
ORDER BY a.last_execution_time DESC

Insert Rows into Columns in SQL Server

DECLARE @i INT,@SQL VARCHAR(1000),@ColVal varchar(1000);
DECLARE RowDataToColumn CURSOR FOR

SELECT Testing FROM Test1
FOR READ ONLY

OPEN RowDataToColumn
FETCH NEXT FROM RowDataToColumn INTO @colval

SELECT @SQL = 'INSERT INTO Test2 VALUES(',
@i = 0

WHILE @@FETCH_STATUS = 0
BEGIN
if @i = 0 -- First append
SET @SQL = @SQL + '''' + @colval + ''''
else
SET @SQL = @SQL + ', ''' + @colval + ''''

FETCH NEXT FROM RowDataToColumn INTO @colval
SET @i = @i + 1
END
CLOSE RowDataToColumn
DEALLOCATE RowDataToColumn

SET @SQL = @SQL + ')'

EXEC(@SQL) -- This executes insert

Script to collect the wait stats:

-- Isolate top waits for server instance since last restart or statistics clearWITH Waits AS(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rnFROM sys.dm_os_wait_statsWHERE wait_type NOT IN('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR','LOGMGR_QUEUE','CHECKPOINT_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT','CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT','XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN','SQLTRACE_INCREMENTAL_FLUSH_SLEEP'))SELECT W1.wait_type,CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,CAST(W1.pct AS DECIMAL(12, 2)) AS pct,CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pctFROM Waits AS W1INNER JOIN Waits AS W2ON W2.rn <= W1.rnGROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pctHAVING SUM(W2.pct) - W1.pct < 99 OPTION (RECOMPILE); -- percentage thresholdGO

This dynamic management view collects all the information since the time when the SQL Server services have been restarted. You can also manually clear the wait stats using the following command:

DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

Retrieve Date Using Date Fortmat

DECLARE @StartMonth INT,@StartYear VARCHAR(4),@EndMonth INT,@EndYear varchar(4);
SET @StartMonth = 7
SET @StartYear = 2001
SET @EndMonth = 06
SET @EndYear = 2004

SELECT SpecialOfferID,[Description],StartDate,EndDate
FROM Sales.SpecialOffer
WHERE MONTH(StartDate) = @StartMonth AND YEAR(StartDate) = @StartYear
AND MONTH(EndDate) = @EndMonth AND YEAR(EndDate) = @EndYear

SQL Server Logical Reads - What's it?

--SQL Server Logical Reads - What's it?

Logical read indicates total number of data pages needed to be accessed from data cache to process query. It is very possible that logical read will access same data pages many times, so count of logical read value may be higher than actual number of pages in a table. Usually the best way to reduce logical read is to apply correct index or to rewrite the query.

--Create table objects

CREATE TABLE TblLogicalReads
(
TranId INT,
TrnData VARCHAR(100),
TrnDate DATETIME
)
GO



--After creating objects, 49999 records will be inserted by following scripts,


DECLARE @cnt BIGINT
SET @cnt  = 1
WHILE (@cnt < 50000)
BEGIN
INSERT INTO TblLogicalReads (TranId,TrnData,TrnDate)
VALUES (@cnt, 'Demo Records ' + CONVERT(VARCHAR(100),@cnt ), GETDATE() - @cnt)

SET @cnt = @cnt +1

END
GO

--Now we are checking logical reads for the script which are going to be run.

SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT
TranId,
TrnData,
TrnDate
FROM TblLogicalReads
WHERE TranId = 5

SELECT
TranId,
TrnData,
TrnDate
FROM TblLogicalReads
WHERE TrnDate = '2009-12-26 18:10:47.653'
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
GO


-- Creating indexes on tables to reduce it

CREATE CLUSTERED INDEX IX_TranId ON TblLogicalReads(TranId)
GO



CREATE NONCLUSTERED INDEX IX_TrnDate ON TblLogicalReads(TrnDate)
GO

Task Progress

--This query can be executed with any database context. This is written to show the estimated time in hrs, min and sec and also shows percentage of completion. This is quite helpful to check the backup progress, restore progress, integrity check progress, rebuild index task progress etc.

select
convert (varchar(50),(estimated_completion_time/3600000))+'hrs'+
convert (varchar(50), ((estimated_completion_time%3600000)/60000))+'min'+
convert (varchar(50), (((estimated_completion_time%3600000)%60000)/1000))+'sec'
as Estimated_Completion_Time,
status, command, db_name(database_id), percent_complete
from sys.dm_exec_requests

Restore DB

RESTORE DATABASE [Test] FROM  DISK = N'D:\Testing' WITH  FILE = 1,
MOVE N'Testing' TO N'D:\Data\Test.mdf',
MOVE N'Fg1' TO N'D:\Data\Fg1.ndf',
MOVE N'Fg2' TO N'D:\Data\Fg2.ndf',
MOVE N'Testing_log' TO N'D:\Data\Test.LDF',
NOUNLOAD,  REPLACE,  STATS = 10
GO

Backup all file group

/*
Script  : Backup all file group
Version : September 2012
Author  : Sushil Rout
Web     : http://sushildbasupports.blogspot.com
*/

DECLARE @destinationpath NVARCHAR(1000)
DECLARE @beginbackuptimestamp VARCHAR(20)

SET @destinationpath = '\\SERVER-01\sqlbackupshare$\'
SET @beginbackuptimestamp =  REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(30),GETDATE(),20),'-',''),':',''),' ','_')

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#backupcommand%')
   DROP TABLE #backupcommand
 
CREATE TABLE #backupcommand (id INT IDENTITY(1,1), command VARCHAR(1000))

INSERT INTO #backupcommand (command)
SELECT 'BACKUP DATABASE [' + DB_NAME()+'] FILEGROUP = ''' + name + ''' TO DISK = ''' + @destinationpath  + DB_NAME() +  '_' + @beginbackuptimestamp +  '_' + name +'.BAK'''
FROM sys.filegroups
--WHERE name <> 'NCIndexes'
ORDER BY data_space_id

DECLARE @intCounter INT
SET @intCounter = 0

DECLARE @intMaxId INT
SELECT @intMaxId = MAX(ID) FROM #backupcommand

DECLARE @CurrentCommand VARCHAR(1000)

 WHILE (@intCounter <= @intMaxId)

BEGIN
SET @intCounter = @intCounter + 1

  SELECT @CurrentCommand = command FROM #backupcommand
WHERE id = @intCounter
   
    -- display command being executed. using RAISERROR here to display immediately, unlike PRINT
    RAISERROR (@CurrentCommand, 10, 1) WITH NOWAIT
   
    -- do the backup
    EXEC  (@CurrentCommand)
       
END
GO

Script to grant exec permission to all sps in a db

USE [databaseName]
Go
---*********ADD EXEC PERMS TO ALL SPS******--------------
DECLARE @cmd varchar(8000)
DECLARE @objectCount int
DECLARE @OwnerName varchar(128)
DECLARE @ObjectName varchar(128)
DECLARE @user VARCHAR(100) = 'myuser';

CREATE TABLE #StoredProcedures
(OID int IDENTITY (1,1),
StoredProcOwner varchar(128) NOT NULL,
StoredProcName varchar(128) NOT NULL)


INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)
SELECT
u.[Name],
o.[Name]
FROM
dbo.sysobjects o
INNER JOIN dbo.sysusers u
ON o.uid = u.uid
WHERE o.Type = 'P';


SELECT @objectCount = MAX(OID) FROM #StoredProcedures

WHILE @objectCount > 0
BEGIN


SELECT @OwnerName = StoredProcOwner,
@ObjectName = StoredProcName
FROM #StoredProcedures
WHERE OID = @objectCount


SELECT @cmd = 'GRANT EXEC ON ' + '[' + @OwnerName + ']' + '.' + '[' + @ObjectName + ']' + ' TO ' + @user

SELECT @cmd;

EXEC(@cmd);


SET @objectCount = @objectCount- 1;

END

DROP TABLE #StoredProcedures;

What got restored from where, by who and when

Working in a small environment where people have their fingers in many pies can sometimes be a pain.

The developers have full db_owner rights to the databases and can restore the DB to a previous version, this script helped my find who restored what, when they restored it and what backup / server was used as the source database.

Not that I like pointing fingers at who broke something, but could come in handy if someone rolled back the DB and something went missing.

SELECT
DatabaseRestoredTo = RH.destination_database_name,
TimeOfRestore = RH.restore_date,
UserImplimentingRestore = RH.user_name,
RestoreType = CASE RH.restore_type WHEN 'D' THEN 'Full DB Restore'
WHEN 'F' THEN 'File Restore'
WHEN 'G' THEN 'Filegroup Restore'
WHEN 'I' THEN 'Differential Restore'
WHEN 'L' THEN 'Log Restore'
WHEN 'V' THEN 'Verify Only'
END,
ServerWhereBackupTaken = BS.server_name,
UserWhoBackedUpTheDatabase = BS.user_name,
BackupOfDatabase = BS.database_name,
DateOfBackup = BS.backup_start_date,
RestoredFromPath = BMF.physical_device_name
FROM
msdb.dbo.restorehistory RH
INNER JOIN
msdb.dbo.backupset BS
ON
RH.backup_set_id = BS.backup_set_id
INNER JOIN
msdb.dbo.backupmediafamily BMF
ON
BS.media_set_id = BMF.media_set_id
ORDER BY
RH.restore_history_id

Check SQL Job timings

Open Management studio. Use msdb database and execute the script. The user should be sysadmin to execute this script.

Use msdb
go
SELECT name,
   step_id
  ,run_date
  ,count(*) howMany
  ,min((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) lowest_Min
  ,avg((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) average_Min
  ,max((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) highest_Min
  ,stdev((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) stdev_Min
 from sysJobHistory h
inner join sysjobs j  on
(h.job_id = j.job_id)
where name =''
group by name, step_id, run_date
order by run_date desc

All database files growth

Execute the given script which will provide growth details for each file of the databases.

select DB_NAME(database_id) as Database_name,Name,growth,
case is_percent_growth
when 0 then 'In MB'
when 1 then 'In Percent'
end as Typeofgrowth,
case max_size
when 0 then 'Restricted'
when -1 then 'Unrestricted'
when 268435456 then 'Unrestricted'
else convert(varchar(20),max_size/128)
end as Maxsize
 from sys.master_files

Find size of all tables in a database

Declare @v_TableName Varchar(100)
Declare @v_Table Table (Table_Name Varchar(100))
Declare @v_TableSize Table (
Table_Name Varchar(100),
rows BigInt,
Reserved Varchar(50),
ActualDataSize Varchar(50),
IndexSize Varchar(50),
Unused Varchar(50)
)
Insert Into @v_Table
Select Table_Name From Information_Schema.Tables Where Table_Type = 'BASE TABLE'
Select @v_TableName = Table_Name From @v_Table
While Exists (Select 1 From @v_Table)
Begin
Insert Into @v_TableSize exec sp_spaceused @v_TableName
Delete From @v_Table Where Table_Name = @v_TableName
Select @v_TableName= Table_Name From @v_Table
End
Select * From @v_TableSize Order By rows Desc
Go

How SPARSE column saves space in database table.

Example of how SPARSE column saves space in database table.

CREATE TABLE UnSparsed(ID INT IDENTITY(1,1),
FirstCol INT,
SecondCol VARCHAR(100),
ThirdCol SmallDateTime)
GO
CREATE TABLE Sparsed(ID INT IDENTITY(1,1),
FirstCol INT SPARSE,
SecondCol VARCHAR(100) SPARSE,
ThirdCol SmallDateTime SPARSE)
GO
DECLARE @idx INT = 0
WHILE @idx < 50000
BEGIN
INSERT INTO UnSparsed VALUES (NULL,NULL, NULL)
INSERT INTO Sparsed VALUES (NULL, NULL, NULL)
SET @idx+=1
END
GO
sp_spaceused 'UnSparsed'
GO
sp_spaceused 'Sparsed'
GO
DROP TABLE UnSparsed
GO
DROP TABLE Sparsed
GO

Where is the Object ? In which Database ?

I want to use one stored procedure or any other object name contains 'Blog' from current Server or Instance.

But, I don't know where is/are the procedure or object actually exists! or In which database ?

Is there any way to search an object in all the databases in current Instance ?
 
Use  Master 
Go
Declare @WhereIs Table
(DBName Varchar(100),
ObjectName Varchar(150),
ObjectType Varchar(150)
)
Insert @WhereIs
Exec sp_msforeachdb 'use [?]; select DB_NAME(), name, Type_desc from sys.objects where name like ''%Blog%'''

Select * from @WhereIs
Go

To capture current executing statements that are waiting on IO or just finished waiting on IO:

select r.session_id, s.login_name, s.program_name, r.start_time, r.status, r.command, r.wait_type, r.wait_time, r.last_wait_type, r.logical_reads, (r.logical_reads * 8192) as 'KB Read', r.writes, (r.writes * 8192) as 'KB Written', t.[text] from sys.dm_exec_requests r cross apply sys.dm_exec_sql_text(sql_handle) t inner join sys.dm_exec_sessions s on r.session_id = s.session_id where s.is_user_process = 1 and (r.wait_type like 'PAGEIOLATCH%' or r.last_wait_type like 'PAGEIOLATCH%') and r.session_id != @@SPID

Find Next Running Time of Scheduled Job Using T-SQL

USE msdb ;WITH CTE AS (SELECT schedule_id, job_id, RIGHT('0'+CAST(next_run_time AS VARCHAR(6)),6) AS next_run_time, next_run_date FROM sysjobschedules) SELECT A.name Job_Name, 'Will be running today at '+ SUBSTRING(CONVERT(VARCHAR(10), CASE WHEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) > 12 THEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time),1,2) -12 ELSE SUBSTRING (CONVERT(VARCHAR(10),next_run_time),1,2) END),1,2) +':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time),3,2) +':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),5,2) 'Scheduled At' FROM sysjobs A ,CTE B WHERE A.job_id = B.job_id AND SUBSTRING(CONVERT(VARCHAR(10),next_run_date) , 5,2) +'/'+ SUBSTRING(CONVERT(VARCHAR(10),next_run_date) , 7,2) +'/'+ SUBSTRING(CONVERT(VARCHAR(10),next_run_date),1,4) = CONVERT(VARCHAR(10),GETDATE(),101) AND (SUBSTRING( CONVERT(VARCHAR(10), CASE WHEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) > 12 THEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) -12 ELSE SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) END),1,2) +':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),3,2) +':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),5,2)) > SUBSTRING (CONVERT( VARCHAR(30) , GETDATE(),9),13,7)

SQL SERVER – 2008 – Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE

MERGE is a new feature that provides an efficient way to perform multiple DML operations. In previous versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions, but now, using MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it.

One of the most important advantage of MERGE statement is all the data is read and processed only once. In previous versions three different statement has to be written to process three different activity (INSERT, UPDATE or DELETE), however using MERGE statement all update activity can be done in one pass of database table. This is quite an improvement in performance of database query.

Syntax of MERGE statement is as following:
MERGE
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ] target_table [ WITH ( ) ] [ [ AS ] table_alias]
USING
ON
[ WHEN MATCHED [ AND ]
THEN ]
[ WHEN NOT MATCHED [ BY TARGET ] [ AND ]
THEN ]
[ WHEN NOT MATCHED BY SOURCE [ AND ]
THEN ]
[ ]
[ OPTION ( [ ,...n ] ) ]
;
Example:

Let’s create Student Details and StudentTotalMarks and inserted some records.

Student Details:

USE AdventureWorks
GO
CREATE TABLE StudentDetails
(
StudentID INTEGER PRIMARY KEY,
StudentName VARCHAR(15)
)
GO
INSERT INTO StudentDetails
VALUES(1,'SMITH')
INSERT INTO StudentDetails
VALUES(2,'ALLEN')
INSERT INTO StudentDetails
VALUES(3,'JONES')
INSERT INTO StudentDetails
VALUES(4,'MARTIN')
INSERT INTO StudentDetails
VALUES(5,'JAMES')
GO

StudentTotalMarks:

CREATE TABLE StudentTotalMarks
(
StudentID INTEGER REFERENCES StudentDetails,
StudentMarks INTEGER
)
GO
INSERT INTO StudentTotalMarks
VALUES(1,230)
INSERT INTO StudentTotalMarks
VALUES(2,255)
INSERT INTO StudentTotalMarks
VALUES(3,200)
GO



In our example we will consider three main conditions while we merge this two tables.

1.Delete the records whose marks are more than 250.
2.Update marks and add 25 to each as internals if records exist.
3.Insert the records if record does not exists.
Now we will write MERGE process for tables created earlier. We will make sure that we will have our three conditions discussed above are satisfied.

MERGE StudentTotalMarks AS stm
USING (SELECT StudentID,StudentName FROM StudentDetails) AS sd
ON stm.StudentID = sd.StudentID
WHEN MATCHED AND stm.StudentMarks > 250 THEN DELETE
WHEN MATCHED THEN UPDATE SET stm.StudentMarks = stm.StudentMarks + 25
WHEN NOT MATCHED THEN
INSERT(StudentID,StudentMarks)
VALUES(sd.StudentID,25);
GO

There are two very important points to remember while using MERGE statement.

Semicolon is mandatory after the merge statement.
When there is a MATCH clause used along with some condition, it has to be specified first amongst all other WHEN MATCH clause.
After the MERGE statement has been executed, we should compare previous resultset and new resultset to verify if our three conditions are carried out.





AS we can see there are 5 rows updated. StudentID 2 is deleted as it is more than 250, 25 marks have been added to all records that exists i.e StudentID 1,3 and the records that did not exists i.e. 4 and 5 are now inserted in StudentTotalMarks .

MERGE statement is very handy improvement for T-SQL developers who have to update database tables with complicated logic. MERGE statement also improves the performance of database as it passes through data only once.

Grant Execute Permission to User for SP & Functions

--Grants EXECUTE permission to TestUser for all user created procedures.

declare @username varchar(255)
set @username = 'TestUser'
SELECT 'grant exec on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
QUOTENAME(ROUTINE_NAME) + ' TO ' + @username FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0
and ROUTINE_TYPE='PROCEDURE'

-- Grants EXECUTE permission to TestUser for all user created functions.

declare @username varchar(255)
set @username = 'TestUser'
SELECT 'grant exec on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
QUOTENAME(ROUTINE_NAME) + ' TO ' + @username FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0
and ROUTINE_TYPE='FUNCTION'

Backup and Restore History of a Particular DB

-- Backup History

SELECT s.backup_set_id,s.user_name,s.backup_start_date,s.backup_finish_date,
datediff(mi,s.backup_start_date,s.backup_finish_date)as mins,
datediff(s,s.backup_start_date,s.backup_finish_date)as sec,
s.type,s.backup_size,s.database_name,f.physical_device_name,s.server_name,
s.recovery_model
from msdb..backupset s,msdb..backupmediafamily f
where s.media_set_id=f.media_set_id --and s.database_name='{Database name}'
order by s.backup_finish_date DESC


-- Restore History

SELECT h.backup_set_id,h.destination_database_name,f.physical_device_name,
h.restore_date ,h.user_name,h.restore_type,h.replace,s.server_name
from msdb..restorehistory h,msdb..backupmediafamily f,msdb..backupset s
where h.backup_set_id=s.backup_set_id and s.media_set_id=f.media_set_id
--and h.destination_database_name ='{Database name}'
ORDER BY h.restore_date DESC

CREATE PROMO CODE

CREATE PROCEDURE [TestingSP].[prc_PromoCode_INSERT_New]
@chrRoleID CHAR(2),
@intQuantity INT,
@dtsValidUpTo SMALLDATETIME
AS
/*
** 02/12/2010 modifyr
*/
DECLARE @count int

BEGIN
SET NOCOUNT ON;
DECLARE @Eid INT
set @count=0
-- while loop for insert quantity
WHILE @count<@intQuantity
BEGIN
INSERT INTO Testing.PromoCodes(RID,PcCodeValidUpto) VALUES (@chrRoleID,@dtsValidUpTo)
SET @count = @count+1
END
return 1
END
GO