Template for creating index script in sql server:
--------------------------------------------------------
/******************************************************************
======== BEGIN MAINTENANCE WRAPPER ==========
******************************************************************/
-- Check if the index already exists
IF EXISTS ( SELECT 1
FROM sysindexes sysind--------------------------------------------------------
/******************************************************************
======== BEGIN MAINTENANCE WRAPPER ==========
******************************************************************/
-- Check if the index already exists
IF EXISTS ( SELECT 1
,sysobjects sysobj
WHERE sysind.name = 'IX_INDEX_NAME'
AND sysind.id = sysobj.id
AND sysobj.name = 'TBL_TABLE_NAME'
AND sysobj.uid = user_id('dbo')
AND sysobj.type = 'U')
-- P = Proc; V = View; U = Table; TR = Trigger
BEGIN
-- If it exists, drop to recreate the index on table TBL_TABLE_NAME
DROP INDEX dbo.TBL_TABLE_NAME.IX_INDEX_NAME
PRINT 'INDEX IX_INDEX_NAME OF TABLE TBL_TABLE_NAME DROPPED SUCCESSFULLY'
END
ELSE
BEGIN
PRINT 'INDEX IX_INDEX_NAME OF TABLE TBL_TABLE_NAME DROPPED FAILED'
END
GO
/******************************************************************
======== END MAINTENANCE WRAPPER ==========
******************************************************************/
/********************************************************
** REVISION HISTORY : *
** ====================== *
** DATE VERSION NAME COMMENTS *
** ---------- ------- ----------------- -----------------*
** MM/DD/CCYY 1.0 Developer Name Initial Draft *
** *
*****************************************************************/
CREATE UNIQUE CLUSTERED INDEX IX_INDEX_NAME
ON TBL_TABLE_NAME
( COLUMN_NAME1
,COLUMN_NAME2
)
GO
/******************************************************************
======== BEGIN MAINTENANCE WRAPPER ==========
******************************************************************/
-- Check for errors in creating the index
IF EXISTS (SELECT 1
FROM sysindexes sysind
,sysobjects sysobj
WHERE sysind.name = 'IX_INDEX_NAME'
AND sysind.id = sysobj.id
AND sysobj.name = 'TBL_TABLE_NAME'
AND sysobj.uid = user_id('dbo')
AND sysobj.type = 'U')
-- P = Proc; V = View; U = Table; TR = Trigger
BEGIN
PRINT 'INDEX IX_INDEX_NAME ON TABLE TBL_TABLE_NAME CREATED SUCCESSFULLY'
END
ELSE
BEGIN
PRINT 'INDEX IX_INDEX_NAME ON TABLE TBL_TABLE_NAME CREATION FAILED'
END
GO
/******************************************************************
======== END MAINTENANCE WRAPPER ==========
******************************************************************/
