SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by dun / Identity Crisis: Attack of the Clone / Latest Posts

RE: Identity Crisis: Attack of the Clone

zymos — Fri, 16 Mar 2012 15:36:23 GMT

Easy question. Thanks

RE: Identity Crisis: Attack of the Clone

thoover — Sat, 03 Sep 2011 20:08:19 GMT

Good Question. I had to read the flow of logic twice, but i selected the right option.

RE: Identity Crisis: Attack of the Clone

khullargirish02 — Thu, 26 May 2011 01:06:51 GMT

Nice Question, QotD.. keep us bugging like this only :-)

RE: Identity Crisis: Attack of the Clone

Peter Trast — Fri, 13 May 2011 09:06:43 GMT

[quote][b]Tom.Thomson (5/11/2011)[/b][hr][quote][b]WayneS (5/11/2011)[/b][hr][quote][b]Tom.Thomson (5/11/2011)[/b][hr]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".[/quote]Tom, I think it was referring to the schema "Playground", which the table was created in.[/quote]Ah, yes, probably. My mistake. I find it hard at times to remember that MS misuses this particular term so horribly, and my reaction :alien: was as it was because I interpreted "schema" as "schema" rather than as what MS calls "schema". Must remember loony MS terminology in future :(.[/quote]True many people are unaware of all the implications of "schema" in SQL as it covers many things "to make SQL databases and objects self-describing". Things like structure and integrity constraints, security and authorization specifications, object identifiers, features and packages, support of features, implementation information and sizing items, and schema is actually divided into "information" and "definition" schemas.So loony :hehe: actually applies to the description of schema by many MS DBA's, not Microsoft itself. Unfortunately, many DBA's just never really learn how broadly the term applies and tend to assume the third position of the name resolution process only...But we're all learning!!

RE: Identity Crisis: Attack of the Clone

UMG Developer — Thu, 12 May 2011 14:49:28 GMT

Another nice question, thanks!

RE: Identity Crisis: Attack of the Clone

Franl Quintana — Thu, 12 May 2011 09:25:03 GMT

What about the error with the schema:w00t:

RE: Identity Crisis: Attack of the Clone

Surii — Wed, 11 May 2011 21:18:05 GMT

Excellent question.

RE: Identity Crisis: Attack of the Clone

L' Eomot Inversé — Wed, 11 May 2011 18:09:18 GMT

[quote][b]WayneS (5/11/2011)[/b][hr][quote][b]Tom.Thomson (5/11/2011)[/b][hr]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".[/quote]Tom, I think it was referring to the schema "Playground", which the table was created in.[/quote]Ah, yes, probably. My mistake. I find it hard at times to remember that MS misuses this particular term so horribly, and my reaction :alien: was as it was because I interpreted "schema" as "schema" rather than as what MS calls "schema". Must remember loony MS terminology in future :(.

RE: Identity Crisis: Attack of the Clone

Peter Trast — Wed, 11 May 2011 13:48:37 GMT

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.

RE: Identity Crisis: Attack of the Clone

SQLRNNR — Wed, 11 May 2011 10:57:24 GMT

nice and straight forward - thanks

RE: Identity Crisis: Attack of the Clone

FargoUT — Wed, 11 May 2011 10:54:31 GMT

[quote][b]WayneS (5/11/2011)[/b][hr][quote][b]Tom.Thomson (5/11/2011)[/b][hr]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".[/quote]Tom, I think it was referring to the schema "Playground", which the table was created in.[/quote]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.

RE: Identity Crisis: Attack of the Clone

toddasd — Wed, 11 May 2011 10:50:23 GMT

[quote][b]mtassin (5/11/2011)[/b]...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.[/quote]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.

RE: Identity Crisis: Attack of the Clone

SanDroid — Wed, 11 May 2011 10:47:59 GMT

[quote][b]steven.chester (5/11/2011)[/b][hr]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.[/quote]That comma does not generate any error when the code is executed as listed in the QOTD. :-POf course you would need to execute this command before you execute the QOTD SQL to test that. Don't forget to replace parameters.[code="sql"]CREATE SCHEMA PLAYGROUND AUTHORIZATION <Your User Name, sysname, DBuser>GO[/code]

RE: Identity Crisis: Attack of the Clone

WayneS — Wed, 11 May 2011 09:43:06 GMT

Good question - thanks!

RE: Identity Crisis: Attack of the Clone

WayneS — Wed, 11 May 2011 09:41:16 GMT

[quote][b]Tom.Thomson (5/11/2011)[/b][hr]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".[/quote]Tom, I think it was referring to the schema "Playground", which the table was created in.

RE: Identity Crisis: Attack of the Clone

srkrell — Wed, 11 May 2011 09:37:55 GMT

