Kindly explain the case query.

Question

Kindly explain the case query.

KMPSSS

Ten Centuries

Points: 1052
More actions
February 20, 2011 at 10:24 pm

#388902

Hi,
Actually i got this query from net. Below one is query.
select sum(convert(bigint,case when status & 64 = 0 then size else 0 end))
, sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
from dbo.sysfiles
I dont know, why they used & between status and 64. What is the meaning for this. Whether its adding value or not. Can you please explain through sample code

Viewing 6 posts - 1 through 5 (of 5 total)

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

dogramone Hall of Fame Points: 3216 More actions · Answer 1

It looks to me like they are using bitwise processing, the ability to store multiple values in a single field.

Have a look at this article http://www.databasejournal.com/features/mssql/article.php/3359321/Storing-Multiple-Statuses-Using-an-Integer-Column.htm

diamondgm SSCrazy Points: 2824 More actions · Answer 2

It does indeed seem like bitwise operation; I believe & is the binary AND operation.

Though it also seems that the logic is flawed as the same result would occur whether or not the AND operation resulted in 1 or 0 (true or false).

Jeff Moden SSC Guru Points: 1004190 More actions · Answer 3

It's actually not the same result. The answer would appear in one or the other column depending on the bit map. Now, WHY they needed it that way, I could only guess. 😀

--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)

diamondgm SSCrazy Points: 2824 More actions · Answer 4

diamondgm

SSCrazy

Points: 2824

February 27, 2011 at 8:28 pm

#1292125

My bad, I missed the = vs <>

john.arnott SSChampion Points: 11882 More actions · Answer 5

KMPSSS (2/20/2011)
Hi,
Actually i got this query from net. Below one is query.
select sum(convert(bigint,case when status & 64 = 0 then size else 0 end))
, sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
from dbo.sysfiles
I dont know, why they used & between status and 64. What is the meaning for this. Whether its adding value or not. Can you please explain through sample code

The ampersand (&) is indeed a bitwise AND. This allows the "status" column to be seen as a set of bit switches. The state of each bit may be queried by using the bitwise AND. The bit being examined in the example would be the seventh (1,2,4,8,16,32, 64...) in "status".

We can find that the "64" bit in sysfiles.status indicates whether the growth factor for a log file is in MB or KB (see http://msdn.microsoft.com/en-us/library/ms178009.aspx for details on this deprecated ~~table~~ view). The query may have been more easily understood with a local variable for the constant 64 and with column name aliases applied. Note also that I'd prefer to emphasize the binary nature of the bitwise AND by comparing to the constant as in the second sum shown here rather than to zero.

Declare @LogGrowthUnitInd Int

Set @LogGrowthUnitInd = 64

select sum(convert(bigint,case when status & @LogGrowthUnitInd = 0 then size else 0 end))

as TotalSizeFilesGrowInMb

, sum(convert(bigint,case when status & @LogGrowthUnitInd = @LogGrowthUnitInd then size else 0 end))

as TotalSizeFilesGrowInKb

from dbo.sysfiles

edit: "view", not "table....