I believe that this question needs to be better explored. There is no general answer, as it depends on the implementation of each database engine. SQL is declarative. You say what whether and not as get. The as is on the engine. In some cases it is possible to give a hint (hint) to the engine, but not radically change the way it works.
So, what I’m going to show you here are tests I did on SQL Server 2005.
My tests were based on two queries. The first of them is in the question. The second (cross-Join), is in that reply. See below:
Query 1
SELECT
NUMBER,
(SELECT COUNT(*) FROM NUMBERS)
FROM
NUMBERS
Query 2
SELECT
NUMBER,
TOTAL.T
FROM
(SELECT COUNT(*) T FROM NUMBERS) TOTAL,
NUMBERS
Table Numbers
The creation and completion of the Numbers table (999999 records) can be seen below.
CREATE TABLE [dbo].[Numbers](
[Number] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[Number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--1 milhão de registros são adicionados
insert into Numbers(Number)
select top 1000000 row_number() over(order by t1.number) as N
from master..spt_values t1
cross join master..spt_values t2
Hypothesis
The hypothesis is that no SELECT COUNT(*) is made for each record in the case of Query 1.
It is a simple optimization that SQL Server programmers would not pass up. Note that (SELECT COUNT(*) FROM NUMBERS) is completely independent of the query. Its value can be calculated once, stored and only placed in the SQL return (as if it were a constant).
Analyzing
The image below shows the execution plan of Query 1:
This image shows the execution plan of the Query 2:
The only difference is an operator called Computer Scalar. The rest of the operators are exactly equal in the position within the tree and in the values computed/estimated by the SQL Server Planner. The Scalar Computer has an estimated 2% cost for this case.
I went a little further and made an analysis using the SQL Server Profile. See the return:
The most important thing to note here is that the number of Reads (readings) of the two queries is equal. Already the time of Query 1 is a little higher exactly due to CPU consumption (also a little higher). Certainly is Scalar Computer operator shown above.
Still on the profile, I checked which cost to run only the instruction below:
SELECT COUNT(*) FROM NUMBERS
The result can be seen below:
Completion
Given the above, it is possible to realize that the hypothesis raised is correct and that the SQL Server Planner 2005, in the context presented, nay performed a Count operation for each returned line. Quite possibly other Bank Engineers also optimize queries like this one, in order to avoid unnecessary processing.
Note that, even so, the Query 2 has a slightly better performance and may be indicated by it. However, it should be clear that the objective here was not to compare the performance of the two queries, but to show that there is no Count(*) per entry in the SQL of Query 1.
It depends on how Voce uses its subquery. In your example as already exposed in repsosta it only runs once. But if your subquery depends on the value of the record, it runs N times records. Ex:
SELECT Codigo, (SELECT T2.Descricao FROM TABELA2 T2 WHERE T2.Codigo = T1.Codigo_T2) FROM TABELA1 T1
– Roger Barretto
Hello ramaral, I created the test table, only one thing is why are using 2-point-comma? this to finish, see the third option is wrong, the right is to be without the point-of-comma at the end and then the
GROUP
. Triesselect tipo, count(*) as parcial from tabela group by tipo;
– KingRider
@Kingrider You’re right about the point and comma, it’s the most, it was error while typing.
– ramaral