Do the following test with UPDLOCK:

1. Open Query Analyser twice selecting your db in both.

2. In one copy of QA run:

BEGIN TRAN

SELECT MIN(serialNo)

FROM card_pins WITH (UPDLOCK)

3. In the other copy...

I think you need to use either an UPDLOCK or an XLOCK. An UPDLOCK should be alright here.

SET XACT_ABORT ON

BEGIN TRANSACTION

SELECT @get_PIN = MIN(serialNo)

FROM card_pins WITH (UPDLOCK)

DELETE card_pins

WHERE serialNo= @get_PIN...

http://databases.aspfaq.com/database/coalesce-vs-isnull-sql.html

SELECT DATEADD(day, 0, DATEDIFF(day, CAST(0 as datetime) ,StartTime)) AS WorkDay

 ,COUNT(DISTINCT EmployeeID) AS MaxEmp

FROM TimeClock WITH (NOLOCK)

WHERE StartTime >= '20061001' AND EndTime < '20061101'

GROUP BY DATEADD(day, 0, DATEDIFF(day, CAST(0 as datetime)...

SELECT username

FROM usermaster

GROUP BY username

HAVING COUNT(*) > 1

What you want is unclear. If the rows returned are exact duplicates then replace SELECT with SELECT DISTINCT, otherwise you will need to decide which EUS_ID row to return. eg...

Maybe:

SELECT L.LOB_ID

 ,L.EUS_ID

 ,L.LOI_START_DATE

 ,L.LOI_END_DATE

 ,E.CTY_CODE

FROM learning_object_instances L

 CROSS JOIN end_users E

 JOIN (

  SELECT L1.EUS_ID

  FROM learning_object_instances L1

  WHERE L1.LOI_START_DATE > '20060907'

  GROUP BY L1.EUS_ID

  HAVING COUNT(*) > 1 ) D

 ON L.EUS_ID = D.EUS_ID

WHERE E.CTY_CODE = 'RU'

 AND L.LOI_START_DATE > '20060907'

You may want to consider a different strategy:

1. Create views for all tables and views in your db along the lines of:

CREATE VIEW RO_YourTableName

AS

SELECT *

FROM YourTableName WITH (NOLOCK)

2. For your...

I think this can be done by creating a db that only consists of VIEWs of the tables in the main db. All the SELECTs in the VIEWs will have the...

Old style

select d1.InvNum,d1.ItemID,d2.ItemID

from InvDetl d1,InvDetl d2

where d1.InvNum=d2.InvNum and d1.ItemID<>d2.ItemID

New style

select d1.InvNum,d1.ItemID,d2.ItemID

from InvDetl d1

  INNER join InvDetl d2 on d1.InvNum=d2.InvNum and d1.ItemID<>d2.ItemID

or

select d1.InvNum,d1.ItemID,d2.ItemID

from InvDetl d1

  CROSS join InvDetl d2

WHERE d1.InvNum=d2.InvNum and...

Something like:

-- *****************

-- Test Data

DECLARE @t TABLE

(

 col1 CHAR(3) NOT NULL

 ,col2 SMALLINT NOT NULL

)

INSERT @t

SELECT 'abc', 101 UNION ALL

SELECT 'def', 201 UNION ALL

SELECT 'ghi', 301

-- End of Test Data

-- *****************

-- Use...

Using the above test data, and assuming trainingname is the same as QuizName, how about:

SELECT E.JobCCNo AS CostCenter

 ,D.trainingname AS QuizName

 ,COUNT(DISTINCT E.EmpNo) AS NumberOfEmployees

 ,COUNT(DISTINCT T.EmpNo) As NumberTrained

 ,COUNT(DISTINCT E.EmpNo) - COUNT(DISTINCT T.EmpNo)...

Your column names seem to be confused, but this may be what you want:

-- *********************

-- Test Data

DECLARE @tblCurrentWinTrainingLog TABLE

(

 EmpNo CHAR(6) COLLATE DATABASE_DEFAULT NOT NULL

 ,tdate SMALLDATETIME NOT NULL

 ,trainingname VARCHAR(30) COLLATE DATABASE_DEFAULT...

Probably best done on the front end, but the following should work:

SELECT userid

 ,[name]

 ,address

 ,CASE

 WHEN columnA IS NULL AND columnB is NULL

 THEN 'None'

 WHEN columnA IS NULL

 THEN columnB

 WHEN columnB IS NULL

 THEN columnA

 ELSE columnA...

That sounds like caching. Run them both a couple of times and then compare performance.