Yesterday we reviewed some tips on how to optimize query performance through the use of # tables and indexes. The resulting query performance increase was dramatic. In our real world scenario the time dropped from 30 seconds to under a second. In our comments, AZ Jim brought up the fact that the two queries we execute could be materialized permanently […]

Last week I concluded with a high level review of the different Join Techniques Defined in the ANSI standards, as implemented in SQL Server. We looked at the Merge, Loop and Hash join methods, in preparation for the topic today on table variables and # tables. The problem, bringing back again this much argued topic, is a simple report, based […]

SQL Server has three different join techniques we’re used to hearing about. A Merge Join takes two ordered sets and walks through them, keeping the matching rows. This join only looks at records sequentially in both tables it only moves forward, never backward. If no match is found in table A, and the value in table B is Lower, then […]

There was a lot of feedback on yesterday’s editorial citing confusion or miss-represented concepts. I think I haven’t been clear in my terms, and there are terms that are nearly the same “RowVersion” = Type, RowVersions = Data Change Tracking. Perhaps this paragraph from https://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.110).aspx will clarify, or make the concept even more murky. This is how ADO.Net is configured […]

I’m bringing two different concepts from editorials this past couple of weeks to demonstrate a whole new topic. Previously we talked about auditing data in your database. SQL Server, by default, has the ability for you to turn on a feature called RowVersioning. When this feature is enabled, SQL Server maintains a history table of all RowVersioned tables in your […]

Today I share a tip for ne SQL developers that will help with performance. As much as is reasonable, if you build your queries, procedures views and functions to access tables in a consistent order, it can have a big impact on reducing contention when multiple queries are execute. If query A needs a table held by Query B, but […]

Here’s an old tip for SQL newbies. Avoid using “SELECT *“. There are lots of performance problems, and potential inaccuracies that may occur by using this syntax. I don’t mean you should NEVER use SELECT *. When you are doing discovery, don’t know what the table definition is, or forget a column name, don’t hesitate to use this syntax. The […]

There was a lot of valuable feedback from yesterday’s editorial, “Generate Audit Triggers Using SQL Script“, demonstrating a script that creates audit trigger code. Interesting that most of the comments were really about the auditing technique rather than the code generation. So, let me add a few thoughts: The script example provided is not the most efficient code. It doesn’t […]

Here is an example of what I was trying to describe as generating code from the metadata stored in SQL Server. I once had a customer needing to audit all changes in their database. We developed a two phase implementation. First we created a generic audit queue table defined below. We created an audit trigger on any table to be […]

Here is an example of what I was trying to describe as generating code from the metadata stored in SQL Server. I once had a customer needing to audit all changes in their database. We developed a two phase implementation. First we created a generic audit queue table defined below. We created an audit trigger on any table to be […]