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 the computer and log in with an account that has administrative rights
  2. Launch Microsoft SQL Server
  3. In the Authentication combo box, select Windows Authentication
  4. Click Connect
  5. To create a new database, in the Object Explorer, right-click Databases and click New Database...
  6. Set the name to DepartmentStore2
  7. Click OK
  8. In the Object Explorer, right-click Databases and click Refresh
  9. Expand DepartmentStore2
  10. Right->click Tables and click New Table...
  11. Click the fields as follows:
     
    Column Name Data Type Allow Nulls
    EmployeeNumber int Unchecked
    FirstName nvarchar(20) Checked
    MI nchar(1) Checked
    LastName nvarchar(20) Unchecked
    Username nvarchar(20) Unchecked
    Password nvarchar(20) Checked
  12. Close the table
  13. When asked whether you want to save it, click Yes
  14. Set the name to Employees
  15. Click OK
  16. On the Standard toolbar, click the New Query button New Query
  17. To create a new database and a table, type the following:
    IF EXISTS(SELECT name FROM sys.databases
    	  WHERE name = N'HotelManagement1')
    DROP DATABASE HotelManagement1;
    GO
    CREATE DATABASE HotelManagement1;
    GO
    
    USE HotelManagement1;
    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
  18. Press F5 to execute

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 DepartmentStore2, right-click Tables and click Refresh
  2. Expand the Tables node. Right-click dbo.Employees and click Design
  3. Right-click anywhere in the white area and click Indexes/Keys...
  4. Click Add
  5. Click Columns and click its ellipsis button
  6. Click the combo box of the default EmployeeNumber and select LastName
  7. Click the box under LastName
  8. Click the arrow of its combo box and select Username
     
    Index Columns
  9. Click OK
  10. Click (Name) and type IX_Credentials
  11. Click Close
  12. Close the table
  13. 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 Index

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 Indexes

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. Click in the top section of the Query window,  press Ctrl + A to select the whole text
  2. To create an index using Transact-SQL, type the following:
    USE HotelManagement1;
    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
  3. Press F5 to execute
 
 
 

Indexes and Table Creation

We already know 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 create a primary key, 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. Click the top section of the Query window and press Ctrl + A
  2. To complete the database, type the following:
    USE HotelManagement1;
    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
  3. 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. Click the top area of the Query window and press Ctrl + A
  2. Type the following:
    USE HotelManagement1;
    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
  3. 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. Click the top area of the Query window and press Ctrl + A
  2. Type the following:
    USE HotelManagement1;
    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
  3. 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 FunDS2 Main Repository
  4. If you have another partition or another drive such as D:, create a folder on it and name it FunDS2 Secondary Repository. Then, in the code below, replace the indicated drive of FunDS2 Secondary Repository to that drive
    If you don't have another drive, create another folder on the C: drive and name it FunDS2 Secondary Repository
  5. If you have one more partition or another drive such as E:, create a folder on it and name it FunDS2 Third Repository. Then, in the code below, replace the indicated drive of FunDS2 Third Repository to that drive
    If you don't have another drive, on the C: drive, create another folder FunDS2 Third Repository
  6. Check each of those folders and notice that they are empty
  7. Return to Microsoft SQL Server Management Studio
  8. Click inside the top area of the Query window and press Ctrl + A
  9. To create a database and the accompanying file groups, type the following (this is the FunDS2a.sql (FunDS2a.txt) file):
    USE master;
    GO
    
    -- =============================================
    -- Database: FunDS2
    -- Author:   FunctionX
    -- Date:     Sunday 06 February 2011 - 18:08
    -- =============================================
    USE master
    GO
    
    -- Drop the database if it already exists
    IF  EXISTS (
    	SELECT name 
    		FROM sys.databases 
    		WHERE name = N'FunD2'
    )
    DROP DATABASE FunDS2;
    
    CREATE DATABASE FunDS2
    ON PRIMARY
      ( NAME = N'FunDS2Primary',
        FILENAME = N'C:\FunDS2 Main Repository\FunDS2Main.mdf',
        SIZE = 4MB,
        MAXSIZE = 10MB,
        FILEGROWTH = 1MB),
    FILEGROUP FunDS2GroupRecords1
      ( NAME = N'FunDS2Records1',
        FILENAME = N'C:\FunDS2 Main Repository\FunDS2First.ndf',
        SIZE = 1MB,
        MAXSIZE = 10MB,
        FILEGROWTH = 1MB),
    FILEGROUP FunDS2GroupRecords2
      ( NAME = N'FunDS2Records2',
        FILENAME = N'C:\FunDS2 Secondary Repository\FunDS2Second.ndf',
        SIZE = 1MB,
        MAXSIZE = 10MB,
        FILEGROWTH = 1MB),
    FILEGROUP FunDS2GroupRecords3
      ( NAME = N'FunDS2Records3',
        FILENAME = N'C:\FunDS2 Third Repository\FunDS2Third.ndf',
        SIZE = 1MB,
        MAXSIZE = 10MB,
        FILEGROWTH = 1MB)
    LOG ON
      ( NAME = N'FunDS23Log',
        FILENAME = N'C:\FunDS2 Main Repository\FunDS2Logger.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. Click the top section of the Query window and press Ctrl + A
  2. Type the following:
    USE FunDS2;
    GO
    
    CREATE PARTITION FUNCTION FunDS2Segmentation(int)
    AS RANGE LEFT FOR VALUES(1, 10);
    GO
  3. 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. Click the top section of the Query window and press Ctrl + A
  2. Type the following:
    USE FunDS2;
    GO
    
    CREATE PARTITION SCHEME FunDS2DistributionScheme
    AS PARTITION FunDS2Segmentation
    TO (FunDS2GroupRecords1, FunDS2GroupRecords2, FunDS2GroupRecords3);
    GO
  3. 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. Click the top section of the Query window and press Ctrl + A
  2. Type the following (the red parts simply indicates something that is new to us; otherwise, you should type everything) (this is the FunDS2b.sql file (FunDS2b.txt)):
    USE FunDS2;
    GO
    
    CREATE SCHEMA Inventory;
    GO
    CREATE FUNCTION Inventory.SetDateEntered(@days int)
    RETURNS Date
    AS
    BEGIN
        RETURN DATEADD(d, @days, SYSDATETIME());
    END
    GO
    CREATE TABLE Inventory.Manufacturers
    (
        ManufacturerID int identity(1, 1) not null,
        Name nvarchar(40),
        OtherName1 nvarchar(40),
        OtherName2 nvarchar(40),
        CONSTRAINT PK_Manufacturers PRIMARY KEY(ManufacturerID)
    ) ON FunDS2DistributionScheme(ManufacturerID);
    GO
    CREATE TABLE Inventory.Categories
    (
        CategoryID int identity(1, 1) not null,
        Category nvarchar(40),
        CONSTRAINT PK_Categories PRIMARY KEY(CategoryID)
    ) ON FunDS2DistributionScheme(CategoryID);
    GO
    CREATE TABLE Inventory.SubCategories
    (
        SubCategoryID int identity(1, 1) not null,
        SubCategory nvarchar(40),
        CONSTRAINT PK_SubCategories PRIMARY KEY(SubCategoryID)
    ) ON FunDS2DistributionScheme(SubCategoryID);
    GO
    -- =======================================================
    -- Table:	    StoreItems
    -- Description:	This table holds an inventory of all the
    --				merchandise sold in the store
    -- =======================================================
    CREATE TABLE Inventory.StoreItems
    (
    	ItemNumber int unique,
    	DateEntered date,
    	ManufacturerID int null Constraint FK_Manufacturers
    		References Inventory.Manufacturers(ManufacturerID),
    	CategoryID int null Constraint FK_Categories
    		References Inventory.Categories(CategoryID),
    	SubCategoryID int null Constraint FK_SubCategories
    		References Inventory.SubCategories(SubCategoryID),
    	ItemName nvarchar(80) null,
    	Size nvarchar(32),
    	UnitPrice money,
    	DiscountRate decimal(6, 2),
        Constraint PK_StoreItems Primary Key(ItemNumber)
    ) ON FunDS2DistributionScheme(ItemNumber);
    GO
  3. On the SQL Editor toolbar, click the Execute button
  4. Open the FunDS2c.sql file (FunDS2c.txt)
  5. To execute it, right-click inside the document and click Execute
  6. In the Object Explorer, right-click FunDS2 and click Refresh. Expand FunDS2
  7. Expand its Tables node
  8. Right-click Inventory.StoreItems and click Edit Top 200 Rows
  9. On the Query Designer toolbar, click the Show Diagram Pane button Show Diagram, the Show Criteria Pane button Show Criteria Pane, and the Show SQL Pane button Show SQL Pane
  10. In the Criteria pane, click Column and press Delete
  11. In the SQL pane, delete Top (200)
  12. In the Diagram pane, click the check boxes of ItemNumber, DateEntered, Name, Category, SubCategory, ItemName, Size, UnitPrice, DiscountRate
     
  13. To see the result, on the main menu, click Query Designer -> Execute SQL
     
    Indexes
  14. To see women's items, in the Criteria pane, click the box at the intersection of Category and Filter
  15. Type women
  16. To see the result, on the main menu, click Query Designer -> Execute SQL
     
    Indexes
  17. Close the Query window
  18. Close Microsoft SQL Server

Exercises

 

Lesson Summary Questions

  1. Consider the following table:
    CREATE TABLE Students
    (
    	StudentNumber nchar(8),
    	[Full Name] nvarchar(50),
    );
    GO
    Which one of the following codes will create an index?
    1. CREATE INDEX SomeIndex ON COLUMN StudentNumber FROM Students;
      GO
    2. CREATE INDEX SomeIndex FROM Students ON COLUMN StudentNumber;
      GO
    3. FROM Students CREATE INDEX SomeIndex ON StudentNumber;
      GO
    4. ON StudentNumber CREATE INDEX SomeIndex FROM Students;
      GO
    5. CREATE INDEX SomeIndex ON Students(StudentNumber);
      GO
  2. What's the basic formula to create an index?
    1. CREATE OBJECT::INDEX IndexName ON Table/View(Column(s))
    2. CREATE IndexName ON Table/View(Column(s)) AS INDEX
    3. CREATE INDEX IndexName ON Table/View(Column(s))
    4. WITH INDEX CREATE IndexName ON Table/View(Column(s))
    5. CREATE IndexName ON Table/View(Column(s)) = INDEX
  3. What is the syntax of creating a partition function?
    1. CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )
      AS RANGE [ LEFT | RIGHT ] 
      FOR VALUES ( [ boundary_value [ ,...n ] ] ) 
      [ ; ]
    2. CREATE OBJECT::partition_function_name ( input_parameter_type )
      AS RANGE [ LEFT | RIGHT ] 
      FOR VALUES ( [ boundary_value [ ,...n ] ] ) 
      [ ; ] AS PARTITION FUNCTION
    3. CREATE FUNCTION partition_function_name ( input_parameter_type )
      AS RANGE [ LEFT | RIGHT ] 
      FOR VALUES ( [ boundary_value [ ,...n ] ] ) 
      IN PARTITION PartitionName[ ; ]
    4. CREATE PARTITION partition_function_name ( input_parameter_type )
      WITH RANGE [ LEFT | RIGHT ] 
      FOR VALUES ( [ boundary_value [ ,...n ] ] ) 
      [ ; ] AS FUNCTION
    5. CREATE PARTITION FUNCTION AS partition_function_name ( input_parameter_type )
      FOR RANGE [ LEFT | RIGHT ] 
      SET VALUES = ( [ boundary_value [ ,...n ] ] ) 
      [ ; ]

Answers

  1. Answers
    1. Wrong Answer: The COLUMN keyword is not used in the formula to create an index
    2. Wrong Answer: The FROM keyword is not used to specify the table of an index
    3. Wrong Answer: That whole formula is wrong
    4. Wrong Answer: The creation of an index starts with CREATE INDEX
    5. Right Answer: That code will create an index
  2. Answers
    1. Wrong Answer: You don't need OBJECT:: to create an index
    2. Wrong Answer: An index is created with the CREATE INDEX expression, not CREATE ... AS INDEX
    3. Right Answer
    4. Wrong Answer
    5. Wrong Answer
  3. Answers
    1. Right Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
 
 
   
 

Previous Copyright © 2009-2011 FunctionX.com Next