You might know how many lines of data you have in your table but do you actually know how much disk space the table is taking up? This was my issue this past weekend. I needed to know the aproximate size of a table in my production SQL database. I thought about it for a little while and then I emailed my database administrator and he gave me a command that made my life a little easier.
It turns out SQL has a stored procedure that will do exactly what I needed it to do. It will tell me the size of a table and its indexes. The stored procedure is pretty easy to use and invoke. Check it out:
Just enter the above query into your SQL Query Analyzer and replace the ‘Tablename’ with your actual table’s name. You can see it in action against our Northwind database below:
You can see that 540 kb of space has been allocated for the table. There is 160 kb of data in the table and the indexes are the biggest space consumer using 320 kb of data. It was very interesting to find out (even in my production database) that the indexes use more space than the actual data.