SQL Server and T-SQL Basics: Build an Invoice Report

SQL Server databases contain schemas such as dbo, which contain tables and other objects. dbo.invoices explicitly identifies the intended namespace. USE can change the database context where the client and permissions allow it, but it is not a PostgreSQL connection-switch instruction. A production connection string should normally identify the target database deliberately.

GO is a batch separator understood by clients and tools such as SQL Server utilities; it is not an ordinary T-SQL statement executed by the server in the same way as SELECT. Do not send GO through a driver that does not process it. Semicolons terminate statements, and ending the statement before a WITH clause avoids a common CTE parsing ambiguity.

IDENTITY(1,1) generates integer values starting at one and increasing by one according to SQL Server’s allocation rules. The primary key still establishes the key constraint. Identity values can have gaps and do not by themselves represent customer-visible invoice numbering. When a statement needs to return an inserted identity, an OUTPUT clause is a direct statement-level tool; other connection or scope functions have their own rules.

The example customer field uses NVARCHAR for Unicode text and its literals use the N prefix. DECIMAL(10,2) gives an exact decimal amount representation. BIT is commonly used for boolean-like saved values in SQL Server, rather than copying PostgreSQL BOOLEAN declarations. Field types should follow the required domain rather than being translated by name alone.

TOP (2) belongs immediately after SELECT in T-SQL. It restricts the result to two records but does not decide which two unless ORDER BY defines their priority. ORDER BY amount DESC,id ASC requests larger amounts first and resolves ties with the unique key. A default storage order or clustered index should not be treated as a substitute for an output-order clause.

TOP ... WITH TIES can return more records than its nominal count when the boundary ORDER BY values tie. Adding a unique id to that ordering removes complete-row ties, so choose the order according to the intended contract. For offset pagination, SQL Server uses ORDER BY with OFFSET and FETCH rather than a MySQL-style trailing LIMIT.

COUNT(*) counts invoice rows, SUM(amount) totals known values and AVG(amount) measures their arithmetic mean. SQL Server’s AVG result type depends on the input type; integer input can lose fractional intent unless cast appropriately. Decimal monetary input should be used with the required precision and rounding policy, not converted to floating point simply to print a decimal.

CAST(expression AS type) is standard-style, while CONVERT(type,expression,style) is T-SQL’s conversion form. TRY_CAST and TRY_CONVERT can produce NULL for many failed permitted conversions, helping an import identify bad text without treating it as a legitimate zero. Retain the raw import field and validity indicator so originally missing and invalid values remain distinguishable.

BEGIN TRANSACTION, COMMIT and ROLLBACK group related row changes. If invoice creation and its line-item insertion are one business operation, define how failure prevents a partial saved invoice. SQL Server error handling often uses TRY...CATCH with appropriate transaction-state checks; production handling should be designed for the actual failure cases rather than copied as unexplained boilerplate.

Bind customer names and thresholds through the database driver. T-SQL variables use an at-sign, but driver parameter syntax can differ from handwritten local variables. A query that includes an at-sign placeholder still needs proper value binding; interpolating user text into it does not become safe merely because the tutorial mentions parameters.

A local temporary table commonly has a single-hash name such as #invoice_stage. Its scope differs from a permanent dbo table and from a global temporary table. Keep multi-statement staging on the intended connection. A CTE lasts only for its statement and should not be treated as a reusable session table.

SQL Server provides estimated and actual execution-plan facilities. Use them to inspect access paths and row estimates after correctness is established. An actual plan accompanies execution, so consider effects for write statements. An index on status and amount might help an open-invoice queue, but its benefit depends on selectivity, ordering and maintenance costs. Measure instead of assuming any indexed field makes a query fast.

Run this T-SQL setup once. Omitting the identity field from the inserts lets SQL Server allocate it in the fresh practice table. The amounts are required, so every summary value in this specific example has complete monetary input.

CREATE TABLE dbo.tsql_invoices (
 id INTEGER IDENTITY(1,1) PRIMARY KEY,
 customer NVARCHAR(40) NOT NULL,
 amount DECIMAL(10,2) NOT NULL CHECK(amount>=0),
 status VARCHAR(10) NOT NULL
);
INSERT INTO dbo.tsql_invoices (customer,amount,status) VALUES
 (N'Ana',40,'open'),(N'Ben',20,'paid'),(N'Ana',30,'open'),(N'Cara',30,'open');

Produce one summary row for open invoices with their record count and total amount. Do not include paid invoice 2.

SELECT COUNT(*) AS open_count,SUM(amount) AS open_total
FROM dbo.tsql_invoices WHERE status='open';

open_count | open_total
3 | 100.00

Open amounts are forty, thirty and thirty, giving a total of one hundred across three records. Both Ana invoices count separately because they are separate liabilities. COUNT(DISTINCT customer) would measure different customers instead and return two, which is not an invoice count. If amounts later become optional, include completeness measures before presenting this as a full outstanding balance.

Consult the T-SQL tutorial, TOP reference and CAST/CONVERT reference for the demonstrated features.