Aller au contenu

Transact-SQL

De Banane Atomic

Liens

Astuces

-- Commentaire
/* Commentaire */

-- variable
declare @maVariable char(10);
set @maVariable = 'ABCDE';
declare @maVariable char(10) = 'ABCDE'; -- en une ligne

-- print
print 'Texte ' + @maVariable + ' Texte ' + cast(@intVariable as nvarchar)

-- échapper un single quote
'it doesn''t work'

-- retour à la ligne CR LF
'première ligne' + char(13) + char(10) + 'deuxième ligne'

like

Mot clé permettant de faire une comparaison sur un modèle plutôt que sur une valeur exacte. 

select * from MaTable
where maColonne like '%BC%'
-- toutes les valeurs qui contiennent BC
_ 1 caractère
% plusieurs caractères
[abc]
[a-z]		 1 caractère dans la liste ou l’intervalle
[^abc]
[^a-z]		 1 caractère en dehors de la liste ou de l’intervalle
Pour utiliser _ % [ ^ comme caractère il faut les encadrer de [ ]

Replace COUNT by EXISTS

IF((SELECT COUNT(*) FROM MyTable) > 0)
IF EXISTS (SELECT 1 FROM MyTable)

IF((SELECT COUNT(*) FROM MyTable) = 0)
IF NOT EXISTS (SELECT 1 FROM MyTable)

Ordre d'évaluation des clauses - binding order

  1. FROM
  2. ON
  3. JOIN
  4. WHERE
  5. GROUP BY
  6. WITH CUBE
  7. HAVING
  8. SELECT
  9. DISTINCT
  10. ORDER BY
  11. TOP

Ainsi un alias de table définit dans FROM pourra être utilisé dans SELECT.
Un alias de colonne définit dans SELECT ne pourra être utilisé dans WHERE mais pourra être utilisé dans ORDER BY.

Bases de données

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = '[MyDb]')
BEGIN
  CREATE DATABASE [MyDb];
END;

drop database [MyDb];
-- Fatal error 615 occurred, avec SQL Server Express
ALTER DATABASE [MyDb] SET AUTO_CLOSE OFF

-- lister les BdD
SELECT * from sys.databases;
SELECT name,suser_sname(owner_sid) as 'owner' from sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');
-- avec show
show databases;

Tables

Lister les tables

-- Lister les tables de la BdD sélectionnée
show tables;

-- Lister les tables de la BdD MaBdD
show tables from MaBdD;

select table_name
from information_schema.tables
where table_type = 'base table'
    and table_name like '%xxx%';

Créer / supprimer une table

IF NOT EXISTS (SELECT * 
               FROM INFORMATION_SCHEMA.TABLES 
               WHERE TABLE_SCHEMA = 'schema'
               AND TABLE_NAME = 'MyTable')
BEGIN
    create table schema.MyTable (
        -- clé primaire
        Id int identity(1,1) not null constraint PK_MyTable primary key,
        Name varchar(50) not null,
        -- insère la date courante si rien n’est spécifié
        Date datetime not null default getdate()
    );
END

-- Supprimer une table
drop table schema.MaTable;

-- Supprimer toutes les tables
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
EXEC sp_MSForEachTable 'DROP TABLE ?'

Ajouter une colonne

IF NOT EXISTS( SELECT *
               FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_NAME = 'ma_table'
               AND TABLE_SCHEMA = 'mon_schema'
               AND COLUMN_NAME = 'ma_colonne')
BEGIN
    ALTER TABLE [mon_schema].[ma_table]
    ADD ma_colonne varchar(20),
    MyColumn INT NOT NULL CONSTRAINT DF_MyTable_MyColumn DEFAULT (0);
END

Modifier une colonne

ALTER TABLE mon_schema.ma_table
ALTER COLUMN ma_colonne DECIMAL(18, 14)
Management Studio (SSMS) interdit par défaut les changements qui requiert la recréation de la table.
Tools → Options → Designer → Prevent saving changes that require table re-creation

Supprimer une colonne

ALTER TABLE MyTable
DROP COLUMN MyColumn;

-- drop the column constraint first
ALTER TABLE MyTable
DROP CONSTRAINT [DF_MyTable_MyColumn]

Computed column

CREATE TABLE MyTable (
    [Name] [nvarchar](255) NOT NULL,
    [ShortName] AS IIF(LEN([Name]) = 8, RIGHT([Name], 3), NULL) PERSISTED
)

ALTER TABLE MyTable
ADD MyComputedColumn AS IIF(LEN([Name]) = 8, RIGHT([Name], 3), NULL) PERSISTED

Contraintes d'intégrités

-- ajout
alter table MaTable
    add constraint ck_maColonne check (maColonne>0));

-- supression
alter table MaTable
    drop constraint ck_maColonne;

Foreign Key

C'est une contrainte permettant de lier une colonne à une clé primaire d 'une autre table.
Dans l'exemple suivant les commandes sont adressées à des clients. Pour être sur que ces clients existent bel et bien, on ajoute une contrainte sur la colonne Client_ID de la table COMMANDE afin de celle-ci ne puisse contenir que des Client_ID de la table CLIENT. 

create table MyTable (
    Id int identity(1,1) not null constraint PK_MyTable primary key,
    OtherTable_Id int not null,
    constraint FK_MyTable_OtherTable foreign key (OtherTable_Id) references OtherTable(Id)
);

alter table MyTable
add constraint FK_MyTable_OtherTable
foreign key (OtherTable_Id) references OtherTable(Id);
Table CLIENT
Colonne Caractéristique
ID Primary Key
Nom
Table COMMANDE
Colonne Caractéristique
Commande_ID Primary Key
Client_ID Foreign Key
Quantité 
-- Suppression d'une clé étrangère
alter table MyTable
drop constraint FK_MyTable_OtherTable

-- Vérifier que la contrainte existe
SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

-- Lister les FK d'une table
SELECT 
  OBJECT_NAME(parent_object_id) AS [FK Table],
  name AS [Foreign Key],
  OBJECT_NAME(referenced_object_id) AS [PK Table]
FROM sys.foreign_keys
WHERE parent_object_id = OBJECT_ID('schema.Table');

Index

-- clustered index
CREATE CLUSTERED INDEX IX_table1_column1 ON database1.schema1.table1 (column1);

-- nonclustered index
CREATE NONCLUSTERED INDEX IX_table1_column1 ON table1 (column1);

-- nonclustered index avec une contrainte unique sur 3 colonnes en spécifiant leur ordre de trie
CREATE UNIQUE INDEX IX_table1_column1_column2_column3 ON table1 (column1 DESC, column2 ASC, column3 DESC);

-- drop index
DROP INDEX IX_table1_column1 ON table1

-- test si index existe déjà
IF EXISTS (SELECT name FROM sys.indexes  
            WHERE name = N'IX_table1_column1')   
    DROP INDEX IX_table1_column1 ON table1;
GO   
CREATE NONCLUSTERED INDEX IX_table1_column1
    ON table1 (column1);   
GO

Données

Vider une table

-- truncate vide la table, c'est le plus rapide, pas de rollback possible
truncate table Ma_Table;

-- delete supprime toutes les lignes une par une
delete from Ma_Table;

INSERT: Ajouter des lignes

INSERT INTO [mon_schema].[ma_table]
        ([colonne_1] ,[colonne_2]) /* optionnel, utile si les valeurs ne sont pas dans le même ordre que les colonnes */
    VALUES
        (N'valeur_1' ,N'valeur_2'),
        (N'valeur_11' ,N'valeur_12'); /* insérer une 2ème ligne */

/* test si l'enregistrement de la ligne a déjà étét fait */
IF NOT EXISTS( SELECT * 
	       FROM [mon_schema].[ma_table]
	       WHERE [colonne_1] = N'valeur_1')
BEGIN
    ...
END
GO

Désactiver la vérification de la foreign key

-- ne plus vérifier la contrainte (foreign key) lors de l'insertion de données
ALTER TABLE [MaTable] NOCHECK CONSTRAINT [FK_AutreTable_AutreColonne_Colonne1];
-- réactiver la vérification de la contrainte (foreign key) lors de l'insertion de données
ALTER TABLE [MaTable] CHECK CONSTRAINT [FK_AutreTable_AutreColonne_Colonne1];

Insert into: ajouter des données provenant d’une autre table

-- insert into MyTable (col1, col2) data from AnotherTable (colX, 1)
INSERT INTO schema.MyTable (col1, col2)
SELECT
    colX,
    1,
    (
        select Id from schema.AnotherTable2 where Name = 'xxx'
    ) as AnotherTable2Id
FROM schema.AnotherTable;

/* test si SELECT renvoie au moins une valeur, dans le cas contraire génère une erreur */
if @@ROWCOUNT=0
THROW 50001, 'ERREUR', 1;

THROW
@@ROWCOUNT

UPDATE: modifier les valeurs des lignes

-- set values based on conditions
update [table]
set [colonne1] = 'valeur',
    [colonne2] = 'valeur'
where [colonne3] = 0

-- set values based on a join
update alias1
set [column1] = 'valeur'
from [table1] as alias1
    inner join [table2] as alias2
        on alias2.columnA = alias1.column2

UPDATE if exist else INSERT

update MATABLE
set MACOLONNE = 'valeur'
where condition
if @@ROWCOUNT=0
    insert into MATABLE (MACOLONNE) values ('valeur')
update ... if @@ROWCOUNT fait un seul table/index scan
IF EXISTS (SELECT ...) UPDATE ... ELSE INSERT fait deux table/index scans, un pour le select et un pour l'update

DELETE: supprimer des lignes

delete from TABLE
where Id = 1 or Id = 2

Copie d’une table vers une autre (perte des clés)

select * into NEW_TABLE
from ORIGIN_TABLE
where 1 = 0 /* empèche la copie des données de la table */

Types de données

Chaînes de caractères

Type Description
char(n) longueur n. 1 < n < 8000 bytes.
varchar(n|max) longueur variable. 1 < n < 8000 bytes. max = 2^31 bytes (2Gio)
nchar(n) unicode, longueur n. 1 < n < 4000 bytes.
nvarchar(n|max) unicode, longueur variable. 1 < n < 8000 bytes. max = 2^31 bytes (2Gio)
sysname nvarchar(128), valeurs NULL interdites
  • Use char when the sizes of the column data entries are consistent.
  • Use varchar when the sizes of the column data entries vary considerably.
  • Use varchar(max) when the sizes of the column data entries vary considerably, and the size might exceed 8000 bytes.

Numériques exacts

