3

I am trying to understand what does this statement does

SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.)/1024 /1024

Also why is there a dot after 8192? Can anybody explain this query bit by bit. Thanks!

CuriousDev
  • 1,255
  • 1
  • 21
  • 44

2 Answers2

2

The dot . after an Integer converts it implicitly to decimal value. This is most likely here to force the output to also be decimal (not an integer). In this case you only need one part of the operation to be converted to force the output to be in that type.

This probably has to do with bytes/pages since the numbers 8192 and 1024 (most likely for converting to larger unit). One could also imply this by the value of property which indicates how much space is being used by a file.

A page fits within 8kB which means that multiplying pages value by 8192 does convert the output to bytes being used. Then division two times by 1024 succesfully converts the output to gigabytes.

Explanation on functions used:

  • FILEPROPERTY returns a value for a file name which is stored within database. If a file is not present, null value is returned
  • CAST is for casting the value to type bigint
  • SUM is an aggregate function used in a query to sum values for a specified group
Kamil Gosciminski
  • 16,547
  • 8
  • 49
  • 72
  • SQL Server does not have a `double` type. The dot is for `decimal` values which are not IEEE-754 floating numbers (so it is incorrect to refer to that type as "`double`"). – Dai Nov 16 '16 at 01:17
  • @Dai thanks for your comment. I didn't mention `double` datatype. I'm only mentioning double precision values. Isn't it correct, that a `decimal` datatype consists of precision and scale? – Kamil Gosciminski Nov 16 '16 at 01:19
  • No, the `decimal` type stores individual decimal digits (hence the name), it is a different internal representation to IEEE-754 ("`float`" or "`single`") - which means it can store exact decimal vales, whereas IEEE-754 stores approximate values: http://stackoverflow.com/questions/1209181/what-represents-a-double-in-sql-server – Dai Nov 16 '16 at 01:26
  • Thanks so much Kamil for the detailed explanation. Upvoted. – CuriousDev Nov 16 '16 at 01:50
2

FILEPROPERTY() returns an int value. Note that the SpaceUsed property is not in bytes but in "pages" - and in SQL Server a page is 8KiB, so multiplying by 8192 to get the size in KiB is appropriate.

I've never encountered a trailing dot without fractional digits before - the documentation for constants/literals in T-SQL does not give an example of this usage, but reading it implies it's a decimal:

decimal constants are represented by a string of numbers that are not enclosed in quotation marks and contain a decimal point.

Thus multiplying the bigint value by a decimal would yield a decimal value, which may be desirable if you want to preserve fractional digits when dividing by 1024 (and then 1024 again), though it's odd that those numbers are actually int literals, so the operation would just be truncation-division.

I haven't tested it, but you could try just this:

SELECT
    SUM( FILEPROPERTY( name, 'SpaceUsed' ) ) * ( 8192.0 / 10485760 ) AS TotalGigabytes
FROM
    ...

If you're reading through code and you need to do research to understand what it's doing - do a favour for the next person who reads the code by adding an explanatory comment to save them from having to do research, e.g. "gets the total number of 8KiB pages used by all databases, then converts it to gigabytes".

Dai
  • 141,631
  • 28
  • 261
  • 374