asked
How does powersum behave
Could anyone with an example let me know the behaviour of powersum... I need to write an alternate query for the same since it is now not available in sql server 2008. I get some in the net, but I want to have my own I donot want the solution, but concept is indeed needed. Thanks
Powersum is an aggregate function which returns a varbinary. It basically takes a result set of integers, eg {1, 3, 5, 7} and creates a binary string. The binary string will have a "1" set for the positions given in the result set, and a "0" for the rest. So for the result set {1,3,4} the binary string will look like '11010'. This string holds five positions and should be read from right to left. The rightmost position represents 0, and since the result set didn't include 0, that bit is set to "0". The next position represents 1, and since the result set contains a 1, that bit is set to "1". Etc. Finally this result set is returned as a varbinary. In this case the binary string converts to 0x1A. Here's an example from an app where there an action table with different actions, numbered from 0 to 10, and a UserPermission table which holds the permissionID's for the actions that a specific user is granted in an application. If we want to export user-data, along with each users permission-table, we could use POWERSUM to create a varbinary which represents a permission string for the user. SELECT POWERSUM(permissionid) FROM ( SELECT permissionID from UserPermissions WHERE userID='some user id' ) as up
A note about my code: I first did "SELECT POWERSUM(permissionid) FROM UserPermissions WHERE userID='some user id'" but that resulted in "Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'permissionid'" Beats me why that code doesn't work...
You can get close to it by using POWER and CONVERT. POWERSUM is basically the sum of 2^n, converted to binary. The following code returns the same in both columns: DECLARE @t TABLE (i [int] ) INSERT INTO @t ([i]) VALUES (1) INSERT INTO @t ([i]) VALUES (3) INSERT INTO @t ([i]) VALUES (5) INSERT INTO @t ([i]) VALUES (7) SELECT powersum(i), CONVERT([varbinary], SUM(POWER(2, i))) FROM @t However, POWERSUM is more efficient, and can take a wider range of input - but you'll have to experiment with it's limits.
