• jishar (11/20/2008)

    Hi all, I need to find out the size of selected rows in a table of Sql server 2005.

    For what it's worth, the dynamic management view [sys.dm_db_index_physical_stats] returns statistics for the row, index, and lob pages related to a table object which can be filtered. Each row contains avg, min, and max record size (and a lot of other useful columns) for a page, which can then be aggregated. For a clustered table, the statistics on the clustered index will give you the record counts and stats for the table data itself. For heap (non-clustered) tables, the index_id will be 0 and will return the same. Maybe you can start from here to get what you really want.


    I'm not sure why you would need the row size only for records returned in the resultset. The size of the record in the table or index versus it's size in the network packet or it's size in the application grid, dataset, Excel sheet, etc. will be different, because it's different data structures.


    cast(db_name(ps.database_id)+'.'+object_name(ps.object_id)+'.'+isnull(i.name,'heap') as varchar(60)) as db_table_index_name,

    sum(ps.record_count) as sum_record_count,

    -- cast(((sum(ps.page_count) * 8192) / 1000000.00) as numeric(9,2)) as size_mb,

    avg(ps.max_record_size_in_bytes) as avg_record_size_in_bytes,

    max(ps.max_record_size_in_bytes) as max_record_size_in_bytes

    -- cast(avg(avg_fragmentation_in_percent) as numeric(9,1)) as avg_fragmentation_in_percent,

    -- cast(avg(ps.avg_page_space_used_in_percent) as numeric(9,1)) as avg_page_space_used_in_percent

    from sys.dm_db_index_physical_stats(db_id(), null, null, null, 'detailed') as ps

    left join sys.indexes as i on i.object_id = ps.object_id and i.index_id = ps.index_id

    where object_name(ps.object_id) in ('employee')

    group bydb_name(ps.database_id), object_name(ps.object_id), i.name

    order bydb_name(ps.database_id), object_name(ps.object_id), i.name;

    DB_Table_Index_Name sum_record_count avg_record_size_in_bytes max_record_size_in_bytes

    ------------------------------------------------------------ -------------------- ------------------------ ------------------------

    AdventureWorks.Employee.AK_Employee_LoginID 293 60 65

    AdventureWorks.Employee.AK_Employee_NationalIDNumber 292 28 29

    AdventureWorks.Employee.AK_Employee_rowguid 290 21 21

    AdventureWorks.Employee.IX_Employee_ManagerID 290 12 12

    AdventureWorks.Employee.PK_Employee_EmployeeID 301 115 219

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho