SQL 2005, SP3

castellansolutions
castellansolutions used Ask the Experts™
on
Hey Guys:

I am having a hard time understanding the following differences: ROWS, CElls.

My understading if a rows is this: any collection related data that crosses multiple columns. so for ex:

FirstName MiddleName LastName
john NULL Adams

In my understanding that would mean that a row is entire line that contans that users info, however each "cell" can contain upto 8k characters if i am using ansi, char type (8000). but then....

VARCHAR is an abbreviation for variable-length character string. It's a string of text characters that can be as large as the page size for the database table holding the column in question.

The size for a table page is 8,196 bytes, and no one row in a table can be more than 8,060 characters. This in turn limits the maximum size of a VARCHAR to 8,000 bytes.

The above definition to me indicates that the entire row (meaning all entries or cells) can only contain a total of 8k characters, i know that's not true but am confused on wht the author said that.

here is the link:

http://searchsqlserver.techtarget.com/tip/...01_mem1,00.html

Thanks, - i know this is a dumb question but i am only on page 119 of my sql book which is all that i know so far about sql.

Robert
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> I am having a hard time understanding the following differences: ROWS, CElls.

As per your example, This entire row "john NULL Adams" is referred to as ROWS..
And the individual values "john","NULL","Adams" are referred to as CELLs

>> The size for a table page is 8,196 bytes, and no one row in a table can be more than 8,060 characters. This in turn limits the maximum size of a VARCHAR to 8,000 bytes.

Yes this statement was correct and the explanation below:

* A table can have a maximum record length of 8060 bytes..
* Eg 1: You have two columns in your table like

a varchar(20)
b varchar(8000)

then this would work because it is well below the overall limit of 8060 bytes

* Eg 2: If you have the same set of columns but with different string length like this

a varchar(200)
b varchar(8000)

then this exceeds your 8060 byte limitation..
Even though a varchar column can store 8000 bytes, in this case you can't have 8000 bytes stored in b or 200 bytes in a since you have a combined limit for the total record size..

Since you are a beginner, Request you to create some tables with some columns of variable sizes and check it out..
Kindly revert in case of any doubts..

Author

Commented:
how can a whole row contain only 8k characters? that makes now sense at all? then why do you have nvarchar(max) which can contain 2GB's of data?
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Ok.. Got your confusion..
Max Row length of 8060 is applicable till SQL Server 2000.
And Starting from 2005, this is rectified or removed out through the introduction of new datatypes varchar(max), nvarchar(max) and all..

By the time SQL Server 2000 was there, having record length of 8000 itself is a big one since many things like xml, html and images are not stored in the databases at all..

Hope this clarifies..
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
Ok. And that is on a record by record basis correct? so to be clear that means that i could have 5000 records on the same table and they could have varying sizes up to the max limit of the column data types ? correct?

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Yes.. 8060 is the maximum limit for a single record length in SQL Server 2000..
And you can have several records in a table with varying sizes upto the maximum limit of record length..

And you can have 5000 or 5 Billion records in a table too ( no issues over there except that you need more disk space for your database).

Hope this clarifies.

Author

Commented:
That does it and thanks so much i was losing it over this confusion.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Welcome..
And glad to help you out..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial