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 product descriptions that can vary significantly in length, from short phrases to long paragraphs, and may include Unicode characters. The engineer wants to optimize storage and performance while accommodating these variable-length strings. Which data type should the engineer use for the product description column?
- ANVARCHAR(MAX)
- BTEXT
- CVARCHAR(255)
- DCHAR(255)
Show answer & explanationAnswer & explanation
Correct answer: A. NVARCHAR(MAX)
NVARCHAR(MAX) is the most suitable data type for variable-length strings that may contain Unicode characters and can be very long. It optimizes storage by only using space for the actual data stored, rather than a fixed length, and supports the full range of Unicode characters.
Why the other options are wrong
- B. TEXT is a deprecated data type in SQL Server (replaced by VARCHAR(MAX) and NVARCHAR(MAX)) and should be avoided in new designs.
- C. VARCHAR(255) is a variable-length non-Unicode string. While it optimizes space for varying lengths, it does not fully support Unicode characters (e.g., emojis, international scripts) without conversion issues.
- D. CHAR(255) is a fixed-length string and would waste considerable space for short descriptions, and it does not inherently support Unicode characters efficiently.
NVARCHAR(MAX)
A variable-length Unicode string data type in SQL Server that can store up to 2 GB of character data, optimizing storage for varying string lengths.
- Stores Unicode characters (e.g., international languages, emojis).
- Variable-length, so storage is efficient for diverse string lengths.
- Can store a very large amount of text (up to 2 GB).
- Preferred over deprecated TEXT data type.
Memory trick: NVARCHAR(MAX) for all your variable Unicode text needs.