SQL Data Types Interview Questions

CHAR is fixed-length and always allocates the declared size, padding shorter values with spaces. CHAR(50) always uses 50 bytes regardless of the actual content length. VARCHAR is variable-length and only uses the space needed plus 1-2 bytes of overhead, making it more storage-efficient for variable-length data. NVARCHAR is like VARCHAR but stores Unicode characters using 2 bytes per character, so NVARCHAR(50) can use up to 100 bytes. Use CHAR for fixed-length data like country codes, VARCHAR for regular text, and NVARCHAR when you need to support multiple languages.

Collation defines the rules for how string data is sorted and compared in the database, covering things like character set, case sensitivity, and accent sensitivity. For example, with a case-insensitive collation, 'Apple' and 'apple' are treated as equal in a WHERE clause or ORDER BY. You can set collation at the server, database, column, or even expression level. Mismatched collations between columns in a JOIN can cause errors or unexpected behavior, which is a common gotcha when working with multiple databases.

-- Column with specific collation
CREATE TABLE products (
    name VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS  -- CI = case insensitive
);

-- Comparing with explicit collation
SELECT * FROM products WHERE name COLLATE Latin1_General_CS_AS = 'Widget';