SQL Server vs. Oracle Learning: Table Management and organization

http://blog.csdn.net/weiwenhp/article/details/8088979

We know the database, as the name implies. The most important thing is to manage the data, and the data in the system is mainly stored in the table, so the database almost all the mechanisms and operations around the table in the circle. The importance of the table is conceivable. In the table in the database is how to save up, How to access it?

The management mechanism of tables in Oralce

In fact, database management systems and file systems are very similar. All the information in the database is ultimately saved in a real file.

The tables in Oracle are ultimately saved in a file with a suffix of dbf. And we'll see a directory when we use the file. The directory in Oracle is a tablespace (tablespace). There will be many files in the directory, and there will be many data files in that table space. We then specify the user's default tablespace when we create the user, so that the current user creates the table by default in this tablespace, but it can also be explicitly specified in the table space. The user is a logical concept in Oracle. It was set up for ease of management. There is also a schema concept in Oracle.

The difference between schema and user

In fact, Oracle now has the ability to fully equate Shcema with users. Then you might ask that the concept of schema should be completely removed, Lest it be misleading. The reason for preserving the schema is said to be historical. At first, the schema in Oralce is not one by one corresponding to the user. First, many schemas are created, each schema has a default tablespace, You can then create a table of tables below the schema. Finally, you can have one user correspond to multiple schemas when you create a user. But this is more complicated, not easy to manage and understand. So the user and schema one by one are mapped. This makes it clear, uncluttered, and easy to manage and understand.

It's said I haven't verified it. Oracle 8i or earlier. Some people say that there is a schema and the user's distinction, It should be understood this way. The tablespace is equivalent to a warehouse. And the schema is the equivalent of one of the cabinets. You can store all kinds of things in it. Each user has a schema. A cabinet. One by one the corresponding relationship. Normally you can't access someone else's locker. But if you have the privilege, you can.

It says a bunch of them, or to give an example.

If there is a data file dbfile1.dbf belongs to the tablespace TSpace1

There are data files dbfile2.dbf belong to tablespace tspcae 2

Create user User Arwen specifies that the default tablespace is TSpace1.

Create a table under user Arwen Table1 do not specify a tablespace Table1 is saved to the Tablespace TSpace1, since the table space has only one data file, the table is actually saved to DBFILE1.DBF

But you can also explicitly specify the Table1 tablespace as TSpace2, at which point Table1 is saved to DBFile2

The table is saved at a glance. Tables--Table spaces--Data files

When we access the table: SELECT * from Arwen.table1 if the current user can also omit the prefix Arwen.

SQL Server table Management mechanism

In fact, SQL Server has a very similar mechanism to Oracle. Probably just like C # and Java. Interestingly, C # and SQL Server are Microsoft, and Java and Oracle are all Oracle's

First of all, the table is also saved in the actual data file, if the system table is saved in the suffix of the MDB file, called the main file, other tables are generally saved in the NDB file, called secondary files. You can actually change the file suffix to something else, So you're going to have to play with your balls. Like Oracle, the suffix is dbf, and of course you have to change some other configuration information. But it is generally not recommended to change the suffix name.

In Oracle, the data file belongs to a table space, but SQL Server does not have the concept of tablespace, but it has the concept of a filegroup. Actually, almost pulled.

Then the user will create a database, and specify its default filegroup, and then the database can be created under the table, if not specified in the default file group, You can also specify explicitly. In fact, the database here is equivalent to the schema in Oracle. Then create the user, a user can be able to correspond to multiple database, equivalent to the previous Oracle in a user name can correspond to multiple schemas.

Let me give you an example.

If there is a data file dbfile1.ndb belongs to the filegroup group1

There are data files dbfile2.ndb belong to filegroup group2

Create DATABASE DB1 Specify the default filegroup group1

Create a table under DB1 Table1 do not specify a filegroup Table1 save to filegroup group1, since the filegroup has only one data file, the table is actually saved to Dbfile1.ndb

But you can also explicitly specify that the TABLE1 filegroup is group2, and Table1 will be saved to Dbfile2.ndb

We can create user weiwenhp, the corresponding databse is DB1

The table is saved at a glance. Table--File groups--Data files

When we visit the table, it is: SELECT * from Db1.dbo.Table1

There is also a dbo, which is the schema. Wait a second, you'll start thinking there's nothing in SQL Server. But the schema here is not the same as in Oracle.

You can then use the user WEIWENHP login to execute the above SQL statement. The user's permissions are not discussed here. The next time we talk about permissions. If another user does not necessarily have permission to execute this SQL

(Note: Think of the below to find that said is not very accurate, involving SQL Server has a very misleading concept of the difference between login and user. Login can correspond to multiple database but user cannot correspond to multiple database, You can only correspond to multiple schemas. The relationship between user and login is a login that can correspond to more than one user. Discuss this later when you talk about permission management)

Supplementing schema in SQL Server

We can assign a databse to the user directly when we create the user. However, this can be wasteful and not easy to manage. So a lot of schemas can be created under each databse. You can think of it like a big warehouse, databse. And the schema is one of the small cabinets. Each time you create a databse, you initialize it with a template, so that the database has some schema by default, There are some small cabinets. What you are most familiar with is the dbo cabinet. Of course you specify a user map a database is equivalent to giving all the schema to him.

When you have a database, you can also add some arbitrary data to the schema, How many cabinets do you love in a warehouse? With the schema, we can create a user without assigning a database to him, instead specifying only one or more schemas. This allows the user to use only one or more cabinets.

Example

If there is a user Arwen, give it a database named MyDB. This database has the DBO,SM and so on the schema at this time the user creates a table tmp

CREATE TABLE tmp (name char (20));

This table is not specified in which schema to save, and is saved to the DBO by default

Find SELECT * from DB1.DBO.TMP//actual tool in management Sudio This graphical interface, we selected a database, and then look for the direct write

SELECT * FROM TMP is OK. The default is to add db1.dbo

CREATE TABLE sm.mytable (name char (20));

When schema SM is specified, the table is saved to SM.

SELECT * FROM db1.sm.mytable//database plus schema plus table name to find.

SQL Server vs. Oracle Learning: Table Management and organization