The Rambling DBA: Jonathan Kehayias : Transact-SQL, MSDN Forums

Using DDL Triggers to Dynamically Create DML Triggers

Jonathan Kehayias — Thu, 28 May 2009 10:24:06 GMT

This one is from the MSDN Forums on a post titled DDL trigger limitations. Basically the problem in a nutshell is that an application is harvesting information from the Internet and storing it in SQL Server on what appears to be a timed schedule. When it stores the information in SQL Server it checks if a tablename exists and performs a DROP/CREATE for the table before storing data into the table. The problem being posed was how to get the data into a "historical data" table so that existing data isn't lost when the table is being dropped by using a DDL Trigger. Complete details are available in the post.

A quick fire solution that I came up with while being narrow focused on the question being asked alone was to use a DDL trigger for CREATE_TABLE that executed a Dynamic SQL Statement to create a DML trigger FOR INSERT on the newly created table. The DML Trigger will then copy the rows from the inserted table into the "historical data" table for permanent storage. It's really not that complex, and it works to solve the immediate problem.

CREATE TABLE dbo.persistedtable      
(rowid INT, somecolumn VARCHAR(100), someothercolumn VARCHAR(100))      
GO      
      
CREATE TRIGGER new_table_add_trigger      
ON DATABASE      
FOR create_table      
AS      
      
SET nocount ON  
      
DECLARE @data xml      
SET @data = EVENTDATA()      
      
DECLARE @objname sysname      
SET @objname = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)')      
      
DECLARE @sql VARCHAR(MAX)      
SET @sql = 'create trigger ' + @objname + '_inserted      
on ' + @objname + '      
after insert       
as       
begin       
      
insert into persistedtable (rowid, somecolumn, someothercolumn)       
select rowid, somecolumn, someothercolumn       
from inserted       
      
end'       
      
EXECUTE (@sql)      
GO      
      
CREATE TABLE testtable1      
(rowid INT IDENTITY PRIMARY KEY, somecolumn VARCHAR(100), someothercolumn VARCHAR(100))      
GO      
      
INSERT INTO testtable1 VALUES ('test1', 'test1')      
GO      
      
DROP TABLE testtable1      
GO       
      
CREATE TABLE testtable1      
(rowid INT IDENTITY PRIMARY KEY, somecolumn VARCHAR(100), someothercolumn VARCHAR(100))      
      
INSERT INTO testtable1 VALUES ('test2', 'test2')      
GO      
      
SELECT * FROM persistedtable

However, what I didn't realize when I wrote my response was that an additional response from the original poster was added with some further details that changed my perception of how best to solve the problem. Essentially there are three major problems with the above solution, though it works.

Triggers are expensive, and are often the last thing you think about when troubleshooting performance problems and irregular activity in your database.
The insert transactions are going to be slower because you are doing a double insert for each record, so expect to take a performance hit.
The Dynamic SQL code being used to create the FOR INSERT trigger on the table is safe only because it is using controlled data in the way it concatenates the object_name into the SQL command to be executed by the EXECUTE command. Using Dynamic SQL in this manner with unchecked inputs because SQL Injection can occur which is very dangerous.

Since the process is running on a schedule, I would instead opt for a slightly more complex, but more also more robust solution if I were actually implementing a change of this nature and couldn't change the application behavior itself. First I would create a table that had a single column for the tablename being created by the application, and then change the DDL Trigger so that all it did was insert the tablename being created into this table. Then a small stored procedure can be written to read the names from this table if any exist and copy the data into the "historical data" table.

CREATE TABLE ddl_changed_tables      
(tablename sysname)      
GO      
      
CREATE TRIGGER new_table_add      
ON DATABASE      
FOR create_table      
AS      
      
SET nocount ON  
      
DECLARE @data xml      
SET @data = EVENTDATA()      
      
INSERT INTO ddl_changed_tables (tablename)      
VALUES(@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'))      
      
GO      
      
CREATE PROCEDURE migrate_new_data      
AS      
BEGIN       
      
DECLARE @sql VARCHAR(MAX)      
DECLARE @tablename sysname      
SELECT @tablename = MIN(tablename)      
FROM ddl_changed_tables      
      
WHILE @tablename IS NOT NULL      
BEGIN      
      
SET @sql = 'insert into persistedtable (rowid, somecolumn, someothercolumn)      
select rowid, somecolumn, someothercolumn       
from ' + @tablename      
      
EXECUTE(@sql)      
      
DELETE ddl_changed_tables      
WHERE tablename = @tablename      
      
SELECT @tablename = MIN(tablename)      
FROM ddl_changed_tables      
      
END      
      
END       
GO

The benefit here is that the copy process doesn't happen during the initial insert, so the performance of the initial process is not impacted. The downside is that you could catch a table in a half load state depending on the scheduling of the process actually running. If I knew more about the problem, this could probably be overcome.

In the end, either of these solutions can be used to solve the problem, but I prefer to avoid the use of DML triggers in design if another process can be developed to accomplish the same task.

SQL Tip: Keep your Presentation Logic in your Presentation Layer

Jonathan Kehayias — Fri, 20 Feb 2009 03:13:00 GMT

Whether you are just starting out in SQL Server, or you have been working in SQL Server for many years, eventually in your work, you cross a boundary where someone will want data formatted in a different manner than it is stored in the database server. Take for example DateTime data. It is not uncommon to see questions on the forums where someone asks how to return data from a DateTime column in a custom format, or to return the difference between two DateTime columns as hours, minutes and seconds. Now it is entirely possible to do this kind of logic inside of SQL Server using TSQL, I'll admit that. I'll also admit to having written some of the most convoluted TSQL there could ever possibly be to solve problems simply because it was all I knew.

I've used this analogy before, and I'll use it again. If you have to build a house of wood and screws and all that you have is a hammer, it is completely possible to drive a screw through a board using a hammer, and you could most assuredly build your house. However, if you were to take a little time and drive to the hardware store and either buy some nails, or a screwdriver, you are probably going to be able to first build the house faster, it will be much more stable, and it will last much longer. The same thing goes with software development. I used to write logic to do everything you can imagine using TSQL, and I to be reusable, I did much of it in functions and or stored procedures using output parameters and created more than my share of badly performing and overly complex TSQL code that did very little actual work.

Back on topic with this post, I find that a lot of people starting out in development work just like I did, writing TSQL and they stick with TSQL to everything in the world. Take the original example, the difference between two dates in a custom format like DD:HH:MI:SS for displaying the result. You can do this in TSQL, and putting the code in a function makes it really easy to use anywhere you want to:

CREATE FUNCTION dbo.testSQL(@first datetime, @second datetime) RETURNS VARCHAR(12) AS BEGIN DECLARE @difference datetime SET @difference=@second-@first DECLARE @retval VARCHAR(12) SELECT @retval = CAST(DATEDIFF(dd, '1900/01/01', @difference) AS VARCHAR)+':'+RIGHT(CONVERT(VARCHAR, @difference, 120),8) RETURN(@retval) END GO

Then again, you can do it in the presentation layer for example C#:

private static string CSharpDateDiff(DateTime first, DateTime second) { TimeSpan diff = second.Subtract(first).Duration(); return string.Format("{0}:{1}:{2}:{3}", diff.Days, diff.Hours, diff.Minutes, diff.Seconds); }

or VB.NET:

Private Shared Function CSharpDateDiff(ByVal first As DateTime, ByVal second As DateTime) As String Dim diff As TimeSpan = second.Subtract(first).Duration() Return String.Format("{0}:{1}:{2}:{3}", diff.Days, diff.Hours, diff.Minutes, diff.Seconds) End Function

Now to me, both of these are much easier to read than the TSQL code, and they are just as reusable in your application when placed in an appropriate library or class that can be imported or used by your code, but what about performance? I'm so glad you asked. Using the following 100000 row test table:

USE tempdb GO CREATE TABLE temptimes ( rowid INT IDENTITY PRIMARY KEY, date1 datetime, date2 datetime ) GO INSERT INTO temptimes SELECT GETDATE(), DATEADD(ss, RAND()*10000, DATEADD(mi, RAND()*10000, GETDATE())) GO 100000
and the following C# console app:
using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlClient; namespace ConsoleApplication3 { class Program { public struct RowData { public int RowID; public string DiffVal; public RowData(int rowid, string diff) { RowID = rowid; DiffVal = diff; } } static void Main(string[] args) { DateTime startTSQL; DateTime finishTSQL; DateTime startCLR; DateTime finishCLR; List<RowData> clrdata = RunCLRTest(); List<RowData> sqldata = RunSQLTest(); startCLR = DateTime.Now; clrdata = RunCLRTest(); finishCLR = DateTime.Now; startTSQL = DateTime.Now; sqldata = RunSQLTest(); finishTSQL = DateTime.Now; TimeSpan sql = finishTSQL.Subtract(startTSQL); TimeSpan clr = finishCLR.Subtract(startCLR); Console.WriteLine(string.Format("SQL RunTime: {0} seconds, {1} milliseconds", sql.Seconds, sql.Milliseconds)); Console.WriteLine(string.Format("CLR RunTime: {0} seconds, {1} milliseconds", clr.Seconds, clr.Milliseconds)); Console.WriteLine("Press Any Key to Exit!"); Console.Read(); } private static List<RowData> RunSQLTest() { List<RowData> ret = new List<RowData>(); SqlConnection conn = new SqlConnection("Server=.;Database=tempdb;Trusted_Connection=True;"); conn.Open(); SqlCommand cmd = new SqlCommand("select rowid, dbo.testSql(date1, date2) from temptimes", conn); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { ret.Add(new RowData(dr.GetInt32(0), dr.GetString(1))); } conn.Close(); return ret; } private static List<RowData> RunCLRTest() { List<RowData> ret = new List<RowData>(); SqlConnection conn = new SqlConnection("Server=.;Database=tempdb;Trusted_Connection=True;"); conn.Open(); SqlCommand cmd = new SqlCommand("select rowid, date1, date2 from temptimes", conn); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { ret.Add(new RowData(dr.GetInt32(0), CSharpDateDiff(dr.GetDateTime(1), dr.GetDateTime(2)))); } conn.Close(); return ret; } private static string CSharpDateDiff(DateTime first, DateTime second) { TimeSpan diff = second.Subtract(first).Duration(); return string.Format("{0}:{1}:{2}:{3}", diff.Days, diff.Hours, diff.Minutes, diff.Seconds); } } }

you will find that the CLR parsing is faster consistently for this simplistic test.

add to that the output of a SqlTrace for the execution:

and you can see that the TSQL function drives more CPU and has a longer duration in both of the executions performed by the C# code above. Speaking of which, why did I run both loads twice in the code? It was done intentionally, to remove any cost of setting up the SqlConnection to the SQL Server initially, and to remove anyone's ability to point to cached data being beneficial to one call over the other. The numbers reported by the app are for the second runs which would have loaded the data into the Buffer Cache so both work from cache. The SqlTrace numbers hold true to the C# numbers as well.

So in the future hopefully this will help you make the decision to do presentation work in the presentation layer, and leave the database server to what it does the best, CRUD (create, retrieve, update, delete) operations on data.

EDIT AFTER THE FACT:

After reading this post, Alexander Kuznetsov wrote a followup post called

Should I Always Keep My Presentation Logic in My Presentation Layer?

where he covers reasons for placing Presentation Login inside SQL Server. After reading this post, please jump over to his and see the other side of the story. Many thanks to Alexander for covering those in his post.

Drop All Indexes and Stats in one Script

Jonathan Kehayias — Mon, 15 Dec 2008 12:44:12 GMT

I am not sure why someone would want to do this, but it was asked on the forums, and I figured I would post the code I created to perform such a nightmarish operation. As with any post that I make providing code that could be potentially damaging and dangerous, if you use it, you do so at your own risk. Don't send me emails complaining that you got fired for deleting all the indexes with the scripts on this post. I am not going to be able to help you fix it, and my recommendation is going to be restore a backup, and start scripting them all off if you still have a job.

SQL Server 2000 Code

DECLARE @ownername SYSNAME DECLARE @tablename SYSNAME DECLARE @indexname SYSNAME DECLARE @sql NVARCHAR(4000) DECLARE dropindexes CURSOR FOR SELECT sysindexes.name, sysobjects.name, sysusers.name FROM sysindexes JOIN sysobjects ON sysindexes.id = sysobjects.id JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE indid > 0 AND indid < 255 AND INDEXPROPERTY(sysobjects.id, sysindexes.name, 'IsStatistics') = 0 AND sysobjects.TYPE = N'U' AND NOT EXISTS (SELECT 1 FROM sysobjects WHERE sysobjects.name = sysindexes.name) ORDER BY sysindexes.id, indid DESC OPEN dropindexes FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername WHILE @@fetch_status = 0 BEGIN SET @sql = N'DROP INDEX '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@indexname) PRINT @sql EXEC sp_executesql @sql FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername END CLOSE dropindexes DEALLOCATE dropindexes GO DECLARE @ownername SYSNAME DECLARE @tablename SYSNAME DECLARE @statsname SYSNAME DECLARE @sql NVARCHAR(4000) DECLARE dropstats CURSOR FOR SELECT sysindexes.name, sysobjects.name, sysusers.name FROM sysindexes JOIN sysobjects ON sysindexes.id = sysobjects.id JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE indid > 0 AND indid < 255 AND INDEXPROPERTY(sysobjects.id, sysindexes.name, 'IsStatistics') = 1 AND sysobjects.TYPE = N'U'; OPEN dropstats FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername WHILE @@fetch_status = 0 BEGIN SET @sql = N'DROP STATISTICS '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@statsname) EXEC sp_executesql @sql --PRINT @sql FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername END CLOSE dropstats DEALLOCATE dropstats

