Tuning the Schema - Professional SQL Server 2005 Performance Tuning - page 227

Example
With all that in mind let's look an example using the Operator table from the normalization section.
Suppose you want to split this table on the LicenseID column. You also want four partitions.

1.

Create four file groups called fgOperator1, fgOperator2, fgOperator3, fgOperator4. Again,
see BOL for syntax details.

2.

Create the partition function. CREATE PARTITION FUNCTION pfLicenseID (int) AS RANGE
LEFT FOR VALUES (1, 2, 3) . This will set up a partition as shown in Table 8-6.

Table 8-6: Partition Scheme

Partition 1 2 3 4

Value Col < =1 Col > 1andCol < =3 Col > 3andCol < =4 Col > 4

3.

Create the partition scheme. CREATE PARTITION SCHEME psLicenseID AS PARTITION
pfLicenseID TO (fgOperator1, fgOperator2, fgOperator3, fgOperator4).
This results in rows being redirected into their respective file groups based on the range.

4.

Finally, create the Operator table:

CREATE TABLE OPERATOR(OperatorID int,
Name varchar(20),
Callsign char(6),
LicenseID int,
ExaminerID int,
RegionID int) ON psLicense(LicenseID)

Once created, SQL Server will transparently redirect operations on the table to the appropriate partition.

Concurrency and Locking (andBlocking)
At this point most of the information concerning table design has been covered. However, we're going
to shift focus a bit to discuss the utilization of those the tables. Concurrency is simply the notion that
the same group of data is accessed simultaneously. However, that access has to be managed in such
a way that undesired effects on the data don't occur. That management is handled by SQL Server's
lock manager.

This is a complex and arduous topic. BOL covers these issues in great detail, and we recommend you
constantly have to return to that for reference.

TransactionRefresher
Before describing concurrency and locking, a brief description of transactions is warranted. Simply stated,
transactions define the beginning and ending of a unit of work. Several statements can comprise a unit
of work. The beginning of the unit of work is created by a BEGIN TRANSACTION SQL statement, and
the unit of work ends with either a ROLLBACK or COMMIT TRANSACTION statement.