January 2008 - Posts

• Create a Dynamic Logging Trigger With XML

  One of the more common uses of triggers is to create data manipulation language (DML) logging functionality.  Essentially you can intercept and log inserts, updates, and deletes to tables. In the simplest case it's enough to know that one of these operations has occurred on a table, the date/time that it occurred, and some sort of identifying information for the user that performed the operation. At the other end of the requirements spectrum is the need to identify all information, including data indicating what exactly has changed.   What you usually find is that people who need to log DML operations will write one trigger and modify the same basic code for every other table that needs to be logged. I know one person who even wrote a client-side utility to automatically generate custom trigger code for hundreds of tables that he had to log. One problem with this method, apart from the sheer boredom of modifying the same trigger, again and again, is maintenance. If the underlying table structure changes your trigger is suddenly shot. In this post we're going to use FOR XML and SQL Server catalog views to create a dynamic trigger that will work on just about any table, and will automatically adjust it's output if the table structure changes.   To start with, we'll create a log table called dbo.AuditDml in the AdventureWorks database:  CREATE TABLE dbo.AuditDml (Id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,   SchemaName VARCHAR(100) NOT NULL,   TableName VARCHAR(100) NOT NULL,   TriggerName VARCHAR(100) NOT NULL,   LogTime DATETIME NOT NULL,   UserName VARCHAR(100) NULL,   SPID INT NOT NULL,   Changes XML); GO Next we’ll create a dynamic trigger.  By dynamic I mean that you can run this script against any table and it will create a trigger that automatically detects its parent table and schema and logs all DML actions against the table properly, regardless of table structure.  The example below creates the dynamic trigger on the AdventureWorks HumanResources.Shift table. -- Change the schema and table name to match any -- existing table in your database CREATE TRIGGER HumanResources.AuditDml_Shift ON HumanResources.Shift FOR UPDATE, INSERT, DELETE AS BEGIN   DECLARE @Changes XML;   SELECT @Changes = COALESCE((       SELECT *       FROM deleted       FOR XML AUTO), '') +     COALESCE((       SELECT *       FROM inserted       FOR XML AUTO), '')    DECLARE @TriggerID INT;   DECLARE @TableID INT;   DECLARE @SchemaID INT;   DECLARE @SchemaName VARCHAR(100);   SET @TriggerID = @@PROCID;   SELECT @TableID = parent_id   FROM sys.triggers   WHERE object_id = @TriggerID;   SELECT @SchemaID = t.schema_id,     @SchemaName = s.name   FROM sys.tables t   INNER JOIN sys.schemas s     ON t.schema_id = s.schema_id   WHERE t.object_id = @TableID;   INSERT INTO dbo.AuditDml (SchemaName,     TableName,     TriggerName,     LogTime,     UserName,     SPID,     Changes)   SELECT @SchemaName,     OBJECT_NAME(@TableID),     OBJECT_NAME(@TriggerID),     GETDATE(),     USER_NAME(),     @@SPID,     @Changes; END GO The dynamic trigger automatically determines it's schema and parent table name based on its own ID; so it doesn't matter what table this trigger is created on. It will always automatically detect this information with no special action on the part of the developer. The actual data changes are grabbed from the inserted and deleted virtual tables, which are dynamically structured using the FOR XML AUTO clause. FOR XML AUTO structures the XML data automatically based on the columns in the inserted and deleted virtual tables. Again, you don't have to do anything special, the trigger will automatically adjust its output based on the structure of the table - even if the table structure changes in the future. Now that we have a dynamic trigger created on the HumanResources.Shift table, we'll run a few DML statements to test it. INSERT INTO HumanResources.Shift (Name, StartTime, EndTime) VALUES ('Noon', '12:00:00', '20:00:00'); UPDATE HumanResources.Shift SET Name = 'Afternoon' WHERE Name = 'Noon'; DELETE FROM HumanResources.Shift WHERE ShiftId > 3; These statements perform an insert, an update, and a subsequent delete of a new example shift I call the "Noon" or "Afternoon" shift. The actions are logged, as shown in the image below. Each entry has information like the schema name, table name, date/time, user name, etc. The Changes column is an XML data type column with the contents of the inserted and deleted virtual tables in XML format. The image below shows the results of the sample UPDATE statement above.   This type of dynamic logging is especially useful when you have to log DML activity for several tables in a transactional system. A couple of caveats:  This trigger may require some changes if your table contains LOB data type columns. Also always take care when using triggers on high-DML activity tables, as triggers of any kind can affect performance.   SQL Server Standard magazine will be publishing more of these tips for getting the most out of SQL Server XML in a future issue. Posted Jan 26 2008, 08:47 PM by Mike C with no comments Filed under: XML, new features, programming, SQL Server, auditing, logging, trigger

• Xml Data Type: nodes() Method

  I've decided to kick this blog off with a double-feature. In the last post I gave an example of shredding XML query plans with the nodes() method. In this one we'll discuss the nodes() method in a little more detail. In a previous post on the Pseudo-Random blog, I recommended that everyone who's still using OPENXML should switch over to the xml data type's nodes() method. The problems I mentioned included: The 1/8th memory problem - the sp_xml_preparedocument system stored procedure allocates 1/8th of the total server memory to the XML cache when you invoke it to turn your text-based XML into a DOM document for further querying and manipulation. COM. COM. COM. - OPENXML relies on MSXML, which it invokes using COM. Besides being on my TTMMU shortlist ("Things That Make Me Unhappy"), running COM objects within the SQL Server process space is inherently dangerous.  Of course if you invoke a COM object from within SQL Server, but keep it outside of the SQL Server process space, then SQL Server has no control over that COM object. This was the same reason we had to wait for SQLCLR to invoke .NET code from within SQL Server. Handles - OPENXML forced us to pass a "handle", essentially an index ID # or pointer to an MSXML DOM instance. To get the DOM instance, of course, you have to first call sp_xml_preparedocument. And if for some reason you don't call sp_xml_removedocument? "Instant memory leak." Ease-of-use - the syntax for OPENXML is a bit kludgy and very non-intuitive, especially if you are dealing with namespaces in your XML document. And the list goes on... I don't want to use up this entire blog entry complaining about OPENXML, so I'll cut it short here. From the above you get the idea that OPENXML is an inferior method of shredding XML data. So what's the alternative?  Glad you asked.  The xml data type includes several built-in methods, including one method designed just for shredding XML data: the nodes() method.  Basically this method works like this: The nodes() method accepts an XQuery path expression Every XML node in your xml data type instance that matches the path expression is returned as a relational row.  Each row is an xml data type instance, but a restricted version (more on this later) The sample query below demonstrates the nodes() method. In the sample we shred the XML document into relational rows, and query the context node ('.') to return each relational row. DECLARE @xml XML; -- Character representation of the XML SET @xml = '<capitals>   <state name="Alabama"     abbreviation="AL"     capital="Montgomery" />   <state name="Alaska"     abbreviation="AK"     capital="Juneau" />   <state name="Arizona"     abbreviation="AZ"     capital="Phoenix" /> </capitals>'; -- Retrieve each individual node from the relational result set SELECT Node.query('.') FROM @xml.nodes('/capitals/state') TempXML (Node); One thing to notice is that both the virtual table name (TempXML) and the virtual column name (Node) are defined as aliases behind the nodes() method.  This is mandatory.  The result of this query shows that each /capitals/state node is shredded into a single row of the result set. Once the xml data type instance is shredded into relational rows of the xml data type, you can use the other xml data type methods like query() and value() to extract nodes and scalar values from the rows.  As I previously said, the xml rows returned by the nodes() method are restricted in their functionality.  You can't cast them to other data types; you can only use the xml data type methods on them to extract their contents.  We can use the value() method in our example to extract each state's name, abbreviation, and capital from the nodes() result set, as shown below. DECLARE @xml XML; -- Character representation of the XML SET @xml = '<capitals>   <state name="Alabama"     abbreviation="AL"     capital="Montgomery" />   <state name="Alaska"     abbreviation="AK"     capital="Juneau" />   <state name="Arizona"     abbreviation="AZ"     capital="Phoenix" /> </capitals>'; -- Retrieve scalar values from the relational result set SELECT Node.value('@name', 'varchar(100)') AS Name,   Node.value('@abbreviation', 'varchar(2)') AS Abbreviation,   Node.value('@capital', 'varchar(100)') AS Capital FROM @xml.nodes('/capitals/state') TempXML (Node); As you can see, this syntax is fairly intuitive, especially when compared to the equivalent OPENXML syntax.  The results are shown below. In the next post we'll talk about loading XML data from files from the file system directly into SQL Server. Posted Jan 22 2008, 07:44 PM by Mike C with no comments Filed under: XML, tutorial, shredding, programming, SQL Server

