Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
17  
One propels a craft, the other is used by dirty hipsters to play music –  billinkc Jan 11 '13 at 22:28
1  
A related post on SO shows clearly that this is an important question. –  dezso Jan 11 '13 at 22:45
    
Over in SAS country we call them 'observations', or obs for short. –  user3490 Jan 12 '13 at 10:43
3  
To me, a database table has rows and columns - while a (e.g. .NET) object in memory has fields and is a record –  marc_s Jan 12 '13 at 18:26
add comment

7 Answers

up vote 30 down vote accepted

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).

I actually wore this shirt to the PASS conference in Grapevine, Texas, in 2006

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.

share|improve this answer
    
fascinating insights into the debate and its context, but it doesn't answer the OP question. The closest you get is in the Ben-Gan quote, but that still leaves the distinction very nebulous in my mind. –  kmote Jan 16 '13 at 18:30
1  
@kmote that's great, but did you read the last two paragraphs as well? What part of the OP's question do you feel has been ignored? –  Aaron Bertrand Jan 16 '13 at 18:35
1  
the last two paragraphs define a row, but not a record (at least, not precisely), so the distinction is unclear. –  kmote Jan 16 '13 at 18:42
4  
@kmote in the context of SQL Server, IMHO there is no such thing as a record. And outside of SQL Server, the definition really is defined by context. For example you might consider a single row in a DataReader a record, but you might also consider a row combined with its child rows a record. This is why I said feel free to call it a record once your application has a hold of it. Let me state this more explicitly: please don't call it a record when it's inside SQL Server. What you call it in your app(s) and how you further define a record in that context is a question for another site, I think. –  Aaron Bertrand Jan 16 '13 at 18:50
1  
I once asked a question in MS forum many years ago in the wee hours of the morning after working on a problem for a long time and accidentally mentioned the word 'record' and yeah, he jumped on me for that one. Now I have a problem with all of the development team calling it record, just to see me wince. –  user1207758 Jan 17 '13 at 20:31
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
5  
One can argue that the ROW is the logical entity, while the RECORD is the physical entity. A ROW may have multiple records: one in the clustered index, several in the NC indexes. A row that does not fit in page can be split between the record in the page and overflow records in the SLOB storage. A BLOB value of a field of a row can span several TEXT records in the BLOB storage. A row in a heap can consist of a stub record and a forward record. Etc etc. –  Remus Rusanu Jan 13 '13 at 9:55
    
Then you would have completely removed the usage of the word record from the app domain, or completely muddied the waters and have taken us away from definable terms and into the realm of theoretical database design and implementation details. You raise good points, but from the convention of apps vs databases, which is where 80% of our readers will be comfortable, my answer still stands, I hold. –  jcolebrand Jan 13 '13 at 19:43
1  
I'm not contradicting your answer, maybe I wordsmith-ed wrong. I only presented how things are seen by the SQL Engine itself, where the physical access layer calls them 'records' vs. the query processing (language) layer which handles 'rows'. –  Remus Rusanu Jan 13 '13 at 20:39
    
I see. I still feel like it muddies the waters. No offense taken, I assure you. –  jcolebrand Jan 13 '13 at 21:48
add comment

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.

share|improve this answer
4  
I applaud the effort into this, but I feel like you only argued one side of the argument. The only "sql row" references you pulled were not from database vendor websites, they were from third party websites that had nothing to do with database authorship. (granted, one guy wrote a book about dbs, but therein lies a sleek difference, yeah?) –  jcolebrand Jan 11 '13 at 23:32
4  
So let me get this straight - when a bunch of people tell you that many experts consider a row and record to be different concepts, you crap all over it and refuse to acknowledge it because it didn't come from an official source (even though one such quote came from an MS Press book written by the one and only Itzik Ben-Gan). Yet your evidence here consists of two excerpts from random junk on tech target? Double standard much? –  Aaron Bertrand Jan 12 '13 at 3:52
4  
"It appears that Oracles implementation of SQL includes a special type called a record." this is highly misleading: PL/SQL is not SQL. Yes a PL/SQL record is"... similar to a struct type in C, C++, or Java."—that's because PL/SQL is similar to C, C++, or Java: it is a procedural programming language. –  Jack Douglas Jan 12 '13 at 9:44
4  
@Aaron +1 for the "random junk on tech target". I have seen no link to a database related website (and yes, I followed them all). And the link from SearchOracle only says that a row is sometimes called a record. That doesn't make it correct. –  ypercube Jan 12 '13 at 11:32
5  
"A row is a record stored linearly." Really? How? In Oracle, at least, a row can span multiple blocks which can reside on different disks. What's linear about that? Utter nonsense. –  FreshPhilOfSO Jan 13 '13 at 0:38
show 3 more comments

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!

share|improve this answer
3  
Excellent research. Thanks @Phil! –  swasheck Jan 13 '13 at 2:17
3  
...and @ypercube! –  Aaron Bertrand Jan 13 '13 at 2:35
add comment

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.

share|improve this answer
    
@jcolebrand : Yes, in Relation Modeling Table as called Relation. The Book I read is Fundamentals Of Database Systems Elmasri Navathe from Pearson-Education –  Grijesh Chauhan Jan 13 '13 at 6:11
    
I stand corrected >.< –  jcolebrand Jan 13 '13 at 7:25
1  
@swasheck What theoretical difference I can understand in Relational Algebra table are set. Where in SQL tables are not set and We have to add DISTINCT keyword explicitly. Where in Relational Algebra (set theory) no two element can be same in a set. –  Grijesh Chauhan Jan 13 '13 at 14:49
add comment

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.

share|improve this answer
1  
I know you were waiting for it: it's Occam's razor :-P. –  Marian Jan 17 '13 at 11:52
    
@Marian - sure, thanks! –  AlexKuznetsov Jan 17 '13 at 14:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.