300,000 data, search for text fields in various ways compared

300,000, with an ID column but no primary key, a nonclustered index is built on the category field to search

Procedure T-sql:

/*


user-defined function: Execution time is around 1150-1200 milliseconds


CREATE FUNCTION [dbo]. [GETHL] (@types nvarchar (4))


RETURNS table as


return select title from book 300,000 Where to categorize like '% ' + @types + '% '





Stored procedures:


CREATE PROCEDURE [dbo]. [GETFL] (@typen nvarchar (4))


as


Select title from book 300,000 Where to categorize like '% ' + @typen + '% '





*/

declare @a datetime,@b nvarchar (4)


set @a=getdate ()


Select title from book 300,000 Where class like '% medicine% '--"category" column has nonclustered index, faster than clustered index 1150, almost execution time around 1100


--Select title from GETHL (' medicine ')--using user-defined functions, efficiency and building a clustered index is a little slower than 1150-1200





--Execute GETFL ' medicine '--calling a stored procedure cannot contain parameters in parentheses Execute getfl (' medical ')





--Select title from VIEW1--View





print ' Run time:





Print DateDiff (Ms,@a,getdate ())

Conclusion：

1, all the above use direct query, function, view, stored procedure performance is similar;

2. In this text field, nonclustered is better than clustered index.

A better way to do this is to create a corresponding retrieval ID on another table, faster!