COLUMN_SET Option in sparse column in SQL Server

A COLUMN_SET is an XML representation of all sparse columns in a table. It appears as one logical column and contains name value pairs for only the sparse columns that are NOT NULL. It does not physically store data itself and it is computed and materialized on demand. It is automatically maintained by the engine and has strict limitations by design.

A COLUMN_SET is a computed XML projection of all sparse columns in a table. It is read-only because it has no physical storage and is dynamically derived from sparse columns to preserve data integrity, type safety, and deterministic behavior.

See the demo

CREATE TABLE EmployeeAttributes (   EmployeeID INT PRIMARY KEY,   Height     INT SPARSE NULL,   Weight     INT SPARSE NULL,   BloodGroup CHAR(2) SPARSE NULL,   Attributes XML COLUMN_SET FOR ALL_SPARSE_COLUMNS ); INSERT INTO EmployeeAttributes (EmployeeID, Height, BloodGroup) VALUES (1, 180, 'O+'),        (2, 163, 'A+');  INSERT INTO EmployeeAttributes (EmployeeID, Weight) VALUES (3, 80),        (4, 63);

Table created successfully and data inserted.

See the data.

SELECT * FROM EmployeeAttributes

See the above value, if the column value is null then it will not return. Here we are seeing the attributes column in XML but if we want to see the all-column value then we need to write the column in name in select statement.

We cannot update the Attributes column. It is read only. We must update sparse columns directly, not via COLUMN_SET. The COLUMN_SET updates automatically.