Fonctionnalités Transact-SQL prises en charge dans Azure Synapse SQL

  • Article

Azure Synapse SQL est un service d’analytique Big Data qui vous permet d’interroger et d’analyser vos données à l’aide du langage T-SQL. Vous pouvez utiliser un dialecte standard du langage SQL conforme à la norme ANSI et utilisé dans SQL Server et Azure SQL Database pour l’analyse des données.

Le langage Transact-SQL est utilisé dans le pool SQL serverless, sachant qu’un modèle dédié peut référencer différents objets et présenter des différences au niveau de l’ensemble des fonctionnalités prises en charge. Dans cette page, vous trouverez les principales différences qui existent entre les modèles de consommation Synapse SQL au niveau du langage Transact-SQL.

Objets de base de données

Les modèles de consommation Synapse SQL permettent d’utiliser des objets de base de données différents. Le tableau suivant compare les types d’objets pris en charge :

Object Dédié Sans serveur
Tables Oui Non, les tables dans la base de données ne sont pas prises en charge. Le pool SQL serverless peut interroger uniquement les tables externes qui font référence à des données stockées dans Azure Data Lake storage ou Dataverse.
Views Oui. Les vues peuvent utiliser les éléments de langage de requête qui sont disponibles dans le modèle dédié. Oui, vous pouvez créer des vues sur des tables externes, des requêtes sur la fonction OPENROWSET et autres vues. Les vues peuvent utiliser les éléments de langage de requête qui sont disponibles dans le modèle serverless.
Schémas Oui Oui, les schémas sont pris en charge. Utilisez des schémas pour isoler différents locataires et placer leurs tables par schéma.
Tables temporaires Oui Les tables temporaires peuvent être utilisées juste pour stocker les informations des vues système, des littéraux ou autres tables temporaires. UPDATE/DELETE sur une table temporaire est également pris en charge. Vous pouvez joindre des tables temporaires aux vues système. Vous ne pouvez pas sélectionner des données dans une table externe pour les insérer dans une table temporaire ou joindre une table temporaire avec une table externe. Ces opérations échouent car les données externes et les tables temporaires ne peuvent pas être mélangées dans la même requête.
Procédures définies par l’utilisateur Oui Oui, les procédures stockées peuvent être placées dans n’importe quelle base de données utilisateur (mais pas dans la base de données master). Les procédures peuvent juste lire des données externes et utiliser des éléments de langage de requête qui sont disponibles dans un pool serverless.
Fonctions définies par l’utilisateur Oui Oui, uniquement les fonctions table inline sont prises en charge. Les fonctions scalaires définies par l’utilisateur ne sont pas prises en charge.
Déclencheurs Non Non, les pools SQL serverless n’autorisent pas la modification des données, donc les déclencheurs ne peuvent pas réagir aux modifications des données.
Tables externes Oui. Voir les formats de données pris en charge. Oui, les tables externes sont disponibles et peuvent être utilisées pour lire des données depuis Azure Data Lake Storage ou Dataverse. Consultez les formats de données pris en charge.
Mise en cache des requêtes Oui, plusieurs formes (mise en cache SSD, en mémoire et mise en cache des jeux de résultats). En outre, la vue matérialisée est prise en charge. Non, seules les statistiques de fichier sont mises en cache.
Mise en cache des jeux de résultats Oui Non, les résultats de la requête ne sont pas mis en cache. Seules les statistiques de fichier sont mises en cache.
Vues matérialisées Yes Non, les vues matérialisées ne sont pas prises en charge dans les pools SQL serverless.
Variables de table Non, utilisez des tables temporaires Non, les variables de table ne sont pas prises en charge.
Distribution de tables Oui Non, les distributions de table ne sont pas prises en charge.
Index de table Oui Non, les index ne sont pas pris en charge.
Partitionnement de table Oui. Les tables externes ne prennent pas en charge le partitionnement. Vous pouvez partitionner des fichiers à l’aide de la structure de dossiers Hive-partition et créer des tables partitionnées dans Spark. Le partitionnement Spark sera synchronisé avec le pool serverless. Si vous n’utilisez pas Spark, vous pouvez partitionner vos fichiers dans une structure de dossiers et créer des vues partitionnées sur la structure de partition de dossiers, mais les tables externes ne peuvent pas être créées sur des dossiers partitionnés.
Statistiques Oui Oui, les statistiques sont créées sur des fichiers externes.
Gestion des charges de travail, classes de ressources et contrôle d’accès concurrentiel Oui, consultez Gestion des charges de travail, classes de ressources et contrôle d’accès concurrentiel. Non, vous ne pouvez pas gérer les ressources qui sont affectées aux requêtes. Le pool SQL serverless gère automatiquement les ressources.
Contrôle des coûts Oui, à l’aide d’actions de scale-up et de scale-down. Oui, vous pouvez limiter l’utilisation quotidienne, hebdomadaire ou mensuelle du pool serverless avec le portail Azure ou la procédure T-SQL.

Langage de requête

Les fonctionnalités prises en charge des langages de requête utilisés dans Synapse SQL peuvent différer selon le modèle de consommation. Le tableau suivant présente les différences les plus importantes qui existent entre les dialectes Transact-SQL au niveau du langage de requête :

. Dédié Sans serveur
Instruction SELECT Oui. L’instruction SELECT est prise en charge, mais certaines clauses de requête Transact-SQL, comme FOR XML/FOR JSON, MATCH, OFFSET/FETCH ne sont pas prises en charge. Oui, l’instruction SELECT est prise en charge, mais certaines clauses de requête Transact-SQL, comme FOR XML, MATCH, PREDICT, GROUPNG SETS et les indicateurs de requête ne sont pas pris en charge.
Instruction INSERT Oui Non. Chargez de nouvelles données sur un lac données avec Spark ou autres outils. Utilisez Azure Cosmos DB avec le stockage analytique pour les charges de travail hautement transactionnelles. Vous pouvez utiliser CETAS pour créer une table externe et insérer des données.
Instruction UPDATE Oui Non, mettez à jour les données Parquet/CSV avec Spark et les modifications seront automatiquement disponibles dans le pool serverless. Utilisez Azure Cosmos DB avec le stockage analytique pour les charges de travail hautement transactionnelles.
Instruction DELETE Oui Non, supprimez les données Parquet/CSV avec Spark et les modifications seront automatiquement disponibles dans le pool serverless. Utilisez Azure Cosmos DB avec le stockage analytique pour les charges de travail hautement transactionnelles.
Instruction MERGE Oui (préversion) Non, fusionnez les données Parquet/CSV avec Spark et les modifications seront automatiquement disponibles dans le pool serverless.
Instruction CTAS Yes Non, l’instruction CREATE TABLE AS SELECT n’est pas prise en charge dans le pool SQL serverless.
Instruction CETAS Oui, vous pouvez effectuer une charge initiale dans une table externe avec CETAS. Oui, vous pouvez effectuer une charge initiale dans une table externe avec CETAS. CETAS prend en charge les formats de sortie Parquet et CSV.
Transactions Oui Oui, les transactions s’appliquent uniquement aux objets de métadonnées.
Étiquettes Oui Non, les étiquettes ne sont pas prises en charge dans les pools SQL serverless.
Chargement des données Oui. L’utilitaire recommandé est l’instruction COPY. Toutefois, le système prend également en charge le chargement en masse (BCP) et CETAS pour le chargement des données. Non, vous ne pouvez pas charger de données dans le pool SQL serverless parce que les données sont stockées sur un stockage externe. Vous pouvez initialement charger des données dans une table externe à l’aide de l’instruction CETAS.
Exportation de données Oui. Avec CETAS. Oui. Vous pouvez exporter des données à partir d’un stockage externe (lac de données Azure, Dataverse, Azure Cosmos DB) vers un lac de données Azure en utilisant CETAS.
Types Oui, tous les types Transact-SQL sauf cursor, hierarchyid, ntext, text et image, rowversion, Spatial Types, sql_variant et xml Oui, tous les types Transact-SQL sont pris en charge, sauf cursor, hierarchyid, ntext, text et image, rowversion, Spatial Types, sql_variant, xml et le type Table. Consultez comment mapper des types de colonnes Parquet à des types SQL ici.
Requêtes entre plusieurs bases de données Non Oui, les requêtes de bases de données croisées et les références de nom en 3 parties sont prises en charge, y compris l’instruction USE. Les requêtes peuvent faire référence aux bases de données SQL serverless ou aux bases de données de lac du même espace de travail. Les requêtes d’espaces de travail croisées ne sont pas prises en charge.
Fonctions intégrées/système (analyse) Oui, toutes les fonctions Transact-SQL de type analytique, conversion, date et heure, logique et mathématique, à l’exception de CHOOSE et PARSE Oui, toutes les fonctions Transact-SQL de type analytique, conversion, date et heure, logique et mathématique sont prises en charge.
Fonctions intégrées/système (chaîne) Oui. Toutes les fonctions Transact-SQL de type chaîne, JSON et classement, à l’exception de STRING_ESCAPE et TRANSLATE Oui. Toutes les fonctions Transact-SQL de type chaîne, JSON et classement sont prises en charge.
Fonctions intégrées/système (chiffrement) Certains HASHBYTES est la seule fonction de chiffrement prise en charge dans les pools SQL serverless.
Fonctions table intégrées/système Oui, les fonctions d’ensemble de lignes Transact-SQL, sauf OPENXML, OPENDATASOURCE, OPENQUERY et OPENROWSET Oui, toutes les fonctions d’ensemble de lignes Transact-SQL sont prises en charge, sauf OPENXML, OPENDATASOURCE et OPENQUERY.
Agrégats intégrés/système Agrégats Transact-SQL intégrés, sauf CHECKSUM_AGG et GROUPING_ID Oui, tous les agrégats intégrés à Transact-SQL sont pris en charge.
Opérateurs Oui, tous les opérateurs Transact-SQL sauf !> et !< Oui, tous les opérateurs Transact-SQL sont pris en charge.
Contrôle du flux Oui. Toutes les instructions de contrôle de flux Transact-SQL, à l’exception de CONTINUE, GOTO, RETURN, USE et WAITFOR Oui. Toutes les instructions de contrôle de flux Transact-SQL sont prises en charge. La requête SELECT dans la condition WHILE (...) n’est pas prise en charge.
Instructions DDL (CREATE, ALTER, DROP) Oui. Toutes les instructions DDL Transact-SQL applicables aux types d’objets pris en charge Oui, toutes les instructions DDL Transact-SQL applicables aux types d’objets pris en charge sont prises en charge.

Sécurité

Les pools Synapse SQL vous permettent d’utiliser des fonctionnalités de sécurité intégrées pour sécuriser vos données et contrôler l’accès. Le tableau suivant montre les principales différences qui existent entre les différents modèles de consommation Synapse SQL.

Fonctionnalité Dédié Sans serveur
Connexions N/A (seuls les utilisateurs contenus sont pris en charge dans les bases de données) Oui, les connexions SQL et Microsoft Entra ID au niveau serveur sont prises en charge.
Utilisateurs N/A (seuls les utilisateurs contenus sont pris en charge dans les bases de données) Oui, les utilisateurs de base de données sont pris en charge.
Utilisateurs contenus Oui. Remarque : Un seul utilisateur Microsoft Entra peut avoir le rôle d’administrateur non restreint. Non, les utilisateurs autonomes ne sont pas pris en charge.
Authentification par nom d’utilisateur/mot de passe SQL Oui Oui, les utilisateurs peuvent accéder au pool SQL serverless avec leur nom d’utilisateur et leur mot de passe.
Authentification Microsoft Entra Oui, les utilisateurs de Microsoft Entra Oui, les utilisateurs et les connexions Microsoft Entra peuvent accéder aux pools SQL serverless avec leurs identités Microsoft Entra.
Authentification directe Microsoft Entra pour le stockage Oui Oui, l’authentification directe Microsoft Entra s’applique aux connexions Microsoft Entra. L’identité de l’utilisateur Microsoft Entra est passée au stockage si aucune information d’identification n’est spécifiée. L’authentification directe Microsoft Entra n’est pas disponible pour les utilisateurs SQL.
Authentification par jeton de signature d’accès partagé de stockage Non Oui, en utilisant DATABASE SCOPED CREDENTIAL avec un jeton de signature d’accès partagé dans EXTERNAL DATA SOURCE ou CREDENTIAL de niveau instance avec une signature d’accès partagé.
Authentification par clé d’accès de stockage Oui, avec DATABASE SCOPED CREDENTIAL dans EXTERNAL DATA SOURCE Non, utilisez un jeton SAS au lieu d’une clé d’accès de stockage.
Authentification par identité managée pour le stockage Oui, avec des informations d’identification Managed Service Identity Oui, la requête peut accéder au stockage avec les informations d’identification d’identité managée de l’espace de travail.
Authentification par principal de service (SPN)/identité de l’application de stockage Oui Oui, vous pouvez créer des informations d’identification avec un ID d’application de principal de service qui sera utilisé pour l’authentification sur le stockage.
Rôles serveur Non Oui, sysadmin, public et d’autres rôles de serveur sont pris en charge
INFORMATIONS D’IDENTIFICATION AU NIVEAU DU SERVEUR Non Oui, les informations d’identification de niveau serveur sont utilisées par la fonction OPENROWSET qui ne se sert pas de source de données explicite.
Autorisations - Au niveau du serveur Non Oui, par exemple CONNECT ANY DATABASE et SELECT ALL USER SECURABLES permettent à l’utilisateur de lire les données de n’importe quelle base de données.
Rôles de bases de données Yes Oui, vous pouvez utiliser les rôles db_ownerdb_datareader et db_ddladmin.
DATABASE SCOPED CREDENTIAL Oui, utilisé dans les sources de données externes. Oui, les informations d’identification délimitées à la base de données peuvent être utilisées dans des sources de données externes pour définir la méthode d’authentification du stockage.
Autorisations - Au niveau de la base de données Oui Oui, vous pouvez accorder, refuser ou révoquer des autorisations sur les objets de base de données.
Autorisations - Au niveau du schéma Oui, y compris la possibilité d’accorder (GRANT), de refuser (DENY) et de révoquer (REVOKE) des autorisations pour les utilisateurs/connexions au niveau du schéma Oui, vous pouvez spécifier des autorisations de niveau schéma, notamment les autorisations GRANT, DENY et REVOKE pour les utilisateurs/connexions sur le schéma.
Autorisations - Au niveau objet Oui, y compris la possibilité d’accorder (GRANT), de refuser (DENY) et de révoquer (REVOKE) des autorisations pour les utilisateurs Oui, vous pouvez accorder (GRANT), refuser (DENY) et révoquer (REVOKE) des autorisations pour les utilisateurs/connexions au niveau des objets système pris en charge.
Autorisations - Sécurité au niveau des colonnes Oui La sécurité au niveau des colonnes est prise en charge dans les pools SQL serverless pour les vues et non pour les tables externes. Dans le cas de tables externes, vous pouvez créer une vue logique en haut de la table externe, puis appliquer la sécurité au niveau des colonnes.
Sécurité au niveau des lignes Oui Non, il n’existe pas de prise en charge intégrée pour la sécurité au niveau des lignes. Utilisez des vues personnalisées comme solution de contournement.
Masquage de données Oui Non, le masquage des données intégré n’est pas pris en charge dans les pools SQL serverless. Utilisez des vues SQL de wrapper qui masquent explicitement certaines colonnes comme solution de contournement.
Fonctions d’identité et de sécurité intégrées/système Certaines fonctions et certains opérateurs de sécurité Transact-SQL : CURRENT_USER, HAS_DBACCESS, IS_MEMBER, IS_ROLEMEMBER, SESSION_USER, SUSER_NAME, SUSER_SNAME, SYSTEM_USER, USER, USER_NAME, EXECUTE AS, OPEN/CLOSE MASTER KEY Certaines fonctions et certains opérateurs de sécurité Transact-SQL sont pris en charge : CURRENT_USER, HAS_DBACCESS, HAS_PERMS_BY_NAME, IS_MEMBER, IS_ROLEMEMBER, IS_SRVROLEMEMBER, SESSION_USER, SESSION_CONTEXT, SUSER_NAME, SUSER_SNAME, SYSTEM_USER, USER, USER_NAME, EXECUTE AS et REVERT. Les fonctions de sécurité ne peuvent pas être utilisées pour interroger des données externes (stockez le résultat dans une variable qui peut être utilisée dans la requête).
Chiffrement transparent des données (TDE) Oui Non, Transparent Data Encryption n’est pas pris en charge.
Découverte et classification des données Oui Non, la découverte et la classification des données ne sont pas prises en charge.
Évaluation des vulnérabilités Oui Non, l’évaluation des vulnérabilités n’est pas disponible.
Protection avancée contre les menaces Oui Non, la protection avancée contre les menaces n’est pas prise en charge.
Audit Oui Oui, l’audit est pris en charge dans les pools SQL serverless.
Règles de pare-feu Oui Oui, les règles de pare-feu peuvent être définies sur le point de terminaison SQL serverless.
Point de terminaison privé Oui Oui, le point de terminaison privé peut être défini sur le pool SQL serverless.

Le pool SQL dédie et le pool SQL serverless utilisent le langage Transact-SQL standard pour interroger les données. Pour plus d’informations sur ces différences, consultez la documentation de référence sur le langage Transact-SQL.

Fonctionnalités de la plate-forme

Fonctionnalité Dédié Sans serveur
Mise à l'échelle Oui Le pool SQL serverless est automatiquement mis à l’échelle en fonction de la charge de travail.
Mettre en pause/reprendre Oui Le pool SQL serverless est automatiquement désactivé lorsqu’il n’est pas utilisé et activé si nécessaire. L’action utilisateur n’est pas requise.
Sauvegardes de base de données Oui Non. Les données étant stockées dans des systèmes externes (ADLS, Cosmos DB), veillez à effectuer des sauvegardes de données à la source. Veillez à utiliser les métadonnées SQL de stockage (table, vue, définitions de procédure et autorisations utilisateur) dans le contrôle de code source. Les définitions de table de la base de données Lake sont stockées dans les métadonnées Spark. Veillez donc à conserver également les définitions de table Spark dans le contrôle de code source.
Restauration de base de données Oui Non. Les données étant stockées dans des systèmes externes (ADLS, Cosmos DB), vous devez récupérer les systèmes sources pour apporter vos données. Veillez à ce que vos métadonnées SQL (table, vue, définitions de procédure et autorisations utilisateur) se trouvent dans le contrôle de code source afin de pouvoir recréer les objets SQL. Les définitions de table de la base de données Lake sont stockées dans les métadonnées Spark. Veillez donc à conserver également les définitions de table Spark dans le contrôle de code source.

