If you are working with Microsoft SQL Server (MSSQL), you will realize that while it follows standard ANSI SQL, it uses its own extension language called Transact-SQL (T-SQL). T-SQL introduces unique functions and behaviors that developers use to write commonly used sql queries mssql server applications.

In this guide, we will cover the most essential, commonly used sql queries mssql server developers write every day, including string formatting, date calculations, paging patterns, and core built-in functions.

1. String Concatenation in SQL Server (+ vs. CONCAT)

In Microsoft SQL Server, there are two primary ways to join multiple strings together: using the + operator or the built-in CONCAT() function. However, they handle NULL values very differently.

Using the ‘+’ Operator (Caveat: NULL values)

If you use the + operator and any of the columns or variables contain a NULL value, the entire concatenation result will evaluate to NULL:

// Returns NULL if middle_name is NULL
SELECT first_name + ' ' + middle_name + ' ' + last_name AS full_name 
FROM employees;

Using the CONCAT() Function (Recommended)

The CONCAT() function automatically converts NULL values into empty strings, making it much safer for optional columns:

// Returns a valid string even if middle_name is NULL
SELECT CONCAT(first_name, ' ', middle_name, ' ', last_name) AS full_name 
FROM employees;

2. Limiting Results and Pagination (TOP vs. OFFSET FETCH)

Unlike MySQL which uses LIMIT, SQL Server uses the TOP clause or the OFFSET FETCH statement to restrict the number of rows returned by a query.

Using SELECT TOP

The TOP clause is the traditional MSSQL way to limit rows from the beginning of the result set:

// Fetch only the top 10 highest-paid employees
SELECT TOP 10 employee_id, salary 
FROM employees 
ORDER BY salary DESC;

Using OFFSET FETCH for Pagination

For modern web application API paging, the OFFSET FETCH clause is preferred because it conforms to ANSI SQL standards and allows you to skip a specific number of rows:

// Skip the first 10 rows and fetch the next 10 rows (Page 2)
SELECT employee_id, first_name 
FROM employees 
ORDER BY employee_id 
OFFSET 10 ROWS 
FETCH NEXT 10 ROWS ONLY;

3. Date and Time Calculations (GETDATE, DATEADD, DATEDIFF)

SQL Server provides powerful date/time functions to fetch the current system timestamp, add intervals, or calculate the duration between two dates:

// 1. Get current date and time
SELECT GETDATE() AS current_system_time;

// 2. Add 7 days to the current date
SELECT DATEADD(day, 7, GETDATE()) AS date_next_week;

// 3. Calculate difference in years (e.g. Employee Age)
SELECT first_name, DATEDIFF(year, birth_date, GETDATE()) AS age 
FROM employees;

4. String Helper Functions (LEN & CHARINDEX)

To measure the length of a string or locate a substring inside a text block in SQL Server, use LEN() and CHARINDEX():

// Find string length (ignores trailing spaces)
SELECT LEN(username) AS char_count FROM users;

// Locate position of character '@' in email (1-based index, returns 0 if not found)
SELECT email, CHARINDEX('@', email) AS at_symbol_position 
FROM users;

ANSI SQL vs. MSSQL Server Equivalence

If you are transitioning from another database engine to SQL Server, keep this quick reference table of standard functions handy:

Summary

By using these standard T-SQL patterns, you can write clean, high-performance queries that integrate perfectly with Microsoft SQL Server. If you want to review general query practices such as aggregations, subqueries, or basic commands, read our guide on Commonly Used SQL Queries (Part 2). You can also refer to the official Microsoft SQL Server CONCAT Documentation for more technical details.