Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
marco gwiliani
Starting Member
25 Posts |
 Posted - 2008-03-20 : 05:20:00
|
| I have a table as follows:PID (PK), intPostcode, ncharAddress1, nvarcharAddress2, nvarcharAddress3, nvarcharAddress4, nvarcharAddress5, nvarcharI have a view on the table. The Output of the view is PID, Postcode, ADDRESS (which is Address1 - Address5 concatenated into one string with spaces between notnull values)I use a sproc to search the view for an address based on a free-text search. Sproc as below. Can anyone advise how I should index the table? I have never created an index before and need to speed up the search, which at the moment is simply to slow. I have 1.8 million records in the table.Here is the sproc:SELECT PID, Upper(Postcode) as 'Postcode', ADDRESS FROM vw_PostcodeWHEREAddress Like '%' + REPLACE(REPLACE(@address,',','%'),' ','%') + '%' OR Postcode Like '%' + REPLACE(REPLACE(@address,',','%'),' ','%')+ '%' Regards MArco |
|
|
ranganath
Posting Yak Master
209 Posts |
Posted - 2008-03-20 : 05:57:38
|
| Hi, Please Refer BOLcreate index ix_pid on vw_Postcode(PID) |
 |
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-03-20 : 06:02:59
|
| i'd be careful about creating an indexed view. make sure you read about them thoroughly in Books Online and understand it, before you go ahead and implement itEm |
|
|
|
marco gwiliani
Starting Member
25 Posts |
Posted - 2008-03-20 : 06:48:55
|
| Thanks, I will look at BOL. Just to confirm do you think I should be indexing the table or the view (I was not aware you could index a view)MArco |
|
|
|
|
|
|
|
|
How to create an index in this scenario?