Introduction
As SQL developers, we often rely on a common subset of T-SQL commands and functions (e.g. SELECT, JOIN, GROUP BY, SUM, COUNT). But beyond the everyday tools lie a number of underutilized SQL constructs, performance‐oriented functions, and keywords that can dramatically improve the efficiency, clarity, and maintainability of your queries—especially in Azure SQL environments. In this post, we’ll explore some of those “hidden gems”: useful, but less familiar, SQL features, and when to use them.
Whether you’re working on Azure SQL Database, Managed Instance, or comparable SQL Server versions, these techniques can boost your productivity and performance.
Why These Matter in Azure SQL
- Azure SQL is a managed environment: you don’t control hardware, so query performance and cost efficiency depend heavily on how well your SQL is written.
- The cost model (DTU / vCore, IO, compute) makes every inefficiency impactful.
- Azure has newer T-SQL additions and optimizations (e.g. GREATEST / LEAST) that aren’t in older SQL Server versions.
- Knowing “lesser-known” commands can help you diagnose, optimize, or simplify tricky queries.
Useful (But Less Common) SQL Commands, Functions & Keywords
Below are commands, functions, or keywords that many developers may not use daily—but that can prove very valuable in the right context.
| Feature | What It Does / Why It’s Useful | Example / Notes |
|---|---|---|
| GREATEST(...) & LEAST(...) | Returns the greatest (or least) value among a list of expressions within the same row. This is different from MAX() over a column. Azure SQL supports GREATEST and LEAST in recent versions. (MSSQLTips.com) |
sql SELECT GREATEST(score1, score2, score3) AS topScore, LEAST(score1, score2, score3) AS bottomScore FROM MyTable; |
Query Hints (OPTION ( … ), WITH (… )) |
Hints let you override or nudge the optimizer’s behavior: force an index, disable parallelism, control join types, etc. Use cautiously. (Wikipedia) | E.g. SELECT * FROM MyTable WITH (INDEX(idx_MyTable_ColA)) WHERE ColA = 42; or … OPTION (MAXDOP 1)
|
| Forced Parameterization (via Azure SQL Advisor recommendation) | Helps reduce plan cache pollution by parameterizing literal values automatically. Good when you see many similar queries differing only by literal constants. (Microsoft Learn) | When enabled, SELECT * FROM Orders WHERE CustomerId = 123 and SELECT * FROM Orders WHERE CustomerId = 456 may reuse plan. |
| Automatic Tuning / Advisor Recommendations | Azure SQL can suggest or even auto-apply index creation/drop or plan fixes. Leverage it for continuous improvements. (Microsoft Learn) | In Azure Portal, go to Intelligent Performance → Recommendations |
| Batching / Bulk operations | Instead of issuing many individual SQL calls, group operations to reduce network overhead and latency. Azure SQL emphasizes batching to improve performance. (Microsoft Learn) | For example, insert multiple rows in one INSERT statement or send grouped updates |
| Intelligent Insights / Performance Patterns | Azure SQL can detect patterns (e.g. resource bottlenecks, memory pressure, locking) automatically via Intelligent Insights. Use them to spot hidden issues. (Azure Docs) | Monitor metrics like wait stats, memory grant waits, or lock escalations |
| Query Store / Query Performance Insight | Helps capture query execution history, identify top resource-consuming queries, and compare over time. Very handy for tuning. (Microsoft Learn) | Use Query Store views (e.g. sys.query_store_query, sys.query_store_runtime_stats) to examine performance trends |
| Window Functions & OLAP / Ranking Functions | Functions like ROW_NUMBER() OVER(...), RANK(), PERCENT_RANK(), LEAD() / LAG() are extremely powerful for analytics, running totals, gaps & islands, etc. (Many devs know basics, but advanced usage is underutilized.) |
E.g. SELECT *, ROW_NUMBER() OVER(PARTITION BY Category ORDER BY Date DESC) AS rn FROM Sales;
|
| Common Table Expressions (CTEs) / Recursive CTEs | Better readability, modular queries, and recursive logic (e.g. hierarchical data). Useful to break down complex logic. | ;WITH cte AS ( SELECT … ) SELECT … FROM cte JOIN … |
| CROSS APPLY / OUTER APPLY | Allows invoking table-valued functions or subqueries for each row. Very handy for lateral joins or applying filters per row. | SELECT a.*, b.* FROM TableA a CROSS APPLY ( SELECT TOP 1 * FROM TableB b WHERE b.aId = a.Id ORDER BY b.Date DESC ) b; |
| INTERSECT / EXCEPT | Set operations to find common or difference of two result sets. Cleaner and sometimes more efficient than IN or manual joins. |
SELECT col1 FROM A INTERSECT SELECT col1 FROM B; |
| TRY_CONVERT, TRY_CAST, TRY_PARSE | These safe cast functions return NULL instead of error if conversion fails—helpful in data cleansing or ETL scenarios. |
SELECT TRY_CONVERT(int, SomeVarchar) FROM MyTable; |
| STRING_AGG (… WITHIN GROUP) | Aggregates string values from rows into a delimited string. Very useful for reporting or concatenated lists. | SELECT Dept, STRING_AGG(EmployeeName, ', ') AS Employees FROM Emp GROUP BY Dept; |
JSON functions & OPENJSON |
If your data or payloads include JSON, these functions help parse, query, and transform JSON without manual parsing logic. | SELECT * FROM OPENJSON(@json) WITH (id INT, name NVARCHAR(50)); |
**
Tips & Best Practices When Using These Features**
- Don’t overhint: Hints are powerful but can backfire if data or distribution changes. Use sparingly and test under real workload. -** Enable Query Store early**: It gives you historical insights. Use it to spot plan regressions or regression changes.
- Leverage automatic tuning: Use Azure’s built-in recommendations as a safety net for continuous tuning.
- Batch your operations: Network latency and round trips are expensive in cloud environments.
- Test with representative data: Some features may behave differently under skewed distributions or large datasets.
- Version awareness: Some features (e.g. GREATEST / LEAST) may be newer in Azure SQL and may not exist in older on-prem versions.
- Monitoring & metrics: Always monitor waits, memory grants, tempdb usage, and other diagnostic stats to catch regressions.
Sample Use Cases & Scenarios
Ranking & Top-N per group: Use ROW_NUMBER() OVER partition to get top 3 sales per region.
Concatenating values: Use STRING_AGG(...) to combine multiple string rows into a single comma-separated list.
Comparing columns within a row: Use GREATEST to pick the highest of multiple score columns.
Safe data import: Use TRY_CONVERT / TRY_CAST to gracefully handle malformed inputs.
5.** Dynamic lateral join logic**: Use CROSS APPLY to select the “best matching” sub-row per outer row.
Combining set results: Use INTERSECT / EXCEPT to find overlapping or differing datasets quickly.
Tuning and maintenance: Review advisor recommendations, and selectively implement index suggestions or forced parameterization via automatic tuning.
Conclusion & Next Steps
By incorporating even a few of these “hidden” SQL features into your toolkit, you’ll elevate your productivity, make your queries more expressive, and optimize performance—especially in managed environments like Azure SQL.
Next steps:
- Pick one or two features (e.g. GREATEST, STRING_AGG, CROSS APPLY) and try them in a sandbox environment with your data.
- Use Query Store and performance dashboards to compare before/after metrics.
- Gradually incorporate hints or automatic tuning suggestions in non-critical workloads, and monitor.
- Document which features or syntax your team adopts so others can learn.
Top comments (1)
very useful, this has helped me finding new commands and all in a single window