SQL Saturday Vancouver – Agile Database Development

Posted on January 11, 2012 | 4 Comments

I am honored to say that I have been chosen to give an updated version of my presentation Database Development: Keep it Agile, Not Fragile at SQL Saturday 114, in Vancouver, BC, on March 17th, 2012.

The world of software development is accelerating. The time between a business idea and its release date is shrinking. That means developers, business staff, and IT personnel need to do more iterative (a.k.a. agile) design,  development, and deployments. Agile development for most types of systems is a known quantity. Agile development for databases is harder, and the best practices are more obscure.

Having done a few hundred database releases in an agile fashion, I’ll be presenting best practices, anti practices, tips, tricks, demos, and scripts you can use. Don’t miss out!

→ 4 Comments

Posted in Agile, SQL Development, SQL PASS, SQL Saturday

Tagged , , , ,

T-SQL Tuesday #25 – Tips and Tricks

Posted on December 13, 2011 | 2 Comments

T-SQL Tuesday logoIt is T-SQL Tuesday once again, hosted by Allen White (b / t). The theme for this T-SQL Tuesday is Tricks.

One of my favorite tricks is to use INTERSECT when I have to rewrite a SELECT query, to make sure I am returning the same data.

Let’s say I have a slow query that returns 1640 rows:

I refactor it for speed, and make sure it still returns 1640 rows:

How am I sure my new query returns the same data? I use INTERSECT, and make sure the intersect-ed query returns the same number of rows as the original query:

Success is concrete. Just like the Mafia.

Now I know my changes have not affected what data is returned. Success!

I also have a more concrete example, using the AdventureWorks2008 R2 database, and Example.sql .

→ 2 Comments

Posted in Performance Tuning, SQL Development, T-SQL Tuesdays

Tagged , , ,

Meme Tuesday – Improvements to SQL Server

Posted on December 6, 2011 | Leave a comment

Why So Serious?

Yesterday’s funny post was about Meme Monday.

Today’s post is the sequel: Meme Tuesday. This is a more serious post.

 

 

Here’s a list of features and changes I wish Microsoft would make:

  • Dynamic query re-optimization. If a query is doing badly, have the SQL engine detect that and re-optimized it on the fly for better performance.
  • Give the MVPs even more influence over which SQL Server products are developed.
  • Give DBAs the ability to create filegroups in tempdb, and assign different tempdb usages to each. I’d love to have user temp tables, sorts, hashes, RCSI data, and rollback data be separated, so I can tune them separately.
  • Make Enterprise features available in Standard edition. Table compression and backup compression are at the top of my list.
  • Object-level and index-level backups and restores. I would love to choose which of my nonclustered indexes to back up, because I don’t need most of them in emergencies.
  • Let companies build their own PDW instances. Very few companies can afford the multi-million dollar price tag attached to a PDW appliance. They’ll usually pay somebody to change the code rather than
  • Add built-in support for code libraries.
  • Change your SQL Server 2012 licensing scheme to be roughly on par with the old prices. The current increase in prices is going to encourage adoption of NoSQL and open-source alternatives.

→ Leave a comment

Posted in Meme Monday, SQL Development

Tagged , , ,