Home

Indexes
 

Indexes Fundamentals
 

Introduction

If you take a look at the last pages of a non-fictional book (such as a book about history, economics, mathematics, sociology, or statistics, etc), you may find a series of pages that start in a section label Index. The words in that series allow you to locate a section of the book that mentions, explains, or describes the word and related topics. An index in a book makes it easy and fast to get to a section of a book that deals with a particular topic.

Like a book, a table or a view can use the mechanism provided by an index. In a table or a view, an index is a column (or many columns) that can be used to locate records and take a specific action based on some rule reinforced on that (those) column(s).

Practical LearningPractical Learning: Introducing Indexes
  1. Start Microsoft SQL Server and login to open the Microsoft SQL Server Management Studio
  2. To create a new database, in the Object Explorer, right-click Databases and click New Database...
  3. Set the name to CollinsUniversity and click OK
  4. In the Object Explorer, expand CollinsUniversity
  5. Right->click Tables and click New Table...
  6. Click the fields as follows:
     
    Column Name Data Type Allow Nulls
    StudentNumber int Unchecked
    FirstName nvarchar(20) Checked
    MI nchar(1) Checked
    LastName nvarchar(20) Unchecked
    Username nvarchar(20) Unchecked
    Password nvarchar(20) Checked
  7. Close the table
  8. When asked whether you want to save it, click Yes
  9. Set the name to StudentRegistration
  10. Click OK
  11. On the Standard toolbar, click the New Query button New Query
  12. To create a new database and a table, type the following:
      
    IF EXISTS(SELECT name FROM sys.databases
	  WHERE name = N'CeilInn3')
DROP DATABASE CeilInn3;
GO
CREATE DATABASE CeilInn3;
GO

USE CeilInn3;
GO

IF OBJECT_ID('Rooms', 'U') IS NOT NULL
  DROP TABLE Rooms
GO

CREATE TABLE Rooms
(
    RoomID int identity(1, 1) primary key not null,
    RoomNumber nvarchar(10),
    LocationCode nchar(10) default N'Silver Spring',
    RoomType nvarchar(20) default N'Bedroom',
    BedType nvarchar(40) default N'Queen',
    Rate money default 75.85,
    Available bit default 0
);
GO
  13. Press F5 to execute
  14. On the Standard toolbar, click the Save button
  15. Set the name as CeilInn3
  16. Click OK

Visually Creating an Index

The database engine is already equipped with a default mechanism to automatically make up an index on a table depending on how the table is created. For example, if you create a primary key on a table, the database engine automatically creates an index for the column(s) used as the primary key. Otherwise, you can still explicitly create an index. You can create an index visually or using SQL code.

To visually create an index, you can use the Indexes/Keys dialog box. To display it, in the Object Explorer, expand the database that holds the table or view and expand the Tables node. Right-click the table or view for which you want to create the index and click Design. In the window, right-click Indexes/Keys... This would open the Indexes/Keys dialog box:

Indexes/Keys

To create an index, click the Add button. The first piece of information you should provide is the name. Normally, the database engine provides a default name. If you want to change it, click (Name) and type the desired name. The other very important piece of information you should provide is at least one column. By default, the database engine selects the first column of the table. If this is not the column you want to use, click Columns and click its ellipsis button Ellipsis. This would open the Index dialog box:

Index Columns

From the boxes under Column Name, you can select each column. Once you are ready, click OK.

Practical LearningPractical Learning: Visually Creating an Index
  1. In the Object Explorer, under the Tables node of CollinsUniversity, right-click StudentRegistration and click Design
  2. Right-click anywhere in the white area and click Indexes/Keys...
  3. Click Add
  4. Click Columns and click its ellipsis button
  5. Click the combo box of the default StudentNumber and select LastName
  6. Click the box under LastName
  7. Click the arrow of its combo box and select Username
  8. Click OK
  9. Click (Name) and type IX_Credentials
  10. Click Close
  11. Close the table
  12. When asked whether you want to save, click Yes

Creating an Index With SQL

To create an index in SQL, the basic formula to follow is:

CREATE INDEX IndexName ON Table/View(Column(s))

Alternatively, open a Query window. Then, in the Templates Explorer, expand the Index node. Drag Create Index Basis (or another sub-node) and drop it in the window. Skeleton code would be generated for you:

-- =============================================
-- Create index basic template
-- =============================================
USE <database_name, sysname, AdventureWorks>
GO

CREATE INDEX <index_name, sysname, ind_test>
ON <schema_name, sysname, Person>.<table_name, sysname, Address> 
(
	<column_name1, sysname, PostalCode>
)
GO

The creation on an index starts with the CREATE INDEX expression, followed by a name for the index, followed by the ON keyword. In the Table/View placeholder, enter the name of the table or view for which you want to create the index, followed by parentheses in which you enter at least one column. Here is an example:

-- =============================================
-- Database: Exercise
-- =============================================
USE master
GO

-- Drop the database if it already exists
IF  EXISTS (
	SELECT name 
		FROM sys.databases 
		WHERE name = N'Exercise'
)
DROP DATABASE Exercise
GO

CREATE DATABASE Exercise
GO

USE Exercise;
GO

-- =============================================
-- Database: Exercise
-- Table;    Employees
-- =============================================
CREATE TABLE Employees
(
	EmployeeNumber int NOT NULL,
	LastName nvarchar(20) NOT NULL,
	FirstName nvarchar(20),
	Username nchar(8) NOT NULL,
	DateHired date NULL,
	HourlySalary money
);
GO

INSERT INTO Employees
VALUES(62480, N'James', N'Haans', N'jhaans', N'1998-10-25', 28.02),
      (35844, N'Gertrude', N'Monay', N'gmonay', N'2006-06-22', 14.36),
      (24904, N'Philomène', N'Guillon', N'pguillon', N'2001-10-16', 18.05),
      (48049, N'Eddie', N'Monsoon', N'emonsoon', N'08/10/2009',   26.22),
      (25805, N'Peter', N'Mukoko', N'pmukoko', N'03-10-2004', 22.48),
      (58405, N'Chritian', N'Allen', N'callen', N'06/16/1995', 16.45);
GO

CREATE INDEX IX_Employees
ON Employees(EmployeeNumber);
GO

If the index will include more than one column, list them separated by commas. Here is an example:

CREATE INDEX IX_Employees
ON Employees(LastName, Username);
GO

Index Maintenance
 

Introduction

In Microsoft SQL Server (and most database systems), an index is treated as an object. That is, an index can be checked or deleted at will.

Deleting an Index

If you don't need an index anymore, you can delete it. You can do this visually or manually.

To visually delete an index, open its table in Design view. Right-click somewhere in the table window and click Indexes/Views. In the left frame, click the name of the index to select it, and click the Delete button. You will not receive a warning. Then click Close. If you want to change your mind and keep the index, don't save the table.

The basic syntax to delete an index in Transact-SQL is:

DROP INDEX IndexName ON TableName;

In this formula, replace the TableName with the name of the table that contains the index. Replace the IndexName with the name of the index you want to get rid of.

Here is an example:

USE Exercise;
GO
DROP INDEX IX_Employees ON Employees;
GO

Checking the Existence of an Indexes

Before performing an operation on an index, you may want to check first that it exists. For example, if you try creating an index and giving it a name that exists already, you would receive an error. The following code

USE Exercise;
GO

CREATE INDEX IX_Employees
ON Employees(EmployeeNumber);
GO

would produce:

Msg 1913, Level 16, State 1, Line 2
The operation failed because an index or statistics 
with name 'IX_Employees' already exists on table 'Employees'.

To visually check the existence of an index, open the table or view in design view, right-click the middle of the window and click Indexes/Keys. The list of indexes should appear on the left side. Here is an example:

To assist you with checking the existence of an index, Transact-SQL provides the following formula:

IF EXISTS (SELECT name FROM sys.indexes
WHERE name = IndexName)
Do something here

The primary thing you need to provide in this formula is the name of the index. Once you have checked, you can take the necessary action. Here is an example:

USE Exercise;
GO

IF EXISTS (SELECT name FROM sys.indexes
		  WHERE name = N'IX_Employees')
DROP INDEX IX_Employees
ON Employees
GO
CREATE INDEX IX_Employees
ON Employees(EmployeeNumber);
GO
 		 
 

 

 

The Types of Indexes
 

Introduction

Microsoft SQL Server supports various types of indexes. The two broadest categories are clustered and non-clustered.

Clustered Indexes

In our introduction, we saw that an index is primarily created using one or more columns from a designated table. This means that, when it comes to using the index, we would use the values stored in the column(s) that was (were) selected for the index. Such an index is referred to as clustered. The columns that were made part of an index are referred to as keys.

To visually create a clustered index, display the Indexes/Keys dialog box. In the dialog box, when creating a new indexed or after clicking the name of an existing index, in the right list, click Create As Clustered and select Yes:

Clustered Index

Once you are ready, click Close.

To create a clustered index in SQL, use the following formula:

CREATE CLUSTERED INDEX IndexName ON Table/View(Column(s))

From the description we gave previously, the only new keyword here is CLUSTERED. Based on this, here is an example:

-- =============================================
-- Database: Exercise
-- =============================================
USE master
GO

-- Drop the database if it already exists
IF  EXISTS(SELECT name 
	   FROM sys.databases 
	   WHERE name = N'Exercise'
)
DROP DATABASE Exercise
GO

CREATE DATABASE Exercise
GO

USE Exercise;
GO

-- =============================================
-- Database: Exercise
-- Table;    Employees
-- =============================================
CREATE TABLE Employees
(
	EmployeeNumber int NOT NULL,
	LastName nvarchar(20) NOT NULL,
	FirstName nvarchar(20),
	Username nchar(8) NOT NULL,
	DateHired date NULL,
	HourlySalary money
);
GO

INSERT INTO Employees
VALUES(62480, N'James', N'Haans', N'jhaans', N'1998-10-25', 28.02),
      (35844, N'Gertrude', N'Monay', N'gmonay', N'2006-06-22', 14.36),
      (24904, N'Philomène', N'Guillon', N'pguillon', N'2001-10-16', 18.05),
      (48049, N'Eddie', N'Monsoon', N'emonsoon', N'08/10/2009',   26.22),
      (25805, N'Peter', N'Mukoko', N'pmukoko', N'03-10-2004', 22.48),
      (58405, N'Chritian', N'Allen', N'callen', N'06/16/1995', 16.45);
