Can I have Multiple Schemas in a SQL Server Database ?

• Raj Malwade

  Mr or Mrs. 500

  Points: 594

  More actions

  August 2, 2004 at 10:41 am

  #83515

  Hi :

  I am new to SQL Server. I am used to ORACLE. In ORACLE I can have multiple schemas in a single databse instance with differerent login's associated with each schema. Can I do similar thing in SQL Server ?  I would like to create single database instance and have multiple schemas. I see that "dbo" is one of the schemas. Can I add other schemas in the same database like "stage", "prod", "dev" etc ?  Or is it better to have seperate databases on the same server called "stage", "prod", "dev" etc?

  Thanks for your help.

   

• Steve Jones - SSC Editor

  SSC Guru

  Points: 737236

  More actions

  August 2, 2004 at 11:27 am

  #517312

  I have separate databases, but there is no reason you cannot have them in one. SQL Server supports multiple schemas. The terminology here is "owner" instead of "schema". You can use sp_changeobjectowner, to move object between owners.

• merceroz

  SSC Enthusiast

  Points: 132

  More actions

  August 2, 2004 at 11:53 pm

  #517375

  There are different ways of thinking about it but one way is ...

  Oracle                     SQL

  Database       =     Server

  Schema         =     Database

  This would suggest a number of databases, rather than one database with a number of similar objects owned by different users.

  You could do it the othe way - I think the main difference would you'd need to be more precise in code and practices.  If you are ambiguous in your SQL / code it is less likely to work.  Where as if you had different databases with uniquely name objects the way SQL interprets ambiguous code generally works.

  I've generally found SQL more forgiving than Oracle.  If you are used to Oracle then you'd probably have no problems being precise and using SQL the way you suggest.

  The only other question is why?  What is it you want to achieve?

• Greg Charles

  SSC-Forever

  Points: 45403

  More actions

  August 3, 2004 at 9:43 am

  #517482

  I might as well throw in my 2 cents.  I've always equated Oracle & Sql Server this way: 

  Oracle                   SQL Server

   

  Database               Server instance

  Tablespace            Database

  Schema                  Owner

   

  One of the advantages of using separate databases is that you can backup each database separately.  There's no facility for separate backups of different owner's objects in a single database.

  Greg

  Greg