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