Voice of the DBA

Tag: syndicated

My 2011 PASS Summit Submissions

I wasn’t sure that I would submit anything, but in the end I committed to a few events in Seattle and decided that this was something I should do. If I get selected it will save my boss some $$ and it’s part of what I should be doing for my job.

I’ve done 3 different presentations in the last year for various events and decided on submitting only one of them to the Summit. The other is a variation on one I’m building for SQL in the City. I built that one to show off some Red Gate tools, so the places where I show demos of tool stuff I’ll rip those out and go with generic solutions instead.

The last one actually came to me one night when I couldn’t sleep. I had been thinking about work and had an upset stomach one night. I was up a few times and while sitting on the edge of the tub, trying to decide if I should go back to bed or just give up, this presentation came to me. I had the iPad with me and spent about 45 minutes or so in Evernote, sketching the outline to the presentation in 10 parts, and then fleshing out 4 or 5 of them into a few paragraphs apiece.

Here are my three, and no idea how they’ll do against the competition, or even if the committee will consider them, but we’ll see.

Preparation for Disaster

Sooner or later some sort of disaster occur on your SQL Server instance. It might be the destruction of a server, the corruption of a page inside the database, or just the unexpected deletion of some data. When disaster does strike, will you be prepared? Steve Jones will cover a number of common disaster occurrences and the ways in which you can be prepared to handle the situations.

The Top Ten SQL Server Skills You Need

Come learn the ten most important skills that every accidental DBA, developer, or even manager should be able to do in SQL server. If you want to know where to start working with SQL server or which 10 things will get you through most of your days, some see Steve Jones show you how to accomplish these ten important tasks and explain why they are important. These ten skills represent the 80/20 rule in action; if you can do these ten things, you can successfully get through most of your days at work.

Branding Yourself for a Dream Job -The Modern Resume

Everyone wants a dream job that they enjoy going to each week. However finding that job, and getting yourself hired can be hard for most people. Steve Jones will give you practical tips and suggestions in this session that show you how to better market yourself, how to get the attention of employers, and help improve the chances that the job you want will get offered to you. Learn about networking, blogging, writing, speaking , and more from Steve Jones, who has successfully used these techniques to find his dream job.

May 6, 2011
A row has no row number
It seems that every month I have someone asking the question about ordering or row numbers for a query. Let’s get one thing clear from the start: there are no "row numbers" in a table.

You can assume that the first row you inserted is row number one, but it’s not. In fact, depending on the indexing or lack of indexing, you may or may not get that row returned first by a query. You can add an ORDER BY when you query the table, and in that case you can get the rows returned in a certain order every time, however the row number is not linked to a row.

As an example. If I have this People table:
```
ID Name
-- -------
1  Steve
2  Gail
```
and I query:
```
select ID, name from people order by name
```
I get
```
ID Name
-- -------
2  Gail
1  Steve
```
I could add a row number
```
 
SELECT row_number() OVER (ORDER BY [name])
       , [Name]
   FROM dbo.People
```
and get this:
```
   Name
-- -------
1  Gail
2  Steve
```
But "Gail" isn’t linked to "1" as a row number. If I do this:
```
 
INSERT people SELECT 3, 'Bob'

SELECT row_number() OVER (ORDER BY [name])
       , [Name]
   FROM dbo.People
```
I now get this:
```
   Name
-- -------
1  Bob
2  Gail
2  Steve
```
Now "Bob" is 1. You can get row numbers, but they are only linked to an ORDER BY and a specific result set. If the data changes, the row numbers may move.

While it might appear in some queries that you are getting consistent ordering of results, don’t confuse coincidence with causality. You might live on those assumptions for years, building code on them, and then make a few changes and lots of things break.

If you need ordering, use ORDER BY.
May 5, 2011
T-SQL Tuesday #18 Coming Next Week

Other than the week I hosted it, I’ve been caught off guard by T-SQL Tuesday most months. This time I caught Bob’s Twitter announcement about #18. The topic this month is CTEs, so you have a week to get things ready.

May 3, 2011
Foreign Keys Help Performance

I have always put FKs into my database for data integrity purposes. I’ve worked on enough applications that didn’t have FKs, or any RI in place and it was always a nightmare when the application broke down or there were enhancements that allowed duplicates, orphans, or other data integrity problems.

However I ran across an old post form Grant Fritchey that shows Foreign Keys do more than that. They can actually help performance because the SQL Server database engine knows that there is data in the related tables that matches because of the FK relationship.

Does that matter?

If you read Grant’s post, and you should, it shows two different queries of the same data, but one has FKs enabled. That results in a much smaller execution plan, hitting fewer tables. I took Grant’s test and added one more twist.

I ran both queries in the same batch, with the execution plan. Guess what I found? Check out this image:

Guess which query has FKs and which one doesn’t? If you read Grant’s post, you’ll realize the first one has the FKs, but more importantly, if you look at the relative percentages of the batches, you see that there’s a 9x difference in resources.

Use FKs. They do more than protect data, they speed things up.

May 2, 2011