estimating the Index size for the database

[BikeBoy20032004]

Hi,

is there an easy way to estimate the size of the indexes for a database?

there's this microsoft site:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_92k3.asp

but it mentions the index levels, ect. I don't know the levels, just need to estimate the sizes.
I know the aprox size for the data, # of rows, etc.

Thanks

 

[mrdenny]

Average size of the data in the column, times the number of rows in the table.

If your average length of the data is 17 char (and your data type is varchar) and you have 1,000,000 rows your index will be ~17,000,000 bytes or 16.2 MB. If your data type is nvarchar than multiply by 2. So the same field would be 34,000,000 bytes or 32.4 MB.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[BikeBoy20032004]

Denny, I suppose by "average length of the data is 17 char" you mean only columns included in the index, correct?

In other words non-clusterd index is as big as the data?

Thanks

 

[mrdenny]

Yeah, pretty much.

Is the column numberic or text?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[BikeBoy20032004]

no, just regular data types.