So, come up with an algorithm to generate a primary key in the front-end or from within the inserting stored procedure. There still could be issues with generating your own primary keys too, like concurrency while generating the key, or running out of values. So, consider both options and go with the one that suits you best.

• Minimize the use of NULLs, as they often confuse the front-end applications, unless the applications are coded intelligently to eliminate NULLs or convert the NULLs into some other form. Any expression that deals with NULL results in a NULL output. ISNULL and COALESCE functions are helpful in dealing with NULL values. Here's an example that explains the problem:
  
  Consider the following table, Customers which stores the names of the customers and the middle name can be NULL.
  
  CREATE TABLE Customers
  (
  FirstName varchar(20),
  MiddleName varchar(20),
  LastName varchar(20)
  )
  
  Now insert a customer into the table whose name is Tony Blair, without a middle name:
  
  INSERT INTO Customers
  (FirstName, MiddleName, LastName)
  VALUES ('Tony',NULL,'Blair')
  
  The following SELECT statement returns NULL, instead of the customer name:
  
  SELECT FirstName + ' ' + MiddleName + ' ' + LastName FROM Customers
  
  To avoid this problem, use ISNULL as shown below:
  
  SELECT FirstName + ' ' + ISNULL(MiddleName + ' ','') + LastName FROM Customers
  
  
• Use Unicode datatypes, like NCHAR, NVARCHAR, or NTEXT, if your database is going to store not just plain English characters, but a variety of characters used all over the world. Use these datatypes only when they are absolutely needed as they use twice as much space as non-Unicode datatypes.
  
  
• Always use a column list in your INSERT statements. This helps in avoiding problems when the table structure changes (like adding or dropping a column). Here's an example which shows the problem.
  
  Consider the following table:
  
  CREATE TABLE EuropeanCountries
  (
  CountryID int PRIMARY KEY,
  CountryName varchar(25)
  )
  
  Here's an INSERT statement without a column list , that works perfectly:
  
  INSERT INTO EuropeanCountries
  VALUES (1, 'Ireland')
  
  Now, let's add a new column to this table:
  
  
  ALTER TABLE EuropeanCountries
  ADD EuroSupport bit
  
  Now run the above INSERT statement. You get the following error from SQL Server:
  
  Server: Msg 213, Level 16, State 4, Line 1
  Insert Error: Column name or number of supplied values does not match table definition.
  
  This problem can be avoided by writing an INSERT statement with a column list as shown below:
  
  INSERT INTO EuropeanCountries
  (CountryID, CountryName)
  VALUES (1, 'England')
  
  
• Perform all your referential integrity checks and data validations using constraints (foreign key and check constraints) instead of triggers, as they are faster. Limit the use triggers only for auditing, custom tasks and validations that can not be performed using constraints. Constraints save you time as well, as you don't have to write code for these validations, allowing the RDBMS to do all the work for you.
  
  
• Always access tables in the same order in all your stored procedures and triggers consistently. This helps in avoiding deadlocks. Other things to keep in mind to avoid deadlocks are: Keep your transactions as short as possible. Touch as few data as possible during a transaction. Never, ever wait for user input in the middle of a transaction. Do not use higher level locking hints or restrictive isolation levels unless they are absolutely needed. Make your front-end applications deadlock-intelligent, that is, these applications should be able to resubmit the transaction incase the previous transaction fails with error 1205. In your applications, process all the results returned by SQL Server immediately so that the locks on the processed rows are released, hence no blocking.
  
  
• Offload tasks, like string manipulations, concatenations, row numbering, case conversions, type conversions etc., to the front-end applications if these operations are going to consume more CPU cycles on the database server. Also try to do basic validations in the front-end itself during data entry. This saves unnecessary network roundtrips.
  
  
• If back-end portability is your concern, stay away from bit manipulations with T-SQL, as this is very much RDBMS specific. Further, using bitmaps to represent different states of a particular entity conflicts with normalization rules.
  
  
• Always add a @Debug parameter to your stored procedures. This can be of BIT data type. When a 1 is passed for this parameter, print all the intermediate results, variable contents using SELECT or PRINT statements and when 0 is passed do not print anything. This helps in quick debugging stored procedures, as you don't have to add and remove these PRINT/SELECT statements before and after troubleshooting problems.
  
  
• Do not call functions repeatedly within your stored procedures, triggers, functions and batches. For example, you might need the length of a string variable in many places of your procedure, but don't call the LEN function whenever it's needed, instead, call the LEN function once, and store the result in a variable, for later use.
  
  
• Make sure your stored procedures always return a value indicating their status. Standardize on the return values of stored procedures for success and failures. The RETURN statement is meant for returning the execution status only, but not data. If you need to return data, use OUTPUT parameters.
  
  
• If your stored procedure always returns a single row resultset, consider returning the resultset using OUTPUT parameters instead of a SELECT statement, as ADO handles output parameters faster than resultsets returned by SELECT statements.
  
  
• Always check the global variable @@ERROR immediately after executing a data manipulation statement (like INSERT/UPDATE/DELETE), so that you can rollback the transaction in case of an error (@@ERROR will be greater than 0 in case of an error). This is important, because, by default, SQL Server will not rollback all the previous changes within a transaction if a particular statement fails. This behavior can be changed by executing SET XACT_ABORT ON. The @@ROWCOUNT variable also plays an important role in determining how many rows were affected by a previous data manipulation (also, retrieval) statement, and based on that you could choose to commit or rollback a particular transaction.
  
  
• To make SQL Statements more readable, start each clause on a new line and indent when needed. Following is an example:
  
  SELECT title_id, title
  FROM titles
  WHERE title LIKE '%Computer%' AND
          title LIKE '%cook%'
  
  
• Though we survived the Y2K, always store 4 digit years in dates (especially, when using cCHAR or INT datatype columns), instead of 2 digit years to avoid any confusion and problems. This is not a problem with DATETIME columns, as the century is stored even if you specify a 2 digit year. But it's always a good practice to specify 4 digit years even with DATETIME datatype columns.
  
  
• As is true with any other programming language, do not use GOTO, or use it sparingly. Excessive usage of GOTO can lead to hard-to-read-and-understand code.
  
  
• Do not forget to enforce unique constraints on your alternate keys.
  
  
• Always be consistent with the usage of case in your code. On a case insensitive server, your code might work fine, but it will fail on a case sensitive SQL Server if your code is not consistent in case. For example, if you create a table in SQL Server or a database that has a case-sensitive or binary sort order, all references to the table must use the same case that was specified in the CREATE TABLE statement. If you name the table as 'MyTable' in the CREATE TABLE statement and use 'mytable' in the SELECT statement, you get an 'object not found' error.
  
  
• Though T-SQL has no concept of constants (like the ones in the C language), variables can serve the same purpose. Using variables instead of constant values within your queries improves readability and maintainability of your code. Consider the following example:
  
  SELECT OrderID, OrderDate
  FROM Orders
  WHERE OrderStatus IN (5,6)
  
  The same query can be re-written in a mode readable form as shown below:
  
  DECLARE @ORDER_DELIVERED, @ORDER_PENDING
  SELECT @ORDER_DELIVERED = 5, @ORDER_PENDING = 6
  
  SELECT OrderID, OrderDate
  FROM Orders
  WHERE OrderStatus IN (@ORDER_DELIVERED, @ORDER_PENDING)
  
  
• Do not use column numbers in the ORDER BY clause. Consider the following example in which the second query is more readable than the first one:
  
  SELECT OrderID, OrderDate
  FROM Orders
  ORDER BY 2
  
  SELECT OrderID, OrderDate
  FROM Orders
  ORDER BY OrderDate
  

Well, this is all for now folks. I'll keep updating this page as and when I have something new to add. I welcome your feedback on this, so feel free to email me. Happy database programming!

Published with the explicit written permission of the author. Copyright 2001.