Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    35

    Unanswered: storage and performance of NULLs and empty strings (was "Noobish Question")

    Am I right in assuming that when I have a column where all fields contain NULL, this does not increase the total data storage size if my database? Also, what kind of impact would it have on performance?

    And what if I inserted "" in varchar columns? I would think the increase in size would be marginal?

    The reason I'm asking is that I want to use an existing table and stored procedures for another purpose, but only need half of the columns. But it would significantly simplify application development.
    Last edited by Robse; 02-11-05 at 13:09.

  2. #2
    Join Date
    Dec 2004
    Location
    Sweden
    Posts
    74
    "NULL" is a value too, just that it means (in laymans terms) "the absence of a value".
    With this definition in mind, it WILL increase your database size if you make a post in a table where all the values are NULL's.
    However, if the fields were n'varchar the will probably not be much diffirence between making a field NULL or "". I haven't tested this, this is just what sounds logical to me.
    The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You can test it for your self

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99(PKID int IDENTITY(1,1) NOT NULL, Col1 varchar(100), Col2 char(100))
    CREATE TABLE myTable00(PKID int IDENTITY(1,1) NOT NULL, Col1 varchar(100) NOT NULL, Col2 char(100) NOT NULL)
    GO
    
    DECLARE @x int 
    SELECT @x = 1
    WHILE @x < 1000
      BEGIN
    	INSERT INTO myTable99(Col1) SELECT 'X'
    	INSERT INTO myTable00(Col1,Col2) SELECT REPLICATE('X',100),REPLICATE('X',100)
    	SELECT @x = @x + 1
      END
    GO
    
    EXEC sp_spaceused myTable99
    GO
    
    EXEC sp_spaceused myTable00
    GO
    
    SET NOCOUNT ON
    DROP TABLE myTable99
    DROP TABLE myTable00
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i tested it, but i don't get it

    name rows reserved data index_size unused
    myTable99 993 136KB 128KB 8KB 0KB
    myTable00 993 264KB 224KB 8KB 32KB

    you insert null into myTable.col2, but a honking great string of Xs into myTable00.col2

    the X's explain the difference in space consumed

    wasn't the question about the difference between NULL and an empty string?

    i'm so lost
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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