Search code examples
sql-serverstar-schema

Most efficient way to set a default value in the fact table of a star schema in SQL Server

I've built a star schema with a number of dimensions, but I'd like to set a default value of 0 for the fact table if no matching value is found for that dimension. Here's my current solution:

Left join and case statements

SELECT 
    CASE WHEN d1.ID is NULL THEN 0 ELSE d1.ID END,
    CASE WHEN d2.ID is NULL THEN 0 ELSE d2.ID END
FROM OriginalTable OT
LEFT JOIN Dim1 d1
    ON OT.field1 = d1.field
LEFT JOIN Dim1 d2
    ON OT.field2 = d2.field

Is there a more efficient way?

Solution

  • In SQL server you can use both IsNULL and coalesce, coalesce returns the first value that isn't null so 

    SELECT 
coalesce(d1.ID,0),
coalesce(d2.ID,0)
FROM OriginalTable OT
LEFT JOIN Dim1 d1
   ON OT.field1 = d1.field
LEFT JOIN Dim1 d2
   ON OT.field2 = d2.field

    should do the trick