If you don't have much data,

first go to the options and restrict the log size for the db to a small value.

and then try doing delete operation within a transaction,...

Sorry for the confusion . My question was why the previous statement should be terminated before starting cte . my bad , I wrote it wrong .

Well I got the...

Yes, exactly these are restricted operators within function.

But what actually are they and how can we say an Insert/Update/TryCatch as Side effecting when used within function

GilaMonster (9/24/2011)

---

As I said, if a function doesn't have side effect (DML orDDL), the optimiser is free to run it as many times as makes sense for a query plan....

To get last updated date you can also use dbcc show statistics

Hey dinsab ,

May I know the reason of rebuilding indexes.

Due you see a lot of fragmentation on your indexes.

Yes, rebuilding won't count towards data modification. But if he is planning for indexes rebuilt, I guess some heavy updates might be the reason for that. So in case we...

I don't think u need to update column stats as well. If your rebuilds and updates have affected enough rows in table I.e. the threshold value for triggering auto update...

Well this is bug in BIDS2008

https://connect.microsoft.com/SQLServer/feedbackdetail/view/382492/msft-employee-in-ssis-the-fast-load-opration-does-not-rollback-if-the-destination-table-is-heap

I have tested the same in DataTools2010 and it works fine.

I am using 2008R2, I think I am missing some Patch for this

Buffer Size is Default i.e. 10485760

And BatchSize, I guess is MaxInsertCommitSize viz. 100

Got it,

Had to reset toolbox

You can use this query to see the index size:

SELECT index_depth AS D

, index_level AS L

, record_count...

My Bad.

Drop_Existing will not cause Non clustered indexes to rebuilt , no matter we have unique or nonunique clustered indexes

Guess, this will be an unresolved mystery for me:(

Thanks all for clarifying