Database designing is a very vast and crucial phase. If you belong to group who goes with Database First Approach, then any decision you make in phase may have huge impacts. I would like to highlight few important points which can be worthy to consider for better database designing and performance.
- Always try to device a naming convention and few standards like field size, datatypes, and general assumptions before you start designing database. These can save lot of time and efforts. These can even play vital role in performance.
- Consider Demoralization, particularly for very large data. But as rule of thumb, first Normalize your Database. It is a good idea to use Indexed view for Demoralization.
- It will be better to define Primary Key and Foreign Key Relationships.
- Clearly finalize Database Isolation Level.
- Clearly define sequence of involvement to tables in transaction to avoid deadlocks.
- It is good practice to have one Identity Column in a table, either it is primary key or not.
- Precisely plan Indexing, and keep in mind, it is ongoing process. Be specific to choose column for clustered and non clustered indexes. In general usage of columns in join, where, order by and group by directly affect your index requirements.
- You need a good plan for index de-fragmentation, particularly when there are lots of insert, delete and update operations.
- Consider new technologies and out of box solution. In general, I may prefer out of box features instead using old custom methodologies until and unless there is some specific reason to do so like backward compatibility.
- Consider built in Table Partitioning feature instead of manual table partitioning for large data tables.
- Consider File Stream introduced in SQL Server 2008 for File Storage.
- Consider new DataTypes over old wherever possible, like Varchar(max) over Text, Date over DateTime and etc.
- Consider In Memory Optimized Tables. Now RAM is very cheap, and such data cache techniques can make a great difference in performance.
- Consider splitting database into multiple files based on your database size.
- Consider separation of database files and log files on different hard disk.
- Always use appropriate datatype, for example, if you have to store English data only then you don't need to use nvarchar, or nchar, if you need to store small value set, then there is no need to use bigint.
- Consider Summary Table or Pre Calculated Data for very large data.
- Plan a clear strategy for performance and maintenance. In many cases you have to address Performance vs Maintenance. Ideally you must go with some acceptable combination of both, but this decision is directly dependent on your needs and budget.
- It will be better to keep same standard for Database objects (Table and Column) and Application Data Model. Although it may not give any performance gain, but it can facilitate developers.
- If you are planning to use Triggers or Cursors, then revaluate alternate approaches.