This article we need to share our best practices inside areyoucodingenough.com, when we need to design the database especially SQL Server. We hope that it will help everyone to design the new or optimize the current one.
Shall we start ?
let’s go.
- Every designing Table should create one column for Clustered Index and this column should be Identified column or Unique column. But do you know that ? In basically. if you define one column to be a Primary Key, the column will automatic create Clustered Index for you by default.
2. When you need to create Index for table, you should to consider which columns are used for ‘Searching’ or ‘Comparing’. Or you can check which column are often to use for where, join…on, order by, group by, top and distinct.
3. Although Index can help to increase searching performance in the table, but in the other way it effects to updating data in table (included Insert, Update and Delete) . You should to make decision and evaluate the number of Index which suitable with your system.
4. Do not create Index for small table.
5. Do not create Index for column which have data type as follow TEXT, NTEXT, IMAGE.
6. Index should be Identified column or Unique column only. It will make highest performance for searching data in table.
7. Some situation if your table have many activities (select , insert, update , delete). You could utilize by temporary create the Index when you need to select the data in table, and when you finish the selection process you could drop it.
8. Do not use data type as FLOAT, REAL and DATETIME to be a Primary Key.
9. Use VARCHAR and CHAR instead of NVARCHAR and NCHAR. Because of NVARCHAR and NCHAR are stand for Unicode data as 16-bit only.
10. If a column has a limitation of value’s length or static value, you should to declare data type of the column as CHAR.
11. If a column has a flexible value or dynamic value, you should to declare data type of the column as VARCHAR.
12. For numeric value always declare data type of the column as INTEGER.
That’s all.
Just our opinion, feel free to comments.
Thanks.