Probe into different points of mysql,db2,sqlserver database (i.)

Simple comparison of mysql,db2,sqlserver database design differences, for the future to see the MySQL source code first hit the bottom, today compare database objects it:

1.DB2 the concept of a table space on a table, simply stated, table space is an important storage structure that establishes a mapping between the logical structure of the database and the physical structure of the operating system in a database system, in general a table space corresponds to a table. DB2 allocates all the physical properties of a tablespace table on a tablespace, such as the first allocation size, the size of the expansion, the initial free space per page of the page, and the percentage of free pages in total pages. Therefore, The system DBA on the DB2 is more table-based. For example, because the deletions on the table inevitably result in the fragmentation of the table pages, DBAs regularly clean up the fragments, and the DBA actually does the reorganization of the tablespace (and, incidentally, reorganizing the index). The DBA's backup of the table is actually implemented by a backup of the table space. So the recovery of the DB2 table can be achieved by restoring a table space file or log file to a single table, at which point the DB2 is better than SQL Server.

There is no concept of table space on SQL Server for tables, and physical information is implemented by the file or filegroup where the database resides. There is also the need for index fragmentation and periodic reorganization/rebuilding of indexes on SQL Server. SQL Server DBA can query the volume of fragmentation of the current index through the DMV view, indexes that are generally higher than 30% must be rebuilt, and indexes below 30% can be processed by reorganization. (The benchmark is generally determined by the project). So regular maintenance of indexes on SQL Server is generally a rebuild index

MySQL storage also has the concept of fragmentation, but according to official documents, this fragment is not caused by General data deletion, but because MySQL internal use of variable-length data types such as Varchar,text,blob. (-_-! pruning Check in the Overlord, is also censored), MySQL use optimize table for defragmentation, mainly used in MyISAM.

Other Db2,sqlserver,myisam management maintenance above is discussed in the next article.

2. About clustered and nonclustered indexes:

The concept of clustered indexes exists in SQL Server and DB2, and you can use statements to explicitly create clustered indexes (plus parameter clustered). There are no explicit clustered and nonclustered indexes in MySQL, and the primary key index under the InnoDB engine is a clustered index, and there is no clustered index under MyISAM.

MySQL in addition to common indexes similar to SQLSERVER,DB2, single-column index, combined index (the latter two in SQL Server and DB2 does not call this title but the use of similar), full-text index (only used in the MyISAM engine), There is also a special spatial index. Spatial index is mainly used in GIS, geometric mathematical calculation and so on. There is no spatial index in DB2, there is a spatial index in SQL Server, but the usage is slightly different from MySQL.

3. View, system table.

Application view there is nothing to say, all three create statements are basically the same. The system table is similar, each database basically provides the system table to query the current database application database system information, such as table structure, table name, table of physical information, schema information, user information, constraint information, stored procedure information and so on. SQL Server also provides a dynamic management view (DMV) that the DBA can use to query the currently running query, the current task taking advantage of CPU information, currently running the slowest SQL, current deadlock or blocking, and so on. The DMV is very important to SQL Server DBAs, which each DBA has to master.

4. On the system built-in function, mysql,sqlserver,db2 basically similar, characters commonly used character processing function Concat,strcmp,substring,reverse,ltrim,rtrim, Replace, etc. exist or exist similar functions on the MYSQL,SQLSERVER,DB2 platform.

5. Triggers and stored procedures:

There are identifiers and actions on the triggers on the MYSQL,DB2 in the old table and the new table, not on SQL Server.

The trigger syntax on SQL Server can involve monitoring of columns, such as ' If Update ' (A1) indicating that if the A1 column is updated, the corresponding action is taken; MYSQL, DB2 No, only through, new,old comparison to achieve.

The trigger levels for MySQL triggers include after and before,sqlserver including after and Instear OF,DB2 contain these three kinds.

The MySQL trigger syntax contains ' for each row ' This display trigger granularity statement, SQL Server triggers are not syntactically, but the actual execution is followed by ' for each row '. There is also the ' for each STATEMENT ' option on DB2, but only when the activation level is after.

The stored procedure creates the syntax, calls the purpose, compiles basically all the same, but invokes the method SQL Server with ' EXEC ', MySQL and DB2 with ' call ' just. It is now known that Ali, a top domestic internet company, has banned the use of stored procedures internally (I have not seen, for a few years into Ali feel).

6. Data type:

Data type Int,smallint in MySQL can be followed by a number, which represents the width of the display, but the width does not affect the actual range of values. For example, int (4), if the input is less than 4 bits, will be filled with space, greater than 4 bits, greater than 4, display its actual value. There is no design for this so-called data type width in Db2,sqlserver.

MYSQL has a richer date-time type than SQLSERVER,DB2.

The date and time types in SQL Server are: data,time,datatime;

The date and time types in DB2 are: data,time,timestamp;

The relevant datetime types in MySQL are: year,date,time,datetime,timestamp

In addition: The year type of MYSQL on 4 digits or characters, 2 digits, 2-bit characters are processed in different ways, specifically, refer to the documentation.

7. MySQL can use the statement to manually change the data engine; This setting is not in SQLSERVER,DB2. (SQL Server provides different engines for different services, such as the engine provided for Analysis Services, unlike the basic Management service.) DB2 Unified use of the default engine)

8. Details of some other SQL queries:

MYSQL Insert new column supports inserting a new column into the front (parameter first) or after a specified field (after), SQLSERVER,DB2 only allows new columns to be inserted at the end of the default. SQL Server allows you to insert a new column behind a column using the design graphical interface, the actual principle is to make a temporary table like the modified table, the data in the original table is placed in the temporary table, and then the original table is deleted. (Hard, sqlserver-_-!)

MySQL supports the Rename statement to change the table name directly to another name, the syntax is as follows:

ALTER TABLE Oldaname RENAME newname; allow with ALTER TABLE ... Change the column name in the same way. The change can also modify the data type of the new column.

SQL Server does not support the direct use of statement modification, allowing the call to the stored procedure ' sp_rename ' to modify the table name, the same stored procedure can be used to modify the column name.

DB2 can be used in rename ... To modifies the table name, modifies the column Name property in the table with the ALTER TABLE statement, but does not allow direct modification of the column name, allowing only the way to add different column names after deletion.

MySQL uses the Limit keyword to query the n rows of data beginning in the table or starting with M rows, and SQL Server supports the TOP keyword to query the first number of rows of data, using ' FETCH first n rows only ' in DB2 to query the data at the beginning of how many rows. The latter two do not support the simple use of the query statement to the beginning of the M row of rows of data (current personal views)

9. Some other settings, such as operators, constraints, and system functions are roughly the same.

Probe into different points of mysql,db2,sqlserver database (i.)