Search code examples
sql-serverunique-constraintunique-index

How to deal with a unique (editable) column in order to bring better performance on retrieve the data? unique index or unique constraint?

I have a table with a column that will be varchar unique with not null value and is editable by the user but keeping the uniqueness, filtering and ordering data will be based on that column.

What is the best way to deal with this column in order to bring the better retrieve performance?

UNIQUE CONSTRAINT or UNIQUE INDEX? Who provides best performance? The editable requirement will affect this in the future? By the ways maybe is editable two or three times in all the record lifetime, in some records maybe never

Mostly used queries on the future:
SELECT * FROM mytable WHERE uniqueEditableColumn like '%foobar%' ORDER BY uniqueEditableColumn ASC
SELECT * FROM mytable WHERE uniqueEditableColumn = 'foobar' ORDER BY uniqueEditableColumn ASC
SELECT * FROM mytable ORDER BY uniqueEditableColumn ASC
SELECT * FROM mytable ORDER BY uniqueEditableColumn ASC, creationDateTime DESC
The average amount or records saved will be more or less 8000 Goals: unique value - retrieve the data the most quickly as possible

Solution

  • Looks like they are treated the same.

    https://dba.stackexchange.com/a/147

    Also, https://technet.microsoft.com/en-us/library/aa224827(v=sql.80).aspx has some info on it, but the only difference they found was regarding the use of FILLFACTOR when altering a table.