Type Description
decimal(p,d)
numeric(p,d) 		numérique de précision p (nb total de chiffres) avec d décimaux. 1 < p < 38 et 1 < d < p. [-10^38 to 10^38-1]
Ex: decimal(8,3) → [-99999,999 to +99999,999]
int entier stocké sur 4 octets. [-2^31 to 2^31-1]
bigint entier stocké sur 8 octets. [-2^63 to 2^63-1]
smallint entier stocké sur 2 octets. [-2^15 to 2^15-1]
tinyint entier stocké sur 1 octets. [0 to 255]
money valeur monétaire sur 8 octets
smallmoney valeur monétaire sur 4 octets
bit entier de valeur 0 (FALSE), 1 (TRUE) ou NULL

Numériques approchés

Type Description
float(n) 1 < n < 53. [-1.79E+308 to -2.23E-308 , 0 , 2.23E-308 to 1.79E+308]
real float(24) sur 4 octets. [-3.40E+38 to -1.18E-38 , 0 , 1.18E-38 to 3.40E+38]

Date

Type Description
datetime date + heure. 01/01/1753 - 31/12/9999. Précision de 3.33 millisecondes
datetime2 date + heure. 01/01/0001 - 31/12/9999. Précision de 100 nanosecondes
datetimeoffset date + heure UTC. 01/01/0001 - 31/12/9999. Précision de 100 nanosecondes
smalldatetime date + heure. 01/01/1900 - 06/06/2079. Précision de 1 minute
date date. 01/01/0001 - 31/12/9999
time heure. Précision de 100 nanosecondes

EOMONTH 

-- the current database system timestamp as a datetime value without the database time zone offset
GETDATE()

MONTH(date) -- get the month numeric value of date, date could be a varchar 'YYYY/MM/DD' or a date type
YEAR(date)  -- get the year numeric value of date

-- convert datetime to date
CONVERT(DATE, GETDATE())

-- obtenir le premier jour du mois courant
-- date courante - 1 mois, puis dernier jour du mois, puis on ajoute 1 jour
DECLARE @first_day_of_month smalldatetime = DATEADD(DAY,1,EOMONTH(GETDATE(), -1))
-- autre méthode
DECLARE @first_day_of_month smalldatetime = DATEFROMPARTS(YEAR(@today), MONTH(@today), 1)

Données binaires

Type Description
binary(n) longueur fixe de n octets. 1 < n < 8000
varbinary(n|max) longueur variable de n octets. 1 < n < 8000. max = 2^31 = 2 Gio

Autres

Type Description
uniqueidentifier GUID de 16 octets. Ex: NEWID()
sql_variant n'importe quel type de données. max 8016 octets
cursor
xml

SELECT

SELECT mt.col1 AS c1 -- column alias
    , 'txt' AS c2 -- static value
FROM MyTable AS mt -- table alias
WHERE (
        mt.col1 > 0
        AND mt.col1 < 100
        )
    OR mt.col1 = - 1

DISTINCT

Retire les doublons (duplicates) du résultat. 

SELECT DISTINCT mt1.col1 AS c1
FROM MyTable1 AS mt1
WHERE mt1.col2 > 0

Sélection des lignes

-- Sélection des 10 premières lignes
SELECT TOP 10 *
FROM MyTable
ORDER BY Id;

-- Sélection de 50% des premières lignes
SELECT TOP 50 PERCENT *
FROM MyTable
ORDER BY Id;

-- Sélection des 10 dernières lignes
SELECT TOP 10 *
FROM MyTable
ORDER BY Id DESC;

-- Sélection de la ligne numéro N (paging)
SELECT *
FROM MyTable
ORDER BY Id
OFFSET N-1 ROWS
FETCH NEXT 1 ROWS ONLY

Derived table

An inner query defined in the FROM clause of an outer query.
The scope of a derived table is the duration of the outer query.

There is probably a better way to get the same result. 
-- outer query to get the users belonging to at least 2 groups
SELECT Name, NumberOfGroups
FROM
(
    -- derived table: get the number of groups a user belongs to
    SELECT Name, COUNT(*) AS NumberOfGroups
    FROM Users
    GROUP BY UserId
) AS NumberOfGroupsPerUser
WHERE NumberOfGroups > 1

Jointures

Erreur lors de la création de la vignette : /bin/bash: /usr/bin/convert: No such file or directory Error code: 127

Group
Id Name
1 G1
2 G2
3 G3
User
Id Name Group_Id
1 U1 1
2 U2 2
3 U3 null


Inner join - jointure interne ou d'égalité

Liaison entre deux tables basée sur une égalité.
Retourne les paires des lignes qui correspondent à la jointure. 

select u.Name as 'user', g.Name as 'group'
from [User] as u
join [Group] as g
on u.Group_Id = g.Id;
user group
U1 G1
U2 G2

Left / Right / Full outer join - jointure externe

Retourne les paires des lignes qui correspondent à la jointure:

left plus toutes les lignes de la table de gauche qui n'ont pas de lien dans la table de droite
right plus toutes les lignes de la table de droite qui n'ont pas de lien dans la table de gauche
full plus toutes les autres lignes des 2 tables qui ne correspondent pas à la jointure 
select u.Name as 'user', g.Name as 'group'
from [User] as u
left/right/full join [Group] as g
on u.Group_Id = g.Id;
left
user group
U1 G1
U2 G2
U3 null
right
user group
U1 G1
U2 G2
null G3
full
user group
U1 G1
U2 G2
U3 null
null G3


LEFT JOIN / IS NULL vs NOT EXISTS

NOT EXISTS is faster because it stops its execution as soon as a record matches the criteria whereas LEFT JOIN always joins all the records. 

-- fetch the groups without users
select g.Id
from Group g
left join User u
on u.GroupId = g.Id
where u.GroupId is null

