In This post Let we see some important question and answers in Sql Server, which is a continuation of previous post.
49. Usage of Rule .
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
No comments:
Post a Comment