What is the Point of using a Unique index on a Guid Column

Question

Since, guid values will always be unique anyway why use a unique index. Isnt it true that when you use a unique index that it slows down inserts?

Answer 1

It's a constraint; not index.

Because the data modeller wanted there to be only one instance of the GUID in all the records for the given table.

Answer 2

I think you may be confusing a bit the idea of a unique constraint and a unique index. While a unique constraint may not be too useful if you are certain that the data is going to be unique, a unique index will create an actual "index" of that column for that table. Essentially this provides you with better performance when querying against that column.

Answer 3

Unique contraint will ultimetely create unique index on a table. Although they have different syntax results are same. Refere Here.

If your table do not have Primary Key declared, unique contraint will create CLUSTERED Index on a table or else it will create non-CLUSTERED index on a table. A table can have only one CLUSTERED index and multiple non-CLUSTERED index.

Compact coulum(with CLUSTERED index on it) lengths allow more index entries to fit on a given 8-KB page, thereby reducing I/O, increasing cache hits, and speeding up character matching. Clustered index keys are used as bookmarks in all your nonclustered indexes, so a long clustered key will increase the size and decrease the I/O efficiency of all your indexes. So i think coulmns responsible for creating clustered or non clustered index should have minimum length. Now as you already have uniqueness by GUID, the size of the GUID is large, you can create indexes on some other coulmns which are comparatively small in terms of size.

Answer 4

First of all, as has been commented - GUID's are not guaranteed to be 100% unique. Plus, if someone dreams up a "let's create my own thing that looks like a GUID" scheme and sends you data which you then have to import into your database, you might still want to make sure that there is a unique constraint on your table.

Also, as others have pointed out, you need to keep apart unique constraint from unique index. Internally in SQL Server, both are implemented as a unique index in order to enforce uniqueness.

But if you specifically create a unique index, then you can use this index to reference that table from another table in a foreign key relationship. Normally that works only against the primary key of a table - but it does also work against a unique index. This is a little known fact about SQL Server - see Kimberly Tripp's blog post on how she found out about it :-)

So if you put a UNIQUE INDEX on your table's GUID column, you can reference that table/column and thus use it for referential integrity purposes.

Marc

Answer 5

Uses of UNIQUE constraints:

• Enforcing uniqueness. Although a generated GUID is very likely to be unique, you could easily insert the same GUID in multiple rows. Of course you could do it by mistake, but this may even be part of your design, for instance for compound key constraints in many-to-many tables.

  CREATE TABLE BookAuthors (
    guid INT PRIMARY KEY,
    BookGuid INT NOT NULL,
    AuthorGuid INT NOT NULL,
    FOREIGN KEY (BookGuid) REFERENCES Books(BookGuid),
    FOREIGN KEY (AuthorGuid) REFERENCES Authors(AuthorGuid),
    UNIQUE KEY (BookGuid, AuthorGuid)
  );
  

• Being the target of a foreign key. You probably are accustomed to FOREIGN KEY referencing a PRIMARY KEY of the parent table. Did you know that a FOREIGN KEY can also reference a UNIQUE KEY?

  CREATE TABLE Acknowledgements (
    guid INT PRIMARY KEY,
    BookGuid INT NOT NULL,
    AuthorGuid INT NOT NULL,
    Acknowledged VARCHAR(100) NOT NULL,
    -- this works because of the UNIQUE constraint in BookAuthors:
    FOREIGN KEY (BookGuid, AuthorGuid) 
      REFERENCES BookAuthors (BookGuid, AuthorGuid)
  );
  

• Performance. UNIQUE is a constraint, as others have pointed out, but in most brands of database, an index is implicit when you define a UNIQUE, PRIMARY KEY, or FOREIGN KEY constraint. You're right that there's some overhead when inserting to any indexed table, but the performance benefit of an index is a net win, usually many times over.

• Uniqueness notwithstanding NULL. While the primary key is crucial for the use of identifying a rows in a table, they don't permit NULLs. You can use UNIQUE constraints to enforce uniqueness in nullable columns.