• Welcome to Pro SQL Server XML

  Hi everyone, in this blog I plan to discuss the new SQL Server 2005 and SQL Server 2008 XML features. While I have several topics I plan to cover already, if you have specific SQL Server + XML related questions, feel free to contact me via this blog and I'll try to answer, or at least help you get a little closer to the answer. I decided to kick this off with a simple example to demonstrate the utility of XML in SQL Server. SQL Server 2008 and 2005 store cached XML query plans that are accessible via the sys.dm_exec_query_plan dynamic management function. This function takes a query plan handle as an argument and returns the XML query plan, along with some other metadata. While I was at the PASS Conference in Denver, I threw together a quick example to demonstrate shredding the cached XML query plans on SQL 2005. I expanded it a little bit to include some other information from the sys.dm_exec_sql_text dynamic management function and the sys.dm_exec_cached_plans dynamic management view. WITH Plans(nodeid, physicalop, estimated_cost, plan_handle, text, query_plan, cacheobjtype, objtype) AS (   SELECT RelOp.op.value('declare default element namespace       "http://schemas.microsoft.com/sqlserver/2004/07/showplan";       @NodeId', 'int'),     RelOp.op.value('declare default element namespace       "http://schemas.microsoft.com/sqlserver/2004/07/showplan";       @PhysicalOp', 'varchar(50)'),     RelOp.op.value('declare default element namespace       "http://schemas.microsoft.com/sqlserver/2004/07/showplan";       @EstimatedTotalSubtreeCost ', 'float'),     cp.plan_handle,     st.text,     qp.query_plan,     cp.cacheobjtype,     cp.objtype   FROM sys.dm_exec_cached_plans cp   CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st   CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp   CROSS APPLY qp.query_plan.nodes('declare default element namespace     "http://schemas.microsoft.com/sqlserver/2004/07/showplan";     //RelOp') RelOp (op) ) SELECT ROW_NUMBER() OVER (PARTITION BY p.plan_handle ORDER BY p.NodeId) AS Operation_Num,   p.physicalop,   p.text,   p.cacheobjtype,   p.objtype,   p.estimated_cost FROM Plans p WHERE p.cacheobjtype = 'Compiled Plan'; Sample results of running this query on my local server are shown in the image below. This sample query uses a few of the new features available starting with SQL 2005: the CROSS APPLY operator, Common Table Expressions (CTEs), dynamic management views/functions, and the ROW_NUMBER() windowing function. The most important feature we're using here is the xml data type and its nodes() and value() methods.  Since the XML query plans are stored as xml data type data, we are using the nodes() method to shred the XML data nodes into rows. Then we apply the value() method to each of these new rows to extract individual query plan operators and operator information from the plan. In this case we pull the physical operator name ("Nested Loops", "Filter", etc.) and the estimated subtree cost for each operator. As a bonus we are grabbing the initial SQL statement that is the basis for the plan from the sys.dm_exec_sql_text dynamic management view. That way we can easily relate the query plan, and its individual operators, back to the source SQL statement. This is just one example of the capabilities of the xml data type.  In upcoming posts I'll describe more precisely how the xml data type methods and XQuery work. Posted Jan 21 2008, 05:38 PM by Mike C with 1 comment(s) Filed under: XML, new features, methods, tutorial, introduction