Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Location
    Irvine
    Posts
    2

    Unanswered: problems updating bit datatype fields

    am running a stored procedure in SQL Server 2000 that contains an update statement on a table with multiple bit fields. I set up the update statement within the procedure to use the old values stored for the record for each field if the field is not explicitly specified in the parameter list to be updated as follows:

    UPDATE Individuals
    SET Password = ISNULL(@Password, Password),
    FirstName = ISNULL(@FirstName, FirstName),
    MiddleName = ISNULL(@MiddleName, MiddleName),
    LastName = ISNULL(@LastName, LastName),
    NickName = ISNULL(@NickName, NickName),
    Title = ISNULL(@Title, Title),
    Suffix = ISNULL(@Suffix, Suffix),
    AddressID = ISNULL(@AddressID, AddressID),
    Gender = ISNULL(@Gender, Gender),
    HomePhone = ISNULL(@HomePhone, HomePhone),
    WorkPhone = ISNULL(@WorkPhone, WorkPhone),
    Ext = ISNULL(@Ext, Ext),
    MobilePhone = ISNULL(@MobilePhone, MobilePhone),
    Pager = ISNULL(@Pager, Pager),
    Fax = ISNULL(@Fax, Fax),
    Email = ISNULL(@Email, Email),
    Notes = ISNULL(@Notes, Notes),
    BillingAddressID = ISNULL(@BillingAddressID, BillingAddressID),
    ContactMail = ISNULL(@ContactMail, ContactMail),
    ContactEmail = ISNULL(@ContactEmail, ContactEmail),
    ValidEmail = ISNULL(@ValidEmail, ValidEmail),
    HTMLEmail = ISNULL(@HTMLEmail, HTMLEmail)
    WHERE IndividualID = @IndividualID

    The table that the statement is updating has five bit fields: "contactmail", "contactemail", "validemail", "htmlemail", and "active". However, the field, "active", is ommitted from the update statement.

    An interesting thing happens, though, when the procedure containing this SQL is run. When explicit values are not specified for the bit fields in the statement, the bit fields are set to their old values already in the table. The one bit field that was NOT included in the statement, however, the field, "active", is always set to 0 even though it's old value was 1. This same pattern happens when at least one of the bit fields is included in the statement; all the other bit fields are set to 0.

    And another interesting thing happens when none of the bit fields are included in the update statement...all of the bit fields keep their original values. Try this out yourself and you'll see what I'm talking about.

    What is going on here?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK, I'm going from memory here, but I believe that SQL Server groups multiple bit-fields into groups of eight so they can be stored in a single byte. The end result of this is that when you update one bit field, the other seven are rewritten as well. This is why they are behaving differently than normal fields, but as to why they are being reset to specific values I'd guess that some of them have defaults defined while others do not.

    Somebody else want to look in Books Online and pick this apart?

    blindman

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Check this script - it works without any problem. I guess problem is in your procedure (defaults, etc.)

    drop table table1
    go
    create table table1(id int primary key
    ,code bit
    ,code1 bit
    ,code2 bit)
    go
    insert table1 select 13,1,0,1
    go
    create proc p_table1(@id int, @code bit=null, @code1 bit=null, @code2 bit=null)
    as
    update table1
    set code=COALESCE(@code,code)
    ,code1=COALESCE(@code1,code1)
    ,code2=COALESCE(@code2,code2)
    where id=@id
    go
    p_table1 @id=13,@code2=0 ,@code1=1
    select * from table1

  4. #4
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Originally posted by blindman
    OK, I'm going from memory here, but I believe that SQL Server groups multiple bit-fields into groups of eight so they can be stored in a single byte. The end result of this is that when you update one bit field, the other seven are rewritten as well. This is why they are behaving differently than normal fields, but as to why they are being reset to specific values I'd guess that some of them have defaults defined while others do not.
    blindman
    Interesting theory, blindman, but nothing than theory. A bit field appears as an separate field, independent on others. You should also note that a bit field can also be NULL.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Somewhere between theory and a solution.

    This is from Books Online:
    "Microsoft® SQL Server™ optimizes the storage used for bit columns. If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 through 16 bit columns, they are stored as 2 bytes, and so on."

    This is probably related to limitations on bit values, such as that most (all?) aggregate functions cannot be applied to them, as well as SIGN, ABS, and others.

    It might also relate to this problem, fixed in Service Pack 2, that sounds suspiciously like what you are seeing. Are you sure all your columns are bit, and not not int? Are all your service packs applied?

    http://support.microsoft.com/default...b;EN-US;294872
    FIX: Update on a Table with INT and BIT Columns May Zero Out INT Columns
    SYMPTOMS
    A table that was created with int columns, which appear somewhere after a bit column, may have the int columns zeroed out when other columns in the table are updated.

    blindman

  6. #6
    Join Date
    Sep 2003
    Location
    Irvine
    Posts
    2

    Re: Somewhere between theory and a solution.

    Thanks for the advice blindman. On closer examination, I don't think the latest service packs have been applied.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •