alchemy_nator (10/30/2014)
hi, i'm new here.. i just want to ask you how to specify primary key?? for example :T code is for 'table'
C code is for 'chair'
2014 is year of productions, and many other year used
then i need to make it a primary key just like T20140001 (T-2014-0001) and 0001 number is increasing as auto_increment
thanks to you for your attention :-):-D:-)
I would not make that my primary key. If you do, you'll be limited to 10,000 rows for the year - 0000 through 9999. While you might not think you'll ever need that many rows, experience has taught me that thinking like that gets people in trouble later.
The technical reason for not wanting to use a varchar(9) as the primary key is that when you create a primary key, the default is to make that column the clustered index. The bytes of the clustered index are inherited by all nonclustered indexes. In the case of a varchar(9), that's 11 bytes - 2 for the length of the string plus one byte for every character in the string. While 11 bytes might not sounds like much, it is almost 3 times as much as a 4-byte integer. Multiple the extra 7 bytes times a million rows and you're talking about an extra 7 MB in each nonclustered index. Since data is stored in 8K pages, that's significant.
The primary key should be narrow, unique, unchanging and ever-increasing. I like to use integers for most OLTP tables, but it really does depend on your specific situation.