SQL server

gouvernance des données : Définit les règles, les politiques, les rôles et les responsabilités.
Qu'est-ce que la lignée des données (data lineage) et pourquoi est-elle importante ? Permet de tracer l'origine, les transformations et l'utilisation finale d'une donnée pour assurer la confiance et la conformité.

Query performance :
Avoid using SELECT *. , instead use column naming
Avoid using function like : WHERE YEAR(OrderDate) = 2026 , in place use WHERE OrderDate>''
use 'text%' in place of '%text'
use union all in place of union when no doubles , to voide using distinct
Use "SET NOCOUNT ON" 

Use standard Temporary Tables (#TempTable) for large data sets that require indexing or multiple passes. Use Table Variables (@TableVariable) only for small data sets (usually under a few hundred rows) because SQL Server does not maintain detailed statistics on table

Always reference database objects using their schema names (e.g., SELECT ... FROM dbo.Orders). This prevents the database engine from scanning multiple schemas to locate the object, improving initial compilation speed

Maintain statistics: The Query Optimizer relies on statistics to estimate row counts and pick the best plan. Ensure AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS are enabled on the database, and run routine index maintenance to rebuild or reorganize fragmented data

 Choose the smallest data type that can safely hold your data (e.g., use TINYINT instead of INT for status codes). Smaller rows mean more data fits into a single 8KB data page, maximizing buffer cache efficiency.

from management studio : 
- SQL activity monitor (I/O, memory, CPU , resources( lock, logging .... ) : right click SQL server
- Performance monitor : from SQL server then reports then there 

Common Table Expression (CTE) : with  as name ( select .. from X)  select * from name 
﻿
Delete doubles as :
DELETE FROM YourTableName
WHERE PrimaryKeyColumn IN (
    SELECT PrimaryKeyColumn 
    FROM (
        SELECT PrimaryKeyColumn,
               ROW_NUMBER() OVER (PARTITION BY Column1, Column2 ORDER BY PrimaryKeyColumn) as RowNum
        FROM YourTableName
    ) temp
    WHERE RowNum > 1
);
﻿
WITH CTE_Duplicates AS (
    SELECT 
        Column1, 
        Column2,
        ROW_NUMBER() OVER (
            PARTITION BY Column1, Column2 
            ORDER BY (SELECT NULL)
        ) AS RowNum
    FROM YourTableName
)
DELETE FROM CTE_Duplicates
WHERE RowNum > 1;
﻿
﻿
﻿
﻿
﻿
﻿
﻿
﻿
﻿
﻿
﻿