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.
"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.
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)
DECLARE @x int
SELECT @x = 1
WHILE @x < 1000
INSERT INTO myTable99(Col1) SELECT 'X'
INSERT INTO myTable00(Col1,Col2) SELECT REPLICATE('X',100),REPLICATE('X',100)
SELECT @x = @x + 1
EXEC sp_spaceused myTable99
EXEC sp_spaceused myTable00
SET NOCOUNT ON
DROP TABLE myTable99
DROP TABLE myTable00