Loading Data Using the INSERT Statement in Azure SQL Database (SQL Server 2022)

The `INSERT` statement in SQL Server 2022 on Azure SQL Database allows developers and database administrators to add new rows to a table with precision, scalability, and cloud-optimized performance. This statement forms a core component of data manipulation within both transactional and analytical workloads hosted on Azure.
Basic Syntax

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

• table_name: The name of the target table.
• column1, column2, ...: The columns into which values will be inserted.
• value1, value2, ...: The actual values that will be added.

If values are provided for all columns in the correct order, the column list can be omitted:

INSERT INTO table_name
VALUES ('A', 100, GETDATE());

Inserting Data from a Query
You can insert data into a table based on the results of a query:

INSERT INTO target_table (column1, column2)
SELECT column1, column2
FROM source_table
WHERE condition;

This is especially useful for data migration, transformations, and replication scenarios.

Azure SQL Database supports default constraints. If a column has a default value and is not listed in the `INSERT` statement, the default will be used. If no default is set and the column allows `NULL`, a `NULL` will be inserted.

CREATE TABLE Products (
    ProductID INT IDENTITY,
    ProductName NVARCHAR(100),
    Quantity INT DEFAULT 10
);

INSERT INTO Products (ProductName)
VALUES ('Widget'); -- Quantity will default to 10

Using IDENTITY and AUTO-INCREMENT Columns
Azure SQL supports identity columns which auto-increment on insert. You do not insert values into these columns unless explicitly enabled:

SET IDENTITY_INSERT table_name ON;
-- insert statement with explicit ID value
SET IDENTITY_INSERT table_name OFF;

Transaction Support
To maintain data integrity in multi-statement inserts:

BEGIN TRANSACTION;
    INSERT INTO Orders (...) VALUES (...);
    INSERT INTO OrderItems (...) VALUES (...);
COMMIT TRANSACTION;

In case of errors, replace `COMMIT` with `ROLLBACK`.
Using TOP to Limit Insert Rows
Azure SQL supports the `TOP` clause in `INSERT INTO ... SELECT` statements:

INSERT INTO target_table (col1, col2)
SELECT TOP (10) col1, col2 FROM source_table
ORDER BY col1;

Bulk Data Insertion
Azure SQL Database does not support file system access, so traditional `BULK INSERT` from local disk is not permitted. Instead, use Azure Blob Storage:

BULK INSERT table_name
FROM 'https://<storageaccount>.blob.core.windows.net/container/file.csv'
WITH (
    DATA_SOURCE = 'MyAzureBlobStorage',
    FORMAT = 'CSV',
    FIRSTROW = 2
);

Configure the external data source using a shared access signature (SAS).
Security and Parameterization
Use parameterized queries to prevent SQL injection:

-- In application code:
INSERT INTO Users (Username, Email)
VALUES (@Username, @Email);

Azure also supports Always Encrypted and row-level security for enhanced protection of inserted data.

Utilize the following tools for performance and logging:

1. Query Performance Insight
2. Azure Monitor Logs
3. Extended Events
4. Dynamic Management Views (DMVs)

Use `sys.dm_db_resource_stats` and `sys.dm_exec_requests` to monitor insert throughput and wait statistics.
When managing SQL Server 2022 workloads with Azure SQL Database, a robust suite of tools is available to monitor performance and log activities. These tools provide insights from high-level overviews down to granular query-level details, enabling effective troubleshooting and optimization.
Here is how you can utilize Query Performance Insight, Azure Monitor Logs, Extended Events, and Dynamic Management Views (DMVs):
Query Performance Insight
Query Performance Insight is a feature within the Azure portal designed to help you quickly identify the most CPU-intensive and long-running queries affecting your Azure SQL Database.

