Using TOP in DELETE statement for T-SQL

Suppose we have a WHILE loop for deleting specified amount of data batches in each loop. Simple solution is to use TOP operator in loop for specifying the number of rows to be deleted in each batch. But it generates error while trying to use TOP operator in delete operation. Let us have a look at the problem.

-- Create a table with only one column in it
CREATE TABLE DemoTable (id SMALLINT identity(1, 1))
GO

-- Insert 100 values in identity column
INSERT INTO DemoTable DEFAULT VALUES
GO 100

-- Verify the values
SELECT * FROM DemoTable
GO

-- Try to delete statement for top 10 rows
DELETE TOP 10 FROM DemoTable

but following error message is generated
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '10'.

It looks that TOP operator is not working in the DELETE statement. So can we use the TOP operator in DELETE operations or not?

Answer is YES, we may use TOP operator in DELETE operations but with little modification. Correct statement is as under with brackets around the number.

-- Using TOP in delete statement
DELETE TOP (10) FROM DemoTable

So we have deleted the 10 records from demo table by using TOP operator in DELETE statement. Similarly instead of constant number any variable may be used in brackets with TOP.

-- Drop DemoTable
DROP TABLE DemoTable

However it is notable that according to BOL
"TOP cannot be used together with UPDATE and DELETE statements on partitioned views.".

Count characters in MS Word 2007 document

Working with my article for MSSQLTips.com i was required to put roughly 4000 characters in code of a stored procedure. After pasting the text in a word document i looked for any information about characters. However word 2007 was displaying number of words at bottom bar.

So in this situation the easy and handy method to get count for characters, words, paragraphs, pages and lines just click on the words count being displayed at bottom bar of document. It would provide all required information.

I hope there would be other methods to access this information but i think this one is most handy and applicable to other versions of MS Word.

Drop specific tables through a stored procedure by using Cursor

Suppose we have to create a procedure which would drop all the tables having a given naming convention. Procedure would utilize a cursor and dynamic SQL. Table names would be pulled from SYS.Objects catalog. Here is the commented procedure for demo

/*
Demo would drop the required tables
by using a cursor in following steps

1. Create tables for drop demo
2. Verify the created tables
3. Create USP
4. Create cursor in USP to pull and execute the drop commands
5. Execute the procedure
6. Verify that tables do not exist now
*/

-- Create Tables
create table rep1 (id smallint)
GO
create table rep2 (id smallint)
GO
create table rep3 (id smallint)
GO
create table rep4 (id smallint)
GO

-- verify the created tables
SELECT name FROM SYS.OBJECTS
WHERE TYPE = 'U'
and name like '%ConventionHere%'
GO

-------------------------------------------------------------
-- Create USP and a cursor in USP
If exists (select name from sys.objects 
where type = 'P' and name = 'USP_DropRepTables')
DROP PROCEDURE USP_DropRepTables
GO

CREATE PROCEDURE USP_DropRepTables
AS
-- Declare variable for SQL command
DECLARE @dropQuery NVARCHAR(400)

-- Declare and populate cursor with required tables
DECLARE DropLoopCursor CURSOR FOR
SELECT 'DROP Table '+name FROM SYS.OBJECTS
WHERE TYPE = 'U'
and name like '%ConventionHere%'

-- Open cursor
OPEN DropLoopCursor

-- Fetch record from cursor
FETCH NEXT FROM DropLoopCursor INTO @dropQuery

-- Configure while loop in cursor
WHILE (@@FETCH_STATUS <> -1)
BEGIN

-- Execute Dynamical SQL
execute (@dropQuery)

-- Fetch next recod
FETCH NEXT FROM DropLoopCursor INTO @dropQuery
END

-- Close and deallocate the cursor
CLOSE DropLoopCursor
DEALLOCATE DropLoopCursor
------------------------------------------------------------
-- End the procedure
GO

-- Execute the procedure
EXECUTE USP_DropRepTables
GO

-- Verify that tables do not exist now
SELECT name FROM SYS.OBJECTS
WHERE TYPE = 'U'
and name like '%ConventionHere%'
GO

You may copy and paste the whole code in SSMS query pane for a better view of comments. Also it is required to take care that given naming convention should not be used by any such table that is not required to drop.

Log backups fail after changing the SIMPLE recovery model

Recently a scenario was shared with me where maintenance plan was failed to create the log backups. Actually recovery model of DB was set to SIMPLE to prevent the log file population for some log intensive bulk operations. After completing the operations recovery model was put back to FULL. Every thing was OK till this point but maintenance plan job began to failed later for creating log backups of that database with following error

BACKUP LOG cannot be performed because there is no current database backup.

The reason for error is that after changing the recovery model of DB from SIMPLE to BULK LOGGED or FULL, we have to create a full or differential backup before going for log backup.
Solution to avoid such error is simple that we have to go in following sequence

• Change recovery model to simple
• Complete the required operations
• Change back to FULL or BULK LOGGED recovery model
• Create FULL or DIFFERENTIAL backup
• Successfully create log backups

Here is a quick demo to simulate the above steps

