May 23, 2015 at 9:24 am
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.
May 23, 2015 at 10:05 am
Yes it can.
Create table [schema].[name] (Id int identity(1,1) primary key clustered)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 30, 2015 at 3:10 pm
thank q
June 1, 2015 at 3:26 pm
Hi Phil,
Why do I need the word Clustered at the end? I've never seen this before.
cheers
June 1, 2015 at 4:24 pm
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.
-- Itzik Ben-Gan 2001
June 2, 2015 at 12:47 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 2, 2015 at 10:37 am
Yes you are right Phil and good habits make code easier to read. Thank you guys.
June 2, 2015 at 10:53 am
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/
June 2, 2015 at 11:54 am
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.
-- Itzik Ben-Gan 2001
June 2, 2015 at 1:45 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply