SQL Server is a glorious beast. It’s powerful, robust, scalable and occasionally tries to gaslight you into believing that the problem is you, not it.
But here’s the thing, deep within its guts are some criminally underused features. The kinds of things that could actually make your life easier, if you knew they existed. So instead of furiously Googling “SQL Server why are you like this,” sit back, sip your coffee, and let’s walk through five SQL Server features that are hiding in plain sight, ready to save your time, your code, and maybe your will to live.
1. “sp_help” and “sp_helptext”: Your Lazy Developer Sidekick
Have you ever stared at a table and thought, “What even is this monstrosity?” Yeah, same.
Enter sp_help and its sibling sp_helptext.
sp_help gives you everything you need to know about a table: column names, data types, constraints, and more.
sp_helptext shows you the actual definition of a view, stored procedure, or function.
Example:
EXEC sp_help 'Sales.Orders';EXEC sp_helptext 'usp_GetTopCustomers';Yes, it’s that easy. You don’t need to open SSMS, click through 48 tabs, and right-click your way into carpal tunnel. Just execute and bask in the metadata glory.
Further Reading:
2. `WITH (NOLOCK)` – AKA “I Swear This Isn’t Dangerous”
If you like reading data without caring about those pesky things like “accuracy” or “integrity,” then say hello to WITH (NOLOCK).
This table hint tells SQL Server: “Don’t wait for locks, just give me whatever’s there right now, even if it’s halfway through a nervous breakdown.”
Example:
SELECT *
FROM Sales.Orders WITH (NOLOCK);You’ll get your data fast. You might even get phantom rows or duplicated values … how exciting! But hey, for reporting queries where speed is more important than truth, NOLOCK is your friend.
Disclaimer: Use with care. Like putting pineapple on pizza, it’s controversial for a reason.
Further Reading:
3. `CROSS APPLY`: The Unsung Hero of Joining Stuff
Most folks live their SQL lives using INNER JOIN, LEFT JOIN, and maybe a FULL OUTER JOIN if they’re feeling fancy. But let’s talk about CROSS APPLY, the Swiss Army knife of relational logic.
CROSS APPLY lets you call table valued functions or even use subqueries per row, making it a dynamic powerhouse for advanced queries.
Example:
Say we want to get the top 3 orders for every customer. Here’s an easy trick using CROSS APPLY:
SELECT c.CustomerID, c.CustomerName, o.OrderID, o.OrderDate
FROM Customers c
CROSS APPLY (
SELECT TOP 3 *
FROM Orders o
WHERE o.CustomerID = c.CustomerID
ORDER BY o.OrderDate DESC
) o; Try doing that elegantly with a basic join.
Further Reading:
4. `WAITFOR DELAY`: Because Sometimes You Just Want to Waste Time On Purpose
Need to simulate a slow running query for testing your timeout handling? Want to irritate your coworker by locking their transaction just a bit longer?
Say hello to WAITFOR DELAY.
Example:
WAITFOR DELAY '00:00:10'; -- 10-second nap
SELECT 'Finally awake';It’s like a coffee break, but for your query.
Use this to test long running procedures, simulate latency, or just to prove to your manager that yes, the system does slow down at 4 PM every Friday (totally the server’s fault, not yours).
Further Reading:
5. `OUTPUT` Clause: So You Actually Know What Just Happened
Remember that time you ran a huge UPDATE and immediately regretted not knowing which rows were changed?
SQL Server has an OUTPUT clause that can save you from that existential dread.
It lets you capture affected rows in INSERT, UPDATE, DELETE, and MERGE statements.
Example: Audit Changed Prices
UPDATE Products
SET Price = Price * 1.10
OUTPUT DELETED.ProductID, DELETED.Price AS OldPrice, INSERTED.Price AS NewPrice
WHERE CategoryID = 5;You just got an on-the-fly change log. No triggers. No cursors. No crying.
Further Reading:
Bonus: DBCC FREEPROCCACHE – The Button You Shouldn’t Press
This command clears the query plan cache. It’s like turning the lights off and on again, except for SQL Server’s memory.
DBCC FREEPROCCACHE;Use it in dev or test environments if you need to reset execution plans. Use it in production if you enjoy chaos and performance regression tickets.
Proceed with Caution:
DBCC FREEPROCCACHE (Official Docs)
Wrapping Up
SQL Server is like that overachieving sibling: brilliant but a bit too humble about it. These features won’t just save you time, they’ll upgrade your entire vibe as a SQL developer.
Now go forth, use CROSS APPLY, output your updates, and embrace the beautiful chaos of WITH (NOLOCK), responsibly, of course.
Extra Resources to Nerd Out:
