What are the main differences between Access and SQL Server?

This article will try to explain some of the differences between Access and SQL Server. It is not an exhaustive list, and in no means should be considered an ultimate authority (you can also see this MSDN article for a more through treatment of the differences). If you have anything to add or correct, please let us know...

DATA TYPES

Here is a list of data types in each environment, and how they are different. Some datatypes from SQL Server were left out (e.g. SQL_VARIANT, TABLE).

Access	SQL Server	SQL Server Definition
Yes/No	BIT	(Integer: 0 or 1)
Number (Byte)	TINYINT	(Positive Integer 0 -> 255)
Number (Integer)	SMALLINT	(Signed Integer -32,768 -> 32,767)
Number (Long Integer)	INT	(Signed Integer -(2^31) -> (2^31)-1)
(no equivalent)	BIGINT	(Signed Integer -(2^63) -> (2^63)-1)
Number (Single)	REAL	(Floating precision -1.79E + 308 -> 1.79E + 308)
Number (Double)	FLOAT	(Floating precision -3.40E + 38 -> 3.40E + 38)
Currency	MONEY	(4 decimal places, -(2^63)/10000 -> ((2^63)-1)/10000)
Currency	SMALLMONEY	(4 decimal places, -214,748.3648 -> 214,748.3647)
Hyperlink	(no equivalent - use VARCHAR())
Decimal	DECIMAL	(Fixed precision -10^38 + 1 -> 10^38 - 1)
Numeric	NUMERIC	(Fixed precision -10^38 + 1 -> 10^38 - 1)
Date/Time	DATETIME	(Date+Time 1753-01-01 -> 9999-12-31, accuracy of 3.33 ms)
Date/Time	SMALLDATETIME	(Date+Time 1900-01-01 -> 2079-06-06, accuracy of one minute)
Text(n)	CHAR(n)	(Fixed-length non-Unicode string to 8,000 characters)
Text(n)	NCHAR(n)	(Fixed-length Unicode string to 4,000 characters)
Text(n)	VARCHAR(n)	(Variable-length non-Unicode string to 8,000 characters)
Text(n)	NVARCHAR(n)	(Variable-length Unicode string to 4,000 characters)
Memo	TEXT	(Variable-length non-Unicode string to 2,147,483,647 characters)
Memo	NTEXT	(Variable-length Unicode string to 1,073,741,823 characters)
OLE Object	BINARY	(Fixed-length binary data up to 8,000 characters)
OLE Object	VARBINARY	(Variable-length binary data up to 8,000 characters)
OLE Object	IMAGE	(Variable-length binary data up to 2,147,483,647 characters)
Autonumber Autoincrement	IDENTITY	(any numeric data type, with IDENTITY property)

Some notes on usage of data types:

Switching from Yes/No to BIT

-- DETERMINING TRUE

-- Access:
[...] WHERE ynColumn = TRUE
[...] WHERE ynColumn = -1

-- SQL Server:
[...] WHERE ynColumn <> 0

------------------------------

-- DETERMINING FALSE

-- Access:
[...] WHERE ynColumn = FALSE
[...] WHERE ynColumn = 0

-- SQL Server:
[...] WHERE ynColumn = 0

Switching from Currency to MONEY

Article #2188

Switching from Hyperlink to VARCHAR()

Like Currency, Access uses internal formatting to make the values stored in the application clickable. This is partly because Access is a client application, and this feature makes it easier to use. However, when you're not physically in the application, you may not want the URL to be clickable (it may just be a display value, or you may want to wrap alternate text -- or an image -- inside the <a href> tag). In SQL Server, use a VARCHAR column (likely 1024 or greater, depending on the need) and apply <a href> tags to it in the client application. Don't expect the database to maintain HTML for you... this only increases storage size, and hurts performance of searches against that column.Switching from Date/Time to DATETIME

-- Access:
[...] WHERE dtColumn >= #2001-11-05#

-- SQL Server:
[...] WHERE dtColumn >= '20011105'

Article #2260

Article #2206

-- Access:
[...] WHERE dtColumn = #11/05/2001#

-- SQL Server:
[...] WHERE CONVERT(CHAR(8), dtColumn, 112) = '20011105'

-- if dtColumn has an index, this will be more efficient:
[...] WHERE dtColumn >= '20011105'
AND dtColumn < '20011106'

-- Access:
SELECT Now()
SELECT Date() & " " & Time()

-- SQL Server:
SELECT GETDATE()
SELECT CURRENT_TIMESTAMP

-- Access:
SELECT Time()

-- SQL Server:
SELECT CONVERT(CHAR(8), GETDATE(), 108)
SELECT LTRIM(STUFF(RIGHT(CONVERT(CHAR(19), GETDATE(), 0), 7), 6, 0, ' '))

-- Access:
SELECT Date()

-- SQL Server:
SELECT GETDATE() - {fn CURRENT_TIME}
SELECT {fn CURDATE()} -- not friendly with certain languages
SELECT CONVERT(CHAR(8), GETDATE(), 112)

-- Access:
SELECT DateAdd("d",1,date())

-- SQL Server:
SELECT CONVERT(CHAR(8), GETDATE()+1, 112)
--or
SELECT DATEADD(DAY, 1, CONVERT(CHAR(8), GETDATE(), 112))

-- Access:
SELECT cstr(DateAdd("d",1,date())) & " " & cstr(time())

-- SQL Server:
SELECT DATEADD(day, 1, GETDATE())

-- Access:
SELECT DateAdd("d",1-day(date()),date())

-- SQL Server:
SELECT CONVERT(CHAR(10),GETDATE()+1-DAY(GETDATE()),101)

-- Access:
SELECT DAY(DATEADD("m", 1, 1-DAY(date()) & date())-1)

-- SQL Server:
SELECT DAY(DATEADD(MONTH, 1, 1-DAY(GETDATE())+GETDATE())-1)

-- This is impossible in Access, but just for fun:
SELECT "Pick a number between 1 and 1000" :-)

-- SQL Server:
SELECT DATEPART(millisecond, GETDATE())

-- Access:
SELECT weekdayname(weekday(date()))

-- SQL Server:
SELECT DATENAME(WEEKDAY, GETDATE())

SELECT columns FROM table WHERE dateColumn = #20030709#

SELECT columns FROM table WHERE dateColumn = #2003-07-09#

SET DATEFORMAT MDY
SELECT ISDATE('20030713') -- 1
SELECT ISDATE('2003-07-13') -- 1

SET DATEFORMAT DMY
SELECT ISDATE('20030713') -- 1
SELECT ISDATE('2003-07-13') -- 0

SET DATEFORMAT MDY
SELECT ISDATE('{d 2003-07-13}') -- 1

SET DATEFORMAT DMY
SELECT ISDATE('{d 2003-07-13}') -- 1

Article #2464

Article #2460

Switching from Autonumber to IDENTITY

-- Access:
CREATE TABLE tablename (id AUTOINCREMENT)

-- SQL Server:
CREATE TABLE tablename (id INT IDENTITY)

Handling Strings

-- Access:
SELECT "Foo-" & barColumn FROM TABLE

-- SQL Server:
SELECT 'Foo-' + barColumn FROM TABLE

SELECT FirstName & ' ' & LastName FROM table

SELECT FirstName + ' ' + LastName FROM table

SELECT COALESCE(FirstName, '') + ' ' + COALESCE(LastName, ' ') FROM table

Article #2073

-- Access:
SELECT CHR(13) & CHR(10)

-- SQL Server:
SELECT CHAR(13) + CHAR(10)

String Functions

Article #2061

Access	SQL Server	TEXT Equivalent
CINT(), CLNG()	CAST()	CAST(SUBSTRING())
FORMAT()	CONVERT()	CONVERT(SUBSTRING())
INSTR()	CHARINDEX()	CHARINDEX(), PATINDEX()
ISDATE()	ISDATE()	ISDATE(SUBSTRING())
ISNULL()	ISNULL()	ISNULL()
ISNUMERIC()	ISNUMERIC()	ISNUMERIC(SUBSTRING())
LEFT()	LEFT()	SUBSTRING()
LEN()	LEN()	DATALENGTH()
LCASE()	LOWER()	LOWER(SUBSTRING())
LTRIM()	LTRIM()	LTRIM(SUBSTRING())
REPLACE()	REPLACE()	STUFF() (also see Article #2445)
RIGHT()	RIGHT()	SUBSTRING()
RTRIM()	RTRIM()	RTRIM(SUBSTRING())
CSTR()	STR()	STR(SUBSTRING())
MID()	SUBSTRING()	SUBSTRING()
UCASE()	UPPER()	UPPER(SUBSTRING())
StrConv()	n/a	n/a
TRIM()	n/a	n/a

CINT(data) -> CAST(data AS INT)

-- Access:
SELECT CINT(column)

-- SQL Server:
SELECT CAST(column AS INT)

INSTR(data, expression) -> CHARINDEX(expression, data)

-- Access:
SELECT INSTR("franky goes to hollywood","goes")

-- SQL Server:
SELECT CHARINDEX('goes','franky goes to hollywood')

ISDATE(data)

-- Access:
SELECT ISDATE(#12/01/2001#)

-- SQL Server:
SELECT ISDATE('12/01/2001')

ISNULL(data)

-- Access:
SELECT ISNULL(column) FROM tbl

-- SQL Server:
SELECT ISNULL(column,1) FROM tbl

SELECT COALESCE(NULL, DateUpdated, DateAdded, GETDATE()) FROM tbl

ISNUMERIC(data)

SELECT ISNUMERIC(column)

LEFT(data, n)

SELECT LEFT(column,5)

LEN(data)

SELECT LEN(column)

LCASE(data) -> LOWER(data)

-- Access:
SELECT LCASE(column)

-- SQL Server:
SELECT LOWER(column)

LTRIM(data)

SELECT LTRIM(column)

REPLACE(data, expression1, expression2)

SELECT REPLACE(column, 'bob', 'frank')

RIGHT(data, n)

SELECT RIGHT(column,8)

RTRIM(data)

SELECT RTRIM(column)

CSTR(data) -> STR(data)

-- Access:
SELECT CSTR(column)

-- SQL Server:
-- if column is NUMERIC:
SELECT STR(column)
-- if column is not NUMERIC:
SELECT CAST(column AS VARCHAR(n))

MID(data, start, length) -> SUBSTRING(data, start, length)

-- Access:
SELECT MID("franky goes to hollywood",1,6)

-- SQL Server:
SELECT SUBSTRING('franky goes to hollywood',1,6)

UCASE(data) -> UPPER(data)

-- Access:
SELECT UCASE(column)

-- SQL Server:
SELECT UPPER(column)

StrConv

-- Access:
SELECT StrConv("aaron bertrand",3)

-- SQL Server:
SELECT LEFT(UPPER('aaron'),1)
+ LOWER(RIGHT('aaron',LEN('aaron')-1))
+ ' '
+ LEFT(UPPER('bertrand'),1)
+ LOWER(RIGHT('bertrand',LEN('bertrand')-1))

proper casing an entire block of text

TRIM(data)

-- Access:
SELECT TRIM(column)
SELECT LTRIM(RTRIM(column))

-- SQL Server:
SELECT LTRIM(RTRIM(column))

String Sorting

CREATE TABLE names
(
fname VARCHAR(10)
)
INSERT names VALUES('bob')
INSERT names VALUES('_bob')
INSERT names VALUES(' bob')
INSERT names VALUES('=bob')
INSERT names VALUES('andy')
INSERT names VALUES('_andy')
INSERT names VALUES(' andy')
INSERT names VALUES('=andy')
INSERT names VALUES('''andy')
INSERT names VALUES('''bob')
INSERT names VALUES('-andy')
INSERT names VALUES('-bob')
INSERT names VALUES('andy-bob')
INSERT names VALUES('bob-andy')

SELECT fname FROM names ORDER BY fname
DROP TABLE names

SQL Server  Access 2K
----------  ---------
andy        andy
bob         bob
'andy       _andy
'bob        _bob
-andy       =andy
-bob        =bob
=andy       andy
=bob        'andy
_andy       -andy
_bob        andy-bob
andy        bob
andy-bob    'bob
bob         -bob
bob-andy    bob-andy

NULL Comparisons

-- Access:
[...] WHERE column <> NULL

-- SQL Server:
[...] WHERE column IS NULL
[...] WHERE column IS NOT NULL

[...] WHERE ISNULL(col1,'') = ISNULL(col2,'') AND col1 IS NULL

Article #2073

OTHER SYNTAX CHANGES

There are possibly dozens of other slight syntax changes that may have to be made when moving from Access to SQL Server. Here are a few of the more significant ones:

WITH TIES

WITH TIES is a common usage with SELECT TOP in SQL Server. This syntax is not valid in Access (the default behavior for TOP in Access is to use WITH TIES, so in truth, there is no direct way to use "without TIES" iN Access).FIRST(), LAST()

FIRST() and LAST() don't have relevance in a relational database that is not based on physical ordering of a table - a table is, by definition, an unordered set of rows. If you want the "first" or "last" row in a dataset, you probably have some idea of which row should be first or last, so use an ORDER BY clause to force it. The order of a SELECT statement is not guaranteed, and could change from one execution to the next (it's up to the optimizer, not the physical storage or what you'd like the order to be). The only way you can ensure a desired ordering of a result set is by using an ORDER BY clause; absolutely no exceptions. So, instead of using FIRST() or LAST(), when you move your query to SQL Server, you can use TOP 1 with an ORDER BY (or ORDER BY DESC) clause, or MIN()/MAX() (with or without a subquery).IIF(expression, resultIftrue, resultIfFalse)

-- Access:
SELECT alias = IIF(Column<>0, "Yes", "No")
FROM table

-- SQL Server:
SELECT alias = CASE WHEN Column<>0 THEN 'Yes' Else 'No' END
FROM table

SELECT alias = CASE
WHEN Column='a' THEN 'US'
WHEN Column='b' THEN 'Canada'
ELSE 'Foreign'
END
FROM table

Switch()

-- Access:
SELECT Switch(
On=1,'On',
On=0,'Off'
) FROM table

-- SQL Server
SELECT CASE
WHEN On=1 THEN 'On'
WHEN On=0 THEN 'Off'
END FROM table

-- or
SELECT CASE On
WHEN 1 THEN 'On'
WHEN 0 THEN 'Off'
END FROM table

VAL()

Val('5561T5') = 5561
Val('T55615') = 0
Val('556165') = 556165

DECLARE @val VARCHAR(12)
SET @val = '5561T5'
SELECT CONVERT(INT, LEFT(@val,PATINDEX('%[^0-9]%',@val+' ')-1))

DISTINCTROW

SQL Server supports DISTINCT but does not support DISTINCTROW.

OBJECTS

When creating tables and other objects, keep the following limitations in mind:

Access uses MAKE TABLE, while both platforms support CREATE TABLE;
Access object names are limited to 64 characters;
SQL Server 7.0+ object names are limited to 128 characters;
SQL Server 6.5 object names were limited to 30 characters and no spaces; and,
Stored queries in Access become Stored Procedures in SQL Server.

For information about the number and size of objects allowed in Access and SQL Server, see Article #2345.

STORED QUERIES

Stored queries in Access are a way to store query information so that you don't have to type out ad hoc SQL all the time (and update it throughout your interface everywhere you make a similar query). Being a non-GUI guy, the easiest way I've found to create a stored query in Access is to go to Queries, open "Create query in Design View", switch to SQL View, and type in a query, such as:

PARAMETERS ProductID INTEGER;
SELECT ProductName, Price
FROM Products
WHERE ProductID = [productID]

Be careful not to use any reserved words, like [name], as parameter names, or to give your parameters the SAME name as the column -- this can easily change the meaning of the query.

Once you have the same schema within SQL Server, when moving to stored procedures, the basic difference you'll need to know is syntax. The above stored query becomes:

CREATE PROCEDURE MyQuery
@ProductID INT
AS
BEGIN
SELECT ProductName, Price
FROM Products
WHERE ProductID = @productID
END

You can create this stored procedure using this code through QUery Analyzer, or you can go into the Enterprise Manager GUI, open the database, open the Stored Procedures viewpane, right-click within that pane and choose New > Stored Procedure. Paste the above code (or a query that might make a bit more sense given *your* schema), click Check Syntax, and if it all works, click Apply/OK. Don't forget to set permissions!

Now in both cases, you can call this code from ASP as follows:

<%
productID = 5
set conn = CreateObject("ADODB.Connection")
conn.open "<connection string>"
set rs = conn.execute("EXEC MyQuery " & productID)
do while not rs.eof
' process recordset here
' ...
rs.movenext
loop
%>

See Article #2201 for a quasi-tutorial on writing stored procedures.

FORMS

Yes, Access has pretty little forms that you can create easily with VBA. There is no such thing in SQL Server; you will either need to develop an application (the most rapid to put together would probably be an ASP front end), or you could use Access as a front end (employing an Access Data Project, or ADP).

SECURITY

Access is limited to security in terms of username / password on the database. It also is subject to Windows security on the file itself (as well as the folder it resides in). Typically, ASP applications must allow the anonymous Internet guest account (IUSR_<machine_Name>) to have read / write permissions on file and folder. Username / password access to the database cannot be controlled with any more granularity.

SQL Server has two authentication modes, and neither are much like Access security at all. You can use Windows Authentication, which allows you direct access to domain Users and Groups from within the interface. You can also use Mixed Mode, which allows SQL Server to maintain usernames and passwords (thereby negating the need for a domain or other Windows user/group maintenance).

Once you have determined an authentication mode, users have three different levels of access into the database: login (at the server level), user (at the database level), and object permissions within each database (for tables, views, stored procedures, etc). Just to add a layer of complexity, SQL Server makes it easy to "clone" users by defining server-wide roles, and adding users to that role. This is much like a Group in a Windows domain; in SQL Server, you can use the built-in definitions (and customize them), or create your own. Alterations to a role's permissions affect all users that are members of that role.

Microsoft has a thorough whitepaper you should skim through before jumping into SQL Server. If you're going to deploy your own SQL Server box (as opposed to leasing a dedicated SQL Server, or a portion of one), by all means read the SQL Server Security FAQ.

MORE INFORMATION

Article #2182 has a list of tools and tutorials that will aid in the migration process. Also be sure to read Microsoft's migration whitepaper for some helpful info from the vendors themselves. Finally, if you're into books, APress has a great title called From Access to SQL Server.



// home // advanced search // news // categories // sql build chart // downloads // statistics
ASP FAQ

What are the main differences between Access and SQL Server?

Related Articles