SQL Server

SQL Server Data Types: What Access Developers Need to Know

SQL Server has more data types than Access — and some Access types map to SQL Server in ways that surprise developers. Here is the complete mapping and what to watch out for.

A
Anthony Passeri, MBA
••7 min read
SQL Server Data Types: What Access Developers Need to Know

When you migrate from Access to SQL Server — whether manually or using SQL Server Migration Assistant — one of the first decisions you face is how to map Access field types to SQL Server data types. Some mappings are obvious. Others are not.

Choosing the wrong data type creates problems that are expensive to fix later: truncated data, failed comparisons, unexpected sort orders, and storage bloat. Here is the complete mapping and the reasoning behind each choice.

Text: VARCHAR vs. NVARCHAR

This is the most consequential data type decision for most databases.

VARCHAR(n) stores non-Unicode text. Each character takes 1 byte. Maximum length: 8,000 characters.

NVARCHAR(n) stores Unicode text. Each character takes 2 bytes. Maximum length: 4,000 characters. Supports any language, including Chinese, Arabic, Japanese, and emoji.

Access Short Text maps to either, depending on your data.

Use NVARCHAR when:

  • Your data includes non-English characters (accented letters, non-Latin scripts)
  • You are not certain about the character set of your data
  • You want to future-proof the column
  • You are building a new application (default to NVARCHAR)

Use VARCHAR when:

  • You are certain the data is ASCII-only (codes, identifiers, postal codes)
  • Storage size is a significant concern
  • You are interfacing with legacy systems that use VARCHAR

SSMA default: SQL Server Migration Assistant maps Access Short Text to NVARCHAR by default. This is the safe choice.

NVARCHAR(MAX) replaces Access Memo/Long Text fields. It stores up to 2 GB of text. Use it for notes, descriptions, and any field that might hold large amounts of text.

Integers: INT, BIGINT, SMALLINT, TINYINT

Access has one integer type (Long Integer, 4 bytes). SQL Server has four:

SQL Server TypeBytesRangeAccess Equivalent
TINYINT10 to 255—
SMALLINT2-32,768 to 32,767Integer
INT4-2.1B to 2.1BLong Integer
BIGINT8-9.2 quintillion to 9.2 quintillion—

For primary keys: use INT for most tables. Use BIGINT if you expect more than 2 billion rows (rare, but worth considering for high-volume transaction tables).

For AutoNumber fields: Access AutoNumber maps to SQL Server IDENTITY:

CustomerID INT IDENTITY(1,1) NOT NULL PRIMARY KEY

IDENTITY(1,1) means start at 1, increment by 1 — the same behavior as Access AutoNumber.

For small lookup values: TINYINT or SMALLINT saves space for columns like StatusCode or CategoryID where values are always small. The savings are modest but add up in large tables.

Decimal Numbers: DECIMAL, FLOAT, REAL

Access has Number (with field sizes Double, Single, Integer, Long Integer) and Currency.

DECIMAL(p, s) — exact numeric. p is total digits, s is digits after the decimal point. DECIMAL(10, 2) stores values like 12345678.99.

FLOAT — approximate numeric, 8 bytes. Stores very large or very small numbers but introduces floating-point rounding errors. Avoid for financial data.

REAL — approximate numeric, 4 bytes. Same rounding issues as FLOAT, less precision.

Mapping:

  • Access Currency → DECIMAL(19, 4) (SQL Server also has a MONEY type, but DECIMAL is more portable)
  • Access Number (Double) → FLOAT or DECIMAL depending on use
  • Access Number (Single) → REAL or DECIMAL

Rule of thumb: use DECIMAL for anything involving money or quantities where exact values matter. Use FLOAT only for scientific calculations where approximate values are acceptable.

Dates and Times

Access has one Date/Time type. SQL Server has several:

DATETIME — the classic SQL Server date/time type. Range: January 1, 1753 to December 31, 9999. Accuracy: 3.33 milliseconds. This is what SSMA uses by default for Access Date/Time fields.

DATETIME2(n) — improved date/time. Range: January 1, 0001 to December 31, 9999. Accuracy: 100 nanoseconds (configurable). Recommended for new development.

DATE — date only, no time component. 3 bytes. Use when you only need the date (birthdays, order dates where time is irrelevant).

TIME(n) — time only, no date. Use for schedules, business hours.

DATETIMEOFFSET — date, time, and time zone offset. Use when you need to store the time zone alongside the timestamp.

Recommendation: use DATETIME2(0) or DATETIME2(3) for most date/time columns in new development. It is more accurate, has a wider range, and is the modern standard. For migration from Access, DATETIME is fine and avoids any compatibility issues with older tools.

Boolean: BIT

Access Yes/No fields map directly to SQL Server BIT.

IsActive BIT NOT NULL DEFAULT 1

BIT stores 0 (false) or 1 (true). SQL Server also accepts TRUE and FALSE as literals in some contexts.

Watch out: Access stores Yes as -1 and No as 0. SQL Server BIT stores 1 and 0. SSMA handles this conversion automatically, but if you are writing migration scripts manually, account for the difference.

Large Objects: VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX)

Access Memo/Long Text → NVARCHAR(MAX) (up to 2 GB of text)

Access OLE Object → VARBINARY(MAX) (up to 2 GB of binary data)

OLE Objects in Access are one of the most problematic migration scenarios. Access stores OLE Objects with OLE wrapper data that is specific to the Windows OLE subsystem — not just the raw file bytes. When you migrate to VARBINARY(MAX), you need to strip the OLE wrapper to get the actual file content.

For most databases, the better approach is to move files out of the database entirely:

  • Store files in SharePoint, Azure Blob Storage, or a file server
  • Store only the file path or URL in the database

This is cleaner, more performant, and avoids the OLE wrapper problem entirely.

Unique Identifiers: UNIQUEIDENTIFIER

SQL Server has a UNIQUEIDENTIFIER type (GUID — a 36-character unique identifier like a1b2c3d4-e5f6-7890-abcd-ef1234567890). Access has no direct equivalent.

GUIDs are used as primary keys in Dataverse and are common in distributed systems where you need globally unique IDs without a central sequence. For SQL Server databases migrated from Access, integer IDENTITY keys are usually preferable — they are smaller, faster to index, and easier to read.

Use UNIQUEIDENTIFIER when:

  • You need to merge data from multiple databases
  • You are integrating with systems that use GUIDs as keys
  • You are building a distributed system where central key generation is impractical

Choosing Column Lengths

For VARCHAR and NVARCHAR columns, choose lengths that reflect the actual data:

ColumnRecommended Length
First/Last NameNVARCHAR(100)
Email AddressNVARCHAR(254) (RFC 5321 maximum)
Phone NumberVARCHAR(20)
Street AddressNVARCHAR(200)
CityNVARCHAR(100)
State/ProvinceNVARCHAR(100) or CHAR(2) for US states
ZIP/Postal CodeVARCHAR(20)
CountryNVARCHAR(100)
Short descriptionNVARCHAR(500)
Long descriptionNVARCHAR(MAX)

Avoid the temptation to use NVARCHAR(MAX) for everything — it prevents the column from being used in indexes and can hurt performance. Use MAX only when you genuinely need more than 4,000 characters.

The Migration Checklist for Data Types

When migrating from Access:

  1. Review SSMA's type mappings before running the migration. The defaults are reasonable but not always optimal.
  2. Convert OLE Object fields — decide whether to store files in the database (VARBINARY(MAX)) or externally (store path/URL as NVARCHAR).
  3. Check Memo field usage — NVARCHAR(MAX) is correct, but verify that no queries try to use these columns in indexes or GROUP BY clauses.
  4. Validate numeric precision — Access Currency fields should become DECIMAL(19,4), not FLOAT.
  5. Test date comparisons — verify that date filters work correctly after migration, especially if your Access database stored dates as text.
  6. Profile data lengths — before setting VARCHAR lengths, run SELECT MAX(LEN(ColumnName)) FROM TableName on each text column to find the actual maximum length in your data.

Getting data types right at migration time is much easier than changing them after the application is built on top of the new schema.

Work With Access Evolved

If you found this article useful, Access Evolved specializes in exactly this kind of work — from hands-on Microsoft Access support to full database modernization projects.

Mapping Access data types to SQL Server as part of a migration? Schedule a free assessment and avoid the data type pitfalls that derail migrations.

Schedule a Free Call →

Thinking about migrating?

Ready to move beyond Microsoft Access?

Access is a great starting point, but growing businesses often hit its limits. Anthony Passeri specializes in migrating Access databases to modern platforms — Power Platform, Power Apps, SQL Server, Azure, and more — so you keep your data and workflows without the headaches.

A

Written by

Anthony Passeri, MBA

Access Expert and Editor

Bridging the gap between Access and the Future

Explore Topics

#SQL Server#data types#T-SQL#migration#Access migration

Keep reading

More on SQL Server