Nope. My idea didn't work.

The only way I can get it to use only Index Seeks, without Scans, is to separately query each index, build a temp table for...

Actually, there is a partial way around it, which is using Union statements instead of Or statements.

For example:

select Col1, Col2

from dbo.Table1

where Col1 = @Col1_in

union

select Col1, Col2

from dbo.Table1

where Col2 = @Col2_in

The...

If you're using NewID as part of the clustered PK, you should also add an incrementing field to it. A transaction time would work. Otherwise, since NewID isn't...

What you're looking for is "left outer join". Look it up in Books Online and you'll get exactly what you need.

Is there a technical reason to do the pivot in the database?

Would it be possible to move the pivot to a front-end application?

The reason I ask is that Excel, Report...

Multiple concurrent selects shouldn't cause a deadlock. Are you sure that's what's causing it?

There isn't a way around that. At least not one I'm aware of.

I used your temp table and test data on 9000 rows, 1000 IDs in #t.

Here's the query I wrote, based on the description you gave in the original post:

;with CTE...

I didn't realize the whole thing was being done on a temp table. Sorry about that.

Good on finding a solution. You're welcome (not that I actually helped much).

Those definitions are the differences.

Transactional Replication copies on a transaction-by-transaction basis. It's near real-time (mainly depending on network latency).

Log shipping sends a backup of the log over and restores...

Yeah. I had found the thing with begin and commit/rollback in different procs, and had assumed that batches would work the same way. Found out I was wrong...

Matt Miller (4/11/2008)

---

rbarryyoung (4/11/2008)

---

Michael Earl (4/7/2008)

---

This is a batch separator. In some places, it will impact what your scripts will do.

The semi-colon you have included is also a batch...

I could probably help, if you provide the table structure, including indexes, some sample data, and the query you're using.

Without that, I would hazard a guess that the difference in...

Gail, I checked that concept out as follows (beginning and ending transactions in separate procs):

create table TranTest (

ID int identity primary key,

Val char(5))

go

create proc TranTest2

as

insert into dbo.trantest (val)

select 'test2'

commit

go

create proc...