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.

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

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.

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

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

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

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)

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.

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

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 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.

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 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.

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

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.

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 , the Show Criteria Pane button , and the Show SQL Pane button
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
     
    
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
     
    
17. Close the Query window
18. Close Microsoft SQL Server

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 ] ] ) 
      [ ; ]

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