Stored Procedure Performance of Table Valued Parameters vs STRING_SPLIT

As soon as I finished working with a bulk upsert stored procedure using table valued parameters, my brain jumped to "What about if it's just a list of string values? Would it be simpler to just use STRING_SPLIT?" OK, thanks brain, now I need to spend some time on that or I won't sleep well tonight.

Setup was simple, created a stored procedure for each approach that takes the input of a DisplayName list, and queries the StackOverflow2010 database Users table for those records. I also added an index on the DisplayName to avoid a table scan.

Test Query: 

USE [StackOverflow2010]

GO

SET STATISTICS IO ON

GO

DECLARE @DisplayNamesString nvarchar(max) = 'Jeff Atwood,user262577,Alberto A. Medina';

DECLARE @DisplayNamesTVP [dbo].[StringSplitTestTVP];

INSERT INTO @DisplayNamesTVP VALUES 

('Jeff Atwood'),

('user262577'),

('Alberto A. Medina');

EXECUTE [dbo].[GetUsersByDisplayNameUsingStringSplit] 

   @DisplayNamesString

EXECUTE [dbo].[GetUsersByDisplayNameUsingTVP] 

   @DisplayNamesTVP

Results? Both methods are fast and return the correct values, but the TVP shows a query cost that's a tenth of the STRING_SPLIT, and has better row estimations. It also has slightly fewer logical reads, but that may be offset by the logical reads inserting into the TVP. I guess the moral of the story is that both approaches are good, but if I ever need to implement in the real world I'm still going with the TVP approach.

Table Valued Parameter:

USE [StackOverflow2010]

GO

/****** Object:  Table Valued Parameter [dbo].[StringSplitTestTVP] ******/

CREATE TYPE [dbo].[StringSplitTestTVP] AS TABLE(

[DisplayName] [nvarchar](40) NOT NULL

)

GO

TVP Stored Procedure: 

USE [StackOverflow2010]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: Tom Barrett

-- Create date: 2022-12-09

-- Description: Gets Users by DisplayName using table valued parameter

-- =============================================

CREATE OR ALTER PROCEDURE [dbo].[GetUsersByDisplayNameUsingTVP] 

@DisplayNames [StringSplitTestTVP] READONLY

AS

BEGIN

/* SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements. */

SET NOCOUNT ON;

    SELECT [Id], [AboutMe], [Age], [CreationDate], [u].[DisplayName], [DownVotes], [EmailHash], [LastAccessDate], [Location], [Reputation], [UpVotes], [Views], [WebsiteUrl], [AccountId]

FROM [dbo].[Users] AS [u]

JOIN @DisplayNames AS [dn] ON [u].[DisplayName] = [dn].[DisplayName]

END

GO

STRING_SPLIT Stored Procedure: 

USE [StackOverflow2010]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: Tom Barrett

-- Create date: 2022-12-09

-- Description: Gets Users by DisplayName using string split.

-- =============================================

CREATE OR ALTER PROCEDURE [dbo].[GetUsersByDisplayNameUsingStringSplit] 

@DisplayNames NVARCHAR(MAX) = ''

AS

BEGIN

/* SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements. */

SET NOCOUNT ON;

    SELECT [Id], [AboutMe], [Age], [CreationDate], [u].[DisplayName], [DownVotes], [EmailHash], [LastAccessDate], [Location], [Reputation], [UpVotes], [Views], [WebsiteUrl], [AccountId]

FROM [dbo].[Users] AS [u]

JOIN STRING_SPLIT(@DisplayNames, ',') AS [dn] ON [u].[DisplayName] = [dn].[value]

END

GO