Mim

Procédure stockées : N'utiliser pas "sp_"

Un petit rappel tout bête sur le nommage des procédures stockées :

Je vois trop souvent des procédures stockées prénommées par le suffixe "sp_".
N'utilisez PAS "sp_" dans le nom de votre procédère stockée, sauf si celle ci est créée pour s'éxécuter sur la base Master.

Oui mais pourquoi ?

Si le moteur SQL trouve une procédudre stockée "sp_" , il cherchera à l'exécuter dans l'ordre suivant :

1. Sur la base de données Master.
2. En l'éxécutant avec son nom complet (Base.Schema.ProcStock)
3. En l'exécutant avec le schéma propriétaire (dbo), si le schéma n'est pas précisé.

Donc si vous avez une procédure stockée commençant par "sp_" alors SQL server fera toujours en premier un Check sur la base de données Master !!

Un effet de bord logique maintenant : Si vous créez une procédure stockée ayant le même nom qu'une procédure stockée Master, celle ci ne sera jamais exécutée sur votre base de données !

Plus de "sp_" s'il vous plait ! :)

Pagination avec SQL Server 2005 : CTE, Row_Number

Aujourd'hui petite astuce pour faire de la pagination de données avec SQL Server 2005 :

Vous avez suremment déjà "exploré" les solutions mises en oeuvres par les scripts SQL des MemberShip Providers pour faire de la pagination de données.
Et comme moi, peut être, vous avez trouvé ça quelque peu ... lourd.

Bon, en même temps, ces scripts se veulent compatibles avec SQL Server 2000 par exemple. De ce fait, les cht'i gars de chez MS n'avaient guère le choix ...

Mais nous, si !

Alors comment faire pour récupérer un jeu de données paginées ?

Tout d'abord, nous allons utiliser Row_Number(), qui a la bonne idée de retourner un numéro de ligne

Par exemple :

Select Row_Number() over (order by BirthDate asc) as RowNumber, E.EmployeeID, E.ManagerID,
E.BirthDate From  HumanResources.Employee E

retourne :

1        268       273         1941-11-17
2        246      51          1942-04-03
3        238       274        1942-06-13
4        9         3            1942-10-29
5        237       108        1943-06-01
6        260      49          1944-05-25
7        243      18          1945-03-02
8       7         21           1946-02-16
9       10        185          1946-04-27
10      19        185          1946-04-30
11      14        21            1946-05-03
12      17        185          1946-05-06

Malheuresement, Row_Number() est analysé par le moteur APRES la clause WHERE.
Du coup interdiction de faire quelque chose du genre :

where Row_Number() over (order by BirthDate asc) > 100 
and Row_Number() over (order by BirthDate asc) < 120

D'où l'idée d'utiliser une Common Type Expression, qui aura la bonne idée de nous fournir notre précieuse information !

Ce qui donne dans notre exemple :

With mCte (RowNumber, EmployeeID, ManagerID, BirthDate)
As
(
 Select Row_Number() over (order by BirthDate asc) as RowNumber, E.EmployeeID, 
 E.ManagerID, E.BirthDate
 From    HumanResources.Employee E
)
Select    *
From mCte C
Where    RowNumber Between 100 and 120

Avec ASP.Net et une data source, il est de bon ton de donner un numéro de page et un nombre d'enregistrements à récupérer.

Si on arrange tout ça pour s'adapter à ce contexte, on obtient une procédure stockée du style :

CREATE procedure [GetEmployeesPaged]
(
    @nStartRowNum int = 100,
    @nStartRowCount int = 10
    
)

as

With mCte (RowNumber, EmployeeID, ManagerID, BirthDate)
As
(
 Select Row_Number() over (order by BirthDate asc) as RowNumber, 
 E.EmployeeID, E.ManagerID, E.BirthDate
 From    HumanResources.Employee E
)
Select    *
From mCte C
Where    RowNumber Between @nStartRowNum and (@nStartRowNum + @nStartRowCount -1)

Pour aller plus loin :

Si on pousse un peu, je vous propose une Double CTE pour récupérer non seulement les employés mais aussi les managers de ceux ci, ce qui va nous obliger à faire une boucle récursive, ce qui est naturel avec une CTE.

bref, plus que des mots ... du SQL !

With    
    EmpCTE (EmpID, MgrID, Level)
    As    (
         Select E.EmployeeID, E.ManagerID, 1 
         From    HumanResources.Employee E
         Where ManagerID is null
         UNION ALL
         Select E.EmployeeID, E.ManagerID, Level + 1 
         From    HumanResources.Employee E
         Inner Join EmpCTE on EmpCTE.EmpID = E.ManagerID
         ),
    myCTE 
    As (    
        Select    Row_Number() over (order by Level asc) as RowNum, *
        From    EmpCTE
        )

Select    C.RowNum, C.EmpID, CE.FirstName+ ' ' + CE.LastName as EmpName,
        C.MgrID, ME.FirstName+ ' ' + ME.LastName as MgrName, C.Level
From myCTE C
Inner Join Person.Contact CE on C.EmpID = CE.ContactID
Left Outer Join Person.Contact ME on C.MgrID = ME.ContactID

Where    RowNum Between @nStartRowNum and (@nStartRowNum + @nStartRowCount -1)

C'est beau non  ;) ?

Chaine de connexion : Application Name

Une petite astuce en passant :

Lorsque vous créez une chaine de connexion dans votre application, n'oubliez pas d'affecter une valeur à la propriété Application Name. Celle ci permet de "taguer" votre chaine de connexion, ce qui nous permettra de tracer plus facilement les évènements dont la connexion a été créée à partir de cette chaine.

Voici un exemple de chaine de connexion, initialisée avec ce paramètre supplémentaire :

<connectionStrings>
<add name="AdvConnectionString" providerName="System.Data.SqlClient"
connectionString="Data Source=.\SQL2005;Initial Catalog=AdventureWorks;
                Integrated Security=True;Application Name=AdventureWorks Application;"
/>

Pour tracer les informations relatives à cette connexion sur votre serveur SQL Server 2005, lancer le Profiler, et appliquer un filtre sur la colonne Application Name :

Maintenant, votre profiler ne remontera que les évènements de votre application !

BCL .Net 2.0 et .. 2.0 bis !!

J'ai eu quelques retours sur l'article présentant les nouvelles fonctionnalités apportées par SQL Server 2008 (les TVP)

Une question est revenue assez fréquemment :

• "Tu utilises ADO.NET 2.0 pour créer ton paramètre"
• "L'ensemble des classes proviennent de System.Data"
• "Donc on peut profiter de tout ça avec le framework 2.0 !!"

Alors.. oui .. mais non !

C'est vrai, dans le code tout indique que tout est exécuté depuis les classes du framework 2.0

Mais il faut savoir que certaines assemblys du framework 2.0 ont subis un léger "lifting" lors du passage au framework 3.5

Pour vérifier, il suffit de comparer les versions des assemblys. Prenons par exemple une machine XP SP2 avec le Fx 2.0 installé, et une machine VISTA avec Orcas :

• XP Sp2 FX 2.0 : version de System.Data : 2.0.50727.42
• Vista Fx 3.5 : version de System.Data : 2.0.50727.1378

Mais alors, qu'est ce qui ne marcherait pas dans le code de l'article TVP ?

Nous créons un paramètre, en lui affectant un type SqlDbTypes.Structured, qui est le "nouveau" type de donnée correspondant aux TVP. Pour mémoire :

SqlParameter param = new SqlParameter("@tmpContact", SqlDbType.Structured);

Nous allons juste pointer du doigt l'énumération SqlDbType. Voici les changements entre la version Fx2.0 et la version Fx2.0"sp1" :

Les différences notables :

• Structured : Le nouveau type pour les TVP (vu dans l'article)
• Date, Time, DateTime2, DateTimeOffset : L'ensemble des nouveaux types dates, pris en charge par Sql Server 2008. (Article in progress !)

Si vous voulez une liste plutôt exhaustive, je vous conseille de lire le blog de Scott Hanselman's , de Microsoft.

Un lien direct sur les changements au niveau core : API Changes

Nouveauté Sql Serveur 2008 : Insertion multiples

Tiens, une petite nouveauté apparue dans SQL Serveur 2008, enfin surtout une amélioration du langage T-SQL; l'insertion multiples.

Pour reprendre l'exemple de l'article sur les TVP précédent, où nous insérons 6 lignes dans une "Table Value Parameter", à grand coup de "copier-coller", il est possible maintenant de passer par une nouvelle syntaxe :

INSERT INTO mytable (champ 1, Champ2)
VALUES  (Valeur1, Valeur2), (ValeurA, ValeurB), (ValeurX, ValeurY)

Dans notre exemple, nous obtiendrons donc :

INSERT INTO @tableTmp(ContactId, MustBeUpdate, UpdatedDate) 
VALUES (1, 0, '01/02/2007') ,(2, 1, '02/03/2006'), (12, 0, null),               
(123, 1, null), (246, 0, null), (264, 1, null) 

Mais comment faisait on avant !! (Copier coller, oui je sais  )

Bon codage !

Article : Nouveauté Sql Serveur 2008 : TVP

TVP : Table Value Parameter

Ce nouveau type introduit dans la nouvelle mouture de Sql Serveur 2008, va vous permettre de passer en paramètre d'une procédure stockée une ... table !

Un exemple du résultat en image :)

Retrouver l'article complet sur le site TechHeadBrothers :

	TVP : Table Value Parameter

Bonne lecture !

XML sous Sql Serveur 2000 et Sql Serveur 2005

Juste une petite piqure de rappel.

Comment lire un champ de type varchar (ou ntext) sous SQL SERVEUR 2000 qui est en fait du XML ?

--  Version SQL 2000
Declare @tmp varchar(3000);

--Exemple de ce que doit contenir notre champ XML en entree
Select @tmp = '<colonnes version="1.0">' + 
'<colonne code="faaPrecedentN_1" type="5" obligatoire="1" derivee="1" /> ' +
'<colonne code="faaDestiPrecedentN_1" type="1" obligatoire="1" derivee="1" />' +
'</colonnes>';


DECLARE @Pointer INT      
EXECUTE sp_xml_preparedocument @Pointer OUTPUT,@tmp 

Select code, [type], obligatoire, derivee From
OPENXML (@Pointer,'/colonnes/colonne')       
      WITH (code varchar(50), [type] int,  obligatoire bit, derivee Bit) 

EXEC sp_xml_removedocument @Pointer

Comment faire la même opération sous SQL SERVEUR 2005, mais cette fois ci avec un vrai champ XML (et l'utilisation de XPATH)

--  Version SQL 2005
Declare @xml xml;

--Exemple de ce que doit contenir notre champ XML en entre9e
Select @xml = '<colonnes version="1.0">' + 
'<colonne code="faaPrecedentN_1" type="5" obligatoire="1" derivee="1" /> ' +
'<colonne code="faaDestiPrecedentN_1" type="1" obligatoire="1" derivee="1" />' +
'</colonnes>';

With CTE_Tmp(Code, [Type], Obligatoire, Derivee) as
(Select R.Colonne.value('@code', 'varchar(50)'),         
R.Colonne.value('@type', 'bit'),          
R.Colonne.value('@obligatoire', 'bit'),         
R.Colonne.value('@derivee', 'bit') 
from @xml.nodes('/colonnes/colonne') as R(Colonne))

Select * from CTE_Tmp