tag:blogger.com,1999:blog-5545011210222359771.post7716395840700004359..comments2024-02-28T03:25:40.469-08:00Comments on SQL Server and BI/BA With A Smile: 5 Things a developer should know about databasesThomas LeBlanchttp://www.blogger.com/profile/02128071506560671112noreply@blogger.comBlogger9125tag:blogger.com,1999:blog-5545011210222359771.post-89485675436517015462014-03-18T12:36:56.641-07:002014-03-18T12:36:56.641-07:00Thomas LeBlancMarch 18, 2014 at 6:47 AM
Maria, my...<br /><br /><br />Thomas LeBlancMarch 18, 2014 at 6:47 AM<br /><br />Maria, my point about "joining is faster with an Index that is an integer?" is about the history of what people believe an identity columns is used as a key. You are correct that sometime it is not significant.<br /><br />The index I was referring to is one with an Include clause. I have seen where this is over used and multiple ones become part of a deadlock.<br /><br />Thanks for the comments,<br />Thomas<br />Thomas LeBlanchttps://www.blogger.com/profile/02128071506560671112noreply@blogger.comtag:blogger.com,1999:blog-5545011210222359771.post-42730101859825752014-03-18T12:36:41.852-07:002014-03-18T12:36:41.852-07:00
Thomas LeBlancMarch 18, 2014 at 6:49 AM
George...<br /><br /><br />Thomas LeBlancMarch 18, 2014 at 6:49 AM<br /><br />George,<br /><br />You are correct. That is actually a good point. Why create an identity for a column lke State Abbreviation? The Int or Bigint would be larger than the 2 character state column. I am actually for the StateAbbreviation for the key and not an identity column in this case.<br /><br />Thanks for the comments,<br />Thomas<br />Thomas LeBlanchttps://www.blogger.com/profile/02128071506560671112noreply@blogger.comtag:blogger.com,1999:blog-5545011210222359771.post-71200791585478456882014-03-18T12:36:12.801-07:002014-03-18T12:36:12.801-07:00Chris,
You would not be able to enter John Smith ...Chris,<br /><br />You would not be able to enter John Smith twice in this table. The creation of this unique constraint was to bring up this in conversation with others. You really have to think when creating tables in a transactional database and make sure you have a natural key to find the uniqueness.<br /><br />Thanks for the comments,<br />ThomasThomas LeBlanchttps://www.blogger.com/profile/02128071506560671112noreply@blogger.comtag:blogger.com,1999:blog-5545011210222359771.post-79739519007439457912014-03-17T11:33:40.477-07:002014-03-17T11:33:40.477-07:00With respect to your example of a natural key, the...With respect to your example of a natural key, the index appears to be a unique key so how do you add 2 customers with the same name? i.e. John Smith.Anonymoushttps://www.blogger.com/profile/09422008697178998923noreply@blogger.comtag:blogger.com,1999:blog-5545011210222359771.post-27534438649768411092014-03-17T10:15:56.231-07:002014-03-17T10:15:56.231-07:00Interesting question about the statement "3.T...Interesting question about the statement "3.The joins are faster and helps with related tables when used as part of the related table’s primary and/or foreign key" may not be correct. If one joins on a low cardinality CHAR(2) column is it faster than using an INT or BIGINT? I don't have the time to dig right now, but I'd guess the CHAR(2) compare in machine language is a loop that might be unrolled whereas the INT compare is a single instruction. On a 64-bit implementation the BIGINT might be a single instruction too. Anyone know?Anonymoushttps://www.blogger.com/profile/01585163540779331779noreply@blogger.comtag:blogger.com,1999:blog-5545011210222359771.post-36927048334287389732014-03-17T08:03:11.071-07:002014-03-17T08:03:11.071-07:00Joining is faster with an Index that is an integer...Joining is faster with an Index that is an integer? Are you sure? I would think it is either the same or just slightly faster. The big advantage with the Identity is you only have to update the "Natural Key" in one place.<br /><br />With "include indexes cause Deadlocking", are you sure it wasn't an index involved with a Deadlock that happened to have an include?<br />Anonymoushttps://www.blogger.com/profile/15495685848378608348noreply@blogger.comtag:blogger.com,1999:blog-5545011210222359771.post-56888268520128425602014-03-17T07:33:53.497-07:002014-03-17T07:33:53.497-07:00This comment has been removed by the author.Anonymoushttps://www.blogger.com/profile/15495685848378608348noreply@blogger.comtag:blogger.com,1999:blog-5545011210222359771.post-89073755179776483992014-03-17T05:11:39.693-07:002014-03-17T05:11:39.693-07:00Thanks Saeed, I hope to follow-up from comments I ...Thanks Saeed, I hope to follow-up from comments I have received.Thomas LeBlanchttps://www.blogger.com/profile/02128071506560671112noreply@blogger.comtag:blogger.com,1999:blog-5545011210222359771.post-54306993223043515802014-03-17T02:31:38.101-07:002014-03-17T02:31:38.101-07:00Nice article. Thank youNice article. Thank youAnonymoushttps://www.blogger.com/profile/11570967443179714172noreply@blogger.com