SQLTeam.com | Weblogs | Forums

Added FT Index to sproc now result set is different


#1

There is a text column that can (and is actually) taking advantage of a FT Index. I deployed and tested the change on our Dev Box. Ran old and new sproc (pretty much same logic, besides the CONTAINS) and now I get more rows with the FT Index.

Is that normal?

I honestly remember a similar improvement on a different sproc a few years ago but it seems the result set was consistent but can't remember.


#2

let's see the old and new


#3

Old sproc

CREATE PROCEDURE [dbo].[OLD]
	@CompanyID int, 
	@MultiCompanyID int, 
	@Billable int, 
	@AssignedUserID int, 
	@QueueID int, 
	@TicketStatusID int, 
	@StartDate smalldatetime = null, 
	@EndDate smalldatetime = null, 
	@Keyword varchar(50) = '', 
	@ProjectID int = -1, 
	@TechID int = -1, 
	@OpenClosed int = 0, 
	@IssueID int =-1,
	@CreatedByID int = -1,
	@TicketName varchar(50) = '',
	@FilteredKeyword varchar(50) = '',
	@MinutesWorked int = null,
	@SubscribedUserID int = -1,
	@CPMStatus int = 0,
	@TicketRecurOn BIT
AS
SET NOCOUNT ON


DECLARE
	@iCompanyID int, 
	@iMultiCompanyID int, 
	@iBillable int, 
	@iAssignedUserID int, 
	@iQueueID int, 
	@iTicketStatusID int, 
	@iStartDate date,-- = null, 
	@iEndDate date,-- = null, 
	@iProjectID int,-- = -1, 
	@iTechID int,-- = -1, 
    @iKeyword varchar(52),
	@iOpenClosed int  = 3, 
	@iIssueID int,-- =-1,
	@iCreatedByID int,-- = -1
    @iTicketName varchar(50),
    @iFilteredKeyword varchar(50),
    @iMinutesWorked int,
	@iSubscribedUserID int,
	@iCPMStatus int

SET @iCompanyID = @CompanyID
SET @iMultiCompanyID = @MultiCompanyID
SET @iBillable = @Billable
SET @iAssignedUserID = @AssignedUserID
SET @iQueueID = @QueueID
SET @iTicketStatusID = @TicketStatusID
SET @iStartDate = Cast(@StartDate as Date)
SET @iEndDate = Cast(@EndDate as Date)
SET @iProjectID = @ProjectID
SET @iTechID = @TechID
SET @iIssueID = @IssueID
SET @iOpenClosed = @OpenClosed
SET @iCreatedByID = @CreatedByID
SET @iTicketName = @TicketName
SET @iFilteredKeyword = @FilteredKeyword
SET @iMinutesWorked = @MinutesWorked
SET @iSubscribedUserID = @SubscribedUserID
SET @iCPMStatus = @CPMStatus

DECLARE @TextKeyword varchar(54)
DECLARE @ResetSearch bit 

If LTRIM(RTRIM(@Keyword)) <> ''
BEGIN
	SET @iKeyword = '%' + LTRIM(RTRIM(@Keyword)) + '%'
	SET @TextKeyword = '"' + LTRIM(RTRIM(@Keyword)) + '"' 
	Set @ResetSearch = 1
END
ELSE
BEGIN
	SET @iKeyword = '%' + LTRIM(RTRIM(@iFilteredKeyword)) + '%'
	SET @TextKeyword = '"' + LTRIM(RTRIM(@iFilteredKeyword)) + '"' 
	Set @ResetSearch = 0
END

SET @iTicketName = LTRIM(RTRIM(@iTicketName))

DECLARE @temp TABLE
    (
		ID INT PRIMARY KEY IDENTITY(1,1),
        TID  INT,
        UNIQUE CLUSTERED (ID,TID)
    )

DECLARE @Results TABLE
    (
		ID INT PRIMARY KEY IDENTITY(1,1),
        TID  INT,
        UNIQUE CLUSTERED (ID,TID)
    )


If @iTicketName <> ''
BEGIN
	--Only Search TicketName
	INSERT INTO @Results (TID)
		SELECT TicketID 
		FROM Ticket 
		WHERE (Name = @iTicketName)
			AND DateDeactivated is null 
		GROUP BY TicketID
END
ELSE If @ResetSearch = 1 
BEGIN
	--Only Search for the keywords
	INSERT INTO @Results (TID)
		SELECT TicketID 
		FROM Entry 
		WHERE Notes like @iKeyword 
			AND DateDeactivated is null 
		GROUP BY TicketID
	
	INSERT INTO @Results (TID)
		SELECT TicketID 
		FROM Note 
		WHERE Note like @iKeyword 
			AND DateDeactivated is null 
		GROUP BY TicketID
		
	INSERT INTO @Results (TID)
		SELECT TicketID 
		FROM Ticket 
		WHERE (Name like @iKeyword OR Title like @iKeyword OR CONTAINS(Description,@TextKeyword) )
			AND DateDeactivated is null 
		GROUP BY TicketID	
End	
ELSE If @iSubscribedUserID <> -1 
BEGIN
	--Only Search for Subscribed tickets
	INSERT INTO @Results (TID)
		SELECT t.TicketID 
		FROM Ticket t
			INNER JOIN TicketSubscriber ts on t.TicketID = ts.TicketID AND ts.UserID = @iSubscribedUserID
		WHERE DateDeactivated is null AND t.TicketStatusID <> 8
		GROUP BY t.TicketID	
End	
ELSE If @iCPMStatus <> 0 
BEGIN
	--Only Search for ticket that have CPM status "pending review" or "reviewed"
	INSERT INTO @Results (TID)
		SELECT t.TicketID 
		FROM Ticket t
		WHERE DateDeactivated is null
			AND t.CPMReviewStatus = @CPMStatus
		GROUP BY t.TicketID	
End
ELSE IF @TicketRecurOn = 1
BEGIN
	INSERT INTO @ResultS(TID)
		SELECT T.TicketID
		FROM Ticket T
		INNER JOIN TicketRecur TR ON T.TicketID = TR.SourceTicketID
		WHERE T.DateDeactivated IS NULL AND T.TicketStatusID = @iTicketStatusID
		GROUP BY T.TicketID
END		
Else
Begin	
	--Filter the Tickets first
	Insert into @temp  (TID)
		Select T.TicketID
		From Ticket T
			INNER JOIN Company C on C.CompanyID = T.CompanyID
			Left Join @temp KeywordTicket on T.TicketID = KeywordTicket.TID 
		WHERE
			((@iCompanyID=-1) OR (T.CompanyID = @iCompanyID)) 
			AND ((@iMultiCompanyID=-1) OR (C.MultiCompanyID = @iMultiCompanyID)) 
			AND ((@iBillable=-1) OR (T.Billable = @iBillable) OR ((@iBillable=2) AND (T.Billable=1) AND (T.Billed=0))) 
			AND ((@iAssignedUserID=-1) 
					OR ((@iAssignedUserID=0) AND (T.AssignedUserID is null)) 
					OR ((@iAssignedUserID=-2) AND (T.AssignedUserID is not null)) 
					OR (T.AssignedUserID = @iAssignedUserID)) 
			AND ((@iQueueID=-1 AND T.QueueID <> 37) OR (T.QueueID = @iQueueID)) 
			AND ((@iTechID=-1) OR (T.TechID = @iTechID)) 
			AND ((@iProjectID=-1) OR (T.ProjectID = @iProjectID)) 
			AND (((@iTicketStatusID=-1) AND (T.TicketStatusID <> 8)) OR (@iTicketStatusID=-2) OR (T.TicketStatusID = @iTicketStatusID)) 
			AND T.DateDeactivated is null
			AND ((@iStartDate is null) OR ((@iOpenClosed=0) AND (T.DateActivated >= @iStartDate)) OR ((@iOpenClosed=1) AND (T.CompletedDate >= @iStartDate)) OR ((@iOpenClosed=2) AND (T.DueDate >= @iStartDate)) )
			AND ((@iEndDate is null) OR ((@iOpenClosed=0) AND (T.DateActivated <= @iEndDate)) OR ((@iOpenClosed=1) AND (T.CompletedDate <= @iEndDate)) OR ((@iOpenClosed=2) AND (T.DueDate <= @iEndDate)))
			AND ((@iIssueID=-1) OR (T.IssueID=@iIssueID))
			AND ((@iCreatedByID = -1) OR (T.CreatedByUserID = @iCreatedByID))
		Group By T.TicketID
	--Backup Users Assigned
	Insert into @temp  (TID)
		Select T.TicketID
		From Ticket T
			INNER JOIN TicketBackupUser TBU on TBU.TicketID = T.TicketID and TBU.UserID = @iAssignedUserID
			INNER JOIN Company C on C.CompanyID = T.CompanyID
		WHERE
			((@iCompanyID=-1) OR (T.CompanyID = @iCompanyID)) 
			AND ((@iMultiCompanyID=-1) OR (C.MultiCompanyID = @iMultiCompanyID)) 
			AND ((@iBillable=-1) OR (T.Billable = @iBillable) OR ((@iBillable=2) AND (T.Billable=1) AND (T.Billed=0))) 
			AND ((@iQueueID=-1 AND T.QueueID <> 37) OR (T.QueueID = @iQueueID)) 
			AND ((@iTechID=-1) OR (T.TechID = @iTechID)) 
			AND ((@iProjectID=-1) OR (T.ProjectID = @iProjectID)) 
			-- ONLY SHOW NEW TICKETS
			AND (T.TicketStatusID=1)
			AND T.DateDeactivated is null
			AND ((@iStartDate is null) OR ((@iOpenClosed=0) AND (T.DateActivated >= @iStartDate)) OR ((@iOpenClosed=1) AND (T.CompletedDate >= @iStartDate)) OR ((@iOpenClosed=2) AND (T.DueDate >= @iStartDate)) )
			AND ((@iEndDate is null) OR ((@iOpenClosed=0) AND (T.DateActivated <= @iEndDate)) OR ((@iOpenClosed=1) AND (T.CompletedDate <= @iEndDate)) OR ((@iOpenClosed=2) AND (T.DueDate <= @iEndDate)))
			AND ((@iIssueID=-1) OR (T.IssueID=@iIssueID))
			AND ((@iCreatedByID = -1) OR (T.CreatedByUserID = @iCreatedByID))
		Group By T.TicketID

	IF @iKeyword <> '%%'
	BEGIN
		INSERT INTO @Results (TID)
			SELECT TicketID 
			FROM [Entry] 
				INNER JOIN @temp T on T.TID = [Entry].TicketID 
			WHERE Notes like @iKeyword 
				AND DateDeactivated is null 
			GROUP BY TicketID
		
		INSERT INTO @Results (TID)
			SELECT TicketID 
			FROM Note 
				INNER JOIN @temp T on T.TID = Note.TicketID 
			WHERE Note like @iKeyword 
				AND DateDeactivated is null 
			GROUP BY TicketID
		
		INSERT INTO @Results (TID)
			SELECT TicketID 
			FROM Ticket
				INNER JOIN @temp T on T.TID = Ticket.TicketID 
			WHERE (Name like @iKeyword OR Title like @iKeyword OR CONTAINS(Description,@TextKeyword) )
				AND DateDeactivated is null 
			GROUP BY TicketID		
	END
	Else
	BEGIN
		INSERT INTO @Results (TID)
			SELECT TID 
			From @temp
	END
END

DECLARE @ResultsUnique TABLE(TID INT PRIMARY KEY)
INSERT INTO @ResultsUnique 
SELECT DISTINCT TID FROM @Results

SELECT T.*, 
	TS.Name as TicketStatusName, TS.SortOrder as TicketStatusSortOrder, 
	C.Name as CompanyName, 
	ISNULL(MC.Name, 'Ctuit') as MultiCompanyName, 
	ISNULL(U.DisplayName, '(Unassigned)') as AssignedUser, 
	Q.Name as QueueName, 
	CASE WHEN ISNULL(T.PublicTicket, 0) = 0 or t.Ranking is null THEN TP.Name ELSE ISNULL(CONVERT(VARCHAR, t.Ranking),'') + ' - ' + TP.Name END as PriorityName, 
	ISNULL(U_tech.DisplayName,'') as TechName, 
	"HoursWorked" = ISNULL((SELECT SUM(ISNULL(dbo.fnSafeDiv(((ceiling(DATEDIFF(n, StartTime, EndTime)/5.0))*5.0), 60.0), 0)) 
					    FROM Entry E 
					    WHERE E.TicketID = T.TicketID and E.DateDeactivated is null), 0), 
	"IsBackup" = 0, 
	"OtherBillableTickets" = (SELECT COUNT(*) 
							  FROM Ticket iT 
							  WHERE iT.TicketStatusID <> 8 
								and iT.DateDeactivated is null
								and iT.CompanyID = T.CompanyID
								AND iT.Billable=1
								and iT.TicketID <> T.TicketID)
	,C.SupportType
	,"MajorSort" = CASE WHEN T.IsClientDriven = 1 THEN 1 ELSE 2 END
FROM @ResultsUnique SelectedTickets
	INNER JOIN Ticket T on T.TicketID = SelectedTickets.TID
	INNER JOIN Company C on C.CompanyID = T.CompanyID
	LEFT JOIN MultiCompany MC on C.MultiCompanyID = MC.MultiCompanyID
	INNER JOIN TicketStatus TS on TS.TicketStatusID = T.TicketStatusID
	INNER JOIN TicketPriority TP on TP.TicketPriorityID = T.TicketPriorityID
	LEFT JOIN Users U on U.UserID = T.AssignedUserID
	INNER JOIN Queue Q on Q.QueueID = T.QueueID
	LEFT JOIN Users U_tech on U_tech.UserID = T.TechID
WHERE
	((@iMinutesWorked is null) OR (ISNULL((SELECT SUM(ISNULL(DATEDIFF(n, StartTime, EndTime), 0)) 
					    FROM Entry E 
					    WHERE E.TicketID = T.TicketID and E.DateDeactivated is null), 0) >= (@iMinutesWorked)))
ORDER BY T.IsClientDriven DESC, T.Name DESC

New version

ALTER PROCEDURE [dbo].[NEW]
	@CompanyID int, 
	@MultiCompanyID int, 
	@Billable int, 
	@AssignedUserID int, 
	@QueueID int, 
	@TicketStatusID int, 
	@StartDate smalldatetime = null, 
	@EndDate smalldatetime = null, 
	@Keyword varchar(50) = '', 
	@ProjectID int = -1, 
	@TechID int = -1, 
	@OpenClosed int = 0, 
	@IssueID int =-1,
	@CreatedByID int = -1,
	@TicketName varchar(50) = '',
	@FilteredKeyword varchar(50) = '',
	@MinutesWorked int = null,
	@SubscribedUserID int = -1,
	@CPMStatus int = 0,
	@TicketRecurOn BIT
AS
SET NOCOUNT ON



DECLARE
	@iCompanyID int, 
	@iMultiCompanyID int, 
	@iBillable int, 
	@iAssignedUserID int, 
	@iQueueID int, 
	@iTicketStatusID int, 
	@iStartDate date,-- = null, 
	@iEndDate date,-- = null, 
	@iProjectID int,-- = -1, 
	@iTechID int,-- = -1, 
    @iKeyword varchar(52),
	@iOpenClosed int  = 3, 
	@iIssueID int,-- =-1,
	@iCreatedByID int,-- = -1
    @iTicketName varchar(50),
    @iFilteredKeyword varchar(50),
    @iMinutesWorked int,
	@iSubscribedUserID int,
	@iCPMStatus int

SET @iCompanyID = @CompanyID
SET @iMultiCompanyID = @MultiCompanyID
SET @iBillable = @Billable
SET @iAssignedUserID = @AssignedUserID
SET @iQueueID = @QueueID
SET @iTicketStatusID = @TicketStatusID
SET @iStartDate = Cast(@StartDate as Date)
SET @iEndDate = Cast(@EndDate as Date)
SET @iProjectID = @ProjectID
SET @iTechID = @TechID
SET @iIssueID = @IssueID
SET @iOpenClosed = @OpenClosed
SET @iCreatedByID = @CreatedByID
SET @iTicketName = @TicketName
SET @iFilteredKeyword = @FilteredKeyword
SET @iMinutesWorked = @MinutesWorked
SET @iSubscribedUserID = @SubscribedUserID
SET @iCPMStatus = @CPMStatus

DECLARE @TextKeyword varchar(54)
DECLARE @ResetSearch bit 

If LTRIM(RTRIM(@Keyword)) <> ''
BEGIN
	--SET @iKeyword = '%' + LTRIM(RTRIM(@Keyword)) + '%'
	SET @iKeyword = '"%"' + LTRIM(RTRIM(@Keyword)) + '"%"' 
	SET @TextKeyword = '"' + LTRIM(RTRIM(@Keyword)) + '"' 
	Set @ResetSearch = 1
END
ELSE
BEGIN
	SET @iKeyword = '"%"' + LTRIM(RTRIM(@iFilteredKeyword)) + '"%"'
	SET @TextKeyword = '"' + LTRIM(RTRIM(@iFilteredKeyword)) + '"' 
	Set @ResetSearch = 0
END

SET @iTicketName = LTRIM(RTRIM(@iTicketName))

--CB: If the user included the quotes already then replace the doubled double quote with single double quote
SET @TextKeyword = Replace(@TextKeyword,'""','"')
--CB: The user will be including quotes in the string, so it will break iKeyword, replacing those out.
SET @iKeyword = Replace(@iKeyword,'"','')

CREATE TABLE #temp
    (
		ID INT PRIMARY KEY IDENTITY(1,1),
        TID  INT,
        UNIQUE CLUSTERED (ID,TID)
    )

--DECLARE @Results TABLE
--    (
--		ID INT PRIMARY KEY IDENTITY(1,1),
--        TID  INT,
--        UNIQUE CLUSTERED (ID,TID)
--    )


IF object_id('tempdb.dbo.#Results', 'u') IS NOT NULL
	DROP TABLE #Results
CREATE TABLE #Results
    (
		ID INT IDENTITY(1,1),
        TID  INT
    )


If @iTicketName <> ''
BEGIN
	--Only Search TicketName
	INSERT INTO #Results (TID)  
		SELECT TicketID 
		FROM dbo.Ticket 
		WHERE Name = @iTicketName
			AND DateDeactivated is null 
		GROUP BY TicketID
END
ELSE If @ResetSearch = 1 
BEGIN
	--Only Search for the keywords
	INSERT INTO #Results (TID)  
		SELECT TicketID 
		FROM dbo.[Entry]
		WHERE CONTAINS(Notes,@TextKeyword)
		--WHERE Notes like @iKeyword 
			AND DateDeactivated is null 
		GROUP BY TicketID
	
	INSERT INTO #Results (TID)  
		SELECT TicketID 
		FROM dbo.Note 
		--WHERE Note like @iKeyword 
		WHERE CONTAINS(Note, @TextKeyword)
			AND DateDeactivated is null 
		GROUP BY TicketID
		
	INSERT INTO #Results (TID)  
		SELECT TicketID 
		FROM dbo.Ticket 
		--Using the text keyword in all contains clauses. The likeness is handled there.
		WHERE (Name like @iKeyword OR CONTAINS(Title,@TextKeyword) OR CONTAINS(Description,@TextKeyword) )
			AND DateDeactivated is null 
		GROUP BY TicketID	
End	
ELSE If @iSubscribedUserID <> -1 
BEGIN
	--Only Search for Subscribed tickets
	INSERT INTO #Results (TID)  
		SELECT t.TicketID 
		FROM dbo.Ticket t
			INNER JOIN dbo.TicketSubscriber ts on t.TicketID = ts.TicketID AND ts.UserID = @iSubscribedUserID
		WHERE DateDeactivated is null AND t.TicketStatusID <> 8
		GROUP BY t.TicketID	
End	
ELSE If @iCPMStatus <> 0 
BEGIN
	--Only Search for ticket that have CPM status "pending review" or "reviewed"
	INSERT INTO #Results (TID)  
		SELECT t.TicketID 
		FROM dbo.Ticket t
		WHERE DateDeactivated is null
			AND t.CPMReviewStatus = @CPMStatus
		GROUP BY t.TicketID	
End	
ELSE IF @TicketRecurOn = 1
BEGIN
	INSERT INTO #ResultS(TID)
		SELECT T.TicketID
		FROM Ticket T
		INNER JOIN TicketRecur TR ON T.TicketID = TR.SourceTicketID
		WHERE T.DateDeactivated IS NULL AND T.TicketStatusID = @iTicketStatusID
		GROUP BY T.TicketID
END		
Else
Begin	
	--Filter the Tickets first
	Insert into #temp  (TID)
		Select T.TicketID
		From dbo.Ticket T
			INNER JOIN dbo.Company C on C.CompanyID = T.CompanyID
			Left Join #temp KeywordTicket on T.TicketID = KeywordTicket.TID
		WHERE
			((@iCompanyID=-1) OR (T.CompanyID = @iCompanyID)) 
			AND ((@iMultiCompanyID=-1) OR (C.MultiCompanyID = @iMultiCompanyID)) 
			AND ((@iBillable=-1) OR (T.Billable = @iBillable) OR ((@iBillable=2) AND (T.Billable=1) AND (T.Billed=0))) 
			AND ((@iAssignedUserID=-1) 
					OR ((@iAssignedUserID=0) AND (T.AssignedUserID is null)) 
					OR ((@iAssignedUserID=-2) AND (T.AssignedUserID is not null)) 
					OR (T.AssignedUserID = @iAssignedUserID)) 
			AND ((@iQueueID=-1 AND T.QueueID <> 37) OR (T.QueueID = @iQueueID)) 
			AND ((@iTechID=-1) OR (T.TechID = @iTechID)) 
			AND ((@iProjectID=-1) OR (T.ProjectID = @iProjectID)) 
			AND (((@iTicketStatusID=-1) AND (T.TicketStatusID <> 8)) OR (@iTicketStatusID=-2) OR (T.TicketStatusID = @iTicketStatusID)) 
			AND T.DateDeactivated is null
			AND ((@iStartDate is null) OR ((@iOpenClosed=0) AND (T.DateActivated >= @iStartDate)) OR ((@iOpenClosed=1) AND (T.CompletedDate >= @iStartDate)) OR ((@iOpenClosed=2) AND (T.DueDate >= @iStartDate)) )
			AND ((@iEndDate is null) OR ((@iOpenClosed=0) AND (T.DateActivated <= @iEndDate)) OR ((@iOpenClosed=1) AND (T.CompletedDate <= @iEndDate)) OR ((@iOpenClosed=2) AND (T.DueDate <= @iEndDate)))
			AND ((@iIssueID=-1) OR (T.IssueID=@iIssueID))
			AND ((@iCreatedByID = -1) OR (T.CreatedByUserID = @iCreatedByID))
		Group By T.TicketID
	--Backup Users Assigned
	Insert into #temp  (TID)
		Select T.TicketID
		From dbo.Ticket T
			INNER JOIN dbo.TicketBackupUser TBU on TBU.TicketID = T.TicketID and TBU.UserID = @iAssignedUserID
			INNER JOIN dbo.Company C on C.CompanyID = T.CompanyID
		WHERE
			((@iCompanyID=-1) OR (T.CompanyID = @iCompanyID)) 
			AND ((@iMultiCompanyID=-1) OR (C.MultiCompanyID = @iMultiCompanyID)) 
			AND ((@iBillable=-1) OR (T.Billable = @iBillable) OR ((@iBillable=2) AND (T.Billable=1) AND (T.Billed=0))) 
			AND ((@iQueueID=-1 AND T.QueueID <> 37) OR (T.QueueID = @iQueueID)) 
			AND ((@iTechID=-1) OR (T.TechID = @iTechID)) 
			AND ((@iProjectID=-1) OR (T.ProjectID = @iProjectID)) 
			-- ONLY SHOW NEW TICKETS
			AND (T.TicketStatusID=1)
			AND T.DateDeactivated is null
			AND ((@iStartDate is null) OR ((@iOpenClosed=0) AND (T.DateActivated >= @iStartDate)) OR ((@iOpenClosed=1) AND (T.CompletedDate >= @iStartDate)) OR ((@iOpenClosed=2) AND (T.DueDate >= @iStartDate)) )
			AND ((@iEndDate is null) OR ((@iOpenClosed=0) AND (T.DateActivated <= @iEndDate)) OR ((@iOpenClosed=1) AND (T.CompletedDate <= @iEndDate)) OR ((@iOpenClosed=2) AND (T.DueDate <= @iEndDate)))
			AND ((@iIssueID=-1) OR (T.IssueID=@iIssueID))
			AND ((@iCreatedByID = -1) OR (T.CreatedByUserID = @iCreatedByID))
		Group By T.TicketID

	IF @iKeyword <> '%%'
	BEGIN
		INSERT INTO #Results (TID)  
			SELECT TicketID 
			FROM dbo.[Entry] 
				INNER JOIN #temp T on T.TID = [Entry].TicketID 
			--WHERE Notes like @iKeyword 
			WHERE CONTAINS(Notes,@TextKeyword)
				AND DateDeactivated is null 
			GROUP BY TicketID
		
		INSERT INTO #Results (TID)  
			SELECT TicketID 
			FROM dbo.Note 
				INNER JOIN #temp T on T.TID = Note.TicketID 
			--WHERE Note like @iKeyword 
			WHERE CONTAINS(Note, @TextKeyword)
				AND DateDeactivated is null 
			GROUP BY TicketID
		
		INSERT INTO #Results (TID)  
			SELECT TicketID 
			FROM dbo.Ticket
				INNER JOIN #temp T on T.TID = Ticket.TicketID 
			--WHERE (Name like @iKeyword OR Title like @iKeyword OR CONTAINS(Description,@TextKeyword) )
			WHERE (Name like @iKeyword OR CONTAINS(Title, @TextKeyword) OR CONTAINS(Description,@TextKeyword) )
				AND DateDeactivated is null 
			GROUP BY TicketID		
	END
	Else
	BEGIN
		INSERT INTO #Results (TID)  
			SELECT TID 
			From #temp
	END
END

--DECLARE @ResultsUnique TABLE(TID INT PRIMARY KEY)

IF object_id('tempdb.dbo.#ResultsUnique', 'u') IS NOT NULL
	DROP TABLE #ResultsUnique
CREATE TABLE #ResultsUnique
    (TID INT)
INSERT INTO #ResultsUnique 
SELECT DISTINCT TID FROM #Results 

SELECT T.TicketID, T.Name, T.CompanyID, T.ContactID, T.Title, T.[Description], T.QueueID, T.AssignedUserID, T.TicketPriorityID, T.TicketStatusID, T.DueDate, T.IssueID, T.SubIssueID, T.Billable, T.QuotedPrice,
	T.QuotedHour, T.AuthorizedByContactID, T.Billed, T.DateActivated, T.DateDeactivated, T.TicketSourceID, T.CreatedByUserID, T.LastModifiedByUserID, T.CompletedDate, T.ProjectID, T.TechID, T.AmountBilled,
	T.BilledCode, T.BilledNotes, T.Comped, T.CompReason, T.CompAmount, T.Ranking, T.PublicTicket, T.DateApproved, T.BillingCompanyID, T.InvoiceNumber, T.DeveloperID, T.IsClientDriven, T.CPMReviewStatus,
	T.CPMReviewDate, T.CPMReviewUserID, 
	TS.Name as TicketStatusName, TS.SortOrder as TicketStatusSortOrder, 
	C.Name as CompanyName, 
	ISNULL(MC.Name, 'Ctuit') as MultiCompanyName, 
	ISNULL(U.DisplayName, '(Unassigned)') as AssignedUser, 
	Q.Name as QueueName, 
	CASE WHEN ISNULL(T.PublicTicket, 0) = 0 or t.Ranking is null THEN TP.Name ELSE ISNULL(CONVERT(VARCHAR, t.Ranking),'') + ' - ' + TP.Name END as PriorityName, 
	ISNULL(U_tech.DisplayName,'') as TechName, 
	"HoursWorked" = ISNULL(HoursWorked.HoursWorked, 0), 
	"IsBackup" = 0, 
	"OtherBillableTickets" = OtherBillableTickets.OtherBillableTickets
	,C.SupportType
	,"MajorSort" = CASE WHEN T.IsClientDriven = 1 THEN 1 ELSE 2 END
FROM #ResultsUnique SelectedTickets
	INNER JOIN dbo.Ticket T on T.TicketID = SelectedTickets.TID
	INNER JOIN dbo.Company C on C.CompanyID = T.CompanyID
	LEFT JOIN dbo.MultiCompany MC on C.MultiCompanyID = MC.MultiCompanyID
	INNER JOIN dbo.TicketStatus TS on TS.TicketStatusID = T.TicketStatusID
	INNER JOIN dbo.TicketPriority TP on TP.TicketPriorityID = T.TicketPriorityID
	LEFT JOIN dbo.Users U on U.UserID = T.AssignedUserID
	CROSS APPLY (SELECT COUNT(*) OtherBillableTickets
				FROM dbo.Ticket iT 
				WHERE iT.TicketStatusID <> 8 
				and iT.DateDeactivated is null
				and iT.CompanyID = T.CompanyID
				AND iT.Billable=1
				and iT.TicketID <> T.TicketID) OtherBillableTickets
	INNER JOIN dbo.[Queue] Q on Q.QueueID = T.QueueID
	LEFT JOIN dbo.Users U_tech on U_tech.UserID = T.TechID
	OUTER APPLY (SELECT SUM(ISNULL(CASE WHEN 60.0 = 0 THEN 0 ELSE ((ceiling(DATEDIFF(n, StartTime, EndTime)/5.0))*5.0) / 60.0 END, 0)) HoursWorked
					    FROM dbo.[Entry] E 
					    WHERE E.TicketID = T.TicketID and E.DateDeactivated is null) HoursWorked
WHERE
	((@iMinutesWorked is null) OR (ISNULL((SELECT SUM(ISNULL(DATEDIFF(n, StartTime, EndTime), 0)) 
					    FROM dbo.[Entry] E 
					    WHERE E.TicketID = T.TicketID and E.DateDeactivated is null), 0) >= (@iMinutesWorked)))
ORDER BY T.IsClientDriven DESC, T.Name DESC

											

DROP TABLE #Results
DROP TABLE #ResultsUnique
DROP TABLE #temp

#4

In your final SELECTs, the Old and New are not the same. The New one has extra outer joins (2 of them IIUC) which can cause more rows selected


#5

Thanks! I will take a look. I also discovered there were changes (not informed to me) that are not included on new one. That may case a discrepancy too.