J'avais une problématique sur un projet qui était de récupérer de manière aléatoire un certain nombre d'enregistrements dans ma table.
La méthode RAND(), ne prenant pas de paramètre, ne retournera qu'un nombre décimal entre 0 et 1... je suis un peu feignant et préfére limiter au maximum la bidouille, je n'avais donc pas très envie de trouver un moyen d'intégrer RAND() dans ma requête.
Quelques petites recherches google plus tard, je suis tombé sur la fonction SQL NEWID(). La fonction NEWID() génère aléatoirement un GUID et peut s'utiliser de différentes façon
Vous pouvez regarder sur la MSDN l'utilisation du NEWID() mais grosso modo un print(NEWID()) vous affichera une donnée du type "87CE1C55-08CC-4771-A9B9-9E030DC39961". Ce n'est pas l'utilisation que je veux en faire mais bon...
Encore plus intéressant, l'utilisation de la fonction dans une requête SELECT vous permettra de générer pour chaque enregistrement un GUID.
Une requête du type SELECT ID, Name, NEWID() as UserId FROM myUsers vous affichera une donnée de type:
Id Name UserId
1 Jean 431D0BD1-1F2E-4EF5-91BA-F6F7957899CD
2 Claude 383EE4EC-8E6C-424A-AD54-7488882E314D
3 Raymond 3B0162D6-FE22-4339-A805-4CDC894A5EC2
4 Michel 9E023E94-0099-46AB-ABE5-B2EC7E882150
5 Guy 3668CF23-D206-4C85-84CB-E844BCA5F993
6 Thérèse 180EA950-A21D-4EE9-9F36-41B1ABFEA411
7 Marie 15215C5E-0BA0-4AD9-832B-3149026FDBCF
8 Alexandra CD9E0C74-9A94-4C38-8E2C-787158E40E42
9 Louise 07345035-30A7-4F0C-95FA-8B104D0A6FDE
10 Marion E9D95049-B37B-44D6-A9FA-05806A4373EC
Vous voyez où je veux en venir?
Un SELECT ID, Name FROM myUsers order by NEWID() va me retourner les id de façon aléatoire puisque les GUID sont générés à la volée et ensuite classés
Id Name
8 Alexandra
1 Jean
4 Michel
6 Thérèse
7 Marie
5 Guy
9 Louise
3 Raymond
10 Marion
2 Claude
Je veux récupérer de façon aléatoire mes enregistrements ayant un Id supérieur à 5:
SELECT ID, Name FROM myUsers WHERE id >5 ORDER BY NEWID()
Id Name
9 Louise
6 Thérèse
8 Alexandra
10 Marion
7 Marie
J'ai rencontré un problème il y a peu en travaillant sur un fichier .sql contenant un ensemble d'instruction de création de procédures stockées et de fonctions.
J'ai ajouté une procédure stockée dans ce fichier suivi d'une fonction de cette manière:
IF OBJECT_ID('P_Procedure_existante, 'P') IS NOT NULL
DROP PROCEDURE dbo.P_Procedure_existante;
GO
CREATE PROCEDURE dbo.P_Procedure_existante
AS
BEGIN
...
END
GO
IF OBJECT_ID('P_Procedure_nouvelle, 'P') IS NOT NULL
DROP PROCEDURE dbo.P_Procedure_nouvelle;
GO
CREATE PROCEDURE dbo.P_Procedure_nouvelle
AS
BEGIN
...
END
IF OBJECT_ID('F_Fonction_nouvelle', 'FN') IS NOT NULL
DROP FUNCTION dbo.F_Fonction_nouvelle;
GO
CREATE FUNCTION dbo.F_Fonction_nouvelle
(
@p_param1 DATETIME,
@p_param2 DATETIME
)
RETURNS INT
AS
BEGIN
...
END
GO
On remarquera qu'il y a bien une instruction GO entre la fin de la procédure existante et entre la nouvelle procédure et qu'il n'y en a pas entre la nouvelle procédure et la nouvelle fonction
J'ai ensuite executé des tests automatisés sur ces méthodes et systématiquement, ma fonction dbo.F_Fonction_nouvelle disparaissait sans raison apparente.
Je me suis aperçu que la fonction disparaissait durant le test de la procédure stockée dbo.P_Procedure_nouvelle. Je suis donc allé voir en base comment avait été généré la procédure stockée et je fut surpris de voir qu'elle avait été écrite de cette manière:
IF OBJECT_ID('P_Procedure_nouvelle, 'P') IS NOT NULL
DROP PROCEDURE dbo.P_Procedure_nouvelle;
GO
CREATE PROCEDURE dbo.P_Procedure_nouvelle
AS
BEGIN
...
END
IF OBJECT_ID('F_Fonction_nouvelle', 'FN') IS NOT NULL
DROP FUNCTION dbo.F_Fonction_nouvelle;
GO
En réalité, l'oubli d'un GO avait fusionné deux instructions. Je pensais que la création de la procédure stockée était délimité par le BEGIN/END mais c'est en réalité le GO qui indique la fin d'un traitement SQL.
Il est donc très important de bien séparer chacunes de vos instructions à l'aide du GO sous peine de fusionner plusieurs traitement non voulus au sein d'une même instruction.
Le numéro SIRET intègre le mécanisme de contrôle de parité connu sous le nom
de formule (ou clé) de Luhn. Ce contrôle de parité peut être utilisé pour
réduire les risques d'erreurs de frappe ou de transmission. L'algorithme consiste à parcourir les chiffres constituant le numéro SIREN,
NIC ou SIRET, à multiplier par 2 les chiffres de rang impair et à faire ensuite
la somme de tous les chiffres obtenus. Attention, il s'agit bien de faire la somme des chiffres obtenus : si après
multiplication par 2 d'un des chiffres du numéro on obtient "12", il
faudra ajouter les chiffres 1 puis 2 et non le nombre 12 (ou Somme-9 implémenté ci-dessous). Le contrôle de parité est correct si le résultat obtenu est un multiple de
10.
Ci-dessous une fonction Sql Serveur qui implémente cet algorithme :
create function dbo.Siret_ok(@siret varchar(24))
returns bit
as
begin
declare
@Position tinyint
declare @tmp int
declare
@Cumul_Siret int
declare
@Siret_valide bit
set
@Siret_valide = 0;
set
@Cumul_Siret = 1;
If Len(@Siret)=14
begin
set @Cumul_Siret = 0;
set @position=1
while @position<=14
begin
if @position % 2 = 0
begin
set @tmp = cast(substring(@siret,@position,1) as int);
end
else
begin
set @tmp = cast(substring(@siret,@position,1) as int) * 2;
if (@tmp > 9)
begin
set @tmp = @tmp - 9;
end
end
set @Cumul_Siret =
@Cumul_Siret + @tmp;
set @position=@position+1
end
set @Siret_valide=(case when
@Cumul_Siret % 10 =
0 then 1 else 0
end);
end
return (@Siret_valide)
end
go
Un de ces jours, je me suis retrouvé à oprimiser une procédure stockée qui traitait à travers un courseur une table de quelques millions d'enregistrements. Le traitement consistait à faire une mise à jour de chaque dernière enregistrement d'un regroupement de données.
Pour réaliser l'optimisation la première étape était d'obtenir la liste des enregistremens à mettre à jour (Ex : sur AdventureWorks) :
SELECT * FROM
(SELECT Sale.CustomerID, Cust.LastName, Sale.SalesOrderID, Sale.OrderDate,
ROW_NUMBER() OVER (partition by CustomerID order by OrderDate desc)as RowNumber
FROM Sales.SalesOrderHeader Sale
inner join Sales.Customer Cust on Cust.CustomerID=Sale.CustomerID) t2
WHERE RowNumber=1
En passant par ROW_NUMBER avec partitionnement tout devient plus simple.
Et maintenant on peut éliminer le courseur et utiliser une mise à jour en join:
update Sales.SalesOrderHeader
set DueDate=getdate()
from
Sales.SalesOrderHeader s1 inner join
(
SELECT * FROM
(SELECT Sale.CustomerID, Cust.LastName, Sale.SalesOrderID, Sale.OrderDate,
ROW_NUMBER() OVER (partition by CustomerID order by OrderDate desc)as RowNumber
FROM Sales.SalesOrderHeader Sale
inner join Sales.Customer Cust on Cust.CustomerID=Sale.CustomerID) t2
WHERE RowNumber=1
) t1 on t1.SalesOrderID=s1.SalesOrderID
La procédure est valable pour retrouver et mettre à jour le 2-ème,..., n-ème élément.
Dans les versions antèrieures de Sql Serveur 2005 pour réaliser un group by avec concaténation on devait toujours passer par une fonction ou un courseur.
Avec la version 2005 et SELECT ... FOR XML PATH('') on obtient :
use AdventureWorks
go
select Cust.CustomerID,
IsNull(stuff( ( select cast(SalesOrderID as varchar(100)) + ','
from Sales.SalesOrderHeader OrderHeader
where OrderHeader.CustomerID=Cust.CustomerID
for xml path('')),1,1,''),'') Sales
from Sales.Customer Cust
group by Cust.CustomerID
order by Cust.CustomerID
L'éxemple va concaténer toutes les Id-s des ventes par CustomerId.
En essayant de déployer Reporting Services comme sous-site d'un site existant, j'ai constaté que le site de Reporting Services, même s'il a son propre web.config charge aussi le web.config du site racine (root). En faite, tous les sous-sites héritent du web.config du site racine.
En cherchant une solution je suis tombé sur le lien ci-dessous :
http://www.aspdotnetfaq.com/Faq/how-to-disable-web-config-inheritance-for-child-applications-in-subfolders-in-asp-net.aspx
La solution est assez simple. Dans le web.config du site racine il faut imbriquer les balises <system.web> entre des balises <location> avec l'attribut inheritInChildApplications="false" :
<location path="." inheritInChildApplications="false">
<system.web>
...
</system.web>
</location>
Dans ces conditions pour chaque sous-site vous devez redéclarer toutes les propriétés de <system.web> dont vous aurez besoin.
Souvent quand on essaye de restaurer une base de données, on se retrouve dans la situation où le serveur refuse la restauration parce qu'on a encore de transactions en attente. Même s'il ne s'agit pas de vrai transactions de mise à jour de base. Dans ces cas il faut aller rechercher les verrous et les supprimer un à un. Ca marche si on fait manuellement ce travail.
Mais qu'est ce qui ce passe si on a un serveur de tests qu'on a besoin de raffraichir automatiquement tout les jours avec une sauvegarde base de production ? On peut restaurer manuellement s'il y a des erreurs suite à la restauration. Mieux, on peut rajouter dans le script de restauration avant la restauration effective une suppression des verrous. Plus...
Les exemples qui suivent configurent le serveur de données en français. Pour d'autres langues merci de regarder dans le BOL.
Au départ pour vérifier la langue configurée :
select @@Language, @@Langid
Pour changer la langue par défaut du serveur Sql Serveur :
exec sp_configure 'default language',2
go
RECONFIGURE WITH OVERRIDE
go
La procédure ci-dessus ne configure pas les logins déjà créés. Donc, pour finaliser l'opération vous pouvez executer le code suivant dans l'annalyseur de requêtes (une autre solution c'est d'utiliser Sql Server Management Studio) : Plus...
Si, comme moi, vous voulez modifier les tests unitaire de SubSonic vous aurez besoin d'une base Northwind. Or celle-ci n'est plus livrée avec SQL Server 2005 ou 2008.
Heureusement, les bases exemples (Northwind, Pubs mais aussi AdventureWorks ) sont téléchargeables sur Codeplex. Plus...
Pour mettre à jour une base dans laquelle de nombreuses personnes sont intervenues, je me suis retrouvé à chercher quels objets utilisent la colonne que je veux modifier. Quelques requêtes de schéma très simples permettent d'avoir facilement l'info. Plus...