April 9, 2015 at 8:09 am
i have one col in a nonclusted index which is bigint 8 bytes
follwoing result shows min_record_size_in_bytes and max_record_size_in_bytes is 20 that is 12+8
page size is 8 kB. does that mean a record will use 20 bytes in a page or it will only use the space equalent to
data stored in the col
what i have seen is page count is same weather i put some data in the col or null
select * From sys.dm_db_index_physical_stats(9,343,null,null,'DETAILED')
database_idobject_idindex_idpartition_numberindex_type_descalloc_unit_type_descindex_depthindex_levelavg_fragmentation_in_percentfragment_countavg_fragment_size_in_pagespage_countavg_page_space_used_in_percentrecord_countghost_record_countversion_ghost_record_countmin_record_size_in_bytesmax_record_size_in_bytesavg_record_size_in_bytesforwarded_record_countcompressed_page_count
999208304101HEAPIN_ROW_DATA102.9527559055118116253.187540514.196627131208358500016516516500
9992083041141NONCLUSTERED INDEXIN_ROW_DATA202553.21699.3544600938967585000202020NULL0
9992083041141NONCLUSTERED INDEXIN_ROW_DATA2101115.510254509513221600262626NULL0
April 9, 2015 at 9:28 am
Yes, the index row length will be 20 bytes. That is normal. Since bigint is a fixed-size data type, even if the value is NULL, it will still take 8 bytes in the row.
I believe the 20 bytes is as follows:
8 for the bigint column
8 for the RID to lookup back to the parent heap
3 for the NULL bitmap (since the key col allows NULL)
1 for index row overhead
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply