Menu
What is the importance of schema in sql server?
Where this schema help me? Is it important for security reasons?
866
Schema is mainly used to Manage several logical entities in one physical database. Schemas offer a convenient way to separate database users from database object owners. They give DBA's the ability to protect sensitive objects in the database, and also to group logical entities together.
Schemas play an important role in allowing the database objects to be organized into these logical groups. The schema also helps in situations where the database object name is the same. But these objects fall under different logical groups. A single schema can be used in multiple databases.
A database schema defines how data is organized within a relational database; this is inclusive of logical constraints such as, table names, fields, data types, and the relationships between these entities.
In SQL Server, a schema is a logical collection of database objects such as tables, views, stored procedures, indexes, triggers, functions. It can be thought of as a container, created by a database user. The database user who creates a schema is the schema owner.
Yes, the primary purpose of SQL schema was -is- to facilitate security management: define who [which principals] can access what [which database objects]. This was made particularly easier starting with SQL 2005 when the schema stopped being directly tied to the owner.
Another use of schema is to serve as a namespace, that is preventing name clashes between objects from different schemas.
The original use of this was to allow multiple [interactive, i.e. ad-hoc like] users of a given database to create their own tables or stored procedures (or other objects), without having to worry about the existence of similarly named objects possibly introduced by other users.
The Namespace-like nature of schema can also be put to use in a planned database setting, i.e. one when a single architect designs the database structure in a way which provides distinct type of access, and indeed different behaviors, for distinct user groups.
158
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
