Home Forums SQL Server 2008 SQL Server 2008 - General String as primary key RE: String as primary key

  • February 27, 2015 at 1:16 pm

    #1780776

    Eric M Russell (2/27/2015)

    A non-clustered index won't necessarily result in double I/O on reads. Given a well designed non-clustered index with all required columns included, it can entirely cover the user query without requiring lookups into the table. The page reads are significantly less. The index could even be a small fraction the size of the table, depending on how many columns other than the varchar key are in the table. A NCI on the wide natural key is very commonly put to good use in this way.

    Also, if this is more of an OLAP table than an OLTP table, then I'm totally not concerned about doubling up on total disk storage or doubling up on insert I/O. I've had OLAP tables where I've trippled up on storage by adding indexes. Yes, I've confirmed they're all put to good use. This is fine for medium sized tables that infrequently written (like nightly batch loads) and very frequently queried during normal business hours. At least for me, a table with a few million rows is a medium sized table, if not on the small end of the scale.

    You're just effectively vertically partitioning the table using a nonclus index. You can just do that directly from the main table if that's what you need/want to do.

    You've stilled doubled the I/O to insert to the table. And anytime someone uses one column not in your carefully planned "covering" index, you're back to lookup I/O or a table scan suddenly popping up on a query "that was running fine yesterday".

    I agree not to be as concerned about size on OLAP, but I assume OLTP unless otherwise stated. Most people are careful to point out when it's OLAP / DM / DW data.

    I understand that it's common to do this type of thing, but that doesn't necessarily mean it's best. My point is that the clustering index is most often by far the single most important factor in overall table performance, thus you want the best clustering key for every table. If that happens to be varchar(50), use it; stop trying to work around that just because it doesn't follow general rules.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.