What is Check Constraint in SQL Server ?

Check Constraint :

A Check Constraint is used to limit the values that can be placed in a column. Check constraints are most often used to enforce domain integrity. If you define a CHECK constraint on a single column it allows only certain values for this column.

If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

1) Defining Check Constraint in a table :

CREATE TABLE Student
(
S_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Student CHECK (S_Id>0 AND City='abc')
)

2) Check Constraint while Altering a table :

ALTER TABLE Student
ADD CONSTRAINT chk_Student CHECK (S_Id>0 AND City='abc')

3) Drop a Check Constraint 

ALTER TABLE Student
DROP CONSTRAINT chk_Student

They are similar to FOREIGN KEY constraints in that they control the values that are put in a column. The difference is in how they determine which values are valid: FOREIGN KEY constraints obtain the list of valid values from another table, and CHECK constraints determine the valid values from a logical expression that is not based on data in another column. For example, the range of values for a salary column can be limited by creating a CHECK constraint that allows for only data that ranges from $15,000 through $100,000. This prevents salaries from being entered beyond the regular salary range.

You can create a CHECK constraint with any logical (Boolean) expression that returns TRUE or FALSE based on the logical operators. For the previous example, the logical expression is: salary >= 15000 AND salary <= 100000.

You can apply multiple CHECK constraints to a single column. You can also apply a single CHECK constraint to multiple columns by creating it at the table level. For example, a multiple-column CHECK constraint can be used to confirm that any row with a country/region column value of USA also has a two-character value in the state column. This allows for multiple conditions to be checked in one location.