Creating a non-unique index

Question

Creating a non-unique index

michael.leach2015

SSCommitted

Points: 1597
More actions
March 26, 2019 at 5:01 pm

#2031624

If you don't specify Unique, then by default a non-unique index is created. But suppose I wanted to explicitly create a non-unique index (just to understand how SQL works).
What is wrong with this code. With or without the hyphen, I still get an error.
CREATE NON-UNIQUE NONCLUSTERED INDEX ix_testIndex
ON Roster(firstName);

Viewing 6 posts - 1 through 5 (of 5 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

michael.leach2015 - Tuesday, March 26, 2019 5:01 PM
If you don't specify Unique, then by default a non-unique index is created. But suppose I wanted to explicitly create a non-unique index (just to understand how SQL works).
What is wrong with this code. With or without the hyphen, I still get an error.
CREATE NON-UNIQUE NONCLUSTERED INDEX ix_testIndex
ON Roster(firstName);

You can specify unique or you can leave it off. Leave it off and you have defined a non-unique index. So in your example, it's just:
CREATE NONCLUSTERED INDEX ix_testIndex ON Roster(firstName);

The documentation has the first line of the statement as:
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
You can find the syntax in this documentation:
CREATE INDEX (Transact-SQL)

Sue

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 2

michael.leach2015 - Tuesday, March 26, 2019 5:01 PM
If you don't specify Unique, then by default a non-unique index is created. But suppose I wanted to explicitly create a non-unique index (just to understand how SQL works).
What is wrong with this code. With or without the hyphen, I still get an error.
CREATE NON-UNIQUE NONCLUSTERED INDEX ix_testIndex
ON Roster(firstName);

Google for "CREATE INDEX" and see that NON-UNIQUE is NOT valid and neither is NONUNIQUE.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

matter2003 SSC Veteran Points: 254 More actions · Answer 3

Jeff Moden? Are you the same person with the awesome splitter function?? I used your version and made a few minor tweaks to have it do what I needed it to do...its pretty awesome! Just wanetd to say thanks if that was you and let you know it was much appreciated!

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 4

Jeff is indeed the MASTER OF THE TALLY TABLE.... and many other things besides. 😉

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 5

I'm humbled. Thank you for the kind words and, as Dixie said, yes... that's me. :blush: I'll also say that it's not just me. DelimitedSplit8K is one of the finest examples in the world of community effort. As the revisions say in the header, a lot of great people a hand in it to make it what is is today.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)