Does many tables matters

Does it matter if we have hugh number of tables vs few tables.
One example is this.
We have a table called Vendors where VendorID is the Primary key, and
another table VendorNotes (VendorID Int, Note varchar(500)) where VendorID
is the foreign key. This is one to one relation, and all vendors won't have
notes.
Is it good practice to do like this, or just add Note column to the vendors
table, and let it be null.
And does it matter if we add many columns to a table without using it.
Please give me some advices/suggestions. I need it desperately.
Thanks for any help you can provide
SteveDatabases with hundreds, even thousands, of tables is not that unusual.
Unused columns take small amounts of storage space (space is inexpensive).
The trade off is storage/retreival cost vs. development/programming cost. In
general, I think it's a 'non issue'.
Having Vendors and VendorNotes is quite acceptable -especially if the notes
are subject to frequent change and growth in size. Of course, the db purists
would say NO, all data related directly to the Vendor key *should* be in the
same table. Others would accept this arrangement for performance and
stability reasons.
So, the tried and true response is: It Depends. It depends upon what works
best for your design and the skill sets of those that have to develop and
maintian the applications that use the database.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Steve, Putman" <Steve@.noemailcom> wrote in message
news:OY$nP5tlGHA.4144@.TK2MSFTNGP05.phx.gbl...
> Does it matter if we have hugh number of tables vs few tables.
> One example is this.
> We have a table called Vendors where VendorID is the Primary key, and
> another table VendorNotes (VendorID Int, Note varchar(500)) where VendorID
> is the foreign key. This is one to one relation, and all vendors won't
> have notes.
> Is it good practice to do like this, or just add Note column to the
> vendors table, and let it be null.
> And does it matter if we add many columns to a table without using it.
> Please give me some advices/suggestions. I need it desperately.
> Thanks for any help you can provide
> Steve
>
>|||Thanks Arnie for your quick reply.
My assumption was that it does not take any storage space if column values
are null.
Steve,
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OXLSCFulGHA.4512@.TK2MSFTNGP04.phx.gbl...
> Databases with hundreds, even thousands, of tables is not that unusual.
> Unused columns take small amounts of storage space (space is inexpensive).
> The trade off is storage/retreival cost vs. development/programming cost.
> In general, I think it's a 'non issue'.
> Having Vendors and VendorNotes is quite acceptable -especially if the
> notes are subject to frequent change and growth in size. Of course, the db
> purists would say NO, all data related directly to the Vendor key *should*
> be in the same table. Others would accept this arrangement for performance
> and stability reasons.
> So, the tried and true response is: It Depends. It depends upon what works
> best for your design and the skill sets of those that have to develop and
> maintian the applications that use the database.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Steve, Putman" <Steve@.noemailcom> wrote in message
> news:OY$nP5tlGHA.4144@.TK2MSFTNGP05.phx.gbl...
>|||Oh NULL values take up storage space alright.
"Steve, Putman" <Steve@.noemailcom> wrote in message
news:eo2IHYwlGHA.748@.TK2MSFTNGP02.phx.gbl...
> Thanks Arnie for your quick reply.
> My assumption was that it does not take any storage space if column values
> are null.
> Steve,
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:OXLSCFulGHA.4512@.TK2MSFTNGP04.phx.gbl...
>|||NULL values do take up storage space. Not as much as, say, a CHAR(40), but
unfortunately representing NULLs does take up some space. Is space your
primary concern?
"Steve, Putman" <Steve@.noemailcom> wrote in message
news:eo2IHYwlGHA.748@.TK2MSFTNGP02.phx.gbl...
> Thanks Arnie for your quick reply.
> My assumption was that it does not take any storage space if column values
> are null.
> Steve,
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:OXLSCFulGHA.4512@.TK2MSFTNGP04.phx.gbl...
>|||I tend to avoid one-to-one joins if I can. I recognize that NULLs can
take up space, and uing a 1-to-1 is a solution for that, but having a
1-to-1 join means that everytime I want to pull back information about
a vendor (including notes), I have to perform a table join. This (in
my opinion) is unnecessary in most scenarios. If an attribute of an
entity exists, then it should belong with that entity.
However, why do your vendors only have one note? This seems like a
great scenario for a 0-to-many join; if you want to add information to
a vendor, INSERT another row in your notes table. If two people are
adding notes about a vendor, then there is minimal opportunity for
concurrency issues.
In sum: if your entity (Vendor) truly has only one instance of an
attribute (VendorNote), then I would include it in the table, and allow
for NULLs (again, a design choice). However, I would first question
why is a VendorNote a singularity.
Stu
Steve, Putman wrote:
> Does it matter if we have hugh number of tables vs few tables.
> One example is this.
> We have a table called Vendors where VendorID is the Primary key, and
> another table VendorNotes (VendorID Int, Note varchar(500)) where VendorID
> is the foreign key. This is one to one relation, and all vendors won't hav
e
> notes.
> Is it good practice to do like this, or just add Note column to the vendor
s
> table, and let it be null.
> And does it matter if we add many columns to a table without using it.
> Please give me some advices/suggestions. I need it desperately.
> Thanks for any help you can provide
> Steve|||>> We have a table called Vendors where VendorID is the Primary key, and an
other table .. <<
The other table is tricky than your pseudo-code:
CREATE TABLE VendorNotes
(vendor_id INTEGER NOT NULL PRIMARY KEY
REFERENCES Vendors(vendor_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
vendor_note VARCHAR(500) NOT NULL);
The **required** uniqueness constraint has overhead. The **required**
DRI actions have overhead. Or you can take the attitude that the
database can fill up with orphans and other crap until it chokes or has
no integrity. And every time you use it, you need an OUTER JOIN. My
favorite was one of these things where a series of identifiers got
re-used and inherited orphans in the un-constrainted 1:1 table.
The cost of adding a few of NULLs is basically a bit flag to mark a
column as NULL-able, or you can default it to an empty string. That is
not looking so bad now.|||Thanks Guys,
Actualy Vendors-VendorNote is just an example I gave.
Actually there are around 40 columns which we have added to a table, and
which is very very rarely used, or may never be used.
In this scenerio should be ok to keep in on a same table or better to
seperate it.
My original question was related to this.
I am still in a learning stage. So I need to follow some good practice.
Steve
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1151201152.819334.35550@.y41g2000cwy.googlegroups.com...
> The other table is tricky than your pseudo-code:
> CREATE TABLE VendorNotes
> (vendor_id INTEGER NOT NULL PRIMARY KEY
> REFERENCES Vendors(vendor_id)
> ON DELETE CASCADE
> ON UPDATE CASCADE,
> vendor_note VARCHAR(500) NOT NULL);
> The **required** uniqueness constraint has overhead. The **required**
> DRI actions have overhead. Or you can take the attitude that the
> database can fill up with orphans and other crap until it chokes or has
> no integrity. And every time you use it, you need an OUTER JOIN. My
> favorite was one of these things where a series of identifiers got
> re-used and inherited orphans in the un-constrainted 1:1 table.
> The cost of adding a few of NULLs is basically a bit flag to mark a
> column as NULL-able, or you can default it to an empty string. That is
> not looking so bad now.
>|||When learning, it's always best to rely on theory. You can develop
"practical" work-arounds later in your career (when theory fails to
perform as well as needed in real-world scenarios). Doing a 1-to-1
join in order to build a complete entity (to avoid the storage of
NULLS) is a practical solution, not a theoretical one.
My .02
Stu
Steve, Putman wrote:
> Thanks Guys,
> Actualy Vendors-VendorNote is just an example I gave.
> Actually there are around 40 columns which we have added to a table, and
> which is very very rarely used, or may never be used.
> In this scenerio should be ok to keep in on a same table or better to
> seperate it.
> My original question was related to this.
> I am still in a learning stage. So I need to follow some good practice.
> Steve
>
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1151201152.819334.35550@.y41g2000cwy.googlegroups.com...

Does It Matter...

MSSQl 2000
When creating a select statement with joins... does it matter where you plac
e additional where-clause criteria.
Considering the two examples below, is it more efficient to place additional
filtering criteria within the join section.? Does it weed out extra rows be
fore joining them? or should I put anything that is not pertinent to the joi
n itself down below in its own where clause?
EXAMPLE 1
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'
inner join table_c C ON C.column_2 = B.column_2
EXAMPLE 2
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
inner join table_c C ON C.column_2 = B.column_2
WHERE
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'For INNER JOINs, I like to put the JOIN criteria in the ON clause and the
filtering criteria in the WHERE clause. This makes it very clear to anyone
who inherits the code (or myself, when I go senile) which criteria are for
the relationship and which criteria are meant to limit the end result.
For OUTER JOINs, it can certainly matter, but it depends on your desired
result. You may exclude rows by moving criteria from ON to WHERE or vice
versa. I don't know of any situations in INNER JOIN where this is true, but
I bet Itzik or Steve will reproduce one if it exists.
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:u8ljCwnfGHA.764@.TK2MSFTNGP03.phx.gbl...
MSSQl 2000
When creating a select statement with joins... does it matter where you
place additional where-clause criteria.
Considering the two examples below, is it more efficient to place additional
filtering criteria within the join section.? Does it weed out extra rows
before joining them? or should I put anything that is not pertinent to the
join itself down below in its own where clause?
EXAMPLE 1
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'
inner join table_c C ON C.column_2 = B.column_2
EXAMPLE 2
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
inner join table_c C ON C.column_2 = B.column_2
WHERE
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'|||Pre SQL 2000 SP4 I would say "Yes" it does matter.
Post SP4 I would say "No" it doesn't matter.
SP4 has given some huge performance gains at my site.
However, try it for yourself. Use Profiler / view the Execution plan etc.
To establish your version use SELECT @.@.VERSION.
http://www.aspfaq.com/SQL2000Builds.asp
--
HTH. Ryan
"rmg66" <rgwathney__xXx__primepro.com> wrote in message news:u8ljCwnfGHA.764
@.TK2MSFTNGP03.phx.gbl...
MSSQl 2000
When creating a select statement with joins... does it matter where you plac
e additional where-clause criteria.
Considering the two examples below, is it more efficient to place additional
filtering criteria within the join section.? Does it weed out extra rows be
fore joining them? or should I put anything that is not pertinent to the joi
n itself down below in its own where clause?
EXAMPLE 1
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'
inner join table_c C ON C.column_2 = B.column_2
EXAMPLE 2
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
inner join table_c C ON C.column_2 = B.column_2
WHERE
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'|||Thanks Aaron,
Actually I'm more concerned with performance at this point.
Any thoughts on that...
Robert
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message news:OUZNW4nfGH
A.2032@.TK2MSFTNGP02.phx.gbl...
> For INNER JOINs, I like to put the JOIN criteria in the ON clause and the
filtering criteria in the WHERE clause. This makes it
> very clear to anyone who inherits the code (or myself, when I go senile) w
hich criteria are for the relationship and which
> criteria are meant to limit the end result.
> For OUTER JOINs, it can certainly matter, but it depends on your desired r
esult. You may exclude rows by moving criteria from ON
> to WHERE or vice versa. I don't know of any situations in INNER JOIN wher
e this is true, but I bet Itzik or Steve will reproduce
> one if it exists.
>
>
> "rmg66" <rgwathney__xXx__primepro.com> wrote in message news:u8ljCwnfGHA.7
64@.TK2MSFTNGP03.phx.gbl...
> MSSQl 2000
> When creating a select statement with joins... does it matter where you pl
ace additional where-clause criteria.
> Considering the two examples below, is it more efficient to place addition
al filtering criteria within the join section.? Does it
> weed out extra rows before joining them? or should I put anything that is
not pertinent to the join itself down below in its own
> where clause?
> EXAMPLE 1
> SELECT
> A.column_1
> B.column_2
> C.column_3
> FROM
> table_a A
> inner join table_b B ON B.column_1 = A.column_1 and
> B.column_2 = 1 and
> B.column_3 = 'boys' and
> B.column_4 between '01/01/06' and '01/31/06'
> inner join table_c C ON C.column_2 = B.column_2
>
> EXAMPLE 2
> SELECT
> A.column_1
> B.column_2
> C.column_3
> FROM
> table_a A
> inner join table_b B ON B.column_1 = A.column_1 and
> inner join table_c C ON C.column_2 = B.column_2
> WHERE
> B.column_2 = 1 and
> B.column_3 = 'boys' and
> B.column_4 between '01/01/06' and '01/31/06'
>
>
>
>|||I think as a rule, the optimizer will do the same thing regardless, although
as Ryan pointed out pre SQL 200 SP 4 it makes a difference. When optimizing
the engine will check only so many possible paths before determining which
one to use, so on larger more complex queries the order of the joins and
criteria can determine which paths get evaluated before it gives up and
chooses one.
I think the bottom line is theoretically it doesn't matter, but the only way
to be totally certain is to test it out both ways. Not just the location of
the criteria, but the order of the tables as well. For simpler queries with
a handful of joins and filter criteria, when the optimizer can afford to
calculate every possibility, it should work the same. If you have dozens of
tables and just as many filters involved, it is worth playing with different
scenarios to see if it makes a difference. With more complex queries the
optimizer can find literally billions of possible execution plans, and
influencing it to look at the right ones can be hit or miss.
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:O1pbODofGHA.4776@.TK2MSFTNGP05.phx.gbl...
> Thanks Aaron,
> Actually I'm more concerned with performance at this point.
> Any thoughts on that...
> Robert
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
message news:OUZNW4nfGHA.2032@.TK2MSFTNGP02.phx.gbl...
the filtering criteria in the WHERE clause. This makes it
which criteria are for the relationship and which
result. You may exclude rows by moving criteria from ON
where this is true, but I bet Itzik or Steve will reproduce
news:u8ljCwnfGHA.764@.TK2MSFTNGP03.phx.gbl...
place additional where-clause criteria.
additional filtering criteria within the join section.? Does it
is not pertinent to the join itself down below in its own
'01/31/06'
>|||I just did one of the MS Courses last w which covered this. Whilst the
reality seems to be what the others have said. MS considers is more correct
to place the items on the join itself, as this will help SQL to choose the
best execution plan. The idea is that placing more items on the join, means
that the selected table will return less results, before the Join is
executed.
The blurb says that the Join syntax is evaluated before the Where syntax.
The course is the optimising and tuning course for SQL 2005!
My personal view is to use the Join in preference to the Where clause, I
find that it helps to make the syntax clearer and easier to understand.
Regards
Colin Dawson
www.cjdawson.com
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:eqcg3SofGHA.2208@.TK2MSFTNGP05.phx.gbl...
>I think as a rule, the optimizer will do the same thing regardless,
>although
> as Ryan pointed out pre SQL 200 SP 4 it makes a difference. When
> optimizing
> the engine will check only so many possible paths before determining which
> one to use, so on larger more complex queries the order of the joins and
> criteria can determine which paths get evaluated before it gives up and
> chooses one.
> I think the bottom line is theoretically it doesn't matter, but the only
> way
> to be totally certain is to test it out both ways. Not just the location
> of
> the criteria, but the order of the tables as well. For simpler queries
> with
> a handful of joins and filter criteria, when the optimizer can afford to
> calculate every possibility, it should work the same. If you have dozens
> of
> tables and just as many filters involved, it is worth playing with
> different
> scenarios to see if it makes a difference. With more complex queries the
> optimizer can find literally billions of possible execution plans, and
> influencing it to look at the right ones can be hit or miss.
> "rmg66" <rgwathney__xXx__primepro.com> wrote in message
> news:O1pbODofGHA.4776@.TK2MSFTNGP05.phx.gbl...
> message news:OUZNW4nfGHA.2032@.TK2MSFTNGP02.phx.gbl...
> the filtering criteria in the WHERE clause. This makes it
> which criteria are for the relationship and which
> result. You may exclude rows by moving criteria from ON
> where this is true, but I bet Itzik or Steve will reproduce
> news:u8ljCwnfGHA.764@.TK2MSFTNGP03.phx.gbl...
> place additional where-clause criteria.
> additional filtering criteria within the join section.? Does it
> is not pertinent to the join itself down below in its own
> '01/31/06'
>|||> The blurb says that the Join syntax is evaluated before the Where syntax.
That is only the logical order. For inner joins, it doesn't matter, and I do
n't even think that the
optimizer know what join type you expressed (the query is transformed into a
tree structure before
the optimizer gets hold of it). The optimizer is free to transform the query
in any way as long as
it returns the same information as if it executed the query as per the rules
for the logical order.

> The course is the optimizing and tuning course for SQL 2005!
Interesting. Which one? There are two such courses, one for "admins" and one
for "developers". Also,
can you point to the module and perhaps even page number and I'll have a loo
k at how they phrase it.

> My personal view is to use the Join in preference to the Where clause, I f
ind that it helps to
> make the syntax clearer and easier to understand.
I absolutely agree.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:C1Icg.74278$wl.24621@.text.news.blueyonder.co.uk...
>I just did one of the MS Courses last w which covered this. Whilst the
reality seems to be what
>the others have said. MS considers is more correct to place the items on t
he join itself, as this
>will help SQL to choose the best execution plan. The idea is that placing
more items on the join,
>means that the selected table will return less results, before the Join is
executed.
> The blurb says that the Join syntax is evaluated before the Where syntax.
The course is the
> optimising and tuning course for SQL 2005!
> My personal view is to use the Join in preference to the Where clause, I f
ind that it helps to
> make the syntax clearer and easier to understand.
> Regards
> Colin Dawson
> www.cjdawson.com
>
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:eqcg3SofGHA.2208@.TK2MSFTNGP05.phx.gbl...
>|||Hello Tibor
The course is 2784A: Tuning and Optimising Database Queries User Microsoft
SQL Server 2005
The bit that I was referring two use in Unit 3. Specifically the Query
logical flow diagram on page 2.
Basically it shows the flow as
From & Join --> Where --> Select -- > .... (lots more stuff)
From experience I do agree that it doesn't seem to matter as the query
optimiser does make changes to the query as typed, into how it wants to
produce the results.
Regards
Colin Dawson
www.cjdawson.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OHntJPpfGHA.4304@.TK2MSFTNGP05.phx.gbl...
> That is only the logical order. For inner joins, it doesn't matter, and I
> don't even think that the optimizer know what join type you expressed (the
> query is transformed into a tree structure before the optimizer gets hold
> of it). The optimizer is free to transform the query in any way as long as
> it returns the same information as if it executed the query as per the
> rules for the logical order.
>
> Interesting. Which one? There are two such courses, one for "admins" and
> one for "developers". Also, can you point to the module and perhaps even
> page number and I'll have a look at how they phrase it.
>
> I absolutely agree.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
> news:C1Icg.74278$wl.24621@.text.news.blueyonder.co.uk...
>|||My personal preference is to use the primary key/foreign key relation
columns in the join clause, and all other predicates in the where
clause. This is a very consistent syntax that underscores the table
relations and automatically moves all filters to the where clause.
As mentioned before it is a different story for outer joins...
Gert-Jan
Tibor Karaszi wrote:
>
> That is only the logical order. For inner joins, it doesn't matter, and I
don't even think that the
> optimizer know what join type you expressed (the query is transformed into
a tree structure before
> the optimizer gets hold of it). The optimizer is free to transform the que
ry in any way as long as
> it returns the same information as if it executed the query as per the rul
es for the logical order.
>
> Interesting. Which one? There are two such courses, one for "admins" and o
ne for "developers". Also,
> can you point to the module and perhaps even page number and I'll have a l
ook at how they phrase it.
>
> I absolutely agree.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> "Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
> news:C1Icg.74278$wl.24621@.text.news.blueyonder.co.uk...|||Colin,

> The course is 2784A: Tuning and Optimising Database Queries User Microsoft
SQL Server 2005
> The bit that I was referring two use in Unit 3. Specifically the Query log
ical flow diagram on
> page 2.
Thanks. I had a quick look through the courses after I posted prior reply, a
nd I guessed this was
the one. The important part here is that it is the *logical* flow. Quote fro
m the same page:
"
Note that
although there is a guaranteed logical order, this is not true of the actual
physical order. The
query
processor can process the query in a different order but still ensure the sa
me results, if it can
find a
more efficient method for doing so.
"
If the optimizer had to respect the logical flow, then almost every query wo
uld give us horrendous
performance:
FROM, grab all columns, and even cross join if old style join syntax
WHERE remove the rows that doesn't satisfies the conditions (including the j
oin if old-style join)
GROUP BY
HAVING
SELECT, until now we had all the columns from all the tables
ORDER BY, not until now could we sort the rows
TOP, ouch, all rows had to be sorted until we throw away all but "top n".
:-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:VsIcg.74297$wl.32163@.text.news.blueyonder.co.uk...
> Hello Tibor
> The course is 2784A: Tuning and Optimising Database Queries User Microsoft
SQL Server 2005
> The bit that I was referring two use in Unit 3. Specifically the Query log
ical flow diagram on
> page 2.
> Basically it shows the flow as
> From & Join --> Where --> Select -- > .... (lots more stuff)
>
> From experience I do agree that it doesn't seem to matter as the query opt
imiser does make changes
> to the query as typed, into how it wants to produce the results.
> Regards
> Colin Dawson
> www.cjdawson.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:OHntJPpfGHA.4304@.TK2MSFTNGP05.phx.gbl...
>

Does column order really matter?

Does column order really matter for Query Optimizer to pick index.

Case 1:
Say my CUSTOMER table has one composite index containing FirstName and LastName. FirstName exists prior than LastName. Does the column, FirstName and LastName, order matter to have Query Optimizer to utilize the index when I write WHERE clause in a SELECT statement?

Statement 1:
SELECT * FROM CUSTOMER
WHERE FirstName = 'John' and LastName ='Smith'

Statement 2:
SELECT * FROM CUSTOMER
WHERE LastName ='Smith' and FirstName = 'John'

Will both statement 1 and 2 use the composite index or only statement 1?

Case 2:
Say my CUSTOMER has two single-column indexes. One index is on column FirstName. Another is on column LastName.For statement 1 and 2 above, which index will be picked by Query Optimizer or both? How does QO pick for index?

I read couple book and some books say column order matter but some say no. Which one should I go with? I'm kind of confused.

Column order is not important but SQL Server 2005 comes with something that gives you the benefit of a composite yet it is not a composite, it is called index column include so the lastname will be covered in your query. Indexes are part of the physical design so the RDMS vendors owns and improves on it. Try the link below for details. Post again if you still have questions. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms190806.aspx

|||

Case 1:

Doesn't matter.

Case 2:

Doesn't matter.

Case 3:

SELECT FirstName FROM CUSTOMER WHERE LastName='Smith'

Now, it matters. The index is bad for this query. Create a new index on LastName,FirstName and this will run much faster.

The analogy is quite simple. Look at a telephone book. It's arranged by lastname,firstname. If I ask you to find me John Smith in the phone book, it'll take you a few seconds. If I ask you to find Smith, John in the phonebook, it'll take you a few seconds. If I ask you to find me all the first names of people whose last name is Smith (it'll take a little bit, but you can do it). Now what if I ask you to tell me the last name of every John? Uh...