See if the PK is a clustered index as well...... if so it's logical it will fall back to an index scan...

Maybe a little late but I contributed a script wich will audit any table.....

It looks if a specificv audit table is present, if it isn't it will create a...

Dim MyConn As SqlConnection = "YOUR SQL CONNECTION"

Dim mySqlCommand As SqlCommand = New SqlCommand("ListPublication", MyConn)

            If MyConn.State <> ConnectionState.Open Then

                MyConn.Open()

            End If

            mySqlCommand.CommandType = CommandType.StoredProcedure

            SqlCommandBuilder.DeriveParameters(mySqlCommand)

            mySqlCommand.Parameters(1).Value = PublicationID

            Dim...

Several possibillities.....

1. There are multiple instances running on the computer.

2. Disable all unneccesary protocols in SQL Server. (ONLY TCP/IP) and no namede pipes

3. Delete everey protocol in windows. Only TCP...

Thanks but this is not what i am looking for.

It would be a diagnostic query.

And installing all kind of stuff on a production server is out of the question.....

Any other...

Don't know if this is the case but in a live situatiuon you can encounter a long lasting lock and this can affect queries....

To get rid of the lock try...

Also it is important how many data you need from the row...

If you need only the fields mentioned in the query you could use covering indexes....

If the rows are very...

I am not trying to offend anyone but if for a certein column the null percentage is 65% or higher i suggest moving those fields out of the table.

Why whase...

I had a second thought....

If you are really working with millions of rows.... Is iterating through every record the best way then.

My guess is that a set based operation will...

i mean what if the user uses SP_EXECUTESQL in his query ??????

I agree with Guarddata...

Ther is now way of us knowing in advance what will be the optimum solution.

So there is nothing left to do then build a small prototype of...

The queru on the permanent table has to do less actions.

Doesn't need to allocate memory, free up memory etc.

A good point made before is the size of the memory table....

Create a memory table with an identity column and the pk of the table.

below a small example :

declare @counter as integer

declare @maxcount as integer -- Used for upper bounds

declare @keyval...

If any SQL statement would be valid....

Would an Exec or sp_executesql be valid as well.......

Well this is maybe a very stupid reply but maybe it isn't......

1. Pass the sql-query to a stored procedure as a parameter, the quotes issue should be solved then.

2. Dont...