Ok, here’s a brain-twister. Not only can you assign values to a column in an UPDATE statement using variables, but you can assign values to variables as well. It’s really not as complicated as it may sound, but there are a few trapdoors to avoid.
Author: daniel
Schemabinding and table spools
There are obvious advantages as well as challenges to schemabinding things, and whether you do or don’t is often a matter of preference. However, in some cases, you need schemabinding; when you’re designing indexed views, and to optimize user-defined functions. Here’s why:
Blocking/non-blocking aggregate operators
Some database operations can be performed in distinctly different manners, with different impacts on query performance. One important example of such an operation is calculating an aggregate. In this article, we’ll take a look at how aggregates can be “blocking” or “non-blocking”, how it affects memory allocation, and ultimately, what impact this has on your query.
Datawarehouse modelling: Inmon vs Kimball
If you’re into business intelligence, data warehousing and analytics, you will have heard an endless number of references to Bill Inmon and Ralph Kimball. These two figureheads in datawarehousing architecture have produced an immense number of books, articles, training seminars, etc. While many of their strategies and modelling approaches are similar, they have near-opposite views on other aspects.
HASH JOIN deep-dive
Among the three different types of join operators used by SQL Server, the HASH JOIN does some of the hardest work. It scales pretty well and is very suitable for parallel processing. As such, it can be very powerful in many applications, but hash joins can potentially consume quite a bit of memory, so seeing on in your query plan could be an indicator of a performance tuning issue in your query or data.
Directed acyclic graphs vs parent-child hierarchies
We’ve recently looked at ways to work with parent-child hierarchies, particularly in reporting scenarios. Regular parent-child hierarchies are great when working with dimensions that are ragged, but they have a critical limitation – any given node in the tree can only have a single parent node. A great solution to this problem is a DAG – directed acyclic graph.
An introduction to filtered indexes
Filtered indexes are an excellent performance tuning feature found on SQL Server 2008 and later. Basically, by adding a WHERE clause to a CREATE INDEX statement, you can make the index smaller, more specific and faster.
Changes in Management Studio keyboard shortcuts
A few keyboard shortcuts have changed in the code editor of the SQL Server 2014 Management Studio. Personally, I found this incredibly annoying because I’ve been using Ctrl+E for many years to execute a query, but now, all I got was
(Ctrl+E) was pressed. Waiting for the second key of chord...
.. in the status bar.
Late-arriving dimension members
In datawarehousing, a common scenario you may have encountered is the phenomenon of late-arriving dimension members. Imagine a situation where you get dimension members and fact data from two entirely different data sources, which may or may not provide data at the same time. Some fact data will probably be ready to load before its corresponding dimension member(s) arrive. How do you tag your fact data, and what consequence does that have for the integrity of your data warehouse?
Minimally logged operations
Today, we’re going to look at which database operations can be performed with what’s known as “minimal logging”, which can result in a considerable performance boost. Included is a quick overview of transaction logging and what it’s good for.