Share via

  • Article

Exercise 4: Techniques for updating a table with a COLUMNSTORE index

In this exercise, you will look at the different methods for updating a table with a columnstore index.

At the end of this exercise, you will understand how to:

  1. Update data in the table by disabling the columnstore index.
  2. Use table partitioning to move data in and out of the table while the columnstore index is still enabled

Task 1 – Updating a Table with a Columnstore Index by Disabling and Rebuilding the Index

  1. Copy and paste the following code into the New Query area of the SQL Management and press F5 in order to demonstrate what happens if you attempt to INSERT a new row into a table with a columnstore index:

    SQL

    INSERT dbo.FactProductInventory (ProductKey, DateKey, UnitCost, UnitsIn, UnitsOut, UnitsBalance) VALUES (167, '2005-01-13', .25, 0, 0, 870)

    The operation should fail with the following error message:

    Output

    Msg 35330, Level 15, State 1, Line 1 INSERT statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the INSERT statement, then rebuilding the columnstore index after INSERT is complete.

  2. Copy and paste the following code into the New Query area of the SQL Management and press F5 in order to disable the columnstore index:

    SQL

    ALTER INDEX [IX_CS_FactProductInventory] ON [dbo].[FactProductInventory] DISABLE GO

    You should see the following message:

    Output

    Command(s) completed successfully.

  3. Copy and paste the following code into the New Query area of the SQL Management and press F5 in order to demonstrate what happens if you attempt to INSERT a new row into a table with a columnstore index:

    SQL

    INSERT dbo.FactProductInventory (ProductKey, DateKey, UnitCost, UnitsIn, UnitsOut, UnitsBalance) VALUES (167, '2005-01-13', .25, 0, 0, 870)

    You should see the following message:

    Output

    (1 row(s) affected)

  4. Copy and paste the following code into the New Query area of the SQL Management and press F5 in order to rebuild the columnstore index:

    SQL

    ALTER INDEX [IX_CS_FactProductInventory] ON [dbo].[FactProductInventory] REBUILD PARTITION = ALL GO

    You should see the following message:

    Output

    Command(s) completed successfully.

  5. Copy and paste the following code into the New Query area of the SQL Management and press F5 in order to demonstrate what happens if you attempt to INSERT a new row into a table with a columnstore index:

    SQL

    INSERT dbo.FactProductInventory (ProductKey, DateKey, UnitCost, UnitsIn, UnitsOut, UnitsBalance) VALUES (167, '2005-01-13', .27, 0, 0, 870)

    The operation should fail with the following error message:

    Output

    Msg 35330, Level 15, State 1, Line 1 INSERT statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the INSERT statement, then rebuilding the columnstore index after INSERT is complete.

Task 2 – Using Table Partitioning to Move Rows Out of and Into a Table with an Enabled Columnstore Index

  1. Copy and paste the following code into the New Query area of the SQL Management and press F5 in order to drop the columnstore index from the FactProductInventory table:

    SQL

    DROP INDEX [IX_CS_FactProductInventory] ON [dbo].[FactProductInventory]

    The operation should return the following message:

    Output

    Command(s) completed successfully.

  2. Next, Copy and paste the following code into the New Query area of the SQL Management and press F5 in order to create a partition function and partition scheme that we will use with the FactProductInventory table (partitioning by month):

    SQL

    CREATE PARTITION FUNCTION [pfn_DateKey](date) AS RANGE RIGHT FOR VALUES (N'2005-01-13', N'2005-02-13', N'2005-03-13', N'2005-04-13', N'2005-05-13', N'2005-06-13', N'2005-07-13', N'2005-08-13', N'2005-09-13', N'2005-10-13', N'2005-11-13', N'2005-12-13', N'2006-01-13', N'2006-02-13', N'2006-03-13', N'2006-04-13', N'2006-05-13', N'2006-06-13', N'2006-07-13', N'2006-08-13', N'2006-09-13', N'2006-10-13', N'2006-11-13', N'2006-12-13', N'2007-01-13', N'2007-02-13', N'2007-03-13', N'2007-04-13', N'2007-05-13', N'2007-06-13', N'2007-07-13', N'2007-08-13', N'2007-09-13', N'2007-10-13', N'2007-11-13', N'2007-12-13', N'2008-01-13', N'2008-02-13', N'2008-03-13', N'2008-04-13', N'2008-05-13', N'2008-06-13', N'2008-07-13', N'2008-08-13', N'2008-09-13', N'2008-10-13', N'2008-11-13', N'2008-12-13') CREATE PARTITION SCHEME [pfn_DateKey] AS PARTITION [pfn_DateKey] ALL TO([PRIMARY])
    

    

    The following message should be returned from this operation:
    

    

    

    Output
    

    
