Auditing Data Modification Statements Using Trigger
Here is an interesting problem: how to audit data modification statements against a table? The goal is to log the SQL executed to modify the table using a trigger. Of course, SELECT statements cannot be logged since only INSERT/UPDATE/DELETE can invoke a trigger. One way to implement this is using a server side trace (which […]
Table Valued Parameters
Passing multiple rows of data to stored procedures has always intrigued application developers. Many different methods have been used, more evolving around XML or some form of concatenated list. Those approaches require a method for parsing before the data can be used in normalized table format. SQL Server 2008 adds another option: table valued parameters. […]
Filtered Indexes
Filtered indexes are another welcome addition in SQL Server 2008. They allow creating an index filtered with WHERE clause. While this was doable in SQL Server 2005 using indexed views on the table, the natural approach of directly defining a filtered index is much more appealing. Below are some examples of using filtered indexes. The […]
Composable DML
Another powerful T-SQL enhancement in SQL Server 2008 is the ability to write composable DML. It allows to consume the OUTPUT clause result set and to feed it as source for a query. Here is one example to demonstrate that. The scenario is to merge data from daily updated branch office table to central office […]
Delighters in SQL Server 2008
Two of the new T-SQL enhancements in SQL Server 2008 are the inline variable initialization and compound assignment (also called delighters). We had those for a long time in languages like C, C++, C#, VB, and they are just coming to T-SQL. While not of significant value, it will make writing code easier and more […]
Convert Hex String to Binary String
Here is another use of table with numbers and concatenation using FOR XML PATH. Given a hexadecimal string value like ‘7FE0’, convert it to the binary representation ‘0111111111100000’. The table with numbers generated by the CTE is used to slice the hex string, then replace it with the corresponding binary value and concatenate using FOR […]