Database Reference
In-Depth Information
Before SQL Server 2005, data and overhead for a single row could not
exceed 8,060 bytes (8K). This was a hard limit that you had to account for
when designing tables. In SQL Server 2005, this limit has been overcome,
in a manner of speaking. Now, if your row exceeds 8,060 bytes, SQL Server
moves one or more of your variable-length columns onto a new page and
leaves a 24-byte pointer in its place. This does not mean that you have an
unlimited row size, nor should you make all your rows bigger than 8,060
bytes. Why not? First, notice that we said SQL Server will move
variable-
length
columns. This means that you are still limited to 8,060 bytes of
fixed-length
columns. Additionally, you are still limited to 8K on your pri-
mary data page for the row. Remember the 24-byte pointer we mentioned?
In theory you are limited to around 335 pointers on the main page. As
ridiculous as a 336-column varchar(8000) table may sound, we have seen
far stranger.
If SQL Server manages all this behind the scenes, why should you
care? Here's why. Although SQL Server moves the variable-length fields to
new pages after you exceed the 8K limit, the result is akin to a fragmented
hard drive. You now have chunks of data that need to be assembled when
accessed, and this adds processing time. As a data modeler you should al-
ways try to keep your rows smaller than the 8K limit for performance rea-
sons. There are a few exceptions to this rule, and we look at them more
closely later in this chapter when we discuss data types. Keep in mind that
there is a lot more complexity in the way SQL Server handles storage and
pages than we cover here, but your data model can't affect the other vari-
ables as much as it can affect table size.
Views
Views
are simply stored T-SQL that uses SELECT statements to display
data from one or more tables. The tables referenced by views are often re-
ferred to as the view's
base tables
.
Views, as the name implies, allow you
to create various pictures of the underlying information. You can reference
as many or as few columns from each base table as you need to make your
views. This capability allows you to slice up data and display only relevant
information.
You access views in almost the same way that you access tables. All the
basic DML statements work against views in the same way they do on tables,
with a few exceptions. If you have a view that references more than one base
table, you can use only INSERT, UPDATE, or DELETE statements that
Search WWH ::
Custom Search