-- same result with "not exists"
select g.Id
from Group g
where not exists
(
    select 1
    from User u
    where u.GroupId = g.Id
)

Cross join

Produit cartésien (cross-product) de 2 tables. 

select u.Name as 'user', g.Name as 'group'
from [User] as u
cross join [Group] as g;
user group
U1 G1
U2 G1
U3 G1
U1 G2
U2 G2
U3 G2
U1 G3
U2 G3
U3 G3

Operators

Union

Concaténation des résultats de 2 requêtes. Les doublons sont supprimés.
union all permet de conserver les doublons.

UNION ALL vs OR condition 
select Name from [User]
union
select Name from [Group];
Name
G1
G2
G3
U1
U2
U3

Intersect - Except

SELECT col1, col2
FROM MyTable1
EXCEPT
SELECT col1, col2
FROM MyTable2

Cross / Outer Apply

Cross Apply permet de faire une jointure interne entre une table et une table-valued fonction (fonction qui retourne une table).
Ce qui revient à exécuter une TVF pour chaque ligne d'une table.
Outer Apply permet de faire une jointure externe sur le même principe. 

-- pour chaque personne, appel de la tvf ufnGetContactInformation qui va ajouter des colonnes au résultat
SELECT *
FROM Person.Person AS p
CROSS APPLY dbo.ufnGetContactInformation(p.Id)

Merge

Permet de mettre à jour la table Destination avec les éléments de la table Source. 

merge [Destination Table] as target
using [Source Table] as source
on target.id = source.id
when matched and target.field1 = source.field1 then
    delete  
when match then
    update set
    field1 = source.field1,
    field2 = source.field2
when not matched then
    insert (id, field1, field2)
    values (source.id, source.field1, source.field2);

Colonnes calculées

select Salaire, Commission,
  round(Salaire*1.1, 0) as SalairePlusDixPourcent,
  -- on utilise coalesce pour remplacer les Commission null par 0
  Salaire + coalesce(Commission,0) as SalairePlusCommission
from Employés

Référencer une colonne calculée

order by

-- seul order by permet de référencer un alias
select Salaire, Commission, round(Salaire*1.1, 0) as SalairePlusDixPourcent
from Employés
order by SalairePlusDixPourcent

Sous-requête

select Salaire, Commission, SalairePlusDixPourcent, SalairePlusCommission, sum(SalairePlusCommission) over() as Somme from
(
  select Salaire, Commission,
    round(Salaire*1.1, 0) as SalairePlusDixPourcent,
    Salaire + coalesce(Commission,0) as SalairePlusCommission
  from Employés
) as CalcSum

WITH common_table_expression

-- calculer la somme pour une colonne calculée
with CalcSum as
(
  select Salaire, Commission,
    round(Salaire*1.1, 0) as SalairePlusDixPourcent,
    Salaire + coalesce(Commission,0) as SalairePlusCommission
  from Employés
)
select Salaire, Commission, SalairePlusDixPourcent, SalairePlusCommission, sum(SalairePlusCommission) over() as Somme
from CalcSum

Opérateurs

if else

if @var = 0
    print 'ok'
else
begin
    print 'ko'
    print 'ko multi-lignes'
end

in

select * from MaTable
where col1 in (0, 1)
-- équivalent à
where col1 = 0 or col1 = 1

-- Sous-requêtes
-- Liste les Id de tous les Manager
-- puis récupère le Nom pour chaque Id
select Id, Nom from Employés
where Id in (select Manager from Employés)

-- Liste les Id de tous les Manager
-- puis liste tous les Employés qui n'ont pas ces Id
-- attention la sous-requête ne doit pas retourner de valeurs null
select Id, Nom from Employés
where Id not in (select Manager from Employés where Manager is not null)

EXISTS

SELECT 1 est équivalent à SELECT * ou SELECT 0 
-- séléctionne toutes les lignes de Table1 qui ont un Id dans Table2
SELECT * FROM Table1 AS t1
WHERE EXISTS (SELECT 1 FROM Table2 AS t2 WHERE t1.Id = t2.Id )

-- séléctionne toutes les lignes de Table1 qui n'ont un Id dans Table2
SELECT * FROM Table1 AS t1
WHERE NOT EXISTS (SELECT 1 FROM Table2 AS t2 WHERE t1.Id = t2.Id )

-- séléctionne tous clients de la table Clients qui ne sont pas dans la table BlackListedClients
select *
from Clients AS c 
where not exists
(select *   
    from BlackListedClients as blc  
    where c.Id = blc.id)

-- équivalant à
select *
from Clients AS c
where c.Id not in
(select blc.id
    from BlackListedClients as blc  
    where c.Id = blc.id)

while

declare @i int
set @i = 0

while  @i < 50
begin
    print @i
    set @i = @i + 1
end

rollup

Agrège par hiérarchie de valeurs. 

# groupe les éléments de colonne1 et compte leurs occurrences
# la première ligne somme toutes les occurrences → rollup
select colone1, count(colonne1) as 'Occurrence'
from MaTable
group by colonne1 with rollup
order by 'Occurrence' desc

# colonne1 | Occurrence
# NULL     | 50
# 2        | 30
# 5        | 20

Fonctions

Aggregate functions

SUM, MIN, MAX, STRING_AGG 

-- regroupe les produits par location et somme leurs quantités
SELECT LocationId, SUM(Quantity) AS QuantityByLocation, STRING_AGG(ProductId, ',')
FROM ProductInventory
GROUP BY LocationId
HAVING SUM(Quantity) > 1000 -- filtre sur le résultat de la colonne agrégée

-- somme des quantités de tous les produits
SELECT SUM(Quantity) AS TotalQuantity
FROM Production.ProductInventory

-- pour chaque produit, affiche les quantités min et max stockées dans les différentes locations
SELECT p.Name, MIN(pi.Quantity), MAX(pi.Quantity)
FROM Production.Product AS p
INNER JOIN Production.ProductInventory AS pi
ON p.ProductID = pi.ProductID
GROUP BY p.Name

OVER / window function

-- total sum
SELECT
  id,
  salary,
  SUM(salary) OVER() AS total_salary -- will contain the sum of all employee salaries in each row
FROM employees;

-- sum by partition
SELECT
  id,
  department_id,
  salary,
  SUM(salary) OVER(partition by department_id) AS department_salary -- will contain the sum of employee salaries for the current department
FROM employees;

-- rolling / running sum
SELECT
  id,
  salary,
  SUM(salary) OVER(order by id) AS running_salary -- will contain the incremental sum of salaries
FROM employees
order by id;

Count

-- compte le nombre de ligne d'une table
SELECT COUNT(*)
FROM Product

-- compte le nombre d'occurences différente, ne prend pas en compte NULL
SELECT COUNT(DISTINCT Color)
FROM Product

-- pour chaque produit, compte le nombre d'étagère
SELECT p.Name, COUNT(pi.Shelf)
FROM Production.Product AS p
INNER JOIN Production.ProductInventory AS pi
ON p.ProductID = pi.ProductID
GROUP BY p.Name
HAVING COUNT(pi.Shelf) > 1  -- filtrer seulement les produits qui ont plus d'une étagère

Math functions

ROUND arrondi
RAND chiffre aléatoire entre 0 et 1
POWER - SQRT puissance - racine carrée
CEILING - FLOOR plus petit entier supérieur ou égal
plus grand entier inférieur ou égal 
SELECT Price,
       ROUND(Price, 1), -- 1 décimale
       ROUND(Price, 2), -- 2 décimales
       ROUND(Price, -1) -- 1 entier (123 → 120)

SELECT RAND() -- le serveur choisit un seed différent à chaque exécution
SELECT RAND(1) -- on force le seed, le chiffre généré est le même si le seed est le même

Parse, Convert et Cast

-- PARSE: string to date/time and number types only
SELECT PARSE('12/31/2019' AS DATE);
SELECT PARSE('31/12/2019' AS DATE USING 'fr-FR');

-- retourne NULL au lieu d'une erreur si la parse n'a pas marché
SELECT TRY_PARSE('31/12/2019' AS DATE);

-- CAST: data type to another data type
SELECT CAST(9.5 AS int);
SELECT TRY_CAST(9.5 AS decimal(6,4));

-- CONVERT: same as CAST with a style
SELECT CONVERT (DATE, '12/31/2019');
SELECT TRY_CONVERT (DATE, '31/12/2019');

-- ISNUMERIC, ISDATE
DECLARE @Var1 NVARCHAR(50) = '123'
DECLARE @Var2 NVARCHAR(50) = '01/01/1980'
SELECT ISNUMERIC (@Var1), ISNUMERIC (@Var2), ISDATE(@Var1), ISDATE(@Var2)
-- 1 0 0 1

CONVERT styles

Time functions

SELECT SYSDATETIME() 2019-04-17 14:36:21.9359144
SELECT SYSDATETIMEOFFSET() 2019-04-17 14:36:21.9359144 +02:00
SELECT SYSUTCDATETIME() 2019-04-17 12:36:21.9359144
SELECT CURRENT_TIMESTAMP 2019-04-17 14:36:21.933
SELECT GETUTCDATE() 2019-04-17 12:36:21.933
SELECT GETDATE() 2019-04-17 14:36:21.933 
-- subpart
SELECT DAY(GETDATE()) -- 17
SELECT MONTH(GETDATE()) -- 4
SELECT YEAR(GETDATE()) -- 2019

SELECT DATEPART(d, GETDATE()) -- 17
SELECT DATEPART(m, GETDATE()) -- 4
SELECT DATEPART(yy, GETDATE()) -- 2019

SELECT DATENAME(m, GETDATE()) -- April

-- build date
SELECT DATEFROMPARTS(2019, 4, 17)
SELECT DATETIMEFROMPARTS(2019, 4, 17, 12, 0, 0, 0)
SELECT SMALLDATETIMEFROMPARTS(2019, 4, 17, 12, 0)
SELECT DATETIME2FROMPARTS(2019, 4, 17, 12, 0, 0, 0, 0)
SELECT DATETIMEOFFSETFROMPARTS(2019, 4, 17, 12, 0, 0, 0, 2, 0, 0)
SELECT TIMEFROMPARTS(12, 0, 0, 0, 0)

-- diff
SELECT DATEDIFF(yy, '1/1/2018', '1/1/2019') -- 1
SELECT DATEDIFF(m, '1/1/2018', '1/1/2019')  -- 12
SELECT DATEDIFF(d, '1/1/2018', '1/1/2019')  -- 365

-- modification
SELECT DATEADD(yy, 1, CONVERT(date, getdate())) -- 2020-04-17
SELECT DATEADD(m, 1, CONVERT(date, getdate()))  -- 2019-05-17
SELECT DATEADD(d, 1, CONVERT(date, getdate()))  -- 2019-04-18
SELECT EOMONTH(GETDATE())    -- 2019-04-30
SELECT EOMONTH(GETDATE(), 1) -- 2019-05-31

CHOOSE

Retourne la nième valeur. 

SELECT CHOOSE(2, 'A', 'B', 'C') -- B

IIF

Retourne une des 2 valeurs en fonction du résultat du test. 

SELECT IIF(1 = 1, 'TRUE', 'FALSE')

CASE

-- simple case
SELECT CASE 2
    WHEN 1 THEN 'A'
    WHEN 2 THEN 'B'
    WHEN 3 THEN 'C'
    ELSE 'Z'
END

-- search case
DECLARE @TempTable TABLE (Id INT NOT NULL PRIMARY KEY, Value NVARCHAR(50) NOT NULL);
INSERT @TempTable VALUES (1, 'A'), (2, 'B'), (3, 'C'), (4, 'D');

SELECT *,
CASE
    WHEN id % 2 = 0 THEN 'Pair'
    WHEN id % 2 = 1 THEN 'Impair'
    ELSE 'Error'
END AS "Pair / Impair"
FROM @TempTable
Id Value Pair / Impair
1 A Impair
2 B Pair
3 C Impair
4 D Pair

ISNULL

Retourne la première valeur si elle est non-nulle, sinon la deuxième. Ne prend que 2 arguments. 

SELECT ISNULL('Value', 'Other') -- Value
SELECT ISNULL(NULL, 'Other')    -- Other

COALESCE

Retourne la première valeur non-nulle. Prend 2 arguments ou plus. 

COALESCE(NULL, NULL, 'Value1', 'Value2') -- Value1

Transaction and rollback

DECLARE @TransactionName VARCHAR(20) = 'Transaction1';  
   
-- start transaction
BEGIN TRAN @TransactionName;

-- update table
UPDATE [table]
SET [column1] = 1
WHERE [column2] = 1;

-- display the modifications
SELECT [column1],
       [column2]
FROM [table];

-- rollback the transaction
ROLLBACK TRAN @TransactionName;

Stored procedure / procédure stockée

-- create / alter
CREATE PROCEDURE schema.USP_MySP
    @P_MyParameter BIT NULL
AS
BEGIN
END;

-- run
DECLARE @p4 [schema].[T_IdListTable]
INSERT INTO @p4 VALUES (1)
INSERT INTO @p4 VALUES (2)

DECLARE @p5 INT = 0

EXEC [schema].[USP_MyStoredProc]
    @P_One = '2019-02-12',
    @P_Two = NULL,
    @P_Three = DEFAULT, -- for tables
    @P_Four = @p4,
    @P_Five = 0;

-- rename
EXEC sp_rename 'schema.OldName', 'NewName';

Delete all SP

DECLARE c CURSOR FOR
SELECT [name], [schema_id] FROM sys.objects WHERE type = 'p';
OPEN c;
FETCH NEXT FROM c
INTO @spName,
     @schemaName;
WHILE @@fetch_status = 0
BEGIN
    SET @dropCmd = 'drop procedure [' + SCHEMA_NAME (@schemaName) + '].[' + @procName + ']';
    EXEC (@dropCmd);
    FETCH NEXT FROM cur
    INTO @spName,
         @schemaName;
END;
CLOSE c;
DEALLOCATE c;

Table-valued functions

DECLARE @p1 [schema].[T_IdListTable]

SELECT * FROM [schema].[FN_MyFunction](@p1, NULL, 0)
-- test if a TVF exists in the db
IF EXISTS (SELECT 1
           FROM INFORMATION_SCHEMA.ROUTINES
           WHERE SPECIFIC_SCHEMA = 'schema'
                 AND SPECIFIC_NAME = 'FN_MyFunction'
                 AND ROUTINE_TYPE = 'FUNCTION')
    PRINT 'Exists'
ELSE
    PRINT 'Doesn''t exist'

Store procedure vs function

  • FN are used to filter data only, no DML(INSERT/UPDATE/DELETE) are allowed.

Cursor : boucle sur toutes les noms des tables de la BdD

DECLARE tableCursor CURSOR FOR 
    SELECT [name] FROM sys.Tables
    WHERE [type]='U' -- Table
    ORDER BY [name]

DECLARE @tableName sysname

OPEN tableCursor

-- Charge la première valeur dans tableName
FETCH NEXT FROM tableCursor INTO @tableName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
    print 'Table name: ' + @tableName
    -- Charge la valeur suivante dans tableName
    FETCH NEXT FROM tableCursor INTO @tableName
END
-- Close cursor and free memory
CLOSE tableCursor
DEALLOCATE tableCursor
Il n'est pas possible d'utiliser une variable comme nom de colonne dans un select.

User-Defined Table Type

Programmability → Types → User-Defined Table Types 

CREATE TYPE [IdListTable] AS TABLE(
    [Id] [INT] NOT NULL
)
GO
-- autoriser le rôle MyDbRole à utiliser le type IdListTable
GRANT EXECUTE ON TYPE:: [IdListTable] TO [MyDbRole]
GO

-- utiliser le UDTT comme paramètre d'une SP
DECLARE @LIST AS [IdListTable];

INSERT INTO @LIST (Id)
VALUES (10), (20)

EXEC [USP_MyStoreProc]
     @P_Param1 = @LIST
Passer un User-Defined Table Type avec SQLParameter en C#.

Modifier un Type

Le Type ne peut être modifier car il est utilisé ailleurs. 