Partition scheme 'pfn_DateKey' has been created successfully. 'PRIMARY' is marked as the next used filegroup in partition scheme 'pfn_DateKey'.

    

    3. 

  3. Copy and paste the following code into the New Query area of the SQL Management and press F5 in order to create the clustered index, a nonclustered index and a columnstore index based on the newly created partition function:
    

    

    

    SQL
    

    
CREATE CLUSTERED INDEX [CI_DateKey] ON [dbo].[FactProductInventory] ([DateKey], [ProductKey]) ON [pfn_DateKey]([DateKey]) GO CREATE NONCLUSTERED INDEX [NCI_UnitCost] ON [dbo].[FactProductInventory] ([UnitCost] ASC) ON [pfn_DateKey]([DateKey]) GO -- Add in the CS index (notice no need to specifiy the partition function) CREATE NONCLUSTERED COLUMNSTORE INDEX [NSI_FactProductInventory] ON [dbo].[FactProductInventory] ( [ProductKey], [DateKey], [UnitCost], [UnitsIn], [UnitsOut], [UnitsBalance] ) GO
    FakePre-50bdc495f0274e5a8b99d80a5bbd4df4-e920f37e61564e37a895e72f9c5c9d08

    

    

    You should see the following message:
    

    

    

    Output
    

    
Command(s) completed successfully.

    

    4. 

  4. Copy and paste the following code into the New Query area of the SQL Management and press F5 in order to demonstrate what happens if you attempt to INSERT a new row into a table with a columnstore index:
    

    

    

    SQL
    

    
INSERT dbo.FactProductInventory (ProductKey, DateKey, UnitCost, UnitsIn, UnitsOut, UnitsBalance) VALUES (167, '2005-01-14', .28, 0, 0, 870)

    

    5. 



The operation should fail with the following error message:






Output



Msg 35330, Level 15, State 1, Line 1 INSERT statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the INSERT statement, then rebuilding the columnstore index after INSERT is complete.





    

  1. Copy and paste the following code into the New Query area of the SQL Management and press F5 in order to create a new table to hold archived data. Also execute the supporting indexes and constraints that are required in order to perform the partition switch activities later in this task:
    

    

    

    SQL
    

    
CREATE TABLE [dbo].[Archive_FactProductInventory] ( [ProductKey] [int] NOT NULL, [UnitCost] [money] NOT NULL, [UnitsIn] [int] NOT NULL, [UnitsOut] [int] NOT NULL, [UnitsBalance] [int] NOT NULL, [DateKey] [date] NOT NULL ) ON [PRIMARY] GO CREATE CLUSTERED INDEX [Archive_FactProductInventory_CI_DateKey] ON [dbo].[Archive_FactProductInventory] ( [DateKey] ASC, [ProductKey] ASC ) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [Archive_FactProductInventory_NCI_UnitCost] ON [dbo].[Archive_FactProductInventory] ([UnitCost] ASC) INCLUDE ([DateKey]) ON [PRIMARY] GO ALTER TABLE [dbo].[Archive_FactProductInventory] WITH CHECK ADD CONSTRAINT [Archive_FactProductInventory_FK_ProductInventory_DimProduct] FOREIGN KEY([ProductKey]) REFERENCES [dbo].[DimProduct] ([ProductKey]) GO ALTER TABLE [dbo].[Archive_FactProductInventory] WITH CHECK ADD CONSTRAINT [chk_Archive_FactProductInventory] CHECK ([DateKey]>=N'2006-06-13' AND [DateKey]<N'2006-07-13') GO
    FakePre-b34dfee8dfdd435e8da056b91f212ca4-d38aa924155846cc933ef15abda12398FakePre-bdc8ff59dfb14d9ebdb3d34d362aeb0f-18fea9454ee44665b7c1f28c21a4043fFakePre-a1bebffc413c407e905650437fb17ecd-25f1c092685f4accbc0c435ff4b76cbb

    

    

    You should see the following message:
    

    

    

    Output
    

    