USE [master]
GO
-- Set the DB to simple recovery model
ALTER DATABASE [DBDigger]
SET RECOVERY SIMPLE
GO

-- Set the DB to full recovery model
ALTER DATABASE [DBDigger]
SET RECOVERY FULL
GO

-- Try to Backup the log, it would fail with error
Backup LOG DBDIgger
to Disk = 'C:\DBD-LogBackup.Log'
GO

-- Now Create full or differential backup of database
Backup DATABASE DBDIgger
to Disk = 'C:\DBD-FullBackup.bak'
GO


-- Try to Backup the log, it would be OK
Backup LOG DBDIgger
to Disk = 'C:\DBD-LogBackup.Log'
GO

Get list of scheduled jobs and associated steps

I am required to generate a list of SQL Server scheduled jobs along with there associated steps. It would be compared to same list from another server where the jobs just have been transferred. Following code would provide me job names along with their steps

USE MSDB
GO

SELECT v.name AS JobName,  step_name 
FROM sysjobs_view v
inner join sysjobsteps t ON v.job_id = t.job_id
ORDER BY v.name, step_name
GO 

We may add further properties of job or step from SYSJOBS_VIEW and SYSJOBSTEPS respectively.

Implementing retention span for SQL Server backup device

SQL Server uses logical devices or media to perform database backups. Backups may be append or overwrite to existing backups in these media sets or devices. It may be a serious issue when a backup in device is silently overwritten without your notice. This scenario may occur if you repeat a previous backup device name with INIT option. Consider the following example for this scenario

-- Create a backup
BACKUP DATABASE DBDIGGER
TO DISK = 'E:\DBD-BackupDemo.bak'
GO

/*
Create another backup with same device name.
By default backup would be performed by using
WITH NOINIT option and would be appended
*/
BACKUP DATABASE DBDIGGER
TO DISK = 'E:\DBD-BackupDemo.bak'
GO

-- Verify the two backups on backup device
RESTORE HEADERONLY FROM DISK = 'E:\DBD-BackupDemo.bak'
GO

Here we are shown two backups on the device. It shows that second backup was also appended to the device. Going further we would issue another backup command by using WITH INIT. This time backup would overwrite the existing backups on the device.

/*
Create another backup with same device name and INIT option.
It would overwrite the existing backups on device.
*/
BACKUP DATABASE DBDIGGER
TO DISK = 'E:\DBD-BackupDemo.bak'
WITH INIT
GO

Now verify that only last backup exists on the device and all previous backups were overwritten.

/*
Verify that previous backups are overwritten
leaving behind the last backup
*/
RESTORE HEADERONLY FROM DISK = 'E:\DBD-BackupDemo.bak'
GO

RETAINING THE BACKUPS ON THE DEVICE

At this point we require some way to ensure that backups on the device would not be overwritten even by accidentally using the WITH INIT option. There may be two approaches to make sure the retention of backups on the device for a specified time.

1. Use WITH EXPIREDATE or WITH RETAINDAYS
2. Set retention days server wide for all databases

 Following is a brief description of these two options

WITH EXPIREDATE | WITH RETAINDAYS days

This option operates along with backup command for a specific device of a database. We may specify a date or days along with WITH EXPIREDATE or WITH RETAINDAYS option respectively.  Here we would create a backup device with retention span of two days

-- Create a backup with retention span 2 days
BACKUP DATABASE DBDIGGER
TO DISK = 'E:\DBD-BackupDemo2.bak'
WITH RETAINDAYS = 2
GO

Now create a backup in same device along with using WITH INIT option. This time error would be generated because backups in the device are retained for two days. 

/*
Create another backup with same device name and INIT option.
It would overwrite the existing backups on device.
*/
BACKUP DATABASE DBDIGGER
TO DISK = 'E:\DBD-BackupDemo2.bak'
WITH INIT
GO

If next backups are created in the device without any retention parameter even then device would maintain the backups with retention parameter and could not be overwritten for next two days. We may get the expiration date or retention span of files by using RESTORE HEADERONLY. 

Similarly EXPIREDATE option may be used to specify an expiration date to which backups on the device would be retained. Date provided should be according to system configured date format.

SET RETENTION DAYS AT SERVER LEVEL

We  have another option to include the fixed retention days for every backup device of databases on that very server. For using this method 

• Right click on instance 
• Go to properties 
• Go to database settings in the left panel
• Set default media retention span in days here. By default it is zero

The retention days span here would automatically be implemented for every backup device of that server without explicit use of EXPIREDATE or RETAINDAYS. 

NOTE:

 Although WITH INIT would not be able to overwrite the retained media but WITH FORMAT may overwrite the header and content of device.

Get current connection context through SUSER_SNAME

If you are required to get the current connection context then SUSER_SNAME() function may be used. Primarily it is meant to return the user when security identification number (SID) is passed as parameter. However without passing parameter it would return the current connection context in terms of user. This information may be more helpful when used along with impersonation. Here is a simple example to use the SUSER_SNAME.

SELECT SUSER_SNAME() 
GO