Features Available in SQL SERVER 2008

In this post we are going to see the New Features available in the SQL SERVER

•  New DataType [Date, time, FileStream] 

         DECLARE @date DATE

         DECLARE @time TIME

•       Compouned Operations [+=,-=,*=,/=]                

         DECLARE @i INT = 1

         SELECT @i+=2

         SELECT @i

•       Merge Statement  

MERGE INTO [targettable] AS t

USING [sourcetable] AS s

ON t.[column] = s.[column]

WHEN MATCHED THEN

-- statements

WHEN NOT MATCHED THEN

-- statements

•       Sparse Columns
•       Table value parameters
•       Integrated Full Text search
•       Intellisense
•       Transparent Data Encryption (Encrypt whole database explicitly)
•       Resource Governor
•       Policy Based Management
•       Linq

Create a Rule in SQL SERVER

In This post we are going to see how to create a Rule in SQL SERVER

Format:

       CREATE RULE [ schema_name . ] 

       rule_name   

       AS condition_expression  [ ; ]  

Example:

CREATE TABLE countries(NAME VARCHAR(3),code INT)

go

CREATE RULE countryrule AS @con IN ('US','IND')

go

EXEC sys.sp_bindrule 'countryrule', -- nvarchar(776)

   'dbo.countries.name' -- nvarchar(776)

   

go

INSERT INTO countries(name ,code)

VALUES ('Ind',545)

INSERT INTO countries(name ,code)

VALUES ('US',545)

INSERT INTO countries(name ,code)

VALUES ('UK',545)

EXEC sys.sp_unbindrule 'countryrule'

Note : This feature will be removed from the microsoft in the future release,so avoid this feature in the development work, Modify the feature

with check constraint.

Example

ALTER TABLE countries

ADD CONSTRAINT countryrule CHECK(name IN ('IND','US','UK'))

From this post i hope you can learn how to create a Rule in SQL SERVER

How to find the existence of a Value in SQL SERVER

In this post we are going to see how to find the existence of a value in SQL SERVER

Execute a two different code , based on occurrence of value in element

--Exist

IF @employeedata.exist('(/Emps/Emp[@dept="HR"])[1]') = 1

BEGIN

  SELECT 'Employee Exist in HR dept'

END

ELSE

BEGIN

      SELECT 'No employee exist in HR Dept'

            END

Delete a Value in a XML based on a Condition in SQL SERVER

In this post we are going to see how to delete a element from xml based on condition in SQL SERVER

Xml format:

<Emps>

  <Emp dept="HR">a</Emp>

  <Emp dept="HR" />

  <Emp dept="tech">R</Emp>

</Emps>

DECLARE @employeedata XML

DECLARE @deletecondition VARCHAR(20) = 'HR'

--xml

SELECT @employeedata = N'<Emps><Emp dept="HR">a</Emp><Emp dept="HR"></Emp><Emp dept="tech">R</Emp></Emps>'

SELECT @employeedata

--Deletion

SET @employeedata.modify('delete (//Emps/Emp[@dept=sql:variable("@deletecondition")])') -- 

delete all HR records

SET @employeedata.modify('delete (//Emps/Emp[@dept=sql:variable("@deletecondition")])[1]') -- delete first HR record

SELECT @employeedata

Output:

<Emps>

  <Emp dept="tech">R</Emp>

</Emps>

Insert an new Element in a XML using SQL SERVER

In this post we are going to see how to add a new element in a XML using SQL SERVER, Now we see how to insert an new element in the existing xml , in different positions.

        Xml format:

<Emps>

  <Emp dept="HR">a</Emp>

  <Emp dept="HR" />

  <Emp dept="tech">R</Emp>

</Emps>

DECLARE @employeedata XML

DECLARE @deletecondition VARCHAR(20) = 'HR'

--xml

SELECT @employeedata = N'<Emps><Emp dept="HR">a</Emp><Emp dept="HR"></Emp><Emp dept="tech">R</Emp></Emps>'

SELECT @employeedata

--Insertion

SET @employeedata.modify('insert <Emp>b</Emp>  as first into (/Emps)[1]')

-- or

SET @employeedata.modify('insert <Emp>b</Emp>  into (/Emps/Emp)[1]') -- insert as last tag

SELECT @employeedata

<Emps>

  <Emp>b<Emp>b</Emp></Emp>

  <Emp dept="HR">a</Emp>

  <Emp dept="HR" />

  <Emp dept="tech">R</Emp>

</Emps>

     

Modify the Value of a XML in SQL SERVER

In this post we are going to see how to modify a value in XML in SQL SERVER, Now below example will explain you how to change the value of an Hr to tech in an xml first tag.    [1]  Refers the occurrence of first element. 

       Xml Format:

<Emps>

  <Emp dept="HR">a</Emp>

  <Emp dept="HR" />

  <Emp dept="tech">R</Emp>

</Emps>

  

DECLARE @employeedata XML

DECLARE @deletecondition VARCHAR(20) = 'HR'

--xml

SELECT @employeedata = N'<Emps><Emp dept="HR">a</Emp><Emp dept="HR"></Emp><Emp dept="tech">R</Emp></Emps>'

SET @employeedata.modify('replace value of (/Emps/Emp[@dept=("HR")]/@dept)[1] with "Tech"')

SELECT @employeedata

Output: 

<Emps>

  <Emp dept="Tech">a</Emp>

  <Emp dept="HR" />

  <Emp dept="tech">R</Emp>

           </Emps>