Space taken by Null !
-
Over internet there are a lot of topics regarding the size taken by NULL values. There is some mismatch in every 2 topics so i though of doing some POC myself. I observed something which was strange. Following is the list of queries i executed.
create table tbltemp1(i int,a nvarchar(50),b varchar(50))
sp_spaceused 'tbltemp1' --Over here it showed as zero
insert into tbltemp1 values(null,null,null)
select * from tbltemp1
sp_spaceused 'tbltemp1' -- over it shows the size
delete from tbltemp1
sp_spaceused 'tbltemp1' --over here it should actually show zero but it is showing some values.
In the last Sp_spaceused, my question is why is it showing the values for the size of DATA.
-
Instead of delete if you fire TRUNCATE statement it will show data size is 0
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
Adding some more points to the problem:
inspite of defining columns as SPARSE it is still taking space in database as I read on some blog that if we specify column as SPARSE it will take any space when using with NULL.
create table tbltemp1(i int sparse,a nvarchar(50) sparse,b varchar(50) sparse)sp_spaceused 'tbltemp1' --Over here it showed as zero
insert into tbltemp1 values(null,null,null)
select * from tbltemp1
sp_spaceused 'tbltemp1' -- over it shows the size
delete from tbltemp1
sp_spaceused 'tbltemp1'
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
kapil_kk (8/21/2013)
Adding some more points to the problem:inspite of defining columns as SPARSE it is still taking space in database as I read on some blog that if we specify column as SPARSE it will take any space when using with NULL.
create table tbltemp1(i int sparse,a nvarchar(50) sparse,b varchar(50) sparse)sp_spaceused 'tbltemp1' --Over here it showed as zero
insert into tbltemp1 values(null,null,null)
select * from tbltemp1
sp_spaceused 'tbltemp1' -- over it shows the size
delete from tbltemp1
sp_spaceused 'tbltemp1'
This behaviour has nothing to do with NULLS, but rather the way SQL Server deallocates pages.
http://technet.microsoft.com/en-us/library/ms189835.aspx
When rows are deleted from a heap the Database Engine may use row or page locking for the operation. As a result, the pages made empty by the delete operation remain allocated to the heap. When empty pages are not deallocated, the associated space cannot be reused by other objects in the database.
To delete rows in a heap and deallocate pages, use one of the following methods.
* Specify the TABLOCK hint in the DELETE statement. Using the TABLOCK hint causes the delete operation to take an exclusive lock on the table instead of a row or page lock. This allows the pages to be deallocated. For more information about the TABLOCK hint, see Table Hints (Transact-SQL).
* Use TRUNCATE TABLE if all rows are to be deleted from the table.
* Create a clustered index on the heap before deleting the rows. You can drop the clustered index after the rows are deleted. This method is more time consuming than the previous methods and uses more temporary resources.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply