[RESOLVED] MS SQL 2005 Schema Usage

[RunsWithScissors]

Hi All-

I am curious about "schema" ini MS SQL Server 2005.

Would it be a correct usage to create "schemas" for the purpose of organizing database objects, if there were a valid reason for doing so, or am I missing something?

For example, if I wanted a means of separating my user-created sprocs from others, (or even sub-classing my user sprocs by function), would this be a "correct" useage of schemas in SQL Server?

[GaryMazzone]

I don't think I would do that. I would use schemas for seperation of data only. If more then one person is using the database with tables named the same.

[RunsWithScissors]

SO the schema is used to say, distinguish between database objects with the same name?

i.e.

dbo.tblContracts
sec.tblContracts

Etc?
assuming these are two unique tables, with different data in each, but have been named the same?

[GaryMazzone]

Yes.... It can also be used if you are segregating data in tables with out the same name. I just don't normally do that.

Be careful also.... In Oracle the schema is based on a user name. When a user connects to an Oracle database they are placed in their own schema by default. They do not have access to any other schema unless specifically granted that right.

So Oracle and SQL Server both have schema objects but they are used somewhat differently.

[szlamany]

Schema's changed a real lot in MS SQL 2005

http://msdn.microsoft.com/en-us/library/ms190387.aspx

But to answer your original question - yes - they can be used for organizing

Look at this link

http://searchsqlserver.techtarget.co...184503,00.html

This talks about using schema's for organizing objects.

[leinad31]

Just bear in mind that you will incur name resolution overhead especially for functions/stored procedures if you segregate them into too many schema.