A service from Red Gate

in
Home SQL .NET Exchange Opinion Blogs Forums About
Sign in | Join | Help
Home Blogs Forums
Blogs Home > Andras > SQL Server 2008: Microsoft has given, and Microsoft has taken away (powersum)

Andras

Software Architect - Red Gate Software

SQL Server 2008: Microsoft has given, and Microsoft has taken away (powersum)

Published Thursday, November 22, 2007 12:14 PM

SQL Server 2008 November CTP came out this week. This CTP finally contains many major features that are worth playing with. However, there are things that are no longer in SQL Server, things that I will be missing (and many things that I’ll be happy to see go).

 

One of my favourite system functions is powersum. This is not documented, so Microsoft cannot be blamed at all for removing it (Well, not really removing it, but just denying public access to it. It is very useful, so Microsoft keep using it internally). However, I liked using this on 2005 to create bit arrays from numbers. Powersum is an aggregate. It takes numbers, and it returns a varbinary. It takes its input number, and SHIFTs 1 to the left by this number, then ORs this with the aggregated value.


For example the following query returns 0x16:


SELECT  powersum(col)

FROM    ( SELECT    1

          UNION ALL

          SELECT    2

          UNION ALL

          SELECT    4

        ) AS data ( col )

 

0x16’s binary representation is 10110 (so the bits at position 1, 2 and 4 are set).


I’m really sorry to see this function go. I used it when returning column permissions in SQL Compare. It allowed me to group column permissions together, consequently my queries were sending less data. It also allowed building query results in 2005 that were similar to SQL Server 2000’s syspermissions table.

 

However, the access restriction to this useful function reminds me that while there are new features in SQL Server 2008, some obsolete features and syntax will go away, just like it has happened with SQL Server 2005. I’ve seen many people finally migrating to the “new” join syntax, and abandoning the =* syntax in 2005. Since SQL Server 2008 does not support compatibility level 70 and before, this syntax will finally die out.

 

2008 also seems to remove the DUMP and LOAD keywords. People should use BACKUP and RESTORE instead, and it is a good time to look at all those old maintenance jobs.

 

Books online mentions a few other changes that we should expect in SQL Server 2008. (The page is Deprecated Database Engine Features in SQL Server 2005). However this list is likely to change. For example this page claims that SETUSER will be removed from 2008, but it still seems to work (let’s not forget though that SQL Server 2008 is not yet released). It is worth however to keep a close eye on this page as well as the "Discontinued Database Engine Functionality in SQL Server 2008" page in 2008’s Books online.

by András

Comments

 

Windows Vista News said:

Did you see the post at www.simple-talk.com
November 22, 2007 6:45 AM
 

Adam Machanic said:

How about doing something like this as a replacement for the powersum:

declare @e varbinary
set @e = 0x0

SELECT @e = @e | power(2, col)
FROM    ( SELECT    1
         UNION ALL
         SELECT    2
         UNION ALL
         SELECT    4
       ) AS data ( col )

SELECT @e

November 26, 2007 10:02 AM
 

Adam Machanic said:

Well, I just played around with powersum a bit and discovered that it handles values greater than 64 bits, so my proposed solution posted here is not equivalent.  So I'll take this opportunity to plug my blog series on dealing with large bitmasks, which I wrote a few years ago -- while a bit more clunky than powersum (which I'd never heard of before this post), you might be able to modify the code to produce a workable solution similar to what you're used to:

http://sqlblog.com/blogs/adam_machanic/archive/tags/bitmasks/default.aspx

November 26, 2007 10:08 AM
 

András said:

Hi Adam,

many thanks for your comments. Unfortunately the query I needed to rewrite was rather complex and it was already using a couple of nice tricks.
Using variables is an alternative people could use, but only when they need the result for the whole query. When one uses group by, one is basically forced to turn this into a user defined function, which is sometimes not an option (like when writing a third party tool :)). But luckily I could push things to the client side :)
Best,
 Andras
November 28, 2007 4:29 AM
 

chris.leonard said:

I also hadn't heard of this function before today, and am a little perplexed by it.  I must be missing something, because I don't understand the explanation of what POWERSUM is supposed to do.  Consider this query:

SELECT powersum(id)
FROM (
  select 4
  union all
  select 5
  union all
  select 7
  union all
  select 8
  ) as t(id)

I would expect this to return a number with bits 4, 5, 7, and 8 set, like this:

  110110000 binary = 0x1B0

However, it returns 0xB001, which is 1011000000000001 in binary.  This number has bits 0, 12, 13, and 15 set.  What am I misunderstanding?

Thanks,
Chris
November 29, 2007 1:21 AM
 

Lionel said:

It is a bit confusing as the results are backwards to what you would expect!. So in your example you were expecting 0x1B0 which I will write 0x01B0 now this contains two bytes 01 and B0 so when we reverse the data we reverse the order of the bytes and get 0xB001. It is not only confusing because it is in the reverse order to what you expect but also since each byte is represented by two letters the reverse looks a little funky when you look at the hex string! try doing

SELECT CONVERT(VARBINARY,REVERSE(0x1B0))

It might make it a little more clear
November 29, 2007 2:12 AM
 

Phil Factor said:

Yes, when I tried out my solution, i'd carefully done it the conventional 'bitmap' way. I know that Lionel had hinted that it was all 'Topsy Turvy' but even with this warning,  it threw me until I noticed that my solution was a mirror image.

Incidentally, bitmaps can be extraordinarily useful to solve ordinary commercial problems such as booking adjacent seats, or anything where sequences and patterns are important. (as well as the obvious use with graphics). It is a shame that SQL Server's support for them is so bad. I suspect that most of us cheat and use strings (with RegEx).
November 29, 2007 2:56 AM
 

Matt Whitfield said:

It looks to me like powersum is returning the data straight from memory, and because x86 is little-endian, you see the 0xB001

This is how bytes are stored in memory at the processor level
November 29, 2007 4:13 AM
You need to sign in to comment on this blog

About András

András Belokosztolszki is the architect of SQL Compare versions 4, 5, 6 and 7, SQL Log Rescue and SQL Refactor. He is focused on database internals, database synchronization and database schema evolution.
  Opinion Home

  Opinion Pieces (96 articles)

  Geek of the Week (37 articles)

  Tony Davis - Editor (6 articles)

  Phil Factor (41 articles)

  Damon Armstrong (8 articles)

  Douglas Reilly (40 articles)

  Adrian Furnham (1 articles)

  Claire Brooking

  Richard Morris (34 articles)

  Tim Gorman (1 articles)

  Jesse Liberty (5 articles)

  Sarah Blow (1 articles)

  Anna Larjomaa (1 articles)

  Gaidar Magdanurov (2 articles)

  Regular Columnists (1 articles)

  Ben Hall (2 articles)

  Blogs Home
<November 2007>
SuMoTuWeThFrSa
28293031123
45678910
11121314151617
18192021222324
2526272829301
2345678
Virtual Exchange Servers
 Microsoft now supports running Exchange Server 2007 in server virtualization environments, not just on... Read more...

Virtualizing Exchange: points for discussion
 With the increasing acceptance of the use of Virtualization as a means of providing server... Read more...

Encouraging .NET Reflector Add-ins
 Jason Haley is well-known for the resources he's provided to developers who wish to extend Reflector's... Read more...

Using .NET Reflector Add-ins
 .NET Reflector by itself is great, but it really comes into its own with the help of some add-ins. Here... Read more...

Unique Experiences!
 You'd have thought that a unique constraint was an easy concept - Not a bit of it; it can cause a lot... Read more...