Database Reference
In-Depth Information
As you see, SQL Server needs to perform about 70 times more reads while scanning
dbo.LargeRows
data, which leads
to the longer execution time.
You can improve the performance of the system by reducing the size of the data rows. One of the ways to do this
is by using appropriate data types when you create tables. For example:
•
Use
bit
instead of
tinyint
,
smallint
, or
int
to store Boolean values. The
bit
data type uses
one byte of the storage space per eight columns.
•
Use the appropriate date/time data type based on the precision you need. For example, an
order entry system can use
smalldatetime
(4 bytes of storage space) or
datetime2(0)
(6 bytes
of storage space) rather than
datetime
(8 bytes of storage space) to store when an order has
been placed into the system when one-minute or one-second precision is enough.
•
Use
decimal
or
real
rather than
float
whenever possible.
•
Use
money
or
smallmoney
data types rather than
float
to store money values.
Do not use large fixed-length char/binary data types unless the data is always populated and
•
static in size.
As an example, let's look at Table
1-4
, which shows two different designs for a table that collects GPS location
information.
Table 1-4.
Table that collects GPS location information
create table dbo.Locations
(
ATime datetime not null, -- 8 bytes
Latitude float not null, -- 8 bytes
Longitude float not null, -- 8 bytes
IsGps int not null, -- 4 bytes
IsStopped int not null, -- 4 bytes
NumberOfSatellites int not null, -- 4 bytes
)
create table dbo.Locations2
(
ATime datetime2(0) not null, -- 6 bytes
Latitude decimal(9,6) not null, -- 5 bytes
Longitude decimal(9,6) not null, -- 5 bytes
IsGps bit not null, -- 1 byte
IsStopped bit not null, -- 0 bytes
NumberOfSatellites tinyint not null, -- 1
byte
)
Total: 36 bytes
Total: 18 bytes
Table
dbo.Locations2
uses 18 bytes less storage space per data row. This does not appear particularly impressive
in the scope of a single row, however it quickly adds up. If s system collects 1,000,000 locations daily, 18 bytes per row
produces about 18MB of space savings per day and 6.11GB per year. In addition to the database space, it affects the
buffer pool memory usage, backup file size, network bandwidth, and a few other things.
At the same time, you need to be careful with such an approach and not be too cheap. For example, choosing
smallint
as the data type for the
CustomerId
column is not a wise step. Even though 32,768 (or even 65,536)
customers look good enough when you just start the development of a new system, the cost of code refactoring and
changing the data type from
smallint
to
int
could be very high in the future.
Table Alteration
Let's look at what happens when you are altering a table. There are three different ways that SQL Server can proceed:
1.
Alteration requires changing the metadata only. Examples of such an alteration include
dropping a column, changing a not nullable column to a nullable one, or adding a nullable
column to the table.