Outils

Vous pouvez utiliser divers outils pour vous connecter à Synapse SQL en vue d’interroger les données.

Outil Dédié Sans serveur
Synapse Studio Oui, les scripts SQL Oui, les scripts SQL peuvent être utilisés dans Synapse Studio. Utilisez SSMS ou ADS au lieu de Synapse Studio si vous retournez une grande quantité de données dans les résultats.
Power BI Oui Oui, vous pouvez utiliser Power BI pour créer des rapports sur un pool SQL serverless. Le mode d’importation est recommandé pour la création de rapports.
Azure Analysis Services Oui Oui, vous pouvez charger des données dans Azure Analysis Service à l’aide du pool SQL serverless.
Azure Data Studio (ADS) Yes Oui, vous pouvez utiliser Azure Data Studio (version 1.18.0 ou ultérieure) pour interroger un pool SQL serverless. Les scripts SQL et les notebooks SQL sont pris en charge.
SQL Server Management Studio (SSMS) Oui Oui, vous pouvez utiliser SQL Server Management Studio (version 18.5 ou ultérieure) pour interroger un pool SQL serverless. SSMS montre uniquement les objets qui sont disponibles dans les pools SQL serverless.

Notes

Vous pouvez utiliser SSMS pour vous connecter au pool SQL serverless et interroger. Il est partiellement pris en charge à partir de la version 18.5 ; vous pouvez l’utiliser pour vous connecter et interroger uniquement.

La plupart des applications utilisent le langage Transact-SQL standard pour interroger les modèles de consommation dédiés et serverless de Synapse SQL.

Accès aux données

Les données qui sont analysées peuvent être stockées sur différents types de stockage. Le tableau suivant liste toutes les options de stockage disponibles :

Type de stockage Dédié Sans serveur
Stockage interne Oui Non, les données sont placées dans Azure Data Lake ou le stockage analytique Azure Cosmos DB.
Azure Data Lake v2 Oui Oui, vous pouvez utiliser des tables externes et la fonction OPENROWSET pour lire les données d’ADLS. Découvrez ici comment configurer le contrôle d’accès.
Stockage Blob Azure Oui Oui, vous pouvez utiliser des tables externes et la fonction OPENROWSET pour lire les données du Stockage Blob Azure. Découvrez ici comment configurer le contrôle d’accès.
Azure SQL/SQL Server (à distance) Non Non, le pool SQL serverless ne peut pas faire référence à la base de données Azure SQL. Vous pouvez référencer des pools SQL serverless d’Azure SQL à l’aide de requêtes élastiques ou de serveurs liés.
Dataverse Non, vous pouvez charger des données Azure Cosmos DB dans un pool dédié en utilisant Azure Synapse Link dans le pool SQL serverless (via ADLS) ou Spark. Oui, vous pouvez lire des tables Dataverse à l’aide d’Azure Synapse Link pour Dataverse avec Azure Data Lake.
Stockage transactionnel Azure Cosmos DB Non Non, vous ne pouvez pas accéder aux conteneurs Azure Cosmos DB pour mettre à jour des données ou lire des données du stockage transactionnel Azure Cosmos DB. Utilisez des pools Spark pour mettre à jour le stockage transactionnel Azure Cosmos DB.
Stockage analytique Azure Cosmos DB Non, vous pouvez charger des données Azure Cosmos DB dans un pool dédié en utilisant Azure Synapse Link dans le pool SQL serverless (via ADLS), ADF, Spark ou autres outils de chargement. Oui, vous pouvez interroger le stockage analytique Azure Cosmos DB avec Azure Synapse Link.
Tables Apache Spark (dans un espace de travail) Non Oui, le pool serverless peut lire les tables PARQUET et CSV à l’aide de la synchronisation des métadonnées.
Tables Apache Spark (distantes) Non Non, le pool serverless peut accéder uniquement aux tables PARQUET et CSV créées dans les pools Apache Spark du même espace de travail Synapse. Toutefois, vous pouvez créer manuellement une table externe qui fait référence à l’emplacement de la table Spark externe.
Tables Databricks (distantes) Non Non, le pool serverless peut accéder uniquement aux tables PARQUET et CSV créées dans les pools Apache Spark du même espace de travail Synapse. Toutefois, vous pouvez créer manuellement une table externe qui fait référence à l’emplacement de la table Databricks.

Formats de données

Les données qui sont analysées peuvent être stockées dans différents formats de stockage. Le tableau suivant liste tous les formats de données qui peuvent être analysés :

Format de données Dédié Sans serveur
Delimited Oui Oui, vous pouvez interroger des fichiers délimités.
CSV Oui, (les délimiteurs multicaractères ne sont pas pris en charge) Oui, vous pouvez interroger des fichiers CSV. Pour de meilleures performances, utilisez PARSER_VERSION 2.0 qui permet une analyse plus rapide. Si vous ajoutez des lignes à vos fichiers CSV, veillez à interroger les fichiers comme étant modifiables.
Parquet Oui Oui, vous pouvez interroger les fichiers Parquet, y compris les fichiers avec des types imbriqués.
Hive ORC Oui Non, les pools SQL serverless ne peuvent pas lire le format ORC Hive.
Hive RC Oui Non, les pools SQL serverless ne peuvent pas lire le format RC Hive.
JSON Oui Oui, vous pouvez interroger des fichiers JSON à l’aide du format texte délimité et des fonctions T-SQL JSON.
Avro Non Non, les pools SQL serverless ne peuvent pas lire le format Avro.
Delta Lake Non Oui, vous pouvez interroger les fichiers Delta Lake, y compris les fichiers avec des types imbriqués.
Common Data Model (CDM) Non Non, le pool SQL serverless ne peut pas lire les données stockées avec Common Data Model.

Étapes suivantes

Pour plus d’informations sur les bonnes pratiques concernant les pools SQL dédiés et les pools SQL serverless, consultez les articles suivants :