A data type defines what a column or expression can contain and how its value is interpreted. Choosing one is a design decision, not a clerical detail: the wrong type can increase storage, memory pressure, TempDB activity, cost, and performance problems. A common mistake is assigning NVARCHAR(MAX) to every employee column because it accepts every value. That apparent flexibility weakens the schema and discards useful information about dates, numbers, identifiers, and limits. section 3.0
Use the most restrictive type that safely stores every required value:
- Represent dates with DATE rather than strings, and numbers with an appropriate numeric type rather than NVARCHAR(MAX). section 3.0
- Choose integer sizes deliberately. TINYINT uses 1 byte, SMALLINT 2, INT 4, and BIGINT 8; smaller key types can materially reduce storage in large tables. section 3.1
- Treat primary-key and foreign-key columns, indexed values, and large fact tables as especially important targets for type review. section 3.1
- Use fixed-length strings when values have consistent lengths and variable-length strings when their lengths vary. section 3.6
- Consider specialized types such as HIERARCHYID, XML, and JSON when the data model and application requirements justify them. section 3.0 section 3.6
The employee table mistake
An employee table created with NVARCHAR(MAX) for EmployeeID, dates, salary, and weekly hours accepts almost anything, but it makes validation and efficient storage harder. A safer definition uses INT or SMALLINT for identifiers, DATE for dates, MONEY for salary, INT for hours, BIT for a staff-or-contract flag, and bounded NVARCHAR lengths for names and departments. The correct choice still depends on expected ranges and requirements; “smallest” means restrictive enough, not arbitrarily narrow. section 3.0 section 3.1
Listing 3.1 Creating an employees table
CREATE DATABASE HumanResources ;
GO
USE HumanResources ;
GO
CREATE TABLE dbo.Employees (
EmployeeID NVARCHAR(MAX) NOT NULL,
FirstName NVARCHAR(MAX) NOT NULL,
LastName NVARCHAR(MAX) NOT NULL,
DateOfBirth NVARCHAR(MAX) NOT NULL,
EmployeeStartDate NVARCHAR(MAX) NOT NULL,
ManagerID NVARCHAR(MAX) NULL,
Salary NVARCHAR(MAX) NOT NULL,
Department NVARCHAR(MAX) NOT NULL,
DepartmentCode NVARCHAR(MAX) NOT NULL,
Role NVARCHAR(MAX) NOT NULL,
WeeklyContractedHours NVARCHAR(MAX) NOT NULL,
StaffOrContract NVARCHAR(MAX) NOT NULL, #1
ContractEndDate NVARCHAR(MAX) NULL
) ;
Inspect the deliberately over-permissive employee-table definition, then compare it with the corrected column types. section 3.0
