Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Very Small Tables Expand / Collapse
Author
Message
Posted Wednesday, October 24, 2012 8:55 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 11:01 AM
Points: 3,461, Visits: 1,785
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
Posted Wednesday, October 24, 2012 9:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:04 PM
Points: 2,694, Visits: 3,755
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
Posted Wednesday, October 24, 2012 9:33 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Posted Wednesday, October 24, 2012 9:52 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 11:01 AM
Points: 3,461, Visits: 1,785
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
Posted Wednesday, October 24, 2012 10:01 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Posted Wednesday, October 24, 2012 10:27 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 42,445, Visits: 35,501
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
Posted Wednesday, October 24, 2012 3:27 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 11:01 AM
Points: 3,461, Visits: 1,785
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
Posted Wednesday, October 24, 2012 4:28 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:41 AM
Points: 3,122, Visits: 11,399
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
Posted Thursday, October 25, 2012 6:21 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 11:01 AM
Points: 3,461, Visits: 1,785
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
Posted Thursday, October 25, 2012 10:19 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:28 AM
Points: 249, Visits: 460
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 »

Add to briefcase

Permissions Expand / Collapse