SELECT LIKE statment
-
Because if you use where replace (col..) = @SomeVar, you'll get an index scan all the time. however if you use the computed column (which updates itself BTW), amd index it, you'll get an index seek.
-
First, you don't have to update a whole new column...it's a COMPUTED column. That means it's a virtual column. When you add new rows, this column is 'automatically' updated.
Second, if you use REPLACE in your script, it has to go through each row, replace (remove) the spaces in each line for EVERY variable that it is comparing.
Using the computed column will be MUCH faster.
Just use the ALTER TABLE tablename ADD columnname command to add the computed column to your existing table. Refer to the BOL for more information (Look up ALTER TABLE in the Index).
-SQLBill
-
wait...how does the computed column update itself?
and, what is an index scan and why is that bad?
-
The name says it. It's computed (calculated) on the fly when you request it in a select. It's usefull when you have complexe calculations that you want to hide (or not repeat) in the select statements. Since the data is not kept on the hd, it's always up to date when you request it. However in this trick, we add an index (sending the data on the hd). Sql server does the work of keeping it up to date.
Now a scan basically mean that 100% of the rows will be fetched to find a single match. However a seek will allow to use a binary search algorithm to find the matching row(s), which makes the select much faster if the table is somewhat big.
-
But why do I need an index on this new column? I thought the LIKE function doesn't work on Indexes.
Thanks!
-
Yes it does :
Select whatever from dbo.YourTable where YourIndexCol like 'sdfk%'
This uses a seek
this however results in a scan
Select whatever from dbo.YourTable where YourIndexCol like '%sdfk%'
-
What is the difference between a seek and a scan?
I don't see why I can't just do this:
ALTER TABLE dbo.dmvrequest ADD
claimraw AS ltrim(rtrim(replace(DRclaimNumber, ' ', '')))
That gives me the new column where I can use LIKE on...
-
Because it'll still do a scan if it's not indexed. Also the trims just don't do squat because the replace already wipped out all the spaces.
SCAN : open the phone directory, now read every number in the book untill you find the number of Joe Zebra.
SEEK : find the same name the way you usually do (start near the nd and skip pages untill you find the right page, then skip names untill you find the right one).
tell me which one is faster.
-
I think SEEK would be faster...just a hunch.
So in short, the index is in place to speed up the query?
What performs the automated updating? Meaning, what updates this new column automatically everytime the originally column is added to?
Thanks!
-
The server will do it for you.
-
I guess I should have asked...what statement in the code you provided, instructs the server to automate the updates on that particular column?
-
alter table..
add colname as replace()
-
Perhaps it would be in order to recommend some reading in Books on Line as well?
'computed columns' is a good thing to search on.
/Kenneth
-
Stupid question, but what is books on line?
-
Books on Line (or BOL for short) is the online manual that comes with SQL Server. It usually installs along with the client tools, like Enterprise Manager, Query Analyzer etc. You should be able to find it in the start menu under programs/Microsoft SQL Server. If not, you can go here: http://www.microsoft.com/sql/techinfo/productdoc/2000/books.mspx
It is your absolute best friend whenever you wonder something about Transact SQL or SQL Server.
/Kenneth
Viewing 15 posts - 16 through 30 (of 48 total)
You must be logged in to reply to this topic. Login to reply