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’ [...]
Conor Cunningham
How to write non-JOIN WHERE clauses
Posted on: March 31, 2008 7:47 pm
ON vs. WHERE – where should you put join conditions?
Posted on: March 29, 2008 2:31 pm
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
Posted on: March 26, 2008 9:01 pm
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
Posted on: March 23, 2008 7:35 pm
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
Posted on: March 17, 2008 7:47 pm
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
Posted on: March 17, 2008 7:00 pm
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
Posted on: March 14, 2008 5:15 pm
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
Posted on: March 12, 2008 7:42 pm
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…
Posted on: March 8, 2008 4:12 pm
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
Posted on: March 8, 2008 3:41 pm
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
Posted on: March 6, 2008 8:55 pm
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
Posted on: March 2, 2008 3:05 pm
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 [...]
