SQL Server Performance

Need help on indexes

Discussion in 'SQL Server 2005 General Developer Questions' started by mrtweaver, Nov 17, 2008.

  1. mrtweaver New Member

    I have a historical database this database is 1M+ records. Off of this historical database I have a view that uses a where clause to only show the last 3 days of production. What I am looking to try to do is find an auto increment field in the historical database that I can pull over using a Query into the view. This way I could use the following type query to get the data from the live table where there has been a change in the pagemech column:
    select t1.machine, t1.start, t1.mech from livedata t1 left join historical t2 on t1.{?} != t2.{?}
    The t1.{?} is the column that is copied from the Historical table into the view
    The t2.{?} is the auto incrementing column located in the historical table. This is the one I dont know where it is located or what to use. Someone in another type forum said about using an index, however is Microsoft SQL I can not seem to find out if this field is auto incrementing and how to access it so I can put it in a query to be placed in a view.
    Can someone please help.
    I am only somewhat familar with MS SQL and the only way I know of doing things is with Management Studio and that is very limited
  2. SteveWolford New Member

    To find the column in table T2 that is auto incrementing, you have several options. You could expand the table in the Management Studio Object Explorer, then expand the column list, then right-click on each column, select Properties and check the "Identity" property. The column that is auto incrementing will have "True" for its Identity property. If there are a lot of columns, this will be time consuming.
    Another approach would be to right click on the table, select "Script table as" -> "CREATE to" -> "New Query Editor Window". The script may look a little unfamiliar to you. But, all that you have to do is search for the word "IDENTITY" in this script. It will be directly to the right of the auto incrementing column. After you have found this column name, make a note of it and close this window (do not run the script!).
    Performance is a separate issue. Your join should perform well if there is an index on T2 that has the auto-incrementing column as its first column. To see the indexes on the table, look in the Object Explorer again, expand the table, and expand the Indexes tab. This shows the indexes that exist on T2. To see what columns an index is composed of, right click on the index and select Properties.
    Hope this helps!

Share This Page