Hidden Gems in Azure SQL: Useful Commands, Functions & Keywords You Should Know

Sanjay Bhujbal — Tue, 14 Oct 2025 16:18:52 +0000

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.

Azure SQL’s latest features

Sanjay Bhujbal — Tue, 14 Oct 2025 16:05:54 +0000

Free Platforms to Publish SQL / Technical Blogs

If you want to share your SQL or technical knowledge with a wide audience, there are several free platforms designed specifically for developers and professionals. Below is a guide comparing the most popular and effective options.
1. Medium — https://medium.com
✅ Pros:

Popular among developers and tech enthusiasts.
Easy-to-use editor with professional formatting.
Great discoverability through tags like #Azure, #SQLServer, #Database.
Join publications such as 'Towards Data Science' or 'The Startup' to expand reach.

⚠️ Cons:

Limited customization without a paid plan.
Paywall may restrict free readers.

💡 Tip: Submit your blog to a publication for greater visibility.
2. Dev.to (DEV Community) — https://dev.to
✅ Pros:

100% free and built for developers.
SEO-friendly and visible in Google quickly.
Markdown editor and strong community interaction.

⚠️ Cons:

Minimal visual customization.

💡 Tip: Use tags like #azure, #sql, #dotnet, #cloud, and #database.
3. Hashnode — https://hashnode.com
✅ Pros:

Free personal domain (yourname.hashnode.dev).
Great for long-term blogging and portfolio building.
Markdown editor with rich embeds and GitHub integration.

⚠️ Cons:

Slightly smaller audience than Medium, but growing fast.

💡 Tip: Ideal for creating a professional personal tech site.
4. Substack — https://substack.com
✅ Pros:

Clean design with built-in email newsletter.
Readers can subscribe for updates.

⚠️ Cons:

Not developer-specific.
Limited code formatting.

💡 Tip: Great for tech newsletters like 'Azure SQL Weekly'.
5. GitHub Pages — https://pages.github.com/
✅ Pros:

100% free hosting for static sites.
Full design control using Jekyll, Hugo, or Next.js.
Great for tech-savvy users.

⚠️ Cons:

Manual setup and no built-in community.

💡 Tip: Ideal for creating a portfolio-style blog (e.g., sanjaybhujbal.dev).
6. LinkedIn Articles — https://linkedin.com
✅ Pros:

Professional audience and easy publishing.
Auto-notifies your network when you post.

⚠️ Cons:

Limited formatting and no syntax highlighting.

💡 Tip: Share summaries or highlights linking back to your main blog.
Recommended Approach
🥇 Use Hashnode as your main platform (e.g., sanjaybhujbal.hashnode.dev).
🥈 Cross-post on Dev.to and share links on LinkedIn for visibility.
This combination provides both branding and community reach.
Example Setup Plan

Create a Hashnode account and personalize your blog.
Write your first article: 'Latest Features in Azure SQL (2025 Edition)'.
Cross-post on Dev.to using canonical links.
Share links on LinkedIn and Azure/SQL communities.

DEV Community: Sanjay Bhujbal

Hidden Gems in Azure SQL: Useful Commands, Functions & Keywords You Should Know

Azure SQL’s latest features

Free Platforms to Publish SQL / Technical Blogs