February 11, 2014 at 2:36 am
Hi all,
Can someone tell me what are the pros and cons for choosing a character CHAR(3) data type for composite primary key in SQL ?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 11, 2014 at 2:49 am
How can a single column be a composite key?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 11, 2014 at 2:51 am
Koen Verbeeck (2/11/2014)
How can a single column be a composite key?
😀
Assuming that there is at least one more column involved here, I'd say that to some extent it depends on what datatype(s) you are using for the other column(s).
Regards
Lempster
February 11, 2014 at 3:06 am
Koen Verbeeck (2/11/2014)
How can a single column be a composite key?
There is anthor columns also...
there is an Identity column which is currently defined as PK so I want to add another two columns of datatype CHAR(3)
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 11, 2014 at 3:21 am
kapil_kk (2/11/2014)
Koen Verbeeck (2/11/2014)
How can a single column be a composite key?There is anthor columns also...
there is an Identity column which is currently defined as PK so I want to add another two columns of datatype CHAR(3)
Why?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 11, 2014 at 3:28 am
Koen Verbeeck (2/11/2014)
kapil_kk (2/11/2014)
Koen Verbeeck (2/11/2014)
How can a single column be a composite key?There is anthor columns also...
there is an Identity column which is currently defined as PK so I want to add another two columns of datatype CHAR(3)
Why?
Bcoz I am getting performance issue due to that.. so I am thinking of including other two columns of NCHAR(3) datatype which is common in all tables of database
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 11, 2014 at 3:42 am
You are getting performance issues because the PK is an identity?
The primary key is already unique, so adding extra columns doesn't make it more unique.
It seems you are just missing an index on the table. Maybe the PK (the identity) should be non-clustered and you should create a clustered index using your nchar(3) columns.
But this all depends on your data and the queries.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 11, 2014 at 4:06 am
Koen Verbeeck (2/11/2014)
You are getting performance issues because the PK is an identity?The primary key is already unique, so adding extra columns doesn't make it more unique.
It seems you are just missing an index on the table. Maybe the PK (the identity) should be non-clustered and you should create a clustered index using your nchar(3) columns.
But this all depends on your data and the queries.
Yes primary key is unique but its not,using anywhere in my joins that's why i think of including char(3) columns with pk
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 11, 2014 at 4:14 am
kapil_kk (2/11/2014)
Koen Verbeeck (2/11/2014)
You are getting performance issues because the PK is an identity?The primary key is already unique, so adding extra columns doesn't make it more unique.
It seems you are just missing an index on the table. Maybe the PK (the identity) should be non-clustered and you should create a clustered index using your nchar(3) columns.
But this all depends on your data and the queries.
Yes primary key is unique but its not,using anywhere in my joins that's why i think of including char(3) columns with pk
Why don't you create an additional covering index?
Adding extra columns to the clustered index has consequences. Maybe this affects other queries who don't use those columns.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 11, 2014 at 4:22 am
are there to many joins you are using in your query (including these char columns). if yes then you can create a non clustered index on these two columns.
February 11, 2014 at 5:33 am
Creating Non-Clustered index might suffice your need...Before removing index from unique column please do think about the queries which are mostly run against the table in the discussion... Please do analyse the performance of the queries once you add the indexes
February 11, 2014 at 5:37 am
Koen Verbeeck (2/11/2014)
Adding extra columns to the clustered index has consequences. Maybe this affects other queries who don't use those columns.
And adding extra columns to the primary key can have massive consequences, including possibly having to recreate every foreign key which references the table, adding those other columns to other tables and as a side effect changing what columns are enforced as unique.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 11, 2014 at 6:00 am
GilaMonster (2/11/2014)
Koen Verbeeck (2/11/2014)
Adding extra columns to the clustered index has consequences. Maybe this affects other queries who don't use those columns.And adding extra columns to the primary key can have massive consequences, including possibly having to recreate every foreign key which references the table, adding those other columns to other tables and as a side effect changing what columns are enforced as unique.
It's a good thing you fill in the gaps 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 11, 2014 at 6:50 am
Koen Verbeeck (2/11/2014)
kapil_kk (2/11/2014)
Koen Verbeeck (2/11/2014)
You are getting performance issues because the PK is an identity?The primary key is already unique, so adding extra columns doesn't make it more unique.
It seems you are just missing an index on the table. Maybe the PK (the identity) should be non-clustered and you should create a clustered index using your nchar(3) columns.
But this all depends on your data and the queries.
Yes primary key is unique but its not,using anywhere in my joins that's why i think of including char(3) columns with pk
Why don't you create an additional covering index?
Adding extra columns to the clustered index has consequences. Maybe this affects other queries who don't use those columns.
Are you talking about creating Included columns as an additional covering index
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 11, 2014 at 6:53 am
kapil_kk (2/11/2014)
Koen Verbeeck (2/11/2014)
kapil_kk (2/11/2014)
Koen Verbeeck (2/11/2014)
You are getting performance issues because the PK is an identity?The primary key is already unique, so adding extra columns doesn't make it more unique.
It seems you are just missing an index on the table. Maybe the PK (the identity) should be non-clustered and you should create a clustered index using your nchar(3) columns.
But this all depends on your data and the queries.
Yes primary key is unique but its not,using anywhere in my joins that's why i think of including char(3) columns with pk
Why don't you create an additional covering index?
Adding extra columns to the clustered index has consequences. Maybe this affects other queries who don't use those columns.
Are you talking about creating Included columns as an additional covering index
Maybe. Without any table DDL, queries used and query plans we are just guessing here.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply