Le coin de Guy Boulais

En effectuant un requête sur la colonne CRDATE de SYSDATABASE on obtient la date de création de chaque DB.

À chaque re-démarrage d’une instance SQL Server la DB tempdb est re-crée dans son état initial avec la date de création.

Pour l’état de l’agent, on vérifie dans SYSPROCESSES s’il existe une rangée pour celui-ci:

SET NOCOUNT ON
DECLARE @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5)
SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'
SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60
IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0
SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))
ELSE
SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))
 -((DATEDIFF( mi, @crdate,GETDATE()))/60)*60

PRINT 'SQL Server "' + CONVERT(VARCHAR(20),
  SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '
  +@hr+' hours & '+@min+' minutes'

IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses 
  WHERE program_name = N'SQLAgent - Generic Refresher')
BEGIN
PRINT 'SQL Server is running but SQL Server Agent
  <<NOT>> running'
END
ELSE BEGIN
PRINT 'SQL Server and SQL Server Agent both are running'
END

Résultat:

SQL Server "TEST" is Online for the past 245 hours & 12 minutes
SQL Server and SQL Server Agent both are running

1-Creons 2 tables identiques afin de simplifier les choses

CREATE TABLE T1
(ID INT, RowNo INT, Value INT);

CREATE TABLE T2
(ID INT, RowNo INT, Value INT);

2-La commande MERGE suivante va:
Si existant dans T2 main non-existant dans T1:  faire in INSERT
Si existant dans T2 et existant dans T1: faire un UPDATE
Si non-existant dans T2 et existant dans T1: faire un DELETE

MERGE T1 t       -- Destination 
USING T2 s       -- Source 
ON t.ID = s.ID AND t.RowNo = s.RowNo
  WHEN MATCHED 
    THEN
      UPDATE SET Value = s.Value
  WHEN NOT MATCHED   -- Target
    THEN
       INSERT (ID, RowNo, Value)
         VALUES (s.ID, s.RowNo, s.Value)
  WHEN NOT MATCHED BY SOURCE 
    THEN
      DELETE;

Définir une variable XML:

DECLARE @ListeClient XML
SET @ListeClient =
'<?xml version="1.0" encoding="UTF-8"?>
<!-- A list of current clients -->
<People>
<Person id="1234">
<FirstName>John</FirstName>
<LastName>Doe</LastName>
</Person>
<Person id="5678">
<FirstName>Jane</FirstName>
<LastName>Doe</LastName>
</Person>
</People>'
SELECT @ListeClient
GO

<!-- A list of current clients --><People><Person id="1234"><FirstName>John</FirstName><LastName>Doe</LastName></Person><Person id="5678"><FirstName>Jane</FirstName><LastName>Doe</LastName></Person></People>

Création d’une table avec colonne XML:

USE GUY
GO
IF OBJECT_ID('dbo.Clients') IS NOT NULL
DROP TABLE dbo.Clients
GO
CREATE TABLE dbo.Clients
(
StoreID INT IDENTITY PRIMARY KEY,
ClientInfo XML NOT NULL
)
GO

Insérons notre contenu XML de la variable ListeClient dans la table:

DECLARE @ListeClient XML
SET @ListeClient =
'<?xml version="1.0" encoding="UTF-8"?>
<People>
<Person id="1234">
<FirstName>John</FirstName>
<LastName>Doe</LastName>
</Person>
<Person id="5678">
<FirstName>Jane</FirstName>
<LastName>Doe</LastName>
</Person>
</People>'
INSERT INTO dbo.Clients (ClientInfo)
VALUES(@ListeClient)
GO

On peut simplifier le traitement en passant la variable XML à une Stored Procédure:

USE GUY
GO
IF OBJECT_ID('dbo.AjoutClientInfo', 'P') IS NOT NULL
DROP PROCEDURE dbo.AjouClientInfo
GO
CREATE PROCEDURE dbo.AjouClientInfo
@Clients XML
AS
INSERT INTO dbo.Clients (ClientInfo)
VALUES(@Clients)
GO

L’insertion se fera ainsi au lieu de faire la commande INSERT:

EXEC dbo.AjouClientInfo @ListeClient

Requête sur la colonne XML:

USE GUY
GO
SELECT ClientInfo.query('/People')
FROM dbo.Clients;

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<People><Person id="1234"><FirstName>John</FirstName><LastName>Doe</LastName></Person><Person id="5678"><FirstName>Jane</FirstName><LastName>Doe</LastName></Person></People>

(1 row(s) affected)

SELECT ClientInfo.query('/People/Person')
FROM dbo.Clients;

<Person id="1234"><FirstName>John</FirstName><LastName>Doe</LastName></Person><Person id="5678"><FirstName>Jane</FirstName><LastName>Doe</LastName></Person>

Pour extraire une personne avec un ID précis:

SELECT ClientInfo.query('/People/Person[@id=1234]')
FROM dbo.Clients;

<Person id="1234"><FirstName>John</FirstName><LastName>Doe</LastName></Person>

Comment extraire seulement son prénom?:

SELECT ClientInfo.query('/People/Person[@id=1234]/FirstName')
FROM dbo.Clients;

<FirstName>John</FirstName>

Extraire le prénom de la personne à la position numéro 1:

SELECT ClientInfo.query('/People/Person[1]/FirstName')
FROM dbo.Clients;

<FirstName>John</FirstName>

Pour extraire seulement la valeur et non le nom le XML, utiliser la méthode .VALUE:

SELECT ClientInfo.value('(/People/Person[1]/FirstName)[1]','varchar(20)')
FROM dbo.Clients;

--------------------
John

SELECT ClientInfo.value('(/People/Person[1])[1]','varchar(20)')
FROM dbo.Clients;

--------------------

JohnDoe

 Retourner une valeur numérique:

SELECT ClientInfo.value('(/People/Person/@id)[1]', 'int')
FROM dbo.Clients;

-----------

1234

 Calculer le nombre de personnes:

SELECT ClientInfo.value('count(/People/Person)', 'int')
FROM dbo.Clients; 

-----------

2

Si on ajoute une nouvelle rangée, bien sur le résultat va doubler:

DECLARE @ListeClient XML
SET @ListeClient =
'<?xml version="1.0" encoding="UTF-8"?>
<!-- A list of current clients -->
<People>
<Person id="4567">
<FirstName>Guy</FirstName>
<LastName>Boulais</LastName>
</Person>
<Person id="5432">
<FirstName>Bob</FirstName>
<LastName>Marley</LastName>
</Person>
</People>'
INSERT INTO dbo.Clients (ClientInfo)
VALUES(@ListeClient)
GO

(1 row(s) affectedSELECT ClientInfo.value('count(/People/Person)', 'int')FROM dbo.Clients;

-----------
2
2
(2 row(s) affected)

 Pour retourner la 2e personne de chaque rangée:

SELECT ClientInfo.value(

  'concat((/People/Person/FirstName)[2], " ", 
 (/People/Person/LastName)[2])', 'varchar(25)')
FROM dbo.Clients;

-------------------------
Jane Doe
Bob Marley
(2 row(s) affected)

Vérifier l’existence d’une valeur dans les rangée (1=trouvé, 0=non-trouvé):

SELECT ClientInfo.exist(
 '/People/Person[FirstName="Jane"]')
FROM dbo.Clients;

-----
1
0
(2 row(s) affected)