SQL Server 2008 - Table Valued Parameters vs XML vs CSV

The scenario is, you want to create a stored procedure that returns the records relating to a finite set of keys/IDs that you pass in to it. How do you do it? Well you could use dynamic SQL, but for the purpose of this article I’m going to ignore that option. Test scenario -- Create basic customer table CREATETABLE[dbo].[Customer](IdINTEGERIDENTITY(1,1)PRIMARYKEY,NameNVARCHAR(50)NOTNULL);GO-- Populate Customer table with 100,000 sample records DECLARE@CounterINTEGERSET@Counter=1WHILE(@Counter<=100000)BEGININSERTCustomer(Name)VALUES('Test Customer #'+CAST(@CounterASVARCHAR(10)))SET@Counter=@Counter+1ENDOption 1 - CSV list of keys In SQL Server 2000, you would most likely have ended up passing in a CSV list of keys, splitting that out into a table, and then joining that on to your real data table to match the records to return. [Read More]