sql server 2005 express space needed to create index formula

What is the formula to figure out how much space an index will need to be created ?

IMPORTANT: We are using sql server express 2005. So 4GB is the limit. Hence this question. Unrestricted Autogrow of log files is not an option. t-sql psuedo code below will explain further.

int spaceNeeded = 
  FN_COMPUTE_SPACE_NEEDED_TO_CREATE_UNCLUSTERED_INDEX(MyTableName, MyColumnName)

I开发者_Python百科t's the one function above that I am stuck at while coding this stored proc I think I can handle all the below with info available on msdn etc.

While(GetDBSpaceAvailable() < spaceNeeded )
{
    purge some data (go thru all tables and delete data logically)
}

create the index we need
CREATE NONCLUSTERED INDEX [IX_NonClustered_MyTable] ON [dbo].[MyTable]
(   [MyColumn] ASC
)

Any pointers appreciated in this regards. Hoping this is a repeat but could not find anything when I searched. thank you

UPDATE: Solution that worked for me uses Aaron's suggestion. (additional 1.2X over and above the space the index occupies now). So basically I purge oldest data until space available is 1.2 to 1.5x over and above the current index size reported by sp_spaceused tablename. Then create the index.

---

The size limit is only per database. Have you considered putting some of your junk, static or at least less volatile data into a separate database? You can create synonyms to make it so that the code doesn't realize some of the data is in its own 4GB space. All on one instance you'd be hard-pressed to notice any performance difference by splitting it up, the biggest problem would be backup consistency as you'd be backing up the data separately. But it would be one way to avoid your index space requirements.

Also, instead of using this old version of SQL Server 2005 Express, why not move to the most recent released version (SQL Server 2008 R2), where you have 6 more GB to play with (database size is limited to 10 GB instead of 4 GB). In addition you get some new features, T-SQL syntax and performance benefits.

---

I don't think it's possible to know for certain.

It's not just the index that is created, it also creates a b-tree used to parse the index leaf nodes, as well as supporting statistics.

Both those factors are affected by the distribution of your data values, selectivity, etc.

There are additional options that affect space used like fill factor, included fields, etc.

Also it would be a real challenge regardless of the factors above to calculate this for varchar or nvarchar fields, since the length (and therefore space used) varies row to row.

It makes more sense just to get a SQL Server License instead of putting in the hundreds of man-hours it would take to make this accurate and deal with recoding when it inevitably had an issue.