Maintainable T-SQL and patterns that avoid correctness and performance problems.
106 associated posts102 primary posts
In theory, all of your T-SQL statements are supposed to end with a semicolon, like this: [crayon-6ae53e3139e7b370031209/] Why? Well, SQL Server 2005's Books Online says: Many code examples use a semicolon (;) as a Transact-SQL statement terminator. Although the semicolon is not required, using it is considered a good practice. You know, like flossing your teeth,…
My First Post Here...
Well, my first technical post, was about how the default index creation method is OFFLINE. If you want that sweet, sweet Enterpri$e Edition ONLINE goodness, you need to specify it. It's been a while since that one; almost six months to the day. So here's another one!
But Window Functions Are Awesome
Heck yeah they are. And how. Boy howdy. Etc. You get the point. I'm enthusiastic. What can be cruel about them? Glad you asked!
SQL Server 2016 brings support for JSON. For those of you who don't know, JSON is JavaScript Object Notation - it's a way of representing application level objects as a string. Imagine XML without anywhere near as much line noise, and you're pretty much there. Our Sample Document In this example, to retrieve orders and…
You can't do that on management studio Recently, while working with a client, I did something in a query that they were mystified by. I didn't think much of it, but I thought it might be useful to you, dear readers, as well. Along with an explanation. Here's a sample query that takes advantage of…
This is a cool SSMS trick I picked up a while back Though not nearly as far back as I wish I had. It's so cool I made a .gif of it in action. When you're done putting your socks back on, I'll tell you how it works. Pure ALT+SHIFT magic. Hold down both…
Common table expressions are a feature of SQL that lets a developer create a query that can be referenced multiple times. This feature gives developers another tool to add flexibility or just to simplify code.
Why Common Table Expression?
Why is it called a Common Table Expression (CTE)? Because that's what the documentation calls it!
Over the years, I've done all kinds of awful things with T-SQL and made countless mistakes. Some were harmless; others were borderline catastrophic (exciting times!). I was curious what kind of horrible mistakes other people make, so I threw the question out to Twitter. Every answer I got was unique, which was both telling (so…
As a developer, one of the things I can no longer live without is a date table. Who wants to type DATENAME and DATEPART over and over again? Not this guy, and once you have a date table, you won't want to either.
In this 16-minute video, I'll give you four reasons why you should stop writing so many date functions and concatenations, and start looking them up in a table instead.
You've been querying comparative numbers like Year To Date and Same Period Last Year by using tedious CTEs and subqueries. Beginning with SQL Server 2012, getting these numbers is easier than ever! Join Doug for a 30-minute T-SQL tune-up using window functions that will cut down dramatically on the amount of code you need to write.
“How many rows exist in a table?” It seems like such an innocent request. It isn’t too hard to get this information out of SQL Server. But before you open SSMS and whip out a quick query, understand that there are multiple methods to get this information out of SQL Server – and none of…
Update - October 2015 - You can download a Torrent of a database (rather than a data dump) now.
Most of my demos involve my favorite demo database: Stack Overflow. The Stack Exchange folks are kind enough to make all of their data available via BitTorrent for Creative Commons usage as long as you properly attribute the source.
While working on some DMV scripts, I came up with a lazy way to have a user definable sort order in the query that seemed like pure genius. I showed it to the team and they’d never seen anything like it before. The Situation Users like to be in control. They want to define custom…
Moving between databases is hard enough, try using multiple databases in the same application and you might start thinking you've gone insane. Different application demands for accessibility, redundancy, backwards compatibility, or interoperability make this a possibility in the modern data center. One of the biggest challenges of running a heterogeneous database environment is dealing with a world of data type differences. There are two main ways to work through this situation:
Erika loves having fresh flowers around the house. Every Saturday morning, I pick up a bouquet at a farmer's market or grocery store and put it in a vase for her. I'm slowly upping my game by learning more and more about the art of arranging flowers.
When I say flowers, I bet you think about the English Garden style: a big, complex vase with all kinds of flowers crammed into every nook and cranny. It's an explosion of color and life.
Without looking in your wallet, do you know how much cash you have? Most of us know within a few dollars. Now, without looking in your SQL Server, do you know much data is cached in memory? You probably don't and that's okay; you shouldn't know how much data SQL Server is caching in memory.…
Aggregate functions are convenient - they solve a business need and they make development easy. Unfortunately, not all business requirements are so easy to solve. Let's look at one example: rolling averages. The Rolling Average A rolling average is a simple concept; an average is computed over a fixed subset of data. Rolling average calculations…
Hi. I'm a former developer who's moved into database administration, and here's what I wish somebody would have told me when I got started.
7. SQL functions rarely perform well.
Good developers like to reuse code by putting it into functions, and then calling those functions from multiple places. That's a great practice in the app tier, but it has huge performance drawbacks in the database tier.
Databases exist to store data and answer questions about that data. The way we ask questions has to change based on the database itself - SQL changes between different RDBMS vendors. Things get even stranger when you venture outside the RDBMS world and start storing your data in Hive. Even though Hive Query Language (HiveQL) looks like SQL, there are some subtle but important differences to querying that first timers will want to keep in mind.
http://youtu.be/2c33WStd94s
Want to help us shape our training? Take 30 seconds and answer 5 questions.
Step right up, ladies and gentlemen, and I will sell you the solution to all of your database needs. That’s right, it’s Doctor Codd’s Third Normal Form, guaranteed to cure all ailments of the schemata, pragmata, and performata. Doctor Codd’s Form will make your data performant and compressive. Accept no substitutes or imitators; Doctor Boyce’s…
