Cry How to...

Create an index on a table unless it already exists

---

To create an index on a table in SQL Server, unless the index has already been created:

if not exists (select * from sysindexes
  where id=object_id('<table_name>') and name='<index_name>')
    create index <index_name> on <table_name>(<field_names>)

where <table_name> is the name of the table, <index_name> is the name of the index and <column_names> are the field names used when creating the index. For example:

if not exists (select * from sysindexes
  where id=object_id('Employees') and name='IE1Employee')
    create index IE1Employee on Employees(name)
go

---

These notes have been tested against SQL Server 7, SQL Server 2000, SQL Server 2005 and SQL Server 2008.