Thursday, July 22, 2010
Wednesday, July 14, 2010
DDL Triggers in SQL Server 2005
DDL Triggers in SQL Server 2005
We all are familiar with DML (Data Manipulation Language) triggers which can be invoked after any DML operations (used for update, insert and delete commands). We can achieve auditing functionality using DML triggers but this does not help us in auditing if someone has altered the definition of the tables or dropped the tables etc.. We can achieve this by using DDL (Data Definition Language) triggers introduced in SQL Server 2005.
As the name suggests, DDL triggers do not get fired for any DML operations but get fired for CREATE, ALTER, DROP, GRANT, DENY, REVOKE, and UPDATE STATISTICS DDL statements. Some of the system stored procedures which perform CREATE like operations, also fire the DDL trigger. One exception is sp_rename. Sp_rename procedure does not invoke DDL trigger even though it performs the operation of renaming the object. Most of the time, DDL triggers are used to prevent modification in the database or to audit the DDL changes in the database.
DDL triggers can be created at the database level for a specific database or at the server/instance level. For creating trigger at the database level, we have to use the ‘ON DATABASE’ clause and for creating trigger at the server/instance level we have to use the ‘ON ALL SERVER’ clause. When created at the server level, it keeps track of DDL changes for any database on that specific instance.
Let us create a table first.
CREATE TABLE dbo.AUDIT_EVENT
(
AUDIT_EVENT_ID INT IDENTITY(1,1),
EVENT_TEXT VARCHAR(MAX),
ACTION_LOGIN VARCHAR(100),
ACTION_DATE DATETIME,
CONSTRAINT PK_AUDIT_EVENT PRIMARY KEY(AUDIT_EVENT_ID)
)
GO
In the above table, we will record the information pertaining to any DDL operation. Let us create trigger to record the changes at database level.
CREATE TRIGGER AUDIT_TRIG
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE, CREATE_TABLE
AS
INSERT INTO dbo.Audit_Event(Event_Text, Action_Login, Action_date)
SELECT EVENTDATA().value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]‘,’nvarchar(max)’), original_login(), getDate()
GO
In the trigger shown above, we are using the EVENTDATA() function to obtain the actual text. We will cover more about EVENTDATA() function in future blog,. We have adopted the idea of using EVENTDATA() function from BOL example. Once trigger is in place, we can start testing it by creating, altering and dropping the table. Let us try to create table first. We will also alter it to add a new column and then we will drop it.
CREATE TABLE TEST(ID INT IDENTITY(1,1))
GO
ALTER TABLE TEST ADD TEST_DESC VARCHAR(30)
GO
DROP TABLE TEST
GO
For all of the above statements, once the statement is executed successfully, trigger will get fired. Let us check our audit table now.
SELECT Event_Text,Action_Login, Action_Date FROM AUDIT_EVENT
GO
Here is the result set:
Event_Text Action_login Action_Date
------------------------------------------- ------------- -----------------------
CREATE TABLE TEST(ID INT IDENTITY(1,1)) sa 2007-09-26 13:43:57.177
ALTER TABLE TEST ADD TEST_DESC VARCHAR(30) sa 2007-09-26 13:43:57.223
DROP TABLE TEST sa 2007-09-26 13:43:57.223
From the above result set, we know what command got executed, at what time and by whom. This prevents unwanted surprises during the development phase and in addition, we can keep track of database changes made within the application. Similar to the example shown above, we can also create trigger for CREATE_PROCEDURE, DROP_PROCEDURE event types to audit the procedure changes.
sponsor links
http://decipherinfosys.wordpress.com/2007/09/27/ddl-triggers-in-sql-server-2005/
We all are familiar with DML (Data Manipulation Language) triggers which can be invoked after any DML operations (used for update, insert and delete commands). We can achieve auditing functionality using DML triggers but this does not help us in auditing if someone has altered the definition of the tables or dropped the tables etc.. We can achieve this by using DDL (Data Definition Language) triggers introduced in SQL Server 2005.
As the name suggests, DDL triggers do not get fired for any DML operations but get fired for CREATE, ALTER, DROP, GRANT, DENY, REVOKE, and UPDATE STATISTICS DDL statements. Some of the system stored procedures which perform CREATE like operations, also fire the DDL trigger. One exception is sp_rename. Sp_rename procedure does not invoke DDL trigger even though it performs the operation of renaming the object. Most of the time, DDL triggers are used to prevent modification in the database or to audit the DDL changes in the database.
DDL triggers can be created at the database level for a specific database or at the server/instance level. For creating trigger at the database level, we have to use the ‘ON DATABASE’ clause and for creating trigger at the server/instance level we have to use the ‘ON ALL SERVER’ clause. When created at the server level, it keeps track of DDL changes for any database on that specific instance.
Let us create a table first.
CREATE TABLE dbo.AUDIT_EVENT
(
AUDIT_EVENT_ID INT IDENTITY(1,1),
EVENT_TEXT VARCHAR(MAX),
ACTION_LOGIN VARCHAR(100),
ACTION_DATE DATETIME,
CONSTRAINT PK_AUDIT_EVENT PRIMARY KEY(AUDIT_EVENT_ID)
)
GO
In the above table, we will record the information pertaining to any DDL operation. Let us create trigger to record the changes at database level.
CREATE TRIGGER AUDIT_TRIG
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE, CREATE_TABLE
AS
INSERT INTO dbo.Audit_Event(Event_Text, Action_Login, Action_date)
SELECT EVENTDATA().value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]‘,’nvarchar(max)’), original_login(), getDate()
GO
In the trigger shown above, we are using the EVENTDATA() function to obtain the actual text. We will cover more about EVENTDATA() function in future blog,. We have adopted the idea of using EVENTDATA() function from BOL example. Once trigger is in place, we can start testing it by creating, altering and dropping the table. Let us try to create table first. We will also alter it to add a new column and then we will drop it.
CREATE TABLE TEST(ID INT IDENTITY(1,1))
GO
ALTER TABLE TEST ADD TEST_DESC VARCHAR(30)
GO
DROP TABLE TEST
GO
For all of the above statements, once the statement is executed successfully, trigger will get fired. Let us check our audit table now.
SELECT Event_Text,Action_Login, Action_Date FROM AUDIT_EVENT
GO
Here is the result set:
Event_Text Action_login Action_Date
------------------------------------------- ------------- -----------------------
CREATE TABLE TEST(ID INT IDENTITY(1,1)) sa 2007-09-26 13:43:57.177
ALTER TABLE TEST ADD TEST_DESC VARCHAR(30) sa 2007-09-26 13:43:57.223
DROP TABLE TEST sa 2007-09-26 13:43:57.223
From the above result set, we know what command got executed, at what time and by whom. This prevents unwanted surprises during the development phase and in addition, we can keep track of database changes made within the application. Similar to the example shown above, we can also create trigger for CREATE_PROCEDURE, DROP_PROCEDURE event types to audit the procedure changes.
sponsor links
http://decipherinfosys.wordpress.com/2007/09/27/ddl-triggers-in-sql-server-2005/
Friday, June 18, 2010
HELP LINKS
SQL Server backup utility using VB.Net and SQL-DMO
http://www.c-sharpcorner.com/UploadFile/shabdarghata/sql-server-backup-utility-sql-dmo02042008134007PM/sql-server-backup-utility-sql-dmo.aspx
http://www.c-sharpcorner.com/uploadfile/shivprasadk/1420/default.aspx?login=true&user=ravuripradeep
http://www.c-sharpcorner.com/UploadFile/shabdarghata/sql-server-backup-utility-sql-dmo02042008134007PM/sql-server-backup-utility-sql-dmo.aspx
http://www.c-sharpcorner.com/uploadfile/shivprasadk/1420/default.aspx?login=true&user=ravuripradeep
Search and Replace SQL Server data in all columns, of all tables
CREATE PROC SearchAndReplace
(
@SearchStr nvarchar(100),
@ReplaceStr nvarchar(100)
)
AS
BEGIN
-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string and replace it with another string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 2nd November 2002 13:50 GMT
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
SET @RCTR = 0
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
SET @SQL= 'UPDATE ' + @TableName +
' SET ' + @ColumnName
+ ' = REPLACE(' + @ColumnName + ', '
+ QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') +
') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
EXEC (@SQL)
SET @RCTR = @RCTR + @@ROWCOUNT
END
END
END
SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS 'Outcome'
END
http://vyaskn.tripod.com/sql_server_search_and_replace.htm
(
@SearchStr nvarchar(100),
@ReplaceStr nvarchar(100)
)
AS
BEGIN
-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string and replace it with another string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 2nd November 2002 13:50 GMT
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
SET @RCTR = 0
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
SET @SQL= 'UPDATE ' + @TableName +
' SET ' + @ColumnName
+ ' = REPLACE(' + @ColumnName + ', '
+ QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') +
') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
EXEC (@SQL)
SET @RCTR = @RCTR + @@ROWCOUNT
END
END
END
SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS 'Outcome'
END
http://vyaskn.tripod.com/sql_server_search_and_replace.htm
Thursday, June 17, 2010
Frequently Asked Questions - SQL Server Data Types
1. What's the difference between CHAR and VARCHAR data types and when do I use them?
CHAR and VARCHAR data types are both non-Unicode character data types with a maximum length of 8,000 characters. The main difference between these 2 data types is that a CHAR data type is fixed-length while a VARCHAR is variable-length. If the number of characters entered in a CHAR data type column is less than the declared column length, spaces are appended to it to fill up the whole length.
Another difference is in the storage size wherein the storage size for CHAR is n bytes while for VARCHAR is the actual length in bytes of the data entered (and not n bytes).
You should use CHAR data type when the data values in a column are expected to be consistently close to the same size. On the other hand, you should use VARCHAR when the data values in a column are expected to vary considerably in size.
2. What's the difference between NCHAR and NVARCHAR data types and when do I use them?
NCHAR and NVARCHAR data types are both Unicode character data types with a maximum length of 4,000 characters. The main difference between these 2 data types is that an NCHAR data type is fixed-length while an NVARCHAR is variable-length. If the number of characters entered in an NCHAR data type column is less than the specified column length, spaces are appended to it to fill up the whole length.
Another difference is in the storage size wherein the storage size for NCHAR is two times n bytes while for NVARCHAR is two times the number of characters entered (in bytes).
You should use NCHAR data type when the data values in a column are expected to be consistently close to the same size. On the other hand, you should use NVARCHAR when the data values in a column are expected to vary considerably in size.
3. What's the difference between CHAR and NCHAR data types and when do I use them?
CHAR and NCHAR data types are both character data types that are fixed-length. Below is the summary of the differences between these 2 data types:
CHAR(n) NCHAR(n)
Character Data Type Non-Unicode Data Unicode Data
Maximum Length 8,000 4,000
Character Size 1 byte 2 bytes
Storage Size n bytes 2 times n bytes
You would use NCHAR data type for columns that store characters from more than one character set or when you will be using characters that require 2-byte characters, which are basically the Unicode characters such as the Japanese Kanji or Korean Hangul characters.
4. What's the difference between VARCHAR and NVARCHAR data types and when do I use them?
VARCHAR and NVARCHAR data types are both character data types that are variable-length. Below is the summary of the differences between these 2 data types:
VARCHAR(n) NVARCHAR(n)
Character Data Type Non-Unicode Data Unicode Data
Maximum Length 8,000 4,000
Character Size 1 byte 2 bytes
Storage Size Actual Length (in bytes) 2 times Actual Length (in bytes)
You would use NVARCHAR data type for columns that store characters from more than one character set or when you will be using characters that require 2-byte characters, which are basically the Unicode characters such as the Japanese Kanji or Korean Hangul characters.
5. What's the difference between TINYINT, SMALLINT, INT and BIGINT data types and when do I use them?
TINYINT, SMALLINT, INT and BIGINT are all the same in the sense that they are all exact number data types that use integer data. The difference between these data types are in the minimum and maximum values that each can contain as well as the storage size required by each data type, as shown in the following table:
Data Type Minimum Value Maximum Value Storage Size
tinyint 0 255 1 byte
smallint -2^15 (-32,768) 2^15 - 1 (32,767) 2 bytes
int -2^31 (-2,147,483,648) 2^31 - 1 (2,147,483,647) 4 bytes
bigint -2^63 (-9,223,372,036,854,775,808) 2^63 - 1 (9,223,372,036,854,775,807) 8 bytes
Choosing which of these data types to use depends on the value you want to store for the column or variable. The rule of thumb is to always use the data type that will require the least storage size. Don't always use INT as your data type for whole numbers if you don't need to. If you simply need to store a value between 0 and 255 then you should define your column as TINYINT.
CHAR and VARCHAR data types are both non-Unicode character data types with a maximum length of 8,000 characters. The main difference between these 2 data types is that a CHAR data type is fixed-length while a VARCHAR is variable-length. If the number of characters entered in a CHAR data type column is less than the declared column length, spaces are appended to it to fill up the whole length.
Another difference is in the storage size wherein the storage size for CHAR is n bytes while for VARCHAR is the actual length in bytes of the data entered (and not n bytes).
You should use CHAR data type when the data values in a column are expected to be consistently close to the same size. On the other hand, you should use VARCHAR when the data values in a column are expected to vary considerably in size.
2. What's the difference between NCHAR and NVARCHAR data types and when do I use them?
NCHAR and NVARCHAR data types are both Unicode character data types with a maximum length of 4,000 characters. The main difference between these 2 data types is that an NCHAR data type is fixed-length while an NVARCHAR is variable-length. If the number of characters entered in an NCHAR data type column is less than the specified column length, spaces are appended to it to fill up the whole length.
Another difference is in the storage size wherein the storage size for NCHAR is two times n bytes while for NVARCHAR is two times the number of characters entered (in bytes).
You should use NCHAR data type when the data values in a column are expected to be consistently close to the same size. On the other hand, you should use NVARCHAR when the data values in a column are expected to vary considerably in size.
3. What's the difference between CHAR and NCHAR data types and when do I use them?
CHAR and NCHAR data types are both character data types that are fixed-length. Below is the summary of the differences between these 2 data types:
CHAR(n) NCHAR(n)
Character Data Type Non-Unicode Data Unicode Data
Maximum Length 8,000 4,000
Character Size 1 byte 2 bytes
Storage Size n bytes 2 times n bytes
You would use NCHAR data type for columns that store characters from more than one character set or when you will be using characters that require 2-byte characters, which are basically the Unicode characters such as the Japanese Kanji or Korean Hangul characters.
4. What's the difference between VARCHAR and NVARCHAR data types and when do I use them?
VARCHAR and NVARCHAR data types are both character data types that are variable-length. Below is the summary of the differences between these 2 data types:
VARCHAR(n) NVARCHAR(n)
Character Data Type Non-Unicode Data Unicode Data
Maximum Length 8,000 4,000
Character Size 1 byte 2 bytes
Storage Size Actual Length (in bytes) 2 times Actual Length (in bytes)
You would use NVARCHAR data type for columns that store characters from more than one character set or when you will be using characters that require 2-byte characters, which are basically the Unicode characters such as the Japanese Kanji or Korean Hangul characters.
5. What's the difference between TINYINT, SMALLINT, INT and BIGINT data types and when do I use them?
TINYINT, SMALLINT, INT and BIGINT are all the same in the sense that they are all exact number data types that use integer data. The difference between these data types are in the minimum and maximum values that each can contain as well as the storage size required by each data type, as shown in the following table:
Data Type Minimum Value Maximum Value Storage Size
tinyint 0 255 1 byte
smallint -2^15 (-32,768) 2^15 - 1 (32,767) 2 bytes
int -2^31 (-2,147,483,648) 2^31 - 1 (2,147,483,647) 4 bytes
bigint -2^63 (-9,223,372,036,854,775,808) 2^63 - 1 (9,223,372,036,854,775,807) 8 bytes
Choosing which of these data types to use depends on the value you want to store for the column or variable. The rule of thumb is to always use the data type that will require the least storage size. Don't always use INT as your data type for whole numbers if you don't need to. If you simply need to store a value between 0 and 255 then you should define your column as TINYINT.
Subscribe to:
Comments (Atom)