SQL constraints

This SQL Server article explains the list of available indexes and constraints with an example of each and their definitions. In SQL Server, constraints play a vital role in query performance.

When you insert a record into a table in SQL Server, the server will place that row in the next available location. So, it doesn’t follow any particular order, such as based on ID, Date, or alphabetical order to insert rows. Therefore, when you fetch a record using the SELECT statement, the server has to inspect each row to get the query result. It is the most expensive and time taken process in the production environment.

For instance, if you want to SELECT the New York sales, the server has to perform a table scan to search each row for New York City. If a match exists, add that record to the result set and do the same for the remaining rows.

The following is the list of SQL Server constraints.

  1. Primary Key
  2. Foreign Key
  3. Referential Integrity
  4. Check Constraint
  5. Default Constraint
  6. Unique Constraint
  7. Clustered Index
  8. Non-Clustered Index
  9. Filtered Index
Categories SQL