Post reply

Partitioning and bitwise operators

  • November 1, 2005 at 2:52 am

    #111318

    Hi Guys,

    I've a questions concerning the use of bitwise operators and horizontal partitioning in sql 2000.

    Basically we have the partitioning setup and working properly, this has been tested and validated but when we come to introduce some bitwise operators into the where clause it suddenly decides to ignore the constraints and check each of the underlying tables.

    So, I guess i'm asking if anyone out there has ever used bitwise operators in the same area as horizontal partitioning and whether they've come across and resolved this isssue?  I know the usual responses will be can we have the DDL etc and that wont be a problem if needed but right now i'm more interested in whether anyone else has come across it before.

    Mike

  • November 2, 2005 at 3:46 am

    #601782

    I can only assume because bitwise operators are non SARGable - effectively SQL cannot use an index when you are working with such operations.  Thus SQL cannot, based on the conditions using the bitwise operators, make a decision about which table would contain the data.  Can you get rid of your bitwise operations (which probably means you have more than one data element being stored in a single int style column) and have several "bit" columns instead?

  • November 2, 2005 at 3:54 am

    #601785

    Thanks Ian, that makes sense

  • November 2, 2005 at 9:52 am

    #601909

    I can add a few comments about indexing bit columns ( yes honestly !! ) One of the many features I've encountered are bit flags to say if a row is current / enabled / good / bad etc. so that every query includes a and where bitfield = 1 or bitfield = 0 .  I resolved my problems by adding the bit column as a second column to my row id ( which in this case is the clustered index and PK ) certainly improved performance no end. You can't add bits to indexes in the gui has to be done in T-SQL.

    Don't know if this helps at all  - probably not - but I've used bits in indexes for selections very effectively.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • November 2, 2005 at 10:16 am

    #601920

    Thanks for that Colin, i'll have a look and see if it's applicable to my problem

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

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