COALESCE vs ISNULL

One of the best interview questions I was ever asked was about the difference between COALESCE vs ISNULL when it comes to a TSQL query. Of course it was one of those questions that was designed to stump the candidate and I believe I only got half of the question correct but it was such a good question it stuck with me (thanks Onur).

Here is the example that was presented to me after my interview.

DECLARE @x as varchar(3) = null, @y as varchar(10) = ‘1234567890’

SELECT COALESCE (@x,@y) as [coalesce],

isnull(@x,@y) as [isnull]

Query Results:

As you can see they both work the same when checking if one value is null then use the second value but as you can also see ISNULL will use the data type and length of the first column even if it is null.

This could cause a lot of data being truncated. You determine if you trust ISNULL.

Just to let you know I still got the job.

Note: This is how I accomplished what I was trying to do. There might be other ways to accomplish the same task and by no means do I think this is the only way to accomplish this task.

SQL Server Get Table Layout

When you start reverse engineering a process or a database it is always nice to get table layouts in a format that you can throw into documentation. Here is a quick script that will give you what you need for documentation.

SELECT
t.name AS table_name
  , SCHEMA_NAME(t.schema_id) AS schema_name
  , c.name AS column_name
  , types.name
  , c.column_id
  , c.max_length
  , c.precision
FROM sys.tables AS t
  INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
  INNER JOIN sys.types types on c.system_type_id = types.system_type_id
WHERE t.name = <Table Name>
ORDER BY schema_name, table_name;

Replace <Table Name> with the table you would like to get the layout for.

In SSMS copy results with headers and drop into Excel.

Note: This is how I accomplished what I was trying to do. There might be other ways to accomplish the same task and by no means do I think this is the only way to accomplish this task.