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

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
   AFTER INSERT, UPDATE, DELETE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

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

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

END
GO

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.

Want More ASP.NET Core Coding Tutorials?

Subscribe to my YouTube channel to get more ASP.NET Core coding tutorials.

You'll get videos where I share my screen and implement a how-to guide on a topic related to ASP.NET Core.

You can expect to see videos from the following technologies:

  • Blazor
  • Web APIs
  • SQL Server
  • Entity Framework
  • SignalR
  • and many more...

By subscribing, you can get access to all my ASP.NET Core coding tutorials completely free!

And so you never miss out on a new video, you have the option to be notified every time a new video is published.

So what are you waiting for?

To subscribe, click here to view my YouTube channel, and click on the red "Subscribe" button.

David Grace

David Grace

I am a .NET developer, building web applications in .NET Framework and .NET Core with a SQL Server database.

Some of the .NET packages I have used include Entity Framework and MVC.

I've also used many JavaScript frameworks such as React and jQuery, and have experience building CSS in SASS.

Twitter Feed