Chris, is there any any Microsoft official document which states this?

Jeff, is there a link for what u just mentioned?

Thanks both

I have one question here

Does SQL Server guarantee that update will be done strictly in top down manner?

I didnt find it writtem anywhere in BOL.

-- it needs to have a clustered index on the orderno column

CREATE TABLE #test (ID int, OrderNo varchar(20), PhoneID varchar(20), CONSTRAINT PK_T1 PRIMARY KEY CLUSTERED (OrderNo, ID));

INSERT INTO...

It cud be any no between 1 and 20 and in some rare cases more than 20.

but if we are using varchar datatype sql s. will reserve space as much required for actual value.Any say??

Thanks Paul!

More the query is complex more possible plans are possible and SQL Sever is not going to pick up best one necessarily.

Best design is one which is simple.

Thanks Gail

strating with simple and smaller queries seems to be right approach.

I understand that upto a large extent it depends on scenerio on hand but sometimes task is so big and complicated that it becomes difficult to decide which approach...

Here question is not about performance but WHAT change company wants.

There was a not null constraint already on this column means company didnt want to have null value for this...

this question seems to be contradictory...

I have one more question, if two tables are refering each other how can we truncate both tables?

I got following error while compiling this x_TruncateTable proc:

Server: Msg 197, Level 15, State 1, Procedure x_TruncateTable, Line 126

EXECUTE cannot be used as a source when inserting into a table...