Mehedi's Earth

Last week, I was discussing with one of my junior colleagues. He needed to add thousands phone numbers from a web form to database. He was looking for an efficient way. I told him to use Table-Valued Parameters.

Table-Valued Parameters

Table-valued parameters (TVP) enables you to send thousands of data to database at a time. For this you need to declare user-defined table types. Then you can send multiple rows of data to T-SQL statement, stored procedure or function.

Step-1

Let's create a user defined table type PhoneNumberType.

CREATE TYPE dbo.PhoneNumberType AS TABLE
(
    [phone_number] VARCHAR(15)
)

Step-2

Create table which will store the phone numbers.

CREATE TABLE [dbo].[PhoneNumbers](
    [pk_id] [int] IDENTITY(1,1) NOT NULL,
    [phone_number] [varchar](15) NULL,
 CONSTRAINT [PK_PhoneNumbers] PRIMARY KEY CLUSTERED
(
    [pk_id] ASC
)
) ON [PRIMARY]
GO

Step-3

Now we will create stored procedure and pass TVP as parameter.

CREATE PROCEDURE dbo.AddPhoneNumbers
    @phone [dbo].[PhoneNumberType] READONLY
AS
BEGIN
    INSERT INTO PhoneNumbers(phone_number)
    SELECT phone_number
    FROM @phone
END

Step-4

Following C# code snippets will generate 100,000 rows and pass it through TVP.

            DataTable dataTable = new DataTable();

            dataTable.Columns.Add("phone_number", typeof(string));

            // Populate the dataTable with values …

            for(int i=1;i<100000;i++)
            {
                DataRow dr = dataTable.NewRow();
                dr["phone_number"] = String.Format("{0:000000}", i);
                dataTable.Rows.Add(dr);
            }    

            using (var con = new SqlConnection(@"Your Connection String"))
            {
                con.Open();

                using (var cmd = new SqlCommand("dbo.AddPhoneNumbers", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@phone", SqlDbType.Structured) { Value = dataTable });
                    cmd.ExecuteNonQuery();
                }

                con.Close();
            }

Benefits

Table-valued parameters have many benefits such as:

• It provides an easy way to send multiple rows of data from a client application to SQL Server at a time without multiple round trips
• No special server-side logic is required for processing the data
• The incoming data stored in TVP variable that can then be operated on by using T-SQL
• Table-valued parameters are strongly typed and their structure is automatically validated
• It has higher performance gain over multiple round trips queries

Conclusion

Considering the ease of use and benefits, Table-valued parameters are great option for many use cases. Happy T-SQLing!