Hi Iain,

Thanks for the performance comparison.

We both seem to expect the quirky update to run faster. As it is non-relational I prefer to let people use their own judgement on...

Sunny,

If you want the groups to show then:

1. like Iain, I suspect the quirky update will be quickest.

The following article, by Jeff Moden, gives the details; you should read the...

SELECT @@options

http://www.mssqltips.com/sqlservertip/1415/determining-set-options-for-a-current-session-in-sql-server/

You will need to watch boundary conditions, but try something like the following:

WITH EmpLoginTime

AS

(

SELECT EmpName

,ROW_NUMBER() OVER (PARTITION BY EmpName ORDER BY LoginTime) AS EmpOrder

,LoginTime

FROM dbo.logintime

)

,EmpTimes

AS

(

SELECT T1.EmpName, T1.EmpOrder, T1.LoginTime AS StartTime

,CASE

WHEN...

If you have space in tempdb, try using read commited snapshot isolation.

raphael.kong (10/10/2011)

---

humm ...

In case of bulk insert, no trigger are fired, OK ; But, when the software insert data into the table (using the DSN) the trigger IS fired, as...

Assuming ID is the PK of stg_PAreporting:

WITH JobOrder

AS

(

SELECT ID

jobname

,jobname + '-' + CAST(ROW_NUMBER() OVER (PARTITION BY jobname ORDER BY [date]) AS varchar(3)) AS NewJobName

FROM stg_PAreporting

)

UPDATE JobOrder

SET jobname = NewJobName

Indexes on FinalOutput, like the following, may also help.

CREATE NONCLUSTERED INDEX FinalOutputProdNumTransDate

ON dbo.FinalOutput ([Product Number (SKU)], [Trans Date])

INCLUDE ([Seller - Company ID]);

CREATE NONCLUSTERED INDEX FinalOutputProdLineTransDate

ON dbo.FinalOutput ([Product Line ID], [Trans...

With some filtered covering indexes on dbo.PromoMaster

CREATE NONCLUSTERED INDEX FIPromoMasterSKUNotAll

ON dbo.PromoMaster (SKU, [Start Date], [End Date])

INCLUDE ([T1 ID], [Programe ID], [Promo Type], [Rebate Value])

WHERE SKU <> 'ALL';

CREATE NONCLUSTERED INDEX FIPromoMasterSKUAll

ON...

This will probably work but will not use any indexes:

UPDATE P

SET UPC = sourc.upc,

PROD_NAME = sourc.name,

...

You should be able to work out what you need to do in the first CTE.

DECLARE @CurrDate datetime;

SET @CurrDate = '20110831';

WITH CurrentWeek

AS

(

SELECT DATEADD(d, DATEDIFF(d, 0, @CurrDate) - DATEPART(weekday, @CurrDate) + 1, 0) AS WeekStart

,DATEADD(d, DATEDIFF(d, 0, @CurrDate) - DATEPART(weekday, @CurrDate) + 8, 0) As WeekEnd

)

,Years100

AS

(

SELECT...

In relational theory a relation (table, view etc) is an unordered set.

You should really alter your code so that the ORDER BY is on the outer most query.

If you are...

Maybe the cartisian product:

WITH Numbers(N)

AS

(

SELECT 0

UNION ALL SELECT 1

UNION ALL SELECT 2

UNION ALL SELECT 3

UNION ALL SELECT 4

UNION ALL SELECT 5

)

SELECT

N1.N AS exid1

,N2.N AS exid2

,N3.N AS exid3

FROM Numbers N1

CROSS JOIN...

You asked a similar question before on this thread:

http://www.sqlservercentral.com/Forums/Topic1139620-145-1.aspx#bm1139686

I suggest you study the original thread so you understand what is happening.