SQL SERVER 2008 schemas

Schema: A container contains tables, views, database objects, and so on. Equivalent to a namespace

How to create a schema:

1. Graphics Wizard

2. Command

Create schema

In SQL Server 2005, you may be working or learning often find such problems, you use an account in the database to create a table, but found that you create a table without modification and query permissions, this is a very depressing thing, There is no such problem in sqlserver2000, so why does this happen in 2005, and what are the benefits of such a setup? In fact, the cause of this problem is mainly due to a new concept-architecture in 2005.

First, let's look at the definition of schema in MSDN: Schema is a collection 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.

Schemas actually exist in sqlserver2000, and when we use Query Analyzer to query a table, The name of a complete table should include the server name. Name of the database. The name of the object, and the fully qualified name of a table in sqlserver2005 should be the server name. The name of the database. The name of the object.

In 2000, if there is an account TT creates a table table1 in the test database, the query statement on the server should be select * from Test.tt.table1, that is, the SQL 2000 the schema to which a table belongs defaults to the login name of the creator of the table, and the user can and modifies all database objects that he creates. However, in 2005, the Association of the user and the schema to which the object was created has been canceled, and a new architecture has been added, the advantages of which are mainly in the following areas:

1. Multiple users can have the same schema through roles (role) or group (Windows groups) memberships. In 2005, each database has a fixed database role in its own schema, if we create a table, give it the name of the schema named db_ddladmin, then any user belonging to the db_ddladmin can query, modify and delete the table belonging to this schema , but other users who do not belong to this group are not, and it is important to note that members of the Db_dbdatareader group can view the tables in all databases, and Db_dbdatawriter group members can modify the tables in all databases Db_ The owner group member can perform all operations on all tables in the database, and members of these groups can obtain special permissions in the database through the role.

2. Deleting database users becomes extremely straightforward. In SQL Server 2000, the user and schema are implicitly associated, that is, each user has a schema with the same name. Therefore, to delete a user, you must first delete or modify all of the database objects owned by this user, such as an employee to leave to delete his account, but also to delete his created tables and views, etc., the impact is too large. After separating the schema from the object, there is no such problem, there is no effect on the database object when the user is deleted.

3. Sharing the default schema allows developers to create specific schemas for specific applications to hold objects, which is better than using only the administrator schema (DBO schema).

4. Setting permissions on the objects contained in the schema and schema (permissions) has a higher level of manageability than the previous version.

5. Distinguish between the objects of different business processing needs, for example, we can set the public table to the pub's schema, and the sales related to the set to sell, so it is easier to manage and access.

Most users are accustomed to directly entering object names when creating objects and omitting the schema name of the object, in 2005, the user creates a table with a default schema, and if the user does not set the default schema, the default schema is dbo, that is, if a db_ A member of the ddladmin creates a table in the database that does not have a schema name. The full name of this table in the database should be dbo. Table name, the creator in the database if not belong to other special groups of members, is not to create a table for any modification and query, it is equivalent to the money you earn into someone else's bank card, but not out of their own.

Full-text index: http://yrq205.blog.163.com/blog/static/4767214020112891835368/