MS SQL Server
  1. MS SQL Server
  3. La création de SQL dynamic en utilisant + = tronquera dans certains cas le contenu précédent
Intereting Posts
Fonctions de la window SQL Server: pouvez-vous faire une construction de type ROW BETWEEN? Insertion d'AutoIncrementID dans la database Besoin de déclarer l'heure et que le getdate () renvoie uniquement la date et joigne les deux résultats Erreur de nom de colonne non valide lors de la création d'une procédure stockée Comment puis-je interroger la culture de database (SQL Server 2005)? Synchronisation de IndexedDB avec SQL Server Quelle est la meilleure configuration de redondance sur AWS pour SQL Server 2014? Comment faire un simple 'Rechercher et replace' dans MsSQL? Quelle est l'importance d'ouvrir et de fermer une connection SQL plusieurs fois? Quel est l'effet exact? Limiter les parameters de procédure stockée hibernate Besoin de simplifier plusieurs requêtes Select Vérifier hiérarchiquement le numéro de parent Requête SQL – Requête mathématique sur 2 tables Ordre de sorting SQL par l'ordre spécifié dans la requête supprimer et recréer toutes les contraintes combinées avec le server SQL sp_MSForEachTable

La création de SQL dynamic en utilisant + = tronquera dans certains cas le contenu précédent

Dans certaines situations, j'ai un comportement bizarre sur SQL Server 2012 (dernière mise à jour) lorsque j'essaie de générer une string en utilisant l'auto-concaténation 

@Str += ...

ou 

 @Str = @Str + ...

Il tronque le contenu précédent de la variable dans la requête, qui est le comportement attendu lors de la concaténation d'une valeur NULL sauf que je ne suis pas …

Voici une version simplifiée du code réduite au minimum pour reproduire le bug sur mon instance. Il est difficile à reproduire car il suffit de copyr le résultat de la fonction sur une table temporaire (ce qui est impossible dans mon cas) pour le corriger, donc je suspecte quelque chose à propos de la planification ou de l'optimization des requêtes. 

 DECLARE @CTESQL VARCHAR(MAX)= ''; SELECT --TOP 4096--Workaround for SQL SERVER bug dropping previous text in some cases (4096 = max statement in a select clause) @CTESQL+= CASE WHEN 1 = ROW_NUMBER() OVER (ORDER BY PvtColumnName) THEN '1' ELSE CASE WHEN LAG(PvtColumnName) OVER (ORDER BY PvtColumnName) <> ISNULL(PvtColumnName, ColumnName) THEN '2' ELSE '3' END END + CASE WHEN PvtColumnName IS NULL THEN '4' ELSE (CASE WHEN 1 = ROW_NUMBER() OVER (ORDER BY t.PvtColumnName DESC) THEN '5' ELSE '6' END) END FROM dbo.ImportDefinition('stgPopulation') t ORDER BY PvtColumnName , ColumnId PRINT (@CTESQL);

La fonction de table 'ImportDefinition' renvoie datatables suivantes: 

 PvtColumnName ColumnId ColumnName ------------------- ----------- -------------------- NULL 3 Country NULL 2 GMPSubRegion NULL 4 ISO_Ctry NULL 9 Source AgeGroupCode 6 Total AreaTypeCode 6 Total AgeGroupCode 7 Under5 AreaTypeCode 7 Under5 AgeGroupCode 8 Urban AreaTypeCode 8 Urban NULL 1 RegionFullName NULL 5 Year

le résultat attendu est: 

 343414343434363636363625

le résultat réel de SQL Server est: 

 25

Un simple travail consiste à utiliser 'TOP n' qui le corrige mais je ne sais pas pourquoi et c'est assez sale.

J'avais espoir que forcer MAXDOP 1 aiderait mais pas de chance là-bas.

C'est la deuxième fois que je me lance contre ce problème, donc en dépit d'une solution de contournement multiple, j'aimerais vraiment comprendre ce qui se passe ou s'il y a un bug quelque part.

