Differences between MySQL and SQL Server

Differences between MySQL and SQL Server

1. mysql supports enum, set, and SQL server.
2. mysql does not support nchar, nvarchar, and ntext
3. the incremental Statement of mysql is AUTO_INCREMENT, while that of mssql is identity)
4. mssql by default, the default value of the table creation statement everywhere is (0), but it is not allowed to contain square brackets in mysql.
5. mysql needs to specify the storage type for the table
6. The mssql identifier is [] and [type], indicating that it is different from the keyword, but mysql is ', that is, the symbol on the left of key 1
7. mssql supports the getdate () method to obtain the current time and date, but mysql can divide the date type and time type to obtain the current date as cur_date (), and the current complete time is the now () function.
8. mssql does not support replace into statements, but in the latest sql20008, it also supports merge syntax
9. mysql supports insert into table1 set t1 = '', t2 ='', but mssql does not.
10. mysql supports insert into tabl1 values)
11 mssql does not support limit statements. Unfortunately, it can only replace limt 0, N, row_number () over () with the top function to replace limit N, M
12. When creating a table, mysql must specify a storage engine type for each table, while mssql only supports one storage engine.
13. mysql does not support the datetime type whose default value is the current time (mssql is easy to implement). in mysql, the timestamp type is used.
14. Check whether the table exists in mssql and delete it again. You need to: if exists (select * from dbo. sysobjects where id = id (n'uc _ newpm ') and OBJECTPROPERTY (id, n'isusertable') = 1) but in mysql, you only need to drop table if exists cdb_forums;
15. mysql supports unsigned integers, so it can store up to twice the maximum number of unsigned mssql statements.
16. mysql does not support the varchar (max) type that is very convenient in mssql. This type can be used in both general data storage and blob Data Storage in mssql.
17. To create a non-clustered index in mysql, you only need to specify the key when creating the table. For example, KEY displayorder (fid, displayorder) must be:
Copy codeThe Code is as follows: create unique nonclustered index index_uc_protectedmembers_username_appid on dbo. uc_protectedmembers (username asc, appid asc)

18. mysql text field types cannot have default values
19. The total field length of a mysql table cannot exceed 65XXX.
20. The obvious difference is that the installation of mysql is very simple, and the file size is only about 23 MB (5.5.23). Compared with Microsoft, the installation progress is simply .....
21. There are several good mysql management tools, mysql_front, and the official suite, but they are not easy to use SSMS, which is a major disadvantage of mysql.
22. mysql stored procedures are only available in the latest version. The stability and performance may not be as good as mssql.
23. With the same load pressure, mysql consumes less CPU and memory, and mssql consumes a lot of resources.
24. php connects to mysql and mssql in similar ways. You only need to replace the mysql of the function with mssql. For PDO mode, you only need to replace mysql with mssql.
25. mysql supports the date, time, and year types. Only date and time are supported by mssql to 2008.

Appendix: differences between MySQL and MSSQL Paging

MySQL has been used for a long time. Although it is slim compared to the mssql database system, it is not inferior. The following describes the differences between the two databases on their respective pages.
Example 1: retrieve the first 10
Copy codeThe Code is as follows:
SELECT * FROM table LIMIT 10;

In mssql
Copy codeThe Code is as follows:
Select top 10 * FROM table

Example 2: 10 entries on each page. The third page is retrieved.
In MySQL
Copy codeThe Code is as follows:
SELECT * FROM table LIMIT 20, 10

In mssql
Copy codeThe Code is as follows:
Select top 10 * FROM table WHERE id not in (
Select top 20 id FROM table order by id DESC
) Order by id DESC;

The preceding example shows that the LIMIT keyword is used for paging in MySQL. If it is LIMIT 10, the first 10 records are obtained. If it is LIMIT 10, the 10 records are shifted to the top 10 records. Use the top keyword in mssql. If only the first n records are obtained, the top n can be obtained directly, but it is troublesome to retrieve the results by page.

Differences between mysql and sqlserver

For program developers, the two most popular backend databases are MySQL and SQL Server. The most basic similarity between the two is data storage and query system. You can use SQL to access the data of these two databases, because they support ANSI-SQL. Also, both database systems support binary keywords and key indexes, which greatly speeds up query. Both of them also support various XML formats. In addition to the obvious differences in software prices, are there any significant differences between the two products? How do you choose between them? Let's take a look at the main differences between the two products, including release fees, performance, and their security.

The fundamental difference is that they follow the basic principles

The basic principle they follow is their main difference: Open vs conservative. SQL Server's narrow, conservative storage engine is very different from MySQL server's scalable and open storage engine. Although you can use the Sybase engine of the SQL Server, MySQL can provide more options, such as MyISAM, Heap, InnoDB, and Berkeley DB. MySQL does not fully support unfamiliar keywords, so it requires less related databases than the SQL server. MySQL also lacks some storage program functions. For example, the MyISAM engine supports the exchange function.

Release fee: MySQL is not all free, but cheap

When it comes to release fees, the two products adopt two absolutely different decisions. For SQL servers, the most common way to get a free development fee is to buy Microsoft Office or Visual Studio. However, if you want to develop commercial products, you must purchase SQL Server Standard Edition. Schools or non-profit enterprises may not consider this additional fee.

Performance: Advanced MySQL

For pure performance, MySQL is excellent because it contains a default desktop format MyISAM. The MyISAM database is very compatible with the disk without occupying too much CPU and memory. MySQL can run on Windows without conflict. It is better to run on UNIX or UNIX-like systems. You can also use a 64-bit processor to obtain additional performance. Because MySQL uses 64-bit integers in many internal departments. Yahoo! Commercial websites use MySQL as the background database.

When talking about the performance of the software, the stability of the SQL Server is much better than its competitors. However, these features have to be paid. For example, additional complex operations, disk storage, and memory loss must be added. If your hardware and software do not fully support the SQL Server, I suggest you select another database such as DBMS, because this will produce better results.

Security Features

MySQL has a binary log used to change data. Because it is binary, this log can quickly copy data from the host to the client. Even if the server crashes, this binary log is still complete and the copied part is not damaged.

On the SQL server, you can also record SQL-related queries, but this requires a high price.

Security

Both products have their own complete security mechanisms. As long as you follow these security mechanisms, the general program will not have any problems. Both use the default IP Ports, but sometimes, unfortunately, these IP addresses are also broken into by some hackers. Of course, you can also set these IP Ports by yourself.

Restoration: Advanced SQL Server

Restoration is also a feature of MySQL, which is mainly manifested in the MyISAM configuration. This method has inherent defects. If you accidentally damage the database, the results may lead to all data loss. However, the SQL Server is very stable. The SQL Server can monitor data exchange points at all times and save the database corruption process.

Decide your choice as needed

If you have to tell me which database is better, I may disappoint you. From my point of view, any database that is helpful to your work is a good database... the rest of the full text>

Differences between Mysql and SQL server

For program developers, the two most popular backend databases are MySQL and SQL Server. The most basic similarity between the two is data storage and query system. You can use SQL to access the data of these two databases, because they support ANSI-SQL. Also, both database systems support binary keywords and key indexes, which greatly speeds up query. Both of them also support various XML formats. In addition to the obvious differences in software prices, are there any significant differences between the two products? How do you choose between them? Let's take a look at the main differences between the two products, including release fees, performance, and their security.

The fundamental difference is that they follow the basic principles

The basic principle they follow is their main difference: Open vs conservative. SQL Server's narrow, conservative storage engine is very different from MySQL server's scalable and open storage engine. Although you can use the Sybase engine of the SQL Server, MySQL can provide more options, such as MyISAM, Heap, InnoDB, and Berkeley DB. MySQL does not fully support unfamiliar keywords, so it requires less related databases than the SQL server. MySQL also lacks some storage program functions. For example, the MyISAM engine supports the exchange function.

Release fee: MySQL is not all free, but cheap

When it comes to release fees, the two products adopt two absolutely different decisions. For SQL servers, the most common way to get a free development fee is to buy Microsoft Office or Visual Studio. However, if you want to develop commercial products, you must purchase SQL Server Standard Edition. Schools or non-profit enterprises may not consider this additional fee.

Performance: Advanced MySQL

For pure performance, MySQL is excellent because it contains a default desktop format MyISAM. The MyISAM database is very compatible with the disk without occupying too much CPU and memory. MySQL can run on Windows without conflict. It is better to run on UNIX or UNIX-like systems. You can also use a 64-bit processor to obtain additional performance. Because MySQL uses 64-bit integers in many internal departments. Yahoo! Commercial websites use MySQL as the background database.

When talking about the performance of the software, the stability of the SQL Server is much better than its competitors. However, these features have to be paid. For example, additional complex operations, disk storage, and memory loss must be added. If your hardware and software do not fully support the SQL Server, I suggest you select another database such as DBMS, because this will produce better results.

Security Features

MySQL has a binary log used to change data. Because it is binary, this log can quickly copy data from the host to the client. Even if the server crashes, this binary log is still complete and the copied part is not damaged.

On the SQL server, you can also record SQL-related queries, but this requires a high price.

Security

Both products have their own complete security mechanisms. As long as you follow these security mechanisms, the general program will not have any problems. Both use the default IP Ports, but sometimes, unfortunately, these IP addresses are also broken into by some hackers. Of course, you can also set these IP Ports by yourself.

Restoration: Advanced SQL Server

Restoration is also a feature of MySQL, which is mainly manifested in the MyISAM configuration. This method has inherent defects. If you accidentally damage the database, the results may lead to all data loss. However, the SQL Server is very stable. The SQL Server can monitor data exchange points at all times and save the database corruption process.

Decide your choice as needed

If you have to tell me which database is better, I may disappoint you. From my point of view, any database that is helpful to your work is a good database... the rest of the full text>