How to generate ROWNUM in SQL Server SELECT

It is often required to create unique values for every row in a result set query in SQL Server. But SQL Server doesn’t have any built in functions such as ROWNUM in oracle to support this feature. There are many ways in which we can generate unique numbers in the select query as shown in the below scenario

Consider the Employee table with EmployeeName and EmployeeID columns and the scenario is to select records from the table based on EmployeeID with uniqueid for each row in the result set. The select statement can be written in below ways

Create table Employee ( EmployeeID INT , EmployeeName Varchar(30))
ROWNUM using ROW_NUMBER
SELECT ROW_NUMBER () OVER (ORDER BY EmployeeID) AS RowNumber, EmployeeName
FROM Employee ORDER BY EmployeeID
ROWNUM using IDENTITY
SELECT IDENTITY(int, 100, 1) AS RowNumber , EmployeeName
INTO #tmp
FROM Employee ORDER BY EmployeeID
SELET RowNumber , EmployeeID FROM #tmp
ROWNUM using NEWID
SELECT NEWID() ,AS RowNumber, EmployeeName FROM Employee
ORDER BY EmployeeID
Related article:
http://samsudeenb.blogspot.com/2009/06/how-to-generate-sequence-number-in-sql.html

Script to RESEED Database in SQL Server 2005

It is often required to RESEED the database tables with new range of identifiers especially when the table’s Primary Key column is designed with data types such as BIGINT/INT.

The following script allows us to RESEED the database tables with the new SEED value for all the user tables with identity column.

-- Declaration statement to capture the new SEED value
DECLARE @DesiredSeed VARCHAR(20)
SET @DesiredSeed = '5000000000'

DECLARE @TableName VARCHAR(256), @SQLStatement VARCHAR(1000)

-- Cursor to get the list of all user tables which have identity columns
DECLARE curIdentityTables CURSOR
FOR
SELECT b.TABLE_SCHEMA +'.'+ OBJECT_NAME (a.[id])
FROM sysobjects a INNER JOIN INFORMATION_SCHEMA.COLUMNS b
ON OBJECT_NAME(a.id) = b.TABLE_NAME and b.ORDINAL_POSITION = 1
WHERE OBJECTPROPERTY (a.[id], 'IsUserTable') = 1
and OBJECT_NAME (a.[id]) <> 'dtproperties'
and OBJECTPROPERTY (a.[id], 'TableHasIdentity') = 1
ORDER BY a.[name]

