10 T-SQL One-Liners You'll Use Every Day

Every DBA has a mental toolbox of go-to queries. Some took years to learn. Some were stumbled upon by chance while working a 2am outage. Today I am sharing 10 of my favorite T-SQL one-liners — the kind of stuff you copy, paste, and immediately feel like a genius. Some are classics, some are new additions -- All of them are useful.

Bookmark this one. I hope you will come back to it.

1. Generate a Number Sequence on the Fly

Need a quick sequence of numbers without creating a tally table? If you are on SQL Server 2022 or later, GENERATE_SERIES is your new best friend:

SELECT value FROM GENERATE_SERIES(1, 100);

That is it. One line. 100 rows. No temp tables, no CTEs, no cross joins. Similar approach for a date range. Use this to return every day of 2025 in a single line:

SELECT DATEADD(DAY, value, '2025-01-01') AS dt FROM GENERATE_SERIES(0, 364);

Version: SQL Server 2022+ (compatibility level 160+)

2. Comma-Separated List from Rows

The old-school FOR XML PATH / STUFF approach was painful. STRING_AGG changed the game. Use this to list every database on your instance, in one comma-separated string:

SELECT STRING_AGG(name, ', ') AS all_databases FROM sys.databases;

Need them ordered alphabetically? Add WITHIN GROUP:

SELECT STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY name) AS all_databases FROM sys.databases;

If you are still writing STUFF(... FOR XML PATH('')...) in 2022+, it's time to let go.

Version: SQL Server 2017+

3. Running Total Without a Cursor

Cursors had a good run. Actually no, they didn't. Set-Based T-SQL and Windows functions ended their reign:

SELECT OrderDate, Amount, SUM(Amount) OVER (ORDER BY OrderDate) AS RunningTotal FROM dbo.Sales;

Running totals, moving averages, cumulative counts — all without a single cursor or temp table. If you are still looping through rows to calculate a running total, this one line just saved you 40 lines of code.

Version: SQL Server 2012+

4. Delete Duplicates, Keep One

You have duplicates. You want to keep exactly one copy of each. Not one line, but pretty close:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Email ORDER BY Id) AS rn FROM dbo.Customers
)
DELETE FROM cte WHERE rn > 1;

Yes, you can DELETE directly from a CTE. That surprises a lot of people. The PARTITION BY defines what 'duplicate' means, and the ORDER BY decides which row survives. Clean, readable, no temp tables.

Version: SQL Server 2005+

5. Dynamic PIVOT Without Dynamic SQL

A lot of people think PIVOT requires dynamic SQL to handle unknown column values. Not true. If you know your categories, you can pivot in one line:

SELECT * FROM (SELECT MONTH(OrderDate) AS Month, Category, Amount FROM dbo.Sales) s
PIVOT (SUM(Amount) FOR Category IN ([Hardware],[Software],[Services])) p;

Rows become columns instantly. Monthly sales by category, all in one shot. The inner query sets up the data, PIVOT does the magic. No CASE WHEN statements, no multiple SUM() calls, no headaches.

Version: SQL Server 2005+

6. Generate Millions of Test Rows Instantly

Need a million rows of data to stress test? Stop writing loops or importing CSV files. Cross join the system tables with themselves:

SELECT TOP (1000000) 
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID,
    'User' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR) AS UserName,
    LEFT(NEWID(), 8) + '@test.com' AS Email
FROM sys.all_objects a, sys.all_objects b, sys.all_objects c;

Cross joining system tables creates a Cartesian product. sys.all_objects usually has a few thousand rows, so three cross joins give you billions of combinations. SQL Server generates realistic test data faster than you can blink. Change the TOP number to whatever you need.

Version: All versions

7. Unpivot Columns to Rows Without UNPIVOT

The UNPIVOT operator works, but CROSS APPLY with VALUES is more flexible and honestly easier to read:

SELECT e.EmployeeId, v.Score, v.Category
FROM Employees e
CROSS APPLY (VALUES 
    (Q1Score, 'Q1'), (Q2Score, 'Q2'), (Q3Score, 'Q3'), (Q4Score, 'Q4')
) v(Score, Category);

Four columns become four rows per employee. Need to add Q5 someday? Just add another line in the VALUES. Try doing that with UNPIVOT without rewriting the whole thing and losing some hair.

Version: SQL Server 2008+

8. Conditional Aggregation in One Line

Stop writing multiple count queries in one call. Here you've got one pass through the table with three answers:

SELECT 
    COUNT(CASE WHEN Status = 'Active' THEN 1 END)   AS ActiveCount,
    COUNT(CASE WHEN Status = 'Inactive' THEN 1 END) AS InactiveCount,
    COUNT(CASE WHEN Status = 'Pending' THEN 1 END)  AS PendingCount
FROM Customers;

If you prefer something shorter, SQL Server 2012+ gives you IIF:

SELECT SUM(IIF(Status = 'Active', 1, 0)) AS ActiveCount FROM Customers;

Version: CASE — all versions. IIF — SQL Server 2012+

9. Get the Nth Highest (or Lowest) Value

Interview question classic. What is the 3rd highest salary?

SELECT DISTINCT Salary FROM Employees ORDER BY Salary DESC OFFSET 2 ROWS FETCH NEXT 1 ROW ONLY;

The key is DISTINCT - without it, you get whoever happens to be in the 3rd row, not the 3rd highest unique value. Change the 2 to N-1 for whatever position you need. OFFSET 0 gives you the highest, OFFSET 1 gives you the second highest, and so on.

Version: SQL Server 2012+

10. Find Every Table's Row Count — Instantly

If you are still running SELECT COUNT(*) FROM every table, stop. This reads metadata and returns in milliseconds, even on tables with billions of rows:

SELECT s.name AS SchemaName, t.name AS TableName, p.rows AS [RowCount]
FROM sys.tables t JOIN sys.schemas s 
  ON t.schema_id = s.schema_id JOIN sys.partitions p 
    ON t.object_id = p.object_id
WHERE p.index_id IN (0, 1)
ORDER BY p.rows DESC;

Every table with row count, sorted top down. No waiting. This reads metadata, not the actual tables, so it's lightning fast even on tables with billions of rows. The count is exact for stable tables, but may lag during active transactions or immediately after bulk operations. For quick estimates and general monitoring, it's perfect. When you need the exact count during heavy activity, fall back to COUNT(*).

Version: All versions

Bonus: Check Disk Space on All Drives

I cannot count how many times I've run this. Hell. I even proceduralized it:

SELECT DISTINCT volume_mount_point, 
    total_bytes/1024/1024/1024 AS TotalGB, 
    available_bytes/1024/1024/1024 AS FreeGB 
FROM sys.master_files mf 
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id);

Shows total size and free space on every drive that has SQL Server files. No need to RDP to the server or dig through Windows - just paste this and know instantly if you're about to run out of space.

Version: SQL Server 2008+

In closing, none of these are earth-shattering, but they are the kind of queries you will come back to again and again. Try them out. I hope they help.

More to Read:

GENERATE_SERIES (Transact-SQL) — Microsoft Learn
STRING_AGG (Transact-SQL) — Microsoft Learn
TSQL Tips and Tricks — MSSQLTips
OFFSET FETCH (Transact-SQL) — Microsoft Learn