10 good habits with SQL Server

This is a number of good habits to have when working with databases:

1) Don’t use dynamic SQL.  That is concatenate some string to form a SQL query then execute it using
exec (@mysqlcommand). This will forbid you and your users from re-using the execution plan set for this query. If it’s frequently used, it can be sinking your performance.

2) Store your tables outside of the Primary file group. Split them by function. As your DB grows and your company is successful you will be able to move your data files belonging to a filegroup to another disk improving your performance. Taking advantage of RAID 10 and RAID 5.

3) Store your tempdb to a different disk from where your transaction log and data files are (that would be 3 disks or Raid system). If  you can’t afford that, group tempdb and transaction log together and put your data files to a different disk.

4) Know your database intimately. How much does it grow every months, how much is the business expected to grow. Is it smooth or does it double suddenly. That will allow you to define the size of your data file when you create it. Save the disk space for this data file allows for the data inserted in table belonging to it to be contiguous. Less fragmentation, better performance.

4) Look at the execution plan.  If you’re learning how to write good  sql, looking at it and trying different ways will help. On the long run, you’ll learn how to identify bottle necks and understand the different methods used by the SQL Server engine.

5)  The join command is not a filter. That one is a tricky one. exemple:

if I do :

select a.column1, a.column2, b.column1
from tablea a
inner join tableb b
on a.id = b.id
where b.column1 =  ‘somefilter’

That will produce good results as long as the database is small.

But as it grows big and reach a few million rows, the table a will still need to be scanned from top to bottom to define which rows matches b.id for which b.column1 = ‘somefilter’.

If tablea is the big table, that’s a serious problem. Answer: filter a. Is it running on all rows or is it for the last day of inserted data? it could be this instead:

select id,  column1,  column2
into #tempa
from tablea
where date = @yesterday

select a.column1, a.column2, b.column1
from #tempa a
inner join tableb b
on a.id = b.id
where b.column1 =  ‘somefilter’

6)  If you’ve got to use the like command,  try to avoid using filters looking like this: ‘%myfitler%’.
You won’t be able to use an index with it because the SQl Server engine can’t tell where to look. It could be anything. Instead use:  like ‘myfilter%’ That way the SQL Server engine knows that you’re looking for something starting with ‘m’.

7)  Give rights on a need to know basis and ask for reasons when you’re being asked for privileges. Data are sensitive and in the end you’re the one in charge.

8) Do good backups and test them regularly. This point would be a long post in itself but… Really. I’ve seen places making backups and feeling safe like that. Better feel truly unsafe than wrongly safe.

9) When deploying solutions, use methods that won’t leave password clear in text files. The best way I’ve found is to stick my stored procedures in SSIS which is then protected either with my user key or a password of its own stored in a configuration file.

10) Have fun doing what you do. Databases are sensitive. Pressure is often high. If you’re doing a good job, nobody will notice because it means nothing is breaking, so really have a lot of fun and pleasure implementing something great. It will make you feel good 🙂