Is there any kind of performance hit when querying by placing tables in different schemas in SQL Server.
For instance if I had 2 tables, Table1 and Table2, and put Table1 into the test schema and Table2 into the history schema, and then I tried to link the tables in a query, is this implicity slower than having the 2 tables in the same schema?
There should be no performance hit at all. The microsoft oslo project is using schemas extensively, and if there were performance problems, I am sure this would not have been the case.
There is no difference between querying tables from one schema or multiple schemas in the same database.
I use Schemas more for grouping objects together than for anything else.
To gain performance by separation, I would go towards filegroups on separate physical drives.
The only place you might hit a penalty is if you don't schema qualify your queries and force the system to figure out where you're pulling from. It's an extremely minor hit, but it's there. Just write the queries correctly and you won't even have that issue.