Create and use temporary INDEX

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Create and use temporary INDEX

20 posts, Page 1 of 2

1 2 »»

Create and use temporary INDEX

Rate Topic

Display Mode

Topic Options

Author

Message

Alberto.Omini

Posted Monday, January 28, 2008 3:29 AM

SSC 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

Brandie Tarvin

Posted Monday, January 28, 2008 5:05 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 11:45 AM
Points: 6,717, Visits: 5,788

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, 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 #448218

Alberto.Omini

Posted Monday, January 28, 2008 5:10 AM

SSC 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

Brandie Tarvin

Posted Monday, January 28, 2008 5:20 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 11:45 AM
Points: 6,717, Visits: 5,788

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, 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 #448232

Grant Fritchey

Posted Monday, January 28, 2008 5:52 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 9:49 AM
Points: 13,436, Visits: 25,281

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

Alberto.Omini

Posted Monday, January 28, 2008 6:27 AM

SSC 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

Brandie Tarvin

Posted Monday, January 28, 2008 7:06 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 11:45 AM
Points: 6,717, Visits: 5,788

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, 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 #448282

Alberto.Omini

Posted Monday, January 28, 2008 7:22 AM

SSC 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

Brandie Tarvin

Posted Monday, January 28, 2008 7:32 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 11:45 AM
Points: 6,717, Visits: 5,788

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, 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 #448298

Matt Miller (#4)

Posted Monday, January 28, 2008 7:32 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:45 PM
Points: 7,002, Visits: 13,999

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 »

20 posts, Page 1 of 2

1 2 »»

Permissions