Rank: Member
Joined: 5/31/2006(UTC)
Posts: 10
|
<SPAN style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Verdana">Does anybody have an idea why the "bvin" column, which is a primary key column in all tables, has been declared as "varchar(36)" as oppose to "uniqueidentifier"?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<SPAN style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Verdana"> <o:p></o:p>
<SPAN style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Verdana">It looks like it anyway stores GUID values. So although it is still going to work, it is a rule of thumb for relational databases, that if you want to follow proper design rules: int, bigint or uniqueidentifier types should be used for primary key columns.<o:p></o:p>
<SPAN style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Verdana"><o:p> </o:p>
<SPAN style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Verdana">I have also noticed that “bvin” column is clustered. <SPAN style="mso-spacerun: yes"> This is another design issue: Clustered indexes are used to keep the physical order of the row on the disk to be the same as the clustered index. <SPAN style="mso-spacerun: yes"> This is usually done to optimize data retrieval by range of dates or range of number. <SPAN style="mso-spacerun: yes"> Since “bvin” column contains random GUIDs it is completely pointless to set it to be a clustered index.<o:p></o:p>
<SPAN style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Verdana"><o:p> </o:p>
<SPAN style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Verdana">Furthermore, creating a clustered index on a varchar column which contain a random GUID string creates a significant performance impact (on larger databases) since every time a row is inserted, the table has to be reshuffled based on the value of the bvin column of the new row.<o:p></o:p> |
|
|
|
|
|
|
Rank: Member
Joined: 11/5/2003(UTC)
Posts: 2,136
Was thanked: 1 time(s) in 1 post(s)
|
I normally ignore condescending posts, but perhaps I am misreading "...if you want to follow proper...". My guess is that BV Software was trying to stay away from proprietary data types. Regardless of whether it is proper or not, uniqueidentifier would be more efficient (about 20 less bytes per row).
I don't know why the indexes are clustered. |
|
|
|
|
|
|
Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.