SQL Server Portal

If you would like to post any most frequent DML (Data manipulation language) transaction except Select statement in any Columnstore Index owned table, it will generate an error. (I shall post the error details in my upcoming post). You must therefore disable/ drop columnstore index before posting any DML (Data manipulation language). Given below is the script that disables all indexes with respect to category (NONCLUSTERED COLUMNSTORE, HEAP, CLUSTERED INDEX, NONCLUSTERED INDEX, XML INDEX, SPATIAL INDEX). Also, it can disable ALL indexes of any particular databases.

CREATE PROCEDURE dbo.[Disable_ALL_INDEXES_PROC]
@Type int
AS
BEGIN
DECLARE @Schema_TableName nvarchar(max)
DECLARE @String varchar(max)
DECLARE IndexCursor CURSOR FOR

Select scma.[name] + '.'
+ Quotename(tbl.name) as [Schema_TableName]
from sys.tables AS tbl
INNER JOIN sys.indexes AS i
ON (i.index_id > 0 AND i.is_hypothetical = 0)
AND (i.object_id=tbl.object_id)
INNER JOIN sys.schemas AS scma
ON tbl.schema_id=scma.schema_id
Where tbl.type='U' AND ((1=(CASE
WHEN @Type is Null THEN 1 ELSE 0 END)
OR i.type = @Type))
Order By scma.[name] + '.' + tbl.name

OPEN IndexCursor
FETCH NEXT FROM IndexCursor INTO @Schema_TableName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @String = 'ALTER INDEX ALL ON ' + @Schema_TableName + ' DISABLE;'
Print @String
EXEC (@String)
FETCH NEXT FROM IndexCursor INTO @Schema_TableName
END
CLOSE IndexCursor
DEALLOCATE IndexCursor
END
GO
--SYNTAX
--EXEC dbo.[Disable_ALL_INDEXES_PROC] Index type

--TO DISABLE NONCLUSTERED COLUMNSTORE INDEXES ONLY
USE AdventureWorks2012
GO
EXEC dbo.[Disable_ALL_INDEXES_PROC] 6

-- TO DISABLE HEAP ONLY
USE AdventureWorks2012
GO
EXEC dbo.[Disable_ALL_INDEXES_PROC] 0
GO
-- TO DISABLE CLUSTERED INDEX ONLY
USE AdventureWorks2012
GO
EXEC dbo.[Disable_ALL_INDEXES_PROC] 1
GO
-- TO DISABLE NONCLUSTERED INDEX ONLY
USE AdventureWorks2012
GO
EXEC dbo.[Disable_ALL_INDEXES_PROC] 2
GO
-- TO DISABLE XML INDEX ONLY
USE AdventureWorks2012
GO
EXEC dbo.[Disable_ALL_INDEXES_PROC] 3
GO
-- TO DISABLE SPATIAL INDEX ONLY
USE AdventureWorks2012
GO
EXEC dbo.[Disable_ALL_INDEXES_PROC] 4
GO
--TO DISABLE ALL INDEXES
USE AdventureWorks2012
GO
EXEC dbo.[Disable_ALL_INDEXES_PROC] NULL
GO

Read Full Post »