asterisk in select_list in queries...

I have been using a subset of the SQL language for queries in FoxPro. I have
been advised against the use of the asterisk in the select_list portion of
queries based on slower performance due to no explicit column names to
instruct SQL Server which indices to utilize. Do you have a short
explanation on the validity of this advice? If so, and it is completely
valid, what are some shortcuts for writing queries where tables contain large
numbers of columns?
Best practice is to avoid using SELECT * in queries (except in an EXISTS
subquery or other subqueries that don't return data and aren't referenced by
an outer query).
Listing the column names makes sense in an N-Tier environment because you
want to make efficient use of network resources by returning to the client
only the data that is actually needed. This is an important difference from
a desktop, ISAM database like FoxPro where you have to retrieve a whole
record whether all the data is required or not.
Listing only the required columns also increases the opportunities for SQL
Server to optimize your query by making use of indexes.
Also, listing column names improves reliability and ease of maintenance. If
you later add another column you don't want to break existing code that
doesn't require that column. Use a column list and then just modify the code
that needs to reference the new column. If you use an asterisk in your
SELECT list then potentially more code could need modification and more code
would need to be unit-tested for each schema change.
Shortcuts? In Query Analyzer you can drag a list of column names from the
Object Browser into the query window. That can save you a lot of typing.
David Portas
SQL Server MVP

asterisk in select_list in queries...

I have been using a subset of the SQL language for queries in FoxPro. I have
been advised against the use of the asterisk in the select_list portion of
queries based on slower performance due to no explicit column names to
instruct SQL Server which indices to utilize. Do you have a short
explanation on the validity of this advice? If so, and it is completely
valid, what are some shortcuts for writing queries where tables contain large
numbers of columns?Best practice is to avoid using SELECT * in queries (except in an EXISTS
subquery or other subqueries that don't return data and aren't referenced by
an outer query).
Listing the column names makes sense in an N-Tier environment because you
want to make efficient use of network resources by returning to the client
only the data that is actually needed. This is an important difference from
a desktop, ISAM database like FoxPro where you have to retrieve a whole
record whether all the data is required or not.
Listing only the required columns also increases the opportunities for SQL
Server to optimize your query by making use of indexes.
Also, listing column names improves reliability and ease of maintenance. If
you later add another column you don't want to break existing code that
doesn't require that column. Use a column list and then just modify the code
that needs to reference the new column. If you use an asterisk in your
SELECT list then potentially more code could need modification and more code
would need to be unit-tested for each schema change.
Shortcuts? In Query Analyzer you can drag a list of column names from the
Object Browser into the query window. That can save you a lot of typing.
--
David Portas
SQL Server MVP
--

ASP.NET & anti-virus & SSRS 2005

Below, I have pasted a portion of an MS MSDN article dealing with a specific issue we are having with Report Server performance. The URL (http://support.microsoft.com/kb/821438/en-us) is the MS KB article on the described fix. The fix mentioned is for "ASP.NET 1.1" but our BP Report Server is using "ASP.NET 2.0".

Has anyone encountered and resolved and how?

Running on box:
ASP.NET 2.0.50727;
SSRS 2005 Sp1 + Hotfix#2175;
Win Server 2003, R2, x64, SP1
Trend Micro OfficeScan Cleint for Win 2003/xp v7.3
IIS Version (I cannot find this #) - ? IE 6.0

===========================
Report Manager or the report server runs very slowly

In some circumstances, ASP.NET applications run very slowly on computers that are running anti-virus software. If the Report Server Web service is restarting frequently, and you are running anti-virus software, you can obtain an ASP.NET fix from Microsoft Customer Support Services.

Symptoms include Web applications or Application Domains restarting for no apparent reason, slow performance, session restarts, and more. For more information about the symptoms, cause, and resolution, see Microsoft Knowledge Base article 821438.

You can find out whether there are excessive server restarts by viewing the number of reportserver_<timestamp>.log files. A new log is created each time the server starts. A large collection of logs created at very short intervals is an indication that the conditions described in article 821438 exist on your server
===========================

It appears the issue goes away with SQL2K5 SP-2 load. Thanks to whoever.....