Support TVP

[fsprojects]

Support TVP

[jackfoxy]

I was already essentially doing this in the SqlCommandTP work I recently did, using a SP developed developed in-house at the shop I'm contracting to. Would really like to know what the most efficient way to do this is, as it is a very common requirement. I'm looking for execution speed low resource usage on the server.

[dmitry-a-morozov]

Can you post some sample code?

[jackfoxy]

[<Literal>]
let sqlCity = @"SELECT TOP (@categoryLimit) city FROM (
                    SELECT DISTINCT city
                    FROM tbQuickLocCount WITH (NOLOCK)
                    WHERE 
                        city LIKE @startsWith                                           
                        AND locType = 'City'
                        AND ownerID IN (select * from dbo.ConvertCSVToTableOfInts(@ownerIDList))
                    ) as temp
                 ORDER BY city"

type QueryCity = SqlCommand<sqlCity, ConnectionStringName="staging", ConfigFile="AutoComplete.config">

let cityHits query ownnerCommaList entityID catLimit = 
    let cmdCity = QueryCity(connectionString = connectionString, startsWith = query + "%", ownerIDList = ownerCommaList, entit

[jackfoxy]

Taking a quick look at expected plan execution ConvertCSVToTableOfInts was 45% of total.

[jackfoxy]

My mistake, dbo.ConvertCSVToTableOfInts is a table-valued function.

[dmitry-a-morozov]

@jackfoxy No problem. It's still good. I understand what use case is. You would prefer to pass in ownerIDList as list of tuples or list of records, right?

[jackfoxy]

I can't think of a use case where I would want to pass in tuples or records. I have never known this use case to consume anything other than simple types, usually int32, sometimes string, but could be something else. And is there any reason to require a list? Why not allow any #seq for maximum flexibility?

I'm curious, what use case do you have in mind to parameterize for a list of tuples or records?

On a related note, I have not investigated, but I'm guessing you implement parameters as objects. Our DBA would disapprove. Especially in the case of varchar columns, SQL parameters should be strongly typed to the SQL DB type.

[dmitry-a-morozov]

sure #seq will be the best fit. In your example, don't you want to pass list of accounts (as #seq) instead of CSV string?

[jackfoxy]

Yes, I certainly would rather pass as #seq. I was just re-using a utility function built for interfacing to C# that is available at this shop, as I was in a hurry. I'm sure you will think of something much better. As I said before, this utility function consumes 45% of the estimated plan overhead on a simple query like the example.

[dmitry-a-morozov]

Btw, I forgot to comment on parameter types. We use SQL db type.So, nothing to worry about.

[rojepp]

The only way I've been able to discover a table-valued parameter is by passing it to a user-defined function or sproc:

CREATE TYPE myTableType 
AS TABLE (myId int not null)

GO

CREATE FUNCTION myUDF (@p1 myTableType readonly)
RETURNS int
AS
BEGIN
   DECLARE @Result int
   SELECT @Result = (select top 1 * from @p1) 
    RETURN @Result
END
GO

sp_describe_undeclared_parameters @tsql=N'select dbo.myUDF(@x)'

This custom type can then be discovered, and a record built for it or something. I do see a use-case for something more complex than a seq of simple types. One could use a more complex type to insert a bunch of things in one go.

[rojepp]

I can have a go at it if no-one is working on it? Let me know.

[dmitry-a-morozov]

Sure, @rojepp. Go ahead. I was thinking that columns info can be retrieved something like:

select c.*
from sys.table_types as tt
inner join sys.columns as c
on tt.type_table_object_id = c.object_id
where tt.name = 'myTableType '

I think a lot of functionality can be reused

[dmitry-a-morozov]

@rojepp Create child type with same name as table type under providedCommanfType. Ideally choice between tuples and custom record type should be control by same param. So it will make sense rename to something like "RowsetType" instead of "ResultType".

[rojepp]

@dmitry-a-morozov I was thinking the same. :) It may be a few days before I have something working.

[dmitry-a-morozov]

@rojepp No problem. Take your time.

[rojepp]

@dmitry-a-morozov The parameter for choosing result type can also be DataTable. I can also imagine wanting DataTable for output but sending TVP as tuples. I suggest adding an optional parameter for TVP type, with a choice between tuples and records. Either that, or just start with tuples, and add records later if need arises. What do you think?

[dmitry-a-morozov]

Let's start with tuples only as input type for TVP.