CREATE INDEX - SQL Server for Oracle DBAs and Developers

In SQL Server, you can use CREATE INDEX statement to create a unique or non-unique, clustered or nonclustered index on a table or view.

Last Update: Microsoft SQL Server 2012 and Oracle 11g R2.

Unique Indexes in SQL Server

An unique index does not allow duplicate values:

NULL Values in Unique Index A single NULL value can be inserted
Multiple NULL values are considered duplicates

SQL Server allows you to define a unique index on nullable columns and insert a NULL value:

SQL Server:

  CREATE TABLE cities 
  (
    name VARCHAR(90) NULL
  );
 
  CREATE UNIQUE INDEX cities_name_idx ON cities(name);
 
  -- Insert NULL value to unique column
  INSERT INTO cities VALUES (NULL);
  # (1 row(s) affected)

But if you insert another NULL value, it is considered as duplicate:

  -- Insert another NULL value (one NULL value already exists)
  INSERT INTO cities VALUES (NULL);
  # Msg 2601, Level 14, State 1, Line 1
  # Cannot insert duplicate key row in object 'dbo.cities' with unique index 'cities_name_idx'.

Resources

SQLines Services

SQLines offers services to migrate Oracle databases and applications to Microsoft SQL Server. For more information, please Contact Us.

You could leave a comment if you were logged in.