Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
flchico
Starting Member
46 Posts |
 Posted - 2007-05-17 : 11:12:23
|
| Supposed I have 3 fieldscField1cField2cField3and I have another field 'cFull' that has the data combined of field1+field2+field3.so if:cField1 = '111'cField2 = '222'cField3 = '333'thencFull = '111222333'I have an index 'fields' (field1,field2,field3) and another index on field 'cFull'.When doing a query what is faster and more efficient, WHERE cField1 = 'xxx' AND cField2 = 'yyy' and cfield3 = 'zzz'OR WHERE cFull = 'xxxyyyzzz'Thanks. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-05-17 : 11:29:21
|
| WHERE cFull = 'xxxyyyzzz'have you tried it, measured it?what kind of results do you get?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
 |
|
|
flchico
Starting Member
46 Posts |
Posted - 2007-05-17 : 11:35:54
|
| Thanks for the response.I haven't tested it with querying each field. My question was just a general rule about speed and how sql works best, basically about how the engine performs more efficiently.I was hoping that the answer would have been going with the fields that way I avoid creating an extra field for the sake of speed in special circumstances. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-05-17 : 11:40:25
|
| ohh... well then you create a covering index that contains all 3 columns._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
flchico
Starting Member
46 Posts |
Posted - 2007-05-17 : 12:59:22
|
| Thanks again for your help. Is there a good book you recommend regarding how the indexing works, how sql server uses the indexes, how it picks them. Lately I've done quite a few queries and it's funny how depending on certain things that you think would never affect the query it affects it greatly going from a index seek to a scan etc. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-05-17 : 14:03:23
|
| these 3 will give you everything you need:http://www.insidesqlserver.com/thebooks.html_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-17 : 14:04:49
|
| Note that the selectivity of the FIRST key in the index may be decisive in whether SQL Server chooses to use that index, or not.Kristen |
|
|
|
|
|
|
query method