Catégorie: SQL Server 2005

Comme vous le savez déjà, depuis SQL Server 2005, Microsoft a mis en place l' utilisation du langage .NET dans SQL Server. Etudions par un simple exemple les performances ? Quand devrons-nous utiliser quand même ce SQLCLR ?

» Lire la suite!

Comme la date des résultats est dépassée et que je pense que le concours reste à titre pédagogique, je me permet de publier quelques résultats qui peuvent vous instruire.

Aujourd'hui, utilisation des CTE pour multiplie une ligne.

( Première Partie ) Problème N°2 Le publipostage. Pour info, j'ai cherche un moment comment multiplier des données avant de comprendre que c'était les CTE la solution.

Réponse :

» Lire la suite!

Bonjour,

Samedi, en me promenant sur le site GUSS, il m'a été démontré une technique remarquable : l'utilisation des CTE pour générer une table. Notez l'option MAXRECURSION qui permet de dépasser 100 récursions par défaut. Encore une utilisation magnifique des CTE sur SQL Serveur 2005. A rapprochez de l'article sur GO [count]!

WITH CTETemps AS  
  (  
  SELECT cast('1990-01-01' AS datetime) Date  
  UNION ALL  
  SELECT Date + 1  
  FROM CTETemps  
  WHERE Date + 1 < '2031-01-01'  
  )  
SELECT top 10 * FROM CTETEMPS ORDER BY Date desc OPTION (MAXRECURSION 0);

Une expression de table courante ( CTE pour common Table Expression ) ressemble beaucoup à une vue non persistante.

Syntaxe :

WITH nom_CTE ( nom_colonne,... )
AS
(
requete
)
SELECT * FROM nom_CTE

Exemple de code :

WITH selection AS (
SELECT identificateur_de_cellule,traffic,datepart(ww,date) as sem,ROW_NUMBER() OVER (PARTITION BY identificateur_de_cellule,datepart(ww,date) ORDER BY traffic) as OrderRank FROM TRAFFIC_CELLULES
)
SELECT * FROM selection where OrderRank < salespersonid='sp.SalesPersonID'
UNION ALL
selectionner
)
SELECT * FROM simpleRecursive

Exemple :

-- Creation d'une table avec les données sous forme d'arbre
CREATE TABLE Employee_Tree( Employee_NM nvarchar(50), employee_ID int Primary Key, reportsTo int )
INSERT INTO Employee_Tree VALUES ('Richard',1,NULL)
INSERT INTO Employee_Tree VALUES('Stephen',2,1)
INSERT INTO Employee_Tree VALUES('Clemens',3,2)
INSERT INTO Employee_Tree VALUES('Malek',4,2)
INSERT INTO Employee_Tree VALUES('Goksin',5,4)

--Requete recursive
WITH SimpleRecursive(Employee_NM,Employee_ID,ReportsTo) AS
( SELECT Employee_NM, Employee_ID, ReportsTo FROM Employee_Tree WHERE Employee_ID = 2
UNION ALL
SELECT p.Employee_NM, p.Employee_ID, p.reportsTo FROM Employee_Tree p INNER JOIN SimpleRecursive A ON A.Employee_ID = P.ReportsTo
)
SELECT sr.Employee_NM AS Employee, et.Employee_NM As Boss FROM SimpleRecursive sr INNER JOIN Employee_Tree et ON sr.ReportsTo = et.Employee_ID

lien : http://sqlpro.developpez.com/cours/sqlserver/cte-recursives/

Syntaxe :
ROW_NUMBER() OVER([PARTITION BY value_expression ] ORDER BY value_expression)

Définition :
ROW_NUMBER()

Retourne le numéro séquentiel d'une ligne d'une partition d'un jeu de résultats, en commençant à 1 pour la première ligne de chaque partition
PARTITION BY

Divise l'ensemble de résultats en partitions. La fonction de fenêtre est appliquée à chaque partition séparément et le calcul redémarre pour chaque partition.
toute l'information à l'adresse :

http://sqljunkies.com/Article/4E65FA2D-F1FE-4C29-BF4F-543AB384AFBB.scuk



Un exemple d'utilisation :

Fournir les 3 premiers traffic d'un identificateur de cellule par semaine.


CREATE TABLE TRAFFIC_CELLULES(identificateur_de_cellule CHAR(3),traffic INT,date DATETIME);  
 

INSERT INTO TRAFFIC_CELLULES(identificateur_de_cellule,traffic,date) VALUES ('A',100,'01/01/2008') 
INSERT INTO TRAFFIC_CELLULES(identificateur_de_cellule,traffic,date) VALUES ('A',200,'01/01/2008') 

INSERT INTO TRAFFIC_CELLULES(identificateur_de_cellule,traffic,date) VALUES ('A',225,'01/01/2008') 
INSERT INTO TRAFFIC_CELLULES(identificateur_de_cellule,traffic,date) VALUES ('A',400,'01/01/2008') 

INSERT INTO TRAFFIC_CELLULES(identificateur_de_cellule,traffic,date) VALUES ('A',150,'01/02/2008') 
INSERT INTO TRAFFIC_CELLULES(identificateur_de_cellule,traffic,date) VALUES ('A',175,'01/02/2008') 

INSERT INTO TRAFFIC_CELLULES(identificateur_de_cellule,traffic,date) VALUES ('A',200,'01/02/2008') 
INSERT INTO TRAFFIC_CELLULES(identificateur_de_cellule,traffic,date) VALUES ('B',100,'01/01/2008') 

INSERT INTO TRAFFIC_CELLULES(identificateur_de_cellule,traffic,date) VALUES ('B',100,'01/03/2008')  
 

WITH selection 
AS 

( 
SELECT identificateur_de_cellule,traffic,datepart(ww,date) as sem,ROW_NUMBER() OVER (PARTITION BY identificateur_de_cellule,datepart(ww,date) ORDER BY traffic) as OrderRank FROM TRAFFIC_CELLULES 

) 
SELECT * FROM selection where OrderRank <= 3 

 
Resultat: 

 
A 100 1 1 

A 200 1 2 
A 225 1 3 

A 150 5 1 
A 175 5 2 

A 200 5 3 
B 100 1 1 

B 100 9 1