Very Small Tables

  • I was recently asked for my opinion on adding an identity column and primary key to a very small table. I recommended a unique constraint but no identity column and no index. My logic is as follows and I would appreciate any opinions.

    The table has a total of 20 characters wide with a max number of rows expected to be about 50. At 20 characters I'm figuring on approx 400 rows per page.

    My understanding of indexes is that they help to determine which pages to load into memory and do nothing to determine where a row is within the page. That being the case a table that fits inside one or two pages, possibly as much as a full extent, will get no benefit from an index. I recently read a blog explaining that a clustered index does not always store the data inside the page in key order. See it here.[/url]

    Because of this my feeling is that all an index will do is add an additional page that need to be read.

    I had the same basic thought about the identity column. There are 5 columns that combined need to be unique. However each of them can be null. Because of this it can't be used as a primary key. This would generally make me think of adding an identity column as the primary key, and a unique key or constraint for the data columns. But again since the table is so small it seems like it would just be a waste of space.

    So my question here is does my logic follow? Am I missing something? My thought is that a very small table should be handled somewhat differently than a larger table.

    Thanks for any opinions

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • I'm in agreement with you. Another way to think of it is, why would you put a table of contents in a 1 page "book"?

    Sql Server will ultimately pull the whole table in memory since it's so small, any indexes or PK's will just add overhead. However, the unique index would probably be a good idea to retain data integrity.

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • For small enough tables, SQL Server will ignore indexes anyway, when creating execution plans, even if you do have them.

    If the table ends up growing enough to need one, it can be added then. If you have good reason to expect it never hitting more than 1 page of storage, then indexes would only be useful if you need things like foreign keys referencing the table, not as performance modifiers.

    If you need non-null uniqueness in each column, you can add filtered unique indexes to them. Not for performance, but for data integrity. If it's combinations of columns that need to be unique, that can get a little more complex, but can still almost certainly be worked out. Are you familiar with filtered indexes?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Actually it's the combination of the columns that are unique. I have used filtered indexes before (and think they are a great addition to SQL Server). In this particular case a single unique key worked just fine.

    I'm actually somewhat surprised. I expected to have at least one person disagree with me.

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • Nah. I wouldn't expect a lot of disagreement on this one. Indexing a 1-page table for something other than data integrity purposes, would be silly.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Never mind.. not up for an argument...

    Just note that by adding a unique constraint, you've added a nonclustered index, unless you made it unique clustered.

    While the rows on page may not be stored in the index order, there is an offset array that is stored in index order.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Excellent point. I definitely should have made it clustered if I was going to put any type of index on it. I'm not really sure how much it helps but it couldn't hurt.

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • Often, small tables are used as foreign key lookups.

    In that case, I prefer an surrogate integer clustered primary key to minimize the amount of space required in the child tables.

  • Very true and I would certainly recommend the same. In this particular case it is a lookup table but not a parent to any other table. It's being used to help calculate some type of rate based on 5 different criteria. It will be read a lot, but not joined to any other tables.

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • I would also take into consideration what is accessing the data. Entity Framework in .NET positively hates compound primary keys when generating POCO classes. Therefore, by default have an identity key, even on small tables just to make life easy. I agree with you about the downsides on the SQL side, but they are not outweighed by the consuming client and its own downfalls.

    Just to play devils advocate I guess.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply