What is the difference between these two statements?

Question

What is the difference between these two statements?

Jason Norsworthy

SSChasing Mays

Points: 636
More actions
November 20, 2010 at 12:36 pm

#229443

When declaring a primary key on a create table statement...
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH ([Removed for simplicity]) ON [PRIMARY]
) ON [PRIMARY]
and
CONSTRAINT [PK_ConstraintName] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH ([Removed for simplicity]) ON [PRIMARY]
) ON [PRIMARY]
Visual Studio's Schema Compare is complaining of a difference and not picking up the primary key added without specifying a constraint name

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Not declaring the name ends up with a system defined one. It's usually used for Temp tables and the like to avoid conflicting names when the proc is run concurrently.

You'll want to use actual names on hard tables, if for no other reason than to make your life easier later. Different schema reviewers don't like the automated names, too, as you've noticed.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jason Norsworthy SSChasing Mays Points: 636 More actions · Answer 2

Thanks for the reply Craig (and for the helpful links in your sig :))

Visual studio's schema compare does indeed do some weird stuff with constraints when they are named/unnamed. After some experimenting, it wasn't that either constraint was missed, it was just reported differently. I'm sorta stuck with the existing database objects, but every constraint of every type will be named from now on.

Thanks,

Jason

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Jason Norsworthy (11/28/2010)
... every constraint of every type will be named from now on.
Thanks,
Jason

Just never name any constraint on a Temp Table or, like Craig said, you'll have some very serious concurrency issues because constraints must be uniquely named throughout a database. Let the system name the constraints on Temp Tables.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jason Norsworthy SSChasing Mays Points: 636 More actions · Answer 4

Jeff Moden (11/28/2010)
Jason Norsworthy (11/28/2010)
... every constraint of every type will be named from now on.
Thanks,
Jason
Just never name any constraint on a Temp Table or, like Craig said, you'll have some very serious concurrency issues because constraints must be uniquely named throughout a database. Let the system name the constraints on Temp Tables.

Thanks for making that distinction