Shame. SQL Server 2008 SP1 CU5 is required to make the OPTION (RECOMPILE) trick fly.

Never mind. Try it anyway, and...

The Constant Scan just provides the literal constants in your query to the UDF that comes next - in the...

Pravin Patel-491467 (5/25/2010)

---

I appreciate your help. But i can't change table definition. this is just an example. i want to apply this logic in my live environment SP. It's already...

No worries. I do it all the time. Glad to get in first for once 🙂

In...

Whatever figure you decide on, remember to send 80% of it to the people on here that have helped you over the last year 😀

More seriously, try to think if...

Rows in a table have no intrinsic order, so you need something to define (or remember) the insertion order:

CREATE TABLE dbo.Test59

(

[RowID] ...

DECLARE @dt TABLE (dt DATETIME NOT NULL PRIMARY KEY);

INSERT @dt

SELECT '20090101' UNION ALL

SELECT '20090210' UNION ALL

SELECT '20090312' UNION ALL

SELECT '20090415' UNION ALL

SELECT '20100510';

SELECT ...

And try this:

SELECT @ID =

STUFF

(

...

You know the drill...'actual' execution plan, table definitions, indexes, sample data.

By the way, what exact version of SQL...

Karthik,

The Constant Scan operator is an in-memory table of constants....

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

http://www.sommarskog.se/dyn-search.html

Select Emp_Name, Emp_Surename, Emp_Shomareshenasname, Edarekol_Id, Vahed_Id

from Employee

where (Emp_Name = Case when @VarEmp_Name is not null Then @VarEmp_Name Else Emp_Name end)

And ...

Which version of SQL Server are you using, and exactly what information would you like to see?

In case you hadn't noticed, that was Gail's blog entry that I pointed you at 🙂

This example shows an exactly equivalent logical representation.

Notice that exactly the same physical execution plan is produced.

Also notice that EXCEPT does an implicit DISTINCT on the first table.

DECLARE @A TABLE...

I believe your colleague is referring to this:

http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/

You should be aware of the concept, but don't draw any unshakeable conclusions - there are very few absolutes in SQL Server and...