Need a SARGABLE way to filter records, and also specify a default value for NULL

Question

I am creating an adapter for user data from a client. I cannot change their schema or change the values ​​in my tables, although I can suggest new indexes. The approach is to use CTE to append and reformat user data to use our column names, enumerated values, etc. After the data is reformatted, our standard CTEs can be added and a query deduced from it, which can perform our standard analyzes.

Some of the values ​​that result from reformatting are NULL due to LEFT JOINs that do not match, or due to values ​​in their data that are actually NULL.

My task is to replace the default values ​​for NULL in many fields, and also allow the inclusion of WHERE clauses in the query. Currently, ISNULL calls or CASE statements are used to handle default values. And now, by the time the WHERE clause expires, this substitution has already been completed, so that the end user with access to our query builder can filter the value, which can be the default value. If the filter value is the default value, select entries with NULL values ​​that have been replaced with the default value.

The problem is that if I have myField = ISNULL (myField, 'MyDefault') as my reformatting formula, and later there is WHERE myField = 'MyDefault' in the outer onion layer (later CTE), then this is where the sentence is not amenable to comparison: the query optimizer does not select my index on myField.

The partial solution that arises for me is to not do NULL replacements in my internal CTEs, and then have a CTE that introduces WHERE clauses, and then have an external CTE that does all the NULL replacements. Such a query may use indexes. (I checked this.) However, where clauses could no longer expect that checking the value against the default would also raise records with NULL values, since this substitution had not yet occurred.

Is there a way to do a null replacement, allow SARGABLE where the filters are, and filter the NULL values ​​as if they were keeping the default value?

NOTE on the size of the problem. A typical example includes the CONNECTION of a 6 mm record table into a 7 mm record table with a many-to-many relationship that creates 12 million records. When the filter is SARGABLE, the query takes about 10 seconds. When it is not SARGABLE, it takes more than 10 minutes on one machine and more than three minutes on a faster machine.

DECISION COMMENT COMMENT:

The smart use of intersection, which allows you to compare a field with NULL or non-NULL without ISNULL or other functions that cannot be processed, can be used in our code with minimal changes in our previous requests.

COMMENT 2: Missing case

These are six cases:

• The selected value is not null and does not equal the default value and does not match the filter value. It should be excluded.
• The selected value is not null and not equal to the default value and corresponds to the filter value. Must include.
• The selected value is not null and equal to the default value and does not match the filter value. It should be excluded.
• The selected value is not null and equal to the default value and corresponds to the filter value. Must include.
• The selected value is null, and the filter value is not the default value. It should be excluded.
• The selected value is null and the default filter value. Must include.

Case 4 does not work using the proposed solution. The selected field is not null, so the first half of the intersection has a record with an anonymous value. But in the second half of the intersection, the NULLIF operator created a record with a null value. Intersection creates null records. Record rejected. I'm still looking for a solution that handles this case. So close...

Update solution:

I have a fix. Let's say that I smell [County name], and my default value is "Unknown" ...

where EXISTS ( select [County Name] intersect (select NULLIF('User selected county name', 'Unknown') union select 'User selected county name') ) 

Answer 1

It looks like you are already building your query dynamically, so when you get the value from your tool that needs to be filtered, you can build a query with a where clause that looks something like this.

SQL Fiddle

Setting up the MS SQL Server 2008 schema :

 create table YourTable ( ID int identity primary key, Name varchar(20) ) create index IX_YourTable_Name on YourTable(Name) insert into YourTable values ('Name1'), ('Name2'), (null) 

Request 1 :

 declare @Param varchar(20) set @Param = 'DefaultName' select ID, coalesce(Name, 'DefaultName') as Name from YourTable where exists(select Name intersect select nullif(@Param, 'DefaultName')) 

Results :

 | ID | NAME | -------------------- | 3 | DefaultName | 

Request 2 :

 declare @Param varchar(20) set @Param = 'Name1' select ID, coalesce(Name, 'DefaultName') as Name from YourTable where exists(select Name intersect select nullif(@Param, 'DefaultName')) 

Results :

 | ID | NAME | -------------- | 1 | Name1 | 

The query plan for the above query will use the expression_name for search.

Reference: Undocumented Query Plans: Comparison Comparisons

Answer 2

You said you cannot change the circuit, but I think outside the box here. You can add a new database that has views that look at the existing database. For instance:

 use NewViewDb GO CREATE VIEW dbo.[T1T2View] AS SELECT field1, field2, COALESCE(field3, 'default value'), ... FROM RealDb.dbo.Table1 t1 LEFT JOIN RealDb.dbo.Table2 t2 ON t1.Id = t2.Id GO 

Answer 3

If you use temporary tables instead of CTEs, you can fill in the data and then index it.

Answer 4

So, ultimately, the problem does not use ISNULL (or COALESCE ) when emitting data, but when filtering. The problem is that you usually cannot use the index when using OR clauses in a predicate, and this is pretty much what these statements are.

Ultimately, the solution is to use dynamic sql if you need to do this inside the SQL server, or just build the final query if you are outside the sql server. If you do this from a stored procedure, you simply make a sql query, and then call sp_executesql in the generated query.

The key points that you will do here is that you will check the value you want to filter (for example, "My default value"), and if this value, then you will either add a predicate to filter by value, or you add a predicate to limit IS NULL

If you need more information on how this will be done, I can provide a sample request.