Because of work , Lucky to learn about Microsoft SQLSERVER Version differences , Mainly from SQL6.5 to SQL2017 The difference syntax encountered by the version . What I have learned , from SQL2008 to SQL2017 There is no difference in grammar , At least the current situation is from SQL2008 Upgrade to SQL2017 Database does not need to do syntax migration. So the contrast of differences is based on SQL2008 The following versions and SQL2008 Comparison of versions .
First of all, take a look at each SQL Version of Compatibility level：
View the compatibility level of the database and the collation SQL：
SELECT compatibility_level, collation_name FROM sys.databases WHERE
name=@database
You can also right-click on the data to view properties →Options View in Compatibility
level,SQL2000 Properties cannot be viewed for and below . Here are SQL2008 and SQL2017 edition ：
1.=null VS is null( Confirmed ,SQL6.5 to SQL2008 The returned results are consistent )
SQL6.5 & SQL2000 Version 2 where Conditions can be used where
table.column=null or <>null To filter the data , But in the SQL2008( Probably from SQL2005 start , Because it wasn't there SQL2005 I tried it on , So I'm not sure , The same below , This paper ignores for the time being SQL2005 edition ) No matter what =null or <>null The conditions are not tenable , It needs to be written in where
table.column is null or is not null ;
2.（* =,=* ） VS （left join,right join）
SQL6.5 & SQL 2000 SQL2008
select * form A, B where A.ID*=B.AID select * from A left join B on A.ID=B.AID
select * from A, B where B.AID=*A.ID select * from B right join A on A.ID=B.AID
select * from A, B where A.ID=B.AID select * from A join B on A.ID=B.AID
3.Dump
SQL6.5 & SQL 2000 SQL2008
DUMP TRANSACTION [database name] WITH TRUNCATE_ONLY
DUMP TRANSACTION [database name] with no_log IF (SELECT
DATABASEPROPERTYEX((SELECT DB_NAME()), 'RECOVERY')) = 'FULL'
BEGIN
ALTER DATABASE [database name] SET RECOVERY SIMPLE
ALTER DATABASE [database name] SET RECOVERY FULL
END
DUMP DATABASE [database name] TO [logical device] WITH NOUNLOAD, INIT, SKIP
BACKUP DATABASE [database name] TO [logical device] WITH NOUNLOAD, INIT, SKIP
4.Load
SQL6.5 & SQL 2000 SQL2008
load database [database name] from "c:\Databases\test20190215.bak" RESTORE
DATABASE [database name] FROM DISK = "c:\Databases\test20190215.bak"
5.DBCC
SQL6.5 & SQL 2000 SQL2008
DBCC NEWALLOC(Database) WITH NO_INFOMSGS DBCC CHECKALLOC(Database) WITH
NO_INFOMSGS
DBCC TEXTALLOC(table) WITH NO_INFOMSGS DBCC CHECKtable(table) WITH NO_INFOMSGS
DBCC TEXTALL(Database) WITH NO_INFOMSGS DBCC CHECKDB(Database) WITH NO_INFOMSGS
DBCC DBREINDEX ALTER INDEX PK_ID ON table REBUILD
DBCC INDEXDEFRAG ALTER INDEX PK_ID ON table REORGANIZE
DBCC showcontig sys.dm_db_index_operational_stats
6."+" & Ltrim & Rtrim
SQL edition select ltrim('')/rtrim('') '' null+'ABC' null+10000 null+getdate()
SQL Compatibility level =65 null null ABC null null
SQL Compatibility level 80 And above ( empty ) ( empty ) null null null
7.Order by surface . field alias
SQL6.5 & SQL2000 SQL2008
select column1 as c1 from table1 order by c1 Y Y
select column1 as c1 from table1 order by table1.c1 Y N
select column1 as c1 from table1 t1 order by t1.c1 Y N
select column1 as c1 from table1 t1 order by t1.column1 Y Y
8.Order by select Duplicate fields in
SQL6.5 & SQL2000 SQL2008
select column1 as c, column2 as c from table1 order by c Y N
stay SQL2008 And later versions are not allowed , It is impossible to determine which field to sort by , and SQL2000 And previous versions are based on the first select To sort .
9. stay SQL2008 And after Order by Field must be distinct In the field of
SQL6.5 & SQL2000 SQL2008
select distinct column1 from table1 order by column2 Y N
10.replace encounter char type
SQL6.5 & SQL2000 SQL2008
SELECT '<' + REPLACE(CONVERT(char(10), ' AB C '), ' ', 'L') + '>' <LLABLC>
<LLABLCLL>
SELECT '<' + REPLACE(CONVERT(VARCHAR(10), ' AB C '), ' ', 'L') + '>' <LLABLCLL>
<LLABLCLL>
As shown in the table SQL Version input results we know by comparison , stay SQL6.5 and SQL2000 edition replace The char The space to the right of the field of type is removed and replaced .
11. System table
SQL6.5 & SQL2000 SQL2008
sysobjects sys.objects
syscolumns sys.columns
syscomments sys.sql_modules
master..Sysdatabases sys.databases
12.Group by
stay sql2000 with 80 The results of the compatibility level database query are as follows ：
stay sql2000 with 65 Compatibility level database query results ：
It can be seen from the above two comparative pictures , stay sql65 Compatibility level sql grammar ,group by It contains sorting function , from sql2000 There is no sorting function in the beginning .
Technology