Monthly Archives: September 2010

SQL Saturday #52 Colorado

Yesterday I was fortunate to have attended SQL Saturday down in Englewood. For those that might not know SQL Saturday is a FREE one-day event with expert speakers that are all about SQL Server.

In attendance were several MVPs: Jason Strate, Chris Shaw, Glenn Berry, and Tim Mitchell (just to name a few).

I started off the day listening to a fab presentation on Extended Events by Jason Strate. Some takeaways about this session were:

1. If you’re running SQL 2008 you already have most of what you need already running
2. It’s usually a good practice to add a single event instead of several in one shot. This way if you need to drop one you don’t have to re-create all the others.
3. You can capture upwards of 250 events
4. SQL Load Generator is a nice tool to easily generate a test load for SQL Server.

After Jason’s great session I wandered over to learn from the DMV master Glenn Berry. He ran through the T-SQL/DMV code in his SQL 2008 Diagnostic and Information Queries. I am chomping at the bit to run this and audit some servers I have going at work.

Before lunch I decided to take in Jason Horner’s presentation on PowerShell. I have been using PowerShell very little and wanted to get an idea on how to bridge the gaps between using it for server admin tasks and SQL Server. Some takeaways here were:

1. You can invoke processes with the & (ampersand)
2. With v2 you can output to a WPF window
3. There are client components for SQL2k SP4+ and SQL2k5 SP2+ as well
4. You can automate table creation and drill into the SQL engine to edit items.

At lunch there were several giveaways and Steve Jones talked about life and career.

Attended three more session after lunch. The first was Glenn Berry talking about SQL Azure and all the caveats. He gave a great presentation but SQL Azure still seems to be lacking a bit in my opinion on what you can do with it (security, fine-tuning, etc).

Jen McCown of the MidnightDBAs gave the next presentation on T-SQL Codes Sins to a packed room. Jen hit home a couple of great points on documenting your code and keeping it clean. “Document as if you’re talking to the new guy”.

Lastly there was an interesting session on the SSIS API by MVP Tim Mitchell. As a C# programmer (as well as a DBA) this made sense and the practical applications of being able to create SSIS jobs on the fly have numerous possibilities.

Taking Mr. Denny Cherry’s advice I decided to go to the after-party for a bit and it was well worth it. I met some super smart and great people. The people at the Colorado SQL User group are awesome! The MVPs were very forthcoming with time and questions.

All in all I would put the event down as a great success for it’s first year. I absolutely can’t wait until next year?

Heck! Maybe I’ll even submit a topic.

Share and Enjoy

Unexpected Results with Views and Order By

Inspired by the upcoming presentation by Jen McCown at SQLSat #52 on brushing up your T-SQL I have embarked on a journey to better my own skills.

So, first off the definition of a view is a “virtual table that is outlined by a query and used as a table“.

It also must follow this criteria:

  • ORDER BY cannot be used in a view unless there is a TOP or FOR XML specified in the view
  • All columns in result must have names
  • All column names must be unique

A sample of creating a view would be like so:

CREATE VIEW dbo.DeptStoreCustomers
AS
SELECT * FROM dbo.Customers
WHERE dbo.Customers.custType = 1

So when this is run:

SELECT * FROM dbo.DeptStoreCustomers;

It would return something like:

** Note using *  is highly discouraged in production use for most scenarios. But for the sake of simplicity I’m using it here.

Ok, so all this making sense so far? Easy enough right?
So what about changing our view to include the TOP and ORDER BY clauses like this:

CREATE VIEW dbo.DeptStoreCustomers
AS
SELECT     TOP (100) PERCENT custID, custName, custType
FROM         dbo.Customers
ORDER BY custName

How do you think this will return rows? Probably not like you expect.

Here’s the output on SQL 2008 R2:

SELECT * FROM dbo.DeptStoreCustomers

So wait! Why aren’t the custNames ordered?

The explanation is that the ORDER BY clause does not guarantee the order of the results unless it’s specified in the outer query of the view.

This means that the ORDER BY within the view is utterly useless in this case as it is only used to determine the rows in the TOP clause not in the view itself.

The way to actually get ordered rows would be to query the actual view with the ORDER BY clause added like so:

SELECT * FROM dbo.DeptStoreCustomers
ORDER BY dbo.DeptStoreCustomers.custName

One important thing to note here. In SQL Server 2000 the original query result would have displayed in the correct order. This is because the query plan incorrectly considers the ORDER BY in the view on the total view and not just the TOP clause.

2005 and greater fixed this problem by requiring an outer sort on the view to get this result.

In summary be sure to update all your code to correctly specify the ORDER BY clause in the SELECT clause of the view and not in the view itself. This might save you some heartache in the form of unexpected results when updating from 2000 to newer versions.

Share and Enjoy

Category: SQL