Explore SQL Server 2022 capabilities

https://www.microsoft.com/en-us/sql-server/sql-server-2022 

New Features in MS SQL Server 2019

    New Features in MS SQL Server 2019  

Ø Intelligent Query Processing Enhancements

Ø Accelerated Database Recovery (ADR)

Ø AlwaysEncrypted with secure enclaves

Ø Query Store custom capture policies

Ø Verbose truncation warnings

Ø Resumable index build

Ø Data virtualization with Polybase

Ø Last actual execution plan DMF

Ø Multiple internal performance improvements

Running Total using windows functions in SQL Server

 Query to find running total using windows analytical functions

/* Create global temporary table  */

CREATE TABLE ##Employee_Temp(EmpId INT,EMPName VARCHAR(100), Salary MONEY,DEPTID INT,LOC VARCHAR(200))

/* Insert Sample Data in global temporary table  */

INSERT INTO ##Employee_Temp VALUES

(1,'RAM',10000,1,'CHARLOTTE'),

(2,'GEORGE',11000,1,'CHARLOTTE'),

(3,'BENJAMIN',9000,2,'CHARLOTTE'),

(4,'MARK',12000,2,'NEW YORK'),

(5,'ALLEN',13000,3,'NEW YORK'),

(6,'NILOLA',14000,3,'NEW YORK'),

(7,'REDIFF',20000,4,'DELHI'),

(8,'SHAYAM',16000,4,'DELHI'),

(9,'ALLARD',17000,4,'LONDON'),

(10,'KEVIN',18000,5,'TOKIO') 

/* Below is the select Query to get running total from top to bottom and bottom to top */

select *, 

SUM(Salary ) OVER( ORDER BY EMPID  ROWS BETWEEN UNBOUNDED PRECEDING 

                                     AND CURRENT ROW) AS RunningTotalUsingROWS_TOPTOBOTTOM,

SUM(Salary ) OVER( ORDER BY EMPID  RANGE BETWEEN UNBOUNDED PRECEDING 

                                     AND CURRENT ROW) AS RunningTotalUsingRANGE_TOPTOBOTTOM,

SUM(Salary ) OVER( ORDER BY EMPID ASC  ROWS BETWEEN CURRENT ROW 

AND UNBOUNDED FOLLOWING ) AS RunningTotalUsingROWS_BOTTOMTOTOP,  

SUM(Salary ) OVER( ORDER BY EMPID ASC  RANGE BETWEEN CURRENT ROW 

     AND UNBOUNDED FOLLOWING ) AS RunningTotalUsingRANGE_BOTTOMTOTOP

 FROM ##Employee_Temp  ORDER BY EMPID ASC

Result:

Running total with selected (3) PRECEDING & FOLLOWING:

select *, 

SUM(Salary ) OVER( ORDER BY EMPID  ROWS BETWEEN 3 PRECEDING 

                                     AND CURRENT ROW) AS RunningTotalUsingROWS_TOPTOBOTTOM,

 

SUM(Salary ) OVER( ORDER BY EMPID ASC  ROWS BETWEEN CURRENT ROW 

AND 3 FOLLOWING ) AS RunningTotalUsingROWS_BOTTOMTOTOP  

 

 FROM ##Employee_Temp  ORDER BY EMPID ASC

Result:

Nth Highest salary in SQL Server

16 Ways to find Nth Highest salary in SQL Server
https://www.youtube.com/watch?v=JItaf9PIL3M&t=815s

Facts about Indexes in SQL Server

Contained Database in SQL Server

Contained Database is independent of the instance of the SQL Server on which it is hosted and also isolated from other databases.

SQL server 2012 allows the user to isolate the contained database in following ways

   1.       All the metadata is maintained in contained database instead of master database

   2.. All metadata are defined using same collation

   3.      User authentication is performed by database instead of the server login

in this way contained database becomes fully portable without having the issue of orphaned users.

Process to implement contained database:

1.  Enable setting for contained database authentication.

2. Create a Contained database named ContainedDB' by selecting containment type='Partial' in option tab.

3. Create database user named ContainedDBUser with SQL Server authentication with no link at instance level.

4. While connecting to containedDB database, connect it by selecting in options of 'connect to server' with the new user named ContainedDBUser.

Use master

GO

sp_configure 'show advanced options',1

GO

RECONFIGURE WITH OVERRIDE

GO

sp_configure 'contained database authentication', 1

GO

RECONFIGURE WITH OVERRIDE

GO

sp_configure 'show advanced options', 0

GO

RECONFIGURE WITH OVERRIDE

GO

Let us do it practically.

    1.       Create ContainedDB

   2.       In options tab select containment type to partial.

    3.       Create database user named ContainedDBUser with usertype as SQL USER WITH Password.

   4.       Connect to ContainedDB database using the above created user ContainedDBUser by mentioning details through options.

 in login mention ContainedDBUser

   5.       In connection properties tab, connect to database ContainedDB

66.       We will get ContainedDB connection as below.

f

     Please try above scenarios in Development environment only.