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: Today @ 3:23 PM
Points: 5,984, Visits: 8,241
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: Sunday, August 24, 2014 6:50 PM
Points: 401, Visits: 556
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: Today @ 3:18 PM
Points: 5,603, Visits: 25,011
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, August 22, 2014 8:50 AM
Points: 4,425, Visits: 3,417
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:51 PM
Points: 13,639, Visits: 10,534
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


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 1,541, Visits: 8,219
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: Friday, July 4, 2014 9:03 AM
Points: 1,415, Visits: 796
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: Today @ 2:55 AM
Points: 3,352, Visits: 1,482
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: Friday, August 29, 2014 10:20 AM
Points: 1,740, Visits: 6,366
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