Hi guys, Ok, this might be most fundamental question you guys might came across. But, as i am newbie to SQL I have to ask this question. I have creaed index on table, table doesn't have any constraint or PK or FK but one of the column in table is Index column and the data type is string. Now, in ETL package I need to lookup that table using lookup transformation. My question is do we need lookup on index or table. Can anyone guide me how the index works, after creating index the lookup query has to point index or table. e.g: select * From Index or select * From table Any example much appreciated. Thanks,

You only query the table not the index (well not explicitly in your SQL code anyway). When SQL Server recieves your query, so for example... select * From table ...the query optimizer will decide whether to use an appropriate index if required. All you need to do is decide whether an index is beneficial to your query (through testing) and the optimizer should then decide to use it. So if you do... select * From Country where city = 'Sydney' ...if the optimiser finds (using statistics->selectivity etc, SQL Server collects statistics to help it make its decision, so it will "record" how many instances of the city Sydney exist in the table) that it can use an index to quickly retrieve the rows for Sydney it will do so rather than scanning the entire table (if the table is small or only contains one row with the city Sydney it may well choose to scan the table instead, just like if you opened a telephone directory and there was only one person in it you would not bother going to the index section). In management studio if you write your query you can then view the query plan which is what is generated by the optimizer to see exactly how it has decided to execute your query, including what indexes it has decided to use etc. Anyway try it out for yourself in SQL Server Management Studio to get a better idea of what is happening... http://msdn.microsoft.com/en-us/library/ms178071.aspx Thanks/Neil Moorthy - Senior SQL Server DBA/Developer (OCA 11g, MCITP (2005), MCAD) Please click the Mark as Answer button if a post solves your problem

Select <col list> from tablename should be the lookup query.Nitesh Rai- Please mark the post as answered if it answers your question

Thanks for your help !! Excellent answer !!