MSSQL: Create an Audit Structure in SQL Server

I was looking at creating an audit trail for data changes in a SQL Server database. There are many mechanisms for doing this and I decided on having a duplicate table to store the audit trail in. So for example:

Base Table: User.Person
    PersonID int
    Name varchar(100)

Audit Table: Audit.Person
    AuditID int IDENTITY(1,1)
    Action char(1)
    Date datetime
    PersonID int
    Name varchar(100)

So the idea is that for each action that occurs (INSERT, UPDATE, DELETE), the data as it was prior to the action is stored into the associated audit table. Obviously in the case of an INSERT the new ID is stored as there was no data prior to the action.

Each base table has a matching TRIGGER on it. This trigger looks like this:

CREATE TRIGGER [User].[TRG_Person_AuditHandle]
   ON  [User].[Person]
   AFTER INSERT, DELETE, UPDATE
AS 
BEGIN
	IF (SELECT COUNT(1) FROM deleted) > 0 AND (SELECT COUNT(1) FROM inserted) > 0
		INSERT INTO [Audit].[Person] ([Action], [PersonID], [Name])
		SELECT 'U', [PersonID], [Name] FROM deleted
	ELSE IF (SELECT COUNT(1) FROM inserted) > 0
		INSERT INTO [Audit].[Person] ([Action], [PersonID])
		SELECT 'I', [PersonID] FROM inserted
	ELSE IF (SELECT COUNT(1) FROM deleted) > 0
		INSERT INTO [Audit].[Person] ([Action], [PersonID], [Name])
		SELECT 'D', [PersonID], [Name] FROM deleted
END

This now gives a full audit trail to the Person table.

Here's a wicked script to create all the audit tables and the triggers for the base tables:

/*
	Creates the audit data structure for all requried tables
	+ Creates the audit table to hold the changes in
	+ Creates the trigger on the base table to add to the audit table
*/
DECLARE @SQL_DROP_AUDIT_TABLE varchar(MAX),
		@SQL_CREATE_AUDIT_TABLE varchar(MAX),
		@SQL_DROP_AUDIT_TRIGGER varchar(MAX),
		@SQL_CREATE_AUDIT_TRIGGER varchar(MAX)

