SQL Server Interview Topic 10: Data Quality and Security

This topic checks whether you think about data safety. Full stack developers must protect the database from wrong data, unsafe queries, and unnecessary access.

🎯 Interview Goal

You should be able to explain how constraints, validation, permissions, parameters, and audit logs keep application data safe.

Q76. How do you prevent duplicate data?

Quick interview answer:

Use a unique constraint or unique index for values that must not repeat, such as admission number or email. Application validation is useful, but database rules are stronger because they protect data even if the application has a bug.

Study in detail: Constraints and Indexes and Performance - These lessons explain unique rules and indexes.

Q77. Why are constraints important even when the API validates data?

Quick interview answer:

API validation can stop many mistakes, but it is not the final protection. Data may come from another API, migration script, import job, or admin tool. Constraints protect the database from invalid data at the source.

Study in detail: Constraints - This article explains database-side rules.

Q78. What is SQL injection?

Quick interview answer:

SQL injection happens when user input changes the meaning of a SQL query. It can expose, change, or delete data. The common protection is to use parameterized queries and avoid joining raw user input into SQL.

Study in detail: Security - This lesson explains safe query habits for developers.

Q79. What is least privilege?

Quick interview answer:

Least privilege means a user or application should have only the permissions it needs. For example, a reporting user may only need read access, not delete access. This reduces damage if an account is misused.

Study in detail: Security - This article explains access control basics.

Q80. How do you protect sensitive data?

Quick interview answer:

Protect sensitive data by limiting access, avoiding unnecessary columns in queries, securing connection strings, and using proper authentication. For very sensitive fields, teams may also use encryption or masking depending on the requirement.

Study in detail: Security and Compliance and Audit - These lessons explain safe handling of important data.

Q81. What should be recorded in an audit table?

Quick interview answer:

An audit table should record the table or record changed, the action, who changed it, when it changed, and important old and new values. This is useful for fees, marks, permissions, and other sensitive operations.

Study in detail: Compliance and Audit and Triggers and Audit Logging - These lessons explain audit design.

Q82. How do you safely delete data?

Quick interview answer:

First check whether the data should be deleted or only marked inactive. If deletion is required, verify affected rows with SELECT, use WHERE, and consider a transaction. In production, confirm backup or restore options.

Study in detail: Update and Delete and Backup and Restore - These articles explain safe delete thinking.

Practice Before Next Topic

Prepare answers for these scenarios:

1. Stop duplicate admission numbers.
2. Protect student mobile numbers.
3. Explain SQL injection to a beginner.
4. Design an audit table for fee changes.
5. Safely delete a test student record.

💡 Beginner Interview Tip

Security answers should be simple and serious. Mention parameters, permissions, validation, and audit when relevant.