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 «««1234»»

Identity Crisis: Attack of the Clone Expand / Collapse
Author
Message
Posted Wednesday, May 11, 2011 9:37 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 6:29 PM
Points: 1,224, Visits: 355
Excellent Question and thorough explanation.
Thanks!
Post #1107100
Posted Wednesday, May 11, 2011 9:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:11 PM
Points: 5,361, Visits: 8,924
Tom.Thomson (5/11/2011)
Nice question.

One niggle: asking people to assume that a schema is valid when a table doesn't have a primary key may encourage a very bad habit if some of your audience don't know any better. Maybe "pretend" would have been a better choice of word than "assume".


Tom, I think it was referring to the schema "Playground", which the table was created in.


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1107106
Posted Wednesday, May 11, 2011 9:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:11 PM
Points: 5,361, Visits: 8,924
Good question - thanks!

Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1107112
Posted Wednesday, May 11, 2011 10:47 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,228, Visits: 1,046
steven.chester (5/11/2011)
What's funny is that I couldn't pick my first answer: a syntax error. There's a superfluous comma in the DDL for the table.

That comma does not generate any error when the code is executed as listed in the QOTD.
Of course you would need to execute this command before you execute the QOTD SQL to test that. Don't forget to replace parameters.

CREATE SCHEMA PLAYGROUND AUTHORIZATION <Your User Name, sysname, DBuser>
GO

Post #1107184
Posted Wednesday, May 11, 2011 10:50 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 2:16 PM
Points: 477, Visits: 3,673
mtassin (5/11/2011)...
At that point, just call the bloody thing a primary key.

So I see INT IDENTITY(1,1) and these days I assume PRIMARY KEY is stuck on the end of it.

Then I misread the answer that said it was a violation of the IDENTITY constraint. Which I know isn't a constraint... I was just rushed because I have a meeting in 10 min. :)

Oops.


Same here Mark. After seeing Identity and primary key together for years, my mind automatically puts them together. I need to start paying atte...ooh, look, ice cream!

Good question.


______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Post #1107188
Posted Wednesday, May 11, 2011 10:54 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 11, 2011 1:51 PM
Points: 341, Visits: 311
WayneS (5/11/2011)
Tom.Thomson (5/11/2011)
Nice question.

One niggle: asking people to assume that a schema is valid when a table doesn't have a primary key may encourage a very bad habit if some of your audience don't know any better. Maybe "pretend" would have been a better choice of word than "assume".


Tom, I think it was referring to the schema "Playground", which the table was created in.


I think he was implying that the table's schema (not the Playground schema -- confused yet?) is not appropriate since there is no primary key or unique identifier. Someone may read the question and assume the table's structure is good practice when it is not.
Post #1107193
Posted Wednesday, May 11, 2011 10:57 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 8:17 PM
Points: 17,729, Visits: 15,595
nice and straight forward - thanks



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1107196
Posted Wednesday, May 11, 2011 1:48 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, June 13, 2014 3:03 PM
Points: 594, Visits: 655
Almost got it wrong until I remembered that IDENTITY does not have to be UNIQUE I just WANT it to be... made me read carefully. That's why I think this is a great question.


Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
Post #1107317
Posted Wednesday, May 11, 2011 6:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:57 PM
Points: 7,745, Visits: 9,493
WayneS (5/11/2011)
Tom.Thomson (5/11/2011)
Nice question.

One niggle: asking people to assume that a schema is valid when a table doesn't have a primary key may encourage a very bad habit if some of your audience don't know any better. Maybe "pretend" would have been a better choice of word than "assume".


Tom, I think it was referring to the schema "Playground", which the table was created in.

Ah, yes, probably. My mistake. I find it hard at times to remember that MS misuses this particular term so horribly, and my reaction was as it was because I interpreted "schema" as "schema" rather than as what MS calls "schema". Must remember loony MS terminology in future .


Tom
Post #1107419
Posted Wednesday, May 11, 2011 9:18 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 11:26 AM
Points: 668, Visits: 485
Excellent question.
Post #1107449
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse