Going back to the original problem and considering how fast the solution turned out to be to do that, I don't believe that I go through setting up FTS for...

You could be right but the FTI wouldn't be on Table 2 according to the OPs latest post. It would only be needed on Table 1. Fortunately, I don't see...

At this point, you're better off just creating a full text index on table2 and searching it. You can join it to table2 and perform LIKE '%a%' on the result...

This makes use of Moden's DelimitedSplit8k which you can download in the scripts section.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION tvf_ChildRowNames

(

@rowName varchar(250)

)

RETURNS TABLE

AS

RETURN

(

SELECT name from table2 where exists...

Can you try this and see what takes the longest?

I'm guessing it's the FT search. Maybe if it gets narrowed down before doing that it will help somehow.

declare @tbl1 table...

Personally I prefer an inline TVF for my 'parameterized views', like so

CREATE FUNCTION tvf_report

(

-- Add the parameters for the function here

@ShiftDepartment varchar(100),

@Date1 datetime,

@Date2 datetime

)

RETURNS TABLE

AS

RETURN

(

select VwQPPMaterialRptPart1.Material,...

Not to be rude, but do you suppose there might be a reason I suggested you create that particular index? And that creating it, testing and perhaps dropping it might...

Did you create the covering index I suggested?

CREATE NONCLUSTERED INDEX [ix_new] ON [dbo].[SVSales]

(

[INum] ASC,

[ProdNum] ASC

)

INCLUDE ( [FullAccNum])

You might have to also include the OpenDate column if it's in SVSales.

The gauntlet, she has been thrown...

Would a shrink maybe put more of the database into the cache?

Have any of the queries changed? Is there maybe an unindexed column in a where clause now? Maybe one index needs a covering column added?

datetime in v1 was definitely crap. v2 should be better though. That wasn't really my point though - my point is that all the...

declare @i int,@done bit

select @i = 0, @done = false

while i<10

begin

try something

observe effects

modify approach

if...

Which is to say, what CAN'T you do in an inline tvf if you really really want to?