209

What's the difference between a Database and a Schema in SQL Server? Both are the containers of tables and data.

If a Schema is deleted, then are all the tables contained in that schema also deleted automatically or are they deleted when the Database is deleted?

Marshal
  • 6,551
  • 13
  • 55
  • 91
sqlchild
  • 8,754
  • 28
  • 105
  • 167

5 Answers5

207

A database is the main container, it contains the data and log files, and all the schemas within it. You always back up a database, it is a discrete unit on its own.

Schemas are like folders within a database, and are mainly used to group logical objects together, which leads to ease of setting permissions by schema.

EDIT for additional question

drop schema test1

Msg 3729, Level 16, State 1, Line 1
Cannot drop schema 'test1' because it is being referenced by object 'copyme'.

You cannot drop a schema when it is in use. You have to first remove all objects from the schema.

Related reading:

  1. What good are SQL Server schemas?
  2. MSDN: User-Schema Separation
Community
  • 1
  • 1
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • 6
    in sql server 2005 and above versions , by default everything goes inside the DBO schema if we don't mention any schema name while creation of database objects? – sqlchild Mar 16 '11 at 10:19
  • @sqlchild yes that's right. A schema is required, every object is held in a schema, so the default is dbo – RichardTheKiwi Mar 16 '11 at 10:23
  • Would you consider replicating tables in the same database and group them in schema according to different client, instead of creating separate database for each client a good practice ? – June Dec 25 '14 at 19:49
  • Is the logic same for AWS Redshift? Anyone? I am confused between the two in terms of AWS. – Aakash Basu Apr 10 '19 at 08:42
30

Schema is a way of categorising the objects in a database. It can be useful if you have several applications share a single database and while there is some common set of data that all application accesses.

Pang
  • 9,564
  • 146
  • 81
  • 122
ceth
  • 44,198
  • 62
  • 180
  • 289
13

Database is like container of data with schema, and schemas is layout of the tables there data types, relations and stuff

Saghir A. Khatri
  • 3,429
  • 6
  • 45
  • 76
  • 3
    This isn't correct for SQL Server specifically; Tables have schemas in general databases yes, but Schemas (for who knows what reason) in SQL Server are also effectively namespaces that contain tables (and those tables also have schemas in the traditional sense). Unfortunate name that Microsoft picked for them - `namespace` is far more appropriate. – Peter Clotworthy Oct 10 '18 at 15:55
8

Schema says what tables are in database, what columns they have and how they are related. Each database has its own schema.

Robert
  • 3,276
  • 1
  • 17
  • 26
  • but if i do not create any schema explicitly and create tables without mentioning any schema name then where do the tables are created . by default they must be going into the dbo schema. isn't it? – sqlchild Mar 16 '11 at 10:17
  • yes but usually you don't deal with schema manually, you create tables either from some management tool or using code first feature of some ORM tool – Robert Mar 16 '11 at 10:19
  • i use sql server management studio and i don't mention any schema name – sqlchild Mar 16 '11 at 10:22
6

Schema in SQL Server is an object that conceptually holds definitions for other database objects such as tables,views,stored procedures etc.

Hardik Mishra
  • 14,779
  • 9
  • 61
  • 96