The following image is a part of Microsoft SQL Server 2008 R2 System Views. From the image we can see that the relationship between sys.partitions
and sys.allocation_units
depends on the value of sys.allocation_units.type
. So to join them together I would write something similar to this:
SELECT *
FROM sys.indexes i
JOIN sys.partitions p
ON i.index_id = p.index_id
JOIN sys.allocation_units a
ON CASE
WHEN a.type IN (1, 3)
THEN a.container_id = p.hobt_id
WHEN a.type IN (2)
THEN a.container_id = p.partition_id
END
But the upper code gives a syntax error. I guess that's because of the CASE
statement.
Can anyone help to explain a little?
Add error message:
Msg 102, Level 15, State 1, Line 6 Incorrect syntax near '='.
A CASE
expression returns a value from the THEN
portion of the clause. You could use it thusly:
SELECT *
FROM sys.indexes i
JOIN sys.partitions p
ON i.index_id = p.index_id
JOIN sys.allocation_units a
ON CASE
WHEN a.type IN (1, 3) AND a.container_id = p.hobt_id THEN 1
WHEN a.type IN (2) AND a.container_id = p.partition_id THEN 1
ELSE 0
END = 1
Note that you need to do something with the returned value, e.g. compare it to 1. Your statement attempted to return the value of an assignment or test for equality, neither of which make sense in the context of a CASE
/THEN
clause. (If BOOLEAN
was a datatype then the test for equality would make sense.)