SQL Advanced SQL Interview Questions

Failover clustering is a high-availability solution where multiple servers (nodes) share access to the same database storage, and if the active node fails, another node automatically takes over with minimal downtime. SQL Server Failover Cluster Instances (FCI) present a single virtual server name to clients, so applications reconnect transparently after a failover. The shared storage (usually a SAN or Storage Spaces Direct) holds the actual data files, and only one node actively owns the SQL Server instance at any given time. It protects against hardware and OS-level failures but not against data corruption, which is why it's often combined with Always On Availability Groups.

A cursor is a database object that lets you process a result set one row at a time, rather than operating on the whole set at once. You declare it with a SELECT statement, then open it, fetch rows in a loop, process each row, and finally close and deallocate it. Cursors are useful when you need to perform row-by-row operations that genuinely cannot be expressed as a set-based query, like complex procedural logic that depends on the previous row. The downside is that cursors are significantly slower than set-based operations and should be avoided or replaced with set-based alternatives whenever possible.

DECLARE emp_cursor CURSOR FOR
    SELECT emp_id, salary FROM employees;

OPEN emp_cursor;
FETCH NEXT FROM emp_cursor INTO @emp_id, @salary;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- process each row here
    FETCH NEXT FROM emp_cursor INTO @emp_id, @salary;
END;

CLOSE emp_cursor;
DEALLOCATE emp_cursor;

An explicit cursor is used in PL/SQL when you need to manually control the fetch process for a query that returns multiple rows. You declare it, open it, fetch rows one at a time in a loop, and then close it. This is useful when you need fine-grained control, such as processing rows conditionally, fetching in bulk using BULK COLLECT, or managing multiple cursors at the same time. Implicit cursors handle single-row queries automatically, so explicit cursors are reserved for cases where that level of control matters.

DECLARE
    CURSOR emp_cursor IS SELECT employee_id, name FROM employees WHERE department_id = 10;
    v_id employees.employee_id%TYPE;
    v_name employees.name%TYPE;
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO v_id, v_name;
        EXIT WHEN emp_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_id || ' - ' || v_name);
    END LOOP;
    CLOSE emp_cursor;
END;

Replication copies and continuously synchronizes data from one database server to one or more other servers. The main purposes are high availability and disaster recovery, since if the primary server fails you can fail over to a replica. It also improves read performance by distributing read queries across multiple replicas so the primary handles mostly writes. Additionally, replication supports geographic distribution, letting users in different regions query a local replica for lower latency, and it can be used for reporting workloads so heavy analytical queries don't compete with production traffic.

BCP stands for Bulk Copy Program and it is a command-line utility in SQL Server used to import or export large amounts of data between a SQL Server instance and a data file very quickly. It bypasses row-by-row logging when run with the TABLOCK hint and a simple recovery model, making it significantly faster than regular INSERT statements for bulk loads. It is typically used for data migrations, loading flat files into staging tables, or exporting data for external systems. The BULK INSERT SQL statement and the .NET SqlBulkCopy class are the programmatic equivalents that do the same job.

Dynamic SQL is SQL that is constructed as a string at runtime rather than written statically. The three main ways to execute it are: using EXEC (or EXECUTE) with a string, using sp_executesql which is preferred because it supports parameterization and reuses cached execution plans, and using EXEC with a stored procedure name constructed dynamically. sp_executesql is the safest option because passing parameters separately protects against SQL injection, whereas concatenating values directly into the EXEC string leaves you vulnerable.

-- 1. EXEC with a string (avoid for user input)
EXEC ('SELECT * FROM ' + @tableName);

-- 2. sp_executesql with parameters (preferred)
EXEC sp_executesql
    N'SELECT * FROM orders WHERE status = @s',
    N'@s VARCHAR(20)',
    @s = @statusParam;

-- 3. EXEC calling a dynamically named procedure
EXEC (@procName);

DTS stands for Data Transformation Services, which was a feature in SQL Server 2000 used for importing, exporting, and transforming data between different sources. It was replaced by SSIS (SQL Server Integration Services) starting with SQL Server 2005, which is far more powerful and flexible. DTS packages allowed you to define workflows that moved data between databases, flat files, Excel, and other sources, and you could apply transformations during the process.

RAID stands for Redundant Array of Independent Disks, and it is a way of combining multiple physical disks to improve performance, fault tolerance, or both. RAID 0 stripes data across disks for speed but offers no redundancy. RAID 1 mirrors data across two disks, so if one fails the other has a full copy. RAID 5 stripes data with distributed parity across at least three disks, balancing performance and fault tolerance. RAID 10 combines mirroring and striping for both speed and redundancy and is commonly used for SQL Server data files because it handles heavy read and write loads well.

DBCC stands for Database Console Commands. It's a set of maintenance, validation, and diagnostic commands in SQL Server that let you check database integrity, manage memory, and fix corruption. Common examples include DBCC CHECKDB (checks structural integrity of a database), DBCC SHRINKFILE (shrinks a data or log file), and DBCC FREEPROCCACHE (clears the plan cache). These commands are especially useful for DBAs doing routine health checks or troubleshooting performance issues.

DBCC CHECKDB ('YourDatabaseName');
DBCC FREEPROCCACHE;
DBCC SHRINKFILE (YourLogFile, 1);

SQL Server supports three main types of replication. Snapshot replication copies and distributes data exactly as it appears at a specific point in time, good for data that doesn't change often. Transactional replication streams incremental changes (inserts, updates, deletes) from the publisher to subscribers in near real-time, making it ideal for high-frequency OLTP systems. Merge replication allows both the publisher and subscribers to make changes independently and then synchronize, which works well for disconnected or mobile scenarios where subscribers aren't always online.

BCP stands for Bulk Copy Program. It's a command-line utility that ships with SQL Server and lets you bulk import or export large volumes of data between a SQL Server table and a data file. It's much faster than row-by-row inserts because it uses bulk operations, and you can control the format with a format file. It's commonly used for ETL tasks, data migrations, or loading flat files like CSVs into staging tables.

-- Export data from a table to a file
bcp YourDatabase.dbo.YourTable out C:\data\output.csv -c -T -S YourServer

-- Import data from a file into a table
bcp YourDatabase.dbo.YourTable in C:\data\input.csv -c -T -S YourServer

The SQL Server Database Engine is the core service that handles storing, processing, and securing data. It provides controlled access to data, handles transactions, enforces constraints and referential integrity, and manages concurrency through locking and isolation levels. It includes both the relational engine (query parsing, optimization, execution) and the storage engine (buffer management, I/O, file management). When people refer to a SQL Server instance, they're primarily referring to the Database Engine service.

SQL Server Analysis Services (SSAS) is an OLAP and data mining engine used to build analytical solutions on top of your relational data. It lets you create multidimensional cubes or tabular models that aggregate and organize data for fast analytical queries, which is something a standard relational database isn't optimized for. Business intelligence tools like Power BI, Excel pivot tables, and Reporting Services commonly connect to SSAS models. It supports MDX and DAX query languages depending on whether you're using multidimensional or tabular mode.

SQL Server Integration Services (SSIS) is an ETL platform for extracting, transforming, and loading data between different sources and destinations. You build packages visually in SQL Server Data Tools that define data flows, transformations, and control flow logic. It supports connections to flat files, Excel, Oracle, XML, web services, and many other sources. It's the standard Microsoft tool for data warehouse loading, data migration, and automating complex data movement tasks.

SQL Server Data Quality Services (DQS) is a knowledge-driven data quality solution that helps you cleanse, match, and enrich your data. You build a knowledge base by defining rules, domains, and reference data, and then DQS uses that knowledge to identify and fix data quality issues like inconsistent formats, duplicates, or invalid values. It integrates with SSIS through the DQS Cleansing component, making it useful in ETL pipelines where incoming data needs to be validated and standardized before loading into a data warehouse.

SQL Server Reporting Services (SSRS) is a server-based platform for creating, managing, and delivering reports. You design reports using Report Builder or SQL Server Data Tools, and then deploy them to the report server where users can access them via a web portal. Reports can be paginated, interactive, or mobile, and can be delivered on-demand, on a schedule, or triggered by events. It connects to many data sources including SQL Server, Analysis Services, Oracle, and more.

SQL Server Master Data Services (MDS) is a tool for managing master data, meaning the core business entities like customers, products, employees, or locations that are shared across multiple systems. It provides a central hub where you can define, manage, version, and govern that data to ensure consistency across your organization. Business users can interact with MDS through an Excel add-in or a web interface without writing SQL, and changes go through approval workflows to maintain data quality and auditability.

Replication in SQL Server is the process of copying and distributing data and database objects from one database to another and then synchronizing them to maintain consistency. It follows a publisher-distributor-subscriber model where the publisher makes data available, the distributor routes changes, and subscribers receive the data. It's commonly used for high availability, load balancing read workloads, or keeping geographically distributed databases in sync. SQL Server supports snapshot, transactional, and merge replication depending on your consistency and latency requirements.

Yes, SQL Server provides several T-SQL commands for importing data without SSIS. BULK INSERT lets you load flat files directly into a table, OPENROWSET with the BULK option lets you read a file as a rowset, and OPENROWSET or OPENDATASOURCE can connect to external data sources like Excel or another database on the fly. These are lightweight options good for one-off imports or scheduled jobs, though they lack the transformation and error-handling features of SSIS.

-- BULK INSERT example
BULK INSERT dbo.Customers
FROM 'C:\Data\customers.csv'
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRSTROW = 2);

-- OPENROWSET BULK example
SELECT * FROM OPENROWSET(
  BULK 'C:\Data\customers.csv',
  SINGLE_CLOB
) AS RawData;

A Linked Server lets SQL Server connect to an external data source, such as another SQL Server instance, Oracle, or even an Excel file, and query it using four-part naming or OPENQUERY. You set it up through sp_addlinkedserver to define the connection and sp_addlinkedsrvlogin to map credentials. Once configured, you can join local and remote tables in a single query, which is useful for cross-instance reporting or data migration tasks. The main thing to watch is performance: remote data is pulled across the network, so minimizing the rows fetched remotely with OPENQUERY is usually better than filtering locally.

-- Add a linked server
EXEC sp_addlinkedserver 
  @server = 'RemoteServer',
  @srvproduct = 'SQL Server';

-- Query using four-part naming
SELECT * FROM RemoteServer.SalesDB.dbo.Orders;

-- Or use OPENQUERY to push the filter to the remote server
SELECT * FROM OPENQUERY(RemoteServer, 'SELECT * FROM SalesDB.dbo.Orders WHERE Year = 2024');

SQL Server has a built-in feature called Database Mail that lets you send emails directly from T-SQL using sp_send_dbmail. You first configure a Database Mail profile and account through the Database Mail configuration wizard or via stored procedures in msdb, which defines the SMTP server, sender address, and credentials. Once a profile is set up, calling sp_send_dbmail with the recipient, subject, and body is straightforward and you can attach query results or files as well. This is commonly used for job failure alerts, report delivery, or automated notifications from within stored procedures.

EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'AlertProfile',
  @recipients   = 'admin@company.com',
  @subject      = 'Daily Report',
  @body         = 'Please find the attached report.',
  @query        = 'SELECT TOP 10 * FROM dbo.Sales',
  @attach_query_result_as_file = 1;

To allow remote connections to SQL Server, a few things need to be in place. First, in SQL Server Configuration Manager you need to enable TCP/IP under the server protocols and make sure SQL Server is listening on port 1433 or your chosen port. Second, the SQL Server Browser service should be running if you are using named instances. Third, the Windows Firewall needs an inbound rule allowing traffic on that port. On the SQL Server side, the setting Allow remote connections to this server must be enabled, which you can check and set in server properties or via sp_configure with the remote access option.

SQL Server offers two main approaches for storing PDF files. The first is using a varbinary(max) column to store the PDF as a binary blob directly in the table, which keeps everything inside the database but can bloat database size and impact backup times. The second and generally preferred option for larger files is FILESTREAM or FileTable, introduced in SQL Server 2008, which stores the binary data on the NTFS file system while keeping a reference in the database, giving you SQL-managed access with better I/O performance for large files.

-- Option 1: store as varbinary(max)
CREATE TABLE dbo.Documents (
  DocumentId INT PRIMARY KEY,
  FileName   NVARCHAR(255),
  FileData   VARBINARY(MAX)
);

-- Insert a file using OPENROWSET
INSERT INTO dbo.Documents (DocumentId, FileName, FileData)
SELECT 1, 'report.pdf', BulkColumn
FROM OPENROWSET(BULK 'C:\Files\report.pdf', SINGLE_BLOB) AS f;