Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

REPEATABLE READ

Repeatable read Isolation in SQL Server

Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.

 

Example:

 
--Query1
 
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 
BEGIN TRAN 
SELECT * FROM Employee 
WAITFOR DELAY '00:00:05' 
SELECT * FROM Employee 
ROLLBACK
 
--Query2
Update Employee set UpdatedDate=getdate()

 

Result:

Run query 1 and then query 2, from query 1 notice that  both select statement returns same data even though you ran a query 2 to modify the data before the second select run. This is because the Update query was forced to wait for Query1 to finish due to the exclusive locks that were opened as you specified Repeatable Read.


 


After Transaction completes and update query executed, Select statement returns modified record:

 


 

 

If you rerun the above Queries but change Query1 to Read Committed you will notice the two selects return different data and that Query2 does not wait for Query1 to finish.

 

For above same use case Read returns:


 

One last thing to know about Repeatable Read is that the data can change between 2 queries if more records are added. Repeatable Read guarantees records queried by a previous select will not be changed or deleted; it does not stop new records being inserted so it is still very possible to get Phantom Reads at this isolation level.

 

Phantom read with repeatable read:

 

--Query1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ   
BEGIN TRAN 
SELECT * FROM Employee 
WAITFOR DELAY '00:00:10' 
SELECT * FROM Employee 
ROLLBACK 
 
--Query2
INSERT INTO Employee(EmployeeName,Country,PrimaryLanguage,CompanyPlant,ManagerId)
 
VALUES ('Anushka', ‘England’,'English','London',5) 

 


 

 

RAISERROR

RAISERROR:

Used to return error message back to application,system warning or error message generated by Sql server database engine.

Syntax:

RAISERROR ( { msg_id | msg_str | @local_variable }

    { ,severity ,state }

    [ ,argument [ ,...n ] ] )

    [ WITH option [ ,...n ] ]

 

msg_id:

user defined error number stored in sys.messages catalog view using sp_addmessage.  user defined number should be greater than 5000. When msg_id not specified RAISERROR raises an error message with default msg_id 50000.

msg_str:

user defined error message,The error message can have a maximum of 2,047 characters.

When msg_str is specified, RAISERROR raises an error message with an error number of 50000.

Other argiuments are severity,state ,argument,option.

 

SQL Server supports the following functions, to describe error or exception details in catch block:

ERROR_NUMBER(): The number assigned to the error.

ERROR_LINE(): The line number inside the routine that caused the error.

ERROR_MESSAGE(): The error message text, which includes the values supplied for any substitutable parameters, such as times or object names.

ERROR_SEVERITY(): The error’s severity.

ERROR_STATE(): The error’s state number.

ERROR_PROCEDURE(): The name of the stored procedure or trigger that generated the error.

Example:

-- =============================================

-- Author:        Nagnath Kendre

-- Create date: <Create Date,,>

-- Description:   Insert's Emplyee Detail, exception handlled with try catch block and RAISERROR

-- =============================================

CREATE PROCEDURE InsertEmplyeeDetails_SP

      -- Add the parameters for the stored procedure here

      @EmployeeName Nvarchar(250),

      @Country Nvarchar(50),

      @PrimaryLanguage Nvarchar(150),

      @CompanyPlant Nvarchar(150),

      @ManagerId int

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

 SET NOCOUNT ON;

 BEGIN TRY

 BEGIN TRANSACTION

  INSERT INTO Employee

([EmployeeName],[Country],[PrimaryLanguage],[CompanyPlant],[ManagerId],[CreatedDate])

 VALUES (@EmployeeName,@Country,@PrimaryLanguage,@CompanyPlant,@ManagerId,GETDATE())

 

 COMMIT TRANSACTION;

 END TRY

 

BEGIN CATCH

IF @@TRANCOUNT > 0

    ROLLBACK TRANSACTION;

 

    DECLARE @ErrorNumber INT = ERROR_NUMBER();

    DECLARE @ErrorLine INT = ERROR_LINE();

    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();

    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();

    DECLARE @ErrorState INT = ERROR_STATE();

 

    PRINT 'Actual error number: ' + CAST(@ErrorNumber AS VARCHAR(10));

    PRINT 'Actual line number: ' + CAST(@ErrorLine AS VARCHAR(10));

 

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)

 

END CATCH

 

END

GO

 

Result:

EXEC  [dbo].[InsertEmplyeeDetails_SP]

            @EmployeeName = N'Sanskruti',

            @Country = N'INDIA',

            @PrimaryLanguage = N'Marathi',

            @CompanyPlant = N'Pune',

            @ManagerId = abd 


Built-in aggregate Functions

Built-in aggregate Functions:

Aggregate functions perform a calculation on a set of values and return a single value.Aggregate functions are deterministic,Aggregate functions ignore null values,Except Count() Function.


Aggregate functions can be used as expressions only in the following:

·         The select list of a SELECT statement (either a subquery or an outer query).

·         A HAVING clause

SQL Server Provides Following Aggregate functions:

1.       AVG()

2.       MIN()

3.       CHECKSUM_AGG()

4.       SUM()

5.       COUNT()

6.       STDEV()

7.       COUNT_BIG()

8.       STDEVP()

9.       GROUPING()

10.   VAR()

11.   GROUPING_ID()

12.   VARP()

13.   MAX()

SUM ():

Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only. Null values are ignored. May be followed by the OVER Clause

Syntax:

SUM ( [ ALL | DISTINCT ] expression )

 

Example:

-- Get total order amount using Sum() Aggrigate function  in shop by month wise and year wise

-- ROW_NUMBER() function returns unique row number value within a partition

-- by OrderYear and OrderMonth

SELECT ROW_NUMBER() OVER (ORDER BY OrderSummery.OrderYear, OrderSummery.OrderMonth) AS [No.]

    , OrderSummery.OrderYear, OrderSummery.OrderMonth

    , OrderSummery.OrderAmountByMonth

FROM

(SELECT YEAR(OrderDate) OrderYear

    , MONTH(OrderDate) OrderMonth

    , SUM(Total) OrderAmountByMonth

FROM [Order]

GROUP BY YEAR(OrderDate), MONTH(OrderDate)

) OrderSummery