--Open the cursor to loop through the table list and reseed it
OPEN curIdentityTables
FETCH NEXT FROM curIdentityTables INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQLSTatement = 'DBCC CHECKIDENT (''' + @TableName + ''', RESEED, ' + @DesiredSeed + ')'
EXEC (@SQLSTatement)
FETCH NEXT FROM curIdentityTables INTO @TableName
END

CLOSE curIdentityTables
DEALLOCATE curIdentityTables

Index maintenance using DMV’s in SQL Server 2005

SQL Server 2005 provides more flexible ways handling index maintenance activities using DMV (Dynamic Management View’s). The DMV’s can be effectively used to identify the index status such as

• Indexes that requires maintenance activities such as reindexing / reorganizing
• List of indexes that are going for index scan
• List of not used indexes

Tables with indexes that require maintenance

SELECT OBJECT_NAME(OBJECT_ID) TableName,
( SELECT NAME FROM SYS.INDEXES
WHERE OBJECT_ID = A.OBJECT_ID
AND INDEX_ID = A.INDEX_ID) IndexName
FROM SYS.DM_DB_INDEX_USAGE_STATS A
WHERE USER_SEEKS >0 AND USER_SCANS >0
AND OBJECT_ID > 97
AND DATABASE_ID = 5
ORDER BY USER_UPDATES, USER_SEEKS DESC

Tables with Indexes going for Scan

SELECT OBJECT_NAME(OBJECT_ID) TableName ,
( SELECT NAME FROM SYS.INDEXES
WHERE OBJECT_ID = A.OBJECT_ID
AND INDEX_ID = A.INDEX_ID) IndexName
FROM SYS.DM_DB_INDEX_USAGE_STATS A
WHERE INDEX_ID <> 0
AND OBJECT_ID > 97
AND DATABASE_ID = 5
AND USER_SCANS > 0 ORDER BY USER_SCANS DESC

Tables with not used indexes

SELECT OBJECT_NAME(OBJECT_ID) TableName,
(SELECT NAME FROM SYS.INDEXES
WHERE OBJECT_ID = A.OBJECT_ID
AND INDEX_ID = A.INDEX_ID) IndexName
FROM SYS.DM_DB_INDEX_USAGE_STATS A
WHERE USER_SEEKS = 0
AND USER_SCANS =0
AND USER_LOOKUPS =0
AND USER_UPDATES = 0
AND OBJECT_ID > 97
AND INDEX_ID <> 0
AND DATABASE_ID = 5

Parallel Data Loading using SQL Server 2005 partition techniques (BCP)

It is common in Enterprise scale projects to simulate the test environment with very large-scale databases. The preparation of real time data and loading consumes considerable amount of time during the environment setup. The SQL Server 2005 partition techniques allow parallel data loading into the tables using BCP / Bulk Insert statements. The data loading of 10 Million records into “Sales” tables using the partition and non-partition methods is explained below.

Data loading with out Partition

CREATE TABLE Sales
(
UniqueID BIGINT,
ItemName VARCHAR(100),
SaledAmount NUMERIC(10,3),
SalesDate DATETIME
)on [Primary]

The data loading for this table can be done using the BCP / Bulk Insert options. But we cannot load the data in parallel, as it will lead to table locking

Data loading with Partition

CREATE TABLE Sales(
ItemName VARCHAR(100),
SaledAmount NUMERIC(10,3),
SalesDate DATETIME,
PartitionID INT
)on fPartitionID(PartitionID)

The table “Sales” is partitioned using the column PartitionID into 10 different partitions (say PartitionID accepts value between 1.10)
As the table is split into 10 different partitions, data loading of this table can be done in parallel follows

• Generate the data into to 10 X 1 Million files
• Load data into the table using 10 BCP / Bulk Insert instances in parallel

Conclusion

The performance of the data loading can be improved up to 10 times if the table is partitioned. This option can be preferred only if there are sufficient hardware resources. As CPU usage of BCP is very high, the number of parallel instances can be reduced as per the resource availability

Restrict SQL Server Login –SQL Server 2005

There is always a need to restrict the user logins into the SQL Server databases. Many DBA’s face this difficulty, as the restriction of logins has to be done for different users with different requirements. SQL Server 2005 introduces an option called “LOGON” triggers which is an easy way restrict the number of user logins as per our requirements.

This Logon trigger is created directly on the database server and registered on the master database. The below sample demonstrates the use of login triggers to restrict the user “john” from accessing the database using “SQL Query Analyzer” window.


USE master
GO
CREATE TRIGGER trgRestrictUser
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
IF (ORIGINAL_LOGIN()= 'john' AND APP_NAME() = 'Microsoft SQL Server Management Studio - Query')
ROLLBACK;
END;

This logon trigger can be used for various auditing purposes in SQL Server. This is a new feature introduced in the SQL Sever 2005 Service Pack 2.We need to upgrade to SP2 to use this feature.

Below links can give more information about Logon triggers
http://msdn2.microsoft.com/en-us/library/bb326598.aspx

SQL Server TempDB useful tips

Moving Temp Database Files
Step 1:
Move the tempdb files to a new physical location (mostly on disk drive in RAID protection 1, 1 + 0 or 5 )
use master
GO
Alter database tempdb modify file (name = tempdev, filename = '\tempdev.mdf')
GO
Alter database tempdb modify file (name = templog, filename = '\templog.ldf')
GO
Step 2:
Restart the SQL Server service
Step 3:
On Restart the tempdb will be created with the new location specified
Step 4:
Remove the data and log file of the tempdb from the old location

Starting SQL Server without TempDB
SQL Server cannot operate with out the tempdb database.When the tempdb filesare corrupted / deleted accidentaly we can restart the SQL Server using the following commandline utlitity
SQLSERVER.exe –s -f -c -T3609

TempDB Best Practices
The below link explains some of the best practices on using the tempdb
http://www.mssqltips.com/tip.asp?tip=1432

Scripts to Drop and ReCreate Indexes in SQL Server 2005

SQL Server 2005 “GENERATE SCRIPTS” wizard does not have the option to generate the Index scripts alone from the database. The below script can be used to DROP and recreate the Indexes from the database.

Steps to be followed

1. Create a function to retrieve the list of key columns used in the Index definition (this function is useful for retrieving the multiple index columns in case of composite Index)

CREATE FUNCTION fGetIndexCols
(
@objname VARCHAR(50),
@indid INT
)
RETURNS NVARCHAR(131)
AS
BEGIN

DECLARE @keys NVARCHAR(2126)
DECLARE @i int, @thiskey NVARCHAR(131) , @objid BIGINT

SELECT @objid = object_id(@objname)
SELECT @keys = index_col(@objname, @indid, 1), @i = 2

IF (indexkey_property(@objid, @indid, 1, 'isdescending') = 1)
SELECT @keys = @keys + '(-)'
SELECT @thiskey = index_col(@objname, @indid, @i)
IF ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
SELECT @thiskey = @thiskey + '(-)'
WHILE (@thiskey is not null )
BEGIN

SELECT @keys = @keys + ', ' + @thiskey, @i = @i + 1
SELECT @thiskey = index_col(@objname, @indid, @i)
IF ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
SELECT @thiskey = @thiskey + '(-)'

END
RETURN (@KEYS)

END

2. Execute the below script to generate the CREATE INDEX statements from the database. I have written this script to generate the scripts for NON CLUSTERED indexes for clustered indexes change the where clause type_desc = ‘CLUSTERED’

SELECT 'CREATE ' +
CASE IS_UNIQUE WHEN 1 THEN 'UNIQUE ' ELSE ' ' END +
'NONCLUSTERED ' + ' INDEX '+
NAME + ' ' +' ON ' +
OBJECT_NAME (OBJECT_ID) +
'('+
DBO.fGetIndexCols (object_NAME(OBJECT_ID), index_id) +
')'+
' ON ['+
( SELECT GROUPNAME
FROM SYSFILEGROUPS
WHERE GROUPID = DATA_SPACE_ID
) + ']' IndexScript
FROM SYS.INDEXES
WHERE NAME IS NOT NULL
AND Is_Primary_Key =0
AND type_desc ='NONCLUSTERED'
AND OBJECT_ID > 97

3. Execute the below script to generate the DROP INDEX statements

SELECT ' DROP INDEX' + OBJECT_NAME (OBJECT_ID) +'.'+ NAME
FROM SYS.INDEXES
WHERE NAME IS NOT NULL
AND Is_Primary_Key =0
AND type_desc ='NONCLUSTERED'
AND OBJECT_ID > 97
I have continued this article with a modified query to generate index using included columns http://samsudeenb.blogspot.com/2009/07/this-article-is-continuation-of_09.html