Database foundations
Tables, schemas and row grain
What does one row represent?
Understand the concept
A table stores a particular entity or event type. A row is one record, and a column is one property of that record. A schema groups objects within a database and can provide an authorisation boundary. lab.Customer is a two-part name: lab is the schema and Customer is the table. dbo is common, but it is not mandatory.
Grain defines what one row represents in the business. One SalesOrder row is an order; one OrderItem row is a product line within that order. COUNT(*) on order lines is not the order count. JOIN and GROUP BY can change grain, so describe a result row in one sentence before writing a query.
An order with two products has two lines. Joining the order date to those lines is valid. Repeating a whole-order total on both lines and adding it up double-counts the order. Separating the model’s grain from the analysis grain is fundamental to correct SQL.
Use grain to decide whether a metric describes customers, orders, lines or days.
Calling COUNT(*) an order count without checking the table grain.
One topic. Three levels of depth.
SELECT TOP (3) CustomerId, FullName FROM lab.Customer ORDER BY CustomerId;One row is one customer. The two-part name identifies the object explicitly.
This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.
Try it yourself first.
Count the lines in order 101.
Show the solution and explanation
SELECT COUNT(*) AS LineCount FROM lab.OrderItem WHERE OrderId = 101;The result is 2. SUM(Quantity) gives 3 units, which is a different metric.