sp_spaceused 和 DataLength SQL Server 之间的区别 [英] Difference between sp_spaceused and DataLength SQL Server

问题描述

当我使用 SP_SpaceUsed N'' 时，我有一个单行表它给了我 16 KB 的数据

当我使用 dataLength 时:-

<前>选择 ClientID ,(0 + isnull(datalength(ClientID), 1) +isnull(数据长度(LeadID), 1) +isnull(datalength(Company_Name), 1) +isnull(数据长度(网站), 1) +isnull(datalength(EmployeeCount), 1) +isnull(数据长度(收入), 1) +isnull(数据长度(地址), 1) +isnull(数据长度(城市), 1) +isnull(数据长度(状态), 1) +isnull(数据长度(邮编), 1) +isnull(datalength(CountryID), 1) +isnull(数据长度(电话), 1) +isnull(数据长度(传真), 1) +isnull(datalength(TimeZone), 1) +isnull(datalength(SicNo), 1) +isnull(datalength(SicDesc), 1) +isnull(数据长度(研究分析), 1) +isnull(datalength(SourceID), 1) +isnull(datalength(BasketID), 1) +isnull(datalength(PipelineStatusID), 1) +isnull(datalength(SurveryID), 1) +isnull(datalength(NextCallDt), 1) +isnull(datalength(CurrentRecStatus), 1) +isnull(datalength(AssignedUserID), 1) +isnull(datalength(AssignedDate), 1) +isnull(数据长度(TotValueAmt), 1) +isnull(数据长度(删除), 1) +isnull(datalength(Release), 1) +isnull(datalength(LegendID), 1) +isnull(datalength(Inserted_Date), 1) +isnull(datalength(Inserted_By), 1) +isnull(datalength(Updated_Date), 1) +isnull(datalength(Updated_By), 1))作为来自 TempLeadHeader 的 rowsize 按 rowsize desc 排序

它给 rowsize 167 我猜这是以字节为单位

我想知道为什么结果中会出现这种差异

提前致谢

解决方案

sp_spaceused 统计页面使用的空间，为 8k 块.请记住，表还包括索引之类的内容，这些内容也占用空间.更何况页面上的数据永远不会被填满，除非填充因子是 100%

datalength 会告诉你你的列有多少字节

I have a table with single Row when i use SP_SpaceUsed N'<TableName>' it gives me data as 16 KB

and when I use dataLength something like this:-

select ClientID , 
(0 + isnull(datalength(ClientID), 1) + 
isnull(datalength(LeadID), 1) + 
isnull(datalength(Company_Name), 1) + 
isnull(datalength(Website), 1) + 
isnull(datalength(EmployeeCount), 1) + 
isnull(datalength(Revenue), 1) +
 isnull(datalength(Address), 1) + 
isnull(datalength(City), 1) + 
isnull(datalength(State), 1) + 
isnull(datalength(ZipCode), 1) + 
isnull(datalength(CountryID), 1) + 
isnull(datalength(Phone), 1) + 
isnull(datalength(Fax), 1) + 
isnull(datalength(TimeZone), 1) + 
isnull(datalength(SicNo), 1) + 
isnull(datalength(SicDesc), 1) +
 isnull(datalength(ResearchAnalysis), 1) + 
isnull(datalength(SourceID), 1) + 
isnull(datalength(BasketID), 1) + 
isnull(datalength(PipelineStatusID), 1) + 
isnull(datalength(SurveryID), 1) + 
isnull(datalength(NextCallDt), 1) + 
isnull(datalength(CurrentRecStatus), 1) +
 isnull(datalength(AssignedUserID), 1) + 
isnull(datalength(AssignedDate), 1) + 
isnull(datalength(TotValueAmt), 1) + 
isnull(datalength(Remove), 1) + 
isnull(datalength(Release), 1) + 
isnull(datalength(LegendID), 1) + 
isnull(datalength(Inserted_Date), 1) +
 isnull(datalength(Inserted_By), 1) + 
isnull(datalength(Updated_Date), 1) + 
isnull(datalength(Updated_By), 1)) 
as rowsize from TempLeadHeader order by rowsize desc

it gives rowsize 167 i guess this is in bytes

I would like to know why this difference is coming up in the result

Thanks in advance

解决方案

sp_spaceused counts the space used by pages, which are 8k blocks. Remember that a table also includes things like indexes that take up space too. not to mention that data on pages are never full unless the fill factor is 100%

datalength will tell you how many bytes your column is

这篇关于sp_spaceused 和 DataLength SQL Server 之间的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！