Size table of Filetable type

Alen Cappelletti 971 Reputation points
2022-10-21T09:54:15.437+00:00

Hi,
I have doubts to exactly count the weight of a system FileTable (new FileStream type I mean).
The SQL below return me 200 Mb for "dbo.Notifiche".

Using sp_spaceused '[dbo].[Notifiche]' I got another weight.

I always used the DATALENGTH function with a SUM of each field ina row in past.

declare @table nvarchar(128)  
declare @sql nvarchar(max)  
set @sql = ''  
DECLARE tableCursor CURSOR FOR    
SELECT name from sys.tables  
  
open tableCursor  
fetch next from tableCursor into @table  
  
CREATE TABLE #TempTable( Tablename nvarchar(max), Bytes int, RowCnt int)  
  
WHILE @@FETCH_STATUS = 0    
begin  
    set @sql = 'insert into #TempTable (Tablename, Bytes, RowCnt) '  
    set @sql = @sql + 'select '''+@table+''' "Table", sum(t.rowsize) "Bytes", count(*) "RowCnt" from (select (0'  
  
    select @sql = @sql + ' + isnull(datalength([' + name + ']), 1) '   
        from sys.columns where object_id = object_id(@table)  
    set @sql = @sql + ') as rowsize from ' + @table + ' ) t '  
    exec (@sql)  
    FETCH NEXT FROM tableCursor INTO @table    
end  
  
--PRINT @sql  
  
CLOSE tableCursor     
DEALLOCATE tableCursor  
  
select Tablename, Bytes, CAST(Bytes / 1048576. as decimal(13,2)) as [Mib] FROM #TempTable  
select sum(bytes) "Sum" from #TempTable  
  
DROP TABLE #TempTable  

253001-image.png

is different FROM

sp_spaceused '[dbo].[Notifiche]'  

252906-image.png

Basically, to the question how much do 1000 rows on disk weigh on average for that type of table? How is it best to proceed?

SELECT SUM(  
       ISNULL(DATALENGTH([stream_id]), 0)  
      +ISNULL(DATALENGTH([file_stream]), 0)  
  +ISNULL(DATALENGTH([file_stream]), 0)  
      +ISNULL(DATALENGTH([name]), 0)  
      +ISNULL(DATALENGTH([path_locator]), 0)  
      +ISNULL(DATALENGTH([parent_path_locator]), 0)  
      +ISNULL(DATALENGTH([file_type]), 0)  
      +ISNULL(DATALENGTH([cached_file_size]), 0)  
      +ISNULL(DATALENGTH([creation_time]), 0)  
      +ISNULL(DATALENGTH([last_write_time]), 0)  
      +ISNULL(DATALENGTH([last_access_time]), 0)  
      +ISNULL(DATALENGTH([is_directory]), 0)  
      +ISNULL(DATALENGTH([is_offline]), 0)  
      +ISNULL(DATALENGTH([is_hidden]), 0)  
      +ISNULL(DATALENGTH([is_readonly]), 0)  
      +ISNULL(DATALENGTH([is_archive]), 0)  
      +ISNULL(DATALENGTH([is_system]), 0)  
      +ISNULL(DATALENGTH([is_temporary]), 0)  
  ) as [sum bytes]  
FROM [dbo].[Notifiche]  

Thanks ALEN

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,122 questions
0 comments No comments
{count} votes

Accepted answer
  1. PandaPan-MSFT 1,906 Reputation points
    2022-10-24T05:35:41.853+00:00

    Hi @Alen Cappelletti ,
    The reason why the two results are different is that sp_spaceused counts the space used by pages, which are 8k blocks. And a table also includes things like indexes that take up space too. Besides the data on pages are never full unless the fill factor is 100%. I quote the answer in this link : https://stackoverflow.com/questions/768320/difference-between-sp-spaceused-and-datalength-sql-server
    Normally , the size of each row on average is 1KB.
    Now that the result got from sp_spaced is connected with other elements, so the choice that

    I always used the DATALENGTH function with a SUM of each field ina row in past.

    You are right!


    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 104.2K Reputation points MVP
    2022-10-21T21:52:46.93+00:00

    I would put my bets on the query with datalength here. I would guess sp_spaceused on looks that the relational data, that is the data that is stored in normal table format and therefore misses the data in the files.

    1 person found this answer helpful.
    0 comments No comments

  2. Olaf Helper 42,576 Reputation points
    2022-10-24T05:52:24.467+00:00

    I always used the DATALENGTH function with a SUM of each field ina row in past.

    That's not always correct, for example data type bit: "If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on."
    See additional: Estimate the Size of a Table

    1 person found this answer helpful.
    0 comments No comments