Posted in Development

SQL Server – Sparse Column Property using SELECT INTO

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

FROM sys.columns

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

FROM tbl_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.