The above script will work for SQL 2005 and 2008 also, but only because compatibility views have been carried forward in code by Microsoft. The correct code for doing this in SQL 2005 and 2008 is as follows:

SQL Server 2005/2008

DECLARE @ownername SYSNAME DECLARE @tablename SYSNAME DECLARE @indexname SYSNAME DECLARE @sql NVARCHAR(4000) DECLARE dropindexes CURSOR FOR SELECT indexes.name, objects.name, schemas.name FROM sys.indexes JOIN sys.objects ON indexes.OBJECT_ID = objects.OBJECT_ID JOIN sys.schemas ON objects.schema_id = schemas.schema_id WHERE indexes.index_id > 0 AND indexes.index_id < 255 AND objects.is_ms_shipped = 0 AND NOT EXISTS (SELECT 1 FROM sys.objects WHERE objects.name = indexes.name) ORDER BY objects.OBJECT_ID, indexes.index_id DESC SELECT * FROM sys.stats OPEN dropindexes FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername WHILE @@fetch_status = 0 BEGIN SET @sql = N'DROP INDEX '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@indexname) PRINT @sql EXEC sp_executesql @sql FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername END CLOSE dropindexes DEALLOCATE dropindexes GO DECLARE @ownername SYSNAME DECLARE @tablename SYSNAME DECLARE @statsname SYSNAME DECLARE @sql NVARCHAR(4000) DECLARE dropstats CURSOR FOR SELECT stats.name, objects.name, schemas.name FROM sys.stats JOIN sys.objects ON stats.OBJECT_ID = objects.OBJECT_ID JOIN sys.schemas ON objects.schema_id = schemas.schema_id WHERE stats.stats_id > 0 AND stats.stats_id < 255 AND objects.is_ms_shipped = 0 ORDER BY objects.OBJECT_ID, stats.stats_id DESC OPEN dropstats FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername WHILE @@fetch_status = 0 BEGIN SET @sql = N'DROP STATISTICS '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@statsname) EXEC sp_executesql @sql --PRINT @sql FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername END CLOSE dropstats DEALLOCATE dropstats

Hope it helps someone out.