Three SQL Server features you may not use too often

16th July 2019

Common SQL features regularly used in your web application will include the normal CRUD operations. However, SQL Server has many more features. Here are three that I picked out that you may not use too often:

Check Constraints

Check constraints is a way that you can check the integrity of your data when you are modifying a column.

You can use the same syntax as if you writing a WHERE statement. For more complex WHERE statements, you can take advantage of SQL Server's functions.

For example, you have a Survey table which contains a Score column. The score column represents a rating of 1 to 10. You can use a check constraint to ensure that the value in the rating column is between 1 and 10.

Check Constraints in SQL Server

Index Types

When it comes to putting indexes in your database, you may not of come across the index type. You have three options for the Index Type:

  • Index
  • Unique
  • Columnstore Index

A Unique Index is basically what is says on the tin. All your columns in an index need to be unique for each row. If there are any rows that have the same data, an exception will be thrown. For indexes with multiple columns, all columns in the index have to match the same value in multiple rows for the exception to be thrown.

A Columnstore Index was introduced in SQL Server 2012. A normal index will focus on each row for speeding up the performance of a query. A Columnstore Index will do the same in speeding up the performance of the query, but will do it at column level, and would be beneficial for tables that have large amounts of data.


Triggers allow you to execute an SQL statement before or after an SQL statement has taken place.

Going back to our example of having a Rating column in a Survey table. After a rating has been modified, we may have a AverageRating column in a SurveyTotals table. That is where a trigger comes in handy. We can set up a trigger to perform this task once a new survey column has been created.

CREATE TRIGGER dbo.UpdateRating
   ON dbo.Survey
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.

  DECLARE @AverageRating FLOAT;
  SET @AverageRating = (SELECT AVG(s.Rating) FROM dbo.Survey s)

    -- Insert statements for trigger here
	UPDATE dbo.SurveyTotals SET AverageRating=@AverageRating


From a personal experience, I have not used triggers too often. If you trigger throws an exception, it can be more tricky trying to debug the error.

Nonetheless, it's a useful feature for those who require it.

About the author

David Grace

David Grace

Senior .NET web developer | ASP.NET Core | C# | Software developer

Free .NET videos

  • Do you want to watch free videos featuring .NET 7 new features?
  • How about what's new in C# 11?
  • Or a recap on the SOLID principles?
Watch our .NET videos