SQL Server Best Practices – Series 1

SQL Server Best Practices – Series 1

1.1 Table Design

The ideal table design for efficiency is to have one long integer ID column in every table acting as the primary key. This violates one of the design principles of normalisation, but the speed gain is so huge compared to the purely theoretical disadvantage that it’s virtually a no-brainer decision to make.

Less ideal is having a primary key consisting of multiple ID columns. It will still be fairly efficient, but not as good as the single column.

Do not use anything other than ID columns in the primary key. Using real data in the primary key implies that it may be changed by the user. This will violate any foreign key constraints placed on the key and generally cause havoc. Keys work best when the values never change once they are created. If you want to ensure that a particular data column is unique, just define a separate unique constraint on it.

Always define an index to match every foreign key definition on the table. This will virtually guarantee efficient joins in all queries.

If a primary key or unique constraint consists of multiple columns, and you find the need to search on only one of them, then define a separate index on it.

Do not be afraid of defining lots of indexes. More indexes do indeed slow down the speed of inserting into the table, but our databases are mostly used for querying and reporting rather than lots of high-speed transactions, so this is not too much of an issue. On the other hand, do not define indexes on columns that are not searched on – this simply wastes processing time needlessly.

A primary key must always be defined on every table. In the vast majority of cases, this should also be the clustered index. You will see a large performance gain from having a clustered primary key. Note that this is different advice to previous DBMSs.

1.2 Temporary Tables

At the moment in Sirius code there are a wide variety of methods used to extract and process temporary data for running reports. Most of these methods either do not support multiple users or have a large performance impact, so here are two standard methods of building up temporary data which overcome these problems. Everyone should use only these two methods from now on, regardless of the database version you are working on.

1.2.1 Inside A Procedure

This method is suitable for generating, processing, and using data within one stored procedure. If you require a set of temporary data that can be persisted across multiple procedures, see the next section.

1. At the start of the procedure, create the table with a # (single hash) prefix and give it any name you like. The table is private to that particular procedure instance, and will never conflict with any table created by another procedure. Data cannot be shared with other procedures that may implement a table with the same name. You can also create indexes on it to speed up querying if you wish.
2. Manipulate this table in whatever way you choose.
3. At the end of the procedure, drop the table.

1.2.2 Between Procedures

This method is suitable for persisting data across multiple procedures, but can also be used inside one procedure if you like.

1. In the database, create a permanent table to hold your data. Call it what you like, but give it a Temp prefix to distinguish it from normal tables.
2. The table must have the following two columns defined before any others in the table.  ID integer IDENTITY,  SessionID integer NOT NULL, The ID column must be designated as the primary key. The SessionID column must be indexed.
3. Before inserting any data into the table, generate a unique session ID.
4. When inserting data into the table, insert the unique session ID value that you generated earlier. This allows multiple users to use the same temporary table at once without their results conflicting.
5. When querying the table, filter the rows on your unique session ID.
6. When you have finished with the table, delete only those rows belonging to your unique session ID.
7. The table itself should never be cleared down unconditionally, dropped, or created. It is a permanent table in the database and should always exist.

Regards

Bhagwan Singh Jatav

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook