July 8, 2015 at 5:55 am
hi all,
i am confused 🙂 does null column take storage in table ?
i explain all with exmple it is the best 🙂 please do int on qa not in prod. (its not big data but you know 🙂
i creted the follwing table:
drop table [dbo].[TBL_orig]
CREATE TABLE [dbo].[TBL_orig](
[1] [bigint] NOT NULL,
[2] [bigint] NULL,
[3] [bigint] NULL,
[4] [bigint] NULL,
[5] [float] NULL,
[6] [datetime] NULL,
[7] [datetime] NULL,
[8] [float] NULL,
[9] [float] NULL,
[10] [smallint] NULL,
[11] [smallint] NULL,
[12] [int] NULL,
[13] [datetime] NULL,
) ON [primary]
insert data to this column :
insert into [dbo].[TBL_orig] ([1],[2],[3],[4],[5],[6],[7])
select 111111,2222222,333333,44444,555555,666666,700000
go 100000
get size on this table :
sp_spaceused '[dbo].[TBL_orig]' :
name rows reserved data index_size unused
TBL_orig100000 11656 KB 11600 KB 8 KB 48 KB
now drop null column: ( i didnt insert data to those column!!!)
alter table [dbo].[TBL_orig]
drop column [13],[12],[11],[10],[9],[8]
--create same table with without those table i removed.
drop table [dbo].[TBL_copy]
CREATE TABLE [dbo].[TBL_copy](
[1] [bigint] NOT NULL,
[2] [bigint] NULL,
[3] [bigint] NULL,
[4] [bigint] NULL,
[5] [float] NULL,
[6] [datetime] NULL,
[7] [datetime] NULL,
) ON [primary]
insert into [dbo].[TBL_copy] ([1],[2],[3],[4],[5],[6],[7])
select [1],[2],[3],[4],[5],[6],[7] from [dbo].[TBL_orig]
get size on this table :
sp_spaceused '[dbo].[TBL_copy]'
name rows reserveddata index_sizeunused
TBL_copy100000 7880 KB7848 KB8 KB 24 KB
table size of data '[dbo].[TBL_copy]' is : 7848
table size of data [dbo].[TBL_orig] is : 11600
diff between orig and copy is : 3752 kb ( almost 4 mb on 100000 rows)
there is matrmtical explain for this 🙂 ?
there is any formule that can explain this gap ?
does null value will take a place even no data inser to this columns and
if yes this answer is4 mb is logical gap ? (:))
i so confuesd 🙂
i didnt find clear explain for this ,
so i will be glad id some one have answer
thaks alot
sharon
July 8, 2015 at 6:47 am
Yes null columns take up space, you can potentially however reduce this overhead using sparse columns:
https://msdn.microsoft.com/en-us/library/cc280604.aspx
MCITP SQL 2005, MCSA SQL 2012
July 8, 2015 at 11:15 am
thanks you for replay,
so you say if column bigint define with null
and i didnt insert data to this column this column
will be null with over head of 8 bytes?
thnak sharon
July 8, 2015 at 11:47 am
sharon-472085 (7/8/2015)
thanks you for replay,so you say if column bigint define with null
and i didnt insert data to this column this column
will be null with over head of 8 bytes?
thnak sharon
Integer and non-variable character based datatypes (int, char, datetime, etc.) are the same byte width regardless of whether they contain NULL, 0, or 9,999,999.
However, Row Compression will (amoung other things) store only meta data for NULL values on the row, I believe 4 bits for each NULL column, so the physical storage for the page on disk and in buffer cache is compacted when it contains columns with NULL values. Page Compression takes it a step further and will compress all rows for each page. This provides mutiples (2x - 10x) compression on tables having low cardinality columns, for example those having mostly NULL or mostly 0 values.
Compression is not compatible with Sparse Columns, so you have to choose between the two. You can experiment to determine which is better for your specific scenario, which probably boils down to how many nullable columns you have and their overall cardinality.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 14, 2015 at 12:17 am
thank you vary much
it help me alot
sharon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply