Can you post the execution plan? Perhaps the index is not covering?

Please don't yell!

-- This table is a MUST! Do not remove!

DECLARE@ID TABLE (CtcID INT PRIMARY KEY CLUSTERED)

INSERTdbo.CONTACT

(

CONTACTID,

FIRSTNAME,

MIDDELNAME,

LASTNAME

)

OUTPUTinserted.CONTACTID

INTO@ID

SELECTContactID,

FirstName,

MiddleName,

LastName

FROMAdventureWorks.Person.Contact

WHEREFIRSTNAME LIKE @FIRST

INSERTdbo.EMPLOYEE

(

CONTACTID,

EMPLOYEEID,

NationalIDNumber,

LoginID,

Title

)

SELECTCONTACTID,

EMPLOYEEID,

NationalIDNumber,

LoginID,

Title

FROMAdventureWorks.HumanResources.Employee

INNER JOIN@ID ON ctcID = ContactID

The answer is quite simple. Your ContactID column in table Contact is not an identity column.

SELECT[Service],

Product_Entered AS [Product Entered],

CIN,

CLI,

CLI_Match AS [CLI Match],

[Date],

CONVERT(CHAR(5), [Time], 114) AS CallTime

FROMdbo.CliExtract

WHERE(CIN = @cin OR @cin IS NULL)

AND ([Service] = @service OR @service IS NULL)

AND [Date] >= COALESCE(@Date, '17530101')

AND [Date] <=...

DECLARE @Sample VARCHAR(100) = '12345'

;WITH cte(CurrPos, LastPos, String)

AS (

SELECT1 AS CurrPos,

DATALENGTH(@Sample) AS LastPos,

CAST(@Sample AS VARCHAR(MAX)) AS String

UNION ALL

SELECTCurrPos + 1 AS CurrPos,

LastPos,

STUFF(String, 2 * CurrPos, 0, ',') AS String

FROMcte

WHERECurrPos <...

DECLARE@CurrID INT = (SELECT MIN(PrimaryKeyColumnNameHere) FROM MyDB1.[dbo].[Company]),

@MaxID INT = (SELECT MAX(PrimaryKeyColumnNameHere) FROM MyDB1.[dbo].[Company]),

@Interval INT = 100000

WHILE @CurrID <= @MaxID

BEGIN

DELETEtgt

FROMMyDB1.dbo.Company AS tgt

LEFT JOINMyDB2.dbo.Company AS src ON src.CompanyID = tgt.CompanyID

WHEREtgt.PrimaryKeyColumnNameHere >= @CurrID

AND...

Here is another approach. Instead of searching for "no duplicate" I keep track of which values that have been used.

SET NOCOUNT ON

CREATE TABLE#Sample

(

ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,

idMvt1 INT...

I meant, what is the new timings for all pool sizes; 100, 1000, 10000 and 100000 unique records on a million sample dataset?

And the other Pool sizes? 100, 1000 and 10000.

And just in case ID doesn't start with 1, changeWHERE id=1;toWHERE id=(select min(id) from mvts);

And using partitions, right?

DECLARE@Sample TABLE

(

ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,

idMvt1 int,

idMvt2 int,

idMvt3 int

)

INSERT@Sample

(

idMvt1,

idMvt2,

idMvt3

)

SELECT271, 204, 136 UNION ALL --1 This will always show up because...

Change

SELECT @XmlData.modify

to

SET @XmlData.modify

Can I throw in a bone too?

--===== Peso's code

;WITH cteSource(ID, SequenceID)

AS (

SELECTu.ID,

ROW_NUMBER() OVER (PARTITION BY u.theValue ORDER BY u.ID) AS SequenceID

FROMdbo.Mvts AS s

UNPIVOT(

theValue

FOR theCol IN (s.idMvt1, s.idMvt2, s.idMvt3)

) AS u

)

SELECTID

FROMcteSource

GROUP...

How does table A look like?

Try this query

SELECTt1.ID,

t1.Postcode,

t1.CityTown,

t1.SuburbDistrict,

t1.StateProvince,

t1.Country_ID,

t1.AddressType_ID,

t1.Postcode_ID

FROM(

SELECTadr.ID,

adr.Postcode,

adr.CityTown,

adr.SuburbDistrict,

adr.StateProvince,

adr.Country_ID,

adr.AddressType_ID,

adr.Postcode_ID

FROMdbo.[Address] AS adr

LEFT JOINdbo.s2_usn AS s2 ON s2.table_id = adr.id

and s2.tablename = 'address'

WHEREadr.Country_ID = 63

AND s2.ID IS NULL

) AS t1

INNER JOIN(

SELECTDISTINCT

u.AddressID

FROM(

SELECTacc.Address_ID AS adr1,

aa.Address_ID AS adr2

FROMAccount AS acc

LEFT...