SQL SQL Basics Interview Questions

SQL stands for Structured Query Language, and it's the standard language for interacting with relational databases. You use it to create and modify database structures, insert and update data, and query records from tables. Almost every relational database system, including PostgreSQL, MySQL, Oracle, and SQL Server, uses SQL as its primary interface. It's been around since the 1970s and remains one of the most essential skills in data engineering and backend development.

SQL databases are relational, meaning data is stored in structured tables with predefined schemas, and relationships between tables are enforced through keys. NoSQL databases cover a broad category including document stores like MongoDB, key-value stores like Redis, and columnar databases like Cassandra, and they typically have flexible or schema-less designs. SQL databases excel at complex queries, joins, and transactional consistency (ACID compliance), while NoSQL databases are often chosen for horizontal scalability, high write throughput, or unstructured data. The choice really depends on your data model and access patterns, not which one is universally better.

SQL commands are grouped into five main categories. DDL (Data Definition Language) handles structure, covering commands like CREATE, ALTER, and DROP. DML (Data Manipulation Language) deals with data itself using INSERT, UPDATE, DELETE, and SELECT. DCL (Data Control Language) manages permissions with GRANT and REVOKE. TCL (Transaction Control Language) handles transactions using COMMIT, ROLLBACK, and SAVEPOINT. Knowing which category a command falls into helps you understand its scope and whether it's reversible.

-- DDL
CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100));

-- DML
INSERT INTO users (id, name) VALUES (1, 'Alice');

-- DCL
GRANT SELECT ON users TO readonly_user;

-- TCL
BEGIN;
UPDATE users SET name = 'Bob' WHERE id = 1;
ROLLBACK;

The SELECT statement is how you retrieve data from one or more tables in a database. You specify which columns you want, which table to pull from, and optionally filter, sort, join, or aggregate the results. It's the most frequently used SQL command and supports a huge range of operations through clauses like WHERE, JOIN, GROUP BY, ORDER BY, and LIMIT.

SELECT name, email
FROM users
WHERE created_at > '2024-01-01'
ORDER BY name ASC
LIMIT 10;

WHERE filters rows before any grouping or aggregation happens, while HAVING filters after grouping. If you want to filter on a raw column value, use WHERE. If you want to filter on the result of an aggregate function like COUNT or SUM, you have to use HAVING because WHERE runs before those calculations exist.

SELECT department, COUNT(*) AS headcount
FROM employees
WHERE status = 'active'          -- filters rows before grouping
GROUP BY department
HAVING COUNT(*) > 10;            -- filters groups after aggregation

WHERE filters individual rows before any grouping happens. It runs early in the query execution pipeline, right after the FROM/JOIN phase. HAVING filters groups after GROUP BY and aggregation are applied, so it can reference aggregate functions like COUNT(), SUM(), or AVG(). You cannot use aggregate functions in a WHERE clause. Use WHERE to filter raw rows and HAVING to filter aggregated results.

-- WHERE filters rows, HAVING filters groups
SELECT department, AVG(salary) AS avg_sal
FROM employees
WHERE status = 'active'    -- filters rows first
GROUP BY department
HAVING AVG(salary) > 50000; -- filters groups after

SQL injection is a security vulnerability where an attacker inserts malicious SQL code into application inputs that get executed by the database. For example, entering ' OR 1=1 -- in a login field could bypass authentication if the application concatenates user input directly into SQL strings. The primary defense is parameterized queries (prepared statements), which separate SQL logic from data. Other defenses include using an ORM, input validation with allowlists, principle of least privilege for database accounts, and stored procedures with parameterized inputs.

SQL Server supports five main join types. INNER JOIN returns only rows where there is a match in both tables. LEFT OUTER JOIN returns all rows from the left table and matching rows from the right, with NULLs where there is no match. RIGHT OUTER JOIN does the opposite. FULL OUTER JOIN returns all rows from both tables, filling NULLs where there is no match on either side. CROSS JOIN returns the Cartesian product of both tables, meaning every row from the left paired with every row from the right. There is also SELF JOIN, which is not a separate keyword but just joining a table to itself using an alias.

-- INNER JOIN
SELECT * FROM A INNER JOIN B ON A.id = B.id;

-- LEFT JOIN
SELECT * FROM A LEFT JOIN B ON A.id = B.id;

-- FULL OUTER JOIN
SELECT * FROM A FULL OUTER JOIN B ON A.id = B.id;

-- CROSS JOIN
SELECT * FROM A CROSS JOIN B;

OLTP stands for Online Transaction Processing and refers to systems designed to handle a large number of short, fast, concurrent transactions like inserts, updates, and deletes. Think of banking systems, e-commerce orders, or point-of-sale terminals where many users are writing and reading small amounts of data simultaneously. OLTP databases are highly normalized to reduce redundancy, optimize for write performance, and maintain data integrity. This is in contrast to OLAP (Online Analytical Processing), which is optimized for complex read-heavy queries over large historical datasets.

SQL Profiler is a graphical tool in SQL Server that lets you capture and analyze a trace of events happening on a SQL Server instance in real time. You can filter by event type, application, login, or duration and see things like which queries are running, how long they take, deadlocks, and login failures. It is extremely useful for diagnosing performance problems and finding slow queries in production. Note that SQL Profiler is considered deprecated as of newer SQL Server versions in favor of Extended Events, which is lighter weight and more flexible.

OPENXML provides a rowset view over an XML document, allowing you to use an XML string as a data source in a SELECT statement or to shred XML into relational rows inside a stored procedure. You first prepare the XML document using sp_xml_preparedocument to get a handle, then pass that handle to OPENXML with an XPath expression and a column mapping, and finally free memory with sp_xml_removedocument. It was the primary way to parse XML in SQL Server before the xml data type and the XQuery methods like .nodes() and .value() were introduced, and those newer approaches are generally preferred today.

DECLARE @xml NVARCHAR(MAX) = '<employees><employee id="1" name="Alice"/></employees>';
DECLARE @handle INT;

EXEC sp_xml_preparedocument @handle OUTPUT, @xml;

SELECT id, name
FROM OPENXML(@handle, '/employees/employee', 1)
WITH (id INT '@id', name VARCHAR(50) '@name');

EXEC sp_xml_removedocument @handle;

An index is a data structure, typically a B-tree, that the database maintains alongside a table to allow fast lookups without scanning every row. When you query with a WHERE clause, ORDER BY, or JOIN on an indexed column, the database can jump directly to the relevant rows instead of doing a full table scan. The tradeoff is that indexes consume storage and slow down INSERT, UPDATE, and DELETE operations slightly because the index must be updated too. You should index columns that are frequently searched or joined on, not every column.

-- Create a basic index
CREATE INDEX idx_employees_department ON employees(department_id);

-- Create a composite index for queries filtering on both columns
CREATE INDEX idx_emp_dept_salary ON employees(department_id, salary);

-- Drop an index
DROP INDEX idx_employees_department;

ORDER BY sorts the result set of a query by one or more columns. By default it sorts ascending (ASC), and you can explicitly specify DESC for descending order. You can sort by multiple columns, where the second column acts as a tiebreaker when the first column has equal values. ORDER BY is always applied last in the logical processing order of a query, after filtering and grouping are done.

-- Sort by salary descending, then by name ascending as tiebreaker
SELECT employee_id, name, salary
FROM employees
ORDER BY salary DESC, name ASC;

A database is an organized collection of structured data stored and managed electronically. It's designed to allow efficient storage, retrieval, and manipulation of data, and it enforces rules like data types and constraints to keep the data consistent. In most software systems, a database sits behind the application and persists data beyond the lifetime of any single session or process.

DBMS stands for Database Management System. It's the software layer that sits between users or applications and the actual database, handling everything from storing and retrieving data to enforcing access controls, managing transactions, and maintaining data integrity. Examples include MySQL, PostgreSQL, Oracle, and SQL Server. Without a DBMS, you'd have to manage raw files yourself with no built-in support for queries, concurrency, or crash recovery.

PL/SQL is Oracle's procedural extension to SQL. It lets you write procedural logic, think loops, conditionals, variables, and exception handling, directly inside the database. You use it to build stored procedures, functions, triggers, and packages. The big advantage is that you can bundle multiple SQL statements into a single program unit that runs on the database server, reducing network round trips and centralizing business logic.

SQL is a declarative query language used to interact with a database, you describe what data you want and the database figures out how to get it. PL/SQL is Oracle's procedural extension that wraps SQL inside a full programming language with variables, loops, conditions, and error handling. SQL operates statement by statement, while PL/SQL lets you write blocks of code that execute as a unit. In short, SQL retrieves or manipulates data, and PL/SQL adds the programming logic around it.

A query is a request you send to a database to retrieve, insert, update, or delete data. Most commonly the term refers to a SELECT statement that asks the database to return a specific set of rows and columns based on conditions you define. Queries can be simple, like fetching all rows from a table, or complex, involving multiple joins, subqueries, aggregations, and window functions.

You use the CREATE TABLE statement and define each column with its name, data type, and any constraints. You can specify primary keys, foreign keys, NOT NULL, UNIQUE, and DEFAULT values inline or as separate constraint definitions at the end of the column list. It's good practice to always define a primary key and think about constraints upfront rather than altering the table later.

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name  VARCHAR(50) NOT NULL,
  last_name   VARCHAR(50) NOT NULL,
  hire_date   DATE,
  department_id INT,
  salary      DECIMAL(10, 2) DEFAULT 0.00
);

A table is the fundamental storage structure in a relational database. It organizes data into rows and columns, similar to a spreadsheet. A field is a single column in that table, representing one specific attribute for every record, such as a customer's email address or order date. Each row in the table is a record, and each field in that row holds the value for that particular attribute.

Views serve several practical purposes. They simplify complex queries by wrapping joins, filters, and aggregations into a reusable object that you query like a table. They also provide a security layer, letting you expose only specific columns or rows to certain users without granting access to the underlying base tables. Views help enforce consistency too, since multiple applications or reports can reference the same view rather than duplicating the same SQL logic everywhere, making maintenance much easier.

Levels in a dimension represent the hierarchy of granularity within that dimension. For a time dimension, levels might be Year, Quarter, Month, Week, and Day. For a geography dimension, you might have Country, State, City, and Zip Code. Levels let OLAP tools drill down from a high-level summary into finer detail, or roll up from detail into a broader view, all within the same dimension.

Local variables are declared inside a stored procedure or batch and are only accessible within that scope. They are prefixed with a single @ symbol in SQL Server, like @myVar. Global variables are system-defined variables provided by the database engine itself, prefixed with @@ in SQL Server, such as @@ROWCOUNT or @@VERSION, and you cannot create your own global variables. The key difference is scope and ownership: local variables are user-declared and temporary, while global variables are system-maintained and available anywhere in the session.

DECLARE @localVar INT = 10;  -- local variable, only lives in this batch
SELECT @@ROWCOUNT;            -- global variable, set by the system after each statement

SQL queries fall into four main categories based on what they do. DDL (Data Definition Language) handles structure: CREATE, ALTER, DROP. DML (Data Manipulation Language) handles data: SELECT, INSERT, UPDATE, DELETE. DCL (Data Control Language) manages permissions: GRANT, REVOKE. TCL (Transaction Control Language) manages transactions: COMMIT, ROLLBACK, SAVEPOINT. Knowing these categories helps you understand what each statement does to the database and when you need special privileges to run it.

A clause is a component of an SQL statement that defines a specific condition or operation. Common clauses include WHERE, GROUP BY, HAVING, ORDER BY, and FROM. Each clause modifies or filters the query in a particular way. You can think of clauses as the building blocks that together form a complete SQL query, and they must follow a specific order: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.

SELECT department, COUNT(*) AS headcount
FROM employees          -- FROM clause
WHERE status = 'active' -- WHERE clause
GROUP BY department     -- GROUP BY clause
HAVING COUNT(*) > 5     -- HAVING clause
ORDER BY headcount DESC;-- ORDER BY clause

The ALIAS command lets you give a table or column a temporary name for the duration of a query, using the AS keyword. It makes output more readable and is essential when you have long table names you need to reference multiple times in joins. Column aliases rename the output header, while table aliases shorten how you reference tables in the query body. The alias only exists within that query and has no permanent effect on the database.

SELECT e.first_name AS fname, d.department_name AS dept
FROM employees AS e
JOIN departments AS d ON e.department_id = d.id;

A CHECK constraint enforces a condition on column values, ensuring that only data satisfying the rule can be inserted or updated. If a value violates the check, the database rejects the operation with an error. It is a way to enforce business rules at the database level rather than relying on application code. For example, you can ensure a salary column never holds a negative value.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    salary DECIMAL(10,2) CHECK (salary >= 0),
    age INT CHECK (age BETWEEN 18 AND 65)
);

A DEFAULT constraint assigns a predefined value to a column when no value is explicitly provided during an INSERT. It saves you from having to specify every column in every insert statement and helps avoid NULL values where a sensible fallback exists. You define it at table creation or add it later with ALTER TABLE. Defaults only kick in when the column is omitted from the INSERT; if you explicitly insert NULL, the default does not apply.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'pending',
    created_at DATETIME DEFAULT GETDATE()
);

-- status and created_at get their defaults automatically
INSERT INTO orders (order_id) VALUES (1);

The most common way is using the DISTINCT keyword, which filters out duplicate rows from the result set. If you need unique records based on specific columns rather than the entire row, you can use GROUP BY on those columns. For more complex deduplication, like keeping only the latest duplicate, a ROW_NUMBER() window function with a CTE is the cleanest approach.

-- Simple distinct
SELECT DISTINCT customer_id FROM orders;

-- Keep only the most recent row per customer
WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
    FROM orders
)
SELECT * FROM ranked WHERE rn = 1;

GROUP BY is used to aggregate rows that share the same values in specified columns, and it almost always appears alongside aggregate functions like COUNT, SUM, or AVG. ORDER BY simply sorts the result set by one or more columns, either ascending or descending, without any aggregation. You can use ORDER BY without GROUP BY, but when you use GROUP BY, you often add ORDER BY afterward to sort the aggregated results. They solve completely different problems: GROUP BY changes the shape of the data, while ORDER BY only changes the presentation order.

SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department        -- collapses rows into groups
ORDER BY headcount DESC;   -- sorts the resulting groups

SQL is a declarative language used to query and manipulate data. You write what you want and the database engine figures out how to get it. PL/SQL is Oracle's procedural extension to SQL that adds programming constructs like variables, loops, conditionals, exception handling, and stored procedures. SQL executes a single statement at a time, while PL/SQL lets you write entire programs that can include multiple SQL statements, business logic, and error handling in a single block. If you are not on Oracle, SQL Server uses T-SQL and PostgreSQL uses PL/pgSQL as their procedural equivalents.

This looks like a partial question comparing SQL and PL/SQL execution models. SQL is designed for single query or command execution, meaning each statement is parsed, optimized, and run independently. PL/SQL is a full programming language that lets you bundle multiple SQL statements with procedural logic into a single compiled block or stored procedure, reducing round trips to the database and enabling complex workflows that a single SQL statement cannot express.

In SQL Server, you create a scheduled job through SQL Server Agent. The process involves creating a job, adding one or more steps that define what T-SQL or command to run, creating a schedule that defines when it runs, and optionally setting up notifications for success or failure. You can do all of this through SQL Server Management Studio under the SQL Server Agent node, or programmatically using system stored procedures.

-- Create the job
EXEC msdb.dbo.sp_add_job @job_name = 'NightlyCleanup';

-- Add a step
EXEC msdb.dbo.sp_add_jobstep
    @job_name  = 'NightlyCleanup',
    @step_name = 'DeleteOldRecords',
    @command   = 'DELETE FROM logs WHERE created_at < DATEADD(day,-90,GETDATE())';

-- Create a schedule (runs daily at 2am)
EXEC msdb.dbo.sp_add_schedule
    @schedule_name     = 'DailyAt2AM',
    @freq_type         = 4,
    @active_start_time = 020000;

-- Attach schedule to job
EXEC msdb.dbo.sp_attach_schedule @job_name = 'NightlyCleanup', @schedule_name = 'DailyAt2AM';
EXEC msdb.dbo.sp_add_jobserver  @job_name = 'NightlyCleanup';

Without seeing the specific query mentioned, the most common issues to look for are: selecting a column in the SELECT list that is not in a GROUP BY clause and not wrapped in an aggregate function, using a WHERE clause to filter on an aggregate instead of HAVING, referencing a column alias in the WHERE clause (aliases are not available at that stage), or using ORDER BY with a column number that is out of range. If you share the actual query, I can point to the exact problem, but reviewing those four areas covers the majority of common query mistakes in interviews.

You handle this with a FOREIGN KEY constraint that includes ON DELETE CASCADE. When you define the foreign key, adding that clause tells the database to automatically delete any child rows in the referencing table whenever the parent row is deleted. This is enforced at the database level, so it works regardless of which application or connection performs the delete.

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE CASCADE
);
-- Deleting a customer will automatically delete all their orders

The main risk is stale data. If another process or application updates the database directly, bypassing Hibernate, the cached object in memory no longer reflects the actual database state, leading to lost updates or incorrect reads. Another risk is that a cached object in a second-level cache can be shared across sessions, so changes made in one transaction may not be visible to another until the cache expires. To overcome this, you can use cache eviction policies and set appropriate TTLs, use cache providers like Ehcache or Redis that support region-based invalidation, mark frequently updated entities as non-cacheable with @Cache(usage = CacheConcurrencyStrategy.NONE), or use optimistic locking with a version column so Hibernate detects and rejects stale writes before they cause data corruption.

Both @@ERROR and @@ROWCOUNT reset after every statement executes, so if you check them sequentially you'll get 0 for the first one by the time you read it. The standard solution is to capture both into local variables in the same statement immediately after the operation you care about. This way both values are preserved before any subsequent statement clears them.

UPDATE Employees SET Salary = Salary * 1.1 WHERE DepartmentId = 3;

DECLARE @err INT, @rows INT;
SELECT @err = @@ERROR, @rows = @@ROWCOUNT;

IF @err <> 0
    PRINT 'Error occurred';
ELSE
    PRINT CAST(@rows AS VARCHAR) + ' rows updated';

In SQL Server you can use TRY_CONVERT or ISDATE to validate whether a string matches a date format. ISDATE returns 1 if the value can be converted to a date and 0 otherwise. TRY_CONVERT is generally preferred because it lets you specify an exact format code and returns NULL on failure instead of raising an error.

-- Using ISDATE (SQL Server)
SELECT CASE WHEN ISDATE('2024-13-01') = 1
            THEN 'Valid Date'
            ELSE 'Invalid Date'
       END AS DateCheck;

-- Using TRY_CONVERT with style code 103 (dd/mm/yyyy)
SELECT CASE WHEN TRY_CONVERT(DATE, '31/12/2024', 103) IS NOT NULL
            THEN 'Valid Date'
            ELSE 'Invalid Date'
       END AS DateCheck;

Use DISTINCT to eliminate duplicate names and filter the DOB column with a BETWEEN clause. It is worth noting that BETWEEN is inclusive on both ends, so the boundary dates are included in the result.

SELECT DISTINCT Name
FROM Employees
WHERE DOB BETWEEN '1960-01-01' AND '1975-12-31'
ORDER BY Name;

Use CREATE TABLE ... AS SELECT with a WHERE clause that evaluates to false, so no rows are copied but the full column structure is. In SQL Server the equivalent syntax is SELECT INTO with a false condition. The resulting table has the same columns and data types but no rows and typically no constraints or indexes from the original.

-- MySQL / PostgreSQL
CREATE TABLE Employees_Empty AS
SELECT * FROM Employees WHERE 1 = 0;

-- SQL Server
SELECT *
INTO Employees_Empty
FROM Employees
WHERE 1 = 0;

The LIKE operator is used for pattern matching in SQL. It works with two wildcards: '%' which matches any sequence of characters, and '_' which matches exactly one character. You can combine them to build flexible search patterns, like finding all names that start with 'J' or contain 'son' anywhere in the string.

SELECT * FROM students WHERE name LIKE 'J%';       -- starts with J
SELECT * FROM students WHERE name LIKE '%son%';    -- contains 'son'
SELECT * FROM students WHERE name LIKE '_ohn';     -- exactly 4 chars ending in 'ohn'

There are five main joins. INNER JOIN returns rows with matching values in both tables. LEFT JOIN returns all rows from the left table plus matching rows from the right, with NULLs where there is no match on the right. RIGHT JOIN is the mirror of that. FULL OUTER JOIN returns all rows from both tables, with NULLs filling in wherever there is no match. CROSS JOIN produces a cartesian product, every row from the left paired with every row from the right, and is used less frequently but is useful for generating combinations.

-- INNER JOIN
SELECT e.name, d.department_name
FROM employees e INNER JOIN departments d ON e.dept_id = d.dept_id;

-- LEFT JOIN
SELECT e.name, d.department_name
FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id;

-- RIGHT JOIN
SELECT e.name, d.department_name
FROM employees e RIGHT JOIN departments d ON e.dept_id = d.dept_id;

-- FULL OUTER JOIN
SELECT e.name, d.department_name
FROM employees e FULL OUTER JOIN departments d ON e.dept_id = d.dept_id;

-- CROSS JOIN
SELECT e.name, d.department_name
FROM employees e CROSS JOIN departments d;

A function in SQL Server is a reusable database object that accepts input parameters, performs some logic, and returns a value. Functions can return either a single scalar value or a table result set. Unlike stored procedures, functions can be used directly inside SELECT, WHERE, or FROM clauses, which makes them very useful for encapsulating reusable calculation logic.

SQL Server has two broad categories: built-in system functions and user-defined functions. Within user-defined functions, there are three types. Scalar functions return a single value like an int or varchar. Inline table-valued functions return a table based on a single SELECT statement. Multi-statement table-valued functions return a table but allow multiple statements and more complex logic inside the function body.

-- Scalar function example
CREATE FUNCTION dbo.GetFullName(@first VARCHAR(50), @last VARCHAR(50))
RETURNS VARCHAR(100)
AS
BEGIN
    RETURN @first + ' ' + @last;
END;

-- Inline table-valued function
CREATE FUNCTION dbo.GetEmployeesByDept(@deptId INT)
RETURNS TABLE
AS
RETURN (SELECT * FROM employees WHERE department_id = @deptId);

SQL Server uses several lock types depending on the operation and isolation level. Shared locks are acquired during reads and allow concurrent reads but block writes. Exclusive locks are taken during INSERT, UPDATE, or DELETE and block all other access. Update locks are used as an intermediate step before escalating to exclusive locks to prevent deadlocks during updates. Intent locks signal that a lower-level lock is being requested, for example an intent exclusive on a table means a row-level exclusive lock will be taken. Schema locks protect table structure during DDL operations. Bulk update locks are used during bulk copy operations.

There are several approaches depending on the volume and requirements. You can use INSERT INTO...SELECT to copy data between databases in the same server with a simple query. Linked servers let you query and move data across different SQL Server instances using four-part naming. BCP (Bulk Copy Program) is a command-line tool for fast bulk exports and imports. SSIS (SQL Server Integration Services) is the enterprise-grade option for complex ETL workflows. You can also use the Import and Export Wizard in SQL Server Management Studio for ad-hoc moves, or backup and restore for moving entire databases.

-- Copy data between databases on the same server
INSERT INTO TargetDB.dbo.employees (name, salary)
SELECT name, salary FROM SourceDB.dbo.employees;

-- Query across a linked server
SELECT * FROM [LinkedServerName].[TargetDB].[dbo].[employees];

You use a SELECT statement to retrieve data from a table. At minimum you need SELECT and FROM, then you add WHERE to filter rows, ORDER BY to sort, and TOP or FETCH NEXT to limit results.

-- Select all columns
SELECT * FROM Employees;

-- Select specific columns with a filter
SELECT FirstName, LastName, Salary
FROM Employees
WHERE Department = 'Sales'
ORDER BY LastName ASC;

SQL Server has a few date-related data types depending on what you need. DATE stores just the date (no time), DATETIME stores date and time down to milliseconds, DATETIME2 is more precise and has a wider date range, and SMALLDATETIME is a smaller but less precise option. You declare a date column the same way as any other data type in a CREATE TABLE or ALTER TABLE statement.

CREATE TABLE Events (
    EventID    INT PRIMARY KEY,
    EventName  VARCHAR(100),
    EventDate  DATE,              -- date only, no time
    CreatedAt  DATETIME2(7)       -- date + time with high precision
);

Synonyms were introduced in SQL Server 2005. A synonym is essentially an alias for a database object, such as a table, view, stored procedure, or function, and it can point to objects in the same database, another database on the same server, or even a linked server. The main benefit is abstraction: if the underlying object moves or gets renamed, you only update the synonym definition rather than every query that references it. Common use cases include simplifying long four-part names for linked server objects, providing a stable public name for objects that might be refactored behind the scenes, and making cross-database queries cleaner.

-- Create a synonym for a table on a linked server
CREATE SYNONYM dbo.Orders FOR LinkedServer.SalesDB.dbo.Orders;

-- Now query it like a local table
SELECT * FROM dbo.Orders WHERE OrderDate > '2024-01-01';

The most direct approach is to check the server name or environment at the top of the script and raise an error if it matches the production instance, which stops execution immediately. You can use RAISERROR or THROW combined with a check against @@SERVERNAME. Another common technique is wrapping code in a transaction with a ROLLBACK at the end during testing, so changes never actually commit. Some teams also use SQLCMD mode with scripting variables to control which environment a script targets.

-- Guard clause at the top of any script
IF @@SERVERNAME = 'PROD-SQL-01'
BEGIN
    RAISERROR('This script must not run on production!', 20, 1) WITH LOG;
    RETURN;
END

The default port for SQL Server is 1433 for the database engine. SQL Server Browser, which helps clients find named instances, listens on UDP port 1434. Named instances are assigned dynamic ports by default, though you can pin them to a specific port through SQL Server Configuration Manager. It is common practice in secure environments to change the default 1433 port to reduce exposure to automated attacks.

You can run SELECT @@VERSION in Query Analyzer or SSMS and it returns a single string containing the SQL Server version, edition, build number, and the Windows operating system version it is running on. It is the quickest way to confirm exactly what you are connected to.

SELECT @@VERSION;

The buffer cache, also called the buffer pool, is an area of memory where SQL Server stores data pages read from disk. When a query needs data, SQL Server checks the buffer cache first to avoid expensive disk reads, which is why a warm cache dramatically improves performance. The log cache is a separate memory area that holds transaction log records before they are flushed to the log file on disk. Log records are written to disk during a checkpoint or when a transaction commits, depending on the recovery model, and this separation lets SQL Server batch log writes for efficiency.