select A.*, CASE

WHEN ArrivalDateTime IS NULL

THEN AdmitDateTime

ELSE ArrivalDateTime

END AS ArrivalOrAdmitDateTime

From AbstractData A

Where DATEDIFF(dd, ArrivalOrAdmitDateTime, DischargeDateTime) < 2

You can not use the alias name in where clause..

do the...

Are the tables same in size, number of columns,indexes etc in both databases? Are statistics upto date in both databases for both of these tables?

As mentioned, by Lynn, post the...

It is not allowed you can not use the functions directly as it must be constant. But I think it should throw an error because when you are not passing...

Try this as well.. This is based on Lynn's DML

select

td.ID,

td.Unit,

td.SysCC

from

#TestData td

...

(One of my friend worked in Oracle for the similar situation, but in ORACLE, they have partitioned the databases with different schemas and logins for various products. Is there something...

Sorry I do not have access to SQL server as of now. So I can not look into it.

But you can try following Put N before the character you are...

How are you importing the data? Is the column where you are inserting this data is defined as unicode ( like nvarchar or nvarbinary) ?

Try this.

http://msdn.microsoft.com/en-us/library/ms189941.aspx

You have to use -N...

Are you deleting from the basedata2 table (this has 7 non clustered indexes.) or incremental data (it has just one non clustered index)? Again how many rows are being deleted...

For fucntion you can not get the plan as it is. You have to use the sys.dm_exec_query_stats dmv

and join this with dm_exec_query_text like below

select

dest.text,deqp.query_plan,deqs.*

from

sys.dm_exec_query_stats deqs

/*F0C6560A-9AD1-448B-9521-05258EF7E3FA*/ --use a newid...

Did you try this?

select * from sys.dm_db_index_physical_stats(DB_ID(),object_id('tablename'),null,null,'DETAILED')

go

It can provide you the forward row count etc..

To fix this you can run the alter table <tablename> rebuild

it should fix the...

Did you try to increase the batchsize say to 5000?

You mentioned that there are around 2 million rows in one table what about other table.After delete how many rows remain...

One possibility could be that there is something in Prod before say nightly batch which execute your function

and a plan is generated. This plan might not be the best when...

Gullimeel (5/26/2012)

--------------------------------------------------------------------------------

GilaMonster (5/22/2012)

--------------------------------------------------------------------------------

andersg98 (5/22/2012)

--------------------------------------------------------------------------------

Yes. The concept is called 'partition elimination'. Especially when scanning the table for a date range, the optimizer will realize it only has to scan the current...

GilaMonster (5/22/2012)

--------------------------------------------------------------------------------

andersg98 (5/22/2012)

--------------------------------------------------------------------------------

Yes. The concept is called 'partition elimination'. Especially when scanning the table for a date range, the optimizer will realize it only has to scan the current year...

Gullimeel (5/26/2012)

--------------------------------------------------------------------------------

I do not want to go that path anymore. I was not testing the performance of max/min or top 1.I used wrong operator to show what i wanted to...