Understanding T-SQL and its Syntax

Most of the T-SQL applications are very useful and also support both Microsoft SQL server and also azure SQL data. Let’s consider example SQL components like data types or operators.

Few T SQL differences in the DDL and DML elements result in the T-SQL statements and queries which are only partially supported. There are also some features and syntaxes which aren’t supported because of azure SQL data on the system of the data and operating system. Most of the instance-level features will not be supported. T SQL statements and options are not being used. If it’s been configured then the instance level options and also the operating system components along with the file system will be configured. When the plus points are available, then the appropriate alternative will often be available in some of the other ways from the SQL data or from another azure feature or the services. High availability will be built into azure and T SQL statements which are related to availability groups will not be supported by the SQL data. The dynamic management views are related to always availability groups are not supported.

T SQL syntax statements:

Core Data definition language statements are available but Data definition language statements extensions related to unsupported features like file placement on the disk will not be supported. In the SQL server, creating data and altering statements have over three dozen options.

This will not be a matter if we generate the data or the information in the SQL Database before we migrate. But if we have migrating T-SQL code that will create the database, we should compare it with the CREATE DATABASE which is Azure SQL Database with the SQL Server syntax at CREATE DATABASE which is SQL Server T-SQL to make sure all the options you use are supported. Create database for the Azure Structured query language Database also has the service objective and also the elastic pool options that apply only to SQL Database. 

The create table and alter table statements will have file tables and file teams options that cannot be used on SQL Database because these features aren’t supported.

Creating login and ALTER LOGIN statements which are been supported, but will not offer all options available in SQL Server. 

Collation of the system objects:

Endpoint statements of the Structure query language where the Database will not support the Windows authentication, but it will support the Azure Active Directory authentication. This will include the authentication of the Active Directory principals federated with the  Azure Active Directory. For further we can see the Connection to SQL Database or Azure Synapse Analytics By Using Azure Active Directory Authentication.

Cross-platform and cross-instance queries by making use of the three or four-part names. Three-part names referencing the temp database and the current database are supported. The elastic query will be supporting only read-only files to the tables in other structure query language databases. Cross-platform who is the owner will be chaining and the trustworthy of the database property.

EXECUTE AS LOGIN. Use EXECUTE AS USER instead.

Extensible key management (EKM) for encryption keys. Transparent Data Encryption which is also called TDE and the customer will manage the keys and Always been Encrypted column master keys which will be not stored in Azure Key Vault.

Eventing: event notifications or query notifications.

File characteristics: here the Syntax is been related to the data file name, placement, and also the other file properties automatically which is been managed by the Structure query language Database.

High availability: during this, the Syntax is associated with the high availability and also the database recovery, which is able to manage by the structure search language Database which incorporates the syntax for backup, restore, Always-On, database mirroring, log shipping, recovery models.

The syntax is been associated with the snapshot, transactional, and merges replication, which isn’t available in SQL Database. Replication subscriptions are supported.

NET Framework: CLR integration

Semantic search

Server credentials: Use database scoped credentials instead.
Server-level permissions: GRANT, REVOKE, and DENY of server-level permissions aren’t supported. 

Most of the server-level permissions are being replaced by the data from the table-level permissions or granted implicitly by built-in server roles. 

Most of the server levels of the DM and the catalog views have similar database-level views.

Set 

SHUTDOWN
sp_addmessage
sp_configure and RECONFIGURE. ALTER DATABASE SCOPED CONFIGURATION is supported.
sp_helpuser
sp_migrate_user_to_contained

Structure query language Server Agent: In this, the Syntax will rely upon the Structure query language Server Agent or the MSDB database: alerts, operators, or the central management servers. Use scripting, like Power Shell, instead of SQL Server audit: Use SQL Database auditing Use SQL Database auditing instead.

SQL Server trace.

Trace flags.

T-SQL debugging.

Server-scoped or logon triggers.

USE statement: To change database c

 
Azure software Synopsis of the structure query language which is a huge data analytic service enables us to query and analyze our data by making use of the T-SQL language.

We can also use the standard ANSI-compliant dialect of the Structure query language used on the Structure query language Server and Azure SQL Database for data analysis.

Transact-Structure query language will be used in the serverless Structure query language of the pool and dedicate the model which will be a reference from different objects and has most of the differences in the set of supported features. 

Data formats:

Delimited	Dedicated	Serverless
CSV	yes	yes
Parquet	yes	yes
Hive ORC	yes	no
JSON	yes	yes
CDM	no	no

Storage options

Internal storage	Dedicated	Serverless
Azure data	yes	yes
Azure blob	yes	yes
Azure SQL	no	no
Azure cosmos	no	no
Apache spark	no	parquet
Apache spark tables	no	no

Key design decisions

Article	Dedicated SQL pool	Serverless
Connection	yes	yes
Resource classes And concurrency	yes	no
Transactions	yes	no
User-defined	yes	yes
Table distribution	yes	no
Table indexes	yes	no

Perquisites:

The first step will be: creating the data where we will execute the queries and then initialize the objects by keeping executing the script on the database and this script will be creating the data sources and data which has scope and external files formats which are being used to read the data in the sample.

We can also create an external table as select statements which is stored the query which results in the storage 

Use db name;

GO

CREATE DATA BASE SCOPED CREADENTIALS 

WITH INDETITY= ‘SHARED ACCESS SIGNATURE’

          SECRET=’ sv=2018-4-23

GO

CREATE EXTERNAL DATA SOURCE

   LOCATION = ‘https:// <storage account name>

);

CREATE EXTERNAL FILE FORMAT WITH (

   FORMAT_TYPE = PARQUET,

DATA_COMPRESSION = ‘org.apache.hadoop.io. compress. snappycodec

);

GO

Making use of the external tables

Use db name;

GO

SELECT 

      Country name, population

    FROM populationcetas

WHERE 

Year= 2019

ORDER BY

Population DESC;

Questions

1. What is use of the external table?