What SQL Text is that Session Running?
Use the code below if you have a long, complicated, stored procedure or piece of dynamic SQL running on a server, and you’d like to see exactly which piece of SQL Text, or code, is…
Modifying XML values
Determining the property syntax when modifying XML values in SQL Server can be time consuming if you don’t work with XML regularly. SQL Server includes a very flexible XML subsystem, called XML_DML, or XML Data…
Using Check Constraints with Column Sets
In our previous post on how to use columns sets, we saw how to design a simple table with a set of sparse columns representing 3 different groups of columns. Each group of columns represents…
Using Column Sets for Sparsely Populated Columns
SQL Server has a little-known feature called Column Sets that is very useful when dealing with large numbers of sparse columns. Sparse columns are useful when you need a table with many columns where the…
Build a CREATE TABLE statement from the output of a Stored Procedure with dm_exec_describe_first_result_set
Day-to-day database administration and development on SQL Server will often require creating a temporary table to hold the output of some stored procedure. The code below uses the dm_exec_describe_first_result_set 1 system dynamic management function, or…
Efficiently Update Data from OLTP to OLAP
OLTP to OLAP: Acronym Time! Online Transaction Processing, or OLTP, serves data to clients for line-of-business applications, enabling core business functionality. Online Analytical Processing, or OLAP, consists of analyzing OLTP data with the intent of…