Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Authors
About us
Contact us
Newsletters
Write for us
Keep up to date - daily newsletter:
Sign up
Back
SQLServerCentral
Register
Home
»
SQLServerCentral.com
»
Articles Requested
»
PK v Unique Index
PK v Unique Index
Post reply
Like
19
Add to Briefcase
PK v Unique Index
View
Options
Author
Message
Steve Jones
Steve Jones
Posted 3 years ago
#1662215
SSC Guru
Group: Administrators
Points: 401791
Visits: 20456
I user request to get an article, which would explain in details the differences between primary key and unique index, especially from disk i/o point of view, I think it will be of a massive help to many SQL Server Central readers.
Follow me on Twitter:
@way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog:
www.voiceofthedba.com
3
Quote
Jeff Moden
Jeff Moden
Posted 3 years ago
#1662259
SSC Guru
Group: General Forum Members
Points: 621167
Visits: 45256
Steve Jones - SSC Editor (2/20/2015)
I user request to get an article, which would explain in details the differences between primary key and unique index, especially from disk i/o point of view, I think it will be of a massive help to many SQL Server Central readers.
Are you sure you're not getting PKs and Unique indexes mixed up with Clustered and Non-Clustered Indexes here?
--Jeff Moden
RBAR
is pronounced ree-bar and is a Modenism for
R
ow-
B
y-
A
gonizing-
R
ow.
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
0
Quote
Steve Jones
Steve Jones
Posted 3 years ago
#1662264
SSC Guru
Group: Administrators
Points: 401791
Visits: 20456
That's what a user asked for.
I think that there shouldn't be any difference between a PK and unique index, whether CI or NCI, but perhaps there's some misunderstanding out there.
Be good to just have someone look at this and document it.
Not sure about the CI v NCI part. That's covered in places, but I'll look over some of our indexing pieces and see what I think.
Follow me on Twitter:
@way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog:
www.voiceofthedba.com
3
Quote
Tom Thomson
Tom Thomson
Posted 3 years ago
#1662270
SSC Guru
Group: General Forum Members
Points: 60427
Visits: 13181
Steve Jones - SSC Editor (2/20/2015)
I think that there shouldn't be any difference between a PK and unique index, whether CI or NCI, but perhaps there's some misunderstanding out there.
Actually there's a difference in that columns in a unique index can contain nulls. In the case of a single column index this is no big deal, because it can have at most one null. But with a multi-column index the number of nulls isn't really limited, the only limit is that two nulls are considered equal when determining uniqueness. I'm fairly sure that this is actually of no practical interest as far as disk io is concerned, though.
Tom
3
Quote
Eirikur Eiriksson
Eirikur Eiriksson
Posted 3 years ago
#1662286
SSC Guru
Group: General Forum Members
Points: 115585
Visits: 21481
TomThomson (2/20/2015)
Steve Jones - SSC Editor (2/20/2015)
I think that there shouldn't be any difference between a PK and unique index, whether CI or NCI, but perhaps there's some misunderstanding out there.
Actually there's a difference in that columns in a unique index can contain nulls. In the case of a single column index this is no big deal, because it can have at most one null. But with a multi-column index the number of nulls isn't really limited, the only limit is that two nulls are considered equal when determining uniqueness. I'm fairly sure that this is actually of no practical interest as far as disk io is concerned, though.
Wording it slightly differently, primary key column cannot be nullable, unique index column can. As for the IO goes, there is absolutely no difference between a non-clustered primary key and a unique index.
2
Quote
Jeff Moden
Jeff Moden
Posted 3 years ago
#1662312
SSC Guru
Group: General Forum Members
Points: 621167
Visits: 45256
I guess my point is that while there is the difference in Nullability, an NC-PK isn't really going to have any I/O advantage over a Unique NCI as the user thinks. The confusion between "PK" and "Clustered" may be exactly what is article-worthy here because a lot of folks think that a PK has to be the Clustered Index.
--Jeff Moden
RBAR
is pronounced ree-bar and is a Modenism for
R
ow-
B
y-
A
gonizing-
R
ow.
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
2
Quote
Eirikur Eiriksson
Eirikur Eiriksson
Posted 3 years ago
#1662324
SSC Guru
Group: General Forum Members
Points: 115585
Visits: 21481
Jeff Moden (2/21/2015)
I guess my point is that while there is the difference in Nullability, an NC-PK isn't really going to have any I/O advantage over a Unique NCI as the user thinks. The confusion between "PK" and "Clustered" may be exactly what is article-worthy here because a lot of folks think that a PK has to be the Clustered Index.
Good point! Would it be worth while touching on the storage difference between CL-PK vs. NC-PK, Unique Index and Unique Constraint or rather that the latter three are all implemented as NC indices? Somehow have the recollection that this has already been covered.
0
Quote
Jeff Moden
Jeff Moden
Posted 3 years ago
#1662329
SSC Guru
Group: General Forum Members
Points: 621167
Visits: 45256
Eirikur Eiriksson (2/21/2015)
Jeff Moden (2/21/2015)
I guess my point is that while there is the difference in Nullability, an NC-PK isn't really going to have any I/O advantage over a Unique NCI as the user thinks. The confusion between "PK" and "Clustered" may be exactly what is article-worthy here because a lot of folks think that a PK has to be the Clustered Index.
Good point! Would it be worth while touching on the storage difference between CL-PK vs. NC-PK, Unique Index and Unique Constraint or rather that the latter three are all implemented as NC indices? Somehow have the recollection that this has already been covered.
Heh... just about
everything
has already been covered on this site in the form of an article. I sometimes have to remind myself that there's nothing wrong with a fresh perspective. For example, I was really nervous about getting "bad marks" from heavy hitters that already knew what a Tally Table was when I wrote the article on the subject but I also knew that a lot of folks still didn't know what it was. Of those that did, a lot of people didn't understand how it worked nor did they have the understanding that a SELECT is actually like a loop.
The other thing is that a lot of folks don't say "Today, I'm going to sit down and learn about the differences in the types of indexes" but they
will
read an article that pops up in the SSC headlines. That's why Steve republishes certain articles on Fridays.
To answer your questions, yes. I believe that such an article as you've described, especially if it were geared to freshman understanding, is exactly the kind of article I thing Steve is looking for based on the question that he said he received and some of the posts that we continue to see. Of course, it should focus on the question that Steve asked... "PK vs Unique Index".
I'm actually out of line here, though, because I've taken to speaking for Steve without asking. With that point in mind...
Steve, is THAT what you're thinking about for an article here?
--Jeff Moden
RBAR
is pronounced ree-bar and is a Modenism for
R
ow-
B
y-
A
gonizing-
R
ow.
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
0
Quote
Tom Thomson
Tom Thomson
Posted 3 years ago
#1662334
SSC Guru
Group: General Forum Members
Points: 60427
Visits: 13181
I think that there are only three points that need to be got over to people on this topic:
(a) a nonclustered primary key and a non-clustered unique index where the columns are constrained NOT NULL are functionally and performance-wise identical.
(b) a clustered primary key and a clustered unique index where the colums are constraint NOT NULL are functionally and performance-wise identical.
(c) when columns in a unique index are not constrained NOT NULL there is a trivial functional difference from the case where they are constrained NOT NULL, but there is still no performance difference from the correstponsing primary key. The functional difference is sometimes useful - sometimes it is useful to allow nulls, sometimes it isn't, somethimes it is usefull to forbid nulls, sometimes it isnt, it depends on whether it is useful to be able to insert things before one knows enough to say exactly what they are or more useful to insist that we know what something is before we insert it; But this trivial difference has no impact whatever on IO performance, or (in fact) on any other aspect of performance.
I think there are some articles already covering this (I've read some good articles about indexes here; but maybe I wouldn't have noticed if they skipped this extrremely elementary level, so I'm not really certain it's covered). I also think that it's such a basic thing that every DBA or Database developer should be 100% aware of it before they are allowed to even think about schema desig, never mind actually do any. The thing is that I can't envisage an article aimed only at comparing the unique index with a primary key on the same columns being any longer than half a page (and I would probably have trouble making it even that long, despite my regrettable inclination towards pedantic verbosity).
Tom
3
Quote
Steve Jones
Steve Jones
Posted 3 years ago
#1663027
SSC Guru
Group: Administrators
Points: 401791
Visits: 20456
Tom has a good summary here. If someone would like to tackle a short, simple, intro piece, we'd appreciate it.
Follow me on Twitter:
@way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog:
www.voiceofthedba.com
3
Quote
Go
Post reply
Like
19
Add to Briefcase
Post quoted reply
Permissions
You
can't
post new topics.
You
can't
post topic replies.
You
can't
post new polls.
You
can't
post replies to polls.
You
can't
edit your own topics.
You
can't
delete your own topics.
You
can't
edit other topics.
You
can't
delete other topics.
You
can't
edit your own posts.
You
can't
edit other posts.
You
can't
delete your own posts.
You
can't
delete other posts.
You
can't
post events.
You
can't
edit your own events.
You
can't
edit other events.
You
can't
delete your own events.
You
can't
delete other events.
You
can't
send private messages.
You
can't
send emails.
You
can
read topics.
You
can't
vote in polls.
You
can't
upload attachments.
You
can
download attachments.
You
can't
post HTML code.
You
can't
edit HTML code.
You
can't
post IFCode.
You
can't
post JavaScript.
You
can
post emoticons.
You
can't
post or upload images.
Select a forum
SQL Server 2017
SQL Server 2017 - Administration
SQL Server 2017 - Development
SQL Server 2016
SQL Server 2016 - Administration
SQL Server 2016 - Development and T-SQL
SQL Server 2014
Administration - SQL Server 2014
Development - SQL Server 2014
SQL Server 2012
SQL 2012 - General
SQL Server 2012 - T-SQL
SQL Server vNext
SQL Server 15 - Administration
SQL Server 15 - Development
SQL Server 2008
SQL Server 2008 - General
T-SQL (SS2K8)
June 2007 CTP
Working with Oracle
July CTP
SQL Server Newbies
Security (SS2K8)
SQL Server 2008 High Availability
SQL Server 2008 Administration
Data Corruption (SS2K8 / SS2K8 R2)
SQL Server 2008 Performance Tuning
Cloud Computing
SQL Azure - Development
SQL Azure - Administration
Amazon AWS and other cloud vendors
General Cloud Computing Questions
CosmosDB
Azure Data Lake
Azure Machine Learning
Reporting Services
Reporting Services
Reporting Services 2005 Administration
Reporting Services 2005 Development
Reporting Services 2008/R2 Administration
Reporting Services 2008 Development
SSRS 2012
SSRS 2014
SSRS 2016
Programming
Connecting
General
SMO/RMO/DMO
XML
Service Broker
Powershell
Testing
TFS/Data Dude/DBPro
SSDT
Continuous Integration, Deployment, and Delivery
R Services and R Language
Data Warehousing
Integration Services
Strategies and Ideas
Analysis Services
Data Transformation Services (DTS)
Performance Point
Data Mining
PowerPivot
R language
Machine Learning
Database Design
Disaster Recovery
Design Ideas and Questions
Relational Theory
Hardware
Virtualization
Security and Auditing
SQLServerCentral.com
Anything that is NOT about SQL!
Contests!
Editorials
SQLServerCentral.com Announcements
SQLServerCentral.com Website Issues
Suggestions
Tag Issues with Content
Podcast Feedback
SQLServerCentral.com Test Forum
Articles Requested
SQL Server 2005
Administering
Backups
Business Intelligence
CLR Integration and Programming.
Data Corruption
Development
Working with Oracle
SQL Server 2005 Compact Edition
SQL Server 2005 General Discussion
SQL Server 2005 Security
SQL Server 2005 Strategies
SS2K5 Replication
SQL Server Express
SQL Server 2005 Performance Tuning
SQL Server 2005 Integration Services
T-SQL (SS2K5)
SQL Server Newbies
SQL Server 7,2000
Administration
Backups
Data Corruption
General
Globalization
In The Enterprise
Working with Oracle
Security
Strategies
SQL Server Newbies
Service Packs
SQL Server CE
Performance Tuning
Replication
Sarbanes-Oxley
T-SQL
SQL Server Agent
SQL Server and other platforms
MySQL
Oracle
PostgreSQL
DB2
SQL Server and Sharepoint
Older Versions of SQL (v6.5, v6.0, v4.2)
Older Versions of SQL (v6.5, v6.0, v4.2)
Career
Certification
Employers and Employees
Events
Job Postings
Resumes and Job Hunters
Presentations and Speaking
Retired Members
Testing Center
Question of the Day (QOD)
SQL Server Security Skills
Microsoft Access
Microsoft Access
Products and Books
Third Party Products
CA
SQLCentric
Extreme Technologies.
Innovartis
Embarcadero
SQL Sentry
Golden Gate Software
Idera
Lumigent
Red Gate Software
Quest Software
ApexSQL
Sonasoft
Discussions about Books
Discuss Programming Books
Discuss XML Books
Discuss Data Warehousing Books
Discuss T-SQL Books
Discuss DTS Books
Discuss SQL Server 2000 Books
Discuss SQL Server 7.0 Books
Notification Services
Administration
Article Discussions
Future Versions
SQL 12
Narrow your search by forum
Explore
Home
Latest
Popular
Calendar
Members
Who's on
Moderators
Forums
SQL Server 2017
SQL Server 2017 - Administration
SQL Server 2017 - Development
SQL Server 2016
SQL Server 2016 - Administration
SQL Server 2016 - Development and T-SQL
SQL Server 2014
Administration - SQL Server 2014
Development - SQL Server 2014
SQL Server 2012
SQL 2012 - General
SQL Server 2012 - T-SQL
SQL Server vNext
SQL Server 15 - Administration
SQL Server 15 - Development
SQL Server 2008
SQL Server 2008 - General
T-SQL (SS2K8)
June 2007 CTP
Working with Oracle
July CTP
SQL Server Newbies
Security (SS2K8)
SQL Server 2008 High Availability
SQL Server 2008 Administration
Data Corruption (SS2K8 / SS2K8 R2)
SQL Server 2008 Performance Tuning
Cloud Computing
SQL Azure - Development
SQL Azure - Administration
Amazon AWS and other cloud vendors
General Cloud Computing Questions
CosmosDB
Azure Data Lake
Azure Machine Learning
Reporting Services
Reporting Services
Reporting Services 2005 Administration
Reporting Services 2005 Development
Reporting Services 2008/R2 Administration
Reporting Services 2008 Development
SSRS 2012
SSRS 2014
SSRS 2016
Programming
Connecting
General
SMO/RMO/DMO
XML
Service Broker
Powershell
Testing
TFS/Data Dude/DBPro
SSDT
Continuous Integration, Deployment, and Delivery
R Services and R Language
Data Warehousing
Integration Services
Strategies and Ideas
Analysis Services
Data Transformation Services (DTS)
Performance Point
Data Mining
PowerPivot
R language
Machine Learning
Database Design
Disaster Recovery
Design Ideas and Questions
Relational Theory
Hardware
Virtualization
Security and Auditing
SQLServerCentral.com
Anything that is NOT about SQL!
Contests!
Editorials
SQLServerCentral.com Announcements
SQLServerCentral.com Website Issues
Suggestions
Tag Issues with Content
Podcast Feedback
SQLServerCentral.com Test Forum
Articles Requested
SQL Server 2005
Administering
Backups
Business Intelligence
CLR Integration and Programming.
Data Corruption
Development
Working with Oracle
SQL Server 2005 Compact Edition
SQL Server 2005 General Discussion
SQL Server 2005 Security
SQL Server 2005 Strategies
SS2K5 Replication
SQL Server Express
SQL Server 2005 Performance Tuning
SQL Server 2005 Integration Services
T-SQL (SS2K5)
SQL Server Newbies
SQL Server 7,2000
Administration
Backups
Data Corruption
General
Globalization
In The Enterprise
Working with Oracle
Security
Strategies
SQL Server Newbies
Service Packs
SQL Server CE
Performance Tuning
Replication
Sarbanes-Oxley
T-SQL
SQL Server Agent
SQL Server and other platforms
MySQL
Oracle
PostgreSQL
DB2
SQL Server and Sharepoint
Older Versions of SQL (v6.5, v6.0, v4.2)
Older Versions of SQL (v6.5, v6.0, v4.2)
Career
Certification
Employers and Employees
Events
Job Postings
Resumes and Job Hunters
Presentations and Speaking
Retired Members
Testing Center
Question of the Day (QOD)
SQL Server Security Skills
Microsoft Access
Microsoft Access
Products and Books
Third Party Products
CA
SQLCentric
Extreme Technologies.
Innovartis
Embarcadero
SQL Sentry
Golden Gate Software
Idera
Lumigent
Red Gate Software
Quest Software
ApexSQL
Sonasoft
Discussions about Books
Discuss Programming Books
Discuss XML Books
Discuss Data Warehousing Books
Discuss T-SQL Books
Discuss DTS Books
Discuss SQL Server 2000 Books
Discuss SQL Server 7.0 Books
Notification Services
Administration
Article Discussions
Future Versions
SQL 12
SQLServerCentral
Register
Search
Narrow your search by forum
Unthreaded, ascending
Unthreaded, descending
Subscribe to topic
Print topic
RSS feed
Go to topics forum
Jump to page
Jump to page
Copyright © 2002-2018 Redgate. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.