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


Create and use temporary INDEX


Create and use temporary INDEX

Author
Message
Alberto.Omini
Alberto.Omini
Old Hand
Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)

Group: General Forum Members
Points: 321 Visits: 111
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.
Brandie Tarvin
Brandie Tarvin
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69088 Visits: 9400
What are you trying to achieve with this temporary index? And why do you want it to only be temporary?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Alberto.Omini
Alberto.Omini
Old Hand
Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)

Group: General Forum Members
Points: 321 Visits: 111
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.
Brandie Tarvin
Brandie Tarvin
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69088 Visits: 9400
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)

Group: General Forum Members
Points: 162691 Visits: 33199
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Alberto.Omini
Alberto.Omini
Old Hand
Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)

Group: General Forum Members
Points: 321 Visits: 111
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.
Brandie Tarvin
Brandie Tarvin
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69088 Visits: 9400
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Alberto.Omini
Alberto.Omini
Old Hand
Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)

Group: General Forum Members
Points: 321 Visits: 111
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 ???
Brandie Tarvin
Brandie Tarvin
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69088 Visits: 9400
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Matt Miller (4)
Matt Miller (4)
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51574 Visits: 19475
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?
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