Full Text Presentation Script

ACTIVATE FILESTREAM
ON THE SERVER, RIGHT CLICK AND GO TO PROPERTIES, THEN ADVANCED, MAKE
FILESTREAM FULLY ADVANCED.
ALSO OPEN SQL SERVER CONFIGURATION MANAGER, GO TO THE PROPERTIES FOR
THE SERVER, THEN THE FILESTREAM TAB AND ENABLE FILESTREAM.
CH 4 EX. 1 TO CREATE NEEDED DATABASES

CREATE DATABASE SQL2008SBS ON PRIMARY
( NAME = N'SQL2008SBS', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SQL2008SBS.mdf' , SIZE = 3MB
, MAXSIZE = UNLIMITED, FILEGROWTH = 10% ),
FILEGROUP FG1 DEFAULT ( NAME = N'SQL2008SBSFG1_Dat1', FILENAME =
N'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SQL2008SBS_1.ndf' , SIZE =
2MB , MAXSIZE = UNLIMITED, FILEGROWTH = 2MB),
(NAME = N'SQL2008SBSFG1_Dat2', FILENAME =
N'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SQL2008SBS_2.ndf' , SIZE =
2MB , MAXSIZE = UNLIMITED, FILEGROWTH = 2MB),
(NAME = N'SQL2008SBSFG1_Dat3', FILENAME =
N'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SQL2008SBS_3.ndf' , SIZE =
2MB , MAXSIZE = UNLIMITED, FILEGROWTH = 2MB)
LOG ON
( NAME = N'SQL2008SBS_Log', FILENAME = N'C:\Program Files\Microsoft
SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SQL2008SBS.ldf' , SIZE =
2MB , MAXSIZE = UNLIMITED , FILEGROWTH = 10MB )
GO
--done
CREATE DATABASE SQL2008SBSFS ON PRIMARY
( NAME = N'SQL2008SBSFS', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SQL2008SBSFS.mdf' , SIZE =
3MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10% ),
FILEGROUP DocumentFileStreamGroup CONTAINS FILESTREAM
( NAME = N'FileStreamDocuments', FILENAME = N'C:\Program
Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SQL2008SBSFS' )
LOG ON
( NAME = N'SQL2008SBSFS_Log', FILENAME = N'C:\Program Files\Microsoft
SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SQL2008SBSFS.ldf' , SIZE
= 2MB , MAXSIZE = UNLIMITED , FILEGROWTH = 10MB )
GO
--done

ALTER DATABASE SQL2008SBS
ADD FILEGROUP FullTextCatalog
GO
--done
ALTER DATABASE SQL2008SBSFS
ADD FILEGROUP FullTextCatalog
GO
--done
ALTER DATABASE AdventureWorks
ADD FILEGROUP FullTextCatalog
GO
--done
ALTER DATABASE SQL2008SBS
ADD FILE (NAME = N'SQL2008SBSFT', FILENAME = N'C:\Program
Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SQL2008SBSFT.ndf')
TO FILEGROUP FullTextCatalog
GO
--done
ALTER DATABASE SQL2008SBSFS
ADD FILE (NAME = N'SQL2008SBSFSFT', FILENAME = N'C:\Program
Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SQL2008SBSFSFT.ndf')
TO FILEGROUP FullTextCatalog
GO
--done
ALTER DATABASE AdventureWorks
ADD FILE (NAME = N'AdventureWorksFT', FILENAME = N'C:\Program
Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorksFT.ndf')
TO FILEGROUP FullTextCatalog
GO
--done

USE SQL2008SBS
GO
CREATE FULLTEXT CATALOG ProductDescription
ON FILEGROUP FullTextCatalog
GO
--done

USE SQL2008SBSFS
GO
CREATE FULLTEXT CATALOG ProductDocument
ON FILEGROUP FullTextCatalog
GO
--done

USE AdventureWorks
GO
CREATE FULLTEXT CATALOG Description
ON FILEGROUP FullTextCatalog
GO
--done (had to add)

/* RUN CH.5 scripts from book */

USE SQL2008SBS
GO
Select * from Products.Product
CREATE FULLTEXT INDEX ON Products.Product(ProductDescription)
KEY INDEX pk_product
ON ProductDescription
WITH CHANGE_TRACKING = AUTO
GO
--done

USE SQL2008SBSFS
GO
CREATE FULLTEXT INDEX ON Products.ProductDocument
(Document TYPE COLUMN DocumentType)
KEY INDEX pk_productdocument
ON ProductDocument
WITH CHANGE_TRACKING = AUTO
GO
--done
USE AdventureWorks
GO
Select * from Production.ProductDescription
CREATE FULLTEXT INDEX ON Production.ProductDescription(Description)
KEY INDEX PK_ProductDescription_ProductDescriptionID
on [Description]
WITH CHANGE_TRACKING = AUTO
GO

--Freetext
SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE FREETEXT(Description,N'safety component')
GO
SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE FREETEXT(Description,N'bike')
GO

SELECT a.ProductDescriptionID, a.Description, b.*
FROM Production.ProductDescription a
INNER JOIN FREETEXTTABLE(Production.ProductDescription, Description,
N'bike') b ON a.ProductDescriptionID = b.[Key]
ORDER BY b.[Rank]
GO

--Contains
SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE CONTAINS(Description,N'bike')
GO

SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE CONTAINS(Description,N'"bike*"')
GO

SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE CONTAINS(Description,N' FORMSOF (INFLECTIONAL,ride) ')
GO

SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE CONTAINS(Description,N' FORMSOF (THESAURUS,metal) ')
GO

SELECT a.ProductDescriptionID, a.Description, b.*
FROM Production.ProductDescription a INNER JOIN
CONTAINSTABLE(Production.ProductDescription, Description,
N'bike NEAR performance') b ON a.ProductDescriptionID = b.[Key]
ORDER BY b.[Rank]
GO

SELECT a.ProductDescriptionID, a.Description, b.*
FROM Production.ProductDescription a INNER JOIN
CONTAINSTABLE(Production.ProductDescription, Description,
N'ISABOUT (performance WEIGHT (.8), comfortable WEIGHT (.6),
smooth WEIGHT (.2) , safe WEIGHT (.5), competition WEIGHT (.5))', 10)
b ON a.ProductDescriptionID = b.[Key]
ORDER BY b.[Rank] DESC
GO


USE AdventureWorks
GO

--Bonus script for populating a full text index if you
-- created it with change tracking and manual update.
CREATE PROCEDURE dbo.asp_fulltextcrawl @databasename sysname,
@schema sysname, @tablename sysname
AS
DECLARE @cmd NVARcHAR(MAX)

SET @cmd = 'ALTER FULLTEXT INDEX ON ' + @databasename + '.' +
@schema + '.' + @tablename + ' START UPDATE POPULATION'

--EXEC master.dbo.sp_executesql @cmd
SELECT @cmd
RETURN
GO

exec dbo.asp_fulltextcrawl 'SQL2008SBS','Products','Product'
GO


(5-3? 1,4?)-jg
5-14

USE SQL2008SBS
GO

CREATE SCHEMA HumanResources AUTHORIZATION dbo
GO

CREATE TABLE Customers.Customer
(CustomerID INT IDENTITY(1,1),
CompanyName VARCHAR(50) NULL,
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,
ModifiedDate DATE NOT NULL CONSTRAINT df_modifieddate
DEFAULT GETDATE(),
CONSTRAINT pk_customer PRIMARY KEY (CustomerID))
GO

CREATE TABLE Customers.CustomerAddress
(AddressID INT IDENTITY(1,1),
CustomerID INT NOT NULL,
AddressTypeID INT NOT NULL,
AddressLine1 VARCHAR(50) NOT NULL,
AddressLine2 VARCHAR(50) SPARSE NULL,
AddressLine3 VARCHAR(50) SPARSE NULL,
City VARCHAR(50) NOT NULL,
StateProvinceID INT NULL,
CountryID INT NULL,
CONSTRAINT pk_customeraddress PRIMARY KEY (AddressID))
GO

CREATE TABLE Orders.OrderHeader
(OrderID INT IDENTITY(1,1),
CustomerID INT NOT NULL,
OrderDate DATE NOT NULL CONSTRAINT df_orderdate DEFAULT GETDATE(),
SubTotal MONEY NOT NULL CONSTRAINT ck_subtotal CHECK
(SubTotal > 0),
TaxAmount MONEY NOT NULL CONSTRAINT ck_taxamount CHECK
(TaxAmount >= 0),
ShippingAmount MONEY NOT NULL CONSTRAINT ck_shippingamount
CHECK (ShippingAmount >= 0),
GrandTotal AS (SubTotal + TaxAmount + ShippingAmount),
FinalShipDate DATE NULL,
CONSTRAINT pk_orderheader PRIMARY KEY (OrderID))
GO

CREATE TABLE Orders.OrderDetail
(OrderDetailID INT IDENTITY(1,1),
OrderID INT NOT NULL,
SKU CHAR(10) NOT NULL,
Quantity INT NOT NULL CHECK (Quantity > 0),
UnitPrice MONEY NOT NULL CHECK (UnitPrice > 0),
ShipDate DATE NULL,
CONSTRAINT pk_orderdetail PRIMARY KEY (OrderDetailID))
GO

CREATE TABLE Products.Product
(ProductID INT IDENTITY(1,1),
SubcategoryID INT NOT NULL,
ProductName VARCHAR(50) NOT NULL,
ProductCost MONEY NOT NULL CHECK (ProductCost > 0),
ListPrice MONEY NOT NULL CHECK (ListPrice > 0),
ProductMargin AS (ListPrice - ProductCost),
ProductDescription XML NULL,
CONSTRAINT pk_product PRIMARY KEY (ProductID))
GO

CREATE TABLE HumanResources.Employee
(EmployeeID INT IDENTITY(1,1),
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
JobTitle VARCHAR(50) NOT NULL,
BirthDate DATE NOT NULL,
HireDate DATE NOT NULL,
CONSTRAINT pk_employee PRIMARY KEY (EmployeeID))
GO

CREATE TABLE HumanResources.EmployeeAddress
(AddressID INT IDENTITY(1,1),
EmployeeID INT NOT NULL,
AddressTypeID INT NOT NULL,
AddressLine1 VARCHAR(50) NOT NULL,
AddressLine2 VARCHAR(50) SPARSE NULL,
AddressLine3 VARCHAR(50) SPARSE NULL,
City VARCHAR(50) NOT NULL,
StateProvinceID INT NULL,
CountryID INT NULL,
CONSTRAINT pk_employeeaddress PRIMARY KEY (AddressID))
GO

CREATE TABLE LookupTables.AddressType
(AddressTypeID INT IDENTITY(1,1),
AddressType VARCHAR(20) NOT NULL UNIQUE,
CONSTRAINT pk_addresstype PRIMARY KEY(AddressTypeID))
GO

CREATE TABLE LookupTables.Country
(CountryID INT IDENTITY(1,1),
CountryName VARCHAR(70) NOT NULL UNIQUE,
CONSTRAINT pk_country PRIMARY KEY(CountryID))
GO

CREATE TABLE LookupTables.StateProvince
(StateProvinceID INT IDENTITY(1,1),
CountryID INT NOT NULL,
StateProvince VARCHAR(50) NOT NULL UNIQUE,
StateProvinceAbbrev CHAR(2) NOT NULL,
CONSTRAINT pk_stateprovince PRIMARY KEY(StateProvinceID))
GO

CREATE TABLE LookupTables.ProductCategory
(CategoryID INT IDENTITY(1,1),
Category VARCHAR(20) NOT NULL UNIQUE,
CONSTRAINT pk_productcategory PRIMARY KEY(CategoryID))
GO

CREATE TABLE LookupTables.ProductSubCategory
(SubcategoryID INT IDENTITY(1,1),
CategoryID INT NOT NULL,
SubcategoryName VARCHAR(20) NOT NULL UNIQUE,
CONSTRAINT pk_productsubcategory PRIMARY KEY(SubcategoryID))
GO

ALTER TABLE Customers.CustomerAddress
ADD CONSTRAINT fk_customertocustomeraddress FOREIGN KEY (CustomerID)
REFERENCES Customers.Customer (CustomerID)
GO

ALTER TABLE Customers.CustomerAddress
ADD CONSTRAINT fk_customeraddresstoaddresstype FOREIGN KEY (AddressTypeID)
REFERENCES LookupTables.AddressType (AddressTypeID)
GO

ALTER TABLE Customers.CustomerAddress
ADD CONSTRAINT fk_customeraddresstostateprovince FOREIGN KEY
(StateProvinceID)
REFERENCES LookupTables.StateProvince (StateProvinceID)
GO

ALTER TABLE Customers.CustomerAddress
ADD CONSTRAINT fk_customeraddresstocountry FOREIGN KEY (CountryID)
REFERENCES LookupTables.Country (CountryID)
GO

ALTER TABLE Orders.OrderHeader
ADD CONSTRAINT fk_customertoorderheader FOREIGN KEY (CustomerID)
REFERENCES Customers.Customer (CustomerID)
GO

ALTER TABLE Orders.OrderDetail
ADD CONSTRAINT fk_orderheadertoorderdeatils FOREIGN KEY (OrderID)
REFERENCES Orders.OrderHeader (OrderID)
GO

ALTER TABLE Products.Product
ADD CONSTRAINT fk_producttosubcategory FOREIGN KEY (SubcategoryID)
REFERENCES LookupTables.ProductSubCategory (SubcategoryID)
GO

ALTER TABLE HumanResources.EmployeeAddress
ADD CONSTRAINT fk_employeetoemployeeaddress FOREIGN KEY (EmployeeID)
REFERENCES HumanResources.Employee (EmployeeID)
GO

ALTER TABLE HumanResources.EmployeeAddress
ADD CONSTRAINT fk_employeeaddresstoaddresstype FOREIGN KEY (AddressTypeID)
REFERENCES LookupTables.AddressType (AddressTypeID)
GO

ALTER TABLE HumanResources.EmployeeAddress
ADD CONSTRAINT fk_employeeaddresstostateprovince FOREIGN KEY
(StateProvinceID)
REFERENCES LookupTables.StateProvince (StateProvinceID)
GO

ALTER TABLE HumanResources.EmployeeAddress
ADD CONSTRAINT fk_employeeaddresstocountry FOREIGN KEY (CountryID)
REFERENCES LookupTables.Country (CountryID)
GO

ALTER TABLE LookupTables.StateProvince
ADD CONSTRAINT fk_countrytostateprovince FOREIGN KEY (CountryID)
REFERENCES LookupTables.Country(CountryID)
GO

ALTER TABLE LookupTables.ProductSubCategory
ADD CONSTRAINT fk_productcategorytosubcategory FOREIGN KEY (CategoryID)
REFERENCES LookupTables.ProductCategory(CategoryID)
GO

USE SQL2008SBSFS
GO

CREATE TABLE Products.ProductDocument
(DocumentID UNIQUEIDENTIFIER ROWGUIDCOL UNIQUE,
ProductID INT NOT NULL,
DocumentType VARCHAR(20) NOT NULL,
Document VARBINARY(MAX) FILESTREAM NULL,
CONSTRAINT pk_productdocument PRIMARY KEY(DocumentID))
GO

CREATE FULL TEXTS CATALOGS
17-1

ALTER DATABASE SQL2008SBS
ADD FILEGROUP FullTextCatalog
GO
ALTER DATABASE SQL2008SBSFS
ADD FILEGROUP FullTextCatalog
GO
ALTER DATABASE SQL2008SBS
ADD FILE (NAME = N'SQL2008SBSFT', FILENAME = N'C:\Program
Files\Microsoft SQL
Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\SQL2008SBSFT.ndf')
TO FILEGROUP FullTextCatalog
GO
ALTER DATABASE SQL2008SBSFS
ADD FILE (NAME = N'SQL2008SBSFSFT', FILENAME = N'C:\Program
Files\Microsoft SQL
Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\SQL2008SBSFSFT.ndf')
TO FILEGROUP FullTextCatalog
GO

17-2

USE SQL2008SBS
GO
CREATE FULLTEXT CATALOG ProductDescription
ON FILEGROUP FullTextCatalog
GO
USE SQL2008SBSFS
GO
CREATE FULLTEXT CATALOG ProductDocument
ON FILEGROUP FullTextCatalog
GO

CREATE FULL TEXT INDICES
17-3

USE SQL2008SBS
GO
CREATE FULLTEXT INDEX ON Products.Product(ProductDescription)
KEY INDEX pk_product
ON ProductDescription
WITH CHANGE_TRACKING = AUTO
GO

USE SQL2008SBSFS
GO
CREATE FULLTEXT INDEX ON Products.ProductDocument
(Document TYPE COLUMN DocumentType)
KEY INDEX pk_productdocument
ON ProductDocument
WITH CHANGE_TRACKING = AUTO
GO

show freetext exampl;es
17-4
USE AdventureWorks
GO
CREATE FULLTEXT INDEX ON Production.ProductDescription(Description)
KEY INDEX PK_ProductDescription_ProductDescriptionID
WITH CHANGE_TRACKING = AUTO
GO

--Freetext
SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE FREETEXT(Description,N'safety component')
GO
SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE FREETEXT(Description,N'bike')
GO

SELECT a.ProductDescriptionID, a.Description, b.*
FROM Production.ProductDescription a
INNER JOIN FREETEXTTABLE(Production.ProductDescription, Description,
N'bike') b ON a.ProductDescriptionID = b.[Key]
ORDER BY b.[Rank]
GO

--Contains
SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE CONTAINS(Description,N'bike')
GO

SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE CONTAINS(Description,N'"bike*"')
GO

SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE CONTAINS(Description,N' FORMSOF (INFLECTIONAL,ride) ')
GO

SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE CONTAINS(Description,N' FORMSOF (THESAURUS,metal) ')
GO

SELECT a.ProductDescriptionID, a.Description, b.*
FROM Production.ProductDescription a INNER JOIN
CONTAINSTABLE(Production.ProductDescription, Description,
N'bike NEAR performance') b ON a.ProductDescriptionID = b.[Key]
ORDER BY b.[Rank]
GO

SELECT a.ProductDescriptionID, a.Description, b.*
FROM Production.ProductDescription a INNER JOIN
CONTAINSTABLE(Production.ProductDescription, Description,
N'ISABOUT (performance WEIGHT (.8), comfortable WEIGHT (.6),
smooth WEIGHT (.2) , safe WEIGHT (.5), competition WEIGHT (.5))', 10)
b ON a.ProductDescriptionID = b.[Key]
ORDER BY b.[Rank] DESC
GO

17-x bonus
USE DBAdmin
GO

--Bonus script for populating a full text index if you
-- created it with change tracking and manual update.
CREATE PROCEDURE dbo.asp_fulltextcrawl @databasename sysname,
@schema sysname, @tablename sysname
AS
DECLARE @cmd NVARcHAR(MAX)

SET @cmd = 'ALTER FULLTEXT INDEX ON ' + @databasename + '.' +
@schema + '.' + @tablename + ' START UPDATE POPULATION'

--EXEC master.dbo.sp_executesql @cmd
SELECT @cmd
RETURN
GO

exec dbo.asp_fulltextcrawl 'SQL2008SBS','Products','Product'
GO