CODEWITHSUNDEEP
sql-servert-sqlclustered-index
alexithymia
alexithymia

Reputation: 357

Insert into table that uses Clustered Columnstore index

I want to insert into a table that uses clustered columnstore index. My logic is the following. First i am checking if the table has clustered columnstore index and then drop the index, insert the new data and finally create again the clustered columnstore index.

Here is my sample code.

declare @sql as nvarchar(max)
if exists (select i.name as indexname, 
       t.name as tablename
from   sys.indexes i
  join sys.tables t on i.object_id = t.object_id
where  i.type in (5, 6) and t.name = 'cci_table')
begin
        set @sql = '
                    DROP CLUSTERED COLUMNSTORE INDEX cci ON dbo.cci_table'
        print @sql

        /** insert data to cci_table **/

        set @sql = '
                    CREATE CLUSTERED COLUMNSTORE INDEX cci ON dbo.cci_table'  
        print @sql
end
else
begin
        set @sql = '
                    CREATE CLUSTERED COLUMNSTORE INDEX cci ON dbo.cci_table'  
        print @sql
end

Is it a good approach do it like this? Is there a different way to insert data in clustered columnstored index table, or do i have to drop current index and then create index again?

Upvotes: 2

Views: 3922

Answers (1)

alexithymia
alexithymia

Reputation: 357

It is not needed to drop and then create columnstore index as tables that use columnstore index are updatable. That means that i can insert new data to existing columnstore table if the index exists with no problem.

 declare @sql as nvarchar(max)
    if exists (select i.name as indexname, 
           t.name as tablename
    from   sys.indexes i
      join sys.tables t on i.object_id = t.object_id
    where  i.type in (5, 6) and t.name = 'cci_table')
    begin

            /** insert data to cci_table **/

    end
    else
    begin
            set @sql = '
                        CREATE CLUSTERED COLUMNSTORE INDEX cci ON dbo.cci_table'  
            print @sql

            /** insert data to cci_table **/

    end

Upvotes: 1

Related Questions