Database:
300,000, with an ID column but no primary key, a nonclustered index is built on the category field to search
procedure T-sql:
Copy Code code as follows:
/*
User-defined functions: Execution time is about 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 + '% '
*/
Copy Code code as follows:
DECLARE @a datetime,@b nvarchar (4)
Set @a=getdate ()
Select title from book 300,000 Where category like '% medicine% '--"category" column has nonclustered indexes, 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, just a little slower at 1150-1200
--Execute GETFL ' medicine '-calling stored procedures 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!
