SQL Server interview questions covering sql server fundamentals.
Open a question for the full answer, code and interview guidance.
Microsoft SQL Server is a relational database management system (RDBMS) developed by Microsoft. It stores, manages, secures, and retrieves structured data. SQL Server uses T-SQL (Transact-SQL), Microsoft's extension of standard SQL. In ASP.NET Core applications, SQL Server is commonly used as the persistent database and is accessed through Entity Framework Core, ADO.NET, Dapper, or stored procedures.
SQL stands for Structured Query Language. It is the standard language used to communicate with relational database systems. SQL is used to create database objects, retrieve data, insert records, update records, delete records, control permissions, and work with transactions. SQL is a language; SQL Server is a database management system that implements SQL and Microsoft's T-SQL extensions.
T-SQL stands for Transact-SQL. It is Microsoft's extension of standard SQL and adds programming features such as variables, conditions, loops, error handling, transactions, stored procedures, and additional built-in functions. These features allow developers to implement complex data-processing logic directly in SQL Server.
A database is an organized collection of related data stored electronically so applications and users can efficiently create, retrieve, update, and manage information. For example, an e-commerce database can contain Customers, Products, Categories, Orders, OrderItems, and Payments tables. SQL Server can host multiple databases within one SQL Server instance.
RDBMS stands for Relational Database Management System. It primarily stores data in tables made of rows and columns and allows relationships to be established between tables. Relationships are normally enforced using keys. For example, Products.CategoryId can reference Categories.Id through a foreign-key constraint. SQL Server, PostgreSQL, MySQL, and Oracle Database are examples of relational database systems.
A table is a database object used to store related data in rows and columns. Columns define attributes and data types, while each row represents an individual record. A Products table, for example, can contain Id, Name, Price, and IsActive columns.
A row represents one complete record in a table, while a column represents one particular attribute shared by all records. For example, in a Products table, Id, Name, and Price are columns. A record containing 1, 'Laptop', and 1200.00 is one row. In relational terminology, a row may also be called a record or tuple, while a column may be called an attribute.
A schema is a logical namespace or container for database objects such as tables, views, stored procedures, and functions. Schemas help organize objects, separate names, and manage permissions. In dbo.Products, dbo is the schema and Products is the table. Custom schemas such as Sales or HR can also be created.
dbo means database owner and is also the name of the default schema commonly used in SQL Server databases. For example, dbo.Products is a schema-qualified object name. Explicitly specifying the schema is a good practice because it makes object references clear and avoids unnecessary schema-resolution ambiguity.
SQL commands are commonly grouped conceptually into DDL, DML, DQL, DCL, and TCL. DDL defines database structures, such as CREATE, ALTER, and DROP. DML modifies data, such as INSERT, UPDATE, and DELETE. DQL retrieves data, primarily with SELECT. DCL manages permissions using commands such as GRANT, DENY, and REVOKE. TCL manages transactions using BEGIN TRANSACTION, COMMIT, and ROLLBACK. The exact classification terminology can vary between books and interviewers, but these categories are useful for explaining the purpose of SQL statements.
DDL stands for Data Definition Language. It is used to create, alter, or remove database structures such as tables and other database objects. Common DDL statements include CREATE, ALTER, and DROP.
DML stands for Data Manipulation Language. It is used to insert, update, and delete data stored in tables. Common DML statements include INSERT, UPDATE, and DELETE. In production systems, UPDATE and DELETE statements should be written carefully because an incorrect or missing WHERE condition can affect many or all rows.
DQL stands for Data Query Language and primarily refers to retrieving data with SELECT. SELECT queries can include filtering, sorting, joins, grouping, subqueries, CTEs, and window functions. Retrieving data efficiently is one of the most important SQL skills for application developers and database interviews.
DELETE removes rows and can use a WHERE condition to remove selected records. TRUNCATE removes all rows from a table, does not support WHERE, and normally resets an identity counter to its seed value. DROP removes the database object itself, including its table definition. A common interview misconception is that TRUNCATE can never be rolled back. In SQL Server, TRUNCATE TABLE can be rolled back when it is executed inside an explicit transaction that has not yet been committed.
A primary key is a constraint that uniquely identifies each row in a table. Primary-key values must be unique and cannot be NULL. A table can have only one primary-key constraint, but the primary key can contain more than one column; this is called a composite primary key.
A foreign key establishes and enforces a relationship between tables. Values in the child table must correspond to valid referenced key values in the parent table, subject to the constraint definition. Foreign keys help enforce referential integrity and prevent invalid relationships, such as assigning a product to a category that does not exist.
IDENTITY is a SQL Server column property that automatically generates numeric values, commonly for surrogate primary keys. IDENTITY(1,1) means the seed starts at 1 and the increment is 1. Identity values should not be assumed to be gap-free because deletes, rollbacks, failed inserts, caching, and other events can leave gaps.
NULL represents a missing, unknown, or unavailable value. It is not the same as zero, an empty string, or FALSE. NULL should normally be checked using IS NULL or IS NOT NULL rather than = NULL. SQL uses three-valued logic—TRUE, FALSE, and UNKNOWN—so NULL can affect comparisons, filters, joins, and expressions in ways that interview candidates should understand.
A constraint is a rule enforced by SQL Server to protect data validity and integrity. Common constraints include PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, and DEFAULT. Database constraints are important even when an ASP.NET Core application performs model validation, because constraints provide an integrity boundary at the database level for every application or process that writes data.
SQL Server is the database engine that stores and processes data. SQL Server Management Studio (SSMS) is a graphical management and development tool used to connect to and administer SQL Server. SQL Server handles storage, query execution, indexes, transactions, locking, security, and other database-engine responsibilities. SSMS provides an interface for writing T-SQL, viewing execution plans, managing database objects, configuring security, and performing administrative tasks such as backup and restore. Therefore, SSMS is not the database itself; it is a client and administration tool used to work with SQL Server.