What is the difference between a "record" and a "row" in SQL Server?

Question

There was a rather innocuous question about adding dates and times in SQL Server that set off a rather fascinating taxonomic debate.

So how do we differentiate between these related terms and how we use them properly?

Row

Record

Answer 1

To quote Joe Celko (not only can you find this reference all over the web and in his Wikipedia entry, but you will even see it on T-shirts at some conferences):

Rows are not records.

A lot of people point him out as a pedantic jerk who just likes to humble and verbally abuse newbies, and I will admit that is how he comes across. But I have also met him in person - even shared a meal with him - and I can't tell you how different his real-life persona is from his online front. I even once caught him calling rows records, and he was very embarrassed (full backstory here).

In any case, say what you will about the guy's online character, but he wrote the standard, and the fact that such an authority dictates that there is a distinction should tell you something. And as much as he cringes when someone calls a row a record, so do many of my colleagues - who are also experts in the SQL Server world. And those of us in that camp believe he is right.

For example, Itzik Ben-Gan, an obvious SQL Server guru. Here is a quote from the very first lesson in his Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012:

As an example of incorrect terms in T-SQL, people often use the terms “field” and “record” to refer to what T-SQL calls “column” and “row,” respectively. Fields and records are physical. Fields are what you have in user interfaces in client applications, and records are what you have in files and cursors. Tables are logical, and they have logical rows and columns.

And, knowing Itzik, if you send him an e-mail or corner him at a conference he will happily tell you the same. If you call a row a record, in his opinion, you're not using the terminology correctly.

Now, being an industry full of folks of all kinds, you are likely to find material (such as the tech target articles posted in another answer) that seem to make very subtle distinctions between the two, and you will find many people in the industry consider them the same (I know several folks at Microsoft, and other folks like Brent Ozar, who will just always call it a record). That doesn't make them right, that's just their way of looking at it - they view logical and physical as the same (at least in this context) and many of them probably think the rest of us are just anal retentives spending too much time on semantics.

Since no vendor gets to say "thou shalt call them {records|rows}", we will forever be dealing with this argument, because there will always be someone who doesn't get the logical vs. physical, or was taught differently, or came from Access or programming backgrounds, etc. Just like some people say tomay-to and other people say tomah-to, there will always be a variety of people who range from "they're the same" to "they're completely different" - and many shades in between. Again, that doesn't make any of them right, because nobody can be the ultimate authority on this. But in the SQL Server space, there is definitely a majority.

---

That said, IMHO, when you are talking about data that is in a table, you call it a row. When you are performing an insert, you are inserting a row into a table. When you run an update, you are updating a row that is in a table. And when you perform a SELECT, you are retrieving rows from a table.

Feel free to call it a record once your application has a hold of it. But don't get angry if you say, "I inserted a record," and someone corrects you.

Answer 2

Microsoft has in several places in their organization provided that the official name for tabular data storage per table-entry (to coin a taxonomic definition that serves my own purpose) is called a "ROW". I submit as evidence ROW_NUMBER, ROWCOUNT, ROWVERSION and the DataTable.Rows property, where a DataTable is a C# representation of a TSQL "table" object. In this case, the MSDN properties as a whole encourage the use of row to refer to a collection of data that is one entry in a table. (note I'm trying to avoid the use of "record" or "row" to define this, that being the point of question)

However, the parlance is that an application deals with user "records". Something unique about a record that may not be directly represented by a single storage row is the fact that a record can have subrecords. True, a table can have related many-to-one tables, but those are not stored contiguously, but they are stored logically related.

So, a row is the thing in a table, and a record is the thing the developer works with in practical use.

Answer 3

I've just searched through the document "Information technology — Database languages — SQL Part 2: Foundation (SQL/Foundation)", which defines the ANSI standard for SQL as implemented by all major RDBMSes.

The word row is used primarily throughout the document several hundred times, as expected.

The word record was only used to describe a record that is akin to a record used in Oracle PL/SQL (specifically describing ADA record datatypes). 6 mentions in the document.

I think this clears up this question, and answers the various arguments on both sides.

---

Additional info

From a copy of a (draft version of the latest freely available) SQL standard, which can be found at wiscorp.com (the page SQL Standards has several other older versions and revisions).

Searching the 7IWD2-02-Foundation-2011-12.pdf, with a date of 2011-12-21 reveals that the word row appears 2277 times in the document while the word record appears only 21 times, either as the verb "record" or in some appendices in the end, in specifications of the data type correspondences for SQL data types and host language types (Ada, Pascal).

Moreover, the same document has at page 57 (emphasis mine):

4.15.1 Introduction to tables

This Subclause is modified by Subclause 4.10.1, “Introduction to tables”, in ISO/IEC 9075-9.

A table is a collection of zero or more rows where each row is a sequence of one or more column values. The most specific type of a row is a row type. Every row of a given table has the same row type, called the row type of that table. The value of the i-th field of every row in a table is the value of the i-th column of that row in the table. The row is the smallest unit of data that can be inserted into a table and deleted from a table.

The degree of a table, and the degree of each of its rows, is the number of columns of that table. The number of rows in a table is its cardinality. A table whose cardinality is 0 (zero) is said to be empty.

A table is either a base table, a derived table, or a transient table.

---

So as far as DBMSs that use SQL is concerned:

Rows are not records, fields are not columns, tables are not files!

Answer 4

Because relational databases are rarely used in isolation, in order to avoid confusion between other parts of systems, I always refer to tables and rows and columns. In a client applications, we typically have other constructs, including datareaders, datasets, datarows, datatables, etc - for instance "field" is often used for on-screen data entry and Pascal has a Record datatype which is similar to a struct in C.

Sometimes in a system design, the idea of a "Record" might be used to mean something broader than a single row. It might be a row and it's history. Just as when we talk about a deleted row we might mean a row which is simply marked as deleted with a column or "moved" to a deleted table (and not simply the absence of a row which, by not existing, is rather hard to pin down). There just more varied usage of the term Record.

Tables, rows, and columns are generally accepted terminology for referring to these entities in relational databases, including papers and work by Codd and Date, and the majority of database professionals prefer this terminology as it is more unambiguous.

There is usually no ambiguity when one talks about rows and columns - other people understand you are talking about the underlying database physical design and not any other kind of artifacts from a logical design before the physical design or any later emergent system entities like fields on a screen.

Answer 5

Although your question is already answered very well. I would like to add my points too. May be you find it helpful upto some extend. Also my answer is not specific to SQL Server

These words are used interchangeably.

 1          2         3              4 
--------------------------------------------------------------------
Row    =  Record  =  Tuple        =  Entity 

Column =  Field   =  Attribute    =  Attribute

table  =  File    =  Relation     =  Entity Types(or Entity Set)

• 4 terminology good to use when we learns ER-Modules
• 3 use when Relational Model
• 2 in- general scene, DataBase books start with these terminology because these are much commonly used by people in real life, also in file-system.

Record is the basic unit in storage system that have implicit meaning. In DBMS the word record use in chapter describes how database tables stores on disk blocks. In DBMS a record-oriented file-system is a file system where files are stored as collections of records.

Answer 6

The language keeps evolving. A few decades ago the literate people used "indices" instead of simpler "indexes". As we switched to "indexes", we eliminated an unnecessary complication and made the language more useful. The need to memorize a plural for "index" was pure overhead - it did not in any way help us communicate. Make no mistake, there used to be grammar Nazis who enjoyed correcting those who switched to "indexes". Of course, grammar Nazis lost. This is how Occam's razor eliminates useless details if the whole thing stays relevant long enough.

So let us take it easy - knowing the difference between rows and records adds absolutely nothing to our ability to develop and maintain databases. Many excellent professionals use rows and records interchangeably, yet develop awesome systems. As such, Occam's razor should eventually eliminate the distinction, and the next generation will have to learn one less useless fact. If, of course, SQL is still relevant at that time.

Answer 7

Short Answer:

• A record is a piece of stored (or collected) data.
• A row is a record stored linearly.
• Where ever possible, use the more specific term.

Note: tables store records linearly and queries return results linearly

Support:

Additional definitions from across the web:

• SQL "row" (1, 2)
• SQL "record" (1, 2)
• "record" (1, 2, 3, 4)
• "row" (1, see also 2, 3, 4)
• Row vs Record on StackOverflow (1, 2)

It is notable that the SQL definitions generally follow the English definition.

If you have a definition which you think should be here, please add it to the comments.
I am especially interested in definitions from the SQL standard or the documentation of an implementation.

The quote has been brought up "Rows are not records." Taken out of context this would seem to contradict my previous assertions (and those of many database professionals). But, if you read the whole post(1 Search for the quote) by Joe Celko(aka --CELKO--) it becomes clear that Joe Celko is trying to correct a misconception of an individual which Joe Celko believes arise from the person's "... background in data processing with traditional file systems ...". In short Joe Celko is saying SQL rows do not work the same as records in other systems. Joe Celko is not claiming right/privilege of defining a term, he is trying to clear up an miss understanding brought on by incorrectly applying principles one storage model to another.

Answer 8

To quote C.J. Date's book "An Introduction to Database Systems" "The rows of such a table can be thought of as the records of the file..."

So, for databases, it is Row.