Comparison between SQL Server and Oracle: Table Management and Organization

Document directory

• Difference between Schema and user
• Supplement
• Schema in SQL Server

We know the database, as its name implies. the most important thing is data management, and data is stored in tables in the system. Therefore, almost all the mechanisms and operations in the database are centered around the table. the importance of tables can be imagined. how are tables stored in the database and accessed?

 

Table Management Mechanism in oralce

 

In fact, the database management system and file system are very similar. All information in the database is stored in actual files.

All the tables in Oracle are stored in files suffixed with DBF. when we use files, we will see directories. the directory in Oracle is the table space ). there will be a lot of files under the directory, and there will also be a lot of data files in that tablespace. then, when creating a user, we will specify the user's default tablespace, so that the current user saves the table to this tablespace by default, but you can also explicitly specify the tablespace. users are a logical concept in Oracle. it is set up to facilitate management. in addition, Oracle has a schema concept.

 

Difference between Schema and user

In fact, in Oracle, shcema and users can be exactly the same. you may ask this question to completely remove the schema concept, so as not to mislead people. it is said that the schema is stored for historical reasons. at the beginning, the schema in oralce does not correspond to the user one by one. create many schemas, each schema has a default tablespace, and then you can create tables under the schema. at last, a user can correspond to multiple schemas. however, this is complicated and not easy to manage and understand. therefore, the user and Schema are matched one by one. in this way, the Organization is clear and tidy, making it easier to manage and understand.

This is said to have not been verified and I have never played Oracle 8i or earlier. some people say that the difference between Schema and user should be understood in this way. a tablespace is equivalent to a warehouse. the schema is equivalent to a cabinet. you can store various things in it. each user has a schema. has a cabinet. one-to-one correspondence. normally, you cannot access others' cabinets. but if you have the privilege, you can.

 

The above is a bunch of examples.

Assume that the data file dbfile1.dbf belongs to the tablespace tspace1.

The data file dbfile2.dbf belongs to table space tspcae 2.

Create user Arwen and specify the default tablespace as tspace1.

If table 1 is created under user Arwen and no tablespace is specified, table 1 is saved to table space 1. Because there is only one data file in the tablespace, the table is actually saved to dbfile1.dbf.

However, you can also explicitly specify the table space of Table1 as tspace2. Table1 is saved to dbfile2.

 

You can easily save tables. Tables --> tablespaces --> data files

 

When we access the table, select * From Arwen. Table1 can be omitted if it is the current user.

 

 

SQL Server Table Management Mechanism

 

In fact, the mechanism of SQL Server is very similar to that of Oracle. It may be similar to that of C # And java. Interestingly, C # and SQL Server are Microsoft, and both Java and Oracle are oracle.

 

First, tables are naturally stored in actual data file files. If they are system tables, they are stored in files suffixed with mdb, which is called the main file, other tables are generally stored in the NDB file, called secondary files. in fact, you can also change the file suffix to another one, so if you want to be idle or prank, you can change it to the same as that of Oracle, with the suffix DBF. Of course, you have to change some other configuration information. but it is generally not recommended that you change the suffix.

In Oracle, data files belong to a certain tablespace, but SQL server does not have the concept of tablespace, but there is a file group concept. In fact, it is almost pulled.

Then, the user creates databases and specifies their default file groups. Then, tables can be created under the database. If this parameter is not specified, the files are saved in the default file group, you can also explicitly specify. in fact, the database here is equivalent to the schema in Oracle. then create a user. A user can correspond to multiple databases, which is equivalent to a user name in Oracle that corresponds to multiple schemas.

 

Let's take another example.

Assume that the data file dbfile1.ndb belongs to the file group group1.

The data file dbfile2.ndb belongs to the file group group2.

Create Database db1 and specify the default file group group1

If table 1 is created under db1 and no file group is specified, table 1 is saved to group1. because there is only one data file in the file group, the table is actually saved to dbfile1.ndb.

However, you can also explicitly specify the file group of Table1 as group2. Table1 will be saved to dbfile2.ndb.

We can create weiwenhp, and the corresponding Databse is db1.

You can easily save tables. Tables --> file groups --> data files

When we access the table: Select * From db1.dbo. Table1

 

There is a DBO here, which is schema. I will try again later and thought it was not in SQL Server. However, the schema here is different from that in Oracle.

So you can use weiwenhp to log on and execute the preceding SQL statement. user Permissions are not discussed here. next time I talk about permissions. if another user does not have the permission to execute this SQL statement

 

(Note: I have thought about the inaccuracy mentioned above. It involves the difference between login and user, which is a very misleading concept in SQL Server. login can correspond to multiple databases, but the user cannot correspond to multiple databases. It can only correspond to multiple schemas. the relationship between a user and login is that a login can correspond to multiple users. I will discuss this issue later when talking about permission management)

 

Supplement the schema in SQL Server

When creating a user, we can directly specify a Databse for the user. however, this may be a waste, and it is not convenient to manage only. therefore, many schemas can be created under each Databse. you can think of Databse as a large warehouse, and schema is a small cabinet. every time you create a Databse, you use a template to initialize the database. By default, the database has some Schemas and some small cabinets. you are most familiar with the DBO cabinet. of course, you specify the user map a database, which is equivalent to giving all the schemas to him.

When you have a database, you can add schema of any data in it. In a warehouse, you only need a few cabinets. when a user is created with a schema, one or more schemas can be specified instead of one database. in this way, only one or more cabinets can be used.

For example,

If Arwen is a user, specify a database named mydb. The database contains DBO, Sm, and other schemas. At this time, the user creates a table TMP.

Create Table TMP (name char (20 ));

This table does not specify the schema to which it is saved. It is saved to DBO by default.

Find select * From db1.dbo. tmp // actually on the graphical interface tool management sudio, we select a database and then write it directly when searching

Select * from TMP. db1.dbo is added by default.

 

Create Table SM. mytable (name char (20 ));

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

Select * From db1.sm. mytable // database with schema and table name.