MS SQL Server
  1. MS SQL Server
  3. La requête SQL fonctionne correctement dans SQL Server 2012, mais n'a pas pu s'exécuter dans SQL Server 2008 R2
Intereting Posts
Comment puis-je get TotalPages dans le corps du rapport SSRS? SQL Server 2000 – Debugging Deadlocks Index SQL sur deux colonnes Comment spécifier GROUP BY pour l'agrégat PIVOT Conversion de la date SQL en date SAS Amélioration des performances d'ajout d'une colonne avec une seule valeur Convertir des classs C # (bibliothèque de classs) en SQL DDL (tables) Sélection multiple SQL avec différents Où est dans un Select Comment append des jours à la date actuelle dans le server sql? Utiliser la procédure stockée pour insert des données dans une table Ajouter une colonne contenant l'identifiant de la ligne précédente dans chaque groupe Comment filterr les nœuds xml dans le server sql? Aide à la requête SQL, saisissant les dates, les numbers, les moyennes SQL Server se bloque pendant dix minutes Commande SQL pour mapper des données d'une colonne à une autre

La requête SQL fonctionne correctement dans SQL Server 2012, mais n'a pas pu s'exécuter dans SQL Server 2008 R2

J'ai une table appelée MyTextstable (myTextsTable_id INT, myTextsTable_text VARCHAR(MAX)) . Cette table a environ 4 millions d'loggings et j'essaye de supprimer n'importe quelle instance des caractères ASCII dans la gamme suivante (s) la colonne VARCHAR(MAX) myTextsTable_text .

  • 00 – 08
  • 11 – 12
  • 14 – 31
  • 127

J'ai écrit la requête SQL suivante, qui prend less de 10 minutes sur SQL Server 2012, mais n'a pas pu s'exécuter sur SQL Server 2008 R2 même après deux heures (j'ai donc arrêté l'exécution). Veuillez noter que j'ai restauré la sauvegarde d'une database SQL Server 2008 R2 sur SQL Server 2012 (c'est-à-dire que datatables sont exactement les mêmes). 

 BEGIN TRANSACTION [Tran1] BEGIN TRY UPDATE myTextsTable SET myTextsTable_text = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(myTextsTable_text, CHAR(0), ''), CHAR(1), ''), CHAR(2), ''), CHAR(3), ''), CHAR(4), ''), CHAR(5), ''), CHAR(6), ''), CHAR(7), ''), CHAR(8), ''), CHAR(11), ''), CHAR(12), ''), CHAR(14), ''), CHAR(15), ''), CHAR(16), ''), CHAR(17), ''), CHAR(18), ''), CHAR(19), ''), CHAR(20), ''), CHAR(21), ''), CHAR(22), ''), CHAR(23), ''), CHAR(24), ''), CHAR(25), ''), CHAR(26), ''), CHAR(27), ''), CHAR(28), ''), CHAR(29), ''), CHAR(30), ''), CHAR(31), ''), CHAR(127), '') WHERE myTextsTable_text LIKE '%[' + CHAR(0) + CHAR(1) + CHAR(2) + CHAR(3) + CHAR(4) + CHAR(5) + CHAR(6) + CHAR(7) + CHAR(8) + CHAR(11) + CHAR(12) + CHAR(14) + CHAR(15) + CHAR(16) + CHAR(17) + CHAR(18) + CHAR(19) + CHAR(20) + CHAR(21) + CHAR(22) + CHAR(23) + CHAR(24) + CHAR(25) + CHAR(26) + CHAR(27) + CHAR(28) + CHAR(29) + CHAR(30) + CHAR(31) + CHAR(127) + ']%'; COMMIT TRANSACTION [Tran1]; END TRY BEGIN CATCH ROLLBACK TRANSACTION [Tran1]; --PRINT ERROR_MESSAGE(); END CATCH;

Il n'y a que 135 loggings affectés. Comme la requête UPDATE unique ne fonctionnait pas dans SQL Server 2008, j'ai essayé l'approche suivante avec une table temporaire. 

 BEGIN TRANSACTION [Tran1] BEGIN TRY IF OBJECT_ID('tempdb..#myTextsTable') IS NOT NULL DROP TABLE #myTextsTable; SELECT myTextsTable_id, myTextsTable_text INTO #myTextsTable FROM myTextsTable WHERE myTextsTable_text LIKE '%[' + CHAR(0) + CHAR(1) + CHAR(2) + CHAR(3) + CHAR(4) + CHAR(5) + CHAR(6) + CHAR(7) + CHAR(8) + CHAR(11) + CHAR(12) + CHAR(14) + CHAR(15) + CHAR(16) + CHAR(17) + CHAR(18) + CHAR(19) + CHAR(20) + CHAR(21) + CHAR(22) + CHAR(23) + CHAR(24) + CHAR(25) + CHAR(26) + CHAR(27) + CHAR(28) + CHAR(29) + CHAR(30) + CHAR(31) + CHAR(127) + ']%'; UPDATE #myTextsTable SET myTextsTable_text = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(myTextsTable_text, CHAR(0), ''), CHAR(1), ''), CHAR(2), ''), CHAR(3), ''), CHAR(4), ''), CHAR(5), ''), CHAR(6), ''), CHAR(7), ''), CHAR(8), ''), CHAR(11), ''), CHAR(12), ''), CHAR(14), ''), CHAR(15), ''), CHAR(16), ''), CHAR(17), ''), CHAR(18), ''), CHAR(19), ''), CHAR(20), ''), CHAR(21), ''), CHAR(22), ''), CHAR(23), ''), CHAR(24), ''), CHAR(25), ''), CHAR(26), ''), CHAR(27), ''), CHAR(28), ''), CHAR(29), ''), CHAR(30), ''), CHAR(31), ''), CHAR(127), '') UPDATE myTextsTable SET myTextsTable_text = new.myTextsTable_text FROM myTextsTable INNER JOIN #myTextsTable new ON new.myTextsTable_id=myTextsTable.myTextsTable_id DROP TABLE #myTextsTable; COMMIT TRANSACTION [Tran1]; END TRY BEGIN CATCH ROLLBACK TRANSACTION [Tran1]; --PRINT ERROR_MESSAGE(); END CATCH;

Cependant, le résultat est le même. Fonctionne parfaitement bien dans SQL Server 2012, mais pas dans SQL Server 2008 R2. J'ai trouvé que la requête UPDATE était toujours en cours d'exécution même après deux heures (les loggings ont été sauvegardés dans la table temporaire ( #myTextsTable ) en quelques minutes, je l'ai vérifié plus tard pour m'assurer que la partie prend plus de time).

Comme les deux methods précédentes ne fonctionnaient pas, j'ai essayé d'utiliser ceci en utilisant les variables TABLE juste pour vérifier si cela fait une différence, mais le résultat était le même (fonctionne correctement dans SQL Server 2012 mais pas dans SQL Server 2008 R2) 

 BEGIN TRANSACTION [Tran1] BEGIN TRY DECLARE @myTextsTable TABLE (myTextsTable_id INT, myTextsTable_text VARCHAR(MAX)) INSERT INTO @myTextsTable(myTextsTable_id, myTextsTable_text) SELECT myTextsTable_id, myTextsTable_text FROM myTextsTable WHERE myTextsTable_text LIKE '%[' + CHAR(0) + CHAR(1) + CHAR(2) + CHAR(3) + CHAR(4) + CHAR(5) + CHAR(6) + CHAR(7) + CHAR(8) + CHAR(11) + CHAR(12) + CHAR(14) + CHAR(15) + CHAR(16) + CHAR(17) + CHAR(18) + CHAR(19) + CHAR(20) + CHAR(21) + CHAR(22) + CHAR(23) + CHAR(24) + CHAR(25) + CHAR(26) + CHAR(27) + CHAR(28) + CHAR(29) + CHAR(30) + CHAR(31) + CHAR(127) + ']%'; UPDATE @myTextsTable SET myTextsTable_text = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(myTextsTable_text, CHAR(0), ''), CHAR(1), ''), CHAR(2), ''), CHAR(3), ''), CHAR(4), ''), CHAR(5), ''), CHAR(6), ''), CHAR(7), ''), CHAR(8), ''), CHAR(11), ''), CHAR(12), ''), CHAR(14), ''), CHAR(15), ''), CHAR(16), ''), CHAR(17), ''), CHAR(18), ''), CHAR(19), ''), CHAR(20), ''), CHAR(21), ''), CHAR(22), ''), CHAR(23), ''), CHAR(24), ''), CHAR(25), ''), CHAR(26), ''), CHAR(27), ''), CHAR(28), ''), CHAR(29), ''), CHAR(30), ''), CHAR(31), ''), CHAR(127), '') UPDATE myTextsTable SET myTextsTable_updated = GETDATE() ,myTextsTable_updatedby = 'As per V87058' ,myTextsTable_text = new.myTextsTable_text FROM myTextsTable INNER JOIN @myTextsTable new ON new.myTextsTable_id=myTextsTable.myTextsTable_id COMMIT TRANSACTION [Tran1]; END TRY BEGIN CATCH ROLLBACK TRANSACTION [Tran1]; --PRINT ERROR_MESSAGE(); END CATCH;

Quelqu'un pourrait-il expliquer pourquoi cela se produirait? Comment faire fonctionner cette requête SQL dans SQL Server 2008 R2?

Remarque: Je sais que les manipulations de strings dans le server / la couche de database ne sont pas idéales et il serait recommandé de faire des manipulations de strings dans la couche application, puis de les save dans DB. Mais, j'essaie de comprendre pourquoi ce serait un problème dans une version et pourquoi pas dans une autre version.

SQL Server 2012
Microsoft SQL Server 2012 – 11.0.5058.0 (X64)
Standard Edition (64 bits) sur Windows NT 6.3 (Build 9600:) (hyperviseur)

SQL Server 2008 R2
Microsoft SQL Server 2012 – 11.0.5058.0 (X64)
Standard Edition (64 bits) sur Windows NT 6.3 (Build 9600:) (hyperviseur)

C'est un problème connu sur SQL Server 2008 avec les types de données LOB et certains classments.

Il est facile de reproduire 

 /*Hangs on 2008*/ DECLARE @VcMax varchar(max)= char(0) + 'a' SELECT REPLACE(@VcMax COLLATE Latin1_General_CS_AS, char(0), '')

Pendant qu'il est accroché, il est lié au CPU et semble être dans une boucle infinie à travers ces fonctions.

entrez la description de l'image ici

Et la solution est facile aussi. Utilisez un type de données non MAX …

… ou une collation binary 

 /*Doesn't Hang*/ DECLARE @VcMax varchar(max)= char(0) + 'a' SELECT REPLACE(@VcMax COLLATE Latin1_General_100_BIN2, char(0), '')

Pour ceux qui lisent ceci à l'avenir, les façons suivantes ont bien fonctionné.

Way 1. Modification de COLLATION dans la colonne VARCHAR(MAX) dans la requête UPDATE SQL à BINARY COLLATION comme suggéré par Martin Smith (voir la réponse acceptée).

REPLACE (myTextsTable_text COLLATE Latin1_General_100_BIN2, CHAR (0), …

La solution sera comme ci-dessous: 

 GO BEGIN TRANSACTION [Tran1] BEGIN TRY IF OBJECT_ID('tempdb..#myTextsTable') IS NOT NULL DROP TABLE #myTextsTable; SELECT myTextsTable_id, myTextsTable_text INTO #myTextsTable FROM myTextsTable WHERE myTextsTable_text LIKE '%[' + CHAR(0) + CHAR(1) + CHAR(2) + CHAR(3) + CHAR(4) + CHAR(5) + CHAR(6) + CHAR(7) + CHAR(8) + CHAR(11) + CHAR(12) + CHAR(14) + CHAR(15) + CHAR(16) + CHAR(17) + CHAR(18) + CHAR(19) + CHAR(20) + CHAR(21) + CHAR(22) + CHAR(23) + CHAR(24) + CHAR(25) + CHAR(26) + CHAR(27) + CHAR(28) + CHAR(29) + CHAR(30) + CHAR(31) + CHAR(127) + ']%'; UPDATE #myTextsTable SET myTextsTable_text = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(myTextsTable_text COLLATE Latin1_General_100_BIN2, CHAR(0), ''), CHAR(1), ''), CHAR(2), ''), CHAR(3), ''), CHAR(4), ''), CHAR(5), ''), CHAR(6), ''), CHAR(7), ''), CHAR(8), ''), CHAR(11), ''), CHAR(12), ''), CHAR(14), ''), CHAR(15), ''), CHAR(16), ''), CHAR(17), ''), CHAR(18), ''), CHAR(19), ''), CHAR(20), ''), CHAR(21), ''), CHAR(22), ''), CHAR(23), ''), CHAR(24), ''), CHAR(25), ''), CHAR(26), ''), CHAR(27), ''), CHAR(28), ''), CHAR(29), ''), CHAR(30), ''), CHAR(31), ''), CHAR(127), '') UPDATE myTextsTable SET myTextsTable_updated = GETDATE() ,myTextsTable_updatedby = 'As per V87058' ,myTextsTable_text = new.myTextsTable_text FROM myTextsTable INNER JOIN #myTextsTable new ON new.myTextsTable_id=myTextsTable.myTextsTable_id DROP TABLE #myTextsTable; COMMIT TRANSACTION [Tran1]; END TRY

Way 2: J'ai créé une SQL function pour replace ces caractères par STUFF au lieu d'utiliser la fonction REPLACE .

Note: Veuillez noter que la fonction SQL est écrite selon mes besoins spécifiques. En tant que tel, il remplace uniquement les caractères dans la plage suivante.

  • 00 – 08
  • 11 – 12
  • 14 – 31
  • 127

– 

 Go CREATE FUNCTION [dbo].RemoveASCIICharactersInRange(@InputSsortingng VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS BEGIN IF @InputSsortingng IS NOT NULL BEGIN DECLARE @Counter INT, @TestSsortingng NVARCHAR(40) SET @TestSsortingng = '%[' + NCHAR(0) + NCHAR(1) + NCHAR(2) + NCHAR(3) + NCHAR(4) + NCHAR(5) + NCHAR(6) + NCHAR(7) + NCHAR(8) + NCHAR(11) + NCHAR(12) + NCHAR(14) + NCHAR(15) + NCHAR(16) + NCHAR(17) + NCHAR(18) + NCHAR(19) + NCHAR(20) + NCHAR(21) + NCHAR(22) + NCHAR(23) + NCHAR(24) + NCHAR(25) + NCHAR(26) + NCHAR(27) + NCHAR(28) + NCHAR(29) + NCHAR(30) + NCHAR(31) + NCHAR(127)+ ']%' SELECT @Counter = PATINDEX (@TestSsortingng, @InputSsortingng COLLATE Latin1_General_BIN) WHILE @Counter <> 0 BEGIN SELECT @InputSsortingng = STUFF(@InputSsortingng, @Counter, 1, '') SELECT @Counter = PATINDEX (@TestSsortingng, @InputSsortingng COLLATE Latin1_General_BIN) END END RETURN(@InputSsortingng) END GO

Ensuite, la requête UPDATE SQL (dans mon approche de la table temporaire) sera quelque chose comme ci-dessous: 

 UPDATE #myTextsTable SET myTextsTable_text = [dbo].RemoveASCIICharactersInRange(#myTextsTable_text) Go

Mon path préféré personnel serait le premier.

Probablement le problème est l'imbrication dans le rlocation et il est rapporté sur l'exécution et non sur la fonction compilación check @@ nestlevel. https://technet.microsoft.com/en-us/library/ms190607(v=sql.105).aspx