10 T-SQL Tips and Tricks

Maximize SQL Server Efficiency

Alex Maher

--

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;

3. Table Variables and Temporary Tables

Sometimes you need a temporary spot to store your data mid-query.

Table variables and temporary tables are perfect for this. They’re like those temporary storage bins you use when you’re reorganizing your room.

DECLARE @TempTable TABLE (CustomerID INT, TotalSales MONEY);
INSERT INTO @TempTable (CustomerID, TotalSales)
SELECT CustomerID, SUM(OrderTotal)
FROM Orders
GROUP BY CustomerID;

4. Indexes: Make your queries run faster.

If your queries are slow, indexes might just be the boost you need. They’re like shortcuts that help SQL Server find data faster. But remember, too many can slow down data updates, so it’s all about balance.

There are a couple of types of indexes, but let’s keep it simple and talk about the two…

--

--

Alex Maher

.NET C# dev with 10+ yrs exp, self-taught & passionate web developer. Sharing tips & experiences in C# and web dev.