Curseurs (SQL Server)

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de données SQL dans Microsoft Fabric

Les opérations réalisées dans une base de données relationnelle s'exécutent sur un ensemble complet de lignes. Par exemple, l’ensemble de lignes retourné par une instruction SELECT contient toutes les lignes satisfaisant aux conditions de la clause WHERE de l’instruction. Cet ensemble complet de lignes retournées par l’instruction est appelé ensemble de résultats. Les applications, en particulier les applications interactives en ligne, peuvent ne pas toujours fonctionner efficacement si l’ensemble de résultats est traité comme une unité. Ces applications ont besoin d'un mécanisme leur permettant de travailler avec une seule ligne ou avec un petit bloc de lignes à la fois. Les curseurs sont une extension des ensembles de résultats et fournissent ce mécanisme.

Les curseurs permettent l'extension du traitement des résultats en procédant aux opérations suivantes :

• Ils permettent de vous positionner sur des lignes spécifiques de l'ensemble de résultats.

• Extraction d'une ligne ou d'un bloc de lignes depuis la position actuelle dans l'ensemble de résultats.

• Prise en charge des modifications des données apportées aux lignes situées à la position actuelle dans l'ensemble de résultats.

• Ils prennent en charge différents niveaux de visibilité des modifications apportées par d'autres utilisateurs aux données de la base de données qui figurent dans l'ensemble de résultats.

• Ils permettent aux instructions Transact-SQL figurant dans les scripts, les procédures stockées et les déclencheurs d’accéder aux données d’un ensemble de résultats.

Remarques

Dans certains scénarios, s’il existe une clé primaire dans une table, on peut utiliser une boucle WHILE à la place d’un curseur, sans subir le surcoût lié à un curseur.

Cependant, dans certains scénarios, les curseurs ne sont pas seulement inévitables, ils sont bel et bien obligatoires. Dans ce cas, s’il n’est pas nécessaire de mettre à jour les tables à partir du curseur, utilisez des curseurs firehose, c’est-à-dire des curseurs en avant seulement et en lecture seule.

Implémentations des curseurs

SQL Server prend en charge trois implémentations de curseurs.

Type de curseur

SQL Server prend en charge quatre types de curseur.

Les curseurs peuvent utiliser des tables de travail tempdb. Tout comme les opérations d’agrégation ou de tri en dépassement, ils engendrent des coûts d’E/S et peuvent constituer un goulot d’étranglement qui nuisent aux performances. STATIC Les curseurs utilisent des tables de travail depuis leur création. Pour plus d’informations, consultez la section Tables de travail dans le Guide d’architecture de traitement des requêtes.

En avant uniquement

Un curseur avant uniquement est spécifié avec FORWARD_ONLY et READ_ONLY et ne prend pas en charge le défilement. Ces curseurs sont aussi appelés firehose et prennent en charge uniquement l’extraction des lignes en série du début jusqu’à la fin du curseur. Les lignes ne sont pas sorties de la base de données tant qu’elles n’ont pas été extraites. Les effets de toutes les instructions INSERT, UPDATE et DELETE émises par l’utilisateur actif ou validées par d’autres utilisateurs et ayant une incidence sur les lignes du jeu de résultats sont visibles à mesure que les lignes sont extraites à partir du curseur.

Étant donné que le curseur ne permet pas le défilement arrière, la plupart des modifications apportées aux lignes de la base de données après l’extraction d’une ligne ne sont pas visibles par le biais du curseur. En revanche, si une valeur utilisée pour déterminer l'emplacement de la ligne dans le jeu de résultats est modifiée, par exemple dans le cas de la mise à jour d'une colonne couverte par un index cluster, la valeur modifiée est visible à l'aide du curseur.

Bien que les modèles de curseur de l’API de base de données considèrent un curseur en avant uniquement comme un type distinct de curseur, ce n’est pas le cas de SQL Server. SQL Server considère les options « en avant seulement » et « de défilement » comme des options applicables aux curseurs statiques, à jeu de clés et dynamiques. Les curseurs Transact-SQL prennent en charge les curseurs en avant uniquement, statiques, pilotés par jeu de clés et dynamiques. Les modèles de curseur de l'API de base de données supposent que les curseurs statiques, à jeu de clés et dynamiques sont toujours parcourables. Lorsqu’un attribut ou une propriété de curseur d’API de bases de données est défini comme étant de type avant uniquement, SQL Server implémente ce curseur en tant que curseur dynamique avant uniquement.

statique

Le jeu de résultats complet d’un curseur statique est créé dans tempdb à l’ouverture du curseur. Un curseur statique affiche toujours l'ensemble de résultats tel qu'il était au moment où le curseur a été ouvert. Les curseurs statiques détectent peu ou pas de modifications, mais consomment relativement peu de ressources pendant le défilement.

Le curseur ne reflète pas les modifications de la base de données qui concernent soit l’appartenance de l’ensemble de résultats, soit les modifications apportées aux valeurs des colonnes des lignes constituant l’ensemble de résultats. Après son ouverture, un curseur statique n’affiche pas les nouvelles lignes insérées dans la base de données, même si celles-ci correspondent aux conditions de recherche de l’instruction SELECT du curseur. Si certaines lignes constituant l’ensemble de résultats sont mises à jour par d’autres utilisateurs, les nouvelles valeurs des données ne sont pas affichées dans le curseur statique. Le curseur statique affiche les lignes supprimées de la base de données après l'ouverture du curseur. Aucune opération UPDATE, INSERT ou DELETE n’apparaît dans un curseur statique (à moins qu’il soit fermé et rouvert), ni même les modifications effectuée en utilisant la même connexion que celle qui a ouvert le curseur.

Étant donné que l’ensemble de résultats d’un curseur statique est stocké dans une table de travail de la base de données tempdb, la taille des lignes de l’ensemble de résultats ne peut pas dépasser la taille maximale des lignes d’une table SQL Server.

Pour plus d’informations, consultez la section Tables de travail dans le Guide d’architecture de traitement des requêtes. Pour plus d’informations sur la taille maximale des lignes, consultez Spécifications des capacités maximales pour SQL Server.

Transact-SQL utilise le terme insensitive pour désigner les curseurs statiques. Certaines API de bases de données les appellent des curseurs de capture instantanée.

jeu de touches

La composition et l'ordre des lignes d'un curseur piloté par jeu de clés sont fixés au moment de l'ouverture du curseur. Les curseurs pilotés par jeu de clés sont contrôlés par un ensemble d’identifiants uniques, appelés clés, appelé jeu de clés. Les clés sont créées à partir d'un ensemble de colonnes qui identifient uniquement les lignes de l'ensemble de résultats. Le jeu de clés est l’ensemble des valeurs de clés de toutes les lignes remplissant les conditions requises par l’instruction SELECT au moment où le curseur a été ouvert. Le jeu de clés d’un curseur piloté par un jeu de clés est généré dans tempdb lors de l’ouverture du curseur.

Dynamique

Les curseurs dynamiques sont le contraire des curseurs statiques. Les curseurs dynamiques reflètent toutes les modifications apportées aux lignes de leur jeu de résultats lors du parcours du curseur. Les valeurs des données, l'ordre et l'appartenance des lignes du jeu de résultats peuvent changer à chaque extraction. Toutes les instructions UPDATE, INSERT et DELETE émises par l’ensemble des utilisateurs sont visibles à l’aide du curseur. Les mises à jour sont visibles immédiatement si elles ont été effectuées à l’aide du curseur, soit au moyen d’une fonction API comme SQLSetPos, soit au moyen de la clause WHERE CURRENT OF Transact-SQL. Les mises à jour effectuées en dehors du curseur ne sont pas visibles tant qu’elles n’ont pas été validées, à moins que le niveau d’isolement d’une transaction de curseur soit paramétré pour permettre la lecture des données non validées. Pour plus d’informations sur les niveaux d’isolation, consultez SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Demander un curseur

SQL Server prend en charge deux méthodes de demande de curseur :

• Transact-SQL

  Le langage Transact-SQL prend en charge une syntaxe permettant l’utilisation de curseurs modélisés d’après la syntaxe de curseur ISO.

• Fonctions de curseur de l'interface de programmation d'application (API) de bases de données

  SQL Server prend en charge la fonctionnalité de curseur des API des bases de données suivantes :

  • ADO (objet de données ActiveX Microsoft)

  • OLE DB

  • ODBC (Open Database Connectivity)

Une application ne doit jamais combiner ces deux méthodes de demande de curseur. Une application qui utilise l’API pour spécifier des comportements de curseur ne doit pas ensuite exécuter une instruction DECLARE CURSOR Transact-SQL pour demander aussi un curseur Transact-SQL. Une application ne doit exécuter une instruction DECLARE CURSOR que si elle ramène tous les attributs de curseur de l’API à leurs valeurs par défaut.

Dans le cas où ni un curseur Transact-SQL, ni un curseur d’API n’a été demandé, SQL Server retourne par défaut à l’application un ensemble de résultats complet, appelé ensemble de résultats par défaut.

Processus du curseur

Les curseurs Transact-SQL et les curseurs d’API ont une syntaxe différente, mais le processus général suivant est utilisé avec tous les curseurs SQL Server :

1. Associez un curseur à l’ensemble de résultats d’une instruction Transact-SQL et définissez les caractéristiques du curseur, en indiquant par exemple si les lignes contenues dans le curseur peuvent être mises à jour.

2. Exécutez l’instruction Transact-SQL pour remplir le curseur.

3. Récupérez les lignes du curseur que vous souhaitez afficher. L'opération consistant à récupérer une ligne ou un bloc de lignes à partir d'un curseur est appelée une extraction. On appelle défilement le fait d’effectuer une série de lectures pour récupérer des lignes vers l’avant ou vers l’arrière.

4. Vous pouvez, si vous le souhaitez, effectuer des opérations de modification (mise à jour ou suppression) sur la ligne à la position actuelle du curseur.

5. Fermez le curseur.

Contenu connexe

• Comportements des curseurs
• Comment les curseurs sont implémentés
• DECLARE CURSOR (Transact-SQL)
• Curseurs (Transact-SQL)
• Fonctions de curseur (Transact-SQL)
• Procédures stockées de curseur (Transact-SQL)
• SET TRANSACTION ISOLATION LEVEL (Transact-SQL)