May be you are just getting started with learning SQL server programming. May be you are facing some performance related issues. Or, perhaps you just like learning new stuff. Regardless, you will find this post insightful on the do’s and don’ts of SQL Server programming and maintenance.
If you follow these dos and don’ts religiously, you can greatly enhance the performance of your SQL Server. Other than that, it’s always good to have some tips handy, for you never know when you may have to use them. The points mentioned below will also help you in times of SQL server recovery.
- Know Your Tools.
It’s surprising that many SQL programmers are not fully knowledgeable of all the T-SQL commands. Many of them think that it’s a waste of time learning all the commands they are probably never going to use. But, don’t underestimate the power of knowledge. You never know when a command may come handy while designing a query.
- Normalize Your Tables
A lot of programmers don’t normalize their database, thinking that it will affect the performance and slow it down. But, the truth is that de-normalizing databases usually results in only slower designs. Therefore, always normalize while designing it.
- Know How the Data will be Accessed
One of the best things a database can have is an index design that is robust. But, it requires skills to achieve that. Every time a programmer adds an index, it makes things faster on SELECT. However, DELETE and INSERT become slower. So, maintaining and building indexes is a lot of work.
- Use Transactions
Use them particularly on long running queries. The practice will prove helpful in case things go awry.
- Beware of Deadlocks
Get used to the habit of accessing tables in the same order. If you are habitual of locking Table A followed by Table B, then do it the same way for all the procedures and transactions. If you break the sequence, some day you might encounter a deadlock. Deadlocks can be tricky to solve if the sequence was not carefully designed.
- Do Not Use Cursors
Using cursors only kills the performance of a system. A lot of beginners tend to use cursors without realizing that they are losing the performance. Novice programmers tend to use cursors too much because they feel comfortable in that way. But, that slows down the performance and the procedure takes a lot of time to run.
- Don’t Select (*)
This one can be difficult to get used to. But use it only for the columns that you need. Doing so will:
- Reduce memory consumption as well as network bandwidth.
- Will make it easier for the optimizer to read all columns from indexes.
- Ease down the security design.
- Do Not Open Large Recordsets
If you are wondering how to fill a combo with 100,000 items, then you should drop this idea. It is erroneous to do so. It will be tiresome to browse through so many records for your user just to stumble upon the right one. You need a better user interface here. Ideally, do not show any more than 200 records to users.
Peter Song is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including outlook recovery and PDF recovery software products. For more information, visit www.datanumen.com.