10 T-SQL Tips and Tricks

Maximize SQL Server Efficiency

I realised I don’t cover T-SQL enough.

.NET and T-SQL share a bond that’s unmistakable. For as long as I can remember, I’ve worked with T-SQL alongside C# and Entity Framework Core.

However, beneath all that abstraction, it’s easy to lose touch with the nuances of raw SQL and the importance of understanding it deeply.

So let’s dive in some RAW SQL :)

1. Common Table Expressions (CTEs)

Ever found yourself lost in a sea of subqueries? CTEs can be a lifesaver. They let you name a temporary result set and use it within your main query. Here’s how you can use it:

WITH Sales_CTE AS (
  SELECT CustomerID, SUM(OrderTotal) AS TotalSales
  FROM Orders
  GROUP BY CustomerID
)
SELECT *
FROM Sales_CTE
WHERE TotalSales > 1000;

2. Window Functions

Seeing data from a new perspective.

Window functions allow you to look at your data through a ‘window’ and perform calculations across related rows. Think of calculating a running total or ranking items.

SELECT 
  CustomerID, 
  OrderDate, 
  OrderTotal, 
  SUM(OrderTotal) OVER (ORDER BY OrderDate) AS RunningTotal
FROM Orders;