Recently, a member of the SQLBangalore UG Facebook page asked an interesting question about the SELECT-INTO statement in SQL Server. They were curious why triggers are not carried forward when using SELECT-INTO to create a new table. This sparked a discussion among the community, and it got me thinking about other aspects of SELECT-INTO that may not be carried forward.

While SELECT-INTO does bring forward most of the attributes of the columns during the table definition, it is not always true that all definitions will be moved forward. One particular aspect that caught my attention is the definition of columns as SPARSE.

To explore this further, I decided to test the behavior of SELECT-INTO with SPARSE columns in our environment. Here is the script I 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
DROP TABLE tbl_host;
DROP TABLE tbl_Copy;

The output of the above script shows that the SPARSE property is not carried forward when using SELECT-INTO to create a new table. This is an important consideration for those working with SPARSE columns in their databases.

I would love to hear from you about other scenarios where SELECT-INTO does not carry forward certain attributes. Have you encountered any surprises or learned something new while using the SELECT-INTO clause? Please share your knowledge and experiences in the comments section below.