Excellent Question and thorough explanation.Thanks!

RE: Identity Crisis: Attack of the Clone

steven.chester — Wed, 11 May 2011 09:09:24 GMT

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.

RE: Identity Crisis: Attack of the Clone

mtassin — Wed, 11 May 2011 08:49:52 GMT

[quote][b]Richard Warr (5/11/2011)[/b][hr]A question that enables most people to get the correct answer after some careful thought is by no means "too simple". Especially as over 1/3 of respondants have got it wrong.For me this was close to a "Goldilocks" question. For some it's too hard, for others too soft, but for many, it's just right.[/quote]I got it wrong... only because of two things.1. I've spent the past several weeks going through hundreds of tables with structures like [code]CREATE TABLE staff(STAFFID INT IDENTITY(1,1) NOT NULL,STAFFNAME VARCHAR(100))CREATE UNIQUE CLUSTERED INDEX UCI_PK ON staff(STAFFID)[/code]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.

RE: Identity Crisis: Attack of the Clone

Chris Umbaugh — Wed, 11 May 2011 08:28:53 GMT

good question and explanation. Thanks.

RE: Identity Crisis: Attack of the Clone

SanDroid — Wed, 11 May 2011 08:07:27 GMT

RE: Identity Crisis: Attack of the Clone

SanDroid — Wed, 11 May 2011 08:06:40 GMT

Good easy question, but I do not agree with the answers explination.A unique constraint is what a table needs to garuntee an column does not repeat values.Making a column part of a Primary Key value does create a constraint for Unique values.That is the effect not the cause.If both columns in the table are selected as Primary Key, then you could still insert duplicates into the Unique_Id column.Make sure your Identity columns have there own Unique Index and/or value constraint and you don't have to worry about dupes.

RE: Identity Crisis: Attack of the Clone

mohammed moinudheen — Wed, 11 May 2011 07:33:16 GMT

Really good question

RE: Identity Crisis: Attack of the Clone

malleswarareddy_m — Wed, 11 May 2011 07:30:46 GMT

Excellent question and better explanation.

RE: Identity Crisis: Attack of the Clone

cfradenburg — Wed, 11 May 2011 07:13:24 GMT

[quote][b]sread (5/11/2011)[/b][hr]Won't the create table statement fail due to the comma after the last column name?[/quote]I just tried it on my machine and it ran fine. I do remember this being discussed someplace as being legit and someone mentioning that they have made a practice of doing to make it easier to add columns (one less thing to forget).

RE: Identity Crisis: Attack of the Clone

sread — Wed, 11 May 2011 05:25:04 GMT

Won't the create table statement fail due to the comma after the last column name?

RE: Identity Crisis: Attack of the Clone

Richard Warr — Wed, 11 May 2011 04:24:30 GMT

A question that enables most people to get the correct answer after some careful thought is by no means "too simple". Especially as over 1/3 of respondants have got it wrong.For me this was close to a "Goldilocks" question. For some it's too hard, for others too soft, but for many, it's just right.

RE: Identity Crisis: Attack of the Clone

L' Eomot Inversé — Wed, 11 May 2011 04:16:29 GMT

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".

RE: Identity Crisis: Attack of the Clone

damir60 — Wed, 11 May 2011 03:08:11 GMT

too simple.

RE: Identity Crisis: Attack of the Clone

cengland0 — Wed, 11 May 2011 02:45:34 GMT

Nice question but this one almost had me because of the extra comma in the CREATE TABLE part of the query. When there wasn't a distractor with an error message to address that issue, I assumed that the creation of the table would have to work with the extra comma.

RE: Identity Crisis: Attack of the Clone

nigel. — Wed, 11 May 2011 02:23:35 GMT

Excellent question !

RE: Identity Crisis: Attack of the Clone

sharath.chalamgari — Wed, 11 May 2011 01:48:12 GMT

Simple One i just need to read the question and answers carefully :)

RE: Identity Crisis: Attack of the Clone

srikant maurya — Wed, 11 May 2011 00:29:27 GMT

Good Question,Srikant

RE: Identity Crisis: Attack of the Clone

Koen Verbeeck — Wed, 11 May 2011 00:11:47 GMT

Great question!

RE: Identity Crisis: Attack of the Clone

Gobikannan — Wed, 11 May 2011 00:09:41 GMT

Nice question with nice explanation.

RE: Identity Crisis: Attack of the Clone

bitbucket-25253 — Tue, 10 May 2011 21:14:46 GMT

Nice question - required carefully reading .. but that is how the QOD should .. so again nice question.

Identity Crisis: Attack of the Clone

dun — Tue, 10 May 2011 21:13:27 GMT

Comments posted to this topic are about the item [B]Identity Crisis: Attack of the Clone[/B]