SCAN vs. SEEK

Question

SCAN vs. SEEK

greg6363

Grasshopper

Points: 22
More actions
February 13, 2016 at 8:35 pm

#308044

I'm stumped. I have two tables. The first table has a field where I need to extract an alpha-numeric string where I can use the substring function to achieve that . The second table is where I need to extract that same alpha-numeric string but it is embedded within the field but not always in the same position. How do I use either SCAN or SEEK to extract that string so I can perform a match with the same string from the first table. Any assistance would be greatly appreciated. Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Err... Seek and scan are SQL's two internal operations for accessing indexes, they're not something you explicitly use.

What exactly are you trying to do? Post table definitions (as CREATE TABLE statements) and sample data (as INSERT statements) please

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

greg6363 Grasshopper Points: 22 More actions · Answer 2

[Err... Seek and scan are SQL's two internal operations for accessing indexes, they're not something you explicitly use.

What exactly are you trying to do? Post table definitions (as CREATE TABLE statements) and sample data (as INSERT statements) please]

I need to extract the same string from two tables. I can use the SUBSTRING function to pull the alpha-numeric sequence from the first table since it is always in the same position in the field but I need to pull the same alpha-numeric sequence from a text field in the second table and the sequence is not always in the same position within the field. What code do I use so I can match the alpha-numeric sequence between the two tables? Any assistance would be greatly appreciated. Thanks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

GilaMonster (2/14/2016)
What exactly are you trying to do? Post table definitions (as CREATE TABLE statements) and sample data (as INSERT statements) please

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 4

GilaMonster (2/14/2016)
GilaMonster (2/14/2016)
What exactly are you trying to do? Post table definitions (as CREATE TABLE statements) and sample data (as INSERT statements) please

... plus the expected results

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 5

greg6363 (2/14/2016)
[Err... Seek and scan are SQL's two internal operations for accessing indexes, they're not something you explicitly use.
What exactly are you trying to do? Post table definitions (as CREATE TABLE statements) and sample data (as INSERT statements) please]
I need to extract the same string from two tables. I can use the SUBSTRING function to pull the alpha-numeric sequence from the first table since it is always in the same position in the field but I need to pull the same alpha-numeric sequence from a text field in the second table and the sequence is not always in the same position within the field. What code do I use so I can match the alpha-numeric sequence between the two tables? Any assistance would be greatly appreciated. Thanks.

You'll need to use a mid-string LIKE or a CHARINDEX to find the string in the table where the position is unknown. There is no way that will occur with any decent performance. The entire column will need to be scanned.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

crmitchell SSCarpal Tunnel Points: 4557 More actions · Answer 6

With the value being in a known position in the first table you could preprocess that table to extract the string you require into a new column and create an index on that new column which could at least allow a seek on that.

Would depend on whether the overhead for creating the new column and indexing it is lower than the cost of the scan but if the table is being used for many such joins then it probably is.

I can't see any option for the text field though.

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 7

As an example, here's DDL with data.

CREATE TABLE dbo.MyTable ( mystring VARCHAR(200) );

GO

INSERT dbo.MyTable

( mystring )

VALUES

( 'This is a 7' )

, ( 'There is a 7 in this string' )

, ( 'Why must 7 always be here' )

, ( 'No 7s here. JK' )

, ( 'My 7s here' )

, ( 'An 7s here.' )

, ( 'On 7s here.' );

GO

SELECT

'SetPosition' = SUBSTRING(mystring, 4, 1)

, 'FindThe7' = SUBSTRING(mystring, CHARINDEX('7', mystring), 1)

FROM

dbo.MyTable AS mt;

DROP TABLE dbo.MyTable;

Note that in the first 3 rows, the 7 varies by position. The next 4 are fixed, so the first column in my result set works for only the 4 rows that have a fixed position. The second column finds the pattern no matter where it is.