SQL: Index über ein View? Schemabinding ist die Lösung – aber nicht immer…

Schemabindung ist sehr interessant, weil dann komplexe Views plötzlich schnell aufgerufen werden können. Aber hat auch Voraussetzungen und sogar Nachteile haben. Z.B. sind dann alle Tabellen, die auf den View zugreifen gesperrt , gegen struktureller Änderungen. Das bedeutet, wenn ein neues Feld in die Tabelle soll, muss der/die View/s gelöscht werden und dann wieder angelegt werden. Die alle Indizes, dieser betroffenen View/s müssen dann auch wieder angelegt werden.

Voraussetzungen sind recht komplex und sind in der Quelle reichlich beschrieben. Aber es wird keine Fehlermeldung geben, lediglich kann es sein, dass der „View with schemabinding“ lediglich die Daten nicht persistent speichert.

Aber damit man ganz schnell feststellen kann welche SET-Options im System eingerichtet sind ist in einer „neuen“ SSMS-Query hiermit leicht abfragen:

SELECT 
SESSIONPROPERTY ('NUMERIC_ROUNDABORT')  as "NUMERIC_ROUNDABORT soll 0",
SESSIONPROPERTY ('ANSI_PADDING') as "ANSI_PADDING soll 1", 
SESSIONPROPERTY ('ANSI_WARNINGS') as "ANSI_WARNINGS soll 1", 
SESSIONPROPERTY ('CONCAT_NULL_YIELDS_NULL') as "CONCAT_NULL_YIELDS_NULL soll 1",
SESSIONPROPERTY ('ARITHABORT') as "ARITHABORT soll 1",
SESSIONPROPERTY ('QUOTED_IDENTIFIER') as "QUOTED_IDENTIFIER soll 1" ,
SESSIONPROPERTY ('ANSI_NULLS') "ANSI_NULLS soll 1"

https://docs.microsoft.com/de-de/sql/relational-databases/views/create-indexed-views?view=sql-server-2017

MS SQL: SQL-Code von Views, Trigger Prozeduren und Funktionen darstellen

/*

Darstellen von SQL Code von Views. Trigger, Prozeduren und Funktionen

*/
Select s.name, m.definition 
from sys.sql_modules m inner join sys.views s ON s.object_id = m.object_id 
where 1=1


Select s.name, m.definition 
from sys.sql_modules m inner join sys.triggers s ON s.object_id = m.object_id 
where 1=1


Select s.name, m.definition 
from sys.sql_modules m inner join sys.procedures s ON s.object_id = m.object_id 
where 1=1
order by 1


Select s.name, m.definition 
from sys.sql_modules m inner join sys.objects s on s.object_id = m.object_id and s.Type='FN'
where 1=1
order by 1

Problem 1: Nicht immer sind die CRLF verblieben

Problem 2: Code ist möglw. länger als 4000/8000 Zeichen

Problem 3: Code ist möglw. verschlüsselt

Änderungen von Datenbanken dokumentieren per Trigger…

Aufwaendiges Logging:
http://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/

–Einfaches Logging:
http://msdn.microsoft.com/en-us/library/ms173781.aspx

–Bedeutung von „Event_Instance“
http://www.sqlteam.com/article/using-ddl-triggers-in-sql-server-2005-to-capture-schema-changes

Es muss eine Tabelle geben, in der die Änderungen gesammelt werden. Diese Tabelle wurde im u. g. Code in der Datenbank „master“ abgelegt.

/*
Jede strukturelle Änderungen an einer oder allen Datenbanken soll (völlig automatisch) nachvollziehbar sein.

Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)
Um alle technische Änderungen nachvollziehen zu können.
Also werden alle Veränderungen an Struktruen von allen Datenbanken dokumentiert.
Dies gilt auch für Trigger und neue Datenbanken u.ä.
Es werden somit sämtliche "DDL" Befehle dokumentiert, aber keine DML Befehle und deren Wirkungen!
DDL = Data Definition Language
DML = Data Manipulation Language

Möglicherweise sollte die getriggerte Änderungen 
an die Ziel-Datenbank (bsp. master.dbo.fod_ddl_log) 
eines zweitem SQL-Server gesendet werden.

Hinweis: sa-Rechte (oder vergleichbare) sind notwendig.
Hinweis: Dieser Trigger ist auf dem SQL-Server unter ServerObjects/Triggers zu finden

Change:
20160126 ODFR: Dieser Kopf und JEGLICHER "DELETE-Code" wurde auskommentiert + with execute as 'sa'


*/


USE master;
GO

--Logging-Tabelle anlegen:
CREATE TABLE fod_ddl_log (
	id bigint identity(1,1),
       xmlEVENTDATA xml
);
GO

--Logging-View anlegen (nur wg. dem Auslesen von XML):
Create View dbo.vfod_ddl_log
as
Select id, 
		xmlEVENTDATA.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime') as PostTime,
		xmlEVENTDATA.value('(/EVENT_INSTANCE/ServerName)[1]', 'varchar(256)') as ServerName,
		xmlEVENTDATA.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)') as DatabaseName, 
		xmlEVENTDATA.value('(/EVENT_INSTANCE/UserName)[1]', 'varchar(256)') as UserName, 
		xmlEVENTDATA.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') as EventType, 
		xmlEVENTDATA.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') as TSQLCommand,
		xmlEVENTDATA
from GC_ddl_log 

--TRIGGER  anlegen:
CREATE TRIGGER GC_ddl_log 
	/*ON DATABASE*/
	ON ALL SERVER
	WITH EXECUTE AS 'sa'
	FOR DDL_DATABASE_LEVEL_EVENTS 
	AS
	DECLARE @data XML;
	SET @data = EVENTDATA();
	INSERT GC_ddl_log  ( [xmlEVENTDATA] ) 
	VALUES ( @data ) ;
GO


--- DER CODE SOLL ERST NUR BIS HIER GESTARTET WERDEN!!!


/*
--Test the trigger:
--------------------
Begin Transaction
CREATE TABLE TestTable (a int)
Rollback

CREATE TABLE TestTable (a int)
GO
DROP TABLE TestTable ;
GO
SELECT * FROM master.dbo.vGC_ddl_log order by PostTime desc;
GO
*/



/*
--ALLES WIEDER ENTFERNEN, WENN ES NICHTGEWÜNSCHT IST:
-----------------------------------------------------
--Drop the trigger.
DROP TRIGGER master.dbo.fod_ddl_log
--ON DATABASE 
ON ALL SERVER
GO
--Drop View vfod_ddl_log:
use master
go
DROP VIEW dbo.vfod_ddl_log;
GO
--Drop table fod_ddl_log:
use master
go
DROP TABLE dbo.fod_ddl_log;
GO
*/


/*
--Aufwaendiges Logging:
http://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/

--Einfaches Logging:
http://msdn.microsoft.com/en-us/library/ms173781.aspx

--Bedeutung von  „Event_Instance“
http://www.sqlteam.com/article/using-ddl-triggers-in-sql-server-2005-to-capture-schema-changes
*/

Alle Views finden, in dem bestimmter Code vorkommt

/*
In welchem View kommt ein bestimmter Code vor?
Hinweis: in verschluesselten Views funktioniert dies nicht
Hinweis: Möglicherweise funktioniert der Code nur ab SQL Server 2005
Hinweis: optional ginge auch: exec sp_helptext 'schema.viewname'
*/

Select v.name, m.definition
from sys.sql_modules m
inner join sys.views v ON v.object_id = m.object_id
where 1=1
--and v.object_id = 2815072
and m.definition like '%sysdba.%'
and m.definition like '%dbo.%'
and m.definition like '%Contact%'
order by v.name, v.object_id