.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Using ADO.NET SqlDbType.Structured TVP (Table Valued Parameters) causes SQL Compilation for every ca

Posted By:      Posted Date: October 04, 2010    Points: 0   Category :Sql Server

When calling a stored procedure with a table valued parameter from ADO.Net, each call requires a SQL Compilation.  In an application that makes this call thousands of times per second, this causes SQL Compilations thousands of times per second leading to degraded performance.

When calling the same stored procedure directly from Management Studio, no SQL Compilations are performed.

Steps to reproduce ...

1. Create the following User Defined Type and Stored Procedure (SQL2008 required) ...

CREATE TYPE [dbo].[BigIntListType] AS TABLE(
 [Id] [bigint] NOT NULL,
  [Id] ASC

  @itemIds AS BigIntListType READONLY
  SELECT col1, col2 FROM table1
  JOIN @itemIds ON table1.id = @itemIds.Id

2. Start SQL Profiler and capture "Showplan XML for Query Compile".  Start Perfmon and add "SQL Compilations / Second"

3. Run the following code from Management Studio as many times as possible ...

declare @p2 dbo.BigIntListType

insert into @p2 values(601)
insert into @p2 values(1)

exec testProc @itemIds = @p2

View Complete Post

More Related Resource Links

using table valued parameters for stored procedures any limitations

I am thinking of using a table valued parameter for my new stored procedure, but wonder if there are any performance implications to be aware of and is the limitation of using statistics applicable with table value parameters, i.e do they keep statistics and will this affect the judgement of query plans i.e estimated number of rows, from my experience table variables do suffer from this problem but I would like to confirm if it applies to table value parameters as well.

Table Valued Parameters (TVP)


hi all;

this is new to me but i want to have a TVP to accept list of records as a parameter of a stored procedure. the following is the code i have written.

CREATE TABLE dbo.GroupRole
userRole VARCHAR(100) NOT NULL,


CREATE PROCEDURE grpRoleRecorder (@userRole usrRoleArr READONLY)
 INSERT INTO dbo.GroupRole VALUES (@userRole);

when i execute the code it gives the folling error.

Msg 137, Level 16, State 1, Procedure grpRoleRecorder, Line 4
Must declare the scalar variable "@userRole".

Please help me on this.

Thank You All

using a checkbox list and Table Valued Parameters


I have a checkboxlist that gets all all the items from a look up table. The user is going to go to the list, check certail items and insert the checkboxid, the value (0 or 1),  and the userid  into a table valued paramater using a stored procedure. I have the user defined table type created but thats about it. Any body out there have any hints.

Which jdbc version support table valued parameters to stored procedures?


Hi all,

I want to use table valued parameters in stored procedures; we have that in .NET client, so I do not want to write another version for jdbc client.

Does anyone know, which version of jdbc engine support that function?

Please give several lines of source codes.

Thanks a million,



FormView -- Parameters and asp:Table


I've already solved this problem to my satisfaction, I just want to know WHY it works this way.

I have a FormView control bound to an ObjectDataSource.  Let's say our data source is a hypothetical table with three columns:  id, name, age.  Why is it that when I use the <asp:Table> element inside of the FormView, the parameter values are not set, but when I use the standard <table> tag, it works fine?

This seems like a .NET problem to me.  I expect the <asp:Table> element to work in the same way as <table> because they are, for all intents and purposes, the same thing.  Am I missing something?

Creating a table-valued function on a user defined type in SQL Server CLR

We would like to be able to create a table-valued function on a user defined type.  We would like the syntax in SQL to look like it works for the XML nodes function:   DECLARE @myXml XML = '<a><b>1</b><b>2</b><b>3</b></a>' ; SELECT  node.query('text()') FROM    @myXml.nodes('a/b') nodes (node) ;   In other words, in the FROM clause, we can access the "nodes" method of the xml variable and it returns a rowset. Thanks!

How to insert values into my table the data is coming from inline table valued function

Hi all,   i have table oi want insert values into my table the data is coming from inline table valued function how i can use the inline table valued function to insert datainto my table i have inline function which out puts 3 collumns of data how i can insert this data into my table i have the insert statment like below insert into tablename(c1,c2,c3,c4,c5) select ('abc',....................,'xyz') how i can use the out put put of my inline function to  to insert data into my columns c2,c3,c4. please help me out Thanks & Regards Sunil Yoganna

table valued function problem

This is my table-valued function Problem is it is returning only 1 record but it is supposed to return more than one i stuck with this problem plz replyALTER function [dbo].[funcqidata](@dst int,@mth int,@yer int) returns @cqi_rpt table ( hosp_name varchar(100) NULL, bedded_strength int NULL, out_patients int NULL, in_patients int NULL, minor_surgeries int NULL, major_surgeries int NULL, deliveries int NULL, radio int NULL, lab_test int NULL, tot_part1 int NULL, waste_man int NULL, swab_test int NULL, attendence int NULL, drug int NULL, charges_thismonth int NULL, charges_lastmonth int NULL, usr_charges_marks int NULL, tot_part2 int NULL --grand_tot int NULL ) as BEGIN DECLARE @hosp_name varchar(100), @bedded_strength int, @out_patients int, @in_patients int, @minor_surgeries int, @major_surgeries int, @deliveries int, @radio int, @lab_test int, @tot_part1 int, @opd_handled int, @opd_target int, --------- supporting variables declared for calculation ----------- @ipd_handled int, @ipd_target int, @minor_handled int, @minor_target int, @major_handled int, @major_target int, @delivery_handled int, @delivery_target int, @radio_handled int, @radio_target int, @lab_handled int, @lab_target int, -------- end of declaring variables ----------------------------- @check_charges int, @waste_man int, @swab_test int,

trying to use CASE within table-valued function

Hi, I am trying to create a table-valued function that takes a string argument and, depending upon it's value, returns one of a number of identically-structured tables (and before someone asks why multiple identically structured tables, NOT my idea!).  I am a novice with T-SQL, but I've been attempting something like this for the function: Case @Input   When 's1' then return table1   When 's1' then return table2 End ... but I keep getting errors.  Can someone help get the ball rolling and show me the basics on how to set up a function to do this?  I'm hoping since I'm just redirecting to existing tables that this function will have minimal performance hits.

Cannot call Table Valued Function from Dynamic stored procedure statement

Hello,  I have a table-valued function that splits string into a table column. I can easily call this function from a stored procedure within a regular SELECT statement: SELECT * FROM Table1 WHERE Code1 = '1' AND Code2 IN(SELECT * FROM [dbo].[fnSplitValues](@Code2String))   However when I try to use the same logic for a dynamic SELECT statement: ‘SELECT * FROM Table1 WHERE Code1 = '1' AND Code2 IN(SELECT * FROM ' + [dbo].[fnSplitValues](@Code2String) + ')’ I get an error ‘Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnSplitValues", or the name is ambiguous.’ Any idea what is wrong with my dynamic SQL?   Please help, Lana

Schema comparer mistake in table-valued function (Nullability in column definition)

Hello. In my database i have table valued function. In its definition nullability of column is not set directly. This column has a user-defined data type. So the question is: is this column nullable? As i can see here: http://msdn.microsoft.com/en-us/library/ms174979.aspx   When column nullability is not explicitly specified, column nullability follows the rules shown in the following table. Column data type Rule Alias data type The Database Engine uses the nullability that is specified when the data type was created. To determine the default nullability of the data type, use sp_help. It should be not null, as user defined type is declared as not null. SMO says, that column is not null, but schema compare says it is nullable. Who is wrong and why?

searching SQL table with multiple parameters

I want to query an SQL table using some Web controls to provide the parameters for filtering the records in the table. Results are displayed in a GridView. I have a TextBox with an ID of "keyword" where the user may enter one or more keywords. I'd like to search 2 columns for instances of these keywords. I also have a DropDownList named "category" that lists categories contained in a "category" column of the table. I have a dataset with a TableAdapter for the table I want to search. Got it working fine with the DropDown List but not sure how to proceed with the TextBox and keywords. What SQL query should I use? The user may not enter anything into the textbox, they may enter one word, or multiple words. This is where I am now: SELECT * FROM tablename WHERE category = @category AND ?????  

Inline table valued function, full outer join and cross apply

Good afternoon, I'm experiencing quite strange issue with our T-SQL code. We have inline table-valued function which is using full outer join on 1 = 1. The reason why we do it this way is - we need to combine one row from 2 different result sets either of them can have either 0 or 1 row. The strange thing, that if we have the situation when both row-sets are empty, function does not return the row (as expected). Although, select which uses this function with cross apply "uses" the result from the previous row. Below is the simplified example. First select with ID = 2 predicate does not return any rows. Although if I remove this filter, it re-uses Value2 from the previous row. create FUNCTION dbo.Func(@Id int) RETURNS TABLE AS RETURN ( with CTE1(Value1) as ( select 'Value1' where @ID = 1 ) ,CTE2(Value2) as ( select 'Value2' from CTE1 ) select CTE1.Value1, CTE2.Value2 from CTE1 full outer join CTE2 on 1 = 1 ) go select * from dbo.Func(2) go create table dbo.TestTable ( ID int not null ) go insert into dbo.TestTable values(1), (2) go select t.ID, f.Value1, f.Value2 from dbo.TestTable t cross apply dbo.Func(t.ID) f where t.ID = 2 go select t.ID, f.Value1, f.Value2 from dbo.TestTable t cross apply dbo.Func(t.ID) f order by t.id go Our SQL version is: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) &nb

Dynamically fill query parameters in a table adapter ?

SO I have created an object data source on a page from a table adapter using the various Wizards in Visual Studio.Its meant to query a diary Database by a DateTime parameter called diaryDate. I want to query from a date Now to the next 14 days ahead.I want to display the entries in a Grid View when the page loads.So I'm a bit stuck now as to how to set the two query parameters date1 and date2. I want to set date1 = now() and date2 = now() + 14 days.How do I set those parameters when the page loads ?Should I have used the adapter Wizard approach of do I have to write all the code in the code behind page ??This is my datasource code'<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" InsertMethod="Insert" OldValuesParameterFormatString="original_{0}" SelectMethod="GET_nextTwoWeeks" TypeName="query_getNextTwoWeeksTableAdapters.diaryDatesTableAdapter"> <SelectParameters> <asp:FormParameter DefaultValue="" FormField="date1" Name="date1" Type="DateTime" /> <asp:FormParameter DefaultValue="" FormField="date2" Name="date2" Type="DateTime" /> </SelectParameters> <InsertParameters>

Table-valued parameter and DataTable from ADO.NET


I'm going to use TVP:s to enhance performance in my c# .net fw 4 application which uses stored procs in SQL Server 2008. My first test was very successful and I inserted a batch of rows from by using a TVP in a stored proc. So far so good. Now to my question: when looking for the performance profit with SQL Profiler I saw it was really fast, but the generated sql-code made me a little bit confused. My "table" (DataTable class) from the .net-app was "retabled" like this in sql:

declare @table MyTvpType;

insert @table values (1, 2, 3...); ... x number of times

exec MyProc @table;

This was quite surprising, but when examining my .net code, I think there should be som way to "import" my MyTvpType into .net code, and avoid the extra delaration and inserts done as it is now. I'm using this code in .net:

Issue with multi valued parameters in SSRS using Oracle data source


Hi All,

I have a dataset which is getting data from oracle datasource and my Dataset query expression is

="select To_Char(Time_Stamp,'Month') as Month, Node_Name as Device,Connection,MSNAME,Monitor,Avg as Average,Max as Maximum FROM OracleDataVW where ((monitor='X' and msname in ('Y')) or (MONITOR = 'CPU Utilization' AND MSNAME = 'utilization')) and TO_Char(Time_Stamp,'Mon YYYY') in  ('"+ Join(Parameters!Parameter1.Value,",") +"')"

My parameter is multiValued parameter. The above query is working good if i select one value,however if select more than one value or all it is not giving me the data. I tried to put Ltrim and Rtrim in Join but it is giving me another syntax error.

Please help me with this

Infopath: problem with structured layouts within a repeating section or table



I've run into a bit of a problem when trying to structure information in a repeating section or table.

What I need to be able to do is use a repeating section or repeating table to receive data from a secondary data source and then structure this data and have the ability to have the field names at the top of each row in the table.

Both options seem floored:

repeating table: this method seems ok at first but it doesn’t seem possible to have the field names at the top of each row, which is a requirement. 

repeating section: this does include the field names for each row but for some reason will not allow me to structure the fields in a nested table, so its messy.

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend