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.

With the follwoing test data:

CREATE TABLE #Accounts

(

AccountName varchar(25) NOT NULL

)

INSERT INTO #Accounts VALUES ('AAA'),('BBB')

CREATE TABLE #Products

(

ProductName varchar(25) NOT NULL

,[Range] varchar(25) NOT NULL

)

INSERT INTO #Products VALUES ('ProdAA','Core'),('ProdBB','2nd'),('ProdCC','Core')

CREATE TABLE #AccountProduct

(

AccountName varchar(25) NOT...

You will need to put the groups in order.

Something like the following should work:

WITH Grps

AS

(

SELECT Historyid, SequenceNumber

,HistoryId - ROW_NUMBER() OVER (PARTITION BY Letter ORDER BY historyid) AS grp

FROM #Test

)

, MinHists

AS

(

SELECT...