Command(s) completed successfully.

    

    2. 

  2. Next, Copy and paste the following code into the New Query area of the SQL Management and press F5 in order to switch out a populated partition from the FactProductInventory table to the new archive table:
    

    

    

    SQL
    

    
ALTER TABLE [AdventureWorksDW2012].[dbo].[FactProductInventory] SWITCH PARTITION 19 TO [AdventureWorksDW2012].[dbo].[Archive_FactProductInventory]

    

    

    You should see the following message:
    

    

    

    Output
    

    
Command(s) completed successfully.

    

    3. 

  3. Copy and paste the following SQL code into the New Query area of the SQL Management and press F5 in order to validate that rows were indeed moved to the archive table:
    

    

    

    SQL
    

    
SELECT COUNT(*) FROM [AdventureWorksDW2012].[dbo].[Archive_FactProductInventory]

    

    

    You should see the value 18,180 returned.
    4. 

  4. Copy and paste the following code into the New Query area of the SQL Management and press F5 in order to validate that – while the partition switch worked – other types of data modifications are still not allowed:
    

    

    

    SQL
    

    
INSERT dbo.FactProductInventory (ProductKey, DateKey, UnitCost, UnitsIn, UnitsOut, UnitsBalance) VALUES (167, '2005-01-14', .28, 0, 0, 870)

    

    5. 



The operation should fail with the following error message:






Output



Msg 35330, Level 15, State 1, Line 1 INSERT statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the INSERT statement, then rebuilding the columnstore index after INSERT is complete.





    

  1. Copy and paste the following code into the New Query area of the SQL Management and press F5 in order to switch back data into the FactProductInventory table from Archive_FactProductInventory. The first query checks the row count and the second performs the ALTER TABLE SWITCH:
    

    

    

    SQL
    

    
SELECT COUNT(*) FROM [dbo].[FactProductInventory] GO ALTER TABLE [dbo].[Archive_FactProductInventory] SWITCH TO [dbo].[FactProductInventory] PARTITION 19 GO
    

    

    This operation should have returned a row count of 758,107, followed by the following error message:
    

    

    

    Output
    

    
Msg 4947, Level 16, State 1, Line 2 ALTER TABLE SWITCH statement failed. There is no identical index in source table 'AdventureWorksDW2012.dbo.Archive_FactProductInventory' for the index 'NSI_FactProductInventory' in target table ‘AdventureWorksDW2012.dbo.FactProductInventory’.

    

    2. 

  2. Copy and paste the following code into the New Query area of the SQL Management and press F5 in order to create the required columnstore index on the archive table (required to allow us to perform the switch back to the main table):
    

    

    

    SQL
    

    
CREATE NONCLUSTERED COLUMNSTORE INDEX [NSI_ArchiveFactProductInventory] ON [dbo].[Archive_FactProductInventory] ( [ProductKey], [DateKey], [UnitCost], [UnitsIn], [UnitsOut], [UnitsBalance] )WITH (DROP_EXISTING = OFF) GO

    

    

    You should see the following message:
    

    

    

    Output
    

    
Command(s) completed successfully.

    

    3. 

  3. Copy and paste the following code into the New Query area of the SQL Management and press F5 in order to switch back the partition:
    

    

    

    SQL
    

    
ALTER TABLE [dbo].[Archive_FactProductInventory] SWITCH TO [dbo].[FactProductInventory] PARTITION 19

    

    

    You should see the following message:
    

    

    

    Output
    

    
Command(s) completed successfully.

    

    4. 

  4. Copy and paste the following code into the New Query area of the SQL Management and press F5 in order to check the row count of FactProductInventory:
    

    

    

    SQL
    

    
SELECT COUNT(*) FROM [dbo].[FactProductInventory]

    

    5. 

  5. You should see that the row count is now 776,287 – the expected increase from the previous 758,107 row count.
    6. 










< previous | next >














Downloads


Download The Offline Training Kit

Download Hands-on Lab Document (DOCX)

Download Hands-on Lab Source Files


Contents