CODEWITHSUNDEEP
sql-serverdatabasesql-server-2005database-schema
sqlchild
sqlchild

Reputation: 9064

Difference between database and schema

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?

Upvotes: 214

Views: 251748

Answers (5)

ceth
ceth

Reputation: 45285

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.

Upvotes: 30

RichardTheKiwi
RichardTheKiwi

Reputation: 107696

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

Upvotes: 213

Saghir A. Khatri
Saghir A. Khatri

Reputation: 3128

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

Upvotes: 13

Hardik Mishra
Hardik Mishra

Reputation: 14877

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

Upvotes: 6

Robert
Robert

Reputation: 3302

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

Upvotes: 8

Related Questions