There’s a place in the SQL Server hell where you can find poor souls wandering the paths of their circle, shouting nonsense table names or system-generated constraint names, trying to baptize everything they find on their way in a different manner. They might seem innocuous at a first glance, but beware those damned souls, as they can raise confusion and endanger performance. What they say in Heaven Guided by the Intelligent Designer’s hands, database architects in Heaven always name their tables, columns and all database objects following the rules in the ISO 11179 standard. [Read More]

Constraints are sometimes annoying in real life, but no society can exist without rules and regulations. The same concept is found in Database Design: no good data can exist without constraints. What they say in Heaven Constraints define what is acceptable in the database and what does not comply with business rules. In Heaven, where the perfect database runs smoothly, no constraint is overlooked and all the data obeys to the rules of angels: Every column accepts only the data it was meant for, using the appropriate data type Every column that requires a value has a NOT NULL constraint Every column that references a key in a different table has a FOREIGN KEY constraint Every column that must comply with a business rule has a CHECK constraint Every column that must be populated with a predefined value has a DEFAULT constraint Every table has a PRIMARY KEY constraint Every group of columns that does not accept duplicate values has a UNIQUE constraint Chaos belongs to hell OK: Heaven is Heaven, but what about hell? [Read More]

Choosing the right data type for your columns is first of all a design decision that has tremendous impact on the correctness of the database schema. It is not just about performance or space usage: the data type is the first constraint on your data and it decides what can be persisted in your columns and what is not acceptable. Choosing the wrong data type for your columns is a mistake that might make your life as a DBA look like hell. [Read More]

Object-Oriented programming taught us that generalizing is a good thing and, whenever possible, we should do it. Complex class hierarchies are a good way of reusing code, hitting the specialized classes only when a special implementation is needed. In the database world, the concept doesn’t play exactly well. What they say in Heaven In Heaven, there is a lookup table for each attribute, no matter how simple and no matter how small is the lookup table. [Read More]

There’s a special place in the SQL Server Hell for those who design their schema without following the Best Practices. In this first episode of SQL Server Infernals, we will explore together the Row of the Poor Schema Designers, also known as “undernormalizers”. What they say in Heaven In Heaven, where all Best Practices are followed and everything runs smoothly while angels sing, they design their databases following the rules of normalization. [Read More]

Today I’m starting a new blog series called “SQL Server Infernals”. Throughout this series, I will take your hand and walk you through the hell of SQL Server Worst Practices, as Virgil did with Dante in his Commedia. You may ask why you should care about worst practices, when you have loads of great sources for Best Practices. The answer is that they are not enough. There are too many Best Practices: how are you supposed to know all of them? [Read More]

I usually don’t post small things like this, but today I fought with this obnoxious problem long enough to convince me that it deserved a shout out to the community. When you create an alias in the SQL Server Configuration Manager, make sure that the alias name contains no spaces, otherwise it won’t work as you expect. In my case, I had a Reporting Services instance with many data sources pointing to a SQL Server instance (let’s call it MyServer) and I wanted to redirect connections to a different instance using an alias. [Read More]

Don’t be fooled by the title of this post: while counting the number of rows in a table is a trivial task for you, it is not trivial at all for SQL Server. Every time you run your COUNT(*) query, SQL Server has to scan an index or a heap to calculate that seemingly innocuous number and send it to your application. This means a lot of unnecessary reads and unnecessary blocking. [Read More]

If you want to have faster turnaround on your forum questions, you will need to provide enough information to the forum users in order to answer your question. In particular, talking about T-SQL questions, there are three things that your question must include: Table scripts Sample data Expected output   Table Script and Sample data Please make sure that anyone trying to answer your question can quickly work on the same data set you’re working on, or, at least the problematic part of it. [Read More]

One of the things I hate the most about “old” databases is the fact that unused tables are kept forever, because nobody knows whether they’re used or not. Sometimes it’s really hard to tell. Some databases are accessed by a huge number of applications, reports, ETL tools and God knows what else. In these cases, deciding whether you should drop a table or not is a tough call. Search your codebase The easiest way to know if a table is used, is to search the codebase for occurences of the table name. [Read More]