Jack Corbett (7/18/2012)

---

Gerard,

What platform is your experience with?

BY default SQL Server does pessimistic locking. So, within a transaction it will lock what it needs to lock to...

In addition to that, it is a fact that with a unique constraint on the column, it is IMPOSSIBLE for there to be duplicate account numbers. That's the point...

The database engine will perform its own locks as needed because of the unique constraint. You seem to assume that things happen asynchronously at the database level, but it...

Jeff Moden (7/17/2012)

---

gerard-593414 (7/17/2012)

---

I have given a very simple example , as i dont have specific code, and am currenlty actually writing the code.

The example of updating a Table with...

Awesome...

I don't understand why you think that it needs these locks. SQL Server is smart enough to handle this for you when you place a unique constraint and use...

Jasmine D. Adamson (7/17/2012)

---

The execution plans for those are substantially the same, with the exception that the "filter" step runs faster in my version (but the index scan runs faster...

I think you are thinking too much into this. Let the database engine handle it, that's what it is built to do. You can do an IF EXISTS and if...

As an example, run this and look at the actual execution plan:

DECLARE @test-2 VARCHAR(10)

SET @test-2 = 'cid'

SELECT *

FROM sys.columns

WHERE @test-2 = '' OR @test-2 = name

SELECT *

FROM sys.columns

WHERE ...

Jasmine D. Adamson (7/17/2012)

---

I got it backwards, you just needed to wait for my edit. Happens to the best of us, which is proven by the fact that it happened...

Ok, let's all take a moment and think about this... If you reference a default...

Nope, that will not return all rows when @mySearchVar = ''

The previous post by me is the...

If I were in your position, I would start with only the first 2 tables. Then add the 3rd and make sure it is giving you desired results. ...

Look at this code:

CREATE TABLE #identity_map (id int identity(1,1), name varchar(110));

INSERT INTO #identity_map(name)

SELECT 'sql-mmt';

;WITH tagTypes (tagType)

AS

(Select '%-FRN%'

UNION

Select '%-BCK%'

UNION

Select '%-PPP%'

UNION

Select '%-MES%'

UNION

Select '%-MMT%')

SELECT *

FROM tagTypes

LEFT JOIN #identity_map IM

ON IM.name LIKE...

You probably need to change all of your INNER JOINs to LEFT JOIN