If you're looking to sharpen your SQL skills and move beyond the basics, you're in the right place. This post covers many practical tips and techniques that will help you write clearer, faster, and more efficient SQL queries. You'll learn how to use window functions effectively, simplify complex operations with pivoting, handle NULLs properly, rank and sort data like a pro, and much more.
Whether you’re preparing data for reports or optimizing your queries for better performance, these insights will help you level up your SQL game.
Understanding Aggregations and Window Functions in SQL
When calculating totals, such as summing salaries by department, SQL offers two common approaches: aggregate functions with GROUP BY
and window functions with OVER (PARTITION BY)
.
- Aggregate function + GROUP BY: This collapses multiple rows into a single row per group. For example,
SUM(salary) GROUP BY department_id
will give you one total salary per department. - Window function + PARTITION BY: Here, you calculate the sum within each partition (e.g., department), but the original rows remain visible. Using
SUM(salary) OVER (PARTITION BY department_id)
adds a rolling total or a running sum as a column, without losing individual employee details.
The key difference is that aggregates summarize and limit output rows to one per group, but window functions keep rows intact, adding extra insight for each row.
Example:
dept_id | employee | salary | SUM(salary) GROUP BY dept_id | SUM(salary) OVER (PARTITION BY dept_id) |
---|---|---|---|---|
1 | John | 50000 | 150000 | 50000 |
1 | Jane | 60000 | 150000 | 60000 |
1 | Mike | 40000 | 150000 | 40000 |
2 | Lisa | 70000 | 125000 | 70000 |
2 | Mark | 55000 | 125000 | 55000 |
Knowing when to use which depends on your goal:
- Use aggregate + GROUP BY when you want summarized results.
- Use window functions when you want calculations alongside original row details, like running totals or rankings.
Window functions often perform well and provide extra analysis flexibility.
Mastering the PIVOT Function in SQL
Sometimes you want to transform rows into columns for better readability, like turning departmental sales data into a table where each department is a column. This is where the PIVOT function shines.
How to Write a PIVOT Query
- Start with your source table or a table expression.
- Use the
PIVOT
keyword. - Inside parentheses, define:
- The aggregation function, like
SUM(salary)
. - The column you want to use for pivoting (e.g.,
department_name
). - The pivoted values in square brackets, like
[HR], [Finance], [IT]
.
- The aggregation function, like
- Give the pivot table an alias after the pivot clause.
Example Syntax:
SELECT *
FROM
(SELECT department_name, salary FROM employees) AS SourceTable
PIVOT
(
SUM(salary)
FOR department_name IN ([HR], [Finance], [IT])
) AS PivotTable;
This query converts department rows to columns with summed salaries for each.
Why Use PIVOT Instead of UNION ALL?
Without PIVOT, you might write long UNION ALL
queries to get the same effect — listing each department’s total one by one. That can be tedious and slow.
Also, for generating quick temporary data, the VALUES
function provides a neater alternative to lengthy UNION ALL
statements.
Use PIVOT for:
- Summarizing data in cross-tab reports.
- Reshaping data from multiple rows into columns for easier analysis.
- Cleaner, maintainable SQL code.
Efficient Data Manipulation: Updating and Swapping Column Values
Swapping values between two columns in a table is a common task but can be tricky if you’re not sure how to write the update.
Swapping Two Columns in One Step
Suppose you want to swap column values ID1
and ID2
for all rows. You can do this with a single update statement:
UPDATE your_table
SET ID1 = ID2,
ID2 = ID1;
This swaps the values in place for every record. Run the update again, and the values swap back.
If your database doesn’t allow this syntax, try alternate methods like using a temporary variable or columns.
Using VALUES Instead of UNION ALL for Temporary Data
Instead of writing tedious multiple UNION ALL
queries to generate test data, the VALUES
clause lets you quickly specify rows:
SELECT *
FROM (VALUES
(1, 'A'),
(2, 'B'),
(3, 'C')
) AS TempTable(ID, Name);
This makes your code cleaner and easier to modify.
String Operations and Handling Nulls in SQL
Splitting Strings
You can use STRING_SPLIT(string, delimiter)
to split a delimited string into rows, but be careful with syntax. The correct way to select values is:
SELECT value
FROM STRING_SPLIT('A,B,C', ',');
Common errors come from incorrect syntax or trying to call it wrongly.
Concatenating Strings with NULLs
String concatenation often produces NULL
if any part is NULL. Instead, use the CONCAT
function which ignores NULLs:
SELECT CONCAT(first_name, last_name) AS full_name FROM employees;
If you want to insert spaces between names and still handle NULLs, use CONCAT_WS
(Concatenate With Separator):
SELECT CONCAT_WS(' ', first_name, last_name) AS full_name FROM employees;
Trimming Unwanted Characters
Use the TRIM
function to clean up strings:
TRIM(LEADING 'x' FROM string)
removes characters from the start.TRIM(TRAILING 'x' FROM string)
removes from the end.TRIM(BOTH 'x' FROM string)
removes from both ends.
This helps clean inputs or remove unwanted whitespace.
Measuring Length Considering Trailing Spaces
LEN
often ignores trailing spaces. To find the true length including trailing spaces, append a unique character and subtract one:
SELECT LEN(col + '|') - 1 FROM your_table;
This trick highlights strings with trailing spaces.
Ranking and Row Number Functions for Ordering Data
SQL provides several ways to rank or number rows based on ordering.
Differences Between RANK, DENSE_RANK, and ROW_NUMBER
- RANK(): Assigns the same rank to tied values but skips ranks afterward. For example, with two employees tied for rank 2, the next gets rank 4.
- DENSE_RANK(): Like RANK but does not skip ranks. The next rank after ties continues immediately.
- ROW_NUMBER(): Assigns a unique sequential number to each row regardless of ties.
Example:
salary | RANK() | DENSE_RANK() | ROW_NUMBER() |
---|---|---|---|
100000 | 1 | 1 | 1 |
90000 | 2 | 2 | 2 |
90000 | 2 | 2 | 3 |
80000 | 4 | 3 | 4 |
Sorting with NULL Values
By default, SQL Server sorts NULLs first in ascending order. If you want NULLs last, use conditional sorting:
ORDER BY CASE WHEN department_name IS NULL THEN 1 ELSE 0 END, department_name;
This moves NULL values to the bottom.
Simplifying Complex Conditional Logic with IIF and CASE
Using IIF for Short Conditional Logic
IIF(condition, true_value, false_value)
offers a compact alternative to CASE
for simple conditions.
Example: Mark employees as managers if manager_id >= 5
:
SELECT employee_id,
IIF(manager_id >= 5, 1, 0) AS is_manager
FROM employees;
CASE Executes Conditions in Order
CASE
checks conditions sequentially and returns once a true condition is found.
If multiple conditions are true, only the first matching one applies.
Example:
SELECT CASE
WHEN salary > 90000 THEN 'High'
WHEN salary > 70000 THEN 'Medium'
ELSE 'Low'
END AS Salary_Level
FROM employees;
Generating and Managing Data Efficiently
Creating Tables Like Existing Ones
Use SELECT INTO
to clone a table’s structure and data:
SELECT *
INTO new_table
FROM original_table;
To create an empty copy, add a WHERE 1 = 2
clause so no rows get selected.
Generating Number and Date Series
GENERATE_SERIES
can produce a range of numbers or dates. For dates, generate numbers and add them as days:
SELECT DATEADD(day, number, '2024-01-01') AS DateValue
FROM GENERATE_SERIES(0, 365);
This handles leap years like 2024 with 366 days.
Inserting Data with Single Quotes
To insert text that contains single quotes, escape them by doubling:
INSERT INTO person(lastname) VALUES ('O''Hara');
Formatting Numbers with Leading Zeros
Use the FORMAT
function to pad integers:
SELECT FORMAT(7, '000') -- Output: 007
SELECT FORMAT(77, '000') -- Output: 077
Rounding Numbers
ROUND(value, decimals)
: Standard rounding.CEILING(value)
: Rounds up.FLOOR(value)
: Rounds down.
Performance and Database Management Tips
Using OUTPUT Clause to Track Changes
When inserting, updating, or deleting rows, OUTPUT
returns affected rows:
INSERT INTO table1 (col1) OUTPUT inserted.* VALUES ('value');
UPDATE table1
SET col1 = 'newvalue'
OUTPUT inserted.col1, deleted.col1
WHERE col1 = 'oldvalue';
This helps audit changes as they happen.
Killing Long-Running Queries
To find and terminate slow queries:
- Use
sp_who2
to list active sessions. - Identify the SPID causing issues.
- Run
KILL <SPID>
to stop it.
Renaming Tables
Use sp_rename
to rename tables:
EXEC sp_rename 'old_table_name', 'new_table_name';
After renaming, queries using the old name will error.
Generating Scripts with Data in SSMS
Right-click on a database, choose Tasks > Generate Scripts. In Advanced options, select Schema and Data to export both table structure and rows.
Adding Check Constraints
To prevent invalid data like end_date < start_date:
ALTER TABLE your_table
ADD CONSTRAINT chk_dates CHECK (end_date >= start_date);
Existing bad rows must be removed before adding the constraint.
Advanced Data Filtering and Searching Techniques
Using LIKE with Wildcards
%
matches any number of characters._
matches exactly one character.
To find names where the second last character is 'A':
WHERE first_name LIKE '%A_'
Character Ranges in LIKE
Search for names starting with 'N' or 'R' quickly:
WHERE first_name LIKE '[NR]%'
Use [^]
to find strings with characters outside specified ranges.
Searching Across Multiple Columns
Instead of multiple OR conditions, use:
WHERE 'Main Street' IN (address1, address2, address3);
This returns customers whose address contains 'Main Street' in any address column.
Useful SQL Functions and Tricks to Know
Using LEAD and LAG Functions
Retrieve next or previous row values with:
LEAD(column, offset, default) OVER (ORDER BY col)
LAG(column, offset, default) OVER (ORDER BY col)
offset
skips multiple rows, default
replaces nulls when no prior or next row exists.
Handling Exponential Numbers
Convert exponential strings to floats before assigning to numeric columns:
CAST(your_exponential_value AS FLOAT)
Simplifying Date Formatting
Use CONVERT
to get year and month without complex string operations:
CONVERT(varchar, your_date, 112) -- Returns YYYYMMDD format
Masking Sensitive Data
Use functions within ALTER TABLE
to mask columns like email for privacy, restricting sensitive info exposure.
Retrieving Table Metadata
Use the system stored procedure:
EXEC sp_help 'table_name';
This returns column info, keys, constraints, and other metadata.
To find tables with a specific column name, query:
SELECT table_schema, table_name
FROM information_schema.columns
WHERE column_name LIKE '%product%';
Handling NULLs and Data Integrity in SQL Queries
NULLs in Filters and Counts
Remember WHERE department_name <> 'HR'
excludes NULL rows because NULL comparisons return unknown (not true).
As a result, counts may be unexpected.
Proper NULL Handling Strategies
Use:
WHERE department_name <> 'HR' OR department_name IS NULL;
Or use COALESCE
to replace NULLs for comparisons.
Ordering with NULLs
SQL Server puts NULLs first by default. Push them to the end with:
ORDER BY
CASE WHEN department_name IS NULL THEN 1 ELSE 0 END,
department_name;
COUNT and NULLs
COUNT(column)
skips NULLs, but COUNT(*)
counts all rows.
Wrapping It Up
These SQL tricks will make your queries cleaner, more efficient, and easier to maintain. From understanding sums to mastering pivot tables, handling NULLs, and writing concise conditional logic, these tips cover a broad range of everyday SQL tasks.