In SSMS have you run a show plan on the query ?
Also... you could use SQL profiler as well to show what is happening when you send the code down the line from your client application.
How are you calling the code from the SQLCLient ? Can you post the code here so we can take a look at that as well.
Sorry for the update... Can you send us the structore of the table as well. Indexes and all.
>As soon as I created an index on that field, it started to be as fast as in the SSMS environment. And, that field is not even used in any WHERE clause condition. So, does that mean we have to index all the fields from all the tables in order to avoid this situation when using the SQLClient provider from a ASP.NET Web site where this situation does not happen in the SSMS environment. This is a good question.
Rod Paddock
Editor in Chief CoDe Magazine
President Dash Point Software, Inc.
VP Red Matrix Technologies,Inc.