SET @SQL_DROP_AUDIT_TABLE =
'IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''[DF_#TABLE_NAME#_Date]'') AND type = ''D'')
	ALTER TABLE [Audit].[#TABLE_NAME#] DROP CONSTRAINT [DF_#TABLE_NAME#_Date]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[Audit].[#TABLE_NAME#]'') AND type in (N''U''))
	DROP TABLE [Audit].[#TABLE_NAME#]
GO'

SET @SQL_CREATE_AUDIT_TABLE = 
'CREATE TABLE [Audit].[#TABLE_NAME#](
	[AuditID] int IDENTITY(1,1) NOT NULL,
	[Action] char(1) NOT NULL,
	[Date] datetime NOT NULL,
	#COLUMNS#
 CONSTRAINT [PK_Audit_#TABLE_NAME#] PRIMARY KEY CLUSTERED 
(
	[AuditID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [Audit].[#TABLE_NAME#] ADD  CONSTRAINT [DF_Audit_#TABLE_NAME#_Date]  DEFAULT (getdate()) FOR [Date]
GO'

SET @SQL_DROP_AUDIT_TRIGGER = 
'IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[#SCHEMA_NAME#].[TRG_#TABLE_NAME#_AuditHandle]''))
	DROP TRIGGER [#SCHEMA_NAME#].[TRG_#TABLE_NAME#_AuditHandle]
GO'

SET @SQL_CREATE_AUDIT_TRIGGER =
'CREATE TRIGGER [#SCHEMA_NAME#].[TRG_#TABLE_NAME#_AuditHandle]
   ON  [#SCHEMA_NAME#].[#TABLE_NAME#]
   AFTER INSERT, DELETE, UPDATE
AS 
BEGIN
	IF (SELECT COUNT(1) FROM deleted) > 0 AND (SELECT COUNT(1) FROM inserted) > 0
		INSERT INTO [Audit].[#TABLE_NAME#] ([Action],#COLUMNS#)
		SELECT ''U'',#COLUMNS# FROM deleted
	ELSE IF (SELECT COUNT(1) FROM inserted) > 0
		INSERT INTO [Audit].[#TABLE_NAME#] ([Action], [#ID_COLUMN#])
		SELECT ''I'', [#ID_COLUMN#] FROM inserted
	ELSE IF (SELECT COUNT(1) FROM deleted) > 0
		INSERT INTO [Audit].[#TABLE_NAME#] ([Action],#COLUMNS#)
		SELECT ''D'',#COLUMNS# FROM deleted
END
GO'

-- Get all tables
DECLARE @SQL varchar(MAX), @SQLAuditTrigger varchar(MAX)
DECLARE @Schema varchar(255), @Table varchar(255), @SchemaTable varchar(255)
DECLARE cTables CURSOR FAST_FORWARD READ_ONLY
FOR
	SELECT TABLE_SCHEMA, TABLE_NAME, '[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' AS [SCHEMA_TABLE]
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_SCHEMA NOT IN ('PUT_SCHEMA_NAME_FILTERS_HERE_IF_REQUIRED')
	AND TABLE_NAME NOT IN ('PUT_TABLE_NAME_FILTERS_HERE_IF_REQUIRED')
	ORDER BY TABLE_SCHEMA, TABLE_NAME
OPEN cTables

-- Loop each table
FETCH NEXT FROM cTables INTO @Schema, @Table, @SchemaTable
WHILE (@@FETCH_STATUS <> -1)
BEGIN
	IF (@@FETCH_STATUS <> -2)
	BEGIN
		-- Used to hold the column SQL that gets built for the CREATE statement
		DECLARE @ColumnSQL varchar(MAX) = '', @TriggerColumnSQL varchar(MAX) = ''
		
		-- Get all columns for a table
		DECLARE @Column varchar(255), @Position int, @DataType varchar(255), @DataLength float
		DECLARE cColumns CURSOR FAST_FORWARD READ_ONLY
		FOR
			SELECT COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
			FROM INFORMATION_SCHEMA.COLUMNS
			WHERE TABLE_SCHEMA = @Schema
			AND TABLE_NAME = @Table
			ORDER BY ORDINAL_POSITION
		OPEN cColumns
		
		-- Loop each column
		FETCH NEXT FROM cColumns INTO @Column, @Position, @DataType, @DataLength
		WHILE (@@FETCH_STATUS <> -1)
		BEGIN
			IF (@@FETCH_STATUS <> -2)
			BEGIN
				-- Set up if it's the first column of a table
				IF @Position = 1
				BEGIN
					-- Compile the drop statments and execute
					SET @SQL = REPLACE(@SQL_DROP_AUDIT_TABLE, N'#TABLE_NAME#', @Table)
					PRINT @SQL
					--EXEC(@SQL)
					SET @SQL = REPLACE(REPLACE(@SQL_DROP_AUDIT_TRIGGER, N'#TABLE_NAME#', @Table), N'#SCHEMA_NAME#', @Schema)
					PRINT @SQL
					--EXEC(@SQL)
					
					-- Prepare the create statement (no columns in it at this point
					SET @SQL = REPLACE(@SQL_CREATE_AUDIT_TABLE, N'#TABLE_NAME#', @Table)
					--PRINT @SQL
					SET @SQLAuditTrigger = REPLACE(REPLACE(REPLACE(@SQL_CREATE_AUDIT_TRIGGER, N'#TABLE_NAME#', @Table), N'#SCHEMA_NAME#', @Schema), N'#ID_COLUMN#', @Column)
					--PRINT @SQLAuditTrigger
				END
				
				-- Build in the columns
				SET @TriggerColumnSQL = @TriggerColumnSQL + ' [' + @Column + '],'
				SET @ColumnSQL = @ColumnSQL + '[' + @Column + '] '
				SET @ColumnSQL = @ColumnSQL + 
					CASE
						WHEN @DataType = 'varchar' OR @DataType = 'nvarchar' OR @DataType = 'char' OR @DataType = 'nchar' THEN
							CASE
								WHEN (@DataType = 'varchar' OR @DataType = 'nvarchar') AND @DataLength = -1 THEN
									@DataType + '(MAX)'
								ELSE
									@DataType + '(' + CAST(@DataLength AS varchar(MAX)) + ')'
							END
						ELSE
							@DataType
					END
				SET @ColumnSQL = @ColumnSQL + ' NULL, '
				
				-- If it's the last column then compile and execute the CREATE statements
				IF (SELECT COUNT(1) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @Schema AND TABLE_NAME = @Table) = @Position
				BEGIN
					SET @SQL = REPLACE(@SQL, N'#COLUMNS#', @ColumnSQL)
					PRINT @SQL
					--EXEC(@SQL)
					
					SET @SQL = REPLACE(@SQLAuditTrigger, N'#COLUMNS#', SUBSTRING(@TriggerColumnSQL, 1, LEN(@TriggerColumnSQL) - 1))
					PRINT @SQL
					--EXEC(@SQL)
				END
			END
		
			FETCH NEXT FROM cColumns INTO @Column, @Position, @DataType, @DataLength
		END
	END

	 -- Tidy columns
	CLOSE cColumns
	DEALLOCATE cColumns
	
	FETCH NEXT FROM cTables INTO @Schema, @Table, @SchemaTable
END

 -- Tidy tables
CLOSE cTables
DEALLOCATE cTables

You'll see I'm using schemas - obviously if you are not, then change it ;) It assumes that the first column in the table is the unique identifier to the row.

Insert Into a Table With Only One Identity Column

If you have a table that as only one column and that column is an identity column, then in order to INSERT into it, you need to use this statement:

INSERT INTO [MyTable] DEFAULT VALUES

This works in SQL Server 2008 and I haven't tested in earlier versions.

In SQL Server 2012, we have the introduction of a SEQUENCE. You could consider using one of those as an alternative.

SQLCE: Large SQL Statements

Been dabbling quite a lot recently with SQLCE on Windows mobile devices. I came across something that was a little bizarre with a very large statement that I was executing.

I was performing a DELETE with a WHERE clause using a NOT IN. The NOT IN had approximately 1200 IDs in it. Very cunningly, each time it executed, the device hung with no exceptions. Great!

I haven't been able to find any evidence of issues with executing large statements like this, so I resorted to adding the IDs to a table and then modifying the WHERE clause to use NOT IN (SELECT [MyID] FROM [MyTable]).

This took way too long to run! So I changed it to use a NOT EXISTS instead - what a difference, took less than 1 second rather than the 5 or 6 seconds when using NOT IN!

Original:
DELETE FROM [MyTable]
WHERE [MyID]  NOT IN (1, 2, 3, 4, ...1200)

Revised:
DELETE FROM [MyTable]
WHERE [MyID]  NOT EXISTS (SELECT * FROM [MyIDTable] WHERE [MyTable].[MyID] = [MyIDTable].[MyID])

How To Reset an Identity Column in MSSQL / SQL Server

Something I often find myself needing to do is reset the identity column on auto incrementing field in MS SQL / SQL Server.

There are a couple of ways of doing it:

1. If you are emptying the table, instead of using a DELETE, use a TRUNCATE. That will automatically reset the identity.
2. The ID can be set to a specific number by using DBCC CHECKIDENT([TABLE_NAME], RESEED, n). Where n is the number that is to be set.

If you already have data in the table and you want to make sure that the sequence is followed, then use this:

DBCC CHECKIDENT([TABLE_NAME], RESEED, 0)
DBCC CHECKIDENT([TABLE_NAME], RESEED)

Auto Trimming - Whitespace Removal in SQL Server/MSSQL

I got really angry about this one yesterday as I can't see one possible reason why this "feature" would be required.

If you run this script, the whitespace padded string is also returned along with the unpadded string.

CREATE TABLE #t ([col1] varchar(100))

INSERT INTO #t ([col1]) VALUES ('hello')
INSERT INTO #t ([col1]) VALUES ('hello     ')

SELECT '''' + [col1] + '''', LEN([col1]), DATALENGTH([col1])
FROM #t

SELECT * FROM #t WHERE [col1] = 'hello'
SELECT * FROM #t WHERE [col1] = 'hello     '

DROP TABLE #t

How can this possibly be right? If I want 'hello     ' to be returned, then return 'hello     ' not 'hello'. These are two very different strings!

Peoples arguments are why would want to save that and other such silly statements. My philosophy is, if I can think of it, it will happen! I can think of many cases where it would be beneficial to have the padding - dealing with old systems or external systems over which control is not possible.

Now I know that SQL Server does this stupid thing, I can account for it - but seriously - why?? It's stupid!

Oh and ANSI_PADDING - makes no odds!

MSSQL ISDATE - Is It?

Another is it type one from MSSQL, but this time ISDATE. SQLServerCentral.com article by Kameswari Aravindh very kindly makes us aware that ISDATE('1973') returns a true response. So long as the number is between 1753 and 9999, it's a valid year and therefore a valid date.

Bear it in mind!! ;)

MSSQL ISNUMERIC - Is it?

I didn't realise that ISNUMERIC('123 £') returns 1 - did you? If you didn't either then I'd suggest you read this article Why doesn't ISNUMERIC work correctly? by Jeff Moden on SQLServerCentral.com.

Taken from the article, the easiest test is to use a regular expression:

SELECT *
FROM mytable
WHERE mytable.mycolumn NOT LIKE '%[^0-9]%'

There is also a useful function he has written that could prove to be handy.

Thanks Jeff. Think I'd better re-trace some steps to make sure scripts using ISNUMERIC are a bit tighter!

MSSQL Reporting Services: Invalid Object Name on Temporary Table

I had an Invalid Object Name error when I was using a temporary table inside a stored procedure when I was reporting services. It ran fine in management studio but errored in the report designer wizard.

Fixed it by using the SET FMTONLY OFF command. Happy days :)

Monitor High CPU Usage in SQL Server

Been having a situation with a client of mine recently where the SQL Server has been thrashing the CPU and ultimately killing it. And what comes through this morning in the SQLServerCentral.com newsletter but an article Geoff Albin wrote on how to monitor high CPU usage in SQL Server.
Sweet :)

GRANT ALL Permissions on SQL Objects

Here's a quick little widget I created to GRANT ALL permissions on all SQL Server objects. It's crude and nasty, but does the trick. Quite easy to modify if need be...

DECLARE cObjects CURSOR READ_ONLY
FOR
SELECT name, xtype
FROM sysobjects
LEFT OUTER JOIN syspermissions ON sysobjects.id = syspermissions.id
WHERE xtype IN ('V', 'P', 'U', 'FN')
AND syspermissions.id IS NULL

DECLARE @ObjName varchar(255), @ObjType varchar(2)
DECLARE @SQL varchar(MAX)
DECLARE @User varchar(100)
SELECT @User = '[DOMAIN]\[USER]'

OPEN cObjects

FETCH NEXT FROM cObjects INTO @ObjName, @ObjType
WHILE (@@FETCH_STATUS <> -1)
BEGIN
 IF (@@FETCH_STATUS <> -2)
 BEGIN
  --Set the access permissions
  IF @ObjType = 'V' -- View
   SELECT @SQL = 'SELECT, INSERT, DELETE, UPDATE'
  ELSE IF @ObjType = 'P' -- Stored Procedure
   SELECT @SQL = 'EXEC'
  ELSE IF @ObjType = 'U' -- Table
   SELECT @SQL = 'SELECT, INSERT, DELETE, UPDATE'
  ELSE IF @ObjType = 'FN' -- User Defined Function
   SELECT @SQL = 'EXEC'

  --Build the rest of the statement and execute
  SELECT @SQL = 'GRANT ' + @SQL + ' ON dbo.[' + @ObjName + '] TO [' + @User + ']'
  EXEC(@SQL)
  PRINT @SQL
 END
 FETCH NEXT FROM cObjects INTO @ObjName, @ObjType
END

CLOSE cObjects
DEALLOCATE cObjects
GO

Multi-Script, Multi-DB Deployments

A useful, but quite obvious way of executing multiple scripts: http://www.sqlservercentral.com/articles/scripting/70783/.

I need to do this quite regularly - so will probably write a little widget to compile the scripts into a file. Hopefully save a little bit of time there as well :)