how to find the Space consumed by a each column in sql server 2005?

Question

Post reply

how to find the Space consumed by a each column in sql server 2005?

pkposani

SSC-Addicted

Points: 426
More actions
December 21, 2010 at 10:07 am

#230996

Hi,
I'm trying to find the space consumed by a single column in a table.I'm able to find the total size of the table by using sp_spaceused but to further investigate I need to know how much space is used by each column?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

SELECT SUM(DATELENGTH(ColName)) As Bytes FROM dbo.table

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

That will take a bit of extrapolation. If you know the data types, and by knowing the storage requirements for the data types you can begin calculating the space usage of your columns.

Here is an article to help with storage requirements of data types:

http://www.databasejournal.com/features/mssql/article.php/3718066/Disk-Space-Usage-and-SQL-Server-Performance.htm

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

pkposani SSC-Addicted Points: 426 More actions · Answer 3

SELECT SUM(DATALENGTH(colname)) As Bytes FROM db.table...will this work for ntext datatype?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

pkposani (12/21/2010)
SELECT SUM(DATALENGTH(colname)) As Bytes FROM db.table...will this work for ntext datatype?

What version of SQL Server are you running?

NText was Deprecated in SQL 2005

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

pkposani SSC-Addicted Points: 426 More actions · Answer 5

I'm restoring a SQL 2000 DB to a 2005 enterprise edition and running in 2000 compatible mode..

Thanks,

Pavan Posani