New blog location

Here is the new blog  – please update those readers. I’m getting settled into working for Microsoft again – it’s basically like drinking from a firehose. I am also still hard at work on the book chapter for the upcoming SQL Internals series.  FYI I believe Paul and Kimberly still plan to host the […]

My blog will be moving

As I mentioned in my previous post, I’m going to be taking on a new job soon.  I’m actually going back to work for Microsoft, so it’s not entirely new ;).  I’ll still be living in Austin, TX, and commuting up to visit Redmond regularly. Unfortunately, working for MS and blogging on a consultant’s website […]

What’s Conor been doing?

Well, I can tell you that I’ve been doing a few things lately. 1. I’m writing a chapter for an upcoming book – lots of interesting details like what you read here on the blog about query processing and query optimization.  It will come out around the end of the year. 2. I’ve been getting […]

A nice MSDN article on SQL 2008 Data Warehousing It covers some of the new features in SQL 2008 w.r.t. Data Warehousing. Conor Cunningham

The Trouble with Triggers

(Apologies to Star Trek). I received a question about trigger performance, especially the do’s and dont’s about how they are used. Let’s first start by saying that triggers can be very useful tools to facilitate server-side programming in OLTP systems.  They are powerful ways to do things like audit changes to a table, validate business […]

Outer joins and ON clauses vs. WHERE clauses

(I am still around – I’ve just been busy with a few personal projects of late – keep asking questions if you have them). I received a question from a reader named Andy that was a follow-up to my questions on OUTER JOINs and WHERE clauses and ON clauses. Upon re-reading my previous entries, I […]

Contradictions within Contradictions

I had a question from a reader about contradiction detection. The basic idea is to determine that this kind of query: SELECT * FROM TABLE WHERE col1 > 5 and col1 < 0; …is utterly meaningless and requires no work to be done.  It will always return zero rows.  In fact, in many cases the […]

SQL 2008 Geometry type tidbits

I spent some time playing with the SQL 2008 geometry data type.  This post will cover some of the things I have learned about how it is implemented from playing with the feature.  Some of these will impact the feature’s usability for some.  Others may not care. 1. late-binding of the geometry data is an […]

COUNT(1) vs. COUNT(star)

A follow-up from my previous post. Is COUNT(1) faster than COUNT(*)? No, COUNT(1) is mapped into COUNT(*) early in the QP.  From the logic of my previous post, COUNT(1) is always non-null and thus is the same as just doing a COUNT(*).  Since it is semantically equivalent to COUNT(*), the QP just maps it to […]

COUNT(star) vs COUNT(column) vs. COUNT(DISTINCT) vs. COUNT(col) OVER(…)

So COUNT isn't an operator, but you should view this post as a "what is this operator" kind of post since I talk about how these things work and what they mean. One of the benefits of building database engines is that all (well, most of) the syntax rules end up stuck in your head, […]