• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 350
  • Last Modified:

SQL Index Storage Requirements

I have a SQL 2008 table with 200,000,000 rows and I want to add a non-unique index to a tinyint column. Is there a formula I can use to calculate the storage requirements?

Thanks,
Dean
0
dthansen
Asked:
dthansen
  • 2
  • 2
1 Solution
 
knightEknightCommented:
If my understanding is correct (and somebody help me if it isn't), when you create a non-unique index, SQL Server adds an invisible "column" to the index to make each entry unique.  Therefore, if there is a single-column primary key on the table, and that column is of type int, you are better off adding this column to your index and making it a unique index.

  create unique index X1 on myTable( myTinyIntCol, myPKcol )
0
 
Daniel_PLDB Expert/ArchitectCommented:
>when you create a non-unique index, SQL Server adds an invisible "column" to the index to make each entry unique
Yes, indeed, SQL Server will add 4-byte value to make each entry unique

>if there is a single-column primary key on the table, and that column is of type int, you are better off adding this column to your index and making it a unique index
Primary key is already unique (it have to be if it exists). You are not able to create primary key constraint on non unique column(s).

I believe it's not the case of this question. I encourage you to post a new one ;)
0
 
knightEknightCommented:
>> Primary key is already unique (it have to be if it exists).

Yep, understood, the point is to use such a PK column as the second column in what would otherwise be a non-unique index, to make it unique.  It will not take up less disk space than a non-unique index without the PK included (because of the 4-byte value supplied by SQL Server), but it will relate the indexed column (a tinyint in this case) to the PK.  :)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now