Create and use temporary INDEX

  • 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.

  • 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/[/url]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.

  • 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.

  • 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/[/url]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.

  • 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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • 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.

  • 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/[/url]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.

  • 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 ???

  • 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/[/url]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.

  • 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?

  • 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.

  • 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?

  • 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(*)

  • And if i create a new empty table and i try to link one index to the big table ????

    is possible ?????

  • 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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]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.

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply