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.
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 Type | Bytes | Range | Access Equivalent |
|---|---|---|---|
| TINYINT | 1 | 0 to 255 | — |
| SMALLINT | 2 | -32,768 to 32,767 | Integer |
| INT | 4 | -2.1B to 2.1B | Long Integer |
| BIGINT | 8 | -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) →
FLOATorDECIMALdepending on use - Access Number (Single) →
REALorDECIMAL
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:
| Column | Recommended Length |
|---|---|
| First/Last Name | NVARCHAR(100) |
| Email Address | NVARCHAR(254) (RFC 5321 maximum) |
| Phone Number | VARCHAR(20) |
| Street Address | NVARCHAR(200) |
| City | NVARCHAR(100) |
| State/Province | NVARCHAR(100) or CHAR(2) for US states |
| ZIP/Postal Code | VARCHAR(20) |
| Country | NVARCHAR(100) |
| Short description | NVARCHAR(500) |
| Long description | NVARCHAR(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:
- Review SSMA's type mappings before running the migration. The defaults are reasonable but not always optimal.
- Convert OLE Object fields — decide whether to store files in the database (VARBINARY(MAX)) or externally (store path/URL as NVARCHAR).
- Check Memo field usage — NVARCHAR(MAX) is correct, but verify that no queries try to use these columns in indexes or GROUP BY clauses.
- Validate numeric precision — Access Currency fields should become DECIMAL(19,4), not FLOAT.
- Test date comparisons — verify that date filters work correctly after migration, especially if your Access database stored dates as text.
- Profile data lengths — before setting VARCHAR lengths, run
SELECT MAX(LEN(ColumnName)) FROM TableNameon 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.
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.
Written by
Anthony Passeri, MBA
Access Expert and Editor
Bridging the gap between Access and the Future
Explore Topics
Keep reading
More on SQL Server
SQL Server Views: The Access Developer\'s Guide
SQL Server views work like saved queries in Access — but with important differences in how they perform, how they are updated, and how they fit into a migration strategy.
SQL Server Stored Procedures for Access Developers
Stored procedures are one of the biggest advantages SQL Server has over Access. Here is what they are, why they matter, and how to start writing them as an Access developer.
SQL Server Indexes: A Practical Guide for Access Developers
Indexes in SQL Server work like indexes in Access — but with more types, more control, and bigger performance implications. Here is what you need to know after migrating.