Microsoft Azure Data FundamentalsDescribe how to work with relational data on AzureMedium
A data engineer is designing a database schema for an Azure SQL Database. The database will store customer information, including addresses. The `PostalCode` column needs to store alphanumeric values, including letters, numbers, and sometimes hyphens, up to a maximum of 10 characters. Which data type is most appropriate for the `PostalCode` column?
- AINT
- BNCHAR(10)
- CVARCHAR(10)
- DNVARCHAR(10)
Show answer & explanationAnswer & explanation
Correct answer: D. NVARCHAR(10)
NVARCHAR(10) is the most appropriate choice because it supports Unicode characters (for global postal codes), is variable-length (saving space for shorter codes), and can store alphanumeric values including hyphens up to 10 characters.
Why the other options are wrong
- A. INT is for whole numbers only and cannot store letters or hyphens.
- B. NCHAR(10) is fixed-length, using 20 bytes even for shorter codes, which is less efficient than NVARCHAR.
- C. VARCHAR(10) is variable-length but stores non-Unicode characters, which might be insufficient for international postal codes (e.g., those with diacritics).
NVARCHAR Data Type
A variable-length Unicode string data type in SQL Server that stores up to 4000 characters. It is suitable for storing text data that may contain characters from different languages.
- Stores Unicode characters, using 2 bytes per character.
- Variable-length, so it only uses as much space as needed.
- Good for internationalization and mixed character sets.
Memory trick: NVARCHAR: Flexible, global text.