order of columns in where clause

Question

Does the order of columns in where clause matter ? Why? Is there any article related to this ?

Does the order of columns in Composite key Index matter? Why? Is there any article related to this?

Does the order of columns in matter anywhere inside SQL Query? In order,group,IN etc.. Why?

Answer 1

The order of columns in the WHERE clause should not matter. I believe we discussed this problem relatively recently in this forum.

The order of columns in the composite index is very important. First should go the column with the most selectivity. Also if you want to use part of the index expression in the WHERE clause, the item which is going to be selected, should be first in the index.

The order of columns does matter in the ORDER BY clause (as it dictates the order). Say, ORDER BY Item, Price DESC is completely different than ORDER BY Price DESC, Item

In all other places, I believe, the order of columns doesn't matter.

 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Thanks for the reply!!!

As per your reply, order of columns matter in the where clause if the columns are the part of index structure. But if the there are no indexes then order of columns doesnt matter at all in where clause. Did I understand correctly ?

 

Answer 3

I think Naomi said the order of the columns in the WHERE clause does not matter regardless of indexes on the columns.  The optimizer may rearrange when the predicates are evaluated in the query plan as long as the end result is as declared in the query.

However, the order of columns in the index does matter.  The index can be used efficiently only when the high-order column(s) of the index are specified in the WHERE and JOIN clauses.

 

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 4

Another place where the order matters is if you do a GROUP BY ROLLUP (<column list>), since the order of the columns determines which subtotals will be generated.

Tom

Answer 5

The order of columns in the WHERE clause should not matter.

---

This statement holds TRUE as far as the OUTPUT of the query is concerned. However, if you look at the query performance wise, the order of the columns in the WHERE clause plays a very vital role.

The column with the highest chances to fail should be kept on the LEFT MOST part of the expression if the expression is AND based and the reverse is true if the expression is OR based.

Hope, this helps !

---

-Vinay Pugalia
If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
Web : Inkey Solutions
Blog : My Blog
Email : Vinay Pugalia

Answer 6

I think the answers are conflicting here. Do everybody agree with vinaypugalia that order of columns in where clause does matter performancewise ? Is there any article or white paper which proves this ?

 

Thanks,

Answer 7

@Dan Guzman : Naomi replied "Also if you want to use part of the index expression in the WHERE clause, the item which is going to be selected, should be first in the index."

Isnt she saying that the order of columns in where clause matter if the columns are the part of index? Isnt she saying that

if the index is created as (a,b,c) then where clause should have where a=1 and b=2 and c=3. if the index is created as (b,a,c) then where clause should have where b=2 and a=1 and c=3.

Thanks,

Answer 8

Isnt she saying that the order of columns in where clause matter if the columns are the part of index? Isnt she saying that

No, that is not what she said.  The order of the columns in the index does and ORDER BY clause does matter but the order of specification in the WHERE clause does not.  When only some of the columns of the index are specified in the WHERE clause, then those columns must be first in the index in order to use the index efficiently.  The columns can be specified in any order in the WHERE clause.

 

 

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 9

The order of columns in the WHERE clause should not matter. I believe we discussed this problem relatively recently in this forum.

The order of columns in the composite index is very important. First should go the column with the most selectivity. Also if you want to use part of the index expression in the WHERE clause, the item which is going to be selected, should be first in the index.

The order of columns does matter in the ORDER BY clause (as it dictates the order). Say, ORDER BY Item, Price DESC is completely different than ORDER BY Price DESC, Item

In all other places, I believe, the order of columns doesn't matter.

I agree with Naom,

Query optimizer will take care of which index to be used or not.

But if you're creating a composite index, then as thumb rule the most selective column should be defined as first column

 

 

---

SQL Server and T-SQL Tutorials
My Personal Site
Our true mentor in life is science

Answer 10

I don't agree with Vinaypugalia. Please check this quite recent discussion on this topic 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 11

Thanks alot for all the replies. Paul here mentions that order of index keys does not matter for certain cases. You all are experts, so I am sure I am not understanding properly.

http://www.sqlskills.com/blogs/paul/post/How-hard-is-it-to-pick-the-right-non-clustered-indexes.aspx

Does his blog conflicts with what you said(order of index key does matter)?

Answer 12

You may go through this link as well....hope, this will give you more clarity on ORDERING columns in WHERE clause.

---

-Vinay Pugalia
If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
Web : Inkey Solutions
Blog : My Blog
Email : Vinay Pugalia

Answer 13

The link confused me more. The most voted one says:

"With a decent query optimiser: it shouldn't.

But in practice, I suspect it might.

You can only tell for your cases by measuring. And the measurements will likely change as the distribution of data changes in the database."

It says the order shouldnt matter but he suspects it might. Its funny. I am just concerned about SQL Server Optimizer and needed to know if the ordering of columns in where clause matters.

But if I believe in those who said SQL Server Optimizer is intelligent enough to search the fields in the order it wants by looking at the statistics. So, I am now ok with the answer that order of the columns in the where clause do not matter.

My another part of the question is if the order of columns in the index matter. Everybody is saying Yes. But when I went through the following blog it says it doesnt matter for certain cases.So, it might I am not understanding the blog properly or one of the information is wrong. I am newbie in these so wanted to know what is correct.

http://www.sqlskills.com/blogs/paul/post/How-hard-is-it-to-pick-the-right-non-clustered-indexes.aspx

Thanks,

Answer 14

I'd say as a beginner use this rule - create index with the field which is most selective and most used in queries FIRST in the compound index.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 15

I think the answers are conflicting here. Do everybody agree with vinaypugalia that order of columns in where clause does matter performancewise ? Is there any article or white paper which proves this ?

 

Thanks,

Sorry, I'm with Naomi on this one... the order DOES NOT MATTER.

There are countless examples out there of T-SQL's behavior in evaluating predicates in a WHERE clause (and how the order DOES NOT MATTER), though I don't have any links handy... I'm sure Naomi has more than a few examples.

T-SQL is NOT a programming language like C# or VisualBasic or any other language out there.  Those languages WILL do what vinaypugalia suggests, but T-SQL will NOT.  That's because queries in T-SQL are cost-based... the optimizer will do what it thinks is necessary to approach a WHERE clause with the lowest possible cost... therefore it doesn't care about which predicate comes first (as written)... it will start with whatever predicate it feels is best (based on statistics, etc).

The best example is this one:

select CustomerID,CompanyName,PostalCode 
from Northwind.dbo.Customers 
where isnumeric(PostalCode)=1 and convert(int,PostalCode)>99000 
/* 
Msg 245, Level 16, State 1, Line 1 
Conversion failed when converting the nvarchar value 'WA1 1DP' to data type int. 
*/ 

C# or VB or any other language would do the ISNUMERIC evaluation first and, if the PostalCode was NOT numeric, it would short-circuit the evaluation of the second predicate... there would be no need to evaluate it because FALSE AND Anything is equal to FALSE.  But T-SQL sees the CONVERT() function as more cost-effective than the ISNUMERIC() function, and so it attacks that predicate first.  If you want to force T-SQL to do what other languages would do in this case, you'd have to re-write the query like so:

select CustomerID,CompanyName,PostalCode 
from Northwind.dbo.Customers 
where case     
    when isnumeric(PostalCode)=1
    then convert(int,PostalCode)     
    else -1    
   end>99000 
/* 
CustomerID CompanyName       PostalCode 
---------- ----------------------- ---------- 
LAZYK   Lazy K Kountry Store  99362 
OLDWO   Old World Delicatessen 99508 
*/ 

However, that being said, there is one example of a case where the order of the predicates in a WHERE clause DOES matter.  If the predicates are EXISTS (or NOT EXISTS) predicates, then the optimizer DOES evaluate them from left to right.  I don't think it should, but it does.  Here's a link to that phenomenon (in the second section of the article title "NOT EXISTS ==> LEFT (ANTI SEMI) JOIN":

http://bradsruminations.blogspot.com/2010/04/looking-under-hood.html

 

---

--Brad (My Blog)

Answer 16

@Brad: Thanks alot for the reply!!! Sorry,I am confused again. I think your answer is conflicting.

You are saying:

 I'm with Naomi on this one... the order DOES NOT MATTER.

Again you are saying:

However, that being said, there is one example of a case where the order of the predicates in a WHERE clause DOES matter.

Can I conclude that it does matter in some cases which are not documented and nobody know how many cases are there,But for most cases it does not matter ?

 

My another part of the question is if the order of columns in the index matter. Everybody is saying Yes. But when I went through the following blog it says it doesnt matter for certain cases.So, it might I am not understanding the blog properly or one of the information is wrong. I am newbie in these so wanted to know what is correct.

http://www.sqlskills.com/blogs/paul/post/How-hard-is-it-to-pick-the-right-non-clustered-indexes.aspx

Answer 17

@Brad: Thanks alot for the reply!!! Sorry,I am confused again. I think your answer is conflicting.

You are saying:

 I'm with Naomi on this one... the order DOES NOT MATTER.

Again you are saying:

However, that being said, there is one example of a case where the order of the predicates in a WHERE clause DOES matter.

Can I conclude that it does matter in some cases which are not documented and nobody know how many cases are there,But for most cases it does not matter ?

 

 

Yes, the example of the EXISTS was something that was discovered on this forum over a year ago, and so I blogged about it.  It's an unusual case.

Regarding your other question, and the reference to Paul Randal's blog...

The order of the columns in the index DOES matter, and I think some people in this thread gave you some examples or references.

In Paul's blog that you referenced, he says that that for the following query...

WHERE lastname = 'Randal' AND firstname = 'Paul' AND middleinitial = 'S'

... it does not matter if you create a clustered index by (LastName,FirstName,MiddleInitial) or (FirstName,LastName,MiddleInitial) or (MiddleInitial,FirstName,LastName) or any of the other 3 possiblities.  Any of those choices will satisfy that particular query with equal efficiency.

But the blog makes the point that this one query is not going to be the only query for your entire system.  If you have ANOTHER query that is just the following:

WHERE lastname = 'Randal'

Then the best way to satisfy BOTH queries is to have an index that specifies LastName first.

You have to put together indexes that will work best with most queries that will be put up against the database. 

Hope that helps a bit.

 

---

--Brad (My Blog)

Answer 18

Thanks!!!! That helped alot!!!

So, for my second question shall I conclude that,

The first field in the index should be the one which will be used highly in the queries and not the one which has high degree of uniqueness.

For Example:

If you have LastName field with higher degree of uniqueness than FirstName, but Firstname is used most often in the queries than LastName and Firstname, then you need to have FirstName as the first field in the index.

Do I understand correctly?

Thanks,

Answer 19

Yes for the second question.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 20

Thanks!!!! So, if I have firstname and lastname used all the time then the order in the index for firstname and lastname doesnt matter. Right ? If I am right, then I should avoid looking at how unique the column is while creating the index and just look for how often the column is being used in the queries. Right?

Thanks again!!

Answer 21

Generally, yes... The index creation should revolve around the queries that will be made against the database.

You can always create secondary (i.e. nonclustered) indexes to help satisfy other queries as well... you're not forced to choose one and only one index to create.

 

---

--Brad (My Blog)

Answer 22

Yes, but... it also depends on how you use the columns.

If your queries has exact matches on both column (for example WHERE firstname='Gert-Jan' AND lastname='Strik'), then the order of the columns in the index does not matter.

If one predicate does an exact match and the other a range match, then (provided the column is sufficiently selective) the exact match column should go first in the index (for example WHERE firstname BETWEEN 'James' AND 'John' AND lastname = 'Strik').

If both predicates to a range match, then the column that is restricted most should go first in the index (it doesn't matter where you put them in the WHERE clause). So if the ranges of both predicates are equally big, this means that the most selective column should come first (in the index).

 

---

Gert-Jan

Answer 23

Yes, but... it also depends on how you use the columns.

If your queries has exact matches on both column (for example WHERE firstname='Gert-Jan' AND lastname='Strik'), then the order of the columns in the index does not matter.

If one predicate does an exact match and the other a range match, then (provided the column is sufficiently selective) the exact match column should go first in the index (for example WHERE firstname BETWEEN 'James' AND 'John' AND lastname = 'Strik').

If both predicates to a range match, then the column that is restricted most should go first in the index (it doesn't matter where you put them in the WHERE clause). So if the ranges of both predicates are equally big, this means that the most selective column should come first (in the index).

 

---

Gert-Jan

Good points, Gert-Jan!

 

---

--Brad (My Blog)

Answer 24

T-SQL is NOT a programming language like C# or VisualBasic or any other language out there.  Those languages WILL do what vinaypugalia suggests, but T-SQL will NOT.  That's because queries in T-SQL are cost-based... the optimizer will do what it thinks is necessary to approach a WHERE clause with the lowest possible cost... therefore it doesn't care about which predicate comes first (as written)... it will start with whatever predicate it feels is best (based on statistics, etc).

The best example is this one:

select CustomerID,CompanyName,PostalCode 
from Northwind.dbo.Customers 
where isnumeric(PostalCode)=1 and convert(int,PostalCode)>99000 
/* 
Msg 245, Level 16, State 1, Line 1 
Conversion failed when converting the nvarchar value 'WA1 1DP' to data type int. 
*/ 

It seems it does not always work that way....and we can not guarantee this behavior. Please have a look at the below example.

SELECT * FROM JobConfig WHERE Latency_days > 100 AND CONVERT(INT,DBName) = 10

SELECT * FROM JobConfig WHERE CONVERT(INT,DBName) = 10 AND Latency_days > 100

and the result of both of these queries is -

For 1st Query --

(0 row(s) affected)

 

For 2nd Query --
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value 'DIF' to data type int.

 

Here, Latency_Days is of type INT and DBName is of VARCHAR type.

---

-Vinay Pugalia
If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
Web : Inkey Solutions
Blog : My Blog
Email : Vinay Pugalia

Answer 25

What version of SQL are you using?

If I put together something similar...

SELECT * FROM SomeTable WHERE IntColumn>100 AND CONVERT(INT,VarCharColumn)=10
SELECT * FROM SomeTable WHERE CONVERT(INT,VarCharColumn)=10 AND IntColumn>100

...the optimizer re-writes the WHERE clause so that it does the IntColumn comparison first.  In other words, both of the queries above get translated into the following:

SELECT * FROM SomeTable WHERE IntColumn>100 AND CONVERT(INT,VarCharColumn)=10

(You can see this by looking at the query plan and examining the predicate of each query).

It does this because comparing the IntColumn to a constant is more cost-efficient that bothering to do the CONVERT.

So it surprises me that you get the behavior you're getting. 

So what version are you using?  Can you post some code to reproduce this behavior?

---

--Brad (My Blog)

Answer 26

I am using the following version -

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

...the optimizer re-writes the WHERE clause so that it does the IntColumn comparison first.  In other words, both of the queries above get translated into the following:

SELECT * FROM SomeTable WHERE IntColumn>100 AND CONVERT(INT,VarCharColumn)=10

It  seems that the above statement holds TRUE for TableVariables or Temp Tables -

CREATE TABLE #Table (IntCol INT, VarcharCol VARCHAR(10))
INSERT INTO #Table
SELECT 1,'A' UNION ALL
SELECT 2,'B' UNION ALL
SELECT 3,'C' UNION ALL
SELECT 4,'D'


 SELECT * FROM #Table WHERE IntCol > 100 AND CONVERT(INT,VarcharCol) = 10
 |--Table Scan(OBJECT:([tempdb].[dbo].[#Table]), WHERE:([tempdb].[dbo].[#Table].[IntCol]>(100) AND CONVERT(int,[tempdb].[dbo].[#Table].[VarcharCol],0)=(10)))
 

SELECT * FROM #Table WHERE CONVERT(INT,VarcharCol) = 10 AND IntCol > 100
 |--Table Scan(OBJECT:([tempdb].[dbo].[#Table]), WHERE:([tempdb].[dbo].[#Table].[IntCol]>(100) AND CONVERT(int,[tempdb].[dbo].[#Table].[VarcharCol],0)=(10)))

However, for the actual table, below is the execution plan I am getting..

SELECT * FROM JobConfig WHERE Latency_days > 100 AND CONVERT(INT,DBName) = 10
 |--Filter(WHERE:([DBADB].[dbo].[JobConfig].[Latency_days]>(100) AND [Expr1005]=(10)))
    |--Compute Scalar(DEFINE:([DBADB].[dbo].[JobConfig].[NextRunDate]=[DBADB].[dbo].[JobConfig].[NextRunDate], [Expr1005]=CONVERT(int,[DBADB].[dbo].[JobConfig].[DBName],0)))
      |--Compute Scalar(DEFINE:([DBADB].[dbo].[JobConfig].[NextRunDate]=[DBADB].[dbo].[JobConfig].[LastRunDate]+CONVERT_IMPLICIT(datetime,[DBADB].[dbo].[JobConfig].[Latency_days],0)))
         |--Clustered Index Scan(OBJECT:([DBADB].[dbo].[JobConfig].[PK_JobConfig]))
         
 SELECT * FROM JobConfig WHERE CONVERT(INT,DBName) = 10 AND Latency_days > 100
 |--Filter(WHERE:([Expr1005]=(10) AND [DBADB].[dbo].[JobConfig].[Latency_days]>(100)))
    |--Compute Scalar(DEFINE:([DBADB].[dbo].[JobConfig].[NextRunDate]=[DBADB].[dbo].[JobConfig].[NextRunDate], [Expr1005]=CONVERT(int,[DBADB].[dbo].[JobConfig].[DBName],0)))
      |--Compute Scalar(DEFINE:([DBADB].[dbo].[JobConfig].[NextRunDate]=[DBADB].[dbo].[JobConfig].[LastRunDate]+CONVERT_IMPLICIT(datetime,[DBADB].[dbo].[JobConfig].[Latency_days],0)))
         |--Clustered Index Scan(OBJECT:([DBADB].[dbo].[JobConfig].[PK_JobConfig]))

 

Now, it is really surprising for me too ... :)

 

---

-Vinay Pugalia
If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
Web : Inkey Solutions
Blog : My Blog
Email : Vinay Pugalia

Answer 27

Sorry, Vinay, I didn't see your response until now (unfortunately the Alerts are no longer working on this board, so I have no way to track all the threads that I'm participating in).

I think the reason you're getting the plan you're getting is because you have a computed column (NextRunDate).  That forces the Compute Scalar to compute that and then it uses a Filter operator to apply the predicate.  This is actually very interesting... I'm surprised that it does this via a Filter operator as opposed to just applying the predicate directly in the clustered index scan.  I'm assuming because the NextRunDate computed column is dependent on the Latency_days column.  I'll have to try some experiments of my own when I get the time.

In the meantime...

Instead of SELECT * FROM JobConfig, change your query to SELECT DBName,Latency_days FROM JobConfig (or any column list that doesn't involve the NextRunDate column).

That way, I'm thinking it should scan the clustered index with the predicate the same way that the temp table did.

Let me know if you can confirm that.

 

---

--Brad (My Blog)

Answer 28

Hi Brad,

I have tested as per your suggestion and your assumption seems to be correct. Below are my findings -

After explicitly selecting the columns, 

SELECT ID,[DBID],DBName, Latency_days FROM JobConfig WHERE Latency_days > 100 AND CONVERT(INT,DBName) = 10
 |--Clustered Index Scan(OBJECT:([DBADB].[dbo].[JobConfig].[PK_JobConfig]), WHERE:([DBADB].[dbo].[JobConfig].[Latency_days]>(100) AND CONVERT(int,[DBADB].[dbo].[JobConfig].[DBName],0)=(10)))

 SELECT ID,[DBID],DBName, Latency_days FROM JobConfig WHERE CONVERT(INT,DBName) = 10 AND Latency_days > 100
 |--Clustered Index Scan(OBJECT:([DBADB].[dbo].[JobConfig].[PK_JobConfig]), WHERE:([DBADB].[dbo].[JobConfig].[Latency_days]>(100) AND CONVERT(int,[DBADB].[dbo].[JobConfig].[DBName],0)=(10)))

And when added a COMPUTED column to the temp table, it also started behaving the same - i.e. started giving exception.

I have few queries and could you please shed some light on this,

1. Any point to reason out - Why the WHERE clause is not applied while scanning the Table when the computed column was included in the select list.
2. In the WHERE clause, the expression did not re-shuffle when the computed column was included in the select list.

 

---

-Vinay Pugalia
If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
Web : Inkey Solutions
Blog : My Blog
Email : Vinay Pugalia

Answer 29

Vinay...

I can't duplicate the behavior that you're seeing with the Filter Operator.

Can you post something to recreate that?  You said that when you added a computed column to the temp table, it started erroring out.

But I can't make that happen.

Thanks...

---

--Brad (My Blog)

Answer 30

Brad,

I am so sorry and apologize for my below statement...it might have wasted your time

And when added a COMPUTED column to the temp table, it also started behaving the same - i.e. started giving exception.

I wrote that by mistake....actually the COMPUTED column is having not effect on the output. It behaves the same with or without the computed column.

Now, can you please share your views and summarize everything about this behavior.

---

-Vinay Pugalia
If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
Web : Inkey Solutions
Blog : My Blog
Email : Vinay Pugalia

Answer 31

Brad,

I am so sorry and apologize for my below statement...it might have wasted your time

And when added a COMPUTED column to the temp table, it also started behaving the same - i.e. started giving exception.

I wrote that by mistake....actually the COMPUTED column is having not effect on the output. It behaves the same with or without the computed column.

Now, can you please share your views and summarize everything about this behavior.

---

-Vinay Pugalia
If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
Web : Inkey Solutions
Blog : My Blog
Email : Vinay Pugalia

Believe me, I want to share my views... This is an interesting phenomenon to blog about... But unfortunately I can't re-create the problem.

There must be something specific about your JOBCONFIG table that is making it use a Filter rather than just applying the predicate to the Clustered Index Scan, but I don't know what the conditions are... It's something about the computed column, but perhaps there's something else, because I can't reproduce it.

Would you mind posting the DDL (including indexes) of that table for me?  And approximately how many rows are in it?  Then perhaps I can use that to reproduce the behavior and summarize the findings.

Thanks...

 

---

--Brad (My Blog)

Answer 32

Hi Brad,

Please find below the DDL of the Table along with the Indexes -

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[JobConfig](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[DBID] [int] NOT NULL,
	[DBName] [varchar](50) NOT NULL,
	[BackupPath] [varchar](1000) NULL,
	[InternalJobName] [varchar](50) NOT NULL,
	[SuccessEmailIds] [varchar](500) NOT NULL,
	[FailureEmailIds] [varchar](500) NOT NULL,
	[LastRunDate] [datetime] NULL,
	[Latency_days] [smallint] NULL,
	[NextRunDate] AS ([LastRunDate]+[Latency_days]),
	[IsActive] [bit] NULL,
 CONSTRAINT [PK_JobConfig] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [dupindex] ON [dbo].[JobConfig] 
(
	[ID] ASC,
	[DBID] ASC,
	[DBName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

There are around 50 rows in this table as of now.

---

-Vinay Pugalia
If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
Web : Inkey Solutions
Blog : My Blog
Email : Vinay Pugalia

Answer 33

Thank you, Vinay...

Unfortunately, no matter what I do, I cannot duplicate the behavior that you demonstrated.

I always get a Clustered Index Scan with two Compute Scalars, but NO Filter operator (because the Clustered Index Scan applies a predicate based on the WHERE clause).

This is the behavior I expect.  I really WANT TO see the behavior you saw, but I can't do it.  I tried turning statistics off for the table/database... I tried certain environmental settings... but no luck... no change in behavior.

However, all of my tests were with SQL2005 or SQL2008... I don't have SQL2008R2 handy to try it... Perhaps it's something specific to that version?

 

 

---

--Brad (My Blog)