I have a query that is both prone to parameter sensitivity and is suffering from its table-valued parameter. I'm lazy and just want to solve this with query hints. When I'm lazy, I can solve parameter sensitivity with OPTION (OPTIMIZE FOR UNKNOWN). This gives me three very tightly related questions: 1. Does OPTION (OPTIMIZE FOR UNKNOWN) have any impact on table-valued **variables**? 2. Does OPTION (OPTIMIZE FOR UNKNOWN) have any impact on table-valued **parameters**? 3. What query hints other than OPTION(RECOMPILE) can be used to influence cardinality estimates for table-valued **parameters**? Both my own experiments and the documentation suggest that this is a lost cause. Table-valued parameters are subject to parameter sniffing, but all that gets sniffed is their cardinality so OPTION (OPTIMIZE FOR UNKNOWN) ought to do nothing. As for the question of using other table hints, I know that I can change the estimate for operations on the table-valued parameter (e.g. OPTION (USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES')) but I do not think that hints can influence the cardinality estimate for the parameter itself.

With the database setup

CREATE TYPE dbo.TableType AS TABLE (
prop1 int, 
prop2 datetime2, 
prop3 varchar(1000)
);


GO

CREATE OR ALTER PROC dbo.TestTableTypePerf
@Data dbo.TableType READONLY
AS
SELECT COUNT(*)
FROM @Data;

The following code passes the TVP values in a streaming fashion. The enumerable isn't evaluated until after the ExecuteScalarAsync call and there is no need for the whole 5,000,000 elements to be materialized into a collection in the client. It is becoming increasingly popular to eschew TVPs in favour of JSON strings, can anything similar be done for JSON?

-csharp
using System.Data;
using Microsoft.Data.SqlClient;
using Microsoft.Data.SqlClient.Server;

const string connectionString =
    @"...";

await TvpTest();

return;

static async Task TvpTest()
{
    await using var conn = new SqlConnection(connectionString);
    await conn.OpenAsync();
    await using var cmd = new SqlCommand("dbo.TestTableTypePerf", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add(new SqlParameter
    {
        ParameterName = "@Data",
        SqlDbType = SqlDbType.Structured,
        TypeName = "dbo.TableType",
        Value = GetEnumerableOfRandomSqlDataRecords(5_000_000)
    });

    Console.WriteLine($"calling ExecuteScalarAsync at {DateTime.Now:O}");
    var result = await cmd.ExecuteScalarAsync();

    Console.WriteLine($"writing result at {DateTime.Now:O}");
    Console.WriteLine(result);
}

static IEnumerable GetEnumerableOfRandomSqlDataRecords(uint length)
{
    SqlMetaData[] metaData =
    [
        new SqlMetaData("prop1", SqlDbType.Int),
        new SqlMetaData("prop2", SqlDbType.DateTime2),
        new SqlMetaData("prop3", SqlDbType.VarChar, 1000)
    ];

    foreach (var dto in GetEnumerableOfRandomDto(length))
    {
        var record = new SqlDataRecord(metaData);
        record.SetInt32(0, dto.Prop1);
        record.SetDateTime(1, dto.Prop2);
        record.SetString(2, dto.Prop3);

        yield return record;
    }
}


static IEnumerable GetEnumerableOfRandomDto(uint length)
{
    var rnd = new Random();

    for (var i = 0; i < length; i++)
    {
        yield return new Dto(rnd.Next(1, int.MaxValue), 
                             DateTime.Now.AddMinutes(rnd.Next(1, 10000)),
                             Guid.NewGuid().ToString()
                             );

        if ((i + 1) % 100_000 == 0)
            Console.WriteLine($"Generated enumerable {i + 1} at {DateTime.Now:O}");
    }
}


internal record Dto(int Prop1, DateTime Prop2, string Prop3);

I am testing on SQL Server 2019 CU14. Consider the following table-valued function created against a SQL Server database with compatibility level 130 or 140:

-- requires database compat 130 or 140 to see the issue
CREATE OR ALTER FUNCTION [dbo].[TVF_BUG_REPRO_2] (@O DateTime, @Z varchar(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT
CAST(
       CASE
              WHEN SZ.Zone1 > '' THEN (@O at time zone SZ.Zone1) at time zone 'Pacific Standard Time' 
              WHEN LEN(@Z) > 3 THEN (@O at time zone @Z) at time zone 'Pacific Standard Time'
              ELSE @O
       END AS DATETIME
) ConvertedDate
FROM (SELECT CASE @Z WHEN 'ET' THEN 'Eastern Standard Time' ELSE NULL END Zone1) SZ;

The following query executes without error and returns the expected results: SELECT * FROM [dbo].[TVF_BUG_REPRO_2] ('2022-01-10 16:16:51.327', 'ET'); The following query unexpectedly throws an error: SELECT * FROM [dbo].[TVF_BUG_REPRO_2] (GETDATE(), 'ET'); The error message is: > Msg 9820, Level 16, State 1, Line 27 > > The time zone parameter 'ET' provided to AT TIME ZONE clause is invalid. I don't understand why I'm getting an error here. That code should never execute. Running the code without a function as a simple SELECT also does not throw any errors. Why does that query fail? Could this be a bug in SQL Server?

I've got a process that grabs a bunch of records (thousands) and operates on them. When I'm done, I need to mark a large number of them as processed. I can indicate this with a big list of IDs. I'm pulling the IDs down along with "payload" data via another stored proc. I don't need to update all that data, though -- just update a flag on certain records. I'm trying to avoid the "updates in a loop" pattern, so I'd like to find a more efficient way to send this bag of IDs into a SQL Server 2008 stored proc. Proposal #1 - Table Valued Parameters. I can define a table type w/ just an ID field and send in a table full of IDs to update. Proposal #2 - XML parameter (varchar) with OPENXML in proc body. Proposal #3 - List parsing. I'd rather avoid this, if possible, as it seems unwieldy and error prone. Any preference among these, or any ideas I've missed?

I've found that using TVPs to pass multiple values to a stored procedure is much easier and faster, especially when dealing with a few columns and a few hundred rows, as it eliminates the need for additional data parsing. Why might someone choose XML or JSON over TVPs? In what use cases do they offer better performance or advantages? Given a chance, what would you choose between the three? Thanks!

I have defined an In-Memory Table Type to hold a sequence of unique integers for the purpose of passing them between stored procedures. The type has the following definition: CREATE TYPE [dbo].[IntegerUniqueList] AS TABLE( [IntegerValue] [int] NOT NULL, PRIMARY KEY NONCLUSTERED ( [IntegerValue] ASC ) ) WITH ( MEMORY_OPTIMIZED = ON ) In complex queries that use it, even though an index seek seems the obvious choice, the optimizer is choosing to include an Index Spool. This is defeating the point of having the data in memory. This can be replicated in AdventureWorks2019 with the following: DECLARE @IDs dbo.IntegerUniqueList INSERT @IDs (IntegerValue) SELECT p.BusinessEntityID FROM Person.BusinessEntity p SELECT p.BusinessEntityID FROM Person.Person p WHERE (p.BusinessEntityID = 1) OR ( ( (0 = 1) OR EXISTS ( SELECT 1 FROM Person.BusinessEntity be JOIN Person.BusinessEntityAddress bea ON (bea.BusinessEntityID = be.BusinessEntityID) JOIN Person.Address a ON (a.AddressID = bea.AddressID) WHERE (bea.AddressTypeID = 2) AND (be.BusinessEntityID = p.BusinessEntityID) AND (a.AddressLine1 LIKE N'%1%') ) ) AND EXISTS ( SELECT 1 FROM @IDs id WHERE (id.IntegerValue = p.BusinessEntityID) ) ) Here is the plan produced: https://www.brentozar.com/pastetheplan/?id=SkFipTYTR If I remove the in-memory aspect, or change the NONCLUSTERED Primary Key to use a HASH index, the spool goes away. Given that in-memory *should* give better performance and I can't predict how many rows will be put in the variables to predict a reasonable BUCKET_COUNT, neither of those solutions is ideal. What's going on here and are there any other options to prevent the spool?

My version of SQL Server is SQL Server 2019 (RTM-CU18). The following repro code requires that an in memory filegroup is created. For anyone following along, please remember that an in-memory filegroup cannot be dropped from a database once it is created. I have a simple in-memory table in which I insert integers from 1 - 1200: DROP TABLE IF EXISTS [dbo].[InMem]; CREATE TABLE [dbo].[InMem] ( i [int] NOT NULL, CONSTRAINT [PK_InMem] PRIMARY KEY NONCLUSTERED (i ASC) ) WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY ); INSERT INTO [dbo].[InMem] SELECT TOP (1200) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM master..spt_values t1 CROSS JOIN master..spt_values t2; I also have the following natively compiled stored procedure: GO CREATE OR ALTER PROCEDURE p1 WITH NATIVE_COMPILATION, SCHEMABINDING AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english') SELECT c1.i, c2.i, c3.i FROM dbo.[InMem] c1 CROSS JOIN dbo.[InMem] c2 CROSS JOIN dbo.[InMem] c3 WHERE c1.i + c2.i + c3.i = 3600; END; GO The procedure returns one row when executed. On my machine it takes around 32 seconds to complete. I cannot observe any unusual behavior in terms of memory usage while it executes. I can create a similar table type: CREATE TYPE [dbo].[InMemType] AS TABLE( i [int] NOT NULL, INDEX [ix_WordBitMap] NONCLUSTERED (i ASC) ) WITH ( MEMORY_OPTIMIZED = ON ); as well as the same stored procedure but using the table type instead: GO CREATE OR ALTER PROCEDURE p2 (@t dbo.[InMemType] READONLY) WITH NATIVE_COMPILATION, SCHEMABINDING AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english') SELECT c1.i, c2.i, c3.i FROM @t c1 CROSS JOIN @t c2 CROSS JOIN @t c3 WHERE c1.i + c2.i + c3.i = 3600; END; GO The new stored procedure throws an error after about one minute: > Msg 701, Level 17, State 154, Procedure p2, Line 6 [Batch Start Line 57] There is insufficient system memory in resource pool 'default' to run this query. While the procedure executes I can see the amount of memory used by the MEMORYCLERK_XTP memory clerk increase to around 2800 MB for the database by querying the sys.dm_os_memory_clerks dmv. According to the sys.dm_db_xtp_memory_consumers DMV, nearly all of the memory usage seems to be from the "64K page pool" consumer: For reference, here is how I executed the new stored procedure. It uses the same 1200 rows as the table: DECLARE @t dbo.[InMemType]; INSERT INTO @t (i) SELECT i from [dbo].[InMem]; EXEC p2 @t; The resulting query plan is a simple nested loop plan with no blocking operators. Per request, here is an estimated query plan for the second stored procedure. I do not understand why memory usage grows to over 2 GB for such a query when I use a table-valued parameter. I have read various bits of documentation and in-memory OLTP white papers and can't find any reference to this behavior. Using ETW tracing, I can see that the first procedure spends most of its cpu time calling hkengine!HkCursorHeapGetNext and the second procedure spends most of its cpu time calling hkengine!HkCursorRangeGetNext. I can also get the C source code for both procedures. The first procedure is here and the second procedure, with the memory problem, is here . However, I don't know how to read C code so I don't know how to investigate further. Why does a simple natively compiled stored procedure use over 2 GB of memory when performing nested loops against a table-valued parameter? The problem also occurs when I run the query outside of a stored procedure.

I'm relatively new to database programming and am looking for some some theory/best practices and feedback on an issue involving multiple dependent, cascading, computed columns for an OLTP SQL Server/Azure environment. For some context, I've already translated a heavily denormalized reporting table that had hard coded excel formulas (no DAX) into a 3NF RDBMS with one main fact table and all of the applicable dimensions, all with proper primary and foreign key constraints, consistent grain, etc. The fact table is simply just the heavily normalized set of foreign keys, where applicable, along with the data entry from a user form that will be used to compute the resulting field values. Here's a super simplified version in a test environment: https://www.mycompiler.io/view/5WbQMoQOOyL While I can of course render all of the proper denormalized foreign key values in a simple view using JOINS, I find it challenging to know what the best approach would be to render the multiple dependent, cascading calculated or computed columns. I find a view is too simple and a trigger is (perhaps) too complex. Maybe a stored procedure and or various table value function? I'm honestly not sure. I'd like to have this information persisted as opposed to computed or calculated each time the data is called in a query or used in a result set for external stakeholders, but I could also be totally off base here and need an entirely different approach. I just want to ensure speed, reliability and accuracy in the processing. For more context, the total number of records is not very high, roughly 40-50k, adding maybe 9-10k each year, maybe a few hundred give or take each week. Here's a simplified visual:

Column A -- data entry value by user, captured in fact table
Column B -- data entry value by user, captured in fact table
Column C -- A * B
Column D -- data entry value by user, captured in fact table
Column E -- C * D
Column F -- C - E

Any ideas or suggestions would be greatly appreciated.

I have a stored procedure (SS2k8) with a couple table-valued parameters that will sometimes be null or empty. I have seen this StackOverflow post that says that null/empty TVPs should simply be omitted from the calling parameter list. My problem is that I can't figure out how to check for empty or null inside the stored procedure as "IF (@tvp IS NULL)" fails on procedure creation with the message 'Must declare the scalar variable "@tvp"'. Do I have to do a SELECT COUNT(*) on the TVP and check for zero? Code excerpt: CREATE PROCEDURE [foo] (@tvp [TvpType] READONLY) AS IF (@tvp IS NOT NULL) -- doesn't work BEGIN -- lots of expensive processing END ELSE BEGIN -- a little bit of cheap processing END ...

According to this blog parameters to a function or a stored procedure are essentially pass-by-value if they aren't OUTPUT parameters, and essentially treated as a safer version of pass-by-reference if they are OUTPUT parameters. At first I thought the goal of forcing TVP to be declared READONLY was to clearly signal to developers that the TVP can't be used as an OUTPUT parameter, but there must be more going on because we can't declare non-TVP as READONLY. For example the following fails: create procedure [dbo].[test] @a int readonly as select @a > Msg 346, Level 15, State 1, Procedure test
The parameter "@a" can not be declared READONLY since it is not a table-valued parameter. 1. Since statistics aren't stored on TVP what is the rationale behind preventing DML operations? 2. Is it related to not wanting TVP to be OUTPUT parameters for some reason?

A legacy application has a nightly job that repeatedly calls some store procedure using a TVP and passes in batches of 10,000 ids that are in sequential order that it needs to process. Now that the ids are in the millions, it seems that this process is taking noticeably longer. The roughly the same number of batch calls are being run each night, but from profiling it seemed that the procedure was getting slower. We checked the usual culprits, rebuilt the indices and updated stats on the tables in use and tried sticking a recompile on the procedure. But nothing fixed the regression. The procedure does some processing and returns a few result each with a cardinality of maybe 10000 rows. One of my colleagues looked at it and fixed the performance regression by updating the store procedure by simply adding the following to the top of the query:

select id into #t from @ids

and replacing all usages of @ids with #t. I was amazed at this simple fix, and was trying to understand it more. I tried to create a very simple reproduction.

create table dbo.ids
(
   id int primary key clustered,
   timestamp
);

create type dbo.tvp as table(id int primary key clustered)

insert into dbo.ids(id)
select row_number() over (order by 1/0)
from string_split(space(1414),' ') a,string_split(space(1414),' ') b
go
create or alter procedure dbo.tvp_proc
(
    @ids dbo.tvp readonly
)
as
begin
    declare @_ int = 0, @r int = 5;
    while(@r > 0)
        select @_ = count(*), @r -= 1
        from dbo.ids i
        where exists (
            select 1
            from @ids t
            where t.id = i.id     
        );
end 
go
create or alter procedure dbo.temp_proc
(
    @ids dbo.tvp readonly
)
as
begin
    select * into #t from @ids
    declare @_ int = 0, @r int = 5;
    while(@r > 0)
        select @_ = count(*), @r -= 1
        from dbo.ids i
        where exists (
            select 1
            from #t t
            where t.id = i.id     
        );
end

And here is my simple benchmark.

set nocount on;
declare @s nvarchar(4000)=
'declare @ids tvp;
insert into @ids(id)
select @init + row_number() over (order by 1/0)
from string_split(space(99),char(32)) a,string_split(space(99),char(32)) b
declare @s datetime2 = sysutcdatetime()
create table #d(_ int)
insert into #d
exec dbo.tvp_proc @ids
print concat(right(concat(space(10),format(@init,''N0'')),10),char(9),datediff(ms, @s, sysutcdatetime()))',
@params nvarchar(20)=N'@init int'
print 'tvp result'
exec sp_executesql @s,@params,10000000
exec sp_executesql @s,@params,1000000
exec sp_executesql @s,@params,100000
exec sp_executesql @s,@params,10000
select @s=replace(@s,'tvp_proc','temp_proc')
print 'temp table result'
exec sp_executesql @s,@params,10000000
exec sp_executesql @s,@params,1000000
exec sp_executesql @s,@params,100000
exec sp_executesql @s,@params,10000

Running this benchmark on my machine yields the following results:

tvp result
10,000,000	653
 1,000,000	341
   100,000	42
    10,000	12
temp table result
10,000,000	52
 1,000,000	60
   100,000	57
    10,000	59

The results show that the tvp approach seems to slow down as the ids inside get bigger, where as the temp table stays pretty consistent. Anyone have an idea as to why referencing a tvp with larger values is slower than a temp table?

As part of a manual replication process between databases with different but related schemas, for each table, we identify new and modified records in the source database and feed them via a table valued parameter to a stored procedure in the SQL Server destination database. The source database is not SQL Server and is in a different data center. For each row to be upserted, the stored procedure queries for the primary keys of the related rows already in the destination database. Currently, we do a single SELECT to get the primary keys, followed by a single MERGE to perform the upsert. There are two aspects of this approach that I believe may not be as efficient as possible. 1. An implicit transaction unnecessarily wraps the MERGE. The database would remain consistent even with each row being upserted one at a time. If an row's upsert fails, we want the remaining rows to proceed. 2. MERGE interleaves inserts and sets as it goes through the rows, which is fine, but we don't need this. It would be acceptable to set all the modified rows and subsequently insert all the new rows. Based on the flexibility we have, the MERGE performance tip to use UPDATE and INSERT seems to apply to our case: > When simply updating one table based on the rows of another table, improved performance and scalability can be achieved with basic INSERT, UPDATE, and DELETE statements. Do I understand that right? Am I better off with a separate UPDATE and INSERT? And what about the implicit transaction? Is performing a single SELECT, UPDATE, and INSERT over a large batch of rows most efficient, or is it better to take advantage of the ability to do one row at time by using a FOR loop? Or something else? In general, what is the most efficient way to upsert a large batch or rows to a SQL Server table in which the rows are not transactionally related and sets and inserts need not be interleaved?

-sql
    CREATE PROCEDURE [dbo].[GetFruitName]
        (
@quantity int
)
    AS
    BEGIN
          SET NOCOUNT ON;

         INSERT INTO Table1(fname,fprice,quantity)
          SELECT f.name,f.price from table2 f,@quantity

          
    END

but am unable to pass @quantity

I have a stored procedure that calls an external script via a SQL Server language extension. I would like for my stored procedure to use a caller supplied table valued parameter (TVP) to then submit input data to the external script: ALTER procedure [dbo].[testTvp] ( @inputTvp dbo.myTvp READONLY ) AS BEGIN DECLARE @tSqlString nvarchar(100)=N'select * from @inputTvp' EXEC sp_execute_external_script @language = N'Java', @script = N'com.example.TestTvp', @input_data_1 = @tSqlString -- this doesn't work END When I execute this procedure: DECLARE @myInput dbo.myTvp INSERT INTO @myInput values ('a'); INSERT INTO @myInput values ('b'); EXECUTE [dbo].[testTvp] @myInput I get the following error: >Must declare the table variable "@inputTvp". I suppose that means that @inputTvp is not visible (out of scope) to sp_execute_external_script. Is there a way to pass in @inputTvp to sp_execute_external_script? I know that I could store the @inputTvp data into a temp table and pass in a t-sql string to query from that temp table. But I'm trying to avoid using a temp table.

I know how to create a table type (to be used as a table-valued parameter) - but once created, how can I view it?

I can find information on creating procedures with table-valued parameters, and information on table literals, but I can’t find any mention of the two combined. Suppose I have a procedure defined something like: create type info as table(stuff int, more text); create procedure test(@id int,@data info) as begin -- etc end; I would like to execute the procedure using a table literal instead of assigning values into a variable. I would like to be able to do something like this: execute test 42,(values((1,'one'),(2,'deux'),(3,'drei')) as (stuff,more)); It doesn’t seem to work. I know I can create a variable and do it that way, and that is the method generally used in examples. Is there a way to use a a syntax similar to the above? If so, how? I want to this partly for the exercise, but more importantly, it seems to me that since table literals are part of the language, it should be possible to use them wherever tables are required. In this regard I think there are gaps in the implementation. Finally, I think that using a literal is _sometimes_ simpler and more intuitive, so creating a variable just to pass information through is a kludge.

Is there a best practice or strategy for table types used in TVPs? For instance, given the following:

CREATE TABLE dbo.Colors (
    Id int identity PRIMARY KEY,
    Name nvarchar(100),
);

CREATE TABLE dbo.Shapes (
    Id int identity PRIMARY KEY,
    Name nvarchar(100),
);

CREATE TABLE dbo.Materials (
    Id int identity PRIMARY KEY,
    Name nvarchar(100),
);

CREATE TABLE dbo.Items (
    Id int identity PRIMARY KEY,
    Name nvarchar(100),
    ColorId int FOREIGN KEY REFERENCES dbo.Colors (ID),
    ShapeId int FOREIGN KEY REFERENCES dbo.Shapes (ID),
    MaterialId int FOREIGN KEY REFERENCES dbo.Materials (ID),
);

If you implemented a stored procedure for searching items that needed to support selecting multiple colors, multiple shapes, and multiple materials via TVPs (think checkbox lists in the UI), would you create three separate table types, one for every TVP, or would you create a single type for using it across all three? In other words, this:

CREATE TYPE dbo.ColorIds AS TABLE (Id int);
CREATE TYPE dbo.ShapeIds AS TABLE (Id int);
CREATE TYPE dbo.MaterialIds AS TABLE (Id int);
GO

CREATE PROCEDURE dbo.SearchItems
    @ColorIds ColorIds READONLY,
    @ShapeIds ShapeIds READONLY,
    @MaterialIds MaterialIds READONLY
AS
BEGIN
    PRINT 'Do something here'
END
GO

Versus this:

CREATE TYPE dbo.Ids AS TABLE (Id int);
GO

CREATE PROCEDURE dbo.SearchItems
    @ColorIds Ids READONLY,
    @ShapeIds Ids READONLY,
    @MaterialIds Ids READONLY
AS
BEGIN
    PRINT 'Do something here'
END
GO

The sample is deliberately contrived; the real use case consists of a lot more tables which although have different columns, all have a ID int primary key. Because of this, I personally am _much more_ inclined to do the latter. It's far less overhead, but I'm curious to know if there are any cons I should be aware of in doing this. This is of course for TVPs and TVPs _only_ (I would never mix different entities in a _real_ table, or any other structure of a more permanent nature.) While at it, what is your naming convention for naming table types and TVPs?

All of my tables have those two columns: [Id] [uniqueidentifier] NOT NULL [Revision] [bigint] IDENTITY(1, 1) NOT NULL Every other time I write a query, I want to eliminate some of the rows. The elimination conditions are the same for every table. I want an easy way to specify those conditions once and then reuse them on any table. I defined a table type as a "base class" for my tables CREATE TYPE EntityTable AS TABLE ( [Id] [uniqueidentifier] NOT NULL ,[Revision] [bigint] IDENTITY(1, 1) NOT NULL ) And here's a table valued function I create CREATE FUNCTION MaxRevision (@Entities EntityTable READONLY) RETURNS TABLE AS RETURN SELECT * FROM @Entities e WHERE e.Revision = ( SELECT max(Revision) FROM @Entities er WHERE er.id = e.id ) I thought I would be able to use it like that SELECT * FROM MaxRevision(NameOfSomeTableWhichHasIdAndRevisionColumn) WHERE /* some other conditions */ But I'm getting the error Msg 207, Level 16, State 1, Line 2 Invalid column name 'NameOfSomeTableWhichHasIdAndRevisionColumn'. Why it treats the table name I pass as a column name? Are there any other ways to implement the behavior I want?

some time ago I was developing a simple procedure to get the database sizes on a particular server. Here it is the procedure: USE MASTER GO ALTER PROCEDURE dbo.sp_getDBSpace @Radhe sysname=null, @system_only BIT = NULL , @user_only BIT = NULL , @database_list NVARCHAR(MAX) = NULL , @exclude_list NVARCHAR(MAX) = NULL , @recovery_model_desc NVARCHAR(120) = NULL , @compatibility_level TINYINT = NULL , @state_desc NVARCHAR(120) = N'ONLINE' , @is_read_only BIT = 0 , @is_auto_close_on BIT = NULL , @is_auto_shrink_on BIT = NULL , @is_broker_enabled BIT = NULL AS BEGIN SET NOCOUNT ON SET XACT_ABORT ON SET DEADLOCK_PRIORITY NORMAL; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED DECLARE @SQL NVARCHAR(MAX) SET @sql = N'USE [?] SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;with radhe as ( SELECT Servername = @@servername, DatabaseName = DB_NAME(), a.FILE_ID, a.type, -- 0 is log and 1 is data [Drive] = LEFT(UPPER(a.physical_name), 1), [FILE_SIZE_GB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000/1024.000, 2)), [SPACE_USED_GB] = CONVERT(DECIMAL(12, 2), ROUND(FILEPROPERTY(a.NAME, ''SpaceUsed'') / 128.000/1024.000, 2)), [FREE_SPACE_GB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - FILEPROPERTY(a.NAME, ''SpaceUsed'')) / 128.000/1024.000, 2)), a.NAME, a.PHYSICAL_NAME AS [FILENAME] FROM sys.database_files a ) insert into ' + @Radhe + N'([ServerName],[DatabaseName],[Drive],[FILE_ID],[FILE_SIZE_GB],[SPACE_USED_GB],[FREE_SPACE_GB],[NAME],[FILENAME],[TOTAL_DATABASE_SIZE]) SELECT [ServerName] ,[DatabaseName] ,[Drive] ,[FILE_ID] ,[FILE_SIZE_GB] ,[SPACE_USED_GB] ,[FREE_SPACE_GB] ,[NAME] ,[FILENAME] ,TOTAL_DATABASE_SIZE = SUM([FILE_SIZE_GB] ) OVER (PARTITION BY A.DATABASENAME ) --,SPACE_USED = SUM([SPACE_USED_GB] ) OVER (PARTITION BY A.DATABASENAME ) --,TOTAL_FREE_LOG_SPACE_GB = SUM(CASE WHEN A.TYPE = 0 THEN [FREE_SPACE_GB] ELSE 0 END ) OVER (PARTITION BY A.DATABASENAME ) --,TOTAL_FREE_DATA_SPACE_GB = SUM(CASE WHEN A.TYPE = 1 THEN [FREE_SPACE_GB] ELSE 0 END ) OVER (PARTITION BY A.DATABASENAME ) FROM radhe a ' exec sp_foreachdb @system_only = @system_only, @user_only = @user_only, @database_list = @database_list, @exclude_list = @exclude_list, @recovery_model_desc = @recovery_model_desc, @compatibility_level = @compatibility_level, @state_desc = @state_desc, @is_read_only = @is_read_only , @is_auto_close_on = @is_auto_close_on, @is_auto_shrink_on = @is_auto_shrink_on , @is_broker_enabled = @is_broker_enabled ,@command=@SQL END GO And here is how I used to call this procedure: IF OBJECT_ID('tempdb..#Radhe') IS NOT NULL DROP TABLE #Radhe GO CREATE TABLE #Radhe( [ServerName] NVARCHAR(128) NULL, [DatabaseName] NVARCHAR(128) NULL, [Drive] NVARCHAR(1) NULL, [FILE_ID] INT NOT NULL, [FILE_SIZE_GB] DECIMAL(12,2) NULL, [SPACE_USED_GB] DECIMAL(12,2) NULL, [FREE_SPACE_GB] DECIMAL(12,2) NULL, [NAME] SYSNAME NOT NULL, [FILENAME] NVARCHAR(260) NULL, [TOTAL_DATABASE_SIZE] DECIMAL(38,2) NULL) declare @db_list NVARCHAR(MAX) SELECT @db_list = STUFF(( SELECT ', ' + name FROM sys.databases WHERE name NOT IN ('DBA','TABLEBACKUPS','MASTER','MSDB','MODEL','TEMPDB') FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 2, '') --just to check the list --exec sp_foreachdb @database_list = @db_list -- ,@command='use ?; print db_name() + char(13)' exec sp_getDBSpace @Radhe = '#Radhe' ,@database_list = @db_list ,@exclude_list = 'DBA,TABLEBACKUPS,MASTER,MSDB,MODEL,TEMPDB' select * from #Radhe **Question:** Although it works is there a more elegant way to get this done by doing something else rather than passing a temp table name as a parameter? I need the temp table to be available after the call of the stored procedure. Inside the stored procedure, I need to insert data into that temp table. is there any other way of getting this done, without using a temp table as a parameter as I did? there is a similar question here . Some links: -- https://dba.stackexchange.com/questions/121865/how-to-restore-a-database-ignoring-the-free-space-within-each-file/121867#121867 -- https://dba.stackexchange.com/questions/163017/generate-insert-list-but-only-for-non-nullable-columns -- Determining Drive Letters in Use by SQL Server Databases -- https://www.mssqltips.com/sqlservertip/3048/determining-drive-letters-in-use-by-sql-server-databases/ -- PROCEDURE sp_foreachdb -- https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/blob/dev/sp_foreachdb.sql

I've made a stored procedure to get all results which match specific students and objectives. CREATE PROCEDURE [dbo].[GetResults] @students [IdList] READONLY, @objectives [IdList] READONLY AS SELECT Id, UserId, ObjectiveId, DateUploaded, TrafficLight FROM [Results] WHERE [UserId] IN (SELECT [Id] FROM @students) AND [ObjectiveId] IN (SELECT [Id] FROM @objectives) AND [TrafficLight] IS NOT NULL ORDER BY [UserId] ASC, [ObjectiveId] ASC, [DateUploaded] DESC It uses a user-defined table type for passing in arrays of students and objectives: CREATE TYPE [dbo].[IdList] AS TABLE( [Id] [int] NULL ) Typically @students contains ~30 IDs and @objectives contains ~100 IDs. The Results table has about 500,000 rows and a nonclustered index on UserId, ObjectiveId, TrafficLight. At the moment it's taking a long time (up to 5 seconds) to search 500,000 rows. I'm new to stored procedures and think I must be doing something wrong. How can the performance be improved?