Is this way is optimized to retrieve values which are not exist in database?

Question

To retrieve values which are not in db, I use Common Table Expression like this :

;WITH new_id (id) AS (
  SELECT '0x01'
  UNION ALL SELECT '0x00'
  UNION ALL SELECT '0x03'
  UNION ALL SELECT '0x04'
  UNION ALL SELECT '0x05'
)

SELECT  id AS p
FROM   new_id ni EXCEPT(SELECT id FROM table)

Is this an optimized way to retrieve values which are not stored in DB ?

Kind regards,

Florian

Why not use an in memory table (@table_vaiable) instead of a CTE? — Kane, Jan 26 '12 at 09:57
How many rows do you tend to have in the CTE? @Kane `@table_variables` are not in memory tables (or they might be but no more than `#temp` tables are) — Martin Smith, Jan 26 '12 at 09:57

score 3 · Accepted Answer · edited May 23 '17 at 12:30

That should be fine.

For a large number of rows (especially if they contain long string values) I have noticed that these internal tables of constants can take a long time to compile but I doubt you will hit this issue for 30 rows.

From a quick test this end they have an advantage over table variables as SQL Server seems to maintain some statistical information about them so can choose an appropriate join type.

I just tried a CTE with 500 rows and 50 distinct values and the estimated number of rows in the plan below was correct. (though it is a bit of a shame that having worked all this stuff out at compile time and presumably having generated a distinct list of ids for that purpose that SQL Server doesn't just substitute that into the plan instead of having to do it again at run time)

Plan

Is this way is optimized to retrieve values which are not exist in database?

1 Answers1