Very Small Tables

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Very Small Tables

Very Small Tables

Rate Topic

Display Mode

Topic Options

Author

Message

Kenneth.Fisher

Posted Wednesday, October 24, 2012 8:55 AM

Hall of Fame

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 2:29 PM
Points: 3,367, Visits: 1,575

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.

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 Fisher
I strive to live in a world where a chicken can cross the road without being questioned about its motives.
--------------------------------------------------------------------------------
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Link to my Blog Post --> www.SQLStudies.com

Post #1376531

Jason Selburg

Posted Wednesday, October 24, 2012 9:05 AM

SSCrazy

Group: General Forum Members
Last Login: Friday, June 14, 2013 1:01 PM
Points: 2,582, Visits: 3,555

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 you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Post #1376536

GSquared

Posted Wednesday, October 24, 2012 9:33 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:45 PM
Points: 15,442, Visits: 9,572

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

Post #1376553

Kenneth.Fisher

Posted Wednesday, October 24, 2012 9:52 AM

Hall of Fame

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 2:29 PM
Points: 3,367, Visits: 1,575

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 Fisher
I strive to live in a world where a chicken can cross the road without being questioned about its motives.
--------------------------------------------------------------------------------
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Link to my Blog Post --> www.SQLStudies.com

Post #1376561

GSquared

Posted Wednesday, October 24, 2012 10:01 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:45 PM
Points: 15,442, Visits: 9,572

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

Post #1376567

GilaMonster

Posted Wednesday, October 24, 2012 10:27 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:18 PM
Points: 38,062, Visits: 30,359

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 2008, MVP
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

Post #1376583

Kenneth.Fisher

Posted Wednesday, October 24, 2012 3:27 PM

Hall of Fame

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 2:29 PM
Points: 3,367, Visits: 1,575

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 Fisher
I strive to live in a world where a chicken can cross the road without being questioned about its motives.
--------------------------------------------------------------------------------
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Link to my Blog Post --> www.SQLStudies.com

Post #1376697

Michael Valentine Jones

Posted Wednesday, October 24, 2012 4:28 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 2,969, Visits: 10,615

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.

Post #1376717

Kenneth.Fisher

Posted Thursday, October 25, 2012 6:21 AM

Hall of Fame

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 2:29 PM
Points: 3,367, Visits: 1,575

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 Fisher
I strive to live in a world where a chicken can cross the road without being questioned about its motives.
--------------------------------------------------------------------------------
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Link to my Blog Post --> www.SQLStudies.com

Post #1376949

DiverKas

Posted Thursday, October 25, 2012 10:19 AM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 1:38 PM
Points: 243, Visits: 427

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.

Post #1377113

« Prev Topic | Next Topic »

Permissions