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

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.

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.

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!