siamak.s16 (1/14/2011)

---

Working on that.

In the meantime, I guess...

you should follow up the code to get context on whats actually being done and why. asking around is your best bet

TheSQLGuru (1/14/2011)

---

Incorrect. Per BOL:

DISABLE

Prevents...

i feel your pain, i also once worked on a place that had stuff like this.

apart from changing how the system works to get away from that loop, the best...

as i understood the question, he was trying to figure out how DEF DEF ended up in the results.

Steve explains it well, that what he is really doing here is...

search around, there are a couple of ready solutions around

imo, Jeff Moden offers the best split function for strings with less than 4000 chars and Adam Machanic CLR split functions...

if on SQL 2008 issue

ALTER TABLE table SET (LOCK_ESCALATION = DISABLE)

to disable lock escalation on that table, after that sql server will respect locking hints

A.Col1 = B.Col1 matches INSERT INTO #AAA (Col1, Col2) VALUES ('DEF', 'DEF') and INSERT INTO #BBB (Col1) VALUES ('DEF')

no?

for perfect matches HASHBYTES can be used instead of checksum

SELECT HASHBYTES('SHA1','123')

SELECT HASHBYTES('SHA1','321')

ALZDBA (12/2/2010)

---

Since OP stated order is of importance, maybe a join on col1 helps to restrict the cartesian product as well as the where-clause for nMatches > 5

So...

ALZDBA (12/2/2010)

---

Can you post some execution results and timings ? (cpu , elaps, io rates)

Seems to me pulling this kind of stuff into your CLR space could easily blow up...

ill post on what i been testing

create the 2 tables

IF OBJECT_ID('TEMP_1') IS NOT NULL

DROP TABLE TEMP_1

IF OBJECT_ID('TEMP_2') IS NOT NULL

DROP TABLE TEMP_2

GO

--CREATE TABLES

DECLARE @A VARCHAR(1000),@B INT

SELECT @A = '',@B =...

have you tried a Parallel.ForEach or some sort of parallel processing?

with your server specs it might reduce the execution time.

i'll post some code soon if you want to try

are you able to use a CLR?

i believe this sort of processing is best done within the .net framework

ALTER TABLE snapshots.trace_data ADD UniqueCol AS (CAST(SNAPSHOT_ID AS VARCHAR) + '-' + CAST(EVENTSEQUENCE AS VARCHAR)) PERSISTED NOT NULL

adding the column like this made it work.

since its a computed column...