Nested SELECT statements interact in a way that I don't understand

Question

It seemed to me that I understood how I can make SELECT from the results of another SELECT , but there seems to be some kind of blurring of the scope that I don't understand. I am using SQL Server 2008R2.

The easiest way to explain is with an example.

Create a table with one nvarchar column - load a table with one text value and several numbers:

 CREATE TABLE #temptable( a nvarchar(30) ); INSERT INTO #temptable( a ) VALUES('apple'); INSERT INTO #temptable( a ) VALUES(1); INSERT INTO #temptable( a ) VALUES(2); select * from #temptable; 

This will return: apple, 1, 2

Use IsNumeric to get only table rows that can be added to numeric ones - this will leave the apple text value behind. It works great.

  select cast(a as int) as NumA from #temptable where IsNumeric(a) = 1 ; 

This returns: 1, 2

However, if I use the same query as the internal one and try to make a numerical WHERE , it cannot say that it cannot convert the nvarchar "apple" value to an int data type. How did he get the meaning of "apple" back?

  select x.NumA from ( select cast(a as int) as NumA from #temptable where IsNumeric(a) = 1 ) x where x.NumA > 1 ; 

Note that a failed request works just fine without a WHERE :

  select x.NumA from ( select cast(a as int) as NumA from #temptable where IsNumeric(a) = 1 ) x ; 

I find it very awesome. What will I not get? TIA

Answer 1

If you look at the estimated execution plan, you will find that it optimized the internal query in the external one and combined the WHERE clauses.

Using CTE to isolate operations (in SQL Server 2008 R2):

 declare @temptable as table ( a nvarchar(30) ); INSERT INTO @temptable( a ) VALUES ('apple'), ('1'), ('2'); with Numbers as ( select cast(a as int) as NumA from @temptable where IsNumeric(a) = 1 ) select * from Numbers 

Answer 2

The reason you get this is fair and simple. When the query is executed, some steps are performed. These are analysis, algebraization, optimization and compilation. Part of the algebra in this case will receive all the objects necessary for this request. Optimization will use these objects to create a better query plan, which will be compiled and executed ...

So, when you look at this part, you will see that it will scan the table for #temptable. And #temptable is defined as a way to create your table. The fact that you do some calculation on this is another matter. The column still has the nvarchar data type.

To find out how this works, you need to know how to read the request. First, all objects are extracted (from the table, the internal connection table), then the predicates (where, on), then the grouping, etc., Then the choice of columns (with cast), and then the order.

Thus, keeping in mind that you have a combination of choices, the optimizer will still handle it that way. Since your choice is subordinate to the party and joins parts of your request, this will cause this error.

Hope I made it a little clear?

Answer 3

The optimizer can freely move the expressions in the query plan to get the most economical plan for data extraction (the order of predicate estimation is not guaranteed). I think using a case expression like bellow creates NULL in the absence of an ELSE clause and thus accepts APPLE out

select a from #temptable, where case isnumeric (a) = 1, then end> ​​1