February 4, 2015 at 8:56 am
Hi Experts,
I have a table like below,
CREATE TABLE Student
(
Id BIGINT not null
,Name NCHAR(20) not Null
,Branch NVARCHAR (64) null
)
The table contains : 100000 rows .
Can you please help me in getting details of below
1)Number of rows in a data page
2)Total number of pages required for the table
3)Total Table size in KB or MB
4)Total file size in Kb or MB
Thanks!
February 4, 2015 at 9:17 am
Jampandu (2/4/2015)
Hi Experts,I have a table like below,
CREATE TABLE Student
(
Id BIGINT not null
,Name NCHAR(20) not Null
,Branch NVARCHAR (64) null
)
The table contains : 100000 rows .
Can you please help me in getting details of below
1)Number of rows in a data page
2)Total number of pages required for the table
3)Total Table size in KB or MB
4)Total file size in Kb or MB
Thanks!
Number of rows will be around 80 per page given the details above. To get the remaining use this
SELECTs.nameAS SchemaName
, OBJECT_NAME(o.OBJECT_ID)AS TableName
, ISNULL(i.name, 'HEAP')AS IndexName
, i.index_idAS IndexID
, CASE i.[type]
WHEN 0 THEN 'HEAP'
WHEN 1 THEN 'Clustered'
WHEN 2 THEN 'NonClustered'
WHEN 3 THEN 'XML'
WHEN 4 THEN 'Spatial'
ENDAS IndexType
, i.is_disabledAS IsDisabled
, i.data_space_id
, CASE
WHEN i.data_space_id > 65600 THEN ps.name
ELSE f.name
ENDAS FG_or_PartitionName
, p.partition_numberAS PartitionNo
, p.[rows]AS [RowCnt]
, p.data_compression_descAS CompressionType
, au.type_descAS AllocType
, au.total_pagesAS TotalPages
, au.total_pages / 128AS TotalMBs
, au.used_pages/ 128AS UsedMBs
, au.data_pages/ 128AS DataMBs
FROM sys.indexes i
LEFT OUTER JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
LEFT OUTER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN sys.allocation_units au ON
CASE
WHEN au.[type] IN (1,3) THEN p.hobt_id
WHEN au.[type] = 2 THEN p.partition_id
END = au.container_id
WHERE o.is_ms_shipped <> 1
ORDER BY OBJECT_NAME(o.OBJECT_ID)
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 4, 2015 at 9:22 am
Can you please give me the just raw calculations on the size of the page (file) with out using queries.
Want to understand it from general calculations instead of sql queries..
February 4, 2015 at 9:56 am
a page has 8096 bytes of usable space
you have an overhead if fully populated of around 98 bytes per row.
It's not exact as each row may not be fully populated
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 4, 2015 at 10:00 am
The row length excluding the Branch column is ~64 bytes:
8 for bigint
40 for nchar(20)
4 for var length ptr area
1 for bitmap
11 for row overhead
A page is ~8060 bytes.
If the branch column averages 18 chars, the total row length would be ~100 bytes, meaning ~80 rows per page.
If the branch column averages 32 chars (half its max), the total row length would be ~128, or ~62 per page.
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply