Sự khác nhau trong câu lệnh SQL sử dụng trong Access (Jet) và SQL Server (SS)

lehongduc

Member
Hội viên mới
Chào các Bạn,
Tôi xin trích đăng toàn văn 1 đoạn trong tài liệu tìm được từ Internet (đính kèm) về sự khác nhau trong câu lệnh SQL sử dụng trong Access (Jet) và SQL Server (SS).
Core SQL Dialect Differences
A selection of fundamental differences of SQL Server's implementation of Structured Query Language from Jet:
T-SQL string delimiter uses only single quote.
Jet: SELECT * FROM Orders WHERE ShipCountry = "France"
SS : SELECT * FROM Orders WHERE ShipCountry = 'France'
Standard Wildcard Characters are % and _ instead of * and ?
Jet: … WHERE ShipCountry Like "F*"
SS : … WHERE ShipCountry Like 'F%'
Dates are also delimited through single quote ' and not octothorpe #. US format remains the default.
Jet: … WHERE OrderDate Between #12/01/2007# AND #12/31/2007#
SS : … WHERE OrderDate Between '12/01/2007' AND '12/31/2007'
String concatenation uses + symbol and not & (as can be used in Jet) - Nulls therefore do propagate.
Jet: SELECT FirstName & " " & LastName As FullName FROM Employees
SS : SELECT FirstName + ' ' + LastName As FullName FROM Employees
For such circumstances the Access Nz function maps to T-SQL IsNull (or the more powerful Coalesce)
Jet: SELECT Nz(Region, 'N/A') FROM Employees
SS : SELECT IsNull(Region, 'N/A') FROM Employees
VBA's IsNull is not exactly represented (however in either SQL dialect "Is Null" is a better choice)
Jet: SELECT * FROM Employees WHERE Region Is Null
SS : SELECT * FROM Employees WHERE Region Is Null
VBA's Immediate If function is implemented with ANSI SQL Case statements
Jet: SELECT IIF(Region Is Null, 'N/A', Region) FROM Employees
SS : SELECT CASE WHEN Region Is Null THEN ' N/A' ELSE Region END FROM Employees
VBA Type conversion functions can be handled with CAST or CONVERT calls
Jet: SELECT CStr([Birth Date]) FROM Employees
SS : SELECT CONVERT(varChar, Region) FROM Employees
Data Types in T-SQL are *not* implicitly converted for you!
e.g., Jet will silently convert the numeric value to concatenate Text and Integer fields in a SQL statement

Jet: SELECT "Order Dated - " & [Order Date] As FullNameRef FROM Orders
SS : SELECT 'Order Dated - ' + CONVERT(varChar, [Order Date]) As FullNameRef
Boolean (Yes/No) Data Types in Jet (stored as 0 or -1) are represented by Bit fields in SQL Server (stored as 0 or 1). An ODBC linked table will perform the conversion for you – but if you're writing the SQL yourself you need to know. T-SQL also has no "True" and "False" literal constants.
Jet: SELECT * FROM Products WHERE Discontinued = True
SS : SELECT * FROM Products WHERE Discontinued = 1
The aliasing keyword "AS" is optional in T-SQL for both tables and columns – in Jet tables only.
Jet: SELECT [Unit Price] * Quantity As TotalAmt FROM [Order Details] tblDetails
SS : SELECT [Unit Price] * Quantity TotalAmt FROM [Order Details] tblDetails
You cannot reference aliased columns in T-SQL
Jet: SELECT [Unit Price] * Quantity As TotalAmt, TotalAmt/10 As Installment FROM [Order Details]
SS : SELECT [Unit Price] * Quantity As TotalAmt, ([Unit Price] * Quantity)/10 As Installment FROM [Order Details]
 

Đính kèm

  • FAQ PDF.zip
    103.1 KB · Lượt xem: 339

CẨM NANG KẾ TOÁN TRƯỞNG


Liên hệ: 090.6969.247

KÊNH YOUTUBE DKT

Cách làm file Excel quản lý lãi vay

Đăng ký kênh nhé cả nhà

SÁCH QUYẾT TOÁN THUẾ


Liên hệ: 090.6969.247

Top