My point being , to the 'untrained' eye what is the worst performing of these two queries 😀

edit : Judging only by the output of 'set statistics io on'

Query 1...

It purges any in-memory pages SQLServer has , dont attempt to compare relative performance of queries with doing it.

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

Its all down to the query plans...

See this article on how to post them ,

http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Are you doing a fair test ?

Are you doing 'dbcc dropcleanbuffers' between your tests ?

You simply cant. Rollback will rollback all currently open transactions on that connection.

Data written to @ table do survive the rollback , so maybe you can use that ?

Its traditional to post a question 😀

You can use multiple CTE's within the same statement, heres a simple example.

with cteObjects(Name)

as

(

select Name from sysobjects where name like 'a%'

),

cteWithRow(Name,OrderCol)

as

(

Select Name,...

Why Dont you want to use...

Wrap it in Cte

with cteUsers(UserId,UserName)

as

(

select UserID,dbo.getUserName(UserID) as 'UserName'

from User

)

Select UserId,UserName

from cteUsers

order by

case when @sortorder = 1 then UserId end desc,

case when @sortorder = 2...

FYI , this is the best resource on dates in sqlserver

http://www.karaszi.com/SQLServer/info_datetime.asp

Hope there should of been a question mark after that Gail 🙂

Personally , I prefer security through security and use SQLServer's excellent inbuilt processes. 😉

Not challenging at all if you use a calendar table...

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

Looking forward to this explanation!

http://www.sqlservercentral.com/Forums/FindPost776541.aspx

Use a calendar table

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

Go on then , im curious, how does it help security ?