November 9, 2010 at 9:14 am
Hi, would the following query make any difference if I joined the two tables using a candidate key or if I matched separately? I'm thinking if I created a candidate key it would match better.
select a.EmpNum, a.xDate, b.EmpNum, b.xDate
from
t1 as a
join
t2 as b
on (a.EmpNum + a.xDate) = (b.EmpNum + b.xDate)
OR
on a.EmpNum = b.EmpNum and a.xDate = b.xDate
November 9, 2010 at 10:48 am
Use the second method - you'll be able to utilize available indexes. The first method will end up with scans on both tables.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 9, 2010 at 11:40 am
Thanks WayneS, I appreciate you taking the time to explain.
November 9, 2010 at 12:03 pm
No problem. Do you understand why the first method won't utilize the indexes?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 9, 2010 at 12:10 pm
Actually no I don't, my knowledge of indexes in general is limited...
November 9, 2010 at 12:13 pm
Marcus Farrugia (11/9/2010)
Actually no I don't, my knowledge of indexes in general is limited...
Hm, have you any experience then in looking at execution plans?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 9, 2010 at 12:18 pm
Hi Craig, other than reading you're link:
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
No I haven't seen execution plans. Looks like I have some homework to do... 🙂
November 9, 2010 at 12:22 pm
Marcus Farrugia (11/9/2010)
Hi Craig, other than reading you're link:http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
No I haven't seen execution plans. Looks like I have some homework to do... 🙂
Since you're willing to learn, let me point you in the right direction on your adventure with google. You'll want to start with these terms:
-- Indexes
Clustered Index Seek
Non-Clustered Index Seek
Bookmark Lookup
-- Index usage
SARGability (it stands for Search Argument usability)
Index selectivity (or statistic selectivity)
-- Execution Plans
That's actually easier. There's a free e-book by Grant Fritchey called "Dissecting SQL Server Execution Plans". It's amazingly good, and is available here on SSC in the 'Books' link on your left. Read one chapter or ten, you will learn a lot. You'll be better off reading it after getting a better understanding of indexing and search arguments above.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 9, 2010 at 12:26 pm
Great Craig, I'll look into your suggested reading links.
November 9, 2010 at 12:47 pm
Marcus Farrugia (11/9/2010)
Actually no I don't, my knowledge of indexes in general is limited...
Marcus,
Let me try to simply explain.
An index is on multiple columns - in your case, EmpNum and xDate.
Your index is not on multiple columns concatenated together (EmpNum + xDate).
So, the query needs to reference each part of the index.
However, if you use anything that isn't the same datatype of the indexed column, then you incur problems where the index won't be used. Say you have a column of varchar(6), but the value is '006985'. If your query just does "where column = 006985" (note the missing single quotes around the value), then the value in the index will be converted to an integer. This will require scanning the entire index, converting each value along the way, to see if it matches the integer value that you specified to search on (6985 - leading zeros are removed). Same thing if you have a datetime column and you specify something like '20101109' - you will be doing conversions. Functions also can't use an index - the index is built on the column while the function is manipulating the column. Summary: you need to specify each column separately, and it has to be the identical datatype, or you will most likely end up scanning either the index or the table.
Does this help?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 9, 2010 at 12:48 pm
@craig-2: +10! Great job in helping to educate!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 9, 2010 at 1:42 pm
WayneS (11/9/2010)
Marcus Farrugia (11/9/2010)
Actually no I don't, my knowledge of indexes in general is limited...
Marcus,
Let me try to simply explain.
An index is on multiple columns - in your case, EmpNum and xDate.
Your index is not on multiple columns concatenated together (EmpNum + xDate).
So, the query needs to reference each part of the index.
However, if you use anything that isn't the same datatype of the indexed column, then you incur problems where the index won't be used. Say you have a column of varchar(6), but the value is '006985'. If your query just does "where column = 006985" (note the missing single quotes around the value), then the value in the index will be converted to an integer. This will require scanning the entire index, converting each value along the way, to see if it matches the integer value that you specified to search on (6985 - leading zeros are removed). Same thing if you have a datetime column and you specify something like '20101109' - you will be doing conversions. Functions also can't use an index - the index is built on the column while the function is manipulating the column. Summary: you need to specify each column separately, and it has to be the identical datatype, or you will most likely end up scanning either the index or the table.
Does this help?
Yes it does give me a better understanding of what an index is, it's actually not what I thought it was and also doesn't seem as complicated as I thought it was either, this is a good starting point for me to grasp the concept clearly
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply