November 14, 2013 at 2:16 pm
For a table with a Primary Key on an Identity (int) field [myTableId], I would think that SQL Server automatically writes a reference to this PK [edit:] whenever a non-clustered index is created, ergo NOT requiring it to be specified as part of the non-clustered index.
Let's say I want to index the [Active] field on the following table
MyTable
myTableId int (Identity) not null (PK)
field1 ...
field2 ...
Active bool
Should I create the index like so, or is adding [myTableId] not necessary?
CREATE INDEX [IX_myTable_Active]
ON [DB].[dbo].[MyTable] [active], [myTableId])
TIA!
November 14, 2013 at 3:02 pm
I would suggest that you take a look at the stairways articles right here on SSC. Proper knowledge of indexes is vital to the performance of your system.
http://www.sqlservercentral.com/stairway/72399/[/url]
Also, SQL server does not have a bool datatype. 😛
_______________________________________________________________
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/
November 14, 2013 at 3:02 pm
You question is not very clear, but guess what you're asking...
By specifying identity(1,1) primary key, you already have clustered primary key. For the property identity(1,1) there is no need to specify not null, because it's by definition not null.
For the other columns if you want to have an index, you have to create it, for e.g.
create nonclustered index IX_myTable_Active on myTable(Active)
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
November 14, 2013 at 3:09 pm
IgorMi (11/14/2013)
You question is not very clear, but guess what you're asking...By specifying identity(1,1) primary key, you already have clustered primary key. For the property identity(1,1) there is no need to specify not null, because it's by definition not null.
For the other columns if you want to have an index, you have to create it, for e.g.
create nonclustered index IX_myTable_Active on myTable(Active)Regards
IgorMi
A primary key does not always mean a clustered index. You can have a nonclustered index on your primary key and a different clustered index if you desire. The default is that the primary key is clustered but it can easily be specified otherwise.
create table test
(
MyVal int identity primary key nonclustered
)
drop table test
_______________________________________________________________
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/
November 14, 2013 at 3:13 pm
Sean Lange (11/14/2013)
IgorMi (11/14/2013)
You question is not very clear, but guess what you're asking...By specifying identity(1,1) primary key, you already have clustered primary key. For the property identity(1,1) there is no need to specify not null, because it's by definition not null.
For the other columns if you want to have an index, you have to create it, for e.g.
create nonclustered index IX_myTable_Active on myTable(Active)Regards
IgorMi
A primary key does not always mean a clustered index. You can have a nonclustered index on your primary key and a different clustered index if you desire. The default is that the primary key is clustered but it can easily be specified otherwise.
create table test
(
MyVal int identity primary key nonclustered
)
drop table test
Also, if there is already a clustered index defined on the table when you define a primary key, it will be created as a nonclustered index.
November 14, 2013 at 3:50 pm
Lynn Pettis (11/14/2013)
Sean Lange (11/14/2013)
IgorMi (11/14/2013)
You question is not very clear, but guess what you're asking...By specifying identity(1,1) primary key, you already have clustered primary key. For the property identity(1,1) there is no need to specify not null, because it's by definition not null.
For the other columns if you want to have an index, you have to create it, for e.g.
create nonclustered index IX_myTable_Active on myTable(Active)Regards
IgorMi
A primary key does not always mean a clustered index. You can have a nonclustered index on your primary key and a different clustered index if you desire. The default is that the primary key is clustered but it can easily be specified otherwise.
create table test
(
MyVal int identity primary key nonclustered
)
drop table test
Also, if there is already a clustered index defined on the table when you define a primary key, it will be created as a nonclustered index.
You're correct. If you specify "nonclustered" than it will be a nonclustered primary key. And if there is already a clustered key, the primary key will be a nonclustered.
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
November 14, 2013 at 4:09 pm
Edited orig post for clarity.
You guys are off topic. The question is whether adding the PK (also clustered index) to a new index is superfluous.
So I was just able to talk to a DBA friend of mine who said:
1. SQL server automatically adds the Clustered Key to the index, but it is hidden.
2. If the clustered index is not unique, it also adds a "uniqifier" (his term).
That said, when he ran an index suggestion script over a few tables, it DID include the PK in the INCLUDES list in a couple of the 15-20 suggested indexes.
I'll consider this to be unverified, but accurate info unless someone would like to confirm/deny/clarify.
thx
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply