Analyze the SQL Server promotion problem of access database

In the software development process, we often encounter database promotion problems for many reasons, such as acsess access speed ratio.SQLServer slow, after deleting data recordsAccessWill leave a blank file, the file is getting bigger and bigger (you can also reduce the file size by compressing and repairing it, but it is too troublesome), the access speed is getting slower and slower, and even the database is damaged, if the damage is mild, you can find a third-party tool to fix the damage. If the damage is severe, the database cannot be repaired, or the data records will be damaged after the repair. So we have to make a promotion.AccessDatabaseSQLServer;

Now I will talk about the practices and precautions of the author during the promotion.

1. For the Date Field

　　AccessRepresented as: #1981-28-12 #

Sqlserver2000: ''1981-02-12''

2,SQLStatement difference, select,UpdateWhen performing operations on a single tableUpdateStatement differencesAccessIn connection with SQL ServerUpdateStatement comparison:

Update multiple tables in sqlserverUpdateStatement:

　　UpdateTab1

Set a. Name = B. Name

From tab1 A, tab2 B

　　WhereA. ID = B. ID;

Of the same functionSQLStatement inAccessIt should be

　　UpdateTab1 A, tab2 B

Set a. Name = B. Name

　　WhereA. ID = B. ID;

That is:AccessInUpdateThe statement does not have the from clause. All referenced tables are listed inUpdateKeyword.

When updating a single table:

　　UpdateTable1 set AB = '12', Cd = 444Where....

3. Delete statement

　　AccessDelete: delete * From Table1WhereA> 2: You only need to replace the SELECT statement in the SELECT statement with the delete statement.

In sqlserve, it is: delete from Table1WhereA> 2: No *

4. Differences between calculated fields after

　　AccessSelect a, sum (Num) as kc_num, kc_num * num as all_kc_num, the field after as can be used as a database field for calculation.

In SQL Server, select a, sum (Num) as kc_num, sum (Num) * num as all_kc_num cannot be used as a database field for calculation.

5, [.] and [!] Difference

　　AccessFor multi-table joint query: Select tab1! A As tab1a, tab2! B tab2b from tab1, tab2, the intermediate as can be left blank.

In sqlserve: Select tab1.a as tab1a, tab2. B tab2b from tab1, tab2, the intermediate as can not.

6. During joint query,

　　AccessMulti-table join query: 'select A, B from (

Select a, B from tab1WhereA> 3 Union select c, d from tab2) group by a, B

In sqlserve, 'select A, B from (

Select a, B from tab1WhereA> 3 Union select c, d from tab2) tmptable group by A, B is to add a virtual table tmptable, the table name is arbitrary. ---

7,AccessWhen upgrading to sqlserver, you can use sqlserver's data import tool to import data, but necessary processing is required.

　　AccessAnd will not be automatically convertedSQLThe automatic number in can only be converted to the int type. You need to manually change it to the identification field, seed to 1, and remove all the data types of fields whose import is converted to SQL Server starting with N, for example, nvarchar-> varchar. change the date field that requires the second type to the datatime type (SQLConverts all dates to the smalldatetime type)

8, true and 1 = 1

　　AccessUseWhereTrue indicates that the condition is true,

SqlserverWhere1 = 1 indicates that the condition is true.

9. Determine the field valueNullDifference

Normal blank:

　　AccessAndSQLSame as serverWhereCode is null orWhereCode is nol null

Condition NULL:

　　Access: IIF ([num] is null, 0, [num]) or IIF ([num] is null, [num1], [num])

Sqlserver: isnull ([num], 0) or isnull ([num], [num1])

10,SQLDifferences between statements and substrings

　　Access: Mid (field, N1, [n2]), left (field, n), right (field, n)

For example, select left (CS1, 4) + '-' + CS2 as CS3

Sqlserver: substring (expression, start, length)

For example, select substring (CS1, 1, 2) + substring (CS1, 4, 2) + '-' + CS2 as CS3

Attachment: how to publish Based on ADO +SQLServer Applications

Many of my friends are asking how to publish data based on ADO +SQLThe author tests the application of server as follows:

Win98 accesses XP or 2 KSQLServer 2000 Server, no needSQLAs long as 98 can communicate with machines in other working groups (can access Shared Files in another machine), this step ensures that 98 has installed these three protocols, TCP/IP, SPX, IPX, and netbeuidg protocols,

In my test environment, 98SE + XP and 98SE only install the necessary driver, and none of the other programs are installed (specifically installed to test this)