Sql-server – Why does sql server need to convert count(*) result into int before comparing it with an int variable

Your column is of type CHAR(6). Your filter is of type VARCHAR (perhaps counter intuitive, but that what the constant literal '200706' is). The rules of Data Type Precedence dictate that the comparison occurs using the type with higher precedence, ie. the VARCHAR type. This is a different type than the partitioning function, therefore you do not get partition elimination.

Try this instead:

select count(*)
from T (nolock)
where Month = CAST('200706' as CHAR(6));

Your plan should get a partitioning elimination filter.

Scalar functions are called once-per-row, when called as part of a query.

Consider the following example.

1. Create a new, blank database for our tests:

   USE master;
   IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'mv')
   BEGIN
       ALTER DATABASE mv SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
       DROP DATABASE mv;
   END
   GO
   CREATE DATABASE mv;
   GO
   

2. Create a table, a multi-statement function, and a table-valued-function:

   USE mv;
   GO
   CREATE TABLE dbo.t
   (
       t_id int NOT NULL
           CONSTRAINT PK_t
           PRIMARY KEY CLUSTERED
   );
   GO
   
   CREATE FUNCTION dbo.t_func
   (
       @t_id int
   )
   RETURNS bit
   WITH SCHEMABINDING
   AS
   BEGIN
       DECLARE @r bit;
       IF EXISTS (SELECT 1 FROM dbo.t WHERE t.t_id = @t_id)
           SET @r = 1
       ELSE
           SET @r = 0;
       RETURN @r;
   END
   GO
   
   CREATE FUNCTION dbo.t_tvf
   (
       @min_t_id int
       , @max_t_id int
   )
   RETURNS TABLE 
   WITH SCHEMABINDING
   AS
   RETURN (
       SELECT t_id = t.t_id
           , e = dbo.t_func(dbo.t.t_id)
       FROM dbo.t
       WHERE t.t_id >= @min_t_id
           AND t.t_id <= @max_t_id
   );
   GO
   

3. Insert some sample data into the table:

   INSERT INTO dbo.t (t_id)
   SELECT ROW_NUMBER() OVER (ORDER BY c.id, c.colid)
   FROM sys.syscolumns c;
   GO
   

4. Create a table to store function execution stats, and populate it with a start-row showing execution counts for the multi-statement-function, t_func:

   CREATE TABLE dbo.function_stats
   (
       run_num int NOT NULL
       , object_name sysname NOT NULL
       , execution_count int NULL 
       , CONSTRAINT PK_function_stats
           PRIMARY KEY CLUSTERED (run_num, object_name)
   );
   GO
   INSERT INTO dbo.function_stats (run_num, object_name, execution_count)
   SELECT 1
       , o.name
       , COALESCE(fs.execution_count, 0)
   FROM sys.objects o 
       LEFT JOIN sys.dm_exec_function_stats fs ON fs.object_id = o.object_id
   WHERE o.name = 't_func';
   GO
   

5. Run a query against the TVF:

   SELECT t.*
   FROM dbo.t_tvf(1, 2) t;
   GO
   

6. Capture the execution stats now:

   INSERT INTO dbo.function_stats (run_num, object_name, execution_count)
   SELECT 2
       , o.name
       , COALESCE(fs.execution_count, 0)
   FROM sys.objects o 
       LEFT JOIN sys.dm_exec_function_stats fs ON fs.object_id = o.object_id
   WHERE o.name = 't_func';
   

7. The function stats results:

   SELECT *
   FROM dbo.function_stats fs
   ORDER BY fs.run_num
       , fs.object_name;
   

╔═════════╦═════════════╦═════════════════╗
║ run_num ║ object_name ║ execution_count ║
╠═════════╬═════════════╬═════════════════╣
║       1 ║ t_func      ║               0 ║
║       2 ║ t_func      ║               2 ║
╚═════════╩═════════════╩═════════════════╝

As you can see, the multi-statement-function has execute twice, once per row for the source table accessed by the TVF.

I expect the mutli-statement-function is being called many, many times by the TVF, giving the impression that it is running slowly, whereas in fact it is simply being called many times.