-- renommer le type
-- ne pas mettre le schema dans le nouveau nom
EXEC sp_rename '[schema].[T_MyTypeTable]', 'T_MyTypeTable2'

-- re-créer le type avec les modifications
CREATE TYPE [schema].[T_MyTypeTable] AS TABLE(
-- ...
)

-- générer le code de rafraîchissement (remplacer T_MyTypeTable)
SELECT '[' + s.name + '].[' + OBJECT_NAME (d.referencing_id) + ']' AS 'Name',
       'EXEC sp_refreshsqlmodule ''[' + s.name + '].[' + OBJECT_NAME (d.referencing_id) + ']''' AS 'Cmd to execute'
FROM sys.sql_expression_dependencies AS d
INNER JOIN sys.objects AS o
    ON d.referencing_id = o.[object_id]
INNER JOIN sys.schemas AS s
    ON o.[schema_id] = s.[schema_id]
WHERE referenced_class_desc = 'TYPE'
      AND referenced_entity_name = 'T_MyTypeTable';

-- exécuter le code de rafraichissement
EXEC sp_refreshsqlmodule '[schema].[FN_MyFunction]'

-- supprimer le type renommé
DROP TYPE [pri.T_MyTypeTable2]

Temp table

  • They are created in the tempdb database
IF OBJECT_ID ('tempdb..#TempTable') IS NULL
    CREATE TABLE #TempTable
    (
        Id INT NOT NULL PRIMARY KEY,
        Value NVARCHAR(50) NOT NULL
    );
ELSE
    TRUNCATE TABLE #TempTable;

DROP TABLE IF EXISTS #TempTable;

-- create the TempTable and insert data from another table
SELECT *
INTO #TempTable
FROM AnotherTable;

Table variable

  • They are created in the tempdb database
  • They are only visible in the batch where they have been created
  • No need to drop them, they persist as long as the session is open, just like any other variable
  • They are unaffected by transactions
DECLARE @TableVar TABLE
(
    Id INT NOT NULL PRIMARY KEY,
    Value NVARCHAR(50) NOT NULL
);

INSERT @TableVar
VALUES (1, 'One');

-- insert lines from a query
INSERT INTO @TableVar (Id, Value)
SELECT Id, Value
FROM AnotherTable;

-- empty the table
DELETE FROM @TableVar;

Vue - View

Une vue est un table virtuelle, elle n'occupe pas d'espace disque pour ses données. Elle ne stocke que sa requête.
C'est l'équivalent d'une fonction sans paramètre. 

IF EXISTS (SELECT * 
           FROM INFORMATION_SCHEMA.TABLES 
           WHERE TABLE_SCHEMA = 'mon_schema' 
           AND  TABLE_NAME = 'ma_vue')
BEGIN
    drop view mon_schema.ma_vue;
end
go

CREATE VIEW mon_schema.ma_vue
AS   
SELECT *
  FROM ma_table
  WHERE [Colonne1] = 10

GO
create view doit être encadré par 2 instructions go

Indexed view

Limitations:
  • no outer joins
  • no sub-queries
  • no reference to other views
  • no unions
 
-- Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON;

-- Create view with SCHEMABINDING.
IF OBJECT_ID ('pri.VW_Item', 'view') IS NOT NULL
    DROP VIEW pri.VW_Item;
GO
CREATE VIEW pri.VW_Item
WITH SCHEMABINDING
AS
SELECT ItemId, Description
FROM dbo.Item;
GO

-- Create an indexes
CREATE UNIQUE CLUSTERED INDEX FK_Item_VW_Item_ItemId
ON pri.VW_Item (ItemId);
GO

CREATE INDEX IDX_UN_VW_Item_Description
ON pri.VW_FreightEntryWeightCondition (Description);
GO

CTE - Common Table Expression

Stocke le résultat d'une requête pour pouvoir la réutilser. 

;WITH
ProductQty AS
(
    SELECT ProductID, Quantity
    FROM ProductInventory
)
SELECT ProductID, SUM(Quantity) AS SumQuantity
FROM ProductQty
GROUP BY ProductID

-- il n'est pas possible d'avoir une CTE dans une autre CTE
-- mais on peut déclarer une 2ème CTE qui référence la première
;WITH
Cte1 AS
(
    SELECT Id, Value
    FROM MyTable1
),
Cte2 As
(
    SELECT Id, Value
    FROM Cte1
)
SELECT Id, Value
FROM Cte2
GROUP BY ProductID
Bien penser à terminer la commande précédant la CTE avec un ;

Récursif

-- Get all the employees who does not have a manager
-- Then recursively get all their subordinates

;WITH
cte_hierarchy AS
(
    SELECT EmployeeId,
           Name,
           ManagerId
    FROM Employees
    WHERE ManagerId IS NULL
    UNION ALL
    SELECT e.EmployeeId,
           e.Name,
           e.ManagerId
    FROM Employees e
        INNER JOIN cte_hierarchy cte
            ON cte.EmployeeId = e.ManagerId
)
SELECT * FROM cte_hierarchy;

String

SELECT LEFT('12345', 3) -- 123
SELECT RIGHT('12345', 3) -- 345

SELECT LEN('12345 ') -- 5 ne prend pas en compte les espaces de fin de string
SELECT DATALENGTH('12345 ') -- 6

SELECT LTRIM('  abc  ') -- 'abc  '
SELECT RTRIM('  abc  ') -- '  abc'

Découper une chaîne de caractères

SET @string = 'string'
SET @troisPremiersChar = LEFT(@string,3) -- str
SET @sub = SUBSTRING(@string, 2, 4)      -- trin

