Is there any reason why I should write this:
SELECT dbo.Contacts.LastName FROM dbo.Contacts
Well, here's a code maintenence reason, not a performance reason: I worked
in an enviroment where it was decided create Beta versions of all the stored
procedures so that certain users could use whatever version of the software
that was in beta. So, an SQL user named "beta" was created, and beta-owned
versions of all the dbo stored procedures were released. The beta version of
the front end application was configured to call the beta-owned database
objects. There were no beta versions of the tables because this was done in
a production OLTP environment, so the data had to be all captured into the
dbo tables. Within the stored procedures, the table names were not qualified
with the owner prefix. This set-up runs nicely for quite a long time with no
glitches. Then one day I am asked to create beta versions of a couple of
tables that so that we can test some performance enhancements we were
working on. So I do this and after a few seconds I query the beta tables I
just created, and much to my suprise, I find some data in there that was
entered by the beta end-user application. I caught a fish! I was not aware
at the time that if you don't qualify your table name with the owner prefix,
SQL Server first checks for a table owned by the user, in this case "beta".
So, because none of the stored procedures used owner-qualified table names,
my new beta tables started capturing data (*ahem* payments), that were
entered in by the "beta" application users. I had to quickly rename by beta
tables to something unique and insert the fish I caught into the dbo tables
where it belonged. Lesson learned the hard way!
