Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Create and use temporary INDEX
Add to briefcase
20 posts, Page 2 of 2
««12

Create and use temporary INDEX Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Alberto.Omini
Posted Monday, January 28, 2008 7:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 11, 2010 7:57 AM
Points: 35, Visits: 110
my problem is to know if exist inside my db at least one or more rows with this command

Select count(*) from tabelname where field1 = val1 and field2 = val2

Unfortunately does not exist one index with inside my field1 and field2.
Post #448314
Matt Miller (#4)
Posted Monday, January 28, 2008 8:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:20 PM
Points: 6,998, Visits: 13,947
Again - Count(*) is overkill for a scenario like that. You don't care how many there are, you just want to know there are some matching records.

So instead of
select * from table1
where (select count(*) from table2 where table1.field1=table2.val1 and table1.field2=table2.val2) >0

Try using this (which usually runs faster)
select * from table1
where EXISTS (select top1 *from table2 where table1.field1=table2.val1 and table1.field2=table2.val2)

But still - this would run a LOT faster if you simply asked the owner of said DB to add in the index you need. Is this a vendor DB or something that prevents you from even asking?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #448337
Alberto.Omini
Posted Monday, January 28, 2008 9:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 11, 2010 7:57 AM
Points: 35, Visits: 110
is not for me........
i have to read only one table.

for me is necessary understand if there are some record inside one table.......and i think the only way is to make Select count(*)
Post #448431
Alberto.Omini
Posted Monday, January 28, 2008 10:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 11, 2010 7:57 AM
Points: 35, Visits: 110
And if i create a new empty table and i try to link one index to the big table ????
is possible ?????
Post #448462
Brandie Tarvin
Posted Monday, January 28, 2008 10:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:45 AM
Points: 6,657, Visits: 5,680
You can't link indexes between tables. An index only ever goes on 1 table only.

The only way to link tables is with a foreign key constraint and that's not what you want to do.

Brandie Tarvin, MCITP Database Administrator, MCDBA, MCSA

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #448481
Alberto.Omini
Posted Tuesday, January 29, 2008 2:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 11, 2010 7:57 AM
Points: 35, Visits: 110
Ok !!!

I understnad....or i change situation creating one new table...or i use "Select count(*)" and wait

right ?
Post #448738
Brandie Tarvin
Posted Tuesday, January 29, 2008 4:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:45 AM
Points: 6,657, Visits: 5,680
Like Matt, I would not do a Select Count(*). If you're only looking for the existence of the records instead of caring about how many records there actually are, use his code instead. It's much easier.

Brandie Tarvin, MCITP Database Administrator, MCDBA, MCSA

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #448754
EdVassie
Posted Wednesday, January 30, 2008 3:36 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:26 AM
Points: 2,621, Visits: 2,759
Alberto, is there anyone in your office wearing a blue shirt?

When you answer this, do you count all the people who are wearing blue shirts before answering yes or no. Or do you see just one person wearing a blue shirt and answer yes. Which is quicker for you to do?

The query you are describing in your postings seems to be counting the total number of blue shirts just to decide if at least one person has a blue shirt.

Take another look at the previous posts in this thread. If your query just needs to know a true or false answer, look at the suggestion on how to make this work.

Author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2012, 2008 R2, 2008 and 2005. 25 March 2013: now over 23,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #449248
Brandie Tarvin
Posted Wednesday, January 30, 2008 4:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:45 AM
Points: 6,657, Visits: 5,680
Ed,

That is an *excellent* way of rephrasing the question! Thanks for that. @=)

Brandie Tarvin, MCITP Database Administrator, MCDBA, MCSA

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #449264
Alberto.Omini
Posted Wednesday, January 30, 2008 4:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 11, 2010 7:57 AM
Points: 35, Visits: 110
Thank you very much Ed !

Good answer !!!
I open my eyes !!!!!!!!


thank you for all your support !

bye
Alberto.
Post #449272
« Prev Topic | Next Topic »

Add to briefcase
20 posts, Page 2 of 2
««12

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.