SQL Server Guru: Execution Plan Issue?

As a developer, we always say "using a stored procedure, instead of a cliet side SQL statement, provides performance benefits". However, it seems it has not been true anymore since SQL Server 7.0.





See SQL online "Execution Plan Caching and Reuse" at http://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentid=28000409





I am quite confused with the following questions:


1. it seems since SQL 7.0, a SQL statement in client side uses the existing execution plan as a stored procedure does. That means SP doesn't has much advantage over SQL statement in terms of performance.





2. It seems, a stored procedure is not always compled ONLY once. If a stored procedure is not used for a long time, it could be kicked out from procedure cashe.





3. In order to use an existing execution plan, it seems that we have to use the fully qualified identifier, such as


SELECT * FROM Northwind.dbo.Employees





instead of


SELECT * FROM Employees





However, I rarely see anyone uses these kind of fully qualified references for objects both in SQL statements and SP. For example, in the sample database pubs and NorthWind, they don't use the fully qualified expression. I only see the use of it in master database.





I guess I might miss something in the issues above. I would like to get any explanation from SQL guru or anybody. Thanks a lot.