Here are some of the typical things that all SQL Server DBAs and Database developers should know. These rules are above and beyond the most basic textbook recommendations of:
- Ensuring your databases are Normalized and in 3rd Normal Form
- Making sure you have primary keys, foreign keys and simple indexes to improve performance
- Making sure you Back up regularly
- Basic Naming conventions (see some of our object naming conventions)
- Minimizing result set sizes and data over the wire
View Database Coding Standard and Guideline.
Want to develop your SQL Server Database with SSW? Check SSW's Databases consulting page.
Imagine an e-commerce company called Northwind Traders, where thousands of products, multiple sellers, and millions of customers converge. In such a company, the importance of a well-designed database cannot be overstated.
Consider the scenario where a customer places an order only to encounter delays and frustration due to data inconsistencies that wrongly indicate an out-of-stock product. To mitigate such chaos, developers rely on relational database design principles, including normalization and entity-relationship diagrams (ERDs).
These concepts provide the foundation for a well-structured database with improved data integrity and reduced redundancy, ensuring smooth operations and customer satisfaction.
Database Normalization is a systematic approach to designing databases that reduces redundancy and avoids potential anomalies during data update, deletion, or insertion. This process involves organizing the columns (attributes) and tables (relations) of a database to ensure their dependencies are properly enforced by database integrity constraints.
Relational databases are complicated, and understanding the entire architecture of a database can be difficult when expressed solely in words. That's where Entity Relationship Diagrams (ERDs) come in. ERDs are a way of visualizing the relationships between different entities and their cardinality.
NULLs complicate your life. To avoid having to constantly differentiate between empty strings and NULLs, you should avoid storing NULLS if you can. Why? Well, what is wrong with this?
NULLs create difficulty in the middle-tier because you need to add further handling. So avoid them where you can, eg. For a Discount field, make the default 0 and don't allow NULLs.
Text in character columns (char, varchar, text, nchar, varchar, text) can start with spaces or empty lines which is usually data entry error.
The best way to avoid this issue is to handle whitespace in the middle-tier before it reaches the database.
When users are deleting a lot of records as part of normal operations - they can and do make mistakes. Instead of the painful process of having to go to a backup to get these records, why not simply flag the records as IsDeleted?
SQL Server dates can range from the year 1900 up to the year 9999. However, certain date data in your database just wouldn't make any sense in the context of your business. For example, if your company started trading in 2015 you should not have any dates in your database before 2015 (unless you are tracking start dates of your clients, but this is an exception). An invoice date of 2013 wouldn't make sense at all.
There are two methods to avoid this:
Any DateTime fields must be converted to universal time from the application to the stored procedures when storing data into the database.
We can simplify dealing with datetime conversions by using a date and time API such as Noda TIme.
- Do you understand relational database design?
- Do you normalize your database?
- Do you visualize your database with an ERD?
- Data - Do you not allow Nulls in text fields?
- Data - Do you not allow NULLs in number fields if it has the same meaning as zero?
- Data - Do you avoid spaces and empty lines at the start of character columns?
- Data - Do you use Identities in SQL Server?
- Data - Do you avoid deleting records by flagging them as IsDeleted (aka Soft Delete)?
- Data - Dates - Do you make sure you have valid date data in your database?
- Data - Dates - Do you know DateTime fields must be converted to universal time?
- Data - Do you use temporal tables to audit data changes?
- Data - Do you avoid invalid characters in object identifiers?
- Do you have a general Contact Detail table?
- Data - Do you use a URL instead of an image in your database?
- Schema - Do you only use Unicode datatypes (nchar, nvarchar and ntext) in special circumstances?
- Schema - Do you always use Varchar?
- Schema - Do you have standard tables and columns?
- Schema - Do you use Bit/Numeric data type correctly?
- Schema - Do you use Natural or Surrogate primary keys?
- Schema - Do you create primary key on your tables?
- Schema - Do you create clustered index on your tables?
- Schema - Do you avoid using indexes on RowGuid column?
- Schema - Do you have a rowversion column?
- Schema - Do you use FillFactor of 90% for indexes and constraints?
- Schema - Do you always have version tracking tables?
- Schema - Do you use computed columns rather than denormalized fields?
- Schema - Do you use triggers for denormalized fields?
- Do you validate each "Denormalized Field"?
- Schema - Do you avoid using user-schema separation?
- Schema - Do you create a consistent primary key column on your tables?
- Schema - Do you use separate lookup tables rather than one large lookup table for your lookup data?
- Schema - Do you know how to provide best database schema document?
- Schema - Do you add zs prefix to system tables?
- Views - Do you avoid having views as redundant objects?
- General - Do you know every object name should be owned by dbo?
- Stored Procedures - Do you keep your Stored Procedures simple?
- Stored Procedures - Do you return a value indicating the status?
- Stored Procedures - Do you standardize the return values of stored procedures for success and failures?
- Stored Procedures - Do you use OUTPUT parameters if you need to return the value of variables?
- Stored Procedures - Do you check the global variable @@ERROR after executing a data manipulation statement?
- Stored Procedures - Do you use SCOPE_IDENTITY() to get the most recent row identity?
- Stored Procedures - Do you SET NOCOUNT ON for production and NOCOUNT OFF off for development/debugging purposes?
- Stored Procedures - Do you avoid starting user stored procedures with system prefix "sp_" or "dt_"?
- Stored Procedures - Do you avoid using SELECT * when inserting data?
- Stored Procedures - Do you use transactions for complicated stored procedures?
- Stored Procedures - Do you use error handling in your Stored Procedures?
- Stored Procedures - Do you know SQL stored procedure names should be prefixed with the owner?
- Relationships - Do you turn on referential integrity in relationships?
- Relationships - Do you use Update Cascade when creating a relationship?
- Relationships - Do you avoid using Cascade Delete?
- Relationships - Do you set Not For Replication when creating a relationship?
- Relationships - Do you have FOREIGN KEY constraints on columns ending with ID?
- General - Do you know object name should not be a reserved word?
- General - Do you know object name should not contain spaces?
- General - Do you know to not use "sp_rename" to rename objects?
- General - Do you know object name should follow your company naming conventions?
- Do you use a SQL Server object naming standard?
- General - Do you use a SQL Server Stored Procedure Naming Standard?
- General - Do you use a SQL Server Indexes Naming Standard?
- Do you use a SQL Server Relationship Naming Standard?
- Do you know the naming convention for use on database server test and production?
- Middle Tier - Do you implement business logic in middle tier?
- Do you parameterize all input to your database?
- Views - Do you use SQL Views?
- SQL Server - Do you know the different ways to query data?