SQL Server Schema

When a SQL query is a reference table, you need to develop a schema name for it. in the terminology of a database, a pattern is a namespace . This way you can combine objects of the same nature into a common namespace. The schema can also protect the object, so the DBA can grant display permissions to the pattern. For example, a DBA can grant a user the SELECT permission for a pattern so that the user can select rows from any table or view in that pattern.

Each object in the SQL Server database is identified by a 4-part name. The name is in the form of Server.DataBase.Schema.Object. The columns in the table are not real objects, but are properties of the objects. The SELECT clause does not specify a server, only the database, schema, objects, and attributes, and the FROM clause requires a server. Some parts of the four-part name are optional, and if the server name is omitted, SQL Server assumes that the query is running on the current server connection. The same is true for databases, where SQL Server assumes that the server environment that is currently provisioned is the database in which the object resides.

In SQL Server, each user is given a default mode or namespace. A pattern is a specified collection of database objects that make up a namespace. Namespaces are owned by a single user. In namespaces, objects cannot have duplicate names. However, objects in different namespaces or different schemas can have duplicate names.

Http://blog.sina.com.cn/s/blog_7952e89001010jlj.html:

Beginners in the database tend to be confused about relational database schemas, databases, tables (table), users (user), always feeling that their relationships are inextricably linked, but don't know where their connections and differences are. To some problems often can not say why. Below, we discuss the relationship between its schema (schema), database, table (tables), users (user) at the core of SQL Server.

First, let's figure out what a pattern is.

It is clear that the concept of schema in SQL Server is only proposed in the 2005 version, so SQL Server2000 does not support the concept of schema (I have eaten here).

Schemas are also known as schemas, which are defined as collections of database entities that form a single namespace . A namespace is a collection in which the name of each element is unique. Here, we can look at the schema as a container for the objects in the database.

The above text description is too obscure, for a simple example, usually in the computer hard disk storage, we do not have all the contents of a folder, but will be different files according to a certain standard categories, put into different folders. In the database, the role of this is the architecture, database objects (tables, views, stored procedures, triggers, etc.) according to a certain standard, stored in different architectures. Those of you who have experienced Java programming know that namespace names are actually folder names, so we are very clear: An object can belong to only one schema , just as a file can be stored in a single folder. Unlike folders, schemas cannot be nested, that's all. As a result, the benefits of architecture are obvious-easy to manage.

Now, let's look at the relationship between the user and the schema (schema, or architecture).

From the above analysis, we know that a schema can accommodate multiple database objects, but not all users can access the content of a schema, which is called permissions. Take a look at the following table:

& nbsp;	user4
y		schema2	n	y	n	y
schema3	n	y

From this table, we can see that user 1 can access schema 1 and schema 3, user 2 can access schema 1 and schema 2, and so on.

in the SQL server2000 , the user and schema are not separated , and are separated by 2005. In fact, the user and architecture concept in 2000 is to assign a fixed pattern to the user, which is the following table:

	User1	User3	User3
Schema1	Y	—	—
Schema2		Y	—
Schema3	—	—	Y

As described above, the relationship between the user and the framework is many-to-many-one architecture can correspond to multiple users, and one user can correspond to multiple schemas.

Now, let's talk about the database and schema (schema).

To give a very simple example, we can think of the database as a large warehouse, the warehouse has a lot of room, the schema is the room, a schema represents a room, so in different rooms, we can put different things-some put food, some put clothes ... And these are different things that correspond to the objects in our database.

Therefore, we can see that the database is a one-to-many relationship with schemas.

Summing up, in fact, our database is a large warehouse of data, and there are many many patterns created, respectively, with different database objects (including tables), and different modes have different permissions, so, different users have different access rights to access a schema database objects.

Resources:

Http://tech.ddvip.com/2009-01/1231832216105719.html

Http://tech.ddvip.com/2009-01/1231832308105720.html

Benefits of User Architecture separation:

Separating the schema from the database user has the following benefits for administrators and developers:

1. Multiple users can have a schema through role membership or Windows group membership. This expands the functionality that allows users of roles and groups to have objects that are familiar to them.
2. Greatly simplifies the operation of deleting database users.
3. Deleting a database user does not need to rename the objects contained by the user schema. Therefore, after you delete the users who created the objects that the schema contains, you no longer need to modify and test the applications that explicitly reference those objects.
4. Multiple users can share a default schema for unified name resolution.
5. By sharing the default schema, developers can store shared objects in a schema that is specifically created for a particular application, rather than in the DBO schema.
6. You can manage permissions for the objects contained in the schema and schema with a granularity greater than the granularity in earlier versions.

SQL Server Schema