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
Andras's articles on Simple-Talk | Latest: Removing duplicates from a table

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 a software architect at Red Gate Software Ltd. He is a frequent speaker at many UK user groups and events (VBUG, NxtGen, Developer’s Group, SQLBits). He is primarily interested in database internals and database change management. At Red Gate he has designed and led the development of many database tools that compare database schemata and enable source control for databases (SQL Compare versions 4 to 7), refactor databases (SQL Refactor) and show the history of databases by analyzing the transaction log (SQL Log Rescue). András has a PhD from Cambridge and an MSc and BSc from ELTE, Hungary. He is also a MCSD and MCPD Enterprise. See his articles on simple-talk.
  Opinion Home

  Opinion Pieces (123 articles)

  Geek of the Week (46 articles)

  Tony Davis - Editor (6 articles)

  Phil Factor (46 articles)

  Damon Armstrong (8 articles)

  Douglas Reilly (40 articles)

  Adrian Furnham (1 articles)

  Claire Brooking (1 articles)

  Richard Morris (48 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 (5 articles)

  Larry Gonick (15 articles)

  Blogs Home
<November 2007>
SuMoTuWeThFrSa
28293031123
45678910
11121314151617
18192021222324
2526272829301
2345678
Niklaus Wirth: Geek of the Week
 It is difficult to begin to estimate the huge extent of the contribution that Niklaus Wirth has made to... Read more...

Building an Exchange Server 2007 environment
 Of course, changing a 32,000 mailbox system, based in 40 Exchange Servers, to a centralised 25,000... Read more...

Manage Stress Before it Kills You
 The key to a long career in IT is in learning how to cope adaptively with stress. Matt Simmons, like... Read more...

Expecting the Worst
 Optimists are often disappointed Read more...

To Boldly Ask IT for Development Work
 Phil has always been mystified by the way that, in Science-Fiction films, the crew of space-ships are... Read more...