When I run a Select statement it takes like 3 minutes: I cleared the cache before running.
Select distinct CallID from CallLog where CallDesc like '%0d0d0857%' order by CallID
Then i index other columns on the table which is not in the select statement I run the query again and it takes like 30 sec. I cleared the cache before running.
Anyone care to explain? Why indexing other columns which are not being using in the select statement makes it run faster?
i was testing in SS2005. But the table is from SS2000. Looks like its OK now. But I want to know how can i improve the data type from text on SS2000? or any way to improve the speed?
1) Read about how and where TEXT columns are stored in SQL Server 2000. 2) Read about indexing and TEXT (varchar max) columns. Do the 900 bytes limit for index in SQL Server 2000 ring a bell? 3) Read about LIKE operator, especially for queries starting with wildcard character and their use of indexes. 4) Now, if you still haven't found the information you want from Books Online, please tell us what you didn't understand.
1) Read about how and where TEXT columns are stored in SQL Server 2000. 2) Read about indexing and TEXT (varchar max) columns. Do the 900 bytes limit for index in SQL Server 2000 ring a bell? 3) Read about LIKE operator, especially for queries starting with wildcard character and their use of indexes. 4) Now, if you still haven't found the information you want from Books Online, please tell us what you didn't understand.
[font=Courier New]E 12°55'05.63"
1) Good stuff 2) I'm on sql server 2000. I think this is for 2005 3) I know when starting with % won't use the index. 4) I think 1 solved my problem. I don't need to upgrade the box to 2005
Edited by - lamujerdetuhermano10 on 09/08/2008 20:23:55
index question
Posted - 09/04/2008 : 19:17:20
