Syntax help please in creating an index

Question

Syntax help please in creating an index

mw_sql_developer

SSCoach

Points: 19443
More actions
September 30, 2015 at 7:45 am

#311931

Table Name: Denominator
Already has the following constraint:
PK_Denominatorclustered, unique, primary key located on PRIMARY DenominatorID
How can I add a unique key that will cover the 3 fields --> MemberID,MeasureID,TimePeriodID
I also want to know whether we can include the " WITH ( IGNORE_DUP_KEY=ON ) "

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

mw112009 (9/30/2015)
Table Name: Denominator
Already has the following constraint:
PK_Denominatorclustered, unique, primary key located on PRIMARY DenominatorID
How can I add a unique key that will cover the 3 fields --> MemberID,MeasureID,TimePeriodID
I also want to know whether we can include the " WITH ( IGNORE_DUP_KEY=ON ) "

In SSMS, open Object Explorer. Locate your table, expand the node and right-click on the Index node. It's intuitive from there. Once you are done, script the index out so you can see the syntax.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Simon Hundleby SSCarpal Tunnel Points: 4514 More actions · Answer 2

CREATE UNIQUE NONCLUSTERED INDEX [IX_Denominator] ON [dbo].[Denominator]

(

MemberID ASC,

MeasureID ASC,

TimePeriodID ASC

)WITH (IGNORE_DUP_KEY = ON) ON [PRIMARY]

ScottPletcher SSC Guru Points: 100262 More actions · Answer 3

Carefully review what the best clustered index for this table would be. It's extremely like that all, or some, of the keys in your planned new index should actually be the clustering key set instead. Ignore the myth that an identity should "always" be the clustering key, it can be terrible for overall performance.

Edit: You can keep the identity as the PK if you need to, just make it nonclustered.

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!