Always define a Unique Grouping

I've written before about providing a unique ordering. The idea being that downstream systems and developers could begin to depend on records always coming back in the same order. Without having something truly unique in your ORDER BY clause (like a primary key), results could be coming back in different orders.

When using GROUP BY, its even more important to ensure that the grouping is always going to be unique.

Let's look at a fairly obvious example using the Products table from the Northwind database.

Consider this statement:

    SELECT   ProductName,
             AVG(unitPrice) AS [AvgUnitPrice]
    FROM     Products
    GROUP BY ProductName
    ORDER BY ProductName

This is perfectly valid syntax. The resulting records will look fine also.

However, there is a chance that two (or five) products will have the same name, and will get placed into the same group. In this case, the results of this statement are not correct.

The safest thing to do, for this query is to add the ProductID to the GROUP BY clause like this:

    SELECT   ProductName,
             AVG(unitPrice) AS [AvgUnitPrice]
    FROM     Products
    GROUP BY ProductName,
             ProductID
    ORDER BY ProductName

This ensures that the grouping is correct, and that the averages will be correct. However, it still has the problem that the ordering is now not necessarily unique. (As addressed here.)

To also provide a unique ordering, you'd add the ProductID to the ORDER BY also:

    SELECT   ProductName,
             AVG(unitPrice) AS [AvgUnitPrice]
    FROM     Products
    GROUP BY ProductName,

             ProductID
    ORDER BY ProductName,
             ProductID

Always define a unique ordering

Your SELECT statements should always have a unique ordering.

This ensures that "downstream" systems/code that consumes the data doesn't assume (wrongly) that the data is always in the same order.

Let me describe this using the Products table from the MS Northwind database.

Consider this SQL:

SELECT   ProductName

FROM     Products

ORDER BY ProductName

This is syntactically correct SQL, and we'll assume that it returns the correct records.

If all the products have a different ProductName, this will provide a unique ordering of the records. In other words, each time the SELECT statement is executed, the records come back in the same order.

However, in a dynamic environment, with a large number of records, it is likely that two products could have the same ProductName, e.g., "Socks", "Lipstick", "Hammer". (Unless uniqueness is enforced on ProductName.)

The danger here is that downstream systems or consumers of the data from this SELECT statement might actually depend on the records always being in the exact same order. And when they are not, things can go wrong.

So why would a programmer downstream assume that the records are in order? Well, why not? At first glance, they look like they are in order. But with a million records that are changing, how could the programmer be sure? And why sort a million records that look like they are already in order?

A simple safe SQL practice is to always specify a unique ordering. In this case, adding ProductID to the ORDER BY statement would be easy:

SELECT   ProductName

FROM     Products

ORDER BY ProductName, ProductID

This is not likely to be noticed by anyone, and may never make a difference. But it can prevent future errors.

By the way, I encountered a very similar situation on a large software project. The database people said the data was sorted, the programmers wrote their code to depend on the sort. And visual inspection of the data made us think it was sorted. We ended up noticing the changing order of records as we were stepping through code in a debugger. In other words, we were convinced it was a logic error in the code.

Self-Referential Join Example on Northwind Database

Self-Referential Join Example on Northwind Database

In the Microsoft Northwind database, there is an example of a self-referential table, Employee (see data model).

Let's look at some of the data with a simple query:

SELECT   EmployeeID,
         LastName,
         ReportsTo
FROM     Employees
ORDER BY EmployeeID

Which gives the data shown.

So you can see that EmployeeID=6 with the LastName=Suyama reports to EmployeeID=5 with the LastName=Buchanan.

So how do we get this all in one table?







The primary constraint here is that you cannot use the same table name twice in the same FROM statement. So, we need to use a table alias.

SELECT   Employees.EmployeeID,
         Employees.LastName,
         Employees.ReportsTo,
         Boss.EmployeeID,
         Boss.LastName
FROM     Employees
   JOIN  Employees Boss    ON Employees.reportsTo = Boss.EmployeeID

ORDER BY Employees.EmployeeID


Which gives this:

The FROM statement uses the Employees table twice, but one is aliased to be Boss. Based on that alias, the correct join is to use Employees.reportsTo as the foreign key, and Boss.EmployeeID as the primary key; because Employees report to Bosses.

Here's how that join looks in the Query Designer.

Regardless of how the database engine actually accomplishes the results, it's good to view this as two complete copies of the (same) table. They both have exactly the same fields and all the same records.

Reorganizing a Large Index from the SQL Management Studio GUI

Reorganizing a Large Index from the SQL Management Studio GUI

It takes a ...long... time.

Just tried reorganizing the clustered index on a table with about 5.5 GB of data in it. I'm at about 90 minutes and counting...

Rebuilding the clustered index and two non-clustered indexes (1.3GB and 2.5GB) through the command line took about 10 minutes.

Mine is sort of a diabolical situation, but still.

I have a table with lots of rows, and the average record is about 110 bytes. So there are about 73 (8060/110, where 8060 is the non-header portion of a data page)  records on a data page. I removed an unused variable character field, which should save 2 bytes per record.

Strangely enough, a DBCC SHOWCONTIG before and after dropping this field shows exactly the same thing. I had to think about that for a minute, but it makes sense. SQL Server just removes the field from the schema, but makes no actual changes to the underlying data. So it happens very fast. And DBCC SHOWCONTIG is likely calculating its results from sys.allocation_units and sys.partitions, which would not necessarily get updated from a schema change.

By removing my one varchar field, the average record size is now 108 bytes, which would allow about 74 records on each data page. The table in question happens to have 700,000+ pages in it. And each page has very low fragmentation.

If I were doing a REBUILD, SQL Server would essentially copy the entire table into a new area, then swap it into the old table's place. By default, this is an offline operation.

However, a REORGANIZE is an online operation, and is done in-place. Each leaf-level page is defragmented, then more records are added, if possible. So, in my situation, it will do something like this:

1. go to first page
2. defrag it
3. go get some records from the next page to fill up this page
4. go to next page
5. if the page is empty, de-allocate it and go to next page
6. go to step 2

The good news about this, is that it is an online operation, and can be stopped at any time without a huge rollback.

The bad news is that in my situation, SQL Server has a lot of work to do because every record is fragmented - 2 bytes have been removed. Which means every page is fragmented. And every page will need some new records to fill it up. 

Not sure how much of this is the GUI, and how much is the REORGANIZE. But unfortunately, I can't seem to stop the REORGANIZE from the GUI.

Also, it's nice to be able to see the elapsed seconds in the lower right corner of the screen when you do things from the command line. With the GUI, you can't see how long it is taking.

Hopefully, this will be done by tomorrow morning.