The database comes with a manual, but let’s be honest: nobody’s got time for that. DBAs – help me distill the most important concepts and rules down to 140-character chunks. I’ll work with Dell to compile the best tips into a free ebook for developers.
There’s three ways you can contribute:
- Register for the live webcast on Thursday, October 24th from 10AM to 11AM Eastern. Leave a comment in chat during the webcast, and we’ll talk through the best ones to help boil them down to a perfect essence.
- Join me on Twitter on Thursday, October 24th from 10AM to 11AM Eastern using the hash tag #dellsql. If you just want to read along, you can use TweetChat.com at that time.
- Leave a blog post comment below with your nuggets of genius.
Make sure to keep it upbeat and helpful – after all, your name will be next to your suggestion, and you don’t want to look like That DBA. Let’s help developers get started on the right foot!
11 Comments. Leave new
90% of your needs are covered by the basics. Try to stay away from UNION, keep functions out of the WHERE clause, and keep OR to a minimum.
They need to know that we cannot read a single row from disk. We need to Teach them about the buffer cache, pages and how io relates to these concepts.
>They need to know that we cannot read a single row from disk.
I would argue on that, but then it would be a single row fitting the full page.
Don’t use SELECT * in views and leave the ORDER BY out of it.
Can we get a good starting point for server settings if we are setting up an OLTP server, an OLAP server and a hybrid server? I have all three, and right now they run pretty similar settings with just some tweaks on the locking scheme.
I am transitioning to SQL server from MySQL on my webserver and am somewhat terrified of the out of the box SQL server locking bringing the server to a full stop.
Also, do we need to diagram our databases? All it seems to do is make the server complain more when we’re doing maintenance (IE, deleting old stuff).
Just some ideas. Thanks.
Avoid the “fix it later” mentality. Think about scalability. Strive to select *only* the columns and rows that you need for a given task.
Whenever you are testing your code for proper joins use the DISTINCT keyword to compare results. If DISTINCT returns a different number of rows your joins need work.
NEVER use DISTINCT in production code. If your code is right you don’t need it.
While I agree that DISTINCT can be detrimental to the performance of a query, this advice isn’t very helpful without an alternative. If the developer is working with a poorly designed schema they will likely need a way to remove duplicate results, and changing the JOIN conditions will not help them out. They will either need to rely on derived tables, or EXISTS statements.
Again though, I do agree with your statements in general. Any time we can avoid operations that force a sort we are going to be in a better place.
Watch for implicit conversions. Take care to avoid datatype mismatches in WHERE clauses.
One common mistake I see developer’s make when working with .Net code and SQL is improper type conversions from SQL to CLR. This is a very good reference: http://msdn.microsoft.com/en-us/library/cc716729.aspx and so is this: http://msdn.microsoft.com/en-us/library/bb386947(v=vs.110).aspx