GO

CREATE CLUSTERED INDEX IX_Employees
ON Employees(LastName);
GO

A table that contains a clustered index is called a clustered table.

There are various aspects to a clustered index:

  • To make it easy to search the records, they (the records) are sorted. This makes it possible for the database engine to proceed in a top-down approach and quickly get to the desired record
  • Without this being a requirement, each record should be unique (we have already seen how to take care of this, using check constraints; later on, we will see again how to create unique records)
  • There must be only one clustered index per table. This means that, if you (decide to) create a clustered index on a table, the table becomes equipped with one. If you create another clustered index, the previous one (clustered index) is deleted

Non-clustered Index

While a clustered index uses a sorted list of records of a table or view, another type of index can use a mechanism not based on the sorted records but on a bookmark. This is called a non-clustered index. As opposed to a clustered table that can contain only one clustered index, you can create not only one, but as many as 249 non-clustered indexes.

To visually create a non-clustered index, display the Indexes/Keys dialog box. To create a new index, click the Add button. If an index was always created or set as clustered and you want to change it, you can change its Create As Clustered property from Yes to No.

To create a non-clustered index in SQL, use the following formula:

CREATE NONCLUSTERED INDEX IndexName ON Table/View(Column(s))

The new keyword in this formula is NONCLUSTERED. Everything is the same as previously described. Based on this, here is an example:

-- =============================================
-- Database: Exercise
-- =============================================
USE master
GO

-- Drop the database if it already exists
IF  EXISTS(SELECT name 
	   FROM sys.databases 
	   WHERE name = N'Exercise'
)
DROP DATABASE Exercise
GO

CREATE DATABASE Exercise
GO

USE Exercise;
GO

-- =============================================
-- Database: Exercise
-- Table;    Employees
-- =============================================
CREATE TABLE Employees
(
	EmployeeNumber int NOT NULL,
	LastName nvarchar(20) NOT NULL,
	FirstName nvarchar(20),
	Username nchar(8) NOT NULL,
	DateHired date NULL,
	HourlySalary money
);
GO

INSERT INTO Employees
VALUES(62480, N'James', N'Haans', N'jhaans', N'1998-10-25', 28.02),
      (35844, N'Gertrude', N'Monay', N'gmonay', N'2006-06-22', 14.36),
      (24904, N'Philomène', N'Guillon', N'pguillon', N'2001-10-16', 18.05),
      (48049, N'Eddie', N'Monsoon', N'emonsoon', N'08/10/2009',   26.22),
      (25805, N'Peter', N'Mukoko', N'pmukoko', N'03-10-2004', 22.48),
      (58405, N'Chritian', N'Allen', N'callen', N'06/16/1995', 16.45);
GO

CREATE NONCLUSTERED INDEX IX_Employees
ON Employees(LastName, FirstName);
GO

If you create an index without specifying CLUSTERED or NONCLUSTERED, the database engine automatically makes it non-clustered.

Practical LearningPractical Learning: Creating a Non-Clustered Index With SQL
  1. In the CeilInn3 window, to create an index using Transact-SQL, press Ctrl + A to select the whole text type the following:
      
    USE CeilInn3;
GO

IF EXISTS(SELECT name FROM sys.indexes
	  WHERE name = N'IX_RoomsIdentities')
DROP INDEX IX_RoomsIdentities
ON Rooms
CREATE NONCLUSTERED INDEX IX_RoomsIdentities
ON Rooms(RoomNumber, LocationCode);
GO
  2. Press F5 to execute

Indexes and Table Creation

In Lesson 15, we saw how to create a primary key on a table. Here is an example:

USE Exercise;
GO

CREATE TABLE Students
(
	StudentID int PRIMARY KEY,
	FirstName nvarchar(50) NOT NULL,
	LastName nvarchar(50));
GO

When you do this, the database engine automatically creates an index on the table and chooses the primary key column as its key. You have the option of indicating the type of index you want created. To do this, on the right side of the name of the column, enter CLUSTERED or NONCLUSTERED. If you don't specify the type of index, the CLUSTERED option is applied.

Practical LearningPractical Learning: Creating a Clustered Tables
  1. In the CeilInn3 tab, to complete the database, Press Ctrl + A and type the following:
      
    USE CeilInn3;
GO

CREATE TABLE Customers (
    CustomerID int identity(1, 1) primary key CLUSTERED NOT NULL,
    AccountNumber nchar(10) UNIQUE,
    FullName nvarchar(50) NOT NULL,
    PhoneNumber nvarchar(20),
    EmailAddress nvarchar(50),
    CONSTRAINT CK_CustomerContact
	CHECK ((PhoneNumber IS NOT NULL) OR (EmailAddress IS NOT NULL))
);
GO
  2. Press F5 to execute

Data Entry and Analysis With Indexes
 

Introduction

In our introduction, we saw that an index can make it possible to take some action during data entry, such as making sure that a column have unique values for each record or making sure that the combination of values of a group of columns on the same record produces a unique value. Besides this characteristic of indexes, they are actually very valuable when it comes to data analysis.

As mentioned for a book, the primary goal of an index is to make it easy to locate the records of a table or view.

Practical LearningPractical Learning: Entering Data
  1. Replace the content of the CeilInn3 window with the following:
      
    USE CeilInn3;
GO

INSERT INTO Rooms(RoomNumber, LocationCode) VALUES(104, N'SLSP');
GO

INSERT INTO Rooms(RoomNumber, LocationCode,
 BedType, Rate, Available)
           VALUES(105, N'SLSP', N'King', 85.75, 1),
		 (106, N'SLSP', N'King', 85.75, 1)
GO

INSERT INTO Rooms(RoomNumber, LocationCode, Available)
	   VALUES(107, N'SLSP', 1)
GO

INSERT INTO Rooms(RoomNumber, LocationCode, BedType, Rate)
	   VALUES(108, N'SLSP', N'King', 85.75)
GO

INSERT INTO Rooms(RoomNumber, LocationCode, Available)
	   VALUES(109, N'SLSP', 1)
GO

INSERT INTO Rooms(RoomNumber, LocationCode, RoomType, Rate, Available)
	   VALUES(110, N'SLSP', N'Conference', 450.00, 1)
GO
  2. Press F5 to execute

Introduction to Index Uniqueness

An index is made valuable in two ways. On one hand, the records should be sorted. A clustered index itself takes care of this aspect because it automatically and internally sorts its records. What if the records are not unique? For example, in a bad data entry on a list of employees, you may have two or more employees with the same employee's records. If you create an index for such a table, the database engine would create duplicate records on the index. This is usually not good because when it comes time to select records, you may have too many records and take a wrong action.

When creating a table, you can create index for it and let the index apply a rule that states that each record would be unique. To take care of this, you can apply a uniqueness rule on the index.

If you are visually creating an index, in the Indexes/Keys dialog box, select the index on the left side. On the right list, set the Is Unique field to Yes. On the other hand, if you want to remove this rule, set the Is Unique field to No.

To create a uniqueness index in SQL, apply the UNIQUE keyword in the formula:

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name ON Table/View(Column(s))

Start with the CREATE UNIQUE expression, then specify whether it would be clustered or not. The rest follows the descriptions we saw previously. Here is an example:

-- =============================================
-- Database: Exercise
-- =============================================
USE master
GO

-- Drop the database if it already exists
IF  EXISTS (
	SELECT name 
		FROM sys.databases 
		WHERE name = N'Exercise'
)
DROP DATABASE Exercise
GO

CREATE DATABASE Exercise
GO

USE Exercise;
GO

-- =============================================
-- Database: Exercise
-- Table;    Employees
-- =============================================
CREATE TABLE Employees
(
	EmployeeNumber int NOT NULL,
	LastName nvarchar(20) NOT NULL,
	FirstName nvarchar(20),
	Username nchar(8) NOT NULL,
	DateHired date NULL,
	HourlySalary money
);
GO

CREATE UNIQUE CLUSTERED INDEX IX_Employees
ON Employees(EmployeeNumber);
GO

Practical LearningPractical Learning: Using Index Uniqueness
  1. In the CeilInn3 tab, press Ctrl + F5 and type the following:
      
    USE CeilInn3;
GO

INSERT INTO Customers(AccountNumber, FullName,
                      PhoneNumber, EmailAddress)
VALUES(N'395805', N'Ann Zeke', N'301-128-3506', N'azeke@yahoo.jp'),
      (N'628475', N'Peter Dokta', N'(202) 050-1629', 
          N'pdorka1900@hotmail.com'),
      (N'860042', N'Joan Summs', N'410-114-6820', 
	  N'jsummons@emailcity.net'),
      (N'228648', N'James Roberts',
      N'(301) 097-9374', N'jroberts13579@gmail.net')
GO
  2. Press F5 to execute

Unique Indexes and Data Entry

Once you have specified the uniqueness of an index on a table, during data entry, if the user enters a value that exists in the table already, an error would be produced. Here is an example:

USE Exercise;
GO
INSERT INTO Employees(EmployeeNumber, FirstName, 
		      LastName, HourlySalary)
VALUES(92935, N'Joan', N'Hamilton', 22.50)
GO

INSERT INTO Employees(EmployeeNumber, FirstName, 
		      LastName, HourlySalary)
VALUES(22940, N'Peter', N'Malley', 14.25)
GO

INSERT INTO Employees(EmployeeNumber, FirstName, 
		      LastName, HourlySalary)
VALUES(27495, N'Christine', N'Fink', 32.05)
GO
INSERT INTO Employees(EmployeeNumber, FirstName, 
		      LastName, HourlySalary)
VALUES(22940, N'Gertrude', N'Monay', 15.55)
GO
INSERT INTO Employees(EmployeeNumber, FirstName, 
		      LastName, HourlySalary)
VALUES(20285, N'Helene', N'Mukoko', 26.65)
GO

This would produce: 

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in 
object 'dbo.Employees' with unique index 'IX_Employees'.
The statement has been terminated.

(1 row(s) affected)

Table and Index Partitioning
 

Introduction

Data in your database may involve many records, in thousands or millions, so much that at one time, it may become difficult to manage. One way you can deal with this is to store the records of a table in different file groups. This makes it possible to store one section of records in one file group, another section in another file group, possibly another section in another file group, and so on. As a result, when it comes time to look for one or a few records among thousands or millions of records, it would be easier to locate it or to locate them. Of course, the data still belongs to one database and to the same table.

Practical LearningPractical Learning: Introducing Partitioning

  1. Open a file utility, such as Windows Explorer
  2. Display the contents of the drives
  3. On the C: drive, create a folder named Real Estate Main Repository
  4. If you have another partition or another drive such as D:, create a folder on it and name it Real Estate Secondary Repository. Then, in the code below, replace the indicated drive of Real Estate Secondary Repository to that drive
    If you don't have another drive, create another folder on the C: drive and name it Real Estate Secondary Repository
  5. If you have one more partition or another drive such as E:, create a folder on it and name it Real Estate Third Repository. Then, in the code below, replace the indicated drive of Real Estate Third Repository to that drive
    If you don't have another drive, on the C: drive, create another folder Real Estate Third Repository
  6. Check each of those folders and notice that they are empty
  7. Return to Microsoft SQL Server Management Studio
  8. To open a new Query window, on the Standard toolbar, click the New Query button New Query
  9. To create a database and the accompanying file groups, type the following:
      
    USE master;
GO

CREATE DATABASE RealEstate3
ON PRIMARY
  ( NAME = N'RealEstatePrimary',
    FILENAME = N'C:\Real Estate Main Repository\RealEstateMain.mdf',
    SIZE = 4MB,
    MAXSIZE = 10MB,
    FILEGROWTH = 1MB),
FILEGROUP RealEstateGroupRecords1
  ( NAME = N'RealEstateRecords1',
    FILENAME = N'C:\Real Estate Main Repository\RealEstateFirst.ndf',
    SIZE = 1MB,
    MAXSIZE = 10MB,
    FILEGROWTH = 1MB),
FILEGROUP RealEstateGroupRecords2
  ( NAME = N'RealEstateRecords2',
    FILENAME = N'C:\Real Estate Secondary Repository\RealEstateSecond.ndf',
    SIZE = 1MB,
    MAXSIZE = 10MB,
    FILEGROWTH = 1MB),
FILEGROUP RealEstateGroupRecords3
  ( NAME = N'RealEstateRecords3',
    FILENAME = N'C:\Real Estate Third Repository\RealEstateThird.ndf',
    SIZE = 1MB,
    MAXSIZE = 10MB,
    FILEGROWTH = 1MB)
LOG ON
  ( NAME = N'RealEstate3Log',
    FILENAME = N'C:\Real Estate Main Repository\RealEstateLogger.ldf',
    SIZE = 1MB,
    MAXSIZE = 10MB,
    FILEGROWTH = 1MB);
GO
  10. Press F5 to execute
  11. Return to the file utilities such as Windows Explorer and check the content of each of the previously created folders. Also check their sizes
  12. Return to Microsoft SQL Server Management Studio

Partitioning a Table

Before partitioning a table, you must create the necessary file groups. This can be done when creating the database since it is at that time that you specify how the database will be stored; that is, what files will hold the information of the database.

After creating the database and creating its file groups. Before partitioning a table, you must create a partition function and a partition scheme.

A Partition Function

A partition function is used to define the ranges of records that will be stored in what file group. The SQL formula to create a partition function is:

CREATE PARTITION FUNCTION PartitionFunctionName ( ParameterType )
AS RANGE [ LEFT | RIGHT ] 
FOR VALUES (StartRange1, StartRange2, StartRange_n)

To use from a template, open a Query window. In the Templates Explorer, expand the Partition Function node. Drag Create Partition Function and drop it in the Query window. Skeleton code will be generated for you:

-- =====================================
-- Create Partition Function template
-- =====================================
USE <database_name, sysname, AdventureWorks>
GO

CREATE PARTITION FUNCTION <partition_function_name, sysname, myRangePF> ( <data_type_name, sysname, int> )
AS RANGE LEFT FOR VALUES (<data_value1,,1>, <data_value2,,100>, <data_value3,,1000>);

-- Partition function on a partitioning column col1 would be partitioned as follows:
-- Partition 1: col1 less than or equal to <data_value1,,1>
-- Partition 2: col1 greater than <data_value1,,1> AND col1 less than or equal to <data_value2,,100>
-- Partition 3: col1 greater than <data_value2,,100> AND col1 less than or equal to <data_value3,,1000>
-- Partition 4: col1 greater than <data_value3,,1000>

The creation of a partition function starts with the CREATE PARTITION FUNCTION expression followed by a name. The name follows the rules for names in Microsoft SQL Server. Because you are creating a function, the name is followed by parentheses.

In the parentheses of the function, you must specify the data type of the column that will be used to create a range of records. The values of that column will be used to distinguish ranges of records. This means that the values of this column must allow the database engine to predict a range of records. This is called the partitioning column. For example, you can use a column that has an incremental count of values. This is the case for an identity primary key column. As another example, you can use a column that holds a category of values, such as female customers vs male and child customers. As one more example, you can use a column that holds dates so that you can isolate ranges of records from one date to another.

After closing the parenthesis, type AS RANGE, which indicates that you are going to specify the ranges of values. This is followed by either LEFT or RIGHT. When the partition function will have been created and when the table itself will have been created, when the database engine is asked to look for a record or a range of records, it may have to sort the records. If you want it to sort the records from left to right, use the LEFT keyword. If you want the records sorted from right to left, use the RIGHT keyword.

The AS RANGE LEFT or AS RANGE RIGHT expression is followed by FOR VALUES that is followed by parentheses.

When creating a partition function, you must provide a way for the database engine to get a range of records. For example, you can use records from number 1 to number 1000, then another range from 1001 to 5000, and so on. Or you can specify that a range of records would go from February 11th, 2000 to June 26th, 2005. Then another range would go from June 26th 2005 to December 14th, 2006, and so on.

You specify the range in the parentheses that follow the FOR VALUES expression. Type the first value of the first range, followed by a comma, followed by the first value of the second range, and so on.

Practical LearningPractical Learning: Creating a Partition Function
  1. Select the whole contents of the Query window and type the following:
      
    USE RealEstate3;
GO

CREATE PARTITION FUNCTION RealEstateSegmentation(int)
AS RANGE LEFT FOR VALUES(1, 10);
GO
  2. Press F5 to execute

A Partition Scheme

A partition scheme specifies the names of the file groups, in their order that will store the ranges of records that were created in the partition function. The formula to create a partition scheme is:

CREATE PARTITION SCHEME PartitionSchemeName
AS PARTITION PartitionFunctionName
[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )

You start with the CREATION PARTITION SCHEME expression do indication your intention. This is followed by a name. The name follows the rules of objects.

After the name of the partition scheme, type AS PARTITION followed by the name of the partition function you should have previously created.

If you are planning to use only one file group, after the name of the partition function, enter ALL, followed by parentheses, in which you will type PRIMARY.

If you are planning to use different file groups, after the name of the partition function, enter TO, followed by parentheses. We saw that, in the parentheses of the FOR VALUES of the partition function, you entered the starting value of the first range. In the parentheses of the TO keyword, type the name of the file group that will hold the records of the first range of the partition function. We also saw how to specify the second range in the partition function. In the parentheses of the TO clause, after the name of the first file group, type a comma followed by the name of the file group that will hold the records of the second range.

Practical LearningPractical Learning: Creating a Partition Function Scheme
  1. Select the whole contents of the Query window and type the following:
      
    USE RealEstate3;
GO

CREATE PARTITION SCHEME RealEstateDistributionScheme
AS PARTITION RealEstateSegmentation
TO (RealEstateGroupRecords1, RealEstateGroupRecords2, RealEstateGroupRecords3);
GO
  2. On the SQL Editor toolbar, click the Execute button

Partitioning a Table

After creating the partition scheme, you can create the table. The formula to specify a partition scheme when creating a table is:

CREATE TABLE What We Have Learned So Far
(
    What We Have Learned So Far
) ON PartitionSchemeName(ColumnName)

You start with the CREATE TABLE expression, followed by things we have learned so far: an optional schema and a required name. After the name of the table, you open and close the parentheses, in which you include other things we have seen so far: the columns, the constraints, and their options.

Outside the parentheses, type the ON keyword, followed by the name of the partition scheme you will have created, followed by an opening and a closing parentheses. Inside the parentheses of the schema name, enter the name of the table's column that is the partitioning column.

After creating the table, you can use it, like any normal table.

Practical LearningPractical Learning: Partitioning a Table
  1. Select the whole contents of the Query window and type the following:
      
    -- =============================================
-- Author:   	 FunctionX
-- Database: 	 RealEstate3
-- Date Created: Tuesday July 28th, 2009
-- =============================================

CREATE DATABASE RealEstate3;
GO

-- =============================================
-- Author:   	 FunctionX
-- Database: 	 RealEstate3
-- Table:    	 PropertyTypes
-- Date Created: Tuesday July 28th, 2009
-- =============================================
USE RealEstate3;
GO
CREATE TABLE PropertyTypes
(
  PropertyTypeID int identity(1,1) NOT NULL,
  PropertyType varchar(20),
  CONSTRAINT PK_PropertyTypes PRIMARY KEY(PropertyTypeID)
) ON RealEstateDistributionScheme(PropertyTypeID);
GO
INSERT INTO PropertyTypes(PropertyType)
VALUES(N'Condominium');
GO
INSERT INTO PropertyTypes(PropertyType)
VALUES(N'Single Family');
GO
INSERT INTO PropertyTypes(PropertyType)
VALUES(N'Townhouse');
GO
INSERT INTO PropertyTypes(PropertyType)
VALUES(N'Unknown');
GO

-- =============================================
-- Author:   	 FunctionX
-- Database: 	 RealEstate3
-- Table:    	 Conditions
-- Date Created: Tuesday July 28th, 2009
-- =============================================
USE RealEstate3;
GO
CREATE TABLE Conditions
(
  ConditionID int identity(1,1) NOT NULL,
  Condition varchar(20),
  CONSTRAINT PK_Conditions PRIMARY KEY(ConditionID)
) ON RealEstateDistributionScheme(ConditionID);
GO
INSERT INTO Conditions(Condition)
VALUES(N'Excellent');
GO
INSERT INTO Conditions(Condition)
VALUES(N'Good');
GO
INSERT INTO Conditions(Condition)
VALUES(N'Bad Shape');
GO
INSERT INTO Conditions(Condition)
VALUES(N'Mostly Damaged');
GO

-- =============================================
-- Author:   FunctionX
-- Database: RealEstate3
-- Table:    Properties
-- =============================================
CREATE TABLE Properties
(
  PropertyID int identity(1,1) NOT NULL,
  PropertyNumber char(6),
  Address varchar(100),
  City varchar(50),
  State char(2),
  ZIPCode varchar(12),
  PropertyTypeID int
	CONSTRAINT FK_PropertyTypes
	FOREIGN KEY REFERENCES PropertyTypes(PropertyTypeID),
  ConditionID int
	CONSTRAINT FK_Conditions
	FOREIGN KEY REFERENCES Conditions(ConditionID),
  Bedrooms smallint,
  Bathrooms float,
  FinishedBasement bit,
  IndoorGarage bit,
  Stories smallint,
  YearBuilt smallint,
  MarketValue money,
  CONSTRAINT PK_Properties PRIMARY KEY(PropertyID)
) ON RealEstateDistributionScheme(PropertyID);
GO

INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'524880', N'1640 Lombardo Ave', N'Silver Spring', N'MD',
  N'20904', 2, 2, 4, 2.5, 3, 1, 3, 1995, 495880.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'688364', N'10315 North Hacht Rd', N'College Park', N'MD',
       N'20747', 2, 1, 4, 3.5, 3,
       1, 2, 2000, 620724.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, FinishedBasement,
  Stories, MarketValue)
VALUES(N'611464', N'6366 Lolita Drive', N'Laurel', N'MD',
       N'20707', 2, 2, 1, 2, 422625.00);
GO
INSERT INTO Properties(Address, City, PropertyTypeID,
  Bedrooms, MarketValue)
VALUES(N'9002 Palasko Hwy', N'Tysons Corner',
       1, 2, 422895.00);
GO
INSERT INTO Properties(PropertyNumber, State,
  ZIPCode, Bedrooms, YearBuilt, MarketValue)
VALUES(N'420115', N'DC',
       N'20011', 2, 1982, 312555);
GO     
INSERT INTO Properties(PropertyNumber, City, ZIPCode,
 PropertyTypeID, Bedrooms, YearBuilt, MarketValue)
VALUES(N'917203', N'Alexandria', N'22024',
       2, 3, 1965, 345660.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  PropertyTypeID, ConditionID, Bedrooms, Bathrooms, MarketValue)
VALUES(N'200417', N'4140 Holisto Crt', N'Germantown', N'MD',
       1, 1, 2, 1, 215495.00);
GO
INSERT INTO Properties(City, State, PropertyTypeID, ConditionID,
  Bedrooms, Bathrooms,  YearBuilt, MarketValue)
VALUES(N'Rockville', N'MD', 1, 2, 2, 2, 1996, 436885.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'927474', N'9522 Lockwood Rd', N'Chevy Chase', N'MD',
       N'20852', 3, 3, 3, 2.5, 3, 0, 3,
       1992, 415665.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'207850', N'14250 Parkdoll Rd', N'Rockville', N'MD',
       N'20854', 3, 2, 3, 2.5, 2, 1, 2,
       1988, 325995.00);
GO    
INSERT INTO Properties(City, PropertyTypeID, Bedrooms,
  YearBuilt, MarketValue)
VALUES(N'Washington', 3, 4, 1975, 366775.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  YearBuilt, MarketValue)
VALUES(N'288540', N'10340 Helmes Street #408', N'Silver Spring', N'MD',
       N'20906', 1, 2, 1, 1, 2000, 242775.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'247472', N'1008 Coppen Street', N'Silver Spring', N'MD',
       N'20906', 2, 1,
       3, 3, 3, 1, 3, 1996, 625450.00);
GO
INSERT INTO Properties(City, ZIPCode, PropertyTypeID, 
   Stories, YearBuilt, MarketValue)
VALUES(N'Chevy Chase', N'20956', 2, 
       3, 2001, 525450.00);
GO
INSERT INTO Properties(Address, City, State,
  PropertyTypeID, ConditionID, Bedrooms, MarketValue)
VALUES(N'686 Herod Ave #D04', N'Takoma Park', N'MD',
       1, 1, 2, 360885.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'297446', N'14005 Sniders Blvd', N'Laurel', N'MD',
       N'20707', 3, 4,
       4, 1.5, 3, 1, 2, 2002, 412885.00);
GO
INSERT INTO Properties(City, ZIPCode, ConditionID, Bedrooms,
  Stories, YearBuilt)
VALUES(N'Silver Spring', N'20905', 2,
       4, 2, 1965);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'924792', N'680 Prushia Rd', N'Washington', N'DC',
       N'20008', 2, 2,
       5, 3.5, 3, 0, 3, 2000, 555885.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'294796', N'14688 Parrison Street', N'College Park', N'MD',
       N'20742', 2, 1,
       5, 2.5, 2, 1, 2, 1995, 485995.00);
GO
INSERT INTO Properties(City, State, PropertyTypeID, ConditionID,
  Bedrooms, Bathrooms,  YearBuilt, MarketValue)
VALUES(N'Rockville', N'MD', 1, 2, 1, 1, 1996, 418885.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  YearBuilt, MarketValue)
VALUES(N'811155', N'10340 Helmes Street #1012', N'Silver Spring',
       'MD', N'20906', 1, 2,
       1, 1, 2000, 252775.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'447597', N'4201 Vilamar Ave', N'Hyattsville', N'MD',
       N'20782', 3, 1,
       3, 2, 2, 1, 3, 1992, 365880.00);
GO
INSERT INTO Properties(Address, ZIPCode, Bathrooms)
VALUES(N'1622 Rombard Str', 20904, 2.5);
GO
INSERT INTO Properties(City, State, PropertyTypeID, ConditionID,
  Bedrooms, Bathrooms,  YearBuilt, MarketValue)
VALUES(N'Rockville', N'MD', 1, 2, 1, 1, 1996, 420555.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'297415', N'980 Phorwick Street', N'Washington', N'DC',
       N'20004', 2, 2,
       4, 3.5, 3, 3, 1, 2004, 735475.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'475974', N'9015 Marvin Crow Ave', N'Gaithersburg', N'MD',
       N'20872', 2, 4,
       4, 2.5, 3, 1, 1, 1965, 615775.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'836642', N'3016 Feldman Court', N'Rockville', N'MD',
       N'20954', 2, 3,
       5, 3, 3, 1, 3, 1960, 528555.00);
GO
INSERT INTO Properties(Address, City, ZIPCode, PropertyTypeID,
  Bedrooms, Bathrooms, YearBuilt, MarketValue)
VALUES(N'2444 Arielson Rd', N'Rockville', N'20854', 
	1, 2, 1, 1996, 475555.00);
GO

INSERT INTO Properties(City, State, PropertyTypeID, Stories)
VALUES(N'Rockville', N'MD',
       3, 1);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'208304', N'7307 Everett Hwy', N'Washington', N'DC',
       N'20012', 3, 1,
       2, 2.5, 2, 0, 4, 2006, 420550.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms,
  Bathrooms, YearBuilt, MarketValue)
VALUES(N'644114', N'10340 Helmes Street#1006', N'Silver Spring',
       'MD', N'20906', 1, 2,
       2, 2, 2000, 258445.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'937966', N'7303 Warfield Court', N'Tysons Corner', N'VA',
       '22131', 2, 2,
       3, 2.5, 3, 1, 4, 2006, 825775.00);
GO
INSERT INTO Properties(City, ZIPCode, ConditionID, Bedrooms, 
  Stories, YearBuilt)
VALUES(N'Fairfax', N'22232', 2, 3, 3, 1985);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'297497', N'12401 Conniard Ave', N'Takoma Park', N'MD',
       N'20910', 3, 2,
       3, 2.5, 3, 1, 3, 2004, 280775.00);
GO
INSERT INTO Properties(PropertyNumber, City, ZIPCode,
  PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  YearBuilt, Stories, MarketValue)
VALUES(N'855255', N'Laurel', N'20707', 2,
       4, 3, 2, 1962, 2, 342805.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'469750', N'6124 Falk Rd', N'Arlington', N'VA',
       '22031', 2, 4,
       4, 3.5, 3, 1, 1, 1982, 635995.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'826927', N'5121 Riehl Ace', N'Fairfax', N'VA',
       '22232', 3, 1,
       3, 1.5, 2, 0, 1, 2002, 325620.00);
GO
INSERT INTO Properties(City, ZIPCode, PropertyTypeID, Bedrooms, 
  Bathrooms, MarketValue)
VALUES(N'Silver Spring', N'20906', 1, 2, 2, 335655.00);
GO

INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'287064 ', N'9533 Pensulian Rd', N'Silver Spring', N'MD',
       N'20904', 2, 3,
       3, 1.5, 3, 1, 2, 1992, 485775.00);
GO
INSERT INTO Properties(PropertyNumber, City, ZIPCode,
  PropertyTypeID, ConditionID, Bedrooms, YearBuilt, Stories)
VALUES(N'724001 ', N'705 Helios Ave', N'20004',
       3, 3, 3, 1974, 4);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'209275', N'944 Fryer Ave', N'Chevy Chase', N'MD',
       N'20852', 2, 1,
       5, 2.5, 3, 0, 2, 2002, 625665.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'204759', N'1950 Galego Street', N'Germantown', N'MD',
       N'20874', 2, 1,
       4, 3.5, 2, 1, 4, 2007, 428665.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'937259', N'12366 Fowler Ave', N'Alexandria', N'VA',
       '22031', 3, 2,
       3, 1.5, 3, 1, 3, 2007, 402815.00);
GO
  2. On the SQL Editor toolbar, click the Execute button
 
 
   
 
Previous Copyright © 2009 FunctionX, Inc. Next