• How it works: It leverages data from the Query Store, which must be enabled on your database. Query Store captures a history of queries, execution plans, and runtime statistics.
• Key uses:
  • Identify Top Resource Consumers: View the queries consuming the most CPU, data I/O, and log I/O.
  • Analyze Long-Running Queries: Pinpoint queries with the longest execution durations.
  • View Query Details: Drill down into specific queries to see their text, execution history, and resource utilization.
  • Performance Recommendations: Query Performance Insight often integrates with Azure SQL Database Advisor to provide recommendations for optimizing queries.
• Utilization with SQL Server 2022 in Azure: While Query Performance Insight is a direct feature of Azure SQL Database, the underlying Query Store that powers it is also a critical component in SQL Server 2022. When using SQL Server 2022 in an Azure Virtual Machine or with Azure SQL Managed Instance, you would similarly ensure Query Store is active. You can then use tools like SQL Server Management Studio (SSMS) to analyze Query Store data or leverage other Azure monitoring integrations where applicable. For Azure SQL Database specifically, Query Performance Insight provides a built-in, user-friendly interface in the Azure portal.

Azure Monitor Logs
Azure Monitor is a comprehensive monitoring service in Azure that provides a complete toolset to collect, analyze, and act on telemetry from your cloud and on-premises environments. Azure Monitor Logs (powered by Log Analytics workspaces) is a key component for storing and querying log data and performance metrics.

• How it works:
  • Data Collection: Azure SQL Database automatically sends platform metrics to Azure Monitor. For more detailed logging, including resource logs (diagnostic logs), you need to create a diagnostic setting to route this data to a Log Analytics workspace.
  • Log Analytics: Once in a Log Analytics workspace, you can use the Kusto Query Language (KQL) to perform complex queries, create dashboards, and set up alerts.
• Key uses:
  • Centralized Logging: Collect logs and metrics from Azure SQL Database, including query wait statistics, errors, and resource utilization.
  • Performance Analysis: Analyze trends, identify bottlenecks, and correlate database performance with other Azure resource metrics.
  • Alerting: Set up alerts based on specific metrics or log patterns (e.g., high CPU usage, excessive deadlocks, specific error messages).
  • Custom Dashboards and Workbooks: Create visualizations to track key performance indicators (KPIs) over time.
  • SQL Insights: For SQL Server on Azure VMs or Azure Arc-enabled SQL Server, consider using SQL Insights (via Azure Monitor) for more detailed, SQL-specific monitoring dashboards and capabilities within Azure Monitor.
• Utilization with SQL Server 2022 in Azure:
  • Azure SQL Database: Directly configure diagnostic settings to send logs and metrics to Azure Monitor Logs.
  • SQL Server on Azure VM: You can install the Azure Monitor Agent (AMA) or the older Log Analytics agent to collect performance counters, event logs, and other data from the VM and the SQL Server instance. SQL Insights can be particularly beneficial here.
  • Azure Arc-enabled SQL Server: If you are managing SQL Server 2022 instances (even on-premises or in other clouds) with Azure Arc, you can leverage Azure Monitor for centralized monitoring by deploying the Azure Connected Machine agent and configuring data collection.

Extended Events (XEvents)^[1] is a highly configurable and lightweight performance monitoring system built into SQL Server and Azure SQL Database. It allows you to collect very specific information about database engine activities with minimal performance overhead.

• How it works: You create event sessions that define which events to capture, what data to collect for those events (actions), and where to send the data (targets).
• Key uses:
  • Troubleshooting Specific Issues: Capture detailed information about deadlocks, long-running queries, blocking, specific errors, or security-related events.
  • Performance Monitoring: Track wait statistics, query execution details, I/O activity, and other performance counters.
  • Auditing: Although Azure SQL Database has specific auditing features, XEvents can be used for more granular, custom auditing scenarios.
