Primary Key and Identity Column's

Question

Post reply

Primary Key and Identity Column's

patelxx

SSC Eights!

Points: 853
More actions
May 23, 2015 at 9:24 am

#398497

Can a Primary Key column also be a Identity column? The reason I am asking this question is because I have created a table and each time I insert data into the Address Table I am also inserting the AddressID, how do I get the Primary Key (AddressID column) to self generate ID values.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

Yes it can.

Create table [schema].[name] (Id int identity(1,1) primary key clustered)

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

patelxx SSC Eights! Points: 853 More actions · Answer 2

patelxx

SSC Eights!

Points: 853

May 30, 2015 at 3:10 pm

#1801838

thank q

patelxx SSC Eights! Points: 853 More actions · Answer 3

Hi Phil,

Why do I need the word Clustered at the end? I've never seen this before.

cheers

Alan Burstein SSC Guru Points: 61141 More actions · Answer 4

patelxx (6/1/2015)
Hi Phil,
Why do I need the word Clustered at the end? I've never seen this before.
cheers

You don't.

When you create a primary key you have the ability to specify if you want the corresponding index to be clustered or nonclustered. The default is clustered so it's not required.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Phil Parkin SSC Guru Points: 246985 More actions · Answer 5

Alan.B (6/1/2015)
patelxx (6/1/2015)
Hi Phil,
Why do I need the word Clustered at the end? I've never seen this before.
cheers
You don't.
When you create a primary key you have the ability to specify if you want the corresponding index to be clustered or nonclustered. The default is clustered so it's not required.

While this is true, it's potentially useful to unmask the defaults by specifying them explicitly. That's also why I tend to specify either NULL or NOT NULL for each of my columns, when designing permanent tables. Not relying on defaults tends to ensure that I think about these things more carefully at design time.

In this case, perhaps the OP learned something useful as a result.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

patelxx SSC Eights! Points: 853 More actions · Answer 6

Yes you are right Phil and good habits make code easier to read. Thank you guys.

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

Alan.B (6/1/2015)
patelxx (6/1/2015)
Hi Phil,
Why do I need the word Clustered at the end? I've never seen this before.
cheers
You don't.
When you create a primary key you have the ability to specify if you want the corresponding index to be clustered or nonclustered. The default is clustered so it's not required.

Unless there is already a clustered index on the table. In that case it will default to nonclustered. You gotta love defaults that change based on other criteria. 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Alan Burstein SSC Guru Points: 61141 More actions · Answer 8

Sean Lange (6/2/2015)
Alan.B (6/1/2015)
patelxx (6/1/2015)
Hi Phil,
Why do I need the word Clustered at the end? I've never seen this before.
cheers
You don't.
When you create a primary key you have the ability to specify if you want the corresponding index to be clustered or nonclustered. The default is clustered so it's not required.
Unless there is already a clustered index on the table. In that case it will default to nonclustered. You gotta love defaults that change based on other criteria. 😉

Excellent point. I was going to add to my original comment that, as a matter of best practice, that I like to specify certain options, such a NULL / NOT NULL (as Phil said) NONCLUSTERED when creating indexes even when that is the default option. I was doing a terrible job with my explanation so I scrapped it.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

ScottPletcher SSC Guru Points: 101120 More actions · Answer 9

Is it just the id and address, or is there a customer id or other parent entity for the address? If so, the clustering key should be on ( customer_id, address_id ), not just address_id.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".