Thursday 23 June 2016

Question and Answers in Sql Server - Part 14

In This post Let we see some important question and answers in Sql Server, which is a continuation of previous post.


52. What is sparse columns ?
         A Sparse column is a optimised storage format to store NULL values, we can specify a table column as sparse by SPARSE attribute. 

  •  It greatly reduces the space requirement for your table columns.
  • It doesn't take up any space when the column value contains the NULL.
  • It takes up more space when column contains NON-NULL value
  • It is suitable for only the columns which have storage value NULL in large percentage. 

CREATE TABLE employeetable
(
 id      INT IDENTITY(1,1),
 NAME    VARCHAR(40),
 addres  VARCHAR(200),
 tempaddres VARCHAR(300) SPARSE
            )



53. How to modify the value in XML ?

       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>




54. How to insert a new tag in existing XML ?
      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>




55. How to DELETE a Value from XML based on Condition ?
      Delete a element tag from the xml based on condition.


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>




56. How to find the existence of a value or tag in XML ?
       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





No comments:

Post a Comment