詳解SQL Server中DDL觸發(fā)器和索引視圖
索引視圖:
一般視圖都虛表,即視圖本身不存儲數(shù)據(jù),而且是一個查詢,當(dāng)訪問視圖時,SQL SERVER會自動根據(jù)視圖的定義來訪問基表數(shù)據(jù)。具有***的聚集索引的視圖,
索引視圖本身會存儲數(shù)據(jù),可以加快查詢速度,但會增加數(shù)據(jù)修改的開銷。所以索引視圖適用的修改少而查詢多的表。創(chuàng)建索引視圖時,索引視圖的***個索引
必須是CLUSTERED和UNIQUE。
索引視圖的創(chuàng)建:
- CREATE TABLE dbo.t1
- (
- USERID VARCHAR(50),
- USERNAME VARCHAR(256)
- );
- go
- CREATE TABLE dbo.t2
- (
- USERID VARCHAR(50),
- DepartID VARCHAR(50)
- );
- GO
- CREATE TABLE dbo.t3
- (
- DepartID VARCHAR(50),
- DepartName VARCHAR(256)
- );
- GO
- CREATE VIEW dbo.USERINFO
- WITH SCHEMABINDING
- AS
- SELECT a.USERID, a.USERNAME, c.DEPARTID, c.DEPARTNAME
- FROM dbo.t1 a, dbo.t2 b, dbo.t3 c
- WHERE a.USERID = b.USERID
- AND b.DEPARTID = C.DEPARTID
- GO
- CREATE UNIQUE CLUSTERED INDEX IX_USERINFO_USERIDDEPARTID ON dbo.USERINFO(USERID, DEPARTID)
SQL Server 中的DDL觸發(fā)器
DDL觸發(fā)器可以在整數(shù)據(jù)庫范圍內(nèi)對對象的定義、修改、刪除而觸發(fā)執(zhí)行的觸發(fā)器??梢詳?shù)據(jù)庫級別對數(shù)據(jù)庫對象進(jìn)行控制和審記?;蛘叻?wù)器級別的觸發(fā)器,如用戶登錄的審記。
DDL觸發(fā)器事件定義:
- <EVENT_INSTANCE>
- <EventType>type</EventType>
- <PostTime>date-time</PostTime>
- <SPID>spid</SPID>
- <ServerName>name</ServerName>
- <LoginName>name</LoginName>
- <UserName>name</UserName>
- <DatabaseName>name</DatabaseName>
- <SchemaName>name</SchemaName>
- <ObjectName>name</ObjectName>
- <ObjectType>type</ObjectType>
- <TSQLCommand>command</TSQLCommand>
- </EVENT_INSTANCE>
DDL觸發(fā)器的創(chuàng)建:
- CREATE TABLE dbo.t4
- (
- USERNAME VARCHAR(256),
- TSQL VARCHAR(MAX),
- CDATE DATETIME
- );
- GO
- CREATE TRIGGER tr_dbDDL
- ON DATABASE
- FOR
- DROP_TABLE, ALTER_TABLE, CREATE_TABLE,
- CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
- AS
- DECLARE @xdata XML;
- SELECT @xdata = EVENTDATA();
- ROLLBACK;
- INSERT INTO dbo.t4(USERNAME, TSQL, CDATE)
- SELECT @xdata.value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(max)') AS dbUserName,
- @xdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)') AS T_SQL,
- GETDATE() AS CDATE;
- GO
- use master
- go
- CREATE TABLE dbo.t5
- (
- USERNAME VARCHAR(256),
- TSQL VARCHAR(MAX),
- CDATE DATETIME
- );
- GO
- ALTER TRIGGER tr_svrddl
- ON ALL SERVER
- FOR
- CREATE_DATABASE, ALTER_DATABASE, DROP_DATABASE,
- DDL_LOGIN_EVENTS
- AS
- DECLARE @xdata XML;
- SELECT @xdata = EVENTDATA();
- INSERT INTO dbo.t4(USERNAME, TSQL, CDATE)
- SELECT @xdata.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(max)') AS dbUserName,
- @xdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)') AS T_SQL,
- GETDATE() AS CDATE;
- GO
【編輯推薦】
- SQL Server使用索引實現(xiàn)數(shù)據(jù)訪問優(yōu)化
- SQL Server數(shù)據(jù)庫優(yōu)化經(jīng)驗總結(jié)
- 如何使用SQLServer數(shù)據(jù)庫查詢累計值
- 淺析Oracle和SqlServer存儲過程的調(diào)試、出錯處理
- 幾段SQLServer語句和存儲過程
- 50種方法優(yōu)化SQL Server數(shù)據(jù)庫查詢