So for #ThrowbackThursday, we are going back to remember the great twitter chat (#dellsql) that Brent Ozar (@BrentO), a Microsoft Certified Master of SQL and Richard Douglas (SQLRich), a SQL Server Consultant for Dell Software, hosted with the topic being best practices for designing databases.
Let’s take a trip down memory lane shall we? Think back to your freshman year of college. Remember those “101” classes that seemed like a pointless waste of time? Well, we’re sure years later you’re still kicking yourself for not taking better notes. We’ll take mercy on your tired DBA self and pass along a CliffsNote version of this super helpful Twitter Chat.
In our previous post, we talked about the top 10 things DBAs should know when it comes to SQL Server. This week, we wanted to share the best practices, expert advice and tips from our followers on what features, commands and datatypes should be generally avoided. Think of it as an adult, DBA 101 class if you will.
@dpenton says “NEVER use the Construct BETWEEN ‘date here’ AND ‘another date here’”
This is because you will likely be missing data. If you say BETWEEN December 1st and December 31st, SQL Server sees that last parameter as the very beginning of December 31st. For example, any data from, say, 1AM on December 31st and afterwards is going to be excluded.
@SQLBob says “Simplicity is often the best method”
Getting fancy with things like query hints rarely improves query performance.
@kbrian says “If you’re going to nest views, look at and understand the query plan that will result”
Don’t get us wrong – we’re not saying nested views are wrong. Sometimes they can work really well, however you may end up dragging a bunch more tables into your query inadvertently.
@AaronBertrand says “Don’t kill yourself trying to force a seek – they’re not always the most efficient way to access your data”
Remember – sometimes, a scan is easier – especially for grouping.
@SirSQL says “Don’t develop to a date, develop until it’s right, performs and scales”
Although we consider this much more targeted to managers rather than developers, it’s still a good piece of advice to keep in mind.