Specifying Length, Precision and Scale of Datatype

Once you've chosen a datatype for a column, several datatypes ask for one more piece of information: a size argument in parentheses right after the datatype name. What that argument means, and what it's called, depends on which datatype it's attached to. This lesson covers the three variations you'll encounter: length, precision and scale, and fractional-second precision.

CREATE TABLE dbo.Products (
    ProductID   int            NOT NULL PRIMARY KEY,
    ProductCode varchar(20)    NOT NULL,
    ProductName nvarchar(100)  NOT NULL,
    UnitPrice   decimal(10, 2) NOT NULL,
    WeightKg    decimal(8, 3)  NULL,
    CreatedAt   datetime2(3)   NOT NULL
);

Declaration	Meaning
varchar(20)	variable-length text, up to 20 bytes
nvarchar(100)	Unicode text, up to 100 byte-pairs
decimal(10,2)	10 total digits, including 2 fractional digits
decimal(8,3)	8 total digits, including 3 fractional digits
datetime2(3)	3 fractional-second digits, such as .123
int	a fixed-size datatype; no length, precision, or scale argument at all

Length applies to character and binary datatypes: char, varchar, nchar, nvarchar, binary, and varbinary. For varchar(n) and char(n), n ranges from 1 to 8,000 and counts bytes, so a column holding UTF-8 text may store fewer than n characters if any of them are multi-byte. For nvarchar(n) and nchar(n), n ranges from 1 to 4,000 and counts byte-pairs instead of bytes; most characters consume one pair, but a small number of supplementary Unicode characters consume two. When a column genuinely needs to hold more than that, varchar(max) and nvarchar(max) remove the limit entirely, up to 2 GB.

The length you choose matters differently depending on whether the column is fixed-length or variable-length. A char(20) or binary(20) column always consumes exactly 20 bytes per row, whether the value stored is one character or twenty, space-padded or zero-padded to fill the gap. A varchar(20) column, by contrast, only consumes however many bytes the actual value needs, plus a small fixed overhead, so a short value doesn't waste the unused space the way a fixed-length column would. That's why fixed-length types are reserved for data that's genuinely always the same length, a two-letter state abbreviation, a fixed-format product code, while everything else defaults to a variable-length type. Binary data follows the identical rule: binary(32) is the natural choice for a SHA2_256 hash, which is always exactly 32 bytes, while varbinary(n) suits binary data of unpredictable size, a small uploaded image or an attached document.

Precision and scale apply together, only to decimal(p,s) and its exact synonym numeric(p,s):

• Precision (p): the total number of digits the column can hold, from 1 to 38.
• Scale (s): how many of those digits sit to the right of the decimal point, from 0 to p.
• Integer digits: whatever's left over, p minus s, the digits to the left of the decimal point.

decimal(10,2) has 8 integer digits and 2 fractional digits, giving it a range of -99,999,999.99 through 99,999,999.99. Note that the sign and the decimal point itself don't count toward precision; only actual digits do. A few more combinations make the pattern concrete:

Declaration	Integer Digits	Fractional Digits	Range
decimal(5,2)	3	2	-999.99 to 999.99
decimal(10,2)	8	2	-99,999,999.99 to 99,999,999.99
decimal(8,3)	5	3	-99,999.999 to 99,999.999
decimal(18,4)	14	4	-99,999,999,999,999.9999 to 99,999,999,999,999.9999

It's worth being precise here, because a value that's too big for its column fails in different ways depending on the datatype.

For character and binary types, inserting a value longer than the column's declared length doesn't silently truncate it. Under SQL Server's default session settings, the statement fails outright:

-- ProductCode is varchar(20)
INSERT INTO dbo.Products (ProductID, ProductCode, ProductName, UnitPrice, CreatedAt)
VALUES (1, 'THIS-CODE-IS-WAY-TOO-LONG-FOR-TWENTY', N'Sample Product', 19.99, SYSDATETIME());
-- Msg 2628: String or binary data would be truncated in table 'dbo.Products',
-- column 'ProductCode'. Truncated value: 'THIS-CODE-IS-WAY-TOO'.

The error, not a silently shortened value, is the safety net. It only works this way for an implicit insert or update, though; an explicit conversion with CAST or CONVERT to a shorter character type truncates without complaint, so the protection depends on how the value reaches the column.

For decimal and numeric columns, the two ways a value can fail to fit behave differently from each other and from the character case above:

• Too many total digits (exceeding precision). If a value needs more integer digits than the column has room for, SQL Server rejects it outright with an arithmetic overflow error, the same kind of hard stop as the truncation error above. Trying to insert 123456789.00 into the UnitPrice decimal(10,2) column, 8 integer digits available, fails for exactly this reason.
• Too many fractional digits (exceeding scale). If a value fits within the column's integer digits but carries more decimal places than the scale allows, SQL Server doesn't reject it and doesn't truncate it either; it rounds the value to the defined scale. Inserting 19.998 into UnitPrice results in 20.00 being stored, rounded up to the nearest cent, not 19.99.

That second behavior surprises people who assume the extra digits just get chopped off. They don't: SQL Server rounds by default during the conversion, following standard rounding rules, so it's worth checking a scale choice against real data before assuming truncation will quietly protect you from a value that's more precise than the column expects.

Length and precision/scale aren't the only size arguments you'll see. datetime2(n) and time(n) take a single numeric argument of their own, fractional-second precision, ranging from 0 to 7, that controls how many digits of sub-second accuracy the column keeps. This argument isn't precision and scale in the decimal sense, there's no separate "integer digits" concept for a timestamp, but it plays the same role: it's where you tell SQL Server exactly how much detail a column actually needs to retain.

n	Resolution	Example	datetime2(n) Storage
0	1 second	12:00:00	6 bytes
3	1 millisecond	12:00:00.123	7 bytes
7	100 nanoseconds	12:00:00.1234567	8 bytes

datetime2(3), as in the CreatedAt column above, stores time down to the millisecond, enough to order events reliably in most application logging; datetime2(7) stores it down to 100 nanoseconds, the maximum the type supports, useful when you genuinely need to distinguish events that happen extremely close together. A lower value isn't just a style choice; it genuinely saves storage across every row, the same kind of trade-off as choosing a shorter varchar length rather than defaulting to the widest option available.

Leaving the size argument off entirely doesn't mean "give me the maximum." It falls back to a default, and the defaults are smaller than most people expect:

• A bare varchar or char column, declared with no length at all, defaults to a length of just 1.
• A bare decimal or numeric column, declared with no precision or scale, defaults to decimal(18,0), 18 total digits and zero fractional digits, silently dropping anything after the decimal point.

CREATE TABLE dbo.Mistakes (
    Code  varchar,   -- becomes varchar(1), not what anyone intends
    Price decimal    -- becomes decimal(18,0): $19.99 would be stored as $20
);

Both of these are easy mistakes to make by accident, a quick CREATE TABLE typed without thinking through every column's size, and both fail in ways that aren't obvious until you actually try to insert real data. Specifying the argument explicitly on every column that needs one is the simplest way to avoid both traps.

Numbers carry real weight in a database: they're what gets a client paid correctly, what a revenue forecast is built on, what a tax form reports. Getting a column's scale wrong doesn't just look sloppy, it can silently misrepresent the value it's supposed to hold. The right scale depends on what the number actually represents, not just on how many decimals look about right. A few common categories:

• Monetary amounts (invoice line items, unit prices, account balances) almost always use a scale of 2, matching the cents or minor currency unit most businesses transact in.
• Quantities (item counts, number of units sold, number of pills in a prescription bottle, number of dependents on a tax form) are typically whole numbers, scale 0, since there's no such thing as a fractional person or a fractional item on most invoices.
• Physical measurements (shipping weight, wattage for a light bulb, a TV screen's size, a hard disk's RPM rating, a tire's maximum rated speed) vary by how much real-world precision the measurement actually supports; a shipping weight might reasonably need three decimal places, while a screen size in inches rarely needs more than one.

Matching a column's scale to what its values genuinely need avoids both ends of the problem: too little scale loses real precision the data actually has, while too much scale reserves storage, and implies accuracy, that the underlying data never had in the first place. These are also exactly the kinds of numeric columns that later get rolled up into reports and forecasts, through SQL Server Analysis Services or a similar reporting layer, where a sloppily scaled source column turns into a visibly wrong total further downstream.

Size choices don't only affect how much disk space a column consumes; they can also limit what you're later able to do with that column. SQL Server caps the total size of an index's key columns at 900 bytes. A single varchar(900) column fits that limit on its own, but a composite key combining a few generously sized varchar columns can exceed it surprisingly easily, and SQL Server will refuse to create the index, or allow creation but reject any row insert or update whose actual key value is too wide, depending on how the index is defined. A column declared far wider than its data actually needs isn't just wasted storage; it can quietly rule out indexing that column the way you'd planned to.

This is one more reason the earlier advice, size every column to what its values genuinely need rather than reaching for the largest convenient option, pays off well beyond the immediate storage savings.

Table designs evolve, and sooner or later you'll need to widen a column that turned out too small. ALTER TABLE ... ALTER COLUMN handles this:

ALTER TABLE dbo.Products
ALTER COLUMN ProductCode varchar(40) NOT NULL;

Growing a length, precision, or fractional-second argument is usually a quick, low-impact change. Shrinking one is a different matter: SQL Server has to check every existing value in the column against the new, smaller limit, and the statement fails if even one row's data no longer fits, the same overflow and truncation rules covered above, just applied retroactively to data that's already there. It's far cheaper to think through a column's realistic maximum size once, at design time, than to discover years later that production data no longer fits the column you originally chose.

In the next lesson, you'll learn how and when to allow NULL values in a column, the next attribute in this module after a value's type and its size.