Auditing DDL Events
SQL Server doesn’t audit DDL events out of the box, aside from several major events which are captured in the Default System Trace. By “audit”, I mean there is no log of the data-definition-language statements…
Auto Parameterization and Implicit Conversion
When SQL Server processes a query that can be auto parameterized, the results can be a bit, shall we say, unexpected. Auto parameterization makes an implicit conversion to numeric(10,0) when there is a divisor present…
Using Dynamic SQL inside a Stored Procedure
Preventing direct access to database tables for users is a widely considered “best practice” for many DBAs. The pattern typically looks like User -> Stored Procedure -> Tables. Since the stored procedure owner has access…
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…