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: Wednesday, November 19, 2014 3:56 PM
Points: 6,086, Visits: 8,354
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: Thursday, November 20, 2014 9:03 PM
Points: 405, Visits: 572
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: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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: Friday, October 24, 2014 12:43 PM
Points: 4,126, Visits: 3,428
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: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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: Today @ 2:35 PM
Points: 13,532, Visits: 11,328
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 LessThanDot.

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


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 7:41 AM
Points: 1,475, Visits: 8,476
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


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:30 AM
Points: 1,417, Visits: 806
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: Tuesday, October 21, 2014 6:56 AM
Points: 3,352, Visits: 1,487
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
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:17 AM
Points: 1,804, Visits: 6,576
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