-- create database for test

create database Test_Engine
GO


use Test_Engine;
GO

-- create main table

create table info(
  id bigint primary key identity(1,1),
  keyword nvarchar(50) not null,
  Description nvarchar(200) not null
)
GO


-- insert some records

insert into info (keyword, Description)
values ('ali','my interested name')
GO

insert into info (keyword, Description)
values ('abi','blue in persian')
GO

insert into info (keyword, Description)
values ('alias', 'other name')
GO

insert into info (keyword, Description)
values ('samane','girl name in Iran')
GO

insert into info (keyword, Description)
values ('samaneh','system in persian')
GO

insert into info (keyword, Description)
values ('samin','golden')
GO



-- create stored procedure for seaching 

create proc search
(
	@keyword nvarchar(50)
)
as
if exists (select id from info where keyword = @keyword)
begin
	select [description], UPPER(@keyword) + ' found' as report from info where keyword = @keyword
end
else
begin
	select [description], UPPER(@keyword) + ' not found. did you mean: ' + UPPER(keyword) + '?' as report from info 
	where SOUNDEX(keyword) = SOUNDEX(@keyword) or DIFFERENCE(keyword, @keyword) > 3
	UNION
	select '' as [description], UPPER(@keyword) + ' not found' AS report
	where @@ROWCOUNT = 0
end
GO




-- get searching time for some keywords - 
-- Test it in your system to get good results

declare @start datetime
set @start = CURRENT_TIMESTAMP

exec search 'ali'

exec search 'aly'
 
exec search 'samani'

exec search 'saeed'

declare @end datetime
set @end = CURRENT_TIMESTAMP


select 'Operation execution time', datediff(MILLISECOND, @start, @end), 'ms';
GO