COALESCE vs ISNULL

The COALESCE and ISNULL are functions that can be considered as abbreviates of the CASE expression. These two functions are primarily used to address certain situations when there is a NULL value expected in the input.
The COALESCE function accepts a list of expressions and returns the first that is not NULL. If all input expressions are NULL, then it returns NULL.
The ISNULL function accepts only two expression as inputs and returns the first one that is not NULL.
To see how these 2 functions work, see the demo below:

DECLARE
@x AS VARCHAR(3) = NULL,
@y AS VARCHAR(10) = '1234567890';

SELECT COALESCE(@x, @y) AS [COALESCE], ISNULL(@x, @y) AS [ISNULL];


Here's the output of this code:

COALESCE	ISNULL
------------	--------
1234567890	123

Differences:
- As you can see, the COALESCE returns the value of @y variable as expected. However, you may be wondering why the ISNULL only returns the first 3 numbers of the input. The reason for this is because the ISNULL function uses the first input's type. The first input variable @x (in this case VARCHAR(3)) type will be used. The COALESCE function however uses the data type of the value that has the highest precedence (in this case, VARCHAR(10)).

- Another difference between the COALESCE and ISNULL is that COALESCE is the standard while ISNULL is not. So, aside from having limited functionality (since it only accepts 2 inputs), it is recommended to always use the standard function for code flexibility/portability.

- The result expression for ISNULL and COALESCE are also different in terms of NULLability. Therefore, it makes a difference when these functions are used for example, in computed columns. For example, when you want to copy a table using SELECT INTO and you want to have the target column(s) to be defined as NOT NULL. If the source column is defined as NOT NULL, then both expressions in the SELECT INTO ISNULL(srcCol1, 0) AS targetCol1 and/or COALESCE(srcCol1, 0) AS targetCol1 will define the target column attribute as NOT NULL. However, if the source column is defined to allow NULL. The target column will be also defined as allowing NULL if the COALESCE is used while ISNULL will define the target attribute as NOT NULL.
See example below:


SELECT
ISNULL(custid, -1) AS custid,
COALESCE(shipregion, 'NA') AS shipregion,
freight
INTO MyOrdersTest
FROM Sales.Orders
WHERE shipcountry = N'Switzerland';


After running the code above. Note that the column definition for the shipregion is defined as NULL.

Now let's try running the code where we use ISNULL for the shipregion column...


SELECT
ISNULL(custid, -1) AS custid,
ISNULL(shipregion, 'NA') AS shipregion,
freight
INTO MyOrdersTest2
FROM Sales.Orders
WHERE shipcountry = N'Switzerland';


Note the column definition for the shipregion this time is defined as NOT NULL

For more details about these functions. Check out the Microsoft documentation here: https://msdn.microsoft.com/en-us/library/ms190349.aspx

OFFSET-FETCH Notes

The OFFSET-FETCH clause gives the ability to selectively fetch only a fixed window subset of the entire result set. We can also think about it as being able to paginate through the result set. This feature is very useful for UI developers who are developing a grid type of widget or page that wants to be able to paginate through the records in their grid that are being returned from the database.

The OFFSET clause does not really require the FETCH clause in order to work. However, in T-SQL a FETCH clause requires the OFFSET to be present. Also, an ORDER BY clause is mandatory if you want to use the OFFSET-FETCH clause in your query.

Here are some examples:

USE TSQL2012;
GO


-- This will skip the first 10 rows and return the remaining rows.
SELECT orderid, productid, unitprice, qty, discount
FROM Sales.OrderDetails
ORDER BY orderid DESC
OFFSET 10 ROWS;
GO

-- This will skip the first 10 rows and return only the next 10 rows
SELECT orderid, productid, unitprice, qty, discount
FROM Sales.OrderDetails
ORDER BY orderid DESC
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;
GO

-- Example of dynamic OFFSET-FETCH filtering by passing input parameters.
-- This might be similar to a UI passing in the pagesize and pagenumber so that
-- it can paginate thru the result set. (Server-side pagination).
DECLARE
@pagesize AS BIGINT = 10, @pagenum AS BIGINT = 3;

SELECT orderid, productid, unitprice, qty, discount
FROM Sales.OrderDetails
ORDER BY orderid DESC
OFFSET (@pagenum - 1) * @pagesize ROWS
FETCH NEXT @pagesize ROWS ONLY;

-- Example if you want to filter a number of rows in arbitrary order
SELECT orderid, productid, unitprice, qty, discount
FROM Sales.OrderDetails
ORDER BY (SELECT NULL)
OFFSET 10 ROWS;
GO


Logical Query Processing Order

One of the interesting things that I have learned during my 70-461 review is the Logical Query Processing order. I have been writing 'SELECT' statements for a while but it's only now that I really understood the order in how the statement is actually interpreted. So, a typical SELECT statement goes like this:

SELECT country, YEAR(hiredate) AS yearhired, COUNT(*) AS numemployees
FROM HR.Employees
WHERE hiredate >= '20030101'
GROUP BY country, YEAR(hiredate)
HAVING COUNT(*) > 1
ORDER BY country, yearhired DESC;

Before, I thought the main query clauses were processed in the order they were entered (also known as the "keyed-in order"):
1. SELECT
2. FROM
3. WHERE
4. GROUP BY
5. HAVING
6. ORDER BY

No wonder I used to get the occasional Invalid column name '<insert your bad column name here>'. That's because I didn't understand the logical query processing. The conceptual interpretation order actually goes like this:

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

Whoa! Wait, so the SELECT statement is actually processed as the last (if no ORDER BY clause is present)?? Yes! I actually asked the same thing. Each of the steps above (phases) operates on one or more tables as inputs and the result is a virtual table that becomes that input of the next step. It's almost similar to method chaining. That explains why we get the Invalid column name error if for example, we used an alias defined in the SELECT clause in the WHERE clause. This is because the WHERE clause is actually evaluated before the SELECT clause, so it doesn't know anything about the alias yet.