Interview Questions and Answers on Data Types and Constraints | SQL Server

Understanding data types and constraints in SQL Server is crucial for designing robust and efficient databases. Familiarity with SQL Server data types, key constraints, and integrity rules will help you excel in interviews. Below are 30 key interview questions and answers, organized by topics, to ensure you’re well-prepared for your SQL Server interview.

Data Types and Constraints | SQL Server

1. SQL Server Data Types

1. What are the different types of data types in SQL Server?
   • SQL Server offers a variety of data types, including numeric types (e.g., INT, DECIMAL), string types (e.g., VARCHAR, CHAR), date/time types (e.g., DATE, DATETIME), and more.
2. What is the difference between VARCHAR and CHAR in SQL Server?
   • VARCHAR is a variable-length string data type, while CHAR is a fixed-length string type. VARCHAR uses only as much space as needed, while CHAR always uses the specified length.
3. Explain the DATETIME and DATE data types in SQL Server.
   • DATETIME stores both date and time values, while DATE only stores the date part, omitting the time. DATE has a smaller storage requirement than DATETIME.
4. What is the purpose of the FLOAT data type in SQL Server?
   • FLOAT is used to store approximate numeric values with floating decimal points. It is typically used for scientific calculations requiring precision over a wide range of values.
5. What is the BIT data type in SQL Server?
   • The BIT data type stores Boolean values (0, 1, or NULL). It is used to represent logical binary data, such as true/false or yes/no values.
6. What are TEXT and NTEXT data types used for in SQL Server?
   • TEXT and NTEXT are used to store large amounts of text data. TEXT stores non-Unicode characters, while NTEXT is used for Unicode characters. They are being deprecated in newer versions.
7. What is the difference between DECIMAL and NUMERIC data types in SQL Server?
   • DECIMAL and NUMERIC are functionally identical. Both store exact numeric values with a defined precision and scale, commonly used for storing monetary values or fixed-point numbers.
8. What is the MONEY data type used for in SQL Server?
   • The MONEY data type is used to store currency values. It has a fixed precision and scale (4 decimal places) and is ideal for handling financial data.
9. Explain the UNIQUEIDENTIFIER data type in SQL Server.
   • The UNIQUEIDENTIFIER data type stores globally unique identifiers (GUIDs), which are used to generate unique values across systems, typically for use as primary keys.
10. What is the XML data type in SQL Server?
    • The XML data type is used to store XML documents or fragments in a column. It allows storing and querying XML data directly within SQL Server.

2. Primary Keys, Foreign Keys, and Unique Constraints

11. What is a primary key in SQL Server?
    • A primary key is a constraint that uniquely identifies each row in a table. It must contain unique values and cannot contain NULL values. Only one primary key is allowed per table.
12. What is the difference between a primary key and a unique key in SQL Server?
    • Both primary and unique keys enforce uniqueness, but a primary key cannot contain NULL values, while a unique key can. A table can have multiple unique keys but only one primary key.
13. What is a foreign key in SQL Server?
    • A foreign key is a constraint that enforces a relationship between two tables. It ensures that the value in one table’s column matches the primary key value in another table.
14. Can a foreign key reference multiple columns?
    • Yes, a foreign key can reference multiple columns, called a composite foreign key, which is used to enforce referential integrity across multiple columns in a parent table.
15. What is a composite primary key in SQL Server?
    • A composite primary key consists of two or more columns in a table that together uniquely identify a row. It is used when no single column can uniquely identify a record.
16. Can a foreign key be created without an index in SQL Server?
    • Technically, you can create a foreign key without an index. However, for better performance, SQL Server automatically creates an index on the foreign key columns if one does not already exist.
17. What is a unique constraint in SQL Server?
    • A unique constraint ensures that all values in a column or a group of columns are distinct. Unlike primary keys, unique constraints allow for NULL values.
18. Can a column with a foreign key constraint have duplicate values in SQL Server?
    • Yes, foreign key columns can contain duplicate values, as the foreign key ensures that the values match a unique key in the referenced table but does not require uniqueness in the foreign key column itself.
19. What happens if you try to insert a row with a foreign key value that does not exist in the parent table?
    • SQL Server will raise a foreign key constraint violation error and prevent the insertion, maintaining referential integrity.
20. What is a self-referencing foreign key in SQL Server?
    • A self-referencing foreign key is when a table’s foreign key refers to its own primary key. It is used for hierarchical data, such as employee-manager relationships within the same table.

3. NOT NULL, DEFAULT, and CHECK Constraints

21. What is the NOT NULL constraint in SQL Server?
    • The NOT NULL constraint ensures that a column cannot store NULL values. This constraint is used to enforce data integrity by requiring values to be entered in a column.
22. How do you add a NOT NULL constraint to an existing column in SQL Server?
    • You can add a NOT NULL constraint to an existing column using the ALTER TABLE statement: ALTER TABLE table_name ALTER COLUMN column_name datatype NOT NULL;.
23. What is the DEFAULT constraint in SQL Server?
    • The DEFAULT constraint automatically provides a value for a column when no value is specified during an INSERT operation. It helps ensure that all rows have valid data even if not explicitly provided.
24. Can the DEFAULT constraint be used with NULL values?
    • No, the DEFAULT constraint cannot be used to set NULL values. It is only used to assign a non-null default value when no value is provided during an insert.
25. How do you specify a default value when creating a table?
    • You can define a default value using the DEFAULT keyword in the CREATE TABLE statement, for example: CREATE TABLE table_name (column_name datatype DEFAULT value);.
26. What is the CHECK constraint in SQL Server?
    • The CHECK constraint ensures that values in a column meet a specific condition, such as being within a specified range or matching a particular pattern.
27. Can you add a CHECK constraint to an existing column in SQL Server?
    • Yes, you can add a CHECK constraint to an existing column using the ALTER TABLE statement: ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (condition);.
28. What is the difference between CHECK and WHERE clauses in SQL Server?
    • The CHECK constraint is used to enforce rules on column values in a table, whereas the WHERE clause is used in queries to filter rows based on conditions.
29. How does SQL Server handle violations of constraints like NOT NULL or CHECK?
    • When a violation occurs, SQL Server raises an error and prevents the operation (such as INSERT or UPDATE) from being completed, ensuring data integrity.
30. Can constraints be disabled in SQL Server?
    • Yes, constraints can be temporarily disabled using the ALTER TABLE statement with the NOCHECK option, though this is not recommended for production environments as it could lead to data integrity issues.