MS SQL Server
  1. MS SQL Server
  3. Utilisation de la table temporaire dans la clause where
Intereting Posts
Existe-t-il un moyen de fermer toutes les connections dans le pool pour le fournisseur SqlClient Pair ou impair dans SQL Une meilleure architecture alors si (quelque chose) DoIt () else Dont () Pourquoi et quand un LEFT JOIN avec condition dans la clause WHERE n'est pas équivalent au même LEFT JOIN dans ON? Recherche d'inputs chevauchées efficaces dans une table SQL Erreur lors de l'utilisation de Liquibase sur SQL Server Comment convertir une requête SQL en Linq avec GROUP BY sur une partie de datetime uniquement? Comment get un identifiant pour les dates La connection SQL Server à une database locale échoue tout d'un coup. Rien dans le code n'a changé Comment convertir efficacement le prix dans SELECT en utilisant la table de taux de change existante avec une structure donnée? Chaîne de données de saisie semi-automatique à partir d'une table volumineuse Erreur de requête avec 2 noms de colonne ambigus dans SQL Types de données de return UNPIVOT Comment puis-je find un set variable de 5 nombres qualifiés par des traits de soulignement environnants? Vérification d'un DateTime qui fait partie d'un plus grand nvarchar

Utilisation de la table temporaire dans la clause where

Je souhaite supprimer plusieurs lignes avec le même set de valeurs de champs dans certaines tables (6). Je pourrais le faire en supprimant le résultat d'une sous-requête dans chaque table (Solution 1), ce qui serait redondant, car la sous-requête serait la même à chaque fois; Je veux donc stocker le résultat de la sous-requête dans une table temporaire et supprimer la valeur de chaque ligne (de la table temporaire) dans les tables (Solution 2). Quelle est la meilleure solution?

Première solution: 

DELETE FROM dbo.SubProtocols WHERE ProtocolID IN ( SELECT ProtocolID FROM dbo.Protocols WHERE WorkplaceID = @WorkplaceID ) DELETE FROM dbo.ProtocolHeaders WHERE ProtocolID IN ( SELECT ProtocolID FROM dbo.Protocols WHERE WorkplaceID = @WorkplaceID ) // ... DELETE FROM dbo.Protocols WHERE WorkplaceID = @WorkplaceID

Deuxième solution: 

 DECLARE @Protocols table(ProtocolID int NOT NULL) INSERT INTO @Protocols SELECT ProtocolID FROM dbo.Protocols WHERE WorkplaceID = @WorkplaceID DELETE FROM dbo.SubProtocols WHERE ProtocolID IN ( SELECT ProtocolID FROM @Protocols ) DELETE FROM dbo.ProtocolHeaders WHERE ProtocolID IN ( SELECT ProtocolID FROM @Protocols ) // ... DELETE FROM dbo.Protocols WHERE WorkplaceID = @WorkplaceID

Est-il possible de faire la solution 2 sans la sous-requête? Dites faire WHERE ProtocolID IN @Protocols (mais syntaxiquement correct)?

J'utilise Microsoft SQL Server 2005.

Alors que vous pouvez éviter la sous-requête dans SQL Server avec une jointure, comme ceci: 

 delete from sp from subprotocols sp inner join protocols p on sp.protocolid = p.protocolid and p.workspaceid = @workspaceid

Vous constaterez que cela ne vous apporte vraiment aucune performance par rapport à vos approches. Généralement, avec votre sous-requête, SQL Server 2005 optimise cela dans une inner join , car il ne dépend pas de chaque ligne. En outre, SQL Server mettra probablement en cache la sous-requête dans votre cas, donc le placer dans une table temporaire est probablement inutile.

La première façon, cependant, serait susceptible de changements dans les Protocols pendant les transactions, où le second ne le serait pas. Juste quelque chose à penser.

