Writing, reviewing, debugging, and maintaining T-SQL and database code.
478 associated posts232 primary posts
Eventually we all run into a situation that makes us cringe: the users want to design their own reports. If this were something as simple as giving users a color picker and some visual layout tools, that would be one thing. Most of the time it’s more complex. Business users know their problem domain very…
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…
Years ago, I came across an article about a gentleman who made a cat camera. Without boring you too much, this camera gave him a detailed view (including GPS info) about what his cats got up to all day. I got excited about the idea of finding out what my pets did all day. Not having…
Connection strings - boring topic, right? Well, it turns out there's a few things that can save your behind.
Tip 1: Build your application with 3 connection strings.
If you want to scale, write your application from the start with 3 different connection strings:
Writes and real-time reads
Reads that can tolerate data older than 15 seconds (no writes allowed with this connection string)
Reads that can tolerate data several hours old (like reports, and no writes allowed with this string)
Data masking, data scrambling, and just plain old obfuscation: these are ways to get developers access to production-quality data for testing purposes without actually giving them real production data. It's much harder than it looks, though. The Easy Part: Obfuscating Data Developers love working with production data. Today's privacy-concerned companies aren't quite so keen on…
If you're only indexing to make reads faster, you need to think again. Indexes can make every operation in the database faster, even deletes. The Problem: Deletes are Very Slow I was lazily researching new development techniques one day when I received an email from a client asking why deletes could be slow. I rattled…
Deadlocks can kill an application's performance. Users will complain about the app being slow or broken. Developers will ask the DBA to fix the problem, DBAs will push the problem back on developers. The next thing you know, the office looks like Lord of the Flies.
What is a Deadlock?
Aggregations and grouping can be a pain in the rear for the novice SQL developer. Way back in 2003, the ANSI/ISO standards people figured this out and added windows and ranking functions to the standard. In 2005, Microsoft added a few of these functions (ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE()) as well as the OVER() clause…
People love to search. Google has us addicted to fast, easy search functions. Users expect every application to have a built-in blazing-fast search functionality. To pull that off, developers build search queries that let users enter a string, and we ask SQL Server to find matches. For example, say our users need to find some…
The ever-insightful Buck Woody (Blog - @BuckWoody) wrote a post telling you to use database schemas. He writes: "But I still see a lot of applications, developed by shops I know as well as vendors, that don’t make use of a Schema. Everything is piled under dbo.... But if you’ll use them properly you can…
Are all of your servers set up to clean out their backup history periodically?
Are you sure?
R2 makes it easier for DBAs to move databases around from server to server in much the same way virtualization admins move guest OS's around between physical hosts. In my last blog post about SQL Server 2008 R2, I explained that databases are becoming more like virtual servers, and today I'll talk about what that…
If you're hiring someone who will be storing data in a database, here's how to find out if they know what they're doing. Includes questions like, "Explain referential integrity and where it can be enforced," and "What's the fastest way to get a thousand records into the database?"
UPDATE 2013: This code is no longer available. The size of the StackOverflow export has grown beyond what you can import with this method.
Want to play around with the StackOverflow database export? Here's how to import the XML files into SQL Server, and some notes about the tables and data schema.
Script to Import StackOverflow XML to SQL Server
This T-SQL script will create six stored procedures:
Sounds simple, right? Just grab the max. But what if you want to use a single T-SQL operation to fetch the most recent record and some of its attributes? StorminSpank asked: @BrentO SQL Question. 4 columns, ID, Date, Info1, Info2. ID has multiple entries. Want Latest Date for Distinct ID, but also info1 and 2.…
When joining multiple tables together in a query, use aliases every time on every field. Say we've got two tables, Customers and Salespersons. The Customers table has a PreferredSalespersonID field that identifies who their normal sales rep is, and that lets us quickly grab the right salesperson when a customer calls in. We want to…
Looking for someone with more than a couple years of experience? Here's how to find out if they're bluffing. Includes questions like, "I'm a developer - explain why I need a unique key on my table." Of course, I don't include the answers - that would make it too easy for people to read this blog and fake it!
I had to do some developer training last week and I wrote up a paper on the basics of primary keys and indexes. Sure, there's tons of similar stuff around the net, but this is MINE, baby.
Our Table: Phone Numbers
For our evil training purposes, let's say we work for the phone company, and we need a database table with phone numbers. We need to track:
