Inserting and updating data in a Column Set
Records can be inserted to the sparse columns without using the column set but once inserted, records can be retrieved by using the column set.
Code 1: Inserting a record without inserting a value to the column set.
— Insert a record to the table.
INSERT INTO dbo.Customers
([Id], [FirstName], [LastName], [Gender], [Telephone], [MonthlyIncome], [Comments])
VALUES
(1, ‘Dinesh’, ‘Priyankara’, 1, ‘777395871’, 20000, ‘no comments’)
— Retrieve the record and see
SELECT [Id], [FirstName], [LastName], [Gender], [Telephone], [MonthlyIncome], [Comments], [AllSparseColumns] FROM dbo.Customers
/*
Result:
<Gender>1</Gender><Telephone>777395871</Telephone><MonthlyIncome>20000.0000</MonthlyIncome><Comments>no comments</Comments>
*/
Records can be inserted and updated by using the column set. Code 2 shows the way of inserting a record and updating a record via column set.
Code 2: Inserting and updating the column set.
— Inserting a new record. Note that the statement uses the column set to
— insert values for Comments and Telephone columns
INSERT INTO dbo.Customers
([Id], [FirstName], [LastName], [AllSparseColumns])
VALUES
(3, ‘Yeshan’, ‘Santhush’, ‘<Comments>No comments</Comments><Telephone>777225656</Telephone>’)
— Update the record.
— This makes Comments column NULL because xml string does not contain a node for Comments column.
— This updates the Telephone column with the new value.
UPDATE dbo.Customers
SET [AllSparseColumns] = ‘<Telephone>777454632</Telephone><Gender>1</Gender>’
WHERE Id = 3
Tracking modifications with triggers
This is bit tricky. Normally we use the UPDATE() function to find out whether a particular column is updated or not. If you have implemented this in a trigger that is associated with a table that has sparse columns and a column set, the return value of the UPDATE() function will not be what you anticipated. The folloLet us test this one too.
Code 1: Creating a UPDATE trigger on the Customers table.
— Creating a update trigger on Customers table.
CREATE TRIGGER tr_Customers_Update ON dbo.Customers
FOR UPDATE
AS
BEGIN
IF UPDATE(Gender)
print ‘Gender column updated.’
IF UPDATE(Telephone)
print ‘Telephone column updated.’
IF UPDATE(Comments)
print ‘Comments column updated.’
IF UPDATE(AllSparseColumns)
print ‘AllSparseColumns column updated.’
END
When you update the column set explicitly, the UPDATE() function returns true for the column set. Not only that, the UPDATE() functions that are written to all sparse columns return true too. When a sparse column is explicitly updated, the UPDATE() function returns true for the sparse column and for the column set.
Code 2: Updating the table and testing the UPDATE() function in the trigger.
— Update the column set.
— This update makes all UPDATE() functions
— to return true.
UPDATE dbo.Customers
SET [AllSparseColumns] = ‘<Telephone>4455</Telephone>’
WHERE Id = 3
/*
Result:
Gender column updated.
Telephone column updated.
Comments column updated.
AllSparseColumns column updated.
(1 row(s) affected)
*/
— Update the Gender column.
— This update makes UPDATE() function of
— Gender column and column set to return true.
UPDATE dbo.Customers
SET Gender = 1
WHERE Id = 3
/*
Result:
Gender column updated.
AllSparseColumns column updated.
(1 row(s) affected)
*/
If you have written the same trigger for INSERT statements, you will see the same behavior for the INSERT operation. When a value is inserted to one of the sparse columns and you make the others NULL, the UPDATE() function returns true to the sparse column and to the column set. When a value is inserted to the column set, the UPDATE() function returns true to the columns set and for the all sparse columns.