x

Can I have more than one schema in a Database?

Hi

I have a question, and have bit confusion that was asked by one of my colleague, that can we have more than one schema in one database.

Example:

I have a database named as MY_DB and have one schema, can I have other schema in that....

more ▼

asked Jan 09, 2010 at 04:03 AM in Default

Ziyad Mehmood gravatar image

Ziyad Mehmood
61 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort newest

Yes you can, and "schema object" is improved in SQL server 2005/2008. Schemas are like containers in SQL 2005/2008 where you can add objects like tables,views, functions and stored procedures. You may assign SELECT/INSERT/UPDATE/DELETE/EXEC permissions to a schema and then assign permission for a user/role to use the schema. You can actually have two tables with the same name but with different schemas, but I recommend you to read the "best practice" for schemas from Microsoft.

http://technet.microsoft.com/en-us/library/dd283095.aspx

more ▼

answered Jan 09, 2010 at 04:41 AM

Håkan Winther gravatar image

Håkan Winther
15.6k 34 37 48

I wish Microsoft would improve SSMS and add schema as a node level for the databases.
Jan 09, 2010 at 05:29 AM Peso
I agree with you Peso and i added that as a suggestion on "connect", but they rejected it and suggested me to use "filter" in SSMS.
Jan 09, 2010 at 10:42 AM Håkan Winther
I routinely use multiple schemas to logically group tables and help indicate which tables are most closely related to each other. This can help make the database layout more clear in addition to helping with controlling security as Hakan discussed.
Jan 09, 2010 at 01:11 PM TimothyAWiseman
I forgot to mention that one of the benefits with schema is the possibility to group object together, thanks Timothy.
Jan 09, 2010 at 03:59 PM Håkan Winther
+1 - extra info i wanted to add too big for a comment :)
Jan 09, 2010 at 07:04 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

Every software product comes up with a clear documentation. Maximum number of schema for that particular product will be listed in the documentation.

generally a data base can have at-least one schema. definitely you can have one more schema for MY_DB.

more ▼

answered Jan 12, 2010 at 02:25 AM

Nani gravatar image

Nani
232 10 13 13

(comments are locked)
10|1200 characters needed characters left

I'd just like to add that it is a good idea to understand user/schema separation well - understand what the difference between object ownership and belonging to a schema means.

As well as that, it's useful to know which object types are schema bound, and which are not. The general rule of thumb is that objects which are schema bound are found in sys.objects, and other objects are not. However, the rules are more complex than that - for example, XML Schema Collections and User Defined / Assembly / Table Types have a schema, but are not found in sys.objects - tabular child objects (constraints and triggers) have a schema, but it must match the schema of the parent table/view etc.

Please note that this information really relates to 2005+. User Defined Types don't have a schema in 2000, for example.

more ▼

answered Jan 09, 2010 at 07:04 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x33

asked: Jan 09, 2010 at 04:03 AM

Seen: 5656 times

Last Updated: Jan 11, 2010 at 10:12 AM