Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2003
    Posts
    268

    Unanswered: Small Question about BIT columns

    1-8 bit columns in a table consume one byte of storage space. That sounds simple and efficient.

    If a bit could only store two values, this would make sense. But, a bit can be one of three values: NULL, 0, 1.

    How does a NULL-able bit column only consume the storage space of a regular 0/1 bit?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That's easy, it doesn't. One byte for NULL indicator, one bit for the value.

    -PatP

  3. #3
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by Pat Phelan
    That's easy, it doesn't. One byte for NULL indicator, one bit for the value.
    That make sense. So the Microsoft SQL Server 2000 Admin Companion is just wrong (doesn't surprise me).

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by RogerWilco
    That make sense. So the Microsoft SQL Server 2000 Admin Companion is just wrong (doesn't surprise me).
    I'm not sure about that, I rarely look at the companion.

    When I need to know, I go to the font of all SQL knowledge. Drink deep, it is very good!

    -PatP

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    http://www.amazon.com/exec/obidos/tg...books&n=507846

    New & Used!

    Pat, Explain this then? Takes a few minutes

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    GO
    
    CREATE TABLE myTable99(Col1 int IDENTITY, Col2 char(1), Col3 bit)
    CREATE TABLE mySpace99(
    	  Col1 int IDENTITY(1,1)
    	, [name] sysname
    	, [rows] int
    	, reserved varchar(20)
    	, Data varchar(20)
    	, index_size varchar(20)
    	, unused varchar(20))
    GO
    
    INSERT INTO mySpace99([name], [rows], reserved, Data, index_size, unused) 
    EXEC sp_spaceused myTable99
    GO
    
    DECLARE @x int
    
    SELECT @x = 0
    
    WHILE @x < 10000
      BEGIN
    	INSERT INTO myTable99(Col2) SELECT 'x'
    	SELECT @x = @x + 1
      END 
    
    SELECT COUNT(*) FROM myTable99
    
    EXEC sp_updatestats
    GO
    
    INSERT INTO mySpace99([name], [rows], reserved, Data, index_size, unused) 
    EXEC sp_spaceused myTable99
    GO
    
    TRUNCATE TABLE myTable99
    GO
    
    DECLARE @x int
    
    SELECT @x = 0
    
    WHILE @x < 10000
      BEGIN
    	INSERT INTO myTable99(Col2, Col3) SELECT 'x', 0
    	SELECT @x = @x + 1
      END 
    
    EXEC sp_updatestats
    GO
    
    INSERT INTO mySpace99([name], [rows], reserved, Data, index_size, unused) 
    EXEC sp_spaceused myTable99
    GO
    
    TRUNCATE TABLE myTable99
    GO
    
    DECLARE @x int
    
    SELECT @x = 0
    
    WHILE @x < 10000
      BEGIN
    	INSERT INTO myTable99(Col3) SELECT 0
    	SELECT @x = @x + 1
      END 
    
    EXEC sp_updatestats
    GO
    
    INSERT INTO mySpace99([name], [rows], reserved, Data, index_size, unused) 
    EXEC sp_spaceused myTable99
    GO
    
    SELECT * FROM mySpace99
    GO
    
    SET NOCOUNT ON
    GO
    
    DROP TABLE mySpace99
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Explain which part?
    Code:
    Col1        rows        reserved             Data                 index_size           unused               name                                                                                                                             
    ----------- ----------- -------------------- -------------------- -------------------- -------------------- -------------------------------------------------------------------------------------------------------------------------------- 
              1           0 0 KB                 0 KB                 0 KB                 0 KB                 myTable99
              2        9940 200 KB               160 KB               8 KB                 32 KB                myTable99
              3        9969 200 KB               160 KB               8 KB                 32 KB                myTable99
              4        9969 200 KB               160 KB               8 KB                 32 KB                myTable99
    -PatP

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It say that the data is all the same...whethere or not you insert values for col2, or col2 and 3.

    I would have expected Col2 alone to use less space..

    Damn, I gotta buy Kalens book
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You've only got 20 pages of data. There aren't even any variable length columns. What did you expect ?!?!

    -PatP

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I guess the question was does it still allocate the space for the bit value even though it's null...I thought you said it doesn't

    But it appears it does...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Yep. We got bit by this in the upgrade from SQL 6.5 to SQL 2000. We had a database with nullable varchar(255) fields. Absolutely no problem in SQL 6.5, since whatever was not used by data was never allocated on the page, at least as far as nulls were concerned. SQL 2000 has different ideas on the matter. Our databases grew by a factor of 4 in the upgrade. Null values have space allocated for them in SQL 2000. I would guess this is to save on page-splits, as you update the rows, and "fill in the blanks".

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Hey, Thanks..

    Damn, I really need to get that book...luckily never had to an 6.5-2k upgrade
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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