Worst Practices

Don’t tell me that you didn’t see it coming: at some point, Developers end up being put to hell by a DBA! I don’t want to enter the DBA/Developer wars, but some sins committed by Developers really deserve a ticket to the SQL Server hell. In particular, some of those sins are perpetrated when not even a single line of code is written yet and they have to do with the way the development environment is set up. [Read More]

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]