Author Archive: Gail

SQL in the Wild > Articles by: Gail

Execution plan operations – aggregates

Author: 27 January 2008 2 Comments

On to aggregates.

I’m not going to dwell too long on the logical operators for aggregates, as they aren’t as interesting as the joins were. Rather I’m going to concentrate on the physical operators involved.

The two physical operators used for aggregates (and a couple of other logical operations) are:

  • Stream Aggregate
  • Hash Aggregate

Stream Aggregate

The stream aggregate is the faster and more efficient of the aggregate operators. For it to be used, the input rowset must be sorted in order of the grouping columns.

(more…)

Execution Plans, SQL Server

Comparisons with NULL

Author: 16 January 2008 1 Comment

Or ‘True, False or FileNotFound’ (1)

Null seems to confuse a lot of people. The main issue with null is that it is not a value. It’s the absence of a value, a place holder for unknown data. Hence, when it is used in a comparison, it returns results that look surprising. Take this as a quick example

DECLARE @Var1 INT
SET @Var1 = NULL

SELECT
CASE WHEN @Var1 = NULL THEN ‘Var1 = Null’ ELSE ‘Var1 <> NULL’ END AS EqualityTest,
CASE WHEN @Var1 <> NULL THEN ‘Var1 <> Null’ ELSE ‘Var1 = NULL’ END AS InequalityTest

The results look a little strange. They’re supposed to. (No, I didn’t mean IS NULL/IS NOT NULL)

(more…)

SQL Server, T-SQL

SQL 2008 training

Author: 3 January 2008 0 Comments

I’ve run into a bunch of free SQL Server 2008 training over the last few days.

SQL Server

Becoming a better … – lookback

Author: 31 December 2007 0 Comments

I thought I’d take a quick look back over the last two months, see how I faired on what I set out to do.

  • Finish 2 database books by the end of the year – I didn’t manage to finish either, though I started both the ones I said I’d read, and a third one. Part of the problem is that I was studying for an exam until mid december
  • Write at least 1 blog post a week here – Looking back I see that I wrote 8 posts in 8 weeks, however one was decidedly not SQL related and a second was just a comment on an exam. Not too bad.

Now for the next 6 months.

  • Read and finish one database book every 2 months. I don’t think I’m going to be able to do more than this, especially since I have a thesis to write.
  • Read at least one C# book
  • One blog post a week
  • One SQL or development related podcast a week.

We’ll see in 6 months how well this goes.

General

Execution plan operations – joins

Author: 30 December 2007 10 Comments

It’s about time I picked this series up again.

I’m not going to go into too much detail on joins. There are some very good articles elsewhere on joins. The important thing to notice about joins, in the context of an execution plan, is that there are six logical join operators and three physical join operators. The logical operators are what you ask for in the context of the query, the physical operators are what the optimiser picks to do the join.

The six logical operators are:

  • Inner Join
  • Outer Join
  • Cross Join
  • Cross Apply (new in SQL 2005)
  • Semi-Join
  • Anti Semi-Join

Craig Freedman wrote a long article on the logical join operators – Introduction to Joins

The semi-joins are the exception, in that they cannot be specified in a query. Nonetheless, they are present in disguise. They’re the logical operators for EXISTS, IN, NOT EXISTS and NOT IN. They’re used when matching is required, but not a complete join.

(more…)

Execution Plans, SQL Server

Temp tables and table variables

Author: 19 December 2007 4 Comments

I’ve encountered a fair bit of confusion on various forums as to the differences between temporary tables and table variables. As a quick article (I’m knee-deep in some AI stuff at the moment) I thought I’d quickly go over some points on temp tables and table variables.

Temporary Tables

  • Created using the Create table syntax, preceding the table name with a’#’ for a local temp table and ‘##’ for a global temp table
  • Allocated storage space within the TempDB database and entered into the TempDB system tables 1
  • The table’s actual name is the name is was created with, a large number of underscores and a hash value, to prevent object name collisions if two connections create a temp table with the same name
  • Can have a primary key, defaults, constraints and indexes (however the names of these are not hashed, possibly leading to duplicate object errors for constraints and defaults)
  • May not have triggers.
  • Foreign keys are permitted, but are not enforced
  • Have column statistics kept on them. The algorithm for determining when to update is different to permanent tables
  • Exist until they are dropped, or the connection closes.
  • Are visible in any child procedures called from the one where the table was created. Are not visible to parent procedures
  • Are not persisted to disk unless there is memory pressure, or the table is too large to fit in the data cache

(more…)

SQL Server, T-SQL

Once more unto the breach…

Author: 16 December 2007 2 Comments

I wrote 70-447 on friday. It’s the last of the SQL 2005 exams that I intend to write.

I was pleasantly surprised. It wasn’t nearly as hard as I was expecting and I scored well above what I thought I would – 891. Very surprising, seeing as I don’t do much in the way of administration.

Next on the exam plans, either C# or Sharepoint. I haven’t decided yet.

Now, when are the SQL 2008 exams going to be available…..

Career

Birthday musings

Author: 7 December 2007 2 Comments

Another year gone, another year older. It’s been an interesting year.

Professionally, I’m very happy with the past year. I’ve achieved several goals, got to know some interesting people in the SQL community and, of course, started this blog. I’ve also made some fairly important decisions about the future.

Academically, I’m not so happy. My masters thesis is behind where it should be. (a fact I’m sure my supervisor would agree with.) Most of the initial reading is done and I know what I need to do. I need to sit down and get busy. This is going to have to take a higher priority in the next few months.

On more personal matters, a good friend moved up to JHB. It’s very good to see her again. The roleplaying campaign is going well and from the feedback I’ve been having, the players are all enjoying themselves.

Overall, it’s been a good year. Here’s to the next one.

Personal

Copyright 2018 Gail Shaw