tag:blogger.com,1999:blog-20718812.post7660612037477302685..comments2023-11-03T17:28:33.550+05:30Comments on DotNetJalps: GUID Vs Int data type as primary keyJalpesh Vadgamahttp://www.blogger.com/profile/11897824025804539164noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-20718812.post-41522113401828271812011-01-26T10:26:33.759+05:302011-01-26T10:26:33.759+05:30All relational database engines are optimized to w...All relational database engines are optimized to work with indexes on columns whose data value increments after insertions, i.e., integer data types, - this is known as a natural progression.<br /><br />Relational database engines are NOT optimized to work with indexes on columns whose data value is randomized after insertions, i.e., GUIDs - this is known as an un-natural progression.<br /><br />If you are dealing with extremely small data sets, and you don't care if you are perceived as not knowning how to choose a suitable data type, then use GUID. <br /><br />But, if you are dealing with large data sets (particularly when getting into GBs and TBs) you will never be able to scale the database if you are use GUIDs as a data type for index columns or primary key columns.<br /><br />As regards the listed advantages of GUIDs vs integers, well, not a single one of them is valid. Whenever loading a record sourced from another database always deprecated the primary key and generate a new integer primary key value for that record. As regards duplication, if you have viable algorithms in place there is no chance of duplication (this is just an common excuse for bad programming). As regards loading large data set, GUID offer no benefit, but in fact will seriously fragment the data files. Integer data types are perfectly suited for loading small or extremely large data set, that is when you know how to write decent code.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-20718812.post-41750702890182313132010-12-03T03:00:31.277+05:302010-12-03T03:00:31.277+05:30You can use BIGINT to decrease chances of duplicat...You can use BIGINT to decrease chances of duplication.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-20718812.post-63449935463092840672010-12-02T00:05:43.492+05:302010-12-02T00:05:43.492+05:30You missed an advantage of GUIDs which can partial...You missed an advantage of GUIDs which can partially nullify disadvantage #3:<br /><br />You can create a GUID in code and use it before you even hit the database. Then you don't need a function like Scope_Identity().Davenoreply@blogger.com