What is a page in SQL Server and do I need to worry?

I've come across the page in SQL Server 2008, and is somewhat confused about it. I'm currently reading MCTS Self-Paced Training Kit (Exam 70-433): Microsoft SQL Server 2008-Database Development, where authors discuss the concept, but in a limited way.

From MSDN "Understanding Pages and Extents" I get an answer which doesn't really help. The web-page describes the size (8kb) of a page and how rows are stored in the page, and how columns will be moved automatically (by SQL Server) if the rows doesn't fit in the page.

But still, I wonder if the page-level is something I should pay attention to designing a database with ER-diagrams, tables and data types? Or, should I simply rely on that SQL Server handles pages automatically and in the best way possibl开发者_如何学JAVAe?

Thanx for listening!

YES ! A page is the most basic element of storage in SQL Server.

Of the 8192 bytes on a page, approx. 8060 are available to you as a user. If you can manage to fit your data rows onto the page nicely, they'll take up a lot less storage.

If your data row e.g. is 4100 bytes long, only a single row will be stored on a page (and the rest of the page - 3960 bytes - is wasted space). The important point is: those pages aren't just relevant on disk, but also in SQL Server main memory --> you want to try to avoid large areas of space that cannot hold any useful information on a page.

If you can manage to reduce your row to take up 4000 bytes, then suddenly you can store two rows on a page, and thus significantly reduce the overhead of wasted space (down to 60 bytes per page).

You do not need to worry about 8 KB page structure until you get in to performance issues. But in case you want to know internals of 8 KB page , here is a video of youtube http://www.youtube.com/watch?v=He8MRttysmY which demonstrates how 8 KB pages looks like.

1.Page header stores information about the page like page type, next and previous page if it’s an index page, free space in the page etc.

2.After the page header data row section follows. This is where you data is actually stored.

3.Row offset information is stored at the end of the page i.e. after the data row section. Every data row has a row offset and the size of row offset is 2 bytes per row. Row offset stores information about how far the row is from the start of the page.

Putting in simple words the complete page equation comes as shown below.

Page (8 KB/8192 bytes) = Page header (96 bytes) + Actual data (Whatever bytes) + Row offset (2 bytes per row).