Concat

-- concat le résultat d'un select sur une seule ligne
DECLARE @cmd NVARCHAR(MAX) = ''
SELECT @cmd = @cmd + N'tSQLt.FakeTable [' + tableName + N'];' FROM tableNames;

Autres

Variable comme nom de colonne dans un select

Comme ce n'est pas possible, une astuce est de générer du code sql avec ces noms de colonnes. 

SELECT 'if exists (select 1 from Reporting.vFatcaXml where ' + Column_name + ' like ''%xxx%'')
begin 
	print ''' + Column_name + '''
end;'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MaTable'
and DATA_TYPE = 'varchar'
and table_schema = 'dbo'

-- ce qui génère pour chaque colonne varchar de MaTable
select 1 from Reporting.vFatcaXml where Colonne1 like '%xxx%')
begin 
	print 'Colonne1'
end;

THROW

THROW 50001, 'Message', 1;

/* Message avec des paramètres */
EXEC sys.sp_addmessage 50001, 16, '''%s'' is not valid', @replace='replace';
DECLARE @Message varchar(max) = FORMATMESSAGE(50001, 'paramètre'); /* marche avec les paramètres des SqlCommand C# */
THROW 50001, @Message, 1;

/* un message id ne peut être ajouté qu'une fois sinon un warning est généré
« You must specify 'REPLACE' to overwrite an existing message »
Pour éviter cela on peut ajouter « @replace='replace' » ou ne pas exécuter plus d'une fois « sp_addmessage » */
IF NOT EXISTS( SELECT * FROM sys.messages WHERE message_id = 50001)
BEGIN
EXEC sys.sp_addmessage 50001, 16, '''%s'' is not a valid'
END

sp_addmessage

Obtenir les clés primaires d’une table

select c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
	INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
	and CONSTRAINT_TYPE = 'PRIMARY KEY'
	and c.TABLE_NAME = pk.TABLE_NAME
	and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

Requête sur deux bases de données

select * from [base1].[dbo]._EVENTS
select * from [base2].[dbo]._EVENTS

Limitation sur les noms des identifiants

Les identifiants qui contiennent des caractères spéciaux (comme espace) doivent être encapsulés entre [ ]
Tous les caractères sont authorisés sauf [
Les identifiants contiennent au maximum 128 caractères.

Obtenir des infos sur un élément

sp_help 'Schema.ConstraintName'

charset

-- liste des types de colonnes et leurs charset
select data_type, character_set_catalog, character_set_schema, character_set_name, collation_catalog, collation_schema, collation_name, count(*) count
from information_schema.columns
group by data_type, character_set_catalog, character_set_schema, character_set_name, collation_catalog, collation_schema, collation_name;

-- le charset et la collation d'une colonne
select character_set_name, collation_name
from information_schema.columns
where TABLE_SCHEMA = 'xxx' and TABLE_NAME = 'yyy' and COLUMN_NAME = 'zzz'
iso_1 → ISO 8859-1
UNICODE → UCS-2

Générer toutes les combinaisons

-- table temporaire pour stocker l'ensemble des valeurs possibles
declare @table_temp table (i tinyint)
-- compteur, initialisé à 1
declare @i tinyint
set @i = 1
-- on définit la valeur max pour l'ensemble des valeurs possibles
while  @i < 6
begin
	-- on remplit la table temporaire avec l'ensemble des valeurs possibles
	insert into @table_temp values (@i)
	set @i = @i + 1
end;

/* @table_temp
i
-
1
2
3
4
5
*/

with
w1 as (
SELECT t1.i as i1, t2.i as i2
  FROM @table_temp t1
  cross join @table_temp t2
  where t1.i < t2.i)
SELECT w1.*, t1.i as i3
  FROM @table_temp t1
  cross join w1
  where w1.i2 < t1.i

/* Résultat: combinaison de 3 éléments parmi (1,2,3,4,5)
1	2	3
1	2	4
1	2	5
1	3	4
1	3	5
1	4	5
2	3	4
2	3	5
2	4	5
3	4	5
*/

Stocker la première ligne d'un select dans une variable

DECLARE @MyVar nvarchar(50)
SELECT TOP 1 @MyVar = Col1
FROM MyTable

Lister les tables dans lesquelles un clé primaire est utilisée comme clé étrangère

SELECT objs1.name AS ConstraintName,
       objs2.name AS FromTable,
       objs3.name AS ToTable
FROM sys.foreign_key_columns fkcols
    INNER JOIN sys.objects objs1
        ON objs1.object_id = fkcols.constraint_object_id  -- ID de la contrainte FOREIGN KEY.
    INNER JOIN sys.objects objs2
        ON objs2.object_id = fkcols.parent_object_id      -- ID du parent de la contrainte, qui est l'objet référençant.
    INNER JOIN sys.objects objs3
        ON objs3.object_id = fkcols.referenced_object_id  -- ID de l'objet référencé, qui contient la clé candidate.
WHERE objs3.name = 'AmountCalculationMethod';

Erreurs

Cannot insert explicit value for identity column when IDENTITY_INSERT is set to OFF

Les champs identity sont auto générés. Pour pouvoir y forcer une valeur il faut activer IDENTITY_INSERT. 

SET IDENTITY_INSERT MaTable ON

INSERT MaTable (Id) VALUES (1)

SET IDENTITY_INSERT MaTable  OFF
Récupérée de « http://wiki.bananeatomic.fr/index.php?title=Transact-SQL&oldid=29992 »