CREATE TABLE dbo.pageStore(ID int NOT NULL,StoreName varchar(50) NULL,OwnerOccupation varchar(50) NULL, CONSTRAINT PK_pageStore PRIMARY KEY CLUSTERED ([ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOCREATE FULLTEXT CATALOG pageStoreCatGOCREATE FULLTEXT INDEX ON dbo.pageStore( StoreName LANGUAGE English, OwnerOccupation LANGUAGE English)KEY INDEX PK_pageStore ON pageStoreCatWITH CHANGE_TRACKING AUTOGOINSERT INTO pageStore VALUES (393,‘Naroon House‘,‘Ali Haririan‘)INSERT INTO pageStore VALUES (394,‘Andishe Vila‘,‘Ali Zarein‘)INSERT INTO pageStore VALUES (395,‘Naroon‘,‘John Sample‘)GOWAITFOR DELAY ‘00:00:05‘SELECT ID,StoreName,OwnerOccupation,KEY_TBL.[KEY],KEY_TBL.[RANK] FROM pageStore INNER JOIN CONTAINSTABLE(pageStore,(StoreName,OwnerOccupation), N‘ISABOUT( "Ali" WEIGHT (0.2) ,"Naroon" WEIGHT (0.3),"House" WEIGHT (0.1))‘) AS KEY_TBLON pageStore.ID = KEY_TBL.[KEY] ORDER BY [RANK] DESC
The result is:
ID |
Storename |
Owneroccupation |
Key |
Rank |
393 |
Naroon house |
Ali haririan |
393 |
111 |
395 |
Naroon |
John sample |
395 |
73 |
394 |
Andishe Vila |
Ali zarein |
394 |
47 |
Full text search implementation sort implementation solution