Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
waveform
Yak Posting Veteran
93 Posts |
 Posted - 2015-02-22 : 01:08:34
|
I have here a "Lectures" table, each row of which represents a lecture that took place at a specific time.After *some* of those lectures will be a short survey, so there is also a "Surveys" table.The relationship between them is like so (just showing the relevant bits):CREATE TABLE Lectures ( LectureId int IDENTITY ... SurveyId int NULL ... CONSTRAINT FK_Lectures_Surveys FOREIGN KEY (SurveyId) REFERENCES Surveys (SurveyId))CREATE TABLE Surveys ( SurveyId int IDENTITY ...) My question is about how best to structure the INDEX on the foreign key (SurveyId) in the Lectures table. There are two considerations here:1. Only *some* of the lectures will be followed by a survey, so most values of SurveyId in the Lectures table will be NULL.2. The relationship is 1-to-1. That is, each survey record is specific to a single lecture.Therefore, the index I think I should create is this:CREATE UNIQUE INDEX IX_Lectures_SurveyId ON Lectures (SurveyId) WHERE (SurveyId IS NOT NULL) I gather such an index does 2 things:1. Excludes NULLS from the index2. Ensures unique SurveyId values (so two lectures don't accidentally point to the same survey).It seems to make sense to do it like this, but since I have never created a conditional index before, and don't have experience with them, I'm wondering:1. Is this the right approach in this case?2. If so, are there any pitfalls to look out for when using conditional indexes?3. If I'm going down the wrong path, what would be the recommended way to structure the index? |
|
|
|
|
|
Conditional (NOT NULL) indexes for foreign keys