CTE vs Temp Table

 

DELETE vs TRUNCATE vs DROP

                                         DELETE vs TRUNCATE vs DROP

Feature	DELETE	TRUNCATE	DROP
Rollback	✅ Yes	❌ No	❌ No
Where clause	✅ Yes	❌ No	❌ No
Identity reset	❌ No	✅ Yes	—
Logging	Fully logged	Minimally logged	Removes metadata

 

List 50% Rows of a table

 Query Show 50% Rows of a table

WITH employee_ranking AS (

  SELECT

    employee_id,

    last_name,

    first_name,

    salary,

    NTILE(2) OVER (ORDER BY salary ) as ntile

  FROM employee

)

SELECT

  employee_id,

  last_name,

  first_name,

  salary

FROM employee_ranking

WHERE ntile = 1

ORDER BY salary

SQL Server 2022 Enhancements/Features

 

SQL Server 2022 Enhancements/Features

1) Built-In Query Intelligence:

2) Query Store:
With Query store, we have following benefits.

1. Discover slow queries
2. Intervene manually to identify slow SQL queries.
3. Optimize slow SQL queries
4. Go live, observe performance, and repeat optimization if the expected result is not received.

3) Cardinality Estimation Feedback:

4) Memory Grant Feedback:
Memory grants for latest query is estimated by previous query executions

5) Parameter-Sensitive Plan (PSP) optimization:
In SQL Server 2022, the introduced PSP (Plan Stability Priority) helps solve this problem. With the ability of the Query Store to cache multiple execution plans for a single SQL statement, it becomes possible to select a more suitable plan based on parameters. The general principle is as follows: adding a dispatcher between the Query Hash and plan cache hash to determine which cached execution plan to use based on the dispatcher. This feature is indeed a great help for slightly larger databases with complex queries. Although I haven't had the opportunity to witness specific cases yet, based on my experience, this feature will significantly reduce the O&M threshold.

6) Degree of Parallelism (DOP) Feedback

ALTER DATABASE SCOPED CONFIGURATION SET DOP_FEEDBACK = ON

 

7) Enhanced security:

Microsoft Defender for cloud integration: It helps to protect the SQL Servers environment on on-premise, hybrid, and cloud environments.

Microsoft Purview integration: You can use Microsoft Purview policies to SQL servers enrolled in Azure Arc, and Microsoft Purview Data use management.

Ledger: The Ledger provides a highly secure database with tamper evidence capabilities in the database. 

Azure Active Directory authentication: You can configure Azure Active Directory authentication for SQL Server database connections.

Always encrypted with secure enclaves: You can use JOIN, ORDER BY, and GROUP BY clauses for UTF-8 collation in confidential queries using the secure enclaves. 

Support for MS-TDS 8.0 protocol: The SQL Server 2022 supports TFS 8.0 and TLS 1.3 support for data encryption.

8) Performance Improvements:

Improved in-memory OLTP performance: SQL Server 2022 performance improvements for in-memory OLTP include faster startup times and improved query performance.

Reduced I/O: SQL Server 2022 improved buffer pool management and better compression.

Optimized query processing: SQL Server 2022 includes a number of optimizations to query processing, such as improved query plan caching and better adaptive query optimization.

Improved performance for columnstore indexes, temporal tables, and machine learning workloads

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.

WITH RESULT SETS IN SQL Server

SQL server 2012 provide with result sets stored procedure execution facility through which we can have better control over the procedure output results, data types and column names.

/*Create Sample Employee Table */

CREATE TABLE Employee(EmpId int, Name Varchar(50),Salary Money)

/* Insert Sample Data in  Table Employee */

INSERT INTO Employee(EmpId,Name,Salary)

select 1,'Ram',100.5

UNION ALL

SELECT 2,'Shayam',115.5

UNION ALL

SELECT 3,'Sita',120

/* Check the inserted Data*/

SELECT * FROM Employee

Scenarios:

Example 1:

/*Create Stored procedure with complete result set*/

Create Procedure ProcEmployee

as

SELECT * FROM Employee

GO

execute ProcEmployee

with result sets

(

(EmployeeId Int, EmployeeName Varchar(1), Salary Int)

)

/*Output

After execution of above stored procedure with, with Result sets clause, it shows the out put column name and data types as per definition in with result sets clause

 */

Example 2:

Create Procedure ProcEmployee_withNoResult

as

SELECT * FROM Employee

where 1=2

GO

execute ProcEmployee_withNoResult

with result sets

(

(EmployeeId Int, EmployeeName Varchar(1), Salary Int)

)

/* OUTPUT

If we execute stored procedure with no result set and execute it with, with result sets clause it executes successfully with no error

*/

Example 3:

CREATE Procedure ProcEmployee_withPrint

as

PRINT 'PrintStmt'

GO

execute ProcEmployee_withPrint

with result sets

(

 ( EmployeeName Varchar(100) )

)

/* OUTPUT

If we execute SP without any result set have just print statement, it shows below error.

PrintStmt

Msg 11536, Level 16, State 1, Line 44

EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.

*/

Example 4:

CREATE PROCEDURE ProcEmployee_withZeroSelect

as

select top 0 0

GO

execute ProcEmployee_withZeroSelect

with result sets

(

 ( EmployeeName Varchar(100) )

)

/* OUTPUT

If we execute stored procedure with no result set and execute it with, with result sets clause it executes successfully with no error

*/

Example 5:

CREATE PROCEDURE ProcEmployee_withError

as

select 1/0

GO

execute ProcEmployee_withError

with result sets

( ( EmployeeName Varchar(100) ))

/* OUTPUT

If we execute stored procedure having error while execution, it shows below error

Msg 8134, Level 16, State 1, Procedure ProcEmployee_withError, Line 87

Divide by zero error encountered.

*/

Example 6:

EXECUTE dbo.ProcEmployee

WITH RESULT SETS UNDEFINED;

GO

/* OUTPUT: if we don’t want to change any output value or column we can use UNDEFINED clause

*/

Example 7:

EXECUTE dbo.ProcEmployee

WITH RESULT SETS NONE;

GO

/* OUTPUT: if we execute SP with NONE clause even though its returning data, it shows below error

Msg 11535, Level 16, State 1, Procedure ProcEmployee, Line 97

EXECUTE statement failed because its WITH RESULT SETS clause specified 0 result set(s), and the statement tried to send more result sets than this.

*/