If you have only read rights how can you create or alter SP?

If you have rights to run ad-hoc queries only (I better keep silence about such "smart" sequrity model,...

You are trying to hardcode business rules. What must be done UNDER NO CIRCUMSTANCES.

You must create table User_DType:

CREATE TABLE User_DType (

UserId int,

DType nvarchar(30)

)

INSERT INTO User_DType (UserId, DType)

SELECT 1, 'P'

UNION

SELECT 2,...

I rewrote your query keeping original logic:

SELECT   

 UC.UploadControlID,

 LEFT(C.CampaignCode, 2) AS CampaignYear,

 RIGHT(C.CampaignCode, 3) AS CampaignNumber,

 LEFT(HD.AS400Description, 5) AS CampaignSelection,

       SA.TitleID AS Sku, SA.StoreID AS StoreNumber,

 SA.CurrentAllocation AS Quantity,

 CA.SupplierID AS SupplierCode,

      ...

Problem is your view presents NOT NULL column EffectId, so it's NOT NULL by definition.

1st, it's really bad practice to have DEFAULT to replace NULL. Defaults must come only from...

Do you run only SELECT in QA or INSERT?

How many indexes you have on the table being inserted?

What are the fill factors on them?

Is it used intensively for selects?

Index columns with ZIP codes in both tables.

And don't return million rows.

Change this SP to a view. And select in following SP from this view.

It will allow optimiser to narrow...

There is no such thing as first, or last, or second, etc rows in relational model.

All rows are equal in their rights.

If you need to keep the record of time...

What is the purpose of having XML text INSIDE of relational database?

Files must be stored (if required) in the place designed to store files - in file system.

Database is designed...

It was so easy, I could not believe you could not modify my query to get the result you wanted:

SELECT Min(start) as Start,  MAX(Start) as thruDate , FStateID,...

CREATE TABLE #StateSeries

(

rowSeq int,

stateDT smalldatetime,

FStateID int,

BStateID int

)

Insert Into #StateSeries (rowSeq, stateDT, FStateID, BStateID)

SELECT 1, '2006-04-01 00:00', 1, 3

UNION

SELECT 2, '2006-04-01 00:15', 1, 3

UNION

SELECT 3, '2006-04-01 01:30', 1, 3

UNION

SELECT 4, '2006-04-03...

1st. Hash table will be created behind the scene by SQL server, even if you don't realise it. So. this solution will not slow anything down.

2nd. If you are such...

DECLARE @WorkingString varchar(100)

CREATE TABLE #TempControls(

CtrlCode varchar(3)

)

INSERT INTO #TempControls

VALUES ('T1')

INSERT INTO #TempControls

VALUES ('T2')

INSERT INTO #TempControls

VALUES ('T4')

INSERT INTO #TempControls

VALUES ('T7')

INSERT INTO #TempControls

VALUES ('T12')

SELECT IDENTITY(int, 1,1) as ID, CtrlCode

INTO #T

FROM #TempControls

WHERE CtrlCode...

1. Don't do this: SET @WorkingString = ''

2. SELECT @WorkingString = ISNULL(@WorkingString + ', ', '') + CtrlCode

Why you need "*30" in "Group by" ?

Shrikant, don't try to spoil your server, just create required indexes.

That's all.