SQL SERVER – SPARSE Property Doesn’t Carry Forward Using SELECT INTO

During one of the recent posts on a SQLBangalore UG FB page, one of the members asked an interestingly simple question. He was asking why is it that when you use SELECT-INTO to create a new table the triggers are not getting carried forward as part of the definition. A number of folks answered and it was a great learning experience for me personally. This blog was inspired from that post and I thought to add a different spin to it. My spin: the SPARSE property is not carried forward either.

Though the basic premise of Triggers etc. are written on the FB page, let me ask you a simple question. Though using SELECT-INTO bring forward most of the attributes of the columns during the definition. It is not always true that the definitions will move forward.

One of the outlets that I found recently is the definition of the columns as SPARSE. One person asked that I was writing a lot of blogs around SPARSE columns and if the same will be taken forward while doing a SELECT INTO. The best to learn this is by testing in our environment, here is the script I have used:

-- Create a table and insert a row
CREATE TABLE tbl_host (Age SMALLINT sparse, Name VARCHAR(25));
INSERT INTO tbl_host VALUES (24, 'SQLAuthority');
-- Create a new table from the original
SELECT * INTO tbl_Copy FROM tbl_host;
-- See if the sparse property was carried over
SELECT OBJECT_NAME(OBJECT_ID) AS tabname,
name AS colname,
column_id,
is_sparse
FROM sys.columns
WHERE OBJECT_ID = OBJECT_ID( 'tbl_host') OR OBJECT_ID = OBJECT_ID( 'tbl_Copy');
-- Clean up time
DROP TABLE tbl_host
DROP TABLE tbl_Copy

The output of the above script would look like:

From this it is evident that the SPARSE property is not carried forward when we use SELECT-INTO to create a new table. I would love to learn from you folks on other things that don’t carry forward when we use SELECT-INTO. Tell me what scenario’s you were surprised and learnt something new while using the SELECT-INTO clause. Use the comments section to share your knowledge.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.