Designing for performance requires making tradeoffs. To get the best write performance out of your database, you must sacrifice read performance. Before tackling database design issues for your application, it is critical to understand your goals. Do you want faster read performance? Write performance? A more understandable design?
Following are some basic truths about physical database design for SQL Server 2000 and their performance implications:
Keeping tables as narrow as possible ”that is, ensuring that the row size is as small as possible ”is one of the most important things you can do to ensure your database performs well. To keep your tables narrow, choose column data types with size in mind. Don't use an int datatype if a tinyint will do. If you have zero-to-one relationships in your tables, consider vertically partitioning your table. (See "Vertical Data Partitioning" under the "Denormalizing the Database" section later in this chapter for details on this scenario.)
Cascading deletes (and updates) causes extra lookups to be done whenever a delete runs against the parent table. In many cases, the optimizer will use worktables to resolve delete and update queries. Enforcing these constraints manually, from within stored procedures, for example, can give better performance. This is not a wholehearted endorsement against referential integrity constraints. In most cases, the extra performance hit is worth the saved aggravation of coding everything by hand. However, you should be aware of the cost of this convenience.