Be Careful When Creating a Table

Creating a table is not a big task, but a well designed table is basic necessity of every application, as whole application performance in future will be based on these tables. Before creating table, go through the process of normalization and keep in mind that your first goal is PERFORMANCE by using fewer resources. Normalize your tables not beyond third normal form, and de-normalize it when its necessary, as de-normalization of data is as important as normalization is.

Table Naming

• Pascal notation, end your table name with an ‘s’ (like, Orders, ErrorMessages, Products)
• Do Not use spaces in name
• Do not use SQL keywords as the name (like, user is a keyword so avoid using keywords)
• More natural way of naming tables is a prefix tbl before its name (like tblCustomers).

Columns

• Like table, no spaces, no keyword should be used
• If column is primary key column, name should be like TABLENAME+’ID’ (like CustomerID)
• Be careful when selecting data type
• Don’t use the NVARCHAR or NCHAR data types unless you need to store 16-bit character Unicode data as it takes double space as VARCHAR or CHAR data types and implicit conversion from NVARCHAR or NCHAR to VARCHAR or CHAR adversely reduce performance.
• Be sure about your need when it comes to size to data type. Always select the smallest data type which meets your need. For example, if all you are going to store 1 to 10 use TINYINT. When INT (which takes 4 bytes) can covers your future data don’t select BIGINT (which takes 8 bytes)
• Sorting an INTEGER data type is faster then VARHCAR or CHAR, so use INTEGER data types when data is numeric.

NOT NULL property

• Avoid keeping a column default to NULL. A column should always contain something as indexes don’t cover the NULL values.

SELECT * FROM table WHERE column IS NULL

(above query will use full table scan since index doesn’t cover the values you need)

SELECT column FROM table ORDER BY column

(even for ORDER BY clause full table scan will be used)

Primary Key

• Table must not be a HEAP table
• Each table must have a primary key, and for best performance it should be created on column which has INT data type.
• Never use VARCHAR, FLOAT, REAL, DATE and GUID data type columns as Primary Key
• Add a surrogate key, when composite key is selected as data identity.

Referential Integrity Cascade DELETE/UPDATE

• While creating a Foreign Key constraint, enforce cascade delete or cascade update when you want to delete a child when ever you delete its parent. If the child rows are "part" of the parent, then use cascade. Like if you have an employee and a child names table and you never want to warn to delete child record while deleting employee data.
• Using cascade delete is good to enforce as compared to create a TRIGGER for this purpose, as triggers never fire when you use TRUNCATE TABLE, but in cascade case TRUNCATE will return an error.
• When you are not sure for above mentioned points, never ever use cascade delete/update as it creates more problems then it facilitates you.

Computed Columns

• Computed columns don’t follow the normalization rules but if a column beneficial when some sort of data is computed by a query and this query is being run again and again.

Default Value

• Always provide a default value when column is NOT NULL

Description of column

• Keep you table we documented by providing each column description through design view or using following t-sql.

EXECUTE sp_addextendedproperty N'MS_Description', 'your column description here', N'SCHEMA', 'schemaname', N'TABLE', 'tablename', N'COLUMN', 'columnname'