Hi friends, in our day to day work we used SELECT…INTO clause to create replica of a table for different purposes. I was also creating a table for testing using SELECT..INTO from a table which also contains SPARSE property on a column.
So, what is SPARSE column?
In simple words, Sparse columns are the columns which reduces the space storage for NULL values.
As we know that when we create a table using SELECT…INTO clause many column properties are not transferred like constraint, computed column and many more.
But, when I create the table using SELECT..INTO I came to know that Sparse column is also one of those property which doesn’t get inherited.
Let me show you this with an example –
First, we will create a table containing Sparse property on columns:
Create table tbl_Sparse
tbl_Id INT NOT NULL,
Age INT SPARSE,
PersonId INT SPARSE
We can query system table sys.columns to check whether a column has sparse property or not like this:
SELECT OBJECT_NAME(OBJECT_ID) AS tablename, name as ColName, is_sparse
WHERE OBJECT_ID = OBJECT_ID( ‘tbl_sparse’)
As we can see in the output that columns Age and PersonId has value 1 for is_sparse column which means that these columns contains sparse property.
Now we will create replica of this table tbl_sparse with SELECT INTO clause.
SELECT * INTO Test_Sparse
When I again query sys.columns table to check column properties I found that sparse properties not get inherited as seen in screenshot below:
This time columns Age and PersonId has 0 value for column Is_Sparse which means these columns doesn’t have sparse property.
So from this we learned that SPARSE property does not get transferred when we use SELECT..INTO clause to create a new table.
That’s all for the day friends!!! Have a nice day.