Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Index defaults 2 Expand / Collapse
Author
Message
Posted Tuesday, June 26, 2012 8:12 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:56 AM
Points: 6,518, Visits: 9,027
Comments posted to this topic are about the item Index defaults 2


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1321645
Posted Tuesday, June 26, 2012 8:14 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 26, 2015 11:17 PM
Points: 434, Visits: 636
You have two answers that are potentially correct here.

None, and None unless the rest of the command specifies one.

Shame I picked the wrong one!

Edit: I stand corrected. The CREATE INDEX has to be a separate command. Missed that.
Post #1321646
Posted Tuesday, June 26, 2012 9:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, January 4, 2015 7:55 AM
Points: 5,333, Visits: 25,280
Nice question .. learned something



If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1321653
Posted Tuesday, June 26, 2012 10:02 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:51 PM
Points: 4,684, Visits: 3,902
Thanks, Hugo -- a nice one to end my day with!
Post #1321660
Posted Tuesday, June 26, 2012 11:43 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Saturday, June 6, 2015 9:44 PM
Points: 3,448, Visits: 4,408
I can create an index by means of a unique constraint:

CREATE TABLE dbo.QotD
(KeyColumn int NOT NULL PRIMARY KEY,
RefColumn int NOT NULL,
-- More column definitions
CONSTRAINT FK_RefTab FOREIGN KEY (RefColumn)
REFERENCES dbo.RefTab(RefTabKey),
-- More constraints
CONSTRAINT UQ_RefColumn UNIQUE(RefColumn)
);

Doesn't it count as answer #2?
Post #1321688
Posted Wednesday, June 27, 2012 12:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:12 AM
Points: 14,608, Visits: 12,669
vk-kirov (6/26/2012)
I can create an index by means of a unique constraint:

CREATE TABLE dbo.QotD
(KeyColumn int NOT NULL PRIMARY KEY,
RefColumn int NOT NULL,
-- More column definitions
CONSTRAINT FK_RefTab FOREIGN KEY (RefColumn)
REFERENCES dbo.RefTab(RefTabKey),
-- More constraints
CONSTRAINT UQ_RefColumn UNIQUE(RefColumn)
);

Doesn't it count as answer #2?


It's possible, but I doubt a 1-1 foreign key relationship is very useful

I also took answer number 2. Forgot that you cannot specify an index directly in a create table statement. D'oh!




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1321713
Posted Wednesday, June 27, 2012 1:03 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:55 AM
Points: 1,599, Visits: 9,410
I also took answer number 2. Forgot that you cannot specify an index directly in a create table statement. D'oh!


Thanks - I was wondering why my answer was incorrect too. I thought it strange that Hugo might have made a mistake!


BrainDonor
Linkedin
Blog Site
Post #1321719
Posted Wednesday, June 27, 2012 1:53 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, July 17, 2015 7:46 AM
Points: 1,442, Visits: 825
Hugo - nice question.
Surprised only 15% have this correct so far...
Post #1321729
Posted Wednesday, June 27, 2012 1:58 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, August 24, 2015 3:28 AM
Points: 3,352, Visits: 1,512
vk-kirov (6/26/2012)
I can create an index by means of a unique constraint:

CREATE TABLE dbo.QotD
(KeyColumn int NOT NULL PRIMARY KEY,
RefColumn int NOT NULL,
-- More column definitions
CONSTRAINT FK_RefTab FOREIGN KEY (RefColumn)
REFERENCES dbo.RefTab(RefTabKey),
-- More constraints
CONSTRAINT UQ_RefColumn UNIQUE(RefColumn)
);

Doesn't it count as answer #2?


That's exactly what I thought - but then decided that given this was a question from Hugo Kornelis it wouldn't be that sneaky, so went for option 1.

Thanks for the question.
Post #1321731
Posted Wednesday, June 27, 2012 2:14 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:49 AM
Points: 2,017, Visits: 7,325
vk-kirov (6/26/2012)
I can create an index by means of a unique constraint


That's exactly the basis on which I answered "None unless the rest of the statement creates one", which I therefore believe should be the correct answer. Particularly given that the previous "index defaults" question was about the possibility of a Unique constraint appearing later in the statement!
Post #1321735
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse