So,

Declare @FirstMnth datetime

Select @FirstMnth = dateadd(mm,datediff(mm,0,getdate()),0)

Select Id

where DateCol >=@FirstMnth and DateCol < dateadd(mm,1,@Firstmnth)

BTW this is good reading : http://www.karaszi.com/SQLServer/info_datetime.asp

Marco V (1/4/2012)

iam using result to file so i dont have the overhead...on a side note it will not even complete if i choice any other method as it will...

the best way is

where DateCol >='20111201' and DateCol<'20120101'

Dave

Ninja's_RGR'us (1/4/2012)

How would this go in anything less than 6 minutes in SSIS?

How...

That being the case , maybe a simple SSIS task would work better for you here ?

What is consumming the data ?

The plan estimates then data size to be ~500mb , it has also underestimated the amount of rows by a factor of 2....

Sounds to me like an unindexed foreign key lookup, trigger activity, blocking or indexed views.

Take a look at the plan and the active processes to see if you are being...

sqldba_newbie (12/13/2011)

May be, but that portion runs perfectly fine. Issue is really not there, i am sure there is better way to write that code but the real issue what...

I find a calendar table invaluable ...

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

Im afraid your design is wrong. The EAV (Entity , attribute, value) has been demonstrated time and time again to be inefficent.

You really need to change to a 'correct' normalized...

Sounds like a homework question,

have a look at hierarchyId or a recursive cte.

the blocked process report , would be a good option here.

http://www.sqlpassion.at/blog/PermaLink,guid,0cf203a4-963f-4db0-b26f-4e4d3fb41610.aspx

Sounds like a "parameter sniffing" issue , use the search engine of your choice for that phrase

Neither, not a factor in this debate.

Ok , it wasnt clear from your post.

The issue is , probably , duplicate data

Create TAble #t1

(

id integer

)

go

Create TAble #t2

(

id integer

)

go

insert into #t1 values(1),(1)

go

insert into #t2 values(1),(1)

go

select * from...