Know Your SQL Objects

Question

Know Your SQL Objects

Viewing 15 posts - 46 through 60 (of 84 total)

You must be logged in to reply to this topic. Login to reply

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 1

WHAAT???

In the daily newsletter, the question specifies to "select 4". Which is what I did. And then I find that the site thinks I am wrong, because both "Extents may be owned by multiple objects" and "Extents may be owned by only one object" are correct.

Again: WHAAT???

I don't have the time right now to read the entire discussion. But my guess is that someone whined that "Extents may be owned by only one object" should be correct because not all extents are mixed. And I would agree if ONLY this statement were present. But there were two statements about the number of owners of an extent, and "Extents may be owned by multiple objects" already clearly indicates that an extent can have one or more owners ("may", not "must"). So in the context of the rest of the question, the statement "Extents may be owned by only one object" can only be interpreted as the opposite of "Extents may be owned by multiple objects", and they can impossibly both be right.

I really don't care about the points (trust me, I've got plenty), but I do care about the informational value of this site. And if the QotD now claims that two mutually exclusive statements are both correct, that informational value goes downhill at an embarrassing pace.

Such a shame. The original question (if my assumption about what changed between the newsletter and now) was great. The current, "corrected" version is nothing but humbug.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 2

Hugo Kornelis (5/13/2011)
Again: WHAAT???
I don't have the time right now to read the entire discussion....

If you don't have time to read the entire discussion please don't go on a rant about what's going on is wrong. You don't have all the information.

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 3

Hugo Kornelis (5/13/2011)
WHAAT???
In the daily newsletter, the question specifies to "select 4". Which is what I did. And then I find that the site thinks I am wrong, because both "Extents may be owned by multiple objects" and "Extents may be owned by only one object" are correct.

Unfortunately I cannot edit the question in the newsletter online, so I can only emphasize that you need to read it online.

In terms of the may, the two statements are not opposite. They are in fact possibilities and both can be true. Extents may be owned by one object (uniform extent) and extents may be owned by multiple objects (mixed extent). Not the same extent, but different ones.

Paul White SSC Guru Points: 150467 More actions · Answer 4

michael.kaufmann (5/13/2011)
But it's not the data type of your fields/columns that's important here--the page type of the ROW_OVERFLOW_DATA allocation unit is text/image.

I very much doubt this was the intention of the question.

(well, only variable fields with a certain size will actually grow beyond the 8 kB limit for a row).

ROW_OVERFLOW isn't a question of a particular column value growing beyond 8060 bytes of storage requirement. When the row as a whole would require more than 8060 bytes, SQL Server can move variable-length data off row to make room.

It is true that the variable-length column that currently uses most in-row storage will be moved first, but that is an implementation detail. To be clear, it is quite possible for quite small variable-length columns to be moved off-row:

CREATE TABLE dbo.Example

(

C CHAR(8000) NOT NULL DEFAULT '',

D CHAR(13) NOT NULL DEFAULT '',

E VARCHAR(37) NOT NULL,

);

Go

INSERT dbo.Example (E) VALUES (REPLICATE('X', 37));

GO

DBCC IND(0, Example, 1);

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Steve Jones - SSC Editor (5/13/2011)
Hugo Kornelis (5/13/2011)
WHAAT???
In the daily newsletter, the question specifies to "select 4". Which is what I did. And then I find that the site thinks I am wrong, because both "Extents may be owned by multiple objects" and "Extents may be owned by only one object" are correct.
Unfortunately I cannot edit the question in the newsletter online, so I can only emphasize that you need to read it online.
In terms of the may, the two statements are not opposite. They are in fact possibilities and both can be true. Extents may be owned by one object (uniform extent) and extents may be owned by multiple objects (mixed extent). Not the same extent, but different ones.

You should really get going on that un-send e-mail function so that you can resend the qotd after a correction like this one ;-).

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 6

Steve Jones - SSC Editor (5/13/2011)
Comments encouraged on the changes.

As mentioned above, the answer for

Text Data is stored on data pages

is "it depends".

Either that Text part of the question should be removed completely, or the suffix "by default" should be added.

Best Regards,

Chris Büttner

Paul White SSC Guru Points: 150467 More actions · Answer 7

Steve Jones - SSC Editor (5/13/2011)
Comments encouraged on the changes.

"Extents may be owned by only one object".

This still reads (to me) as excluding the possibility that an extent may be owned by more than one object 😉

"Rows can span multiple pages (excluding LOB data)"

I didn't read it this way originally, but it's possible to read that as saying that LOB data cannot span multiple pages.

Who'd be a QotD author/SSC editor? :laugh:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul Randal One Orange Chip Points: 29544 More actions · Answer 8

Steve - I got this wrong because your wording is broken. Extents can NOT be owned by multiple objects. When an extent is a mixed extent, it is 'owned' by the allocation system - technically object ID 99. The individual pages are owned by multiple objects, but none of the objects own the extent.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 9

Christian Buettner-167247 (5/13/2011)
As mentioned above, the answer for
Text Data is stored on data pages
is "it depends".
Either that Text part of the question should be removed completely, or the suffix "by default" should be added.

The text data type, which I assumed this is referring to, is LOB and will be stored on a LOB page regardless of length. If this were referring to the English definition of text, as opposed to SQL definition, it would include char and varchar and you would be right.

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 10

Paul Randal (5/13/2011)
Steve - I got this wrong because your wording is broken. Extents can NOT be owned by multiple objects. When an extent is a mixed extent, it is 'owned' by the allocation system - technically object ID 99. The individual pages are owned by multiple objects, but none of the objects own the extent.

Wow, so how does that translate to the real life?

Up until I thought that I was both owned by my wife and my boss. Am I in fact owned by object id 99 as well?

Hm, SELECT object_name(99) ObjectName returns

God

Kind of makes sense...

Omg, TGIF - gotta go 😀

Best Regards,

Chris Büttner

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 11

Paul Randal (5/13/2011)
Steve - I got this wrong because your wording is broken. Extents can NOT be owned by multiple objects. When an extent is a mixed extent, it is 'owned' by the allocation system - technically object ID 99. The individual pages are owned by multiple objects, but none of the objects own the extent.

Thanks for the clarification. Just saw the tweet as well.

I'll correct to say "pages in the extent"

Paul White SSC Guru Points: 150467 More actions · Answer 12

cfradenburg (5/13/2011)
The text data type, which I assumed this is referring to, is LOB and will be stored on a LOB page regardless of length. If this were referring to the English definition of text, as opposed to SQL definition, it would include char and varchar and you would be right.

If the option 'text in row' did not exist, you would be right :laugh:

CREATE TABLE dbo.Example

(

A TEXT NOT NULL

);

EXECUTE sys.sp_tableoption 'dbo.Example', 'text in row', 24;

INSERT dbo.Example (A) VALUES ('Fish');

DBCC IND (0, Example, 1);

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 13

SQLkiwi (5/13/2011)
Steve Jones - SSC Editor (5/13/2011)
Comments encouraged on the changes.
"Extents may be owned by only one object".
This still reads (to me) as excluding the possibility that an extent may be owned by more than one object 😉
"Rows can span multiple pages (excluding LOB data)"
I didn't read it this way originally, but it's possible to read that as saying that LOB data cannot span multiple pages.
Who'd be a QotD author/SSC editor? :laugh:

I can't be responsible for sheep-reading and down under mis-interpreting the (other) colonist's English. May implies it may or may not be true. It does not exclude the possibility that any particular object can be assigned to multiple objects.

SanDroid SSChampion Points: 10068 More actions · Answer 14

Steve Jones - SSC Editor (5/13/2011)
Hugo Kornelis (5/13/2011)
WHAAT???
In the daily newsletter, the question specifies to "select 4". Which is what I did. And then I find that the site thinks I am wrong, because both "Extents may be owned by multiple objects" and "Extents may be owned by only one object" are correct.
Unfortunately I cannot edit the question in the newsletter online, so I can only emphasize that you need to read it online.
In terms of the may, the two statements are not opposite. They are in fact possibilities and both can be true. Extents may be owned by one object (uniform extent) and extents may be owned by multiple objects (mixed extent). Not the same extent, but different ones.

I could not agree more. The QOTD asked if Exents can (or may) be owned by only one object. They can and may do that.

It also asked if Extents Can (or may) they be owned by more than one object. That is also true.

These statements only become exclusive if you explicitly state the type of extent in both statements.

I believe the author wanted to enforce in us the learning that we have two types of Extents that are owned differantly. He has certainly suceeded at that. 😎

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 15

SanDroid (5/13/2011)
Steve Jones - SSC Editor (5/13/2011)
Hugo Kornelis (5/13/2011)
WHAAT???
In the daily newsletter, the question specifies to "select 4". Which is what I did. And then I find that the site thinks I am wrong, because both "Extents may be owned by multiple objects" and "Extents may be owned by only one object" are correct.
Unfortunately I cannot edit the question in the newsletter online, so I can only emphasize that you need to read it online.
In terms of the may, the two statements are not opposite. They are in fact possibilities and both can be true. Extents may be owned by one object (uniform extent) and extents may be owned by multiple objects (mixed extent). Not the same extent, but different ones.
I could not agree more. The QOTD asked if Exents can (or may) be owned by only one object. They can and may do that.
It also asked if Extents Can (or may) they be owned by more than one object. That is also true.
These statements only become exclusive if you explicitly state the type of extent in both statements.
I believe the author wanted to enforce in us the learning that we have two types of Extents that are owned differantly. He has certainly suceeded at that. 😎

Brandie is actually a girl... just saying. 😉