Table DDL

Create a Table with DML

CREATE TABLE dbo.DimProduct
(
    ProductKey INTEGER NOT NULL,
    ProductAltKey VARCHAR(25) NULL,
    ProductName VARCHAR(50) NOT NULL,
    Category VARCHAR(50) NULL,
    ListPrice DECIMAL(5,2) NULL
);
GO

Creating a Table with COPY INTO

COPY INTO dbo.Region 
FROM 'https://mystorageaccountxxx.blob.core.windows.net/private/Region.csv' WITH ( 
  FILE_TYPE = 'CSV',
  CREDENTIAL = ( 
      IDENTITY = 'Shared Access Signature', 
      SECRET = 'xxx'
   ),
   FIRSTROW = 2
)
GO

Table DML

Insert

INSERT INTO dbo.DimProduct
VALUES
(1, 'RING1', 'Bicycle bell', 'Accessories', 5.99),
(2, 'BRITE1', 'Front light', 'Accessories', 15.49),
(3, 'BRITE2', 'Rear light', 'Accessories', 15.49);
GO

Select

SELECT  d.[Year] AS CalendarYear,
         d.[Month] AS MonthOfYear,
         d.MonthName AS MonthName,
        SUM(so.SalesTotal) AS SalesRevenue
FROM FactSalesOrder AS so
JOIN DimDate AS d ON so.SalesOrderDateKey = d.DateKey
GROUP BY d.[Year], d.[Month], d.MonthName
ORDER BY CalendarYear, MonthOfYear;

Warehouse Monitoring

Fabric DW had three management views:

1. sys.dm_exec_connections
2. sys.dm_exec_sessions
3. sys.dm_exec_requests

Query for Active Requests:

SELECT request_id, session_id, start_time, total_elapsed_time
    FROM sys.dm_exec_requests
    WHERE status = 'running'
    ORDER BY total_elapsed_time DESC;

Query Session information:

SELECT login_name
    FROM sys.dm_exec_sessions
    WHERE 'session_id' = 'SESSION_ID WITH LONG-RUNNING QUERY';

Kill a Session:

KILL 'SESSION_ID WITH LONG-RUNNING QUERY';