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’)

Sparse_1

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:

Sparse_2

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s