Peut essayer ceci 

 DELETE FROM dbo.ProtocolHeaders FROM dbo.ProtocolHeaders INNER JOIN dbo.Protocols ON ProtocolHeaders.ProtocolID = Protocols.ProtocolID WHERE Protocols.WorkplaceID = @WorkplaceID

DELETE ... FROM est une extension T-SQL au DELETE SQL standard qui fournit une alternative à l'utilisation d'une sous-requête. De l'aide:

D. Utilisation de DELETE basé sur une sous-requête et utilisation de l'extension Transact-SQL L'exemple suivant montre l'extension Transact-SQL utilisée pour supprimer des loggings d'une table de base basée sur une sous-requête de jointure ou de corrélation. La première instruction DELETE montre la solution de sous-requête compatible SQL-2003 et la deuxième instruction DELETE montre l'extension Transact-SQL. Les deux requêtes suppriment des lignes de la table SalesPersonQuotaHistory en fonction des ventes cumulées depuis le début dans la table SalesPerson. 

 -- SQL-2003 Standard subquery USE AdventureWorks; GO DELETE FROM Sales.SalesPersonQuotaHistory WHERE SalesPersonID IN (SELECT SalesPersonID FROM Sales.SalesPerson WHERE SalesYTD > 2500000.00); GO -- Transact-SQL extension USE AdventureWorks; GO DELETE FROM Sales.SalesPersonQuotaHistory FROM Sales.SalesPersonQuotaHistory AS spqh INNER JOIN Sales.SalesPerson AS sp ON spqh.SalesPersonID = sp.SalesPersonID WHERE sp.SalesYTD > 2500000.00; GO

Vous voudriez, dans votre deuxième solution, quelque chose comme

– non testé! DELETE FROM dbo.SubProtocols – ProtocolHeaders, etc FROM dbo.SubProtocols INNER JOIN @Protocoles ON SousProtocoles.ProtocolID = @ Protocols.ProtocolID

Toutefois!!

N'est-il pas possible de modifier votre design de sorte que toutes les tables de protocole annexe aient une FOREIGN KEY avec DELETE CASCADE dans la table Protocols principale? Alors vous pourriez juste DELETE des Protocols et le rest serait pris en charge de …

modifier pour append:

Si vous avez déjà configuré des FOREIGN KEY , vous devrez utiliser DDL pour les modifier (je pense qu'il est nécessaire d'effectuer une suppression et une recréation) pour que DELETE CASCADE activé. Une fois cela en place, un DELETE de la table principale DELETE automatiquement les loggings liés de la table enfant.

Sans la table temporaire, vous risquez de supprimer des lignes différentes dans la seconde suppression, mais cela prend trois opérations à effectuer.

Vous pouvez supprimer de la première table et utiliser la clause OUTPUT INTO pour insert dans une table temporaire tous les ID, puis utiliser cette table temporaire pour supprimer la deuxième table. Cela vous assurera que vous supprimez seulement les mêmes keys avec et avec seulement deux déclarations. 

 declare @x table(RowID int identity(1,1) primary key, ValueData varchar(3)) declare @y table(RowID int identity(1,1) primary key, ValueData varchar(3)) declare @temp table (RowID int) insert into @x values ('aaa') insert into @x values ('bab') insert into @x values ('aac') insert into @x values ('bad') insert into @x values ('aae') insert into @x values ('baf') insert into @x values ('aag') insert into @y values ('aaa') insert into @y values ('bab') insert into @y values ('aac') insert into @y values ('bad') insert into @y values ('aae') insert into @y values ('baf') insert into @y values ('aag') DELETE @x OUTPUT DELETED.RowID INTO @temp WHERE ValueData like 'a%' DELETE y FROM @yy INNER JOIN @temp t ON y.RowID=t.RowID select * from @x select * from @y

SÉLECTIONNER LA SORTIE: 

  RowID ValueData ----------- --------- 2 bab 4 bad 6 baf (3 row(s) affected) RowID ValueData ----------- --------- 2 bab 4 bad 6 baf

(3 rangée (s) touchée (s))