?
Solved

Is a Unique Constraint an Index?

Posted on 2005-03-24
12
Medium Priority
?
885 Views
Last Modified: 2008-03-17
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
Comment
Question by:jrb3222
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 34

Assisted Solution

by:arbert
arbert earned 400 total points
ID: 13623853
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
 
LVL 27

Assisted Solution

by:ptjcb
ptjcb earned 1600 total points
ID: 13623888
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
 

Author Comment

by:jrb3222
ID: 13624165
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
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
LVL 34

Assisted Solution

by:arbert
arbert earned 400 total points
ID: 13624199
"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
 
LVL 27

Expert Comment

by:ptjcb
ID: 13624472
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
 
LVL 34

Expert Comment

by:arbert
ID: 13624501
"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
 
LVL 27

Accepted Solution

by:
ptjcb earned 1600 total points
ID: 13624545
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
 
LVL 27

Expert Comment

by:ptjcb
ID: 13624558
arbert - I know, I know. It is great to have standards, we have so many of them.
0
 
LVL 34

Expert Comment

by:arbert
ID: 13624568
"It is great to have standards, we have so many of them."

I agree...so many to choose from....
0
 

Author Comment

by:jrb3222
ID: 13624619
"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
 
LVL 27

Assisted Solution

by:ptjcb
ptjcb earned 1600 total points
ID: 13625277
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
 

Author Comment

by:jrb3222
ID: 13625423
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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question