Temp tables streamline query

Question

I am trying to get to the Customer table from data in the SiteConfiguration table.

I am getting the data and setting it to @AdditionalVoteViewers, which would be a string of emails address. I then split the string and insert the results into a temp table.

At that point, I will join the temp table with the Customer table to return the CustomerId.

I just want to see if there is a better way to do this.

Data In SiteConfiguration Table

[email protected];[email protected]

Data in Customer table

[email protected] 1
[email protected] 2

Code:

declare @AdditionalVoteViewers VARCHAR(100) 
Select @AdditionalVoteViewers = 
(
    SELECT [Value] FROM [SiteConfiguration] 
    where Name = 'AdditionalVoteViewers'
)
print @AdditionalVoteViewers
--CREATE TABLE #TempAdditionalVoteViewers(email  varchar(MAX) )

select * into #TempAdditionalVoteViewers 
from [dbo].[Split] (@AdditionalVoteViewers,';')
--select * from #TempAdditionalVoteViewers

select [CustomerId] into #TempAdditionalVoteViewersId
from [dbo].[Customer] c 
join #TempAdditionalVoteViewers t on c.Email = t.val


select * from #TempAdditionalVoteViewersId

Answer 1

it's fine

You're doing things correctly, in that you're not splitting the string in your query and joining to the results. That is often a recipe for performance disaster.

If I could make one suggestion, it would to make sure either the temp table creation statement or the select into converts the email column into a data type and length that matches the Email column in the Customer table.

In your example code, you're doing this:

--CREATE TABLE #TempAdditionalVoteViewers(email  varchar(MAX) )

select * into #TempAdditionalVoteViewers from [dbo].[Split] (@AdditionalVoteViewers,';')

Using max data types for either columns, or parameters/variables will result in you not being able to seek into any useful indexes you may have. I have no idea what the code for your Split function does, but most of the ones I've seen copy and pasted from 20+ year old SQL Server blogs are meant to be generic, and often return ridiculous output types.