Thursday 23 June 2016

Question and Answers in Sql Server - Part 16


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

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