Friday, 24 June 2016

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>