Re: Null in SQL 2005/2008

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi od

This is NOT true, and has not been true since 7. The article that said it applied to SQL 7 was mistaken.
Starting in SQL 7, all internal storage formats changed. Fixed length columns, whether NULL or not, always take the FULL column length. Only variable length columns will store NULLs in zero bytes.

if a table has a Date
field, a long integer field, an integer field, a Varchar(30) field and this
table only has one record with the integer field has a value of 1 and the
rest are null, this record should only consume 4 bytes long plus the overhead
info for the rest of the fields (3 bytes) and that's it. Am I right?

No, this is wrong.

I will assume by Date you mean datetime and by long integer you mean bigint. Your row will have:

8 bytes for the datetime
8 bytes for the bigint
4 bytes for the int
plus overhead (which will be MORE than 3 bytes -- where does that number come from?)
of at least 11 bytes.

There are some changes in SQL 2008. You can choose to store the data in compressed format, which will take a lot fewer bytes (storage is much too complex to describe here), OR define your nullable columns as SPARSE, in which case there will be just 4 bits of overhead for each null value. But those features are SQL 2008 only (and compression is Enterprise Edition only)
--
HTH
Kalen
----------------------------------------
Kalen Delaney
SQL Server MVP
www.SQLServerInternals.com

"OceanDeep via SQLMonster.com" <u46587@uwe> wrote in message news:9f4fdf877ad59@xxxxxx
Just want to make sure that SQL 2205/2008 is still treating Null value the
same as in pervious version like SQL 2000 or 7. Is the following still true
in SQL 2005/2008? I copied this from a microsoft article back in 2003 which
applies to SQL 6.5 and 7.0.

"For all standard data types, NULL values stored by Microsoft SQL Server do
not take up any additional storage space. The only associated overhead for a
column containing a NULL value is a single byte in the storage record that
defines a pointer to the column. This is a fixed cost that is associated with
the creation of the column. There is a special case for Text/Image storage.

If the columns were created with "nulls allowed," no additional space is
allocated for Text and Image columns until data is actually inserted into
those columns. However, once a Text or Image column has had data inserted
into it, the page space remains allocated even if the column is changed to
NULL. The only way to free this allocated page space is to delete the row."

So if the above is still true in SQL 2005/SQL 2008, if a table has a Date
field, a long integer field, an integer field, a Varchar(30) field and this
table only has one record with the integer field has a value of 1 and the
rest are null, this record should only consume 4 bytes long plus the overhead
info for the rest of the fields (3 bytes) and that's it. Am I right? There
may be some other overhead info about this record that SQLis tracking but I
am just looking at the simple situation for now.

Please advise.

od

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200911/1

.

Relevant Pages

  • Re: Null in SQL 2005/2008
    ... Just to clarify the 4 bits overhead on a SPARSE defined field, In BOL, it ... I want to know if there is mis-print in BOL. ... Starting in SQL 7, ... field, a long integer field, an integer field, a Varcharfield and ...
    (microsoft.public.sqlserver.server)
  • Re: Null in SQL 2005/2008
    ... If you add a new column to an existing table, SQL Server will not immediately go through and add the 4 new bytes to each row. ... Are you asking if there is an article explaining why the devs at MS decided to design the row storage this way, or an article that goes explains the storage in detail? ... field, a long integer field, an integer field, a Varcharfield and ...
    (microsoft.public.sqlserver.server)
  • Re: Seeking by index of nvarchar field does not works
    ... one of the fields is an integer field. ... Note saying that is necessary ... I have simple SQL Mobile database with one table. ... When I try to execute this program on Pocket PC 2003 SE ...
    (microsoft.public.sqlserver.ce)
  • Null in SQL 2005/2008
    ... Just want to make sure that SQL 2205/2008 is still treating Null value the ... not take up any additional storage space. ... field, a long integer field, an integer field, a Varcharfield and this ... this record should only consume 4 bytes long plus the overhead ...
    (microsoft.public.sqlserver.server)
  • Re: size of ftInteger
    ... I did some more investigating. ... When setting up an integer field in a MS SQL table, ...
    (borland.public.delphi.database.ado)