nullsql-server-2008sql-server-2008-r2
I read somewhere online and in a Microsoft Press book there is a field declaration attribute that can be used to indicate NULL VARCHAR values should not consume space at the leaf-level instead of consuming a few characters per leaf.
Could someone help me identify where that attribute is?
I cant remember what the Attribute keyword is but I remember seeing it because of the value of saving the empty 2 bytes and make it a 0 byte storage for no data.
An extension to @db2's answer with less (read:zero) hand-wrangling:
DECLARE @tb nvarchar(512) = N'dbo.[table]';
DECLARE @sql nvarchar(max) = N'SELECT * FROM ' + @tb
+ ' WHERE 1 = 0';
SELECT @sql += N' OR ' + QUOTENAME(name) + ' IS NULL'
FROM sys.columns
WHERE [object_id] = OBJECT_ID(@tb);
EXEC sys.sp_executesql @sql;
@ypercube's answer does manage this partially as a metadata only change.
Adding the constraint with NOCHECK means that no rows will need to be read to verify it, and if you are starting from a position where the column does not contain NULL values (and if you know none will be added between checking and adding the constraint) then, as the constraint prevents NULL values being created from future INSERT or UPDATE operations, this will work.
Adding the constraint can still have an impact on concurrent transactions however. The ALTER TABLE will need to acquire a Sch-M lock first. Whilst it is waiting for this all other table accesses will be blocked as described here.
Once the Sch-M lock is acquired the operation should be pretty quick however.
One problem with this is that even if you know the column in fact has no NULLs the constraint is not trusted by the query optimiser which means that the plans can be sub optimal.
CREATE TABLE T (X INT NULL)
INSERT INTO T
SELECT ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM master..spt_values
ALTER TABLE T WITH NOCHECK
ADD CONSTRAINT X_NOT_NULL
CHECK (X IS NOT NULL) ;
SELECT *
FROM T
WHERE X NOT IN (SELECT X FROM T)
Compare this with the simpler
ALTER TABLE T ALTER COLUMN X INT NOT NULL
SELECT *
FROM T
WHERE X NOT IN (SELECT X FROM T)
One possible problem you might encounter with altering the column definition in this way is that it not only needs to read all the rows to verify that they meet the condition but also can end up actually performing logged updates to the rows.
A possible half way house might be to add the check constraint WITH CHECK. This will be slower than WITH NOCHECK as it needs to read all rows but it does allow the query optimiser to give the simpler plan in the query above and it should avoid the possible logged updates issue.
Best Answer
I believe you are talking about SPARSE columns.