How SQL Server evaluate Null Value in Check Condition

Understanding NULL value with check constraint

           

As I said earlier when the condition of check constraint evaluates to false, it will not allow inserting /updating record  in the table.

In the given below example I am creating a check constraint which is allowing only three word in the country field.

            Alter table MyCountry

Add check (country_name in (‘india’, ’usa’, ’uk ’))

Now I am trying to insert these three record using insert statement

           

            insert into MyCountry(country_name) values(‘india’)

            insert into MyCountry(country_name) values(NULL)

            insert into MyCountry(country_name) values(‘uss’)

Now what will happen, when I will try to execute these three insert statement:

a)      Only first insert statement insert record.

b)      First and second insert record.

c)      First and third insert record successfully.

The correct solution is (b).

The reason the second INSERT statement worked is that the check constraint did not equate to FALSE when the second INSERT statement was run. Because of this, the database engine inserted the record. So why did this happen? This happened because when NULL is used in a comparison operation it equates to UNKNOWN. Since UNKNOWN is not FALSE there is no violation of the check constraint. Therefore, you need to be careful when you write your check constraints where you want to reject values that contain NULLS. Another way to code the above constraint is

           

            Alter table MyCountry

Add check (country_name in (‘india’, ’usa’, ’uk ’) and country_name is not NULL)

Post Reference: Vikram Aristocratic Elfin Share

Predefined value for a column in SQL Server Table object

How to set predefined value for a column in SQL Server Table object

Check constraints contain an expression the database will evaluate when you modify or insert a row. You can use any of the operator (<, >, <=, >=, IN, Like, Between, AND, OR) to build your check constraint expression. Only when the expression in check constraint evaluates to true, you will be allowed to insert or update record in the table whereas if check constraint expression evaluate to false you will not allowed enter/update record in the table.

You can add check constraint for your column in 3 ways: column level, table level or using alter table statement.

1) Column level

            Create table ActivityMaster

            (

                        activity_id  int,

                        activity_name  check (activity_name in (‘cricket’, ’tt’, ’swimming ’))

            }

2)Table Level

            Create table ActivityMaster

            (

                        activity_id  int,

                        activity_name,

constraint  chkActivityName  check (activity_name in (‘cricket’, ’tt’, ’swimming ’))

            }

3)In Alter Statement

            Alter table ActivityMaster

Add check (activity_name in (‘cricket’, ’tt’, ’swimming ’))

Here in the above example we are having a table called ActivityMaster which contain two column activity_id and activity_name then we are adding check constraint with activity_name which says, the activity_name column cannot have other than  (‘cricket’, ’tt’, ’swimming ’).

Some more use of Check

We can have multiple use of check constraint say salary should be greater than 3000

            Alter table EmployeeSalary

Add check (emp_salary > 3000)

           

You can also use ‘AND’ and ‘OR’ operator with check constraint.

            Alter table EmployeeSalary

Add check (emp_salary  >=  3000 AND emp_salary  <= 90000 )

Two or more column in single Check

You can have multiple columns in a single check constraint like the one below

            Alter table EmployeeSalary

            Add check (emp_salary > 10000 and emp_post like ‘manager’)

                                

Post Reference: Vikram Aristocratic Elfin Share

What is Common Table Expression(CTE) in SQL Server?

There are often times when we may need to operate over a set of data that doesn't inherently exist within the system. For example say InvoiceDatabase where we may have tables like Order, OrderDtail, InvoiceMaster, InvoiceDetail but we may need to run report against aggregate data across these tables.

Typically, views are used to break down complex queries into desired set of data but again views are parmanent database objects with will occupy the database space. Then you are left with #temp table option, here also you need to write the whole create table then you need to insert record to the temporary table...

The CTE is a form of the derived table that can be declared once and referenced multiple times in a query.

A Simple Common Table Expression Example
  TempCTE(StreamName,IsActive)as(

select top 5 sm.StreamName, ssm.SubStreamName from StreamMaster sm, SubStreamMaster ssmwhere sm.StreamId=ssm.StreamId )

select  * from Temp CTE

StreamName  SubStreamName
12th Science  B
12th Science  A
12th Science  AB
12th General  General
12th General  Home Science


Common Table Expression Syntax
A Common Table Expression contains three core parts:

• The CTE name (this is what follows the WITH keyword)
• The column list (optional)
• The query (appears within parentheses after the AS keyword)

NOTE: The query using the CTE must be the first query appearing after the CTE. That you can not do like the below:

with abc(StreamName,IsActive)as(

select top 5 sm.StreamName, ssm.SubStreamName from StreamMaster sm, SubStreamMaster ssmwhere sm.StreamId=ssm.StreamId )select * from StreamMasterselect * from abc

Post Reference: Vikram Aristocratic Elfin Share
This query creates a CTE named  TempCTE that returns top 5 StreamName with there sub-stream name. Now you can query the TempCTE to get the resultant data.
with

Difference between varchar and nvarchar?

Storing Unicode Data

varchar: Variable-length, non-Unicode character data. varchar stores unicode data.
nvarchar: Variable-length Unicode character data. nvarchar stores unicode.

If you are using vchar datatype then each time you store data you first need the data to be converted into varchar then only you can store, this is not apply to nvarchar.

So if you are using nvarchar lot of your conversion task will be reduced. But nvarchar takes twice as much space as varchar, so before using this datatype considered this point.

With cheap memory nowadays I prefer nvarchar.
Post Reference: Vikram Aristocratic Elfin Share

Export Data form Sql Server to Excel Datasheet

Do The Following Step to Export Data from Sql Server to Excel

1)Create an Excel Datasheet save it with name testingExcel in D:/testingExcel.xls
2)Save Type of excel sheet should be Excel 97-2003 Worbook.
3)Then enable the 'ad hoc distributed queries' in surface area configuration using  sp_configure

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

4)Then with the help of openrowset function you can insert the data of sql server table to worksheet. Yet not finished you have to create column in excel sheet with the name StreamName and IsActive

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\testingExcel.xls;','SELECT StreamName,IsActive FROM [Sheet1$]') select StreamName,IsActive from StreamMaster
go


5)Be sure you have closed your excel sheet befor executing the above query.
6) Thats all you need to do to export data to excel sheet.

Post Reference: Vikram Aristocratic Elfin Share