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 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.
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:
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.
Learn Blazor WebAssembly with our online courses
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.
