What is check constraint in SQL SERVER?

Check constraint is the constraint on the column where you can restrict the value in columns as per data type or some numeric value. But we can not apply check constrains on null value i.e. if you are inserting the null value in column that will get inserted in the column where you have put some check constraint.

·         For check constraint there should be at least one row in the table on which constrain can be specified.
·         Delete operation doesn’t s validates check constraint. 

1       Create Check Constraint:

CREATE TABLE AvadTemp
(
EMP_Id int NOT NULL CHECK (EMP_Id>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255)

 ) 

2      To apply check constraint for multiple columns use syntax as: 

CREATE TABLE AvadTemp
(
EMP_Id int NOT NULL,
Name varchar(255) NOT NULL,
City varchar(255),
CONSTRAINT chk CHECK (EMP_Id>0 AND City=’Delhi’)
)

3        Alter existing table to add Check Constraint:

ALTER TABLE AvadTemp
ADD CHECK (EMP_Id>0)

4       Delete Check Constraint from table:

ALTER TABLE AvadTemp
DROP  CONSTRAINT  chk

In SQL Server which is the better option "Temporary Table" of "Temp Variable"?

In SQL Server 2005, we can create temp tables below mentioned two ways:

1: Declare @tmp Table (Col1 int, Col2 int);

2: Create Table #tmp (Col1 int, Col2 int);

General guideline available to choose between them is : For small to medium volumes of data and simple usage scenarios we should use table variables.

But above guideline has some exceptions and are not worldly accepted, there are few exception available for this.

There are some points to consider when choosing between them:

·         Temporary Tables are real tables so you can do things like CREATE INDEXes, etc. If you have large amounts of data for which accessing by index will be faster then temporary tables are a good option.

·         Table variables can have indexes by using PRIMARY KEY or UNIQUE constraints. (If you want a non-unique index just include the primary key column as the last column in the unique constraint. If you don't have a unique column, you can use an identity column.)

·         Table variables don't participate in transactions, logging or locking. This means they're faster as they don't require the overhead, but conversely you don't get those features. So for instance if you want to ROLLBACK midway through a procedure then table variables populated during that transaction will still be populated!

·         Temp tables might result in stored procedures being recompiled, perhaps often. Table variables will not.

·         You can create a temp table using SELECT INTO, which can be quicker to write (good for ad-hock querying) and may allow you to deal with changing data types over time, since you don't need to define your temp table structure upfront.

·         You can pass table variables back from functions, enabling you to encapsulate and reuse logic much easier (eg. make a function to split a string into a table of values on some arbitrary delimiter).

·         Using Table Variables within user-defined functions enables those functions to be used more widely (see CREATE FUNCTION documentation for details). If you're writing a function you should use table variables over temp tables unless there's a compelling need otherwise.

·         Both table variables and temp tables are stored in tempdb. This means you should be aware of issues such as COLLATION problems if your database collation is different to your server collation; temp tables and table variables will by default inherit the collation of the server, causing problems if you want to compare data in them with data in your database.

-     Global Temp Tables (##tmp) are another type of temp table available to all sessions and users.