Basic Tenets of Designing for Performance

    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:
     Keep table row sizes as small as possible. This is not about saving disk space; smaller rows mean more rows will fit on a single 8KB page, which means less physical disk reads are required to read a given quantity of rows.
     Use indexes to speed up read access, but      The more indexes that a table has, the longer it takes to insert, update, and delete rows from that table.
     Using triggers to perform any kind of work during an insert, update, or delete will exact a performance toll, and decrease concurrency by lengthening transaction duration.
     Implementing declarative referential integrity (primary and foreign keys) helps maintain data integrity, but enforcing foreign key constraints requires extra lookups on the primary key table to ensure existence.
     Using ON DELETE CASCADE referential integrity constraints likewise helps maintain data integrity, but requires extra work on the server's part.

    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 inyour tables, consider vertically partitioning your table. (See "Vertical Partitioning"under the "Denormalizing the Database"section for details on this scenario.)