Sunday, 17 November 2013

SQL SERVER - Notes 8


57.  What is valid xml and well formed xml?
  
            Well Formed XML: XML which adheres to the syntax rules.
            Valid XML: It is a well formed xml and which is validated against rules present in DTD

58.  Usage of Explicit XML?
            The output of the explicit mode contains all the information of the resulting XML tree. The FOR XML clause in EXPLICIT mode associates with every element, a tag number of the current element and tag number for parent element. This tag numbers are stored in separate columns, named Tag and Parent Respectively.

SELECT      1           AS Tag,
            NULL        AS Parent,
            id          AS [Employees!1!id],
            department AS [Employees!1!dept],
            name        AS[Employees!1]
FROM  employee
FOR XML EXPLICIT

Output:
            <Employees id="2" dept="Tech">DF</Employees>
<Employees id="3" dept="HR">Siv</Employees>
<Employees id="4" dept="BA">SS</Employees>
           
59.  What are the operators present in SQL Server?
o   Arithmetic Operators [+,-,*,/,%]
o   Logical Operators [ALL,AND,OR,ANY,BETWEEN,IN,EXISTS,LIKE]
o   Comparison Operators [=, <,>,>=, <=, <>,! =]
o   Assignment Operators [=]
o   Bitwise Operators [&, |,^]
o   Unary Operator [+,-,~]
o   Compound Operator [+=,*=,-=,/=,&=,^=,|=]

60.  What is the Data Integrity?
            Data Integrity means maintaining accurate, reliable and consistent data during any operation.
·         Entity Integrity
It ensures that each row in a table must be unique identified by an attribute, called primary key
·         Domain Integrity
It ensures that only a valid range of values is allowed to be stored in a column. It restricts the type of data, range of values and format of data.
·         Referential Integrity
It ensures that all the values in the foreign key match the values in primary key.
·         User-Defined Integrity
It enforces the data integrity on tables in two ways by defining constraints or by defining rules.

61.  Usage of GoTo statement?
            Goto statement causes the program control to unconditionally jump to the label specified in the GOTO statement.
           
DECLARE @i INT =1
WHILE(@i < 8)
BEGIN

      IF @i%4=0
      GOTO outofloop;
      ELSE
      GOTO Incr;

      Outofloop:
      BREAK;

      Incr:
      PRINT @i
      SELECT @i+=1

END

Output:
1
2
3

62.  What is ACID?
            ACID is an acronym of four important properties i.e Atomicity, Consistency, Isolation and Durability.
o   Atomicity
It ensures that any update occurs in the database, then either all or none of the transactions executed successfully.

o   Consistency
It ensures that any changes update in the database is consistent and logical after and before of the update committed successfully.

o   Isolation
It ensures that multiple transactions are executed simultaneously, Then each Transaction is isolated from each other till the transaction executed completely.

o   Durability
It ensures that the data will be restored when system crashes or power failure up to last committed successful transaction. Durability ensures that the data is permanent after updating.

63.  What is partition? How it is helpful in improving the performance?
            After index a table we can further optimize the performance of a table by partitioning the tables and indexes. Partitioning allows you to modify the data without affecting the integrity of the entire collection. Partition is the process of distributing the data of tables and indexes on separate filegroups.

       Need to perform the following task to create the partition
o   Create a Partition Function
o   Create File groups
o   Create a Partition Scheme
o   Create a Partition Table

-- Create a Partition Function [function takes one parameter as input]
CREATE PARTITION FUNCTION emp_partfunc(INT)
AS RANGE LEFT        --Left keyword specifies that vlaues one less than the specified
FOR VALUES(1980,1990,2000,2010)

-- Create a File group
RIGHT Click ON DATABASE
CLICK Properties AND SELECT FileGroups AND ADD four GROUP f1,f2,f3,f4

-- Create a Partition Scheme
CREATE PARTITION SCHEME emp_part_sch
AS PARTITION emp_partfunc
TO (f1,f2,f3,f4)

-- Create a partition table
CREATE TABLE employ(id INT IDENTITY(1,1), NAME VARCHAR(40),yearofjoin INT)
ON emp_part_sch(yearofjoin)

64.  Resource Governance
            SQL Server provides a new feature known as Resource Governor, which allows the database administrators to put a check on the server resources consumed by various connections or applications. It can control the CPU time and memory used by specific applications. It allows you to allocate the server resources to different types of connections. It category the incoming connections to different workloads groups, Each of which can be monitored individually. WorkLoad group are contained inside the resource pool which contains the portion of CPU and memory resources available to the current instance. It allows you to set importance for different workload groups.

o   Creating a Resource Pool
o   Creating a workload group
o   Creating a classifier function
o   Configure the Resource governor.

-- Create a Resource Pool
CREATE RESOURCE POOL reportpool
WITH (MAX_CPU_PERCENT = 50, MAX_MEMORY_PERCENT = 50)

-- Create a WorkLoad Group
CREATE WORKLOAD GROUP reportworkload
WITH (IMPORTANCE = HIGH)
USING samplepool

-- Create a Classifier Function
CREATE FUNCTION reportfunc()
RETURNS sysname
WITH schemabinding
AS
BEGIN

   DECLARE @wklgrp sysname
   IF(APP_NAME()= 'Report application')
   SET @wklgrp = 'reportworkload'
   ELSE
   RETURN default
  
   RETURN @wklgrp
END

-- Classifier the Resource Governer
ALTER RESOURCE GOVERNOR
WITH ( CLASSIFIER_FUNCTION = dbo.reportfunc)
GO

ALTER RESOURCE GOVERNOR RECONFIGURE

GO




No comments:

Post a Comment