Post reply

What does the NULL file type mean in this query?

  • March 22, 2020 at 7:31 pm

    #3736111

    --> Screenshot of the result-set is attached. Thanks.

    SELECT [Database Name] = DB_NAME(database_id),

    [Type] = CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'

    WHEN Type_Desc = 'LOG' THEN 'Log File(s)'

    ELSE Type_Desc END,

    [Size in MB] = CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) )

    FROM sys.master_files

    -- Uncomment if you need to query for a particular database

    -- WHERE database_id = DB_ID(‘Database Name’)

    GROUP BY GROUPING SETS

    (

    (DB_NAME(database_id), Type_Desc),

    (DB_NAME(database_id))

    )

     

     

    ORDER BY DB_NAME(database_id), Type_Desc DESC

    GO

    Attachments:
    You must be logged in to view attached files.

    Likes to play Chess

  • March 22, 2020 at 8:09 pm

    #3736113

    Like this?

    SELECT dbname
     , [type]
     , CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) ) AS SizeMB
    FROM
    (SELECT DB_NAME(database_id) as dbname,
      [Type] = CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'
          WHEN Type_Desc = 'LOG' THEN 'Log File(s)'
          WHEN Type_Desc = 'FILESTREAM' THEN 'FileStream'
    ELSE Type_Desc END,
     Size
    FROM sys.master_files) d
    GROUP BY dbName, [Type]

    GROUP BY dbName, [Type]

  • March 22, 2020 at 8:43 pm

    #3736116

    Post removed... I misread the code.  See Jacob's post below.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 23, 2020 at 3:12 pm

    #3736276

    Those NULLs are the NULLs from GROUPING SETS (you can use the GROUPING function to distinguish them from other NULLs).

    You've specified that you want aggregates returned for two different groups: database name and file type, and just database name.

    The file type column has to be included because of the first group, but for the rows that show the aggregate result for the database name (across all file types), it returns a NULL for file type, because the result is not for any specific file type.

    Cheers!

    • This reply was modified 4 years, 9 months ago by  Jacob Wilkins.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply