I only found out the differences between create database and create schema in MySQL. Unfortunately, I don't use MySQL for my work. So I want to ask the differences in SQL Server.

CodePudding user response：

In SQL Server, a schema is just a more granular grouping of objects that belong under a single database (so in relational terms, a database can be one-to-many to schemas).

Schemas allow more flexibility in grouping objects by relevancy and controlling access better from a security standpoint.

For example, a manufacturing company may have a database to track the items they produce, the sales of those products, and their customers they sell to. A single database could encompass all of that.

But one may use schemas to divide all of the sales related tables, SalesOrders, SalesLines, Shipping, etc, in the Sales schema. Another schema called Production can be used to track Inventory, QuantityOnHand, ProductionOrders, ProductionLines etc. Another schema called Customers can be used to store customer specific data such as the Customers themselves, the CustomerAddresses, etc.

And each of these schemas can be locked down with various security access so only certain users can access each schema or a mix of different schemas. For example, you may not want to expose sensitive Customer information or Sales data to people who need access to the Production data. But there may be managers or certain people who should be able to see both Sales and Production data. Schemas make it possible to divide permissions up accordingly to support those use cases.