Why does the number of 'Rows affected' differ between a normal SELECT and a SELECT INTO?

Question

I have a query that looks as follows:

SELECT  
    OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema]
    ,T.[name] AS [Table], AC.[name] AS [Column]
    ,TY.[name] AS [Data Type]
    ,sep.[value] AS [Description]
    ,AC.[max_length] AS [Maximum Field Length]
    ,create_date AS [Date Created]
    ,modify_date AS [Date Modified]
    ,CASE WHEN is_identity = 1 THEN 'Yes' ELSE 'No' END AS [Primary Key]
    ,CASE WHEN AC.[is_nullable] = 1 THEN 'Yes' ELSE 'No' END AS [Allows Nulls]  
--  INTO 
--  tmp_desc
FROM sys.[tables] AS T   
    INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]  
    INNER JOIN sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id] AND AC.[user_type_id] = TY.[user_type_id]   
    left join sys.extended_properties sep on t.object_id = sep.major_id
                                         and ac.column_id = sep.minor_id
                                         and sep.name = 'MS_Description'
WHERE
    T.[is_ms_shipped] = 0

...to list some properties from my tables and fields.

So here is the strange things that happens, when I do the following steps:

1. I run SELECT, with no temp table created yet (I run a drop table just in case): 1018 records returned
2. I run SELECT INTO (uncomment it in the code above): 1028 records affected
3. I comment out the INTO again, don't drop the temp table yet, and SELECT ONLY: 1028 records returned
4. I run 'select * from tmp_desc': 1028 records returned
5. I drop the temp table and run SELECT ONLY again: 1018 records returned

Any ideas creating the temp table would affect the rows returned from the query, if tmp_desc doesn't from part of query?

Answer 1

The data stored in tmp_desc includes the details of itself; obviously when you just run the SELECT (without the INTO) it does not. As a result you get 10 more rows for the INTO after running a pure SELECT, as there are 10 columns in the table tmp_desc.

db<>fiddle

If you want the queries to be "the same", exclude tmp_desc from the query with the INTO:

SELECT S.[name] AS [Schema],
       T.[name] AS [Table],
       AC.[name] AS [Column],
       TY.[name] AS [Data Type],
       sep.[value] AS [Description],
       AC.[max_length] AS [Maximum Field Length],
       create_date AS [Date Created],
       modify_date AS [Date Modified],
       CASE WHEN is_identity = 1 THEN 'Yes' ELSE 'No' END AS [Primary Key],
       CASE WHEN AC.[is_nullable] = 1 THEN 'Yes' ELSE 'No' END AS [Allows Nulls]
INTO tmp_desc
FROM sys.schemas S
     INNER JOIN sys.[tables] T ON S.schema_id = T.schema_id
     INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
     INNER JOIN sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id]
                              AND AC.[user_type_id] = TY.[user_type_id]
     LEFT JOIN sys.extended_properties sep ON T.object_id = sep.major_id
                                          AND AC.column_id = sep.minor_id
                                          AND sep.name = 'MS_Description'
WHERE T.[is_ms_shipped] = 0
  AND NOT(S.[name] = N'dbo' AND T.[name] = 'tmp_desc');