1. RANK ()

select PersonID, Firstname, LastName, RANK() OVER (Order by LastName asc) as LastNameRank from Person

2. DENSE_RANK ()

select PersonID, Firstname, LastName, DENSE_RANK() OVER (Order by LastName asc) as LastNameRank from Person

3. Partition By

SELECT Functionname () OVER (Partition by column Order by colname)
select * from Person
select PersonID, Firstname, LastName, DENSE_RANK() OVER (Partition by Lastname Order by PersonID) as LastNameRank from Person
select PersonID, Firstname, LastName, row_number() OVER (Partition by Lastname Order by PersonID) as RowNumber from Person
select PersonID, Firstname, LastName, nTile(7) OVER (Order by PersonID) as RowNumber from Person

4. Aggregate Windows Function

The main difference between a ROWS clause and a RANGE Clause is in the way duplicate data is treated

ROWS treat duplicate as distinct value RANGE treat them as a single entity

5. First _value, last_value, lead, lag window function

Analytical function

Only two analytical functions first value and last value can use framing

The default condition of subclause is the range between unbounded preceding and current row

FIRST_VALUE

LAST_VALUE

LEAD

LAG

select FirstName, LastName, FatherID,
FIRST_VALUE(FatherID) OVER (Order by LastName) AS Firstvalue,
LAST_VALUE(FatherID) OVER (Order by LastName) AS lastvalue
from (select TOP 15 * from Person) Person

The lead function retrieves a value from the previous to the current one

The lag function retrieves a value from a row after the current one

select FirstName, LastName, FatherID,
LEAD(FatherID, 1) OVER (Order by LastName) AS _lead,
LAG(FatherID, 1) OVER (Order by LastName) AS _lag
from Person

select FirstName, LastName, FatherID,
SUM(FatherID) OVER (PARTITION BY LastName) AS TOTALID
FROM (select top 18 * from Person) Person

6. CTE:

A CTE (Common Table Expression) defines a temporary result set which you can then use in a SELECT statement. It becomes a convenient way to manage complicated queries. Common Table Expressions are defined within the statement using the WITH operator.

WITH CTE AS
(
select FirstName, LastName, FatherID,
SUM(FatherID) OVER (PARTITION BY LastName) AS TOTALID
FROM Person
)
SELECT * FROM CTE
WHERE SUM(FatherID) OVER (PARTITION BY LastName) > 53

Derived Table

SELECT * FROM
(
select FirstName, LastName, FatherID,
SUM(FatherID) OVER (PARTITION BY LastName) AS TOTALID
FROM Person
) AS DerivedTable
SELECT * FROM DerivedTable
WHERE TOTALID > 53

7. HOW TO DELETE THE DUPLICATE RECORDS FORM TABLE

SELECT count (LastName) as DuplicateCount , FirstName
FROM Person
GROUP BY LastName,
FirstName, SrJr, MaidenName, Gender, FatherID,
MotherID, DateOfBirth, DateOfDeath
WITH CT_E (PersonID,LastName,FirstName,ROWNO)
AS(
SELECT PersonID, LastName, FirstName,
ROW_NUMBER() OVER (PARTITION BY LastName,
FirstName, SrJr, MaidenName, Gender, FatherID,
MotherID, DateOfBirth, DateOfDeath ORDER BY PersonID) as ROWNO
FROM Person)
DELETE FROM CT_E where ROWNO > 1

8. To check if duplicate records are present

SELECT count (LastName) as DuplicateCount , FirstName
FROM Person
GROUP BY LastName,
FirstName, SrJr, MaidenName, Gender, FatherID,
MotherID, DateOfBirth, DateOfDeath
HAVING count (LastName) > 1

9. How to merge new changes to an existing table

Merge into Dbo.employee as E
Using Dbo.employeeupdate as Eu
on
E.employeeid = Eu.employeeid
When Matched Then
Update Set E.fullname = = Eu.fullname,
E.employmentstatus = Eu.emplmentstsus
When Not Matched Then
Insert (Emplyoyeeid, Fullname, Employementstatus)
Values (Eu.empleeid, Eu.fullname, Eu.employmentstauts);
Go

10. USE of Coalesce

SELECT Coalesce (NULL, NULL,'','Chaitanya', 50132)

11. Stored Procedure

USE [DBName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[MultiPro]
@par1 int,
@par2 int,
@par3 int Output
AS
Begin
SET NOCOUNT ON;
SET @par3 = @par1*@par2
END
Exececuting it
USE [Family]
GO
DECLARE @return_value int,
@par3 int
EXEC @return_value = [dbo].[MultiPro]
@par1 = 3,
@par2 = 5,
@par3 = @par3 OUTPUT
SELECT @par3 as N’@par3'
SELECT ‘Return Value’ = @return_value
GO

11. Begin and commit transaction is not sufficient: When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing.

Set xact_abort Off

12. Guidelines to write a stored procedure

Create procedure usp_Proc
AS
BEGIN
   BEGIN TRY
       BEGIN TRANSACTION
          — -
          — -
          — -
       COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
       IF @@TRANSCOUNT > 0
    END CATCH

13. HOW TO CREATE A STORE CUSTOMIZED ERRORS

Raiserror (‘message’, 16,1)

14. THROW CATCH

BEGIN CATCH
THROW
END CATCH

Security level

0–10 -> Information or warning message

DB shrink files DB level 8

11–16 -> Fixed by SQL developer or SQL admin

17–19 -> Resources (Database file, log files, or TempDB files are completely filled)

20–25 -> fatal errors(corruption of files etc)

declare @database int = DB_ID()
declare @databaseName sysname — db_name()
raiserror (‘Current databse ID:%d, database name: %s.’, 16, 1, @databaseid, @databasename)

Let’s try a TRY CATCH BLOCK for raise error

select * from sys.messages
BEGIN TRY
DECLARE @RESULT INT = 55/0
END TRY
BEGIN CATCH
PRINT ‘BEFORE THROW’;
THROW;
PRINT ‘AFTER THROW’
END CATCH
PRINT ‘AFTER CATCH’

— RAISERROR ->The severity parameter specified the severity of the exception

— THROW -> There is no severity parameter. The exception severity is always set to 16

— THROW -> Requires preceding statement to end with a semicolon (;) statement terminator

15. Pagination using OFFSET AND FETCH

Declare @i int =5
Declare @j int = 10
SELECT * FOM ORDER
ORDER BY 3
OFFSET @i ROWS FETCH NEXT @j ROWS ONLY
USE Family
Declare @i int = 5
Declare @j int = 3
SELECT * FROM Person
ORDER BY 3
OFFSET @i ROWS FETCH NEXT @j ROWS ONLY

16. SAMPLE PROCEDURE FOR PAGING

USE Family
GO
CREATE PROCEDURE PAGING
Declare
@d varchar(20),
@c varchar(20),
@i int,
@j int
As
SET NONCOUNT ON
BEGIN
SELECT * FROM @d
ORDER BY @c
OFFSET @i ROWS FETCH NEXT @j ROWS ONLY
END
GO

17. Rename a Table with Dependencies Using SYNONYMS

— Query to rename the table

EXEC SP_RENAME ‘OLD’, ‘NEW’

— Query to check the dependencies

EXEC SP_DEPENDS ‘OLD’

— We will use synonyms

Create Synonyms OLD FOR NEW
USING TRY CATCH BLOCK IN A TRANSACTION
USE DB
GO
BEGIN Try
BEGIN Transaction
EXEC SP_RENAME ‘OLD’,’NEW’
Create Synonyms OLD FOR NEW
Commit Transaction
End Try
Begin catch
Rollback Transaction
End Catch