Inner join with greater or equal VS where condition using a variable Suppose I have a table with a unique column and this columns has only integer numbers.

Eg: ID column with rows from 1 to 999.

My intention is to return only rows when my ID is greater than 500, for example.

Using T-SQL, I have several stored procedures that create a variable and make this cut like this:

DECLARE @VAR AS INT

SET @VAR = 500    

SELECT * FROM sample WHERE ID &gt; @VAR


But for debugging purposes I want to use a temporary table to make this validation throughout the whole stored procedure without having to re-declare or reset my @VAR variable:

CREATE TABLE #cut ( ID int )

INSERT INTO #cut VALUES (500)

SELECT * FROM sample A INNER JOIN #CUT B ON A.ID &gt; B.ID


Is there any performance penalty using this approach on SQL Server 2017?

An extra information is that I don't have sysadmin role on this database, therefore there is no chance using the native debugger.
Yes there will be a (marginal) impact on performance, but if it's for debugging purposes I don't think you'll be able to tell the difference.

You can easily test this yourself on your data by adding statistics IO and looking at the query plans

Take this code for example:

SET STATISTICS time ON;
SET STATISTICS IO ON;

WITH randowvalues
    AS(
       SELECT 1 id, CAST(RAND(CHECKSUM(NEWID()))*100 AS int) randomnumber
        UNION  ALL
        SELECT id + 1, CAST(RAND(CHECKSUM(NEWID()))*100 AS int)  randomnumber
        FROM randowvalues
        WHERE 
          id &lt; 1000
      )



SELECT *
INTO sample 
FROM randowvalues
OPTION(MAXRECURSION 0)

GO

DECLARE @VAR AS INT

SET @VAR = 500    

SELECT * FROM sample WHERE ID &gt; @VAR

CREATE TABLE #cut ( ID int )

INSERT INTO #cut VALUES (500)

SELECT * FROM sample A INNER JOIN #CUT B ON A.ID &gt; B.ID


You will notice that in the execution plan that the first query does a simple table scan for an estimated cost of 0.005.

For the second construction you'll see that the plan for the insert has an estimated cost of around 0.01, and the query itself added an extra table scan (for your temporary table) and a nested loops join to combine data from both tables.
The estimated cost for this select is also around 0.01.

The output for the IO statistics for the first select is: 


  Table 'sample'. Scan count 1, logical reads 3, physical reads 0,
  read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
  read-ahead reads 0.


So this query does 3 reads.

The output for the insert and the select with the join is this:


  Table '#cut'. Scan count 0, logical reads 1, physical reads 0,
  read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
  read-ahead reads 0.  
  
  Table 'sample'. Scan count 1, logical reads 3, physical reads 0,
  read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
  read-ahead reads 0. Table '#cut'. Scan count 1, logical reads 1,
  physical reads 0, read-ahead reads 0, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0.


So if you add that up the second construct with the join does a bit more IO.

Creating a clustered index on the ID column replaces the table scan with a clustered index seek as can be seen in this plan or creating a nonclustered index on ID results in this plan.

Even though the impact in your situation can vary depending on your actual data and table structure, your construction with a temporary table will always add some IO, a second table access and a join operator which will impact performance negatively (maybe marginally).