Is it Possible to Query Rows by Page ID?

Question

Is it Possible to Query Rows by Page ID?

Gabriel P

SSCarpal Tunnel

Points: 4289
More actions
November 30, 2016 at 5:08 pm

#337815

Hey, does anyone know if there's some undocumented feature that would allow you to query a table by FileID/PageID?
Something like this:
SELECT *
FROM dbo.ThisTable
WHERE $FID = @FileID
AND $PID = @PageID
Closest I can find is DBCC PAGE but that data is unstructured.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Joe Celko SSCertifiable Points: 5749 More actions · Answer 1

Why? One of the major concepts in RDBMS and SQL is to get away from any physical storage. :w00t:

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Gabriel P (11/30/2016)
Closest I can find is DBCC PAGE but that data is unstructured.

Yup, that's how you would do it.

One of the dump types decodes all the rows. It's not meant to be a primary way of querying data.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

CELKO (11/30/2016)
Why? One of the major concepts in RDBMS and SQL is to get away from any physical storage.

Database corruption is the primary reason that I've seen for this.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

BLOB EATER SSChampion Points: 14609 More actions · Answer 4

BLOB EATER

SSChampion

Points: 14609

December 1, 2016 at 2:50 am

#1915339

sys.fn_PhysLocFormatter ??

BLOB EATER SSChampion Points: 14609 More actions · Answer 5

ignore the above - I don't think you use that as a SELECT statement and WHERE clauses.

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 6

BLOB EATER (12/1/2016)
ignore the above - I don't think you use that as a SELECT statement and WHERE clauses.

Should be able to.

I just ran this to find everything in a certain page in my calendar table:

SELECT file_page_slot=sys.fn_PhysLocFormatter(%%physloc%%),

*

FROM calendar

WHERE sys.fn_PhysLocFormatter(%%physloc%%) LIKE '(1:292044:%'

It seems nicer to use sys.fn_PhysLocCracker:

SELECT fplc.file_id,

fplc.page_id,

fplc.slot_id,

c.*

FROM calendar c

CROSS APPLY

sys.fn_PhysLocCracker(%%physloc%%) fplc

WHERE fplc.file_id=1

AND

fplc.page_id=292044;

It's a lot slower, though.

Cheers!

Eric M Russell SSC Guru Points: 125522 More actions · Answer 7

Gabriel P (11/30/2016)
Hey, does anyone know if there's some undocumented feature that would allow you to query a table by FileID/PageID?
Something like this:
SELECT *
FROM dbo.ThisTable
WHERE $FID = @FileID
AND $PID = @PageID
Closest I can find is DBCC PAGE but that data is unstructured.

Explain the outcome of what you need, not how you want to do it.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho