1.Never use embedded T-SQL; always use stored procedures.

3.       Always obtain execution plan to find table scans or inefficient queries.

4.       Don’t return rows that you don’t need. Use the WHERE clause, SELECT TOP or use SET ROWCOUNT X. In SQL 7.0 always use SELECT TOP instead or ROWCOUNT. In SQL 6.5 when setting ROWCOUNT always set it to 0 again after the query.

5.       Before executing queries check whether the required values have been passed.

6.        Avoid using wildcard (LIKE %) text searches, specially with % at the beginning.

7.        Don’t use “ = NULL”, use “IS NULL”.

8.       Always add a header with details like a description, author, date, modifications, etc.

9.       Use in-line comments when possible. This will make it easier for someone else to understand the queries.

10.    Avoid all use of cursors.

11.    If possible avoid negative predicates (use of NOT) which always cause a table or index scan.

12.    Use the IN keyword in your WHERE clause to specify several conditionals instead of using several OR statements. For example, use this:

WHERE cCustomerState IN (’CT’, ‘NY’, ‘NJ’)

Instead of this:

WHERE cCustomerState = ‘CT’ OR cCustomerState = ‘NY’ OR cCustomerState = ‘NJ’

13.    If you have a trigger on a table that is likely to be bulk changed, do include all code in the trigger rather than calling a stored procedure from it. This will perform better than recurrently calling the SP for each changed record.

Further reading:

-          Transact-SQL SQL Server Performance Tuning Tips.

http://www.sql-server-performance.com/transact_sql.asp.

< ?xml:namespace prefix ="" o ns ="" "urn:schemas-microsoft-com:office:office" />

When using the FOR XML clause in your Transact-SQL applications, don’t include the XMLDATA option. The XMLDATA option returns additional XML schema data that generally is not needed. Because of this, using this option adds extra overhead to your server and network connection, reducing performance. [2000] Added < ?xml:namespace prefix ="" st1 ns ="" "urn:schemas-microsoft-com:office:smarttags" />11-16-2000

*****

The OPENXML function in SQL Server 2000 can be useful for processing multiple table inserts within a single database call, reducing overhead and boosting performance. The ability to map an XML document to a rowset representation of a specified portion of the XML document within a stored procedure can maximize the efficiency with which repetitive type inserts are accomplished. [2000] See related article Added 12-27-2000

*****

As you may know, it can be a performance drag on SQL Server and your web server if you SELECT the same data over and over from SQL Server in order to dynamically create web pages. For example, say that you need to display some text on your webpage that is stored in SQL Server, but that it only changes every couple of days. Currently, you are running a SELECT statement from a stored procedure to retrieve the text each time the page is dynamically created and displayed. This can significantly hurt performance, especially if you are getting many page views a second.

One way to help avoid this problem, and to boost SQL Server and your web server’s performance, is to use SQL Server and XML to periodically re-create static content (such as once an hour, once a day, etc.) instead of dynamically pulling the content each time it is needed from SQL Server, and storing this static content on the web server. This assumes, of course, that the data doesn’t change often, as in our example. This method still allows the page to be created dynamically, but when the text is needed to be inserted into the page, it is retrieved locally off the web server, not from SQL Server each time it is needed, reducing overhead and boosting performance. Click here to see an article on how to do this. [2000] Added 1-5-2001

*****

SQL Server 2000 offers three types of FOR XML queries. They include RAW, AUTO, and EXPLICIT. Each has performance pros and cons.

The RAW type offers the best overall performance, especially if you will be moving a lot of data. The disadvantage of this is that not all XML-based applications is able to use the format the RAW type returns.

The AUTO type offers the next best overall performance, and many more XML-based applications are able to use the format returned, unless of course your application requires XML data in a predefined format.

If your application has to accept data in a predefined format, then you have to use the EXPLICIT type, which is generally the slowest performing option. [2000] Added 5-9-2002

*****

If you need to use the EXPLICIT type of FOR XML query, in some cases you can boost performance if you replace the EXPLICIT FOR XML query with an XPath query instead. XPath queries are faster, and in most, but not all cases, can replace the functionality of a EXPLICIT FOR XML query. [2000] Added 5-9-2002

*****

If you have installed SQLXML 2.0 on your SQL Server 2000 server, you can take advantage of a new feature that can significantly affect SQL Server’s performance. SQLXML 2.0 supports what is called the Client Side for XML. What this feature does is to move the conversion from the SQL rowsets to XML at the client, or middle-tier, instead of on the SQL Server. This can significantly reduce the load on SQL Server, helping to boost its performance. Of course, the conversion still has to be done somewhere, and resources on the client or middle-tier must be used to do this. This performance-enhancing feature is especially useful to those whose SQL Server is already operating at full capacity and the workload need to be off-loaded to other servers. [2000] Added 5-9-2002

*****

SQLXML 2.0 offers three options to use XML documents to modify a SQL Server database. They include OPENXML, Updategrams, and Bulk Load. Each of these have their own performance-related pros and cons.

As you might guess, the best performance is offered by using Bulk Loads, assuming that it will work for you. If you need to insert very large numbers of XML documents as defined by a mapping schema, then Bulk Loads can be very efficient. But in many instances you can’t use the Bulk Load option because it is only limited to INSERTs, not other types of data modifications.

The next most efficient way to use XML documents to modify a SQL Server database is to use the Updategrams option. Updategrams are more efficient that OPENXML because all XML parsing and SQL statement generation is not performed by SQL Server, but in the client or middle-tier. Like Bulk Loads, Updategrams can’t be used in all instances. Updategrams usually require a mapping schema and also require that XML input be in a special format, which may or may not be practical.

The least efficient, but most flexible way to modify SQL Server data using XML documents, is to use OPENXML. The performance problem is a result that an OPENXML stored procedure is parsed into a DOM by a parser that runs in the SQL Server process, and uses SQL Server memory until it is released. While OPENXML works OK for low-volume applications, it does not scale well. [2000] Added 5-9-2002

*****

Here are some ways you can help to maximize the performance of Updategrams:

If you use the sql:key-fields annotation, be sure you select a primary key for each table. If you don’t, all columns in the before element are included in the WHERE clause, and can cause the search to take much longer than necessary.

If you are going to update or delete data a record, and you don’t care what the before value is, you can help boost performance of Updategrams by not including all the columns included in the before element in the WHERE clause.

If an Updategram is used to make unrelated updates, the updates should be split into separate synch blocks. This reduces the transaction size, and helps to boost performance.

[2000] Added 5-9-2002

*****

The “Advanced” tab of the “Configure SQLXML Support in IIS” configuration dialog box has a check box to turn “caching” on and off. When on (the default option), allows Mapping Schemas to be cached and used over and over, helping to boost overall performance. Don’t turn this option off, unless you are in a development or testing environment. [2000] Added 5-9-2002

Further reading:

-          Transact-SQL SQL Server Performance Tuning Tips.

http://www.sql-server-performance.com/transact_sql.asp.

-          Retrieving Dynamic XML from SQL Server 7.0 and 2000

http://www.15seconds.com/issue/001102.htm

< ?xml:namespace prefix ="" o ns ="" "urn:schemas-microsoft-com:office:office" /> 

Databases are the heart and soul of many enterprise applications, and it is very essential to pay special attention to database programming. I’ve seen in many occasions where database programming is overlooked, thinking that it’s something easy that be done by anyone. This is wrong.

For a better performing database you need a real DBA and a specialist database programmer, let it be for Microsoft SQL Server, Oracle, Sybase, DB2 or whatever! If you don’t use database specialists during your development cycle, databases often end up becoming the performance bottleneck. I decided to write this article in order to put together some of the database programming best practices so that my fellow DBAs and database developers can benefit!

Here are some programming guidelines and best practices, keeping quality, performance and maintainability in mind. This list many not be complete at this moment, and will be constantly updated. BTW, special thanks to Tibor Karaszi (SQL Server MVP) and Linda (lindawie) for taking time to read this article and providing suggestions.

• Decide upon a database naming convention, standardize it across your organization, and be consistent in following it. It helps make your code more readable and understandable.
• Make sure you normalize your data at least to the 3rd normal form. At the same time, do not compromise on query performance. A little bit of denormalization helps queries perform faster.
• Write comments in your stored procedures, triggers and SQL batches generously, whenever something is not very obvious. This helps other programmers understand your code clearly. Don’t worry about the length of the comments, as it won’t impact the performance.
• Do not use SELECT * in your queries. Always write the required column names after the SELECT statement, like:

SELECT CustomerID, CustomerFirstName, City

This technique results in reduced disk I/O and better performance.

• Try to avoid server side cursors as much as possible. Always stick to a ’set-based approach’ instead of a ‘procedural approach’ for accessing and manipulating data. Cursors can often be avoided by using SELECT statements instead.

If a cursor is unavoidable, use a WHILE loop instead. I have personally tested and concluded that a WHILE loop is always faster than a cursor. But for a WHILE loop to replace a cursor you need a column (primary key or unique key) to identify each row uniquely. I personally believe every table must have a primary or unique key.

• Avoid the creation of temporary tables while processing data as much as possible, as creating a temporary table means more disk I/O. Consider using advanced SQL, views, SQL Server 2000 table variable, or derived tables, instead of temporary tables.
• Try to avoid wildcard characters at the beginning of a word while searching using the LIKE keyword, as that results in an index scan, which defeats the purpose of an index. The following statement results in an index scan, while the second statement results in an index seek:

  SELECT LocationID FROM Locations WHERE Specialities LIKE ‘%pples’
  SELECT LocationID FROM Locations WHERE Specialities LIKE ‘A%s’

  Also avoid searching using not equals operators (<> and NOT) as they result in table and index scans.

• Use ‘Derived tables’ wherever possible, as they perform better. Consider the following query to find the second highest salary from the Employees table:

  SELECT MIN(Salary) 
  FROM Employees 
  WHERE EmpID IN
  (
  SELECT TOP 2 EmpID 
  FROM Employees 
  ORDER BY Salary Desc
  )

  The same query can be re-written using a derived table, as shown below, and it performs twice as fast as the above query:

SELECT MIN(Salary) 
FROM 
(
SELECT TOP 2 Salary 
FROM Employees 
ORDER BY Salary DESC
) AS A

This is just an example, and your results might differ in different scenarios depending on the database design, indexes, volume of data, etc. So, test all the possible ways a query could be written and go with the most efficient one.

• While designing your database, design it keeping “performance” in mind. You can’t really tune performance later, when your database is in production, as it involves rebuilding tables and indexes, re-writing queries, etc. Use the graphical execution plan in Query Analyzer or SHOWPLAN_TEXT or SHOWPLAN_ALL commands to analyse your queries. Make sure your queries do an “Index seek” instead of an “Index scan” or a “Table scan.” A table scan or an index scan is a very bad thing and should be avoided where possible. Choose the right indexes on the right columns.
• Prefix the table names with the owner’s name, as this improves readability and avoids any unnecessary confusion. Microsoft SQL Server Books Online even states that qualifying table names with owner names helps in execution plan reuse, further boosting performance.
• Use SET NOCOUNT ON at the beginning of your SQL batches, stored procedures and triggers in production environments, as this suppresses messages like ‘(1 row(s) affected)’ after executing INSERT, UPDATE, DELETE and SELECT statements. This improves the performance of stored procedures by reducing network traffic.
• Use the more readable < ?xml:namespace prefix ="" st1 ns ="" "urn:schemas-microsoft-com:office:smarttags" />ANSI-Standard Join clauses instead of the old style joins. With ANSI joins, the WHERE clause is used only for filtering data. Where as with older style joins, the WHERE clause handles both the join condition and filtering data. The first of the following two queries shows the old style join, while the second one shows the new ANSI join syntax:

  SELECT a.au_id, t.title 
  FROM titles t, authors a, titleauthor ta
  WHERE 
  a.au_id = ta.au_id AND
  ta.title_id = t.title_id AND 
  t.title LIKE ‘%Computer%’

  SELECT a.au_id, t.title
  FROM authors a 
  INNER JOIN
  titleauthor ta 
  ON 
  a.au_id = ta.au_id
  INNER JOIN
  titles t
  ON
  ta.title_id = t.title_id
  WHERE t.title LIKE ‘%Computer%’

• Do not prefix your stored procedure names with “sp_”. The prefix sp_ is reserved for system stored procedure that ship with SQL Server. Whenever SQL Server encounters a procedure name starting with sp_, it first tries to locate the procedure in the master database, then it looks for any qualifiers (database, owner) provided, then it tries dbo as the owner. So you can really save time in locating the stored procedure by avoiding the “sp_” prefix.
• Views are generally used to show specific data to specific users based on their interest. Views are also used to restrict access to the base tables by granting permission only on views. Yet another significant use of views is that they simplify your queries. Incorporate your frequently required, complicated joins and calculations into a view so that you don’t have to repeat those joins/calculations in all your queries. Instead, just select from the view.
• Use User Defined Datatypes if a particular column repeats in a lot of your tables, so that the datatype of that column is consistent across all your tables.
• Do not let your front-end applications query/manipulate the data directly using SELECT or INSERT/UPDATE/DELETE statements. Instead, create stored procedures, and let your applications access these stored procedures. This keeps the data access clean and consistent across all the modules of your application, and at the same time centralizing the business logic within the database.
• Try not to use TEXT or NTEXT datatypes for storing large textual data. The TEXT datatype has some inherent problems associated with it. For example, you cannot directly write or update text data using the INSERT or UPDATE statements. Instead,  you have to use special statements like READTEXT, WRITETEXT and UPDATETEXT. There are also a lot of bugs associated with replicating tables containing text columns. So, if you don’t have to store more than 8KB of text, use CHAR(8000) or VARCHAR(8000) datatypes instead.
• If you have a choice, do not store binary or image files (Binary Large Objects or BLOBs) inside the database. Instead, store the path to the binary or image file in the database and use that as a pointer to the actual binary file stored elsewhere on a server. Retrieving and manipulating these large binary files is better performed outside the database, and after all, a database is not meant for storing files.
• Use the CHAR data type for a column only when the column is non-nullable. If a CHAR column is nullable, it is treated as a fixed length column in SQL Server 7.0+. So, a CHAR(100), when NULL, will eat up 100 bytes, resulting in space wastage. So, use VARCHAR(100) in this situation. Of course, variable length columns do have a very little processing overhead over fixed length columns. Carefully choose between CHAR and VARCHAR depending up on the length of the data you are going to store.
• Avoid dynamic SQL statements as much as possible. Dynamic SQL tends to be slower than static SQL, as SQL Server must generate an execution plan every time at runtime. IF and CASE statements come in handy to avoid dynamic SQL. Another major disadvantage of using dynamic SQL is that it requires users to have direct access permissions on all accessed objects, like tables and views. Generally, users are given access to the stored procedures which reference the tables, but not directly on the tables. In this case, dynamic SQL will not work. Consider the following scenario where a user named ‘dSQLuser’ is added to the pubs database and is granted access to a procedure named ‘dSQLproc’, but not on any other tables in the pubs database. The procedure dSQLproc executes a direct SELECT on titles table and that works. The second statement runs the same SELECT on titles table, using dynamic SQL and it fails with the following error:

  Server: Msg 229, Level 14, State 5, Line 1
  SELECT permission denied on object ‘titles’, database ‘pubs’, owner ‘dbo’.

  To reproduce the above problem, use the following commands:

  sp_addlogin ‘dSQLuser’
  GO
  sp_defaultdb ‘dSQLuser’, ‘pubs’
  USE pubs
  GO
  sp_adduser ‘dSQLUser’, ‘dSQLUser’
  GO
  CREATE PROC dSQLProc
  AS
  BEGIN
  SELECT * FROM titles WHERE title_id = ‘BU1032′ –This works
  DECLARE @str CHAR(100)
  SET @str = ‘SELECT * FROM titles WHERE title_id = ”BU1032”’
  EXEC (@str) –This fails
  END
  GO
  GRANT EXEC ON dSQLProc TO dSQLuser
  GO

  Now login to the pubs database using the login dSQLuser and execute the procedure dSQLproc to see the problem.

• Consider the following drawbacks before using the IDENTITY property for generating primary keys. IDENTITY is very much SQL Server specific, and you will have problems porting your database application to some other RDBMS. IDENTITY columns have other inherent problems. For example, IDENTITY columns can run out of numbers at some point, depending on the data type selected; numbers can’t be reused automatically, after deleting rows; and replication and IDENTITY columns don’t always get along well.

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 cannot 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 in case 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 result set, consider returning the result set using OUTPUT parameters instead of a SELECT statement, as ADO handles output parameters faster than result sets 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 behaviour 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

Naming conventions

 Stored Procedures

Stored procedure names should conform to the following convention:

AppName_ActionName< ?xml:namespace prefix ="" o ns ="" "urn:schemas-microsoft-com:office:office" />

e.g. HES_GetOfferDetails

Additional Information Within Stored Procedures

Each stored procedure should include supplementary information that covers:

·         Author and creation date

·         Short description of what the stored procedure does

·         Description of input parameters (when not too obvious). Default values can be specified in the format: “[12]”.

·         Where it is used (platform, section, etc)

If any modifications are made to an existing stored procedure then the following additional information should be provided each time:

·         Author of the modifications and the date they were made

·         Short description of the changes made

Example header (with modifications):

Tables

a)     Table names

Use a descriptive name use Pascal notation. Tables will normally be one or more nouns that should be expressed in plural. E.g. AccommodationTypes, Offers.

When creating a table that link two other tables use the form <table1>To<table2>. E.g. MasterToDetail.

b)     Fields

When naming fields within tables, the following method should be used when naming primary and foreign keys:

In the table with the primary key: PrimaryTable (TableNameID, FieldName1, FieldName2…)

In the table with the foreign key: FKTable (PrimaryTableID, FieldName1, FieldName2…)

Use the singular form for the primary key field; i.e. use OfferID instead of OffersID.

For example if the table Users had a primary key called UserID and this was used as a foreign key in the table Addresses you should use:

c)      Indexes and foreign keys

With respect to Indexes and Foreign keys the following conventions should be adopted:

Index                     -               IX_<TableName>_<ColumnName1>_<ColumnName2>…

Foreign Key         -               FK_<FKTable>_<PrimaryTable>

Idx and FK are prefixes indicating an index and a foreign key respectively, TableName is the name of the table concerned and ColumnName is the name of a column that is included.

PrimaryTable is the table that contains the primary key and FKTable is the table that contains the foreign key. Use abbreviations when the table names are too long. 

Examples of valid foreign keys and indexes for this example:

-          IX_HolidayOffers_Price_Nights: index on fields Price and Nights from HolidayOffers table.

-          FK_HolsOffersToOfferTypes_OfferTypes: Foreign key from HolsOffersTo OfferTypes to OfferTypes.

Views

Use a descriptive name in Pascal notation.

3         Annex A – Pascal notation

The first letter in the identifier and the first letter of each subsequent concatenated word are capitalized. You can use Pascal case for identifiers of three or more characters.

For example: BackColour, TradingName, TelephoneSet.

·        Developer: Acquire a good understanding of the effect a query has on the underlying database.< ?xml:namespace prefix ="" o ns ="" "urn:schemas-microsoft-com:office:office" />

·        DBA: Perform regression testing and trend-analysis when moving any code (in application or database) from development servers to QA/test servers and onto production.

·        DBA: Make sure proper indexes are in place.

·        DBA: Make sure each table has a clustered index.

·        Developer: Making sure to review complex queries with the DBA.

·        DBA: Use read-only copies of databases to accommodate heavy reporting requirements during business hours or limit certain resource intensive queries to off-hours.

·        Developer: Make sure to use read-only, forward-only, result sets whenever possible, and fetch all result set data in the application immediately.

·        DBA: Make sure you are aware of developer requirements, the developer tools being used, and provide feedback to catch potential problems early.

·        DBA: Make sure to select the RAID solution that best supports your needs.

·        DBA: Make sure there is sufficient memory on the server.

·        DBA: Proactively monitor the server hardware to make sure it can handle any increasing loads.

·        DBA and Developer: Speak with the end-users to make sure they are happy with performance.

·        DBA and Developer: Don’t assume pre-packaged applications (like customized CRM applications, SQL access or reporting tools) generate efficient SQL.

·        DBA and Developer: Proactively monitor SQL activity on SQL Server.

·        DBA and Developer: Avoid SQL cursors whenever possible.

·        DBA and Developer: Keep SQL transactions as short as possible.

·        DBA: Avoid running extraneous services on the server running SQL Server.

·        DBA: Make sure that Microsoft’s Internet Information Server (IIS) or other web servers are on a different “box” than SQL Server.

·        DBA and Developer: Use stored procedures for data access wherever possible.