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 |
|
M2
Starting Member
22 Posts |
 Posted - 2003-09-04 : 00:27:51
|
| I am a beginner of Index. Therefore, I have an Index question as following:I have a table called ¡§User¡¨Fields:[UserCode] ¡V This is a unique Code[Tel1][Tel2][Tel3][Tel4][Tel5]50000 recordsIf I try to search a UserCode by passing a single Tel number [Parameter_Tel] into the system, can I write the following statement? DECLARE @Parameter_Tel VARCHAR(10)SET @Parameter_Tel = '993-446122'SELECT UserCode FROM UserWHERE [Tel1]= @Parameter_Tel or[Tel2]= @Parameter_Tel or[Tel3]= @Parameter_Tel or[Tel4]= @Parameter_Tel or[Tel5]= @Parameter_TelIn order to speed up my search operation, then should I set the ([Tel1], [Tel2], [Tel3], [Tel4], [Tel5]) to Index? Will this increase my search performance and response time? How to set the index for above table? Please help! Thanks! |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-09-04 : 02:12:07
|
| To benefit from those indexes the query would have to be run asselect UserCode from User where Tel1 = @punionselect UserCode from User where Tel2 = @punion...You might consider creating an indexed view to facilitate this.Or maybe create a table with the UserCode and Tel's then index that so you can filter on a single indexed field.Another option is to pin the table in memory if this query is frequent.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-09-04 : 02:15:18
|
Holy smokes! I never thought this would work, but it does!  Try this and see if this faster, I hope you have sufficient data to test the speed...SELECT UserCode FROM [User]WHERE @Parameter_Tel IN (Tel1, Tel2, Tel3, Tel4, Tel5)Owais
Make it idiot proof and someone will make a better idiot |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-09-04 : 02:38:29
|
| That should generate the same query plan as the or statements.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
M2
Starting Member
22 Posts |
Posted - 2003-09-04 : 04:31:28
|
| May I know where can I find some example for creating Index, because I have no idea, when should I create it, how to create it, which column should I choose as index !M2 |
|
|
|
|
|
|
|
|
SEARCH operation ! (Index)