Collection de scripts et procédures

AddLinkedServer.sql

Procedure to make create of SQL Server Linked server easier

KillProcess

Kill all actives connections on a database.

p_alter_column

Stored procedure will make the job of altering the type of an SQL Server Table

p_Backup_DB

Procedure in order to Quick launch the backup of a database

VIEW_DEFAULT_CONSTRAINTS

get the details of the defaults constaints

p_drop_column

Easyly drop a column.

p_drop_defaultconstraint

Drop the default constraint associated to a column

p_drop_primaryKey

drops the primary key of a table.

p_export_csv

Export a Query to CSV Format

p_MaintenancePlan

Quick maintenance plan for a database.

p_rebuild_indexes

rebuild the all the indexes of a databases

p_restoreBackup

Will restore a backup from a file.

p_shrinkDB

Shrinks the current database

p_tableAllocation

Will give the size of tables and indexed and number of rows of each table.
This allow to understand how the size is used inside a database.

p_waits

Detect the processes actually waiting for the release of a lock

VIEW_COLUMNS_INDEXES

list the columns included in indexes.

Choisir le type de donnée adaptée

Afin de minimiser la place prise par les données, il convient de choisir le type le mieux adaptée.
Smalldatetime au lieu de DateTime


Il est inutile et coûteux d’utiliser un type datetime lorsqu’un smalldatetime est suffisant.
Une colonne de type DateTime prend 8 Octets alors qu’un smalldatetime prend 4 octets.
On économise donc 4 octets par ligne.


Les colonnes de types datetime peuvent contenir des valeurs du 1er janvier 1753 au 31 décembre 9999 avec une précision de 3,33 millisecondes


Les colonnes de types smalldatetime peuvent contenir des valeurs du 1er janvier 1900 au 6 juin 2079 avec une précision à la minute


Choisir le type entier Correct

Les entiers peuvent êtres stockées dans plusieurs types de colonnes :

TypeIntervalle de valeursStockage
bigint-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)8 Octets
int-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)4 Octets
smallint-2^15 (-32,768) to 2^15-1 (32,767)2 Octets
tinyint0 to 2551 Octet

Scripts SQL

https://github.com/alexiscomte/SQL/blob/master/capit_scripts.sql

Capit_COLUMNS_DEFAULTS
La vue « Capit_COLUMNS_DEFAULTS » est basée sur les vues système SQL Server. Elle permet de ramener facilement les valeurs par défaut des colonnes d’une base.

Capit_sp_ConvertFormat
Capit_sp_ConvertFormat permet de convertir une colonne d’un type vers un autre

Capit_sp_DateTimeToSmallDateTime
Capit_sp_DateTimeToSmallDateTime permet de tranformer une colonne de type DateTime en une colonne de Type smalldatetime.
Une colonne de type DateTime prend 8 Octets alors qu’un smalldatetime prend 4 octets. On economise donc 4 octets par ligne.
Les colonnes de types datetime peuvent contenir des valeurs du 1er janvier 1753 au 31 décembre 9999 avec une précision de 3,33 millisecondes
Les colonnes de types smalldatetime peuvent contenir des valeurs du 1er janvier 1900 au 6 juin 2079 avec une précision à la minute

capit_sp_ConvertAllColumns
capit_sp_ConvertAllColumns permet de tranformer toutes les colonnes d’un type particulier.
Celà peut être utile lorsque l’on veut transformer toutes les colonnes de type DateTime en smalldatetime.

Capit_sp_DropColumn
Procédure permettant de supprimer une colonne d’une table

Encodage et Formattage des fichiers

Ci dessous, un script powershell permettant de modifier l’encodage des fichiers en UTF8 puis de les formatter avec l’outil d’APEX. A noter que j’ai spécifier un profil ADS personnalisé dans APEX formatter qu’il vous faudra donc changer.

Pour que ce script fonctionne, il faut également avoir installé GIT dont je me sers pour détecter l’encodage des fichiers.

https://github.com/alexiscomte/SQL/blob/master/Convert-Encoding.ps1

Exemple d’appel :

powershell.exe .\Convert-Encoding.ps1 "c:\sql\SQL" "c:\TEMP\SQL" 

FORMAT vs Convert Performances

Le convert est beaucoup plus rapide que le format

declare @dStart datetime = getdate()
declare @compteur int = 0
declare @convert varchar(30)
set @compteur = 1
set @dStart = getdate()
while @compteur < 10000
begin
    set @convert = convert(varchar,getdate(),103)
    set @compteur = @compteur + 1
end
print @convert
print 'convert ' + left( datediff(ms,@dstart,getdate()), 40)
set @compteur = 1
set @dStart = getdate()
while @compteur < 10000
begin
    set @convert = format(getdate(),'dd/MM/yyyy')
    set @compteur = @compteur + 1
end
print @convert
print 'format ' + left( datediff(ms,@dstart,getdate()), 40)

DATEFROMPARTS vs ‘19000101’

le DateFromParts est plus performant  

DECLARE @compteur INT;
DECLARE @date DATETIME;
DECLARE @dStart DATETIME;
DECLARE @msString INT = 0;
DECLARE @msdate INT = 0;
DECLARE @msfunc INT = 0;
SET @dStart = GETDATE();
DECLARE @boucle INT;
SET @boucle = 1;
WHILE @boucle < 10
BEGIN

    SET @compteur = 1;
    WHILE @compteur < 10000
    BEGIN
        SET @Date = '19000101';
        SET @compteur = @compteur + 1;
    END;
    SET @msString = @msString + DATEDIFF(ms, @dStart, GETDATE());
    SET @dStart = GETDATE();
    SET @compteur = 1;
    WHILE @compteur < 10000
    BEGIN
        SET @Date = Datefromparts(1900,01,01)
        SET @compteur = @compteur + 1;
    END;
    SET @msdate = @msdate + DATEDIFF(ms, @dStart, GETDATE());
    SET @boucle = @boucle + 1;
END;
PRINT 'String ' + LEFT(@msString, 20) + ' ms ';
PRINT 'Date ' + LEFT(@msdate, 20) + ' ms ';

CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY

Après un peu de recherche,  le mieux pour éviter les locks sont les curseurs

LOCAL STATIC READ_ONLY FORWARD_ONLY  

Voici quelques articles intéressants sur le sujet :  

https://blogs.msdn.microsoft.com/sqlqueryprocessing/2009/08/12/understanding-sql-server-fast_forward-server-cursors/

https://sqlperformance.com/2012/09/t-sql-queries/cursor-options

https://sqlundercover.com/2017/11/16/sql-smackdown-cursors-vs-loops/

https://www.sqlservercentral.com/forums/topic/fast_forward-cursor-blocking-issue

Différence entre WHERE et ON sur SQL Server

En SQL Server, il n’y aura pas de différence de performance entre une clause présente sur le ON ou sur le WHERE

Il est cependant recommandé pour augmenter la lisibilité du code de

  • Utiliser la clause ON pour spécifier uniquement la clause de jointure
  • Utiliser la clause WHERE pour spécifier les filtres de la requête

Source :

https://dataschool.com/how-to-teach-people-sql/difference-between-where-and-on-in-sql/