Size table of Filetable type

Question

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

is different FROM

sp_spaceused '[dbo].[Notifiche]'

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

Accepted Answer

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"

Answer

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.

Answer

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

Share via

Size table of Filetable type

2 additional answers