T-SQL language preview

1. Variables, hints, and a temp table

@variables, @@globals, bracketed identifiers, and NOLOCK

horizon-dark

DECLARE @count INT;

SELECT TOP 10 [OrderId], N'note' AS Note
FROM #TempOrders WITH (NOLOCK)
WHERE CustomerId = @count AND @@ROWCOUNT > 0;
GO

atom-one-dark

DECLARE @count INT;

SELECT TOP 10 [OrderId], N'note' AS Note
FROM #TempOrders WITH (NOLOCK)
WHERE CustomerId = @count AND @@ROWCOUNT > 0;
GO

github-dark

DECLARE @count INT;

SELECT TOP 10 [OrderId], N'note' AS Note
FROM #TempOrders WITH (NOLOCK)
WHERE CustomerId = @count AND @@ROWCOUNT > 0;
GO

dracula

DECLARE @count INT;

SELECT TOP 10 [OrderId], N'note' AS Note
FROM #TempOrders WITH (NOLOCK)
WHERE CustomerId = @count AND @@ROWCOUNT > 0;
GO

nord

DECLARE @count INT;

SELECT TOP 10 [OrderId], N'note' AS Note
FROM #TempOrders WITH (NOLOCK)
WHERE CustomerId = @count AND @@ROWCOUNT > 0;
GO

github

DECLARE @count INT;

SELECT TOP 10 [OrderId], N'note' AS Note
FROM #TempOrders WITH (NOLOCK)
WHERE CustomerId = @count AND @@ROWCOUNT > 0;
GO

2. A stored procedure

PROCEDURE, TRY/CATCH, and error handling

horizon-dark

CREATE PROCEDURE dbo.GetOrderTotal
    @orderId INT
AS
BEGIN
    BEGIN TRY
        SELECT SUM(LineTotal) AS Total
        FROM OrderLines
        WHERE OrderId = @orderId;
    END TRY
    BEGIN CATCH
        THROW;
    END CATCH
END
GO

atom-one-dark

CREATE PROCEDURE dbo.GetOrderTotal
    @orderId INT
AS
BEGIN
    BEGIN TRY
        SELECT SUM(LineTotal) AS Total
        FROM OrderLines
        WHERE OrderId = @orderId;
    END TRY
    BEGIN CATCH
        THROW;
    END CATCH
END
GO

github-dark

CREATE PROCEDURE dbo.GetOrderTotal
    @orderId INT
AS
BEGIN
    BEGIN TRY
        SELECT SUM(LineTotal) AS Total
        FROM OrderLines
        WHERE OrderId = @orderId;
    END TRY
    BEGIN CATCH
        THROW;
    END CATCH
END
GO

dracula

CREATE PROCEDURE dbo.GetOrderTotal
    @orderId INT
AS
BEGIN
    BEGIN TRY
        SELECT SUM(LineTotal) AS Total
        FROM OrderLines
        WHERE OrderId = @orderId;
    END TRY
    BEGIN CATCH
        THROW;
    END CATCH
END
GO

nord

CREATE PROCEDURE dbo.GetOrderTotal
    @orderId INT
AS
BEGIN
    BEGIN TRY
        SELECT SUM(LineTotal) AS Total
        FROM OrderLines
        WHERE OrderId = @orderId;
    END TRY
    BEGIN CATCH
        THROW;
    END CATCH
END
GO

github

CREATE PROCEDURE dbo.GetOrderTotal
    @orderId INT
AS
BEGIN
    BEGIN TRY
        SELECT SUM(LineTotal) AS Total
        FROM OrderLines
        WHERE OrderId = @orderId;
    END TRY
    BEGIN CATCH
        THROW;
    END CATCH
END
GO

3. Window functions and MERGE

ROW_NUMBER, OVER/PARTITION BY, and MERGE

horizon-dark

SELECT
    CustomerId,
    ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate DESC) AS rn
FROM Orders;

MERGE INTO Targets AS t
USING Sources AS s ON t.Id = s.Id
WHEN MATCHED THEN UPDATE SET t.Value = s.Value
WHEN NOT MATCHED THEN INSERT (Id, Value) VALUES (s.Id, s.Value);

atom-one-dark

SELECT
    CustomerId,
    ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate DESC) AS rn
FROM Orders;

MERGE INTO Targets AS t
USING Sources AS s ON t.Id = s.Id
WHEN MATCHED THEN UPDATE SET t.Value = s.Value
WHEN NOT MATCHED THEN INSERT (Id, Value) VALUES (s.Id, s.Value);

github-dark

SELECT
    CustomerId,
    ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate DESC) AS rn
FROM Orders;

MERGE INTO Targets AS t
USING Sources AS s ON t.Id = s.Id
WHEN MATCHED THEN UPDATE SET t.Value = s.Value
WHEN NOT MATCHED THEN INSERT (Id, Value) VALUES (s.Id, s.Value);

dracula

SELECT
    CustomerId,
    ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate DESC) AS rn
FROM Orders;

MERGE INTO Targets AS t
USING Sources AS s ON t.Id = s.Id
WHEN MATCHED THEN UPDATE SET t.Value = s.Value
WHEN NOT MATCHED THEN INSERT (Id, Value) VALUES (s.Id, s.Value);

nord

SELECT
    CustomerId,
    ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate DESC) AS rn
FROM Orders;

MERGE INTO Targets AS t
USING Sources AS s ON t.Id = s.Id
WHEN MATCHED THEN UPDATE SET t.Value = s.Value
WHEN NOT MATCHED THEN INSERT (Id, Value) VALUES (s.Id, s.Value);

github

SELECT
    CustomerId,
    ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate DESC) AS rn
FROM Orders;

MERGE INTO Targets AS t
USING Sources AS s ON t.Id = s.Id
WHEN MATCHED THEN UPDATE SET t.Value = s.Value
WHEN NOT MATCHED THEN INSERT (Id, Value) VALUES (s.Id, s.Value);