SQL Programming FAQs

A SQL statement is an atomic unit of work that forms a transaction. Any transaction can either completely succeed or completely fail, adhering to the Atomicity, Consistency, Isolation, and Durability (ACID) properties. A SQL statement is a set of instructions that can consist of identifiers, parameters, variables, names, data types, and SQL reserved words that must compile successfully. A brief explanation of transactions and their relation to SQL statement is available here.

A SQL query is a type of statement that asks for data. In most cases, a query contains the SELECT command to retrieve a result set. Further insights into the differences between a statement and a query are available here.

A SQL database is a relational database that uses SQL to store and work with data. A relational database is like a highly organized drawer in a filing cabinet. Every drawer fits precisely in a slot, and only certain items can fit inside a drawer and next to each other. Likewise, a relational database consists of tables grouped into schemas. Every table has a structure defined by its columns (attributes) and their data types. This structure is commonly referred to as “schema;” however, the word schema can also describe the database itself. Every table contains a collection of rows (records). It is important to note that a table in the database terminology context is a logical abstraction designed for ease of use. The data are stored in data structures called pages.

SQL commands are classified in the following categories:

– Data Definition Language (DDL) (i.e. CREATE, ALTER, DROP, TRUNCATE)
– Data Manipulation Language (DML) (i.e. INSERT, UPDATE, DELETE, MERGE
– Data Query Language (DQL) (i.e. GRANT, REVOKE, DENY)
– Data Control Language (DCL) (i.e. BEGIN TRANSACTION, COMMIT, ROLLBACK, SAVE TRANSACTION)
– Transaction Control Language (TCL) (i.e. SELECT)

A relationship between two tables in a RDBMS is based on a set of columns acting as primary keys and foreign keys. A key uniquely identifies a row and can consist of one or many columns. Matching keys can produce several types of relationships between tables: one-to-one, one-to-many (many-to-one), and many-to-many. The following document presents a SQL Server Table Design Guide. This other document explains the differences among table constraints such as primary and foreign keys.

Databases execute queries in the following sequence. First comes the Query Parser, which parses and validates SQL statement syntax. Next, the Query Optimizer creates an execution plan. The plan represents the most efficient way to retrieve data or accomplish the script task. The optimizer estimates costs based on statistics, indexes, and data distribution. Finally, the plan generated based on the programmer’s query is handed over to the Execution Engine. The execution engine executes the plan and returns results. The job of a good SQL programmer is to help the optimizer find efficient ways to execute a given query by using the correct commands, indexes, and good practices.

T-SQL stands for Transact-SQL which is an extension of Structured Query Language (SQL). T-SQL builds on top of the standard SQL specification ANSI and ISO have defined. Microsoft SQL Server uses T-SQL as its SQL flavor.

The following summarizes the most common SQL commands. Mastering those commands is a prerequisite for anyone who wants to work with SQL Server daily.

In the most common case, the majority of data professional prefer SSMS (SQL Server Management Studio) or VS Code. Both tools support IntelliSense and syntax highlighting for your SQL code. SSMS is the preferred choice for SQL Server veterans and professionals focusing on database administration. VS Code offers the best of both worlds, with a slightly better appeal to developers and data scientists.

To read data from a SQL table you use the SELECT command. SELECT is the foundational data query command allowing the retrieval of one or many rows as a single result set. A good starting point for learning SELECT is available here and here.

To add data to a SQL table you use the INSERT command. INSERT is a core DML command. New data can come as hardcoded values the developer provides, or from a SELECT statement from another table, or even a stored procedure. A good starting point on how to get started using INSERT is available here and here.

To update existing data in a SQL table you use the UPDATE command. UPDATE is another command part of the DML category. Developers use UPDATE to update one or many columns in a table according to a condition. A good starting point on using UPDATE is available here and here.

To delete existing data in a SQL table you can use the DELETE or TRUNCATE command. DELETE removes all data, subject to a given condition, whereas TRUNCATE directly clears out the whole table. A good starting point on how to get started using DELETE is available here. Some of the fine nuances between DELETE and TRUNCATE are presented here.

While technically the DELETE and TRUNCATE commands are the only commands dedicated to physically removing data, there exist two mechanisms for logical data removal. These mechanisms are called soft delete and archive. The implementations can vary; however, the result is the same. With soft delete we mark a row (a record) as “deleted” by setting a flag, e.g., a column IsDeleted to equal 1. This approach allows easy filtering of “deleted” records when needed or verifying what precisely was “deleted” and when. On the other hand, the archiving mechanism refers to moving data from one table, the operational one, to another table containing “deleted” records.

SQL Server uses the JOIN command to retrieve data from multiple tables based on logical relationships between them. Joins are fundamental to relational database operations and enable you to combine data from two or more tables into a single result set. Joining tables to obtain the needed data for a query, script or stored procedure is a key concept as you learn about SQL Server development. In a nutshell, joins are typically performed in the FROM clause of a table or view for the SELECT, INSERT…SELECT, SELECT…INTO, UPDATE and DELETE statements.

There are several ways to join tables in SQL Server: INNER JOIN (match rows between the two tables specified in the INNER JOIN statement based on one or more columns having matching data), LEFT OUTER JOIN (based on the two tables specified in the join clause, all data is returned from the left table), RIGHT OUTER JOIN (based on the two tables specified in the join clause, all data is returned from the right table), CROSS JOIN (based on the two tables specified in the join clause, a Cartesian product is created), FULL JOIN (based on the two tables specified in the join clause, all data is returned from both tables regardless of matching data.) Finally, there is also the concept of self-join. In this circumstance, the same table is specified twice with two different aliases to match the data within the same table.

The GROUP BY clause in SQL Server allows grouping of rows. Generally, GROUP BY is used with an aggregate SQL Server function, such as SUM, AVG, MIN, MAX, COUNT, etc. GROUP BY can also be used with optional components such as CUBE, ROLLUP and GROUPING SETS. Except for COUNT(*), aggregate functions ignore null values. All aggregate functions are deterministic. In other words, aggregate functions return the same value each time that they are called, when called with a specific set of input values. Instructions on how to best use GROUP BY are available here and here.

NULL is an unknown value that can be stored as a valid value – it is neither a zero, nor an empty string. NULL values can also be evaluated in SQL queries. In a table, NULL is defined at the column level. The following document summarizes what NULL stands for and how to handle NULL values.

Developers use the WHERE clause to define a set of conditions for the returned data. WHERE frequently contains logical operators such as AND, OR, NOT, the LIKE operator for comparing a character string against a pattern, or the IN operator. Examples for the WHERE clause logic are available here.

There are several ways to perform calculations. Here are a few categories and examples.

– Arithmetic – use arithmetic expressions directly in a SELECT statement, e.g., + (addition), – (subtraction), * (multiplication), / (division), % (modulo).
– Functions – use built-in mathematical functions that accept an expression as input. The input can be a column or an arithmetic expression. Some examples include ABS (absolute value), ROUND (rounding), SQRT (square root), POWER (exponentiation).
– Aggregation – to calculate totals or averages over a set of rows we can use aggregate functions: SUM, AVG, MIN, MAX, COUNT.

There are three major aspects to troubleshooting your SQL Code.
The first aspect relates to the code itself, where the developer may need to debug, optimize or track any potential execution errors. If the code executes successfully, but there is still an issue (e.g., slow-running query, index misuse, etc.) it is possible that the SQL Server engine does not use an optimal execution plan. This is where aspect number two of troubleshooting SQL code comes in. Even if the code execution is completed, the developer may need to diagnose any query performance issues. Finally, you may need to diagnose the SQL Server instance itself for instance- or server-related issues (disk space, CPU utilization, etc.). On many occasions a poorly written SQL statement may cause cascading problems.
To get started using the SSMS SQL code debugger you can check this document. For first steps in SQL Server performance troubleshooting you can check this document.

In the most common case, developers interact with a SQL Server database using hand-written SQL code in SSMS or another code editor. However, both SQL Server on-prem and cloud versions support programmatic interaction from application code. One current way to interact programmatically with a SQL Server instance is using the mssql-python driver for Python-based workloads allowing the programmatic generation of DQL or DML statements. Additional libraries also allow modeling the database objects from code.

There are three different interfaces that enable programmatic interaction with SQL Server: ODBC (Open Database Connectivity), JDBC (Java Database Connectivity), and DDBC (Direct Database Connectivity). ODBC is a standard, language-agnostic API that requires database-specific drivers. JDBC is a Java-specific API that allows Java applications to interact with SQL Server. Finally, DDBC enables direct connections to SQL Server without requiring an external driver manager.