How to write non-JOIN WHERE clauses

Based on my previous post describing the differences between ANSI-89 and ANSI-92+ join syntaxes and recommendations, I had a follow-up question in a comment which was (paraphrased) What do I do with non-join WHERE clauses – how should I write those? Example: SELECT p.FirstName + ‘ works in ‘ + d.DepartmentName AS ‘Who Works Where’ […]

ON vs. WHERE – where should you put join conditions?

I had a request from a reader that I’ll answer today about when to do joins in the ON clause and when to do them in the WHERE clause.  For example: SELECT * FROM A, B WHERE A.a = B.b vs. SELECT * FROM A INNER JOIN B ON (A.a = B.b) The short answer […]

Local-Global Aggregation

I don’t know about you, but groupby is one of my favorite operators.  There are a TON of interesting optimizations that a QP considers when you start adding group by into queries, especially when you have joins and then a group by.  TPC-H benchmark wars among the large database vendors are won and lost on […]

Hunting for SQL 2008 SPARSE Column Easter Eggs

So I was playing today with the sparse column implementation in the latest SQL 2008 CTP, and I was specifically looking to see how this thing would work with the query processor.  I wanted to know how much information is exposed for the QP to make smart decisions when this functionality is used in complex […]

1753, datetime, and you

So now that I have the latest CTP working again on my main machine, it’s far less troublesome to go research things and post what I find.  Tonight I’ll talk a little about datetime vs. date, as dates are on my mind for whatever reason. So the “old” SQL Server datetime type only goes back […]

SQL 2008 CTP Installation Problems – Largely Fixed

Well, I was able to get things to finally install using the information from my previous post and using a named instance (different from the default instance I used previously).  So, while I’d still like to track down the keys that define an installation instance with enough detail to remove them, I think I’ve gotten […]

Progress on the SQL 2008 CTP6 Installation Problems

You may recall my previous posts on my trouble with SQL 2008 CTP6.  I’ve made some progress on fixing my machine that I thought I’d share with you.  I now get past the following error: D:\temp\sqlctp6\servers>setupThe following error occurred:MsiGetProductInfo failed to retrieve ProductVersion for package with Product Code = ‘{00E75F61-A126-4CE1-90B8-42295052F1AC}’. Error code: 1605. I useded […]

Inserts against views – An introduction

Well, I spun the wheel of database topics I have here in my room, and today I think I’ll talk about updates and views…. specifically inserts through non-indexed views.  Since I haven’t blogged about this previously, I’ll start at the beginning.  There are many, many update topics, so don’t feel left out – comment if […]

An Era of Increased Expectations…

So while I hope that I’ve demonstrated that I can say some interesting things about database engine design and implementation, I also want to post blog entries on the design of data-driven applications, as this is a topic I’ve been pondering a lot lately. I’ll start with something that seems completely off-topic that isn’t… So […]

Query Optimization DMV du jour – sys.dm_exec_query_optimizer_info

So as you proceed up the river into the jungle, searching for answers about how the query optimizer works, I’ll ask you one question: Did you know that there’s actually a lot of stuff that the Optimizer team just tells you?  It’s in the product, and I’m constantly suprised by how little attention they get.  […]

CTP6 Install Problems Update

I’ve been mostly working with a very kind soul from the SQL Server Installer Dev Team trying to fix my box.  So far my luck hasn’t been too good, but I’ll keep at it. Things that are interesting to post if you have a blog and go through the same exercise that I did:* which […]

SQL 2008 CTP6 Filtered Indexes in Action – disjunction of ranges and index choice

After fighting off a cold all week, I’ve had some more time to go play with CTP6 on my secondary machine. Today we’ll do some experiments to see how the Optimizer picks plans for filtered indexes.  This will help you figure out how to build your database schemas and queries to take advantage of this […]