• Targets:
  • event_file: Writes event data to a file (e.g., in Azure Blob Storage for Azure SQL Database). This is useful for persistent storage and later analysis with SSMS.
  • ring_buffer: Stores event data in memory on a first-in, first-out (FIFO) basis. Good for capturing recent events for ad-hoc troubleshooting.
  • Other targets like event_counter (counts event occurrences) and histogram (groups events by a specific field) are also available.
• Utilization with SQL Server 2022 in Azure:
  • Azure SQL Database: Supports database-scoped event sessions. You can create and manage these using T-SQL or SSMS. Data can be written to Azure Storage.
  • Azure SQL Managed Instance: Supports both server-scoped and database-scoped sessions.
  • SQL Server on Azure VM: Full Extended Events functionality is available, just as with an on-premises SQL Server.
  • SQL Server 2022 Enhancements: SQL Server 2022 introduced new columns in sys.dm_xe_session_events to help measure the performance impact of the Extended Events session itself, which can be useful when designing intensive monitoring sessions. It also introduced new granular permissions for Extended Events.

Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs) are built-in SQL Server objects that return server state information. They are essential for monitoring the health of an instance, diagnosing problems, and tuning performance.
How it works: DMVs are queried using T-SQL like any other view. They provide a snapshot of the current state of the SQL Server engine or database.
Key uses:

• Connection and Session Monitoring: sys.dm_exec_connections, sys.dm_exec_sessions, sys.dm_exec_requests show current connections, sessions, and executing requests.
• Performance Troubleshooting:
  • sys.dm_os_wait_stats: Identifies wait types, indicating resource bottlenecks.
  • sys.dm_exec_query_stats: Provides aggregate performance statistics for cached query plans.
  • sys.dm_io_virtual_file_stats: Shows I/O statistics for data and log files.
  • sys.dm_db_index_usage_stats: Tracks index usage.
  • sys.dm_tran_locks: Information about active locks.
• Resource Usage:
  • sys.dm_db_resource_stats (Azure SQL Database): Provides CPU, I/O, and memory usage for the database.
  • sys.resource_stats (Azure SQL Database master database): Shows resource usage for an Azure SQL Database.
  • sys.server_resource_stats (Azure SQL Managed Instance): Provides resource usage for the managed instance.
• Query Store DMVs: sys.query_store_query, sys.query_store_plan, sys.query_store_runtime_stats, etc., allow direct querying of Query Store data.

Utilization with SQL Server 2022 in Azure:

• Azure SQL Database and Azure SQL Managed Instance: A large subset of DMVs is available. Permissions like VIEW DATABASE STATE or VIEW SERVER STATE (for Managed Instance) are generally required. SQL Server 2022 introduced more granular permissions like VIEW DATABASE PERFORMANCE STATE and VIEW SERVER PERFORMANCE STATE.
• SQL Server on Azure VM: Full DMV functionality is available.

Synergy Between Tools: These tools are often used together. For instance:

1. Azure Monitor might alert you to high CPU usage.
2. You could then use Query Performance Insight (for Azure SQL Database) or Query Store DMVs to identify the top offending queries.
3. For deeper analysis of a problematic query or a specific issue like deadlocking, you might create an Extended Events session.
4. DMVs can be used throughout the process to get real-time insights into waits, locks, and resource consumption while the problematic workload is running.

By understanding and effectively utilizing these tools, you can maintain optimal performance and quickly troubleshoot issues in your SQL Server 2022 and Azure SQL Database environments.
Summary: The `INSERT` statement in Azure SQL Database (SQL Server 2022) provides a secure, scalable, and cloud-native way to load data. Developers are encouraged to use transactions, Azure Blob Storage, and monitoring tools to ensure efficient data operations aligned with modern cloud architectures.

[1] Extended Events (XEvents): Extended Events (XEvents) in SQL Server 2022 is a lightweight and highly configurable performance monitoring system that allows you to collect detailed data about server operations with minimal overhead. It provides a flexible framework to define event sessions, capturing specific events and data points to diagnose performance issues, troubleshoot problems, and audit server activity.