ISNULL vs COALESCE

Feature	ISNULL	COALESCE
Origin	T-SQL Specific (Microsoft)	ANSI SQL Standard (Universal)
Parameters	Accepts only 2 arguments.	Accepts 2 or more arguments.
Data Type	Uses the type of the first argument.	Uses the type with the highest precedence.
Performance	Slightly faster for simple 1-to-1 checks.	Can be slightly slower in complex subqueries.
Evaluation	Evaluates the first argument once.	May evaluate the expression multiple times.

Key Differences Explained

1. Number of Arguments

• ISNULL: Limited to checking one value and providing one replacement.

                SELECT ISNULL(Price, 0) FROM Products;

• COALESCE: Can check multiple columns/values in order and returns the first non-null one.

                SELECT COALESCE(Mobile, HomePhone, OfficePhone, 'N/A') FROM Contacts;

2. Data Type Handling

This is often where developers run into unexpected results or errors.

• ISNULL is rigid. It looks at the data type of the first expression and tries to convert the replacement to match it. If the replacement is longer than the first argument, it may even truncate the data.
• COALESCE follows "Data Type Precedence." It looks at all arguments and converts everything to the type that ranks highest.

3. Performance in SQL Server

In most standard queries, the performance difference is negligible. However:

• ISNULL is built directly into the SQL Server engine as a function.
• COALESCE is actually translated by the engine into a CASE statement.
• Best Practice: Use ISNULL for high-frequency, simple null-to-zero conversions where performance is critical. Use COALESCE for multi-column logic or when writing code that needs to be portable to other databases like PostgreSQL or MySQL.

Note :

Use ISNULL when you are sure you only have two values and want to force a specific data type. Use COALESCE when you need flexibility, multiple fallbacks, or are following ANSI standards

SYS.SQL_MODULES in T-SQL (Catalog View)

The sys.sql_modules system view is a powerful tool for returning object details within a SQL Server database. It allows developers to easily retrieve the definition and metadata for various objects, including Stored Procedures, Replication-filter procedures, Views, and DML Triggers. Additionally, it covers SQL Scalar Functions, Inline Table-valued Functions, Table-valued Functions, and Rules. Using this view is an essential way to audit or search your codebase programmatically.

Columns

object_id
definition
uses_ansi_nulls
uses_quoted_identifier
is_schema_bound
uses_database_collation
is_recompiled
null_on_null_input
execute_as_principal_id

SELECT  object_id, definition, uses_ansi_nulls, uses_quoted_identifier,
                 is_schema_bound,uses_database_collation, is_recompiled,null_on_null_input,                                      execute_as_principal_id
 FROM   sys.sql_modules

UNION and UNION ALL in T-SQL

UNION

The UNION operator is used to combine the result sets of two or more SELECT statements. To use it successfully, you must follow three key rules:

• Column Consistency: Each SELECT statement must have the same number of columns.

• Data Compatibility: The corresponding columns in each statement must have similar or compatible data types.

• Distinct Results: By default, UNION removes duplicate records and returns only distinct values.

UNION ALL

The UNION ALL operator works similarly to UNION, but with one major difference: it retrieves all rows from the selected tables without filtering for duplicates. Because it doesn't spend resources searching for and removing identical records, UNION ALL is significantly faster and more efficient when you know your data is unique or when you specifically want to include every result.  

Use UNION ALL by default if your data is already unique or if you don't mind including duplicate records. Because UNION must perform extra work to ensure only distinct values are returned, it is slower than UNION ALL. Only reach for the standard UNION when you specifically need to filter out overlapping data between your query results.