Stored Procedure code shortening and optimization

This stored procedure takes user defined table type as parameter. It contains data imported from an Excel file. Values inside that type are the same as in #InconsistentRestriction temporary table. It's working fine, but since I'm not an SQL expert I'm afraid it's not written the best way possible (or not even close).

Combination of TextRestrictionTypeId, SMSChannelId, NetworkId, GatewayId and CountryId is unique and is used to recognize whether record with that unique key already exists inside dbo.SMSChannelNetworkTextRestrictions table or not. If record with such unique key already exists we're checking for consistency. All values with the same unique key must have the same PassIfMatch (NULL is considered same as FALSE when checking) value otherwise they're considered inconsistent and should not be inserted, but rather returned to user to correct them and reinsert them. Once you find a record with that unique key inside table, you should retrieve it's Id and insert combination of Id and Expression into dbo.TextRestrictionData table.

First, I'm creating a temporary table which contains the same columns as my user defined table type su.TextRestriction_Type. After that I'm joining user defined table type input with dbo.SMSChannelNetworkTextRestrictions table on unique key values and checking where PassIfMatch is not the same for input records and existing record. That way I'm getting the set of inconsistent input records and I'm placing them into temporary table.

Next step is the insertion of non existing unique key into dbo.SMSChannelNetworkTextRestrictions. So I'm joining input user defined table type with dbo.SMSChannelNetworkTextRestrictions and #InconsistentRestrictions using left join together with where condition to remove existing unique keys and unwanted restrictions from initial input user defined table type. Also, these record are grouped by because only one value of unique key combination can be inserted. By doing

Note that the clauses like

ISNULL(tr.SMSChannelId, trl.SMSChannelId) IS NULL

and

ISNULL(tr.PassIfMatch, 0) != ISNULL(trl.PassIfMatch, 0)

are non-SARG-able, and will thus force a table scan. In fact It is possible that your first large JOIN will be run as a single pass through a cross join of the two tables.

The first thing you can do is to recode clauses like (1) above as

(tr.SMSChannelId IS NULL OR trl.SMSChannelId IS NULL)

Ugly, but necessary.

However on those tables over which you have schema-control, such as @TextRestrictionList and #InconsistentRestrictions, you can do better by making some of the nullable columns non-nullable.

NULL strings can often be converted to empty strings without any semantic change to queries.

NULL numbers can often be converted to one of 0, -1, or MinValue without any semantic change to queries.

Null Dates can usually be converted to one of '001-01-01' or '9999-12-31' without any semantic change to queries.

By performing these changes where possible you will both simplify your query syntax and give the engine optimizer better capability to use indices to optimize your query.

StackExchange Code Review Q#100835, answer score: 3