August 20, 2014 at 9:16 am
Hi,
We use a lot of views, many of which are indexed. Changes are reasonably frequent and made by multiple people.
If ALTER VIEW is executed against an indexed view, any indexes are instantly dropped without a trace.
Instead of blocking all ALTER VIEW statements via [ur=http://msdn.microsoft.com/en-us/library/ms189799.aspxl]BOL's[/url] example "Saftey" trigger (Example D), I just want to prevent them on indexed views.
I am (Stubbornly) trying to challenge BOL's clear statement :
"ALTER VIEW can be applied to indexed views; however, ALTER VIEW unconditionally drops all indexes on the view."
I only know of 4 ways to detect if an index exists. All of them return negative results in an DDL trigger.
sys.sysIndexs
sys.Indexs
OBJECTPROPERTY(<View_Object_ID>, 'IsIndexed')
sp_HelpIndex
It really bugs me that you can rollback an "Index drop" but you can't prevent it.
The information is there somewhere otherwise it can't rollback.
Are there other ways of detecting the index then rolling back?
Transaction log reading? (Can't make heads or tails of the data)
SELECT[RowLog Contents 0],
[RowLog Contents 1],
[Current LSN],
Operation,
Context,
[Transaction ID],
AllocUnitId,
AllocUnitName,
[Page ID],
[Slot ID]
FROMsys.fn_dblog(NULL,NULL)
This code shows that the index information is gone inside the trigger, but is available again after a rollback.
SET NOCOUNT ON
----------------------------------------------------------------------------------------------------------------------------------
-- Pre-clean
----------------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID('MyIndexedView') IS NOT NULL
DROP VIEW MyIndexedView
IF OBJECT_ID('MyIndexedView_Control') IS NOT NULL
DROP VIEW MyIndexedView_Control
IF OBJECT_ID('MyDBs') IS NOT NULL
DROP TABLE MyDBs
IF EXISTS (SELECT 1 FROM sys.triggers WHERE name = 'myDDLTrigger')
DROP TRIGGER myDDLTrigger ON DATABASE
GO
----------------------------------------------------------------------------------------------------------------------------------
-- Create
----------------------------------------------------------------------------------------------------------------------------------
SELECTName DBName, Log_Reuse_Wait_Desc LogReuse
INTOMyDBs
FROMsys.Databases
GO
CREATE VIEW MyIndexedView
WITH SCHEMABINDING
AS
SELECTDBName
FROMdbo.MyDBs
GO
CREATE UNIQUE CLUSTERED INDEX UCI_MyIndexedView ON MyIndexedView (DBName)
GO
CREATE VIEW MyIndexedView_Control
WITH SCHEMABINDING
AS
SELECTDBName
FROMdbo.MyDBs
GO
CREATE UNIQUE CLUSTERED INDEX UCI_MyIndexedView_Control ON MyIndexedView_Control (DBName)
GO
CREATE TRIGGER myDDLTrigger ON DATABASE
FOR ALTER_VIEW
AS
BEGIN
DECLARE @ViewNameVarchar(128),
@ErrMsgVarchar(8000),
@EvntXMLXML,
@ViewIDInt,
@sysIndexesBit,
@Sys_IndexesBit,
@ObjPropertyBit,
@spHelpIndexBit
DECLARE@HelpIndexSPTABLE
(
index_nameSysName,
index_descriptionNVarchar(500),
Index_keysNVarchar(500)
)
-- Test view
SELECT@EvntXML= EVENTDATA()
SELECT@ViewName= @EvntXML.value('(/EVENT_INSTANCE/ObjectName)[1]','varchar(128)')
SELECT@ViewID= OBJECT_ID(@ViewName)
SELECT@sysIndexes = 1 FROM sys.sysIndexes WHERE id = @ViewID
SELECT@Sys_Indexes = 1 FROM sys.Indexes WHERE [object_id] = @ViewID
SELECT@ObjProperty = OBJECTPROPERTY(@ViewID, 'IsIndexed')
INSERT@HelpIndexSP EXEC sp_HelpIndex @ViewName
IF @@ROWCOUNT > 0 SET @spHelpIndex = 1
SELECT@ErrMsg ='View : ' + ISNULL(@ViewName, 'NULL') + CHAR(13) +
'sys.sysIndexes : ' + CASE @sysIndexes WHEN 1 THEN 'Found' ELSE 'Not found' END + CHAR(13) +
'sys.Indexes : ' + CASE @Sys_Indexes WHEN 1 THEN 'Found' ELSE 'Not found' END + CHAR(13) +
'OBJECTPROPERTY : ' + CASE @ObjProperty WHEN 1 THEN 'Found' ELSE 'Not found' END + CHAR(13) +
'sp_Help_index : ' + CASE @spHelpIndex WHEN 1 THEN 'Found' ELSE 'Not found' END + CHAR(13) + CHAR(13)
-- Control view
SELECT@ViewName= @ViewName + '_Control'
SELECT@ViewID= OBJECT_ID(@ViewName)
SELECT@sysIndexes = 1 FROM sys.sysIndexes WHERE id = @ViewID
SELECT@Sys_Indexes = 1 FROM sys.Indexes WHERE [object_id] = @ViewID
SELECT@ObjProperty = OBJECTPROPERTY(@ViewID, 'IsIndexed')
INSERT@HelpIndexSP EXEC sp_HelpIndex @ViewName
IF @@ROWCOUNT > 0 SET @spHelpIndex = 1
SELECT@ErrMsg =@ErrMsg +
'View : ' + ISNULL(@ViewName, 'NULL') + CHAR(13) +
'sys.sysIndexes : ' + CASE @sysIndexes WHEN 1 THEN 'Found' ELSE 'Not found' END + CHAR(13) +
'sys.Indexes : ' + CASE @Sys_Indexes WHEN 1 THEN 'Found' ELSE 'Not found' END + CHAR(13) +
'OBJECTPROPERTY : ' + CASE @ObjProperty WHEN 1 THEN 'Found' ELSE 'Not found' END + CHAR(13) +
'sp_Help_index : ' + CASE @spHelpIndex WHEN 1 THEN 'Found' ELSE 'Not found' END + CHAR(13)
RAISERROR(@ErrMsg, 1, 1)
ROLLBACK
END
GO
-- Check the indexes
SELECTViewName= OBJECT_NAME([object_id]),
IndexName= name
FROMsys.indexes
WHEREOBJECT_NAME([object_id]) LIKE 'MyIndexedView%'
GO
-- Try change the view, dropping and rollingback the drop
ALTER VIEW MyIndexedView
WITH SCHEMABINDING
AS
SELECTDBName
FROMdbo.MyDBs
GO
-- Show both indexes are still there
SELECTViewName= OBJECT_NAME([object_id]),
IndexName= name
FROMsys.indexes
WHEREOBJECT_NAME([object_id]) LIKE 'MyIndexedView%'
GO
-- Extra proof the trigger code is good.
DECLARE @ViewNameVarchar(128),
@ErrMsgVarchar(8000),
@ViewIDInt,
@sysIndexesBit,
@Sys_IndexesBit,
@ObjPropertyBit,
@spHelpIndexBit
DECLARE@HelpIndexSPTABLE
(
index_nameSysName,
index_descriptionNVarchar(500),
Index_keysNVarchar(500)
)
SELECT@ViewName= 'MyIndexedView'
SELECT@ViewID= OBJECT_ID(@ViewName)
SELECT@sysIndexes = 1 FROM sys.sysIndexes WHERE id = @ViewID
SELECT@Sys_Indexes = 1 FROM sys.Indexes WHERE [object_id] = @ViewID
SELECT@ObjProperty = OBJECTPROPERTY(@ViewID, 'IsIndexed')
INSERT@HelpIndexSP EXEC sp_HelpIndex @ViewName
IF @@ROWCOUNT > 0 SET @spHelpIndex = 1
PRINT'View : ' + ISNULL(@ViewName, 'NULL') + CHAR(13) +
'sys.sysIndexes : ' + CASE @sysIndexes WHEN 1 THEN 'Found' ELSE 'Not found' END + CHAR(13) +
'sys.Indexes : ' + CASE @Sys_Indexes WHEN 1 THEN 'Found' ELSE 'Not found' END + CHAR(13) +
'OBJECTPROPERTY : ' + CASE @ObjProperty WHEN 1 THEN 'Found' ELSE 'Not found' END + CHAR(13) +
'sp_Help_index : ' + CASE @spHelpIndex WHEN 1 THEN 'Found' ELSE 'Not found' END + CHAR(13) + CHAR(13)
GO
----------------------------------------------------------------------------------------------------------------------------------
-- Post-clean
----------------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID('MyIndexedView') IS NOT NULL
DROP VIEW MyIndexedView
IF OBJECT_ID('MyIndexedView_Control') IS NOT NULL
DROP VIEW MyIndexedView_Control
IF OBJECT_ID('MyDBs') IS NOT NULL
DROP TABLE MyDBs
IF EXISTS (SELECT 1 FROM sys.triggers WHERE name = 'myDDLTrigger')
DROP TRIGGER myDDLTrigger ON DATABASE
GO
Cheers
August 22, 2014 at 3:25 am
Resolved.
Daily job saves all indexed views into a table.
DDL trigger checks if the view to be alter is in the IndexedView table.
If so, raise error & rollback.
To alter the view, the view must be deleted from the IndexedView table.
August 22, 2014 at 1:19 pm
DennisPost (8/22/2014)
Resolved.Daily job saves all indexed views into a table.
DDL trigger checks if the view to be alter is in the IndexedView table.
If so, raise error & rollback.
To alter the view, the view must be deleted from the IndexedView table.
Good solution. I read this thread yesterday, but couldn't come up with any ideas so didn't commetn.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 22, 2014 at 3:03 pm
Jack Corbett (8/22/2014)
DennisPost (8/22/2014)
Resolved.Daily job saves all indexed views into a table.
DDL trigger checks if the view to be alter is in the IndexedView table.
If so, raise error & rollback.
To alter the view, the view must be deleted from the IndexedView table.
Good solution. I read this thread yesterday, but couldn't come up with any ideas so didn't commetn.
BWAAA-HAAA!!!! "It's not a fault... it's a FEATURE" that forces you to rebuild the indexes anytime you make a change. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply