49. Usage of Rule .
Rule is a object which is used to specify a column acceptable values.
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'))
50. Usage of sp_xml_preparedocument ?
It reads the input xml and parsed the text using the MSXML Parser and returns the handle for the parsed document. this handle is valid for the duration of session or until the invalidated of handle using the sp_xml_removedocument
A Paresed document is stored internal cache of SQL SERVER. MSXML uses 1/8 of the total memory available for the SQL SERVER.\
-- Save the xml document in the Cache of the Sql
Server ?
DECLARE @xmlstring VARCHAR(2000)
DECLARE @handle INT
SELECT @xmlstring = N'<Persons><Person><name>AB</name><age>25</age></Person><Person><name>Bc</name><age>26</age></Person></Persons>'
EXEC sys.sp_xml_preparedocument @handle OUTPUT,@xmlstring
SELECT * FROM OPENXML(@handle,'Persons/Person')
WITH (USERNAME VARCHAR(40) 'name',age INT 'age')
EXEC sys.sp_xml_removedocument @handle
51. New Features available in SQL SERVER 2008 ?
- 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
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