{"id":160,"date":"2016-10-17T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/unique-indexes-and-computed-columns-in-sql-server"},"modified":"2026-03-18T21:52:41","modified_gmt":"2026-03-18T21:52:41","slug":"unique-indexes-and-computed-columns-in-sql-server","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2016\/10\/17\/unique-indexes-and-computed-columns-in-sql-server\/","title":{"rendered":"Unique Indexes and Computed Columns in SQL Server"},"content":{"rendered":"<p>In the <a href=\"http:\/\/www.sqlhammer.com\/non-clustered-indexes-in-sql-server\/\">last post<\/a> of this blog series, I discussed what a non-clustered index is and how to choose an ideal non-clustered index key. In this post, I will discuss indexes on computed columns and unique indexes.<\/p>\n<h2>Computed columns<\/h2>\n<p>Computed columns almost do not fit into this series about indexes but I felt it was important for completeness to touch on how they affect, or don\u2019t affect, indexes. <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms188300.aspx\">Computer columns<\/a> are columns which are derived from one or more of the other columns in a table. By default, they are not persisted and do not consume any disk space. Instead, the value is computed each time it is read.<\/p>\n<p>For the rest of this post I will be using a database derived from <a href=\"http:\/\/dba.stackexchange.com\/\">dba.stackexchange.com<\/a> with a modified dbo.Posts table.<\/p>\n<pre><code class=\"language-\">USE dba_stackexchange\nGO\nALTER TABLE dbo.Posts ADD [StaleDate] AS (DATEADD(DAY,30,[LastActivityDate]))\nGO\nSELECT TOP 10 <em>\nFROM dbo.Posts<\/code><\/pre>\n<p><a href=\"http:\/\/www.sqlhammer.com\/unique-indexes-and-computed-columns-in-sql-server\/computed-column\/\"><img decoding=\"async\" src=\"\/wp-content\/uploads\/computed-column.png\" alt=\"\" \/><\/a><\/p>\n<p>The execution plan above highlights the compute scalar task which is injected into the execution plan to derive the computed column on the fly.<\/p>\n<p>Computed columns can also be persisted. This feature will write the data for the column to the clustered index or heap and maintain the data every time the row is updated.<\/p>\n<pre><code class=\"language-\">ALTER TABLE dbo.Posts DROP COLUMN [StaleDate]\nALTER TABLE dbo.Posts ADD [StaleDate] AS (DATEADD(DAY,30,[LastActivityDate])) PERSISTED\nGO\nSELECT TOP 10 <\/em>\nFROM dbo.Posts\nGO<\/code><\/pre>\n<p><a href=\"http:\/\/www.sqlhammer.com\/unique-indexes-and-computed-columns-in-sql-server\/persisted-computed-column\/\"><img decoding=\"async\" src=\"\/wp-content\/uploads\/persisted-computed-column.png\" alt=\"\" \/><\/a><\/p>\n<p>Notice that the compute scalar which was deriving the column value has disappeared.<\/p>\n<h3>Computed columns in indexes<\/h3>\n<p>When all is said and done, all indexes are persisted to disk. Whether the computed column is part of the base table or not holds no bearing over the index. The below script was run in SQL Server 2016 and it compares the table and index sizes under different conditions. A non-clustered index is applied to the computed column when it is not persisted and persisted.<\/p>\n<pre><code class=\"language-\">-- Space used by base table\nEXEC sp_spaceused 'dbo.Posts'\nGO\n\n<p>-- Add computed column\nALTER TABLE dbo.Posts ADD [StaleDate] AS (DATEADD(DAY,30,[LastActivityDate]))\n--Rebuilding the cluster index to show consistency for the PERSISTED step.\nALTER INDEX [PK_Posts] ON dbo.Posts REBUILD\nGO<\/p>\n\n<p>-- Space used after computed column was added\nEXEC sp_spaceused 'dbo.Posts'\nGO<\/p>\n\n<p>-- Create a non-clustered index on the computed column\nCREATE NONCLUSTERED INDEX IX_dbo_Posts_StaleDate\nON dbo.Posts (StaleDate)\nGO<\/p>\n\n<p>-- Space used after index build\nEXEC sp_spaceused 'dbo.Posts'\nGO<\/p>\n\n<p>-- Drop the computed column and recreate with PERSISTED option<\/p>\n\n<p>ALTER TABLE dbo.Posts DROP COLUMN [StaleDate]\nALTER TABLE dbo.Posts ADD [StaleDate] AS (DATEADD(DAY,30,[LastActivityDate])) PERSISTED\n--Rebuilding the cluster index because the column is not persisted until a rebuild operation.\nALTER INDEX [PK_Posts] ON dbo.Posts REBUILD\nCREATE NONCLUSTERED INDEX IX_dbo_Posts_StaleDate\nON dbo.Posts (StaleDate)\nGO<\/p>\n\n<p>-- Space used after persisted comupted column with index\nEXEC sp_spaceused 'dbo.Posts'\nGO<\/code><\/pre>\n<\/p>\n<p><a href=\"http:\/\/www.sqlhammer.com\/unique-indexes-and-computed-columns-in-sql-server\/computed-size-compare\/\"><img decoding=\"async\" src=\"\/wp-content\/uploads\/computed-size-compare.png\" alt=\"\" \/><\/a><\/p>\n<ul>\n<li>The non-persisted computed column does not affect table space.<\/li>\n<\/ul>\n<ul>\n<li>Creating a non-clustered index on the non-persisted computed column does not convert it to the persisted configuration. However, it does persist the disk in the form of the non-clustered index.<\/li>\n<\/ul>\n<ul>\n<li>A persisted computed column increases the base table\u2019s size. Also, the non-clustered index takes up its own space.<\/li>\n<\/ul>\n<h2>Uniqueness, indexes, and constraints<\/h2>\n<p>It is a primary goal of query tuning to ensure that SQL Server has as much information about the data as possible. Unique indexes and unique constraints provide SQL Server with an important data point which it can include in its execution decisions.<\/p>\n<p>Clustered indexes have to be unique in order to link non-clustered index rows to the clustered index rows. SQL Server does not require that your clustered index key be unique. Instead it will append 4 bytes to your key to make it unique. For more details on how uniqueness on clustered indexes affects performance, review <a href=\"http:\/\/www.sqlhammer.com\/clustered-indexes-sql-server\/\">the post<\/a> on clustered indexes.<\/p>\n<h3>Unique non-clustered indexes<\/h3>\n<p>I am going to focus on the performance of unique non-clustered indexes (NCI). It is important to point out, however, that using a unique NCI protects data integrity as its primary purpose.<\/p>\n<p>Unique NCIs should not be used for data which might not always be unique. They should, however, always be used for appropriate data sets, even if the application already enforces it. The performance benefits are notable.<\/p>\n<h4>Performance gains<\/h4>\n<p>To demonstrate the performance gains of making a NCI unique, I built a table with two columns with matching values. The values are unique in both columns. I then created a NCI on each column, one which is unique and one which is not.<\/p>\n<p>The column with the unique index is <em>uniqueIndexValue<\/em>. The column with the non-unique index is <em>nonUniqueIndexValue<\/em>.<\/p>\n<pre><code class=\"language-\">CREATE TABLE dbo.UniqueSortTest\n(\nid INT NOT NULL,\nuniqueIndexValue INT NOT NULL,\nnonUniqueIndexValue INT NOT NULL,\nrandomText CHAR(100) NOT NULL\n)\n\n<p>INSERT INTO dbo.UniqueSortTest\nSELECT n, n, n, REPLICATE('a',100)\nFROM (\nSELECT TOP 100000\nROW_NUMBER() OVER (ORDER BY (SELECT NULL)) n\nFROM sys.columns c1\nCROSS APPLY sys.columns c2\n) DT<\/p>\n\n<p>CREATE CLUSTERED INDEX IX_dbo_UniqueSortTest_id\nON dbo.UniqueSortTest (id)\nCREATE UNIQUE NONCLUSTERED INDEX IX_dbo_UniqueSortTest_uniqueIndexValue\nON dbo.UniqueSortTest (uniqueIndexValue)\nCREATE NONCLUSTERED INDEX IX_dbo_UniqueSortTest_nonUniqueIndexValue\nON dbo.UniqueSortTest (nonUniqueIndexValue)<\/code><\/pre>\n<\/p>\n<p>When sorting this table by nonUniqueIndexValue as the primary sort key I got a <a href=\"https:\/\/technet.microsoft.com\/en-us\/library\/ms189054(v=sql.105\">TOP N SORT<\/a>.aspx) and the NCI was not used to find the rows.<\/p>\n<pre><code class=\"language-\">SET STATISTICS IO ON\nSET STATISTICS TIME ON\n\n<p>SELECT TOP 10 id\nFROM dbo.UniqueSortTest\nORDER BY nonUniqueIndexValue, uniqueIndexValue<\/code><\/pre>\n<\/p>\n<p>> Table \u2018UniqueSortTest\u2019. Scan count 1, <strong>logical reads 1521<\/strong>, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.<\/p>\n<p><a href=\"http:\/\/www.sqlhammer.com\/unique-indexes-and-computed-columns-in-sql-server\/non-null-sort-perf-top-n-sort\/\"><img decoding=\"async\" src=\"\/wp-content\/uploads\/non-null-sort-perf-top-n-sort.png\" alt=\"\" \/><\/a><\/p>\n<p>When sorting this table by uniqueIndexValue as the primary sort key I got a <a href=\"https:\/\/technet.microsoft.com\/en-us\/library\/ms177432(v=sql.105\">TOP N<\/a>.aspx) operator instead and the appropriate NCI was used.<\/p>\n<pre><code class=\"language-\">SET STATISTICS IO ON\nSET STATISTICS TIME ON\n\n<p>SELECT TOP 10 id\nFROM dbo.UniqueSortTest\nORDER BY uniqueIndexValue, nonUniqueIndexValue<\/code><\/pre>\n<\/p>\n<p>> Table \u2018UniqueSortTest\u2019. Scan count 1, <strong>logical reads 37<\/strong>, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.<\/p>\n<p><a href=\"http:\/\/www.sqlhammer.com\/unique-indexes-and-computed-columns-in-sql-server\/non-null-sort-perf-top-n\/\"><img decoding=\"async\" src=\"\/wp-content\/uploads\/non-null-sort-perf-top-n.png\" alt=\"\" \/><\/a><\/p>\n<p>By declaring the index unique the optimizer produced an execution plan which is over 40 times faster. This happened because the optimizer knows that unique indexes will always return one row per value. It can read the records in the pre-sorted order of the index and it does not need to sort on the second ORDER BY value. It knows that there will never be more than one row to sort on that second value.<\/p>\n<p>This is only one example of how an execution plan can change for the better with a unique index. In general, declaring an index unique lets the optimizer know that it is more selective. When deciding between which indexes to use, the optimizer will pick the most selective index is can because it knows that it will perform better.<\/p>\n<h4>Performance cost of constraint enforcement<\/h4>\n<p>There are benefits to using a unique NCI but isn\u2019t there overhead in enforcing it for every write?<\/p>\n<p>No.<\/p>\n<p>When an insert or update occurs the below work flow kicks off. Step two has to occur on both unique and non-unique indexes. Since you are already traversing the index tree, there is no additional cost to enforcing uniqueness.<\/p>\n<ul>\n<li>Scan the tree and find where to insert the new value.<\/li>\n<\/ul>\n<ul>\n<li>During the scan, check if the inserted key matches an existing key.<\/li>\n<\/ul>\n<p>If it is a unique index, fail when an existing key is found.<\/p>\n<ul>\n<li>Insert the new value.<\/li>\n<\/ul>\n<h3>How are unique constraints different?<\/h3>\n<p>In SQL Server you can create a unique index, you can create a unique constraint, or you can create both on the same key. Which should you use and under what conditions?<\/p>\n<p>The short answer is that SQL Server handles the unique enforcement the same way between both types of objects. In fact, SQL Server creates a unique index to enforce the unique constraint anyways.<\/p>\n<p>I prefer to use unique indexes, however, because there are additional options, not related to uniqueness, which can be applied to indexes but not constraints.<\/p>\n<h4>What can an index do that a constraint cannot?<\/h4>\n<ul>\n<li>Set FILL FACTOR.<\/li>\n<\/ul>\n<ul>\n<li>Add non-key (INCLUDED) columns.<\/li>\n<\/ul>\n<ul>\n<li>Data compression.<\/li>\n<\/ul>\n<ul>\n<li>Lock management settings.<\/li>\n<\/ul>\n<h4>What can a constraint do that an index cannot?<\/h4>\n<p>The only potential benefit I was able to find, or think of, is that constraints can be disabled. If you could enable and disable a unique constraint, that could be one feature that the unique index does not. This is counter-intuitive knowing that the unique constraint is enforced with a unique index behind the scenes.<\/p>\n<p>In order to disable a constraint, such as a foreign key constraint, we can use an ALTER TABLE command with the NOCHECK syntax. Let us try this on a unique constraint.<\/p>\n<pre><code class=\"language-\">ALTER TABLE [dbo].[UniqueSortTest]\nADD CONSTRAINT UQ_dbo_UniqueSortTest_uniqueIndexValue\nUNIQUE ([uniqueIndexValue])\n\n<p>ALTER TABLE [dbo].[UniqueSortTest]\nNOCHECK CONSTRAINT UQ_dbo_UniqueSortTest_uniqueIndexValue<\/code><\/pre>\n<\/p>\n<p>> Msg 11415, Level 16, State 1, Line 8<br \/>\n><br \/>\n>  Object \u2018UQ_dbo_UniqueSortTest_uniqueIndexValue\u2019 cannot be disabled or enabled. This action applies only to foreign key and check constraints.<\/p>\n<p>Well, that did not work. It appears that there are no constraint features of use to us which is why I favor unique indexes over constraints in SQL Server.<\/p>\n<h2>Next time<\/h2>\n<p>In this post I discussed unique constraints and indexes and how they affect query performance. I recommend using unique indexes whenever possible. They provide more options than unique constraints do and knowing that the key is unique is important information for the optimizer to have. Next time I will discuss filtered indexes.<\/p>\n<h1>References<\/h1>\n<ul>\n<li>Performance<\/li>\n<\/ul>\n<p><a href=\"https:\/\/explainextended.com\/2009\/04\/23\/making-an-index-unique\/\">Making an index UNIQUE (explainextended)<\/a><\/p>\n<ul>\n<li><a href=\"https:\/\/technet.microsoft.com\/en-us\/library\/jj835095(v=sql.110\">Unique Index Design Guidelines (technet)<\/a>.aspx#Unique)<\/li>\n<\/ul>\n<ul>\n<li><a href=\"http:\/\/dba.stackexchange.com\/questions\/100749\/how-does-unique-key-help-to-improve-sql-query-performance\">How does unique key help to improve SQL query performance? (dba.stackexchange)<\/a><\/li>\n<\/ul>\n<ul>\n<li><a href=\"http:\/\/blog.sqlauthority.com\/2010\/08\/21\/sql-server-computed-column-persisted-and-storage-part-2\/\">SQL Server \u2013 Computed Column \u2013 PERSISTED and Storage \u2013 Part 2 (sqlauthority)<\/a><\/li>\n<\/ul>\n<ul>\n<li>Index vs. Constraint<\/li>\n<\/ul>\n<p><a href=\"https:\/\/technet.microsoft.com\/en-us\/library\/aa224827(v=sql.80\">Unique Constraints and Unique Indexes (technet)<\/a>.aspx)<\/p>\n<ul>\n<li><a href=\"http:\/\/www.sqlskills.com\/blogs\/kimberly\/foreign-keys-can-reference-unique-indexes-without-constraints\/\">Foreign keys can reference UNIQUE indexes \u2013 without constraints (SQL Skills)<\/a><\/li>\n<\/ul>\n<ul>\n<li><a href=\"https:\/\/technet.microsoft.com\/en-us\/library\/jj835095(v=sql.110\">SQL Server Index Design Guide (technet)<\/a>.aspx#Unique)<\/li>\n<\/ul>\n<ul>\n<li><a href=\"http:\/\/www.sqlservergeeks.com\/unique-index-vs-unique-constraint-sql-server\/\">Unique index vs. unique constraint in SQL Server (sqlservergeeks)<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>In the last post of this blog series, I discussed what a non-clustered index is and how to choose an ideal non-clustered index key. In this post, I will discuss indexes on computed columns and unique indexes. Computed columns Computed columns almost do not fit into this series about indexes but I felt it was [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":555,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,7,9,13,14,21],"tags":[],"class_list":["post-160","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administration","category-community","category-development","category-microsoft-sql-server","category-performance","category-t-sql"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/160","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=160"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/160\/revisions"}],"predecessor-version":[{"id":422,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/160\/revisions\/422"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media\/555"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=160"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=160"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=160"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}