SQL Server Interview Questions, Part 5: Practical Answers

SQL Server interview questions need answers with clear scope and version context. Part 5 covers administration, string functions and data integrity. Explain the rule and its exceptions before recommending a command.

How do you rename a database?

Use ALTER DATABASE with MODIFY NAME for a current user database. Renaming requires appropriate permissions and exclusive access. It does not rename the physical or logical database files.

To get exclusive access, switch the database to SINGLE_USER WITH ROLLBACK IMMEDIATE, rename it, then switch it back to MULTI_USER. Check connections and any code that uses the old name before you touch a working database.

How do server, database and session settings differ?

sp_configure manages server-level options; ALTER DATABASE manages database options. SET statements control applicable session settings. Some options are captured when a module is created, so scope alone does not describe every behavior.

SELECT DB_NAME() AS DatabaseName,
       SESSIONPROPERTY('QUOTED_IDENTIFIER') AS QuotedIdentifier;

SELECT name,value,value_in_use
FROM sys.configurations
WHERE name IN(N'max degree of parallelism',N'cost threshold for parallelism');

These queries read settings without changing them. value and value_in_use can differ for server options. A database default and a connection’s effective setting are also different things.

What are the main replication types?

Transactional and merge publications commonly begin with an initial snapshot. The snapshot is an initialization step, not a fourth independent change-stream type. Choose a topology around data ownership, synchronization needs and conflict handling.

Which Windows services should you recognize?

The Database Engine runs as a SQL Server service. SQL Server Agent runs scheduled jobs where the installed edition supports it. Other services depend on the installed features.

Microsoft Distributed Transaction Coordinator is a Windows service used for distributed transactions. Do not describe every service as newly installed or required by every SQL Server installation. Identify the actual instance and feature before diagnosing its service state.

How do GRANT, DENY and REVOKE differ?

GRANT permits an action, DENY explicitly denies it, and REVOKE removes a permission entry. Revoking one grant does not remove access through every other role or permission path. Evaluate the effective permission in the relevant principal context.

A remembered “DENY always wins” answer is incomplete. SQL Server has exceptions, including column-level grants versus table-level denies. Ownership and privileged execution also need their own context.

What does QUOTED_IDENTIFIER control?

With QUOTED_IDENTIFIER ON, double quotes delimit identifiers and single quotes delimit strings. With it OFF, double quotes can delimit strings. Bracket-delimited identifiers remain available with either setting.

This setting affects parsing and is required for several indexed-object scenarios. Keep the module’s captured setting distinct from a later session change. The first diagnostic above reads the connection’s setting; it does not change it.

How do STUFF and REPLACE differ?

STUFF removes characters at a chosen position and inserts replacement text. REPLACE substitutes every matching occurrence of the search text. The following input makes that difference visible.

SELECT STUFF(N'abcabc',1,3,N'Z') AS ByPosition,
       REPLACE(N'abcabc',N'abc',N'Z') AS ByMatchingText;

The position-based result should be Zabc. The matching-text result should be ZZ. Position, length and match semantics matter more than calling both functions “replacement.”

How do you count rows accurately?

COUNT_BIG(*) counts rows in the selected input. COUNT_BIG(column) excludes NULL values for that column. The query’s isolation and predicates still determine which rows it observes.

DECLARE @Rows table (Value int NULL);
INSERT @Rows(Value) VALUES (1),(NULL),(3);
SELECT COUNT_BIG(*) AS AllRows,
       COUNT_BIG(Value) AS NonNullValues
FROM @Rows;

The example should return three rows and two non-NULL values. SELECT * is not a row-counting technique to recommend. The old sysindexes metadata query is not an exact substitute for counting the intended input.

How do you rebuild master?

System-database rebuilding uses the version-specific SQL Server Setup recovery procedure. It is a planned recovery operation with backups and a restoration plan, not an interview-time utility to run casually.

What do the system databases contain?

tempdb is recreated when the Database Engine starts. Its data has no user-database durability guarantee. Treat system-database recovery as a separate operational topic rather than changing their contents directly.

What is data integrity?

Data integrity means preserving valid values and relationships under your database rules. Constraints enforce parts of those rules. They cannot prove that every recorded fact is true.

What do keys and constraints guarantee?

A foreign key does not have to reference only a primary key. A positive-value CHECK alone does not require a value. Choose the constraint set around the actual domain rule, including NULL behavior.

Related SQL Server Interview Topics

• SQL Server Interview Questions and Answers.
• SQL Server Interview Questions and Answers – Part 1
• SQL Server Interview Questions and Answers – Part 2
• SQL Server Interview Questions and Answers – Part 3
• SQL Server Interview Questions and Answers – Part 4
• SQL Server Interview Questions and Answers – Part 6
• SQL Server Interview Questions and Answers Complete List Download

Interview answers get easier when every rule comes with its limit.

A good interview answer is not a memorized line, it is a rule with its scope.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.