SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index


Index

Author
Message
BeginnerBug
BeginnerBug
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1007 Visits: 350
consider a table

create table company(com_id int primary key,cname varchar(150),clocation varchar(50),cphone varchar(50))


here com_id will be clustered index since primary key..

if i use

select cname,clocation from company where com_id=24

query... should i create non-clustered index for the columns in select ie cname,clocation.... in which case should i use non-clustered index?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (280K reputation)SSC Guru (280K reputation)SSC Guru (280K reputation)SSC Guru (280K reputation)SSC Guru (280K reputation)SSC Guru (280K reputation)SSC Guru (280K reputation)SSC Guru (280K reputation)

Group: General Forum Members
Points: 280969 Visits: 46613
No need for additional indexes here, the where clause is on the clustered index key. You would consider creating indexes when your queries filter or join on other columns

This may be worth reading http://www.sqlservercentral.com/articles/Indexing/68636/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


BeginnerBug
BeginnerBug
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1007 Visits: 350
Thanks a lot Gail...
BeginnerBug
BeginnerBug
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1007 Visits: 350
With this one i attached two image files which depicts two execution plan of 2 queries which gives same results. which execution plan is better one.. how to calculate the perfect execution in terms of cost?. any suggestion pls?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (280K reputation)SSC Guru (280K reputation)SSC Guru (280K reputation)SSC Guru (280K reputation)SSC Guru (280K reputation)SSC Guru (280K reputation)SSC Guru (280K reputation)SSC Guru (280K reputation)

Group: General Forum Members
Points: 280969 Visits: 46613
Cost is an estimate, it can be wrong, don't count on it. Usually the best query is the fastest one.

Post the actual execution plans, not pictures of them, there's lots of missing info. Save as .sqlplan files and upload.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


BeginnerBug
BeginnerBug
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1007 Visits: 350
i couldnt upload the sql plan file due to server problem...

i m posting my queries.

Query 1:select a.ACCESS_ID as accessId,a.ROLE_ID as dmsRespId,a.USER_ID as dmsUserId,
(select NAME as wfRespName From HR_JOBS yy where yy.JOB_ID=a.ROLE_ID) as wfRespName,
(select FIRST_NAME as wfUserName From HR_PERSONS xx where xx.person_id=a.USER_ID) as wfUserName
from SY_DMS_AUTHORIZATION a where a.ACCESS_ID=6


Query 2: select a.ACCESS_ID as accessId,yy.JOB_ID as dmsRespId,a.USER_ID as dmsUserId,
yy.NAME as wfRespName ,
xx.FIRST_NAME as wfUserName
from SY_DMS_AUTHORIZATION a left outer join HR_JOBS yy on yy.JOB_ID=a.ROLE_ID and a.ACCESS_ID=6 left outer join HR_PERSONS xx on xx.person_id=a.user_id



Query 3: select a.ACCESS_ID as accessId,yy.JOB_ID as dmsRespId,a.USER_ID as dmsUserId,
yy.NAME as wfRespName ,
xx.FIRST_NAME as wfUserName
from SY_DMS_AUTHORIZATION a left outer join HR_JOBS yy on yy.JOB_ID=a.ROLE_ID and a.ACCESS_ID=6 left outer join HR_PERSONS xx on xx.person_id=a.user_id



all of them resulting same records....



which one is fastest?... How can i find out the query which results fastly?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (280K reputation)SSC Guru (280K reputation)SSC Guru (280K reputation)SSC Guru (280K reputation)SSC Guru (280K reputation)SSC Guru (280K reputation)SSC Guru (280K reputation)SSC Guru (280K reputation)

Group: General Forum Members
Points: 280969 Visits: 46613
Can't see a difference between queries 2 and 3. I can't tell which will run faster, you'll have to do that. Turn Statistics Time on and run them.


p.s. Why the meaningless aliases? They don't help readability, rather the opposite. If you're going to alias the tables, use something indicative of the table, eg from SY_DMS_AUTHORIZATION AS sda left outer join HR_JOBS AS hj

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


BeginnerBug
BeginnerBug
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1007 Visits: 350
Oh.... thanks gail.. thanks a lot...
Evil Kraig F
Evil Kraig F
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26321 Visits: 7660
In this specific case, there is a unique time to use a nonclustered index that repeats the left side of the clustered index... but you're not at that point.

That case would be when your table is very, very wide (say, near the 5-6k/record mark) and the majority of your queries accessing the table needs only a small handful of small fields (90% of your calls). As an example of a small handful: an ID column and three or four foreign key columns, also of numeric values, and perhaps a date.

Why? These field could be included in a non-clustered index for higher speed access then trying to deal with the full memory set. It's also redundant and puts a load on your insert/update/delete speeds. It's used primarily for speeding up reads on a light transactional system that's either poorly designed or deals with incredibly wide text data that's rarely used (which can also fall under the header of poorly designed, depending on who you ask).

However, you're not there with your example, so don't do that. I just wanted to throw this in there in case you see other designs that may have done it, so you're not suprised.


- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search