Chris Klepeis' Rants I don't know what the internet is, but I want to be on it!

MS SQL Tips

When working on a project, the first, and arguably most important thing to consider is the database. Similar to how a building cannot be built without a solid infrastructure, a good program cannot survive without a solid, well designed database structure. This post will hopefully provide some pointers to fellow developers - SQL and MS SQL do's and don'ts.

Prior to jumping into a project, a developer should always lay out the database design plans first. A poorly thought out database design can and will cause headaches in the future. Any developer having to work with a legacy system that has a poorly designed database will surely attest to this.

1. Normalize your design

A database design should be, for the most part, normalized. There are several levels of normalization. Without going into the details of what each level surmises, normalization essentially prevents data duplication (more details in a future post). Normalizing your database will reap its benefits when your database grows. Do not cut corners when building your table structure. Trust me!

2. Naming Convention

Oftentimes when multiple developers are working on the same project, they won't follow the same naming convention. Although SQL is not case sensitive, all developers in a team should be following the same naming convention to keep everything consistent and much more pleasurable to work with. With table names, I personally prefer to capitalize the first letter of every word. Non-alphanumeric characters should be avoided.

3. Use Schemas

As of MS SQL Server 2005, users and schemas are separated. When a table is referenced as dbo.Table, "dbo" is the schema. When developing in .Net this is essentially what namespaces are used for. This allows us to group tables into logical structure, set permissions based on these schema's, and generally organize our database objects easier.

4. Never use *

ColdFusion developers will be all too familiar with this rule. When using * in ColdFusion it will sometimes throw errors because field names are cached, and if a field in the table changes, ColdFusion will still try to access it based off of sed cache, throwing an error. It's good practice to take the time to write the field names out. If you change a field name, or add a field to a table it may cause your program to shat itself (if a table your joining against has that same field name).

5. Always reference objects with the schema

In your queries, you should always have the schema plugged in next to the object name. If you don't, then MS SQL Server does an unnecessary check in the database to see if the object is a built in object, as opposed to something you created.

SELECT field1 FROM dbo.MyTable

opposed to

SELECT field1 FROM MyTable

6. Use stored procedures where applicable

Creating stored procedure caches the query and 9 times out of 10 makes the query run faster in subsequent executions. Another major benefit of using stored procedures is the ability to fix a query without having to recompile code. On the flip-side, don't use stored procedures for EVERYTHING, its easy to get carried away.

7. Consider synonyms when migrating an application

If your given the unenviable task of updating an existing program, consider using synonyms. Synonyms are exactly what they imply, objects that reference other objects. If you have a table named dbo.THISisACrappyNamedTable you can create a synonym for this table, give it a better name, and eventually rename the original table once all references to it are updated.

8. Correctly set the clustered index

There are two types of indexes in MS SQL Server, clustered and unclustered. Clustered indexes are faster, and by default they are created on your primary key fields. If your primary key field isn't what gets hits the most then consider changing it to another field. Use the execution plan in a development environment to test it out and see if you get a performance boost.

9. Join tables correctly

I frequently see developers writing deprecated joins.

SELECT t.field, t2.field
FROM dbo.Table1 t, dbo.Table2 t2
WHERE t.field2 = t2.field2

Joining tables like this is deprecated and should be avoided. Instead, use the proper syntax:

SELECT t.field, t2.field
FROM dbo.Table1 t INNER JOIN dbo.Table2 t2 ON t.field2 = t2.field2

Well, that completes my first programming blog post. I was trying to hit on a bunch of topics, and will probably go more in depth in future posts.