Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Create and use temporary INDEX Expand / Collapse
Author
Message
Posted Monday, January 28, 2008 3:29 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
Good morning at all.

Im using a Db on a Sql 2005 with Vb .Net
I want to create a temporary index to use only when my program is running.

Is possible or i can search another solution

thank you very much !!!
Alberto.
Post #448184
Posted Monday, January 28, 2008 5:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:08 PM
Points: 7,025, Visits: 6,194
What are you trying to achieve with this temporary index? And why do you want it to only be temporary?



Brandie Tarvin, MCITP Database Administrator

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 #448218
Posted Monday, January 28, 2008 5:10 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
the database i'm reading is not mine.
but is necessary form me to check which information are stored inside.

I dont want to change something , if i put inside a table a new index.

Post #448222
Posted Monday, January 28, 2008 5:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:08 PM
Points: 7,025, Visits: 6,194
That explanation doesn't help me figure out how to help you, unfortunately. What information are you trying to check?

Also, if this DB isn't yours, you need to talk to the person who owns it. By adding an index, ANY index, you are making changes to the table. There are no such things as "temporary" indexes.

You might be able to pull the data into a temporary table and create an index on that, then drop the temp table when you're done. But that's the closest you'll come to a temporary index without making changes to the base table.



Brandie Tarvin, MCITP Database Administrator

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 #448232
Posted Monday, January 28, 2008 5:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:51 AM
Points: 14,788, Visits: 27,264
Further, since as you say this isn't your database, if the owner has set up security appropriately, you probably won't have DDL permissions.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #448250
Posted Monday, January 28, 2008 6:27 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
I understand, but my problem is the table dimension.
This table is very big and my problem have to have fast answer.

I see that inside this table there are no indexes and my question is if I create one tbl to drop at the end of my loop, if there are some changes on the db.

Post #448264
Posted Monday, January 28, 2008 7:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:08 PM
Points: 7,025, Visits: 6,194
Yes, there are changes on the DB. Both when you create the index and when you drop it.

Additionally, if you create a clustered index, you're changing the whole structure of the table itself and maybe the location, depending on where you place the clustered index. If the table is that huge, creating an index might take as much time as just running the query without one.



Brandie Tarvin, MCITP Database Administrator

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 #448282
Posted Monday, January 28, 2008 7:22 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.....is better i don't use a create !!!!

i make one "Select count(*)" to know if there are some record inside table.
I put one "Where" option but is very very long.........
Which kind of other method i can use ???


Post #448291
Posted Monday, January 28, 2008 7:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:08 PM
Points: 7,025, Visits: 6,194
Alberto,

Unfortunately, without more details on what you're trying to pull from the table, I can't assist you further. You need to give more details on what you're trying to accomplish.



Brandie Tarvin, MCITP Database Administrator

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 #448298
Posted Monday, January 28, 2008 7:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:01 PM
Points: 7,081, Visits: 14,673
If you simply want to know about existence, you probably want to look at using something like an EXISTS clause, possibly combining it with a TOP 1 clause.

Count(*) is an awful lot of work, just to check for existence.

If you could give us some data specifics (what the tables look like, what you're trying to check for), we can try to give you some code examples that apply to your case.


----------------------------------------------------------------------------------
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 #448299
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse