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