Merci pour ton expertise.

EDIT Voici un script qui permet de reproduire le même comportement: 

 IF OBJECT_ID('dbo.MyTable', 'U') IS NOT NULL DROP TABLE dbo.MyTable; CREATE TABLE dbo.MyTable ( F1 VARCHAR(255) NOT NULL , F2 NVARCHAR(4000) NULL ) ON [PRIMARY]; GO INSERT INTO dbo.MyTable (F1, F2) VALUES ('foo', 'a') , ('faa', 'b') , ('fuu', 'a'); DECLARE @CTESQL VARCHAR(MAX)= ''; SELECT @CTESQL+= CASE WHEN 1 = ROW_NUMBER() OVER (ORDER BY F2) THEN '1' ELSE CASE WHEN LAG(F2) OVER (ORDER BY F2) <> ISNULL(F2, F1) THEN '2' ELSE '3' END END + CASE WHEN F2 IS NULL THEN '4' ELSE (CASE WHEN 1 = ROW_NUMBER() OVER (ORDER BY F2 DESC) THEN '5' ELSE '6' END) END FROM MyTable ORDER BY F2; PRINT (@CTESQL);

Désolé, il suffit de relire et remarqué que la copy des résultats de la fonction à une table empêche la reproduction du problème.

À less que vous ne souhaitiez créer des scripts pour la (les) table (s) de base et la fonction table permettant aux autres de reproduire le problème, le mieux que tout le monde puisse faire est de deviner.

Ma première supposition est que votre fonction ne renvoie pas les résultats que vous pensez, mais si c'est le cas, il y a quelque chose à propos de l'interaction entre un TVF et la technique non documentée que vous utilisez pour build une string à partir des résultats de la requête.

Je souligne non documenté pour vous callbacker que c'est exactement ce que cette technique que vous utilisez, et il est impossible de dire qu'il y a un "bug" dans le comportement non documenté. SQL n'a jamais été conçu pour fonctionner de la façon dont vous l'utilisez, et il est juste que cela arrive de fonctionner de cette façon la plupart du time, mais il n'y a aucune garantie que cela fonctionnera tout le time, ou pas du tout dans les versions futures . Même le corriger avec TOP n est non documenté et peut ne pas fonctionner dans une future version de SQL Server.

La meilleure solution serait de commencer à utiliser STUFF() pour faire votre concaténation de string. Il existe déjà de nombreux exemples de comment faire cela sur ce site et ailleurs sur internet.

Quant à la question «Pourquoi ça ne marche pas?», Je pense que la meilleure réponse que vous obtiendrez est: «C'est un comportement non documenté.

EDIT en réponse aux commentaires:

  1. La technique non documentée à laquelle je fais reference est la construction d'une variable string utilisant + =. Consultez cet article et faites défiler jusqu'à la section intitulée «Approches non fiables». L'approche que vous utilisez est la deuxième listée, "UDF scalaire avec concaténation variable dans SELECT", bien que vous ne l'utilisiez pas dans un UDF. Pourtant, la technique de SELECT @var = @var + SomeData... est la partie non documentée et donc peu fiable.

  2. La "solution utilisant STUFF ()" dont je parlais est la même que celle proposée par SqlZim dans sa réponse. La même solution utilise à la fois STUFF() et FOR XML . Comme raccourci, je l'ai appelé en utilisant STUFF() , car je savais qu'une search sur ce mot-key conduirait à cette solution.

La seule façon de reproduire votre problème était de supprimer le '+' dans @ ctesql + =.

Vous pourriez essayer la version de stuff () ci-dessous et voir si vous avez le même problème. 

 use TempDb go set nocount on; --if exists (select * from tempdb.sys.objects where name like '#ImportDefinition%') begin; drop table #ImportDefinition; end; --/* if not exists (select * from tempdb.sys.objects where name like '#ImportDefinition%') begin; create table #ImportDefinition (PvtColumnName nvarchar(16) ,ColumnId smallint ,ColumnName nvarchar(16) ) insert into #ImportDefinition values (null ,'3' ,'Country') ,(null ,'2' ,'GMPSubRegion') ,(null ,'4' ,'ISO_Ctry') ,(null ,'9' ,'Source') ,('AgeGroupCode' ,'6' ,'Total') ,('AreaTypeCode' ,'6' ,'Total') ,('AgeGroupCode' ,'7' ,'Under5') ,('AreaTypeCode' ,'7' ,'Under5') ,('AgeGroupCode' ,'8' ,'Urban') ,('AreaTypeCode' ,'8' ,'Urban') ,(null ,'1' ,'RegionFullName') ,(null ,'5' ,'Year'); end; -- select * from #ImportDefinition --*/ declare @ctesql varchar(max)= ''; --/* select @ctesql+=(case when 1 = row_number() over (order by pvtcolumnname) then '1' when lag(pvtcolumnname) over (order by pvtcolumnname) <> isnull(pvtcolumnname, columnname) then '2' else '3' end) + (case when pvtcolumnname is null then '4' when 1 = row_number() over (order by t.pvtcolumnname desc) then '5' else '6' end) from #importdefinition t order by pvtcolumnname, columnid; print (@ctesql); declare @ForXmlPath varchar(max) select @ForXmlPath = stuff((select (case when 1 = row_number() over (order by pvtcolumnname) then '1' when lag(pvtcolumnname) over (order by pvtcolumnname) <> isnull(pvtcolumnname, columnname) then '2' else '3' end) + (case when pvtcolumnname is null then '4' when 1 = row_number() over (order by t.pvtcolumnname desc) then '5' else '6' end) from #importdefinition t order by pvtcolumnname, columnid for xml path (''), type).value('.','varchar(max)'),1,0,''); print @ForXmlPath; --*/ print char(10); print @@version; declare @options int = @@options; print 'disable_def_cnst_chk' + case when 1 & @options = 1 then ' on' else ' off' end; print 'implicit_transactions' + case when 2 & @options = 2 then ' on' else ' off' end; print 'cursor_close_on_commit' + case when 4 & @options = 4 then ' on' else ' off' end; print 'ansi_warnings' + case when 8 & @options = 8 then ' on' else ' off' end; print 'ansi_padding' + case when 16 & @options = 16 then ' on' else ' off' end; print 'ansi_nulls' + case when 32 & @options = 32 then ' on' else ' off' end; print 'arithabort' + case when 64 & @options = 64 then ' on' else ' off' end; print 'arithignore' + case when 128 & @options = 128 then ' on' else ' off' end; print 'quoted_identifier' + case when 256 & @options = 256 then ' on' else ' off' end; print 'nocount' + case when 512 & @options = 512 then ' on' else ' off' end; print 'ansi_null_dflt_on' + case when 1024 & @options = 1024 then ' on' else ' off' end; print 'ansi_null_dflt_off' + case when 2048 & @options = 2048 then ' on' else ' off' end; print 'concat_null_yields_null'+ case when 4096 & @options = 4096 then ' on' else ' off' end; print 'numeric_roundabort' + case when 8192 & @options = 8192 then ' on' else ' off' end; print 'xact_abort' + case when 16384 & @options = 16384 then ' on' else ' off' end; go

résultats dans ceci: 

 343414343434363636363625 343414343434363636363625 Microsoft SQL Server 2012 - 11.0.5058.0 (X64) May 14 2014 18:34:29 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor) disable_def_cnst_chk off implicit_transactions off cursor_close_on_commit off ansi_warnings on ansi_padding on ansi_nulls on arithabort on arithignore off quoted_identifier on nocount on ansi_null_dflt_on on ansi_null_dflt_off off concat_null_yields_null on numeric_roundabort off xact_abort off