Sparse columns are another addition to the SQL Server 2008 tool belt. They offer a good trade-off for many applications: taking no space if they are empty and more space if they have data. In other words they optimize storage for NULL values. Sparse columns are just like ordinary columns with a few limitations. They […]
SQL Saturday is a free event for SQL Server professionals. It offers multiple tracks with sessions for different skill levels. It is a great way to learn, share knowledge, and network with colleagues in the SQL Server field. The upcoming SQL Saturday #3 in Jacksonville (May 3rd, 2008) includes four complete tracks for developers, DBAs, […]
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 […]
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 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 […]
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 […]
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 […]
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 […]
Very often we have a good set of production data but it cannot be used for training or testing because of data confidentiality. And the solution sometimes is just to tweak pieces of the data so it cannot be linked to the original data. Here is one method for obscure data in SQL Server 2005. […]
One very common requirement is to merge source data to a target table. For example, merge the daily changes to accounts data from branch office table to central accounts table. On SQL Server version 2005 and prior this was accomplished using separate INSERT and UPDATE statements. This involved checking if key column exists to perform […]