• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 918
  • Last Modified:

Is a Unique Constraint an Index?

Hello!

I've been re-working some of my table design ideas recently, and have been attempting to learn as much as I can about Indexes and whatnot so as to have the database be as fully functional and efficient as possible.  A question has come up in my mind, however, regarding the differences between Constraints and Indexes (indices?).  Here's an example of my situation:


SubjectVisit - table
-----
SubjectVisitID - Surrogate PK, Autonumber INT Identity field
SubjectNumber - FK to Subject table where it is unique INT arbitraty for each subject
VisitDate - DateTime


This table is going to be used to track when a subject visits the research laboratory that I work for.  Each subject can only visit once per day, but may visit on many different days, so I want to have a Unique Constraint which will limit based on that logic.  I also want to have a Clustered Index on SubjectNumber and would like for VisitDate to have an Index as well since it will be used in the WHERE of many queries, and of course SubjectVisitID will be the PK.


Here are the indexes/constraints that I was thinking would be necessary assuming that a Unique Constraint does not actually act as an Index itself:

PK on SubjectVisitID - In the .ADP I'm working with which is linked to a SQL Server backend this looks like it is simply a Unique Constraint...is this an Index as well?
Unique Constraint on SubjectNumber, VisitDate - For the purposes of business logic, but is this also an Index?
Clustered Index on SubjectNumber - To physically order the data in the most useful fashion.
Index on VisitDate - VisitDate will be searched against often as a criterion.  Hopefully this index will be selective enough to be useful.


So my questions are:  
I've also read that you should never have more then one index on a field.  Does having the Unique Constraint and the Clustered Index on SubjectNumber break this rule?  How exactly are Constraints regarded by the SQL Server?
This may have to be a separate inquiry, but I'm curious...If I changed the Unique Constraint to be a Unique Clustered Index, would that be sufficient for this table?  Would I still need the Index on VisitDate?  I've read that Clustered Indexes really shouldn't be composite which is why I didn't use this in the first place...


Thanks for any help you can offer!
Jamie
0
jrb3222
Asked:
jrb3222
  • 5
  • 4
  • 3
5 Solutions
 
arbertCommented:
From books online:

" Unless a clustered index is explicitly specified, a unique, nonclustered index is created by default to enforce the UNIQUE constraint"

I wouldn't say a Clustered index should be composite--depends on your situation.  The more columns you add to an index (clustered or non-clustered), the more overhead...
0
 
ptjcbCommented:
Would I still need the Index on VisitDate?
Yes. Since VisitDate is searched often (used in the WHERE criteria) then you should have an index on this one.

 How exactly are Constraints regarded by the SQL Server?
Constraints are used to enforce database integrity. For example, if you have a UNIQUE constraint on a column you allow NULL values. If you make that column a Primary Key it cannot, by definition, allow NULL values.

From BOL (Using Unique Indexes):
Specifying a unique index makes sense only when uniqueness is a characteristic of the data itself. If uniqueness must be enforced to ensure data integrity, create a UNIQUE or PRIMARY KEY constraint on the column rather than a unique index. For example, if you plan to query frequently on the Social Security number (ssn) column in the employee table (in which the primary key is emp_id), and you want to ensure that Social Security numbers are unique, create a UNIQUE constraint on ssn. If the user enters the same Social Security number for more than one employee, an error is displayed.

Creating a PRIMARY KEY or UNIQUE constraint automatically creates a unique index on the specified columns in the table. There are no significant differences between creating a UNIQUE constraint and creating a unique index manually. Data validation occurs in the same manner and the query optimizer does not differentiate between a unique index created by a constraint or created manually. A unique index and a UNIQUE constraint cannot be created if there duplicate key values exist.

Creating a unique index instead of non-unique on the same combination of columns provides additional information for the query optimizer; therefore, creating a unique index is preferred.

 




0
 
jrb3222Author Commented:
Thanks for the quick replies!  I'll have to check out this books online thing, too :)

I'm still having a bit of trouble wrapping my mind around this new information...

arbert-  
In my case where I am planning to have a Clustered Index defined for the table (on SubjectNumber), will the unique index still be automatically created?
I guess I'll have to play around with using Composite Indexes to see if in my specific case there are any benefits or not.

ptjbc-  
It seems to me that your two paragraphs of information are conflicting.  The first says that a Unique Constraint should be used instead of a Unique Index (but doesn't offer a reason why), then the second says that using a Unique Constraint automatically creates a Unique Index anyway.  And it seems that they both do essentially the same thing?  Why would I ever want to use a Unique Constraint instead of simply assigning a Unique Index in the first place?  If "there are no significant differences between creating a UNIQUE constraint and creating a unique index manually" then why even bother having two options?  If there really isn't a significant difference, then which should I use in my situation?

Thanks again,
Jamie
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
arbertCommented:
"In my case where I am planning to have a Clustered Index defined for the table (on SubjectNumber), will the unique index still be automatically created?
"

Only if you create the clustered index as UNIQUE.  There will only be one index create--not two....
0
 
ptjcbCommented:
Paraphrasing "Inside SQL Server 2000 (page 271 - 272)"

Internally, primary key and unique constraints are handled almost identically. Declaring either simply results in a unique index being created on the columns or columns, and this index enforces the column's uniqueness in the same way that a unique index created manually would. The query optimizer makes decisions based on the presence of the unique index rather than on the fact that a column was declared as a primary key.

A primary key column cannot have null values. Columns that are part of a unique constraint can be declared null. For a unique index on a single column only one null value is stored (for the purposes of the unique index all nulls are created equal). This is another reason to avoid null whenever possible. If the unique index is a composite key a column may have many nulls as long as the value of the other column is unique.


***
You are right - adding a unique constraint has the same effect as adding a unique index. It is how the database engine implements a unique constraint.


0
 
arbertCommented:
"For a unique index on a single column only one null value is stored (for the purposes of the unique index all nulls are created equal). "

This is also where Oracle people bash SQL Server a lot--Oracle will allow multiple NULL values in a unique constraint/index and SQL doesn't (after all, null doesn't equal null).
0
 
ptjcbCommented:
Here are some quick and dirty rules for indexing (mileage may vary):

Columns that are regularly used in joins should be indexed because the system will perform the join faster.

A column that is often searched for a range of values (your VisitDate column) might be a good choice for a clustered index. Once the first row with the first value is found, rows with subsequent values are usually physically adjacent. A clustered index does not offer as much as an advantage on single values.

Also you should be sure that you have arranged your indexes in the same way that you use them in the WHERE criteria. For example, you have a date column and a name column. If 80% of your queries are WHERE date = date and name = name, then be sure that your index is created in the same order. If your index is name, date there is a good chance that the query optimizer will not see the index and will not use it.


0
 
ptjcbCommented:
arbert - I know, I know. It is great to have standards, we have so many of them.
0
 
arbertCommented:
"It is great to have standards, we have so many of them."

I agree...so many to choose from....
0
 
jrb3222Author Commented:
"after all, null doesn't equal null"

Tricky!

Wow...ok...so, plenty to think about here.  I also just ran another search here on EE and saw another article about this same topic which was revealing if not actually helpful in assisting my decision:  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro01/html/sql01j14.asp

I suppose my initial thought on using a Composite Unique Constraint instead of a Composite Unique Index was twofold:  I wanted to avoid having a Composite Index of any kind, and I also wanted to avoid having any columns be Indexed multiple times.  It now appears as if both of these were going to happen anyways and I should just accept that and move on, so I guess I will!  Not all of the tables that I am designing will run into these same problems, but at least I have a better idea of what's going on behind the scenes now.

For anyone interested, here's my current plan for the table I described above.  Please let me know if you think any of my choices are ill-advised:

PK on SubjectVisitID
Unique Clustered Index on (SubjectNumber, VisitDate)
Non-Clustered Index on VisitDate


This eliminates one of the Indexes which would have been created in favor of a more all-encompassing Index which to some extent takes care of everything I was worried about all in one Index.  I suppose VisitDate is still being Indexed twice, though it's not the left-most field in the Composite Index so I'm not entirely sure whether or not that will be a problem.

I'll wait another day or so in case anyone else has any comments before I assign points, but thanks very much for your help!
Jamie
0
 
ptjcbCommented:
Jamie -

Null should not equal Null. You could think of it as "I don't know about this" is not the same as "I don't know about that." But for some process in SQL Server Null can equal Null. Eh bien.

I like the new design. It should work fine. Creating indexes is more of a balance rather than a science. Too many indexes in a heavily-transacted table and you will see a slow down. Too few and some of the queries will be inefficient.

From your clustered index setup it looks as if most of your WHERE clauses are WHERE subjectnumber = @subjectnumber and visitdate = @visitdate.

The next step is (after you have data in the table) to check the queries against the execution plan in Query Analyzer. It will give you a good idea of how QA will execute your query and you may be in for some surprises, or maybe not. Indexes change. Queries change. What worked efficiently six months ago may wooble badly after a bulk import.

The other tool you should become familar with is SQL Profiler. Create some traces and you can capture how SQL executes your queries. Then run that trace through the Indexing Wizard (but do not believe everything it returns - always take it as advice from someone on the sidelines, good to know, but not gospel).

Bon chance



0
 
jrb3222Author Commented:
Thanks very much for all of the continued advice!  I think I've got enough now to get working on this with a (somewhat) educated view on things.

Jamie
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now