/************************************
Chapter 7 Scripts
For MCITP SQL Server 2005 Database Administration All-in-One Exam Guide
By Darril Gibson

The scripts available here are the same ones used in each of the chapters. Ideally, you would type in the script and learn from any errors you might make. Occasionally, however, you may want a copy of the script to paste into the query window or into a batch file. If so, feel free to use these script files. 

I strongly encourage you not to rely on these pre-typed scripts, but instead to get used to typing the T-SQL yourself. The more you do it (even if you’re just typing what you see in the book), the more comfortable you’ll get with T-SQL.

Some scripts in the book are intended to be run from the command line. They've been included where appropriate so that you can copy and paste them into Notepad and save them as a batch file (.bat). You can then run the batch file from the command prompt.

*************************************/

SELECT Price from Parts WHERE PartID = 101;

/************************************/

SELECT Price from Parts WHERE Name = 'Blue Widget';

/************************************/

-- idx_LastName created manually within chapter

USE AdventureWorks;
GO
DROP INDEX idx_LastName
ON Person.Contact;

/************************************/

USE AdventureWorks;
GO
CREATE NONCLUSTERED INDEX idx_LastFirstName
   ON [Person].[Contact] (LastName)
      INCLUDE ( [FirstName]);

/************************************/

USE AdventureWorks
SELECT AVG(Len(FirstName)) FROM Person.Contact
GO
SELECT AVG(Len(LastName)) FROM Person.Contact

/************************************/

USE AdventureWorks
SELECT Count(*) FROM Person.Contact

/************************************/

USE AdventureWorks;
GO
SELECT * FROM HumanResources.vEmployee
WHERE EmployeeID = 101

/************************************/

USE AdventureWorks;
GO
DBCC SHOW_STATISTICS ('Person.Address', AK_Address_rowguid);
GO

/************************************/

USE AdventureWorks;
GO
UPDATE STATISTICS Person.Address;
GO
DBCC SHOW_STATISTICS ('Person.Address', AK_Address_rowguid);
GO

/************************************/

SELECT DATABASEPROPERTYEX('AdventureWorks','IsFullTextEnabled')

/************************************/

USE AdventureWorks;
SELECT DATABASEPROPERTYEX('AdventureWorks','IsFullTextEnabled')

/************************************/

EXEC [AdventureWorks].[dbo].[sp_fulltext_database] @action = 'enable'

/************************************/

USE AdventureWorks;
CREATE FULLTEXT CATALOG ftCatalog as DEFAULT

/************************************/

CREATE FULLTEXT INDEX ON HumanResources.JobCandidate(Resume) KEY INDEX    
PK_JobCandidate_JobCandidateID;

/************************************/

SELECT *
FROM HumanResources.JobCandidate
WHERE CONTAINS(Resume, ' "mechanical engineering" ');

/************************************/

USE AdventureWorks;
GO
SELECT * FROM Sales.Customer

/************************************/

SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);

/************************************/

SELECT * FROM sys.dm_db_index_usage_stats

/************************************/

SELECT * FROM 
sys.dm_db_index_physical_stats
 (DB_ID('AdventureWorks'), NULL, NULL, NULL, NULL);

USE AdventureWorks;
GO
SELECT * FROM sys.objects
WHERE object_id = 30623152

/************************************/

USE AdventureWorks;
GO
SELECT * FROM sys.dm_db_index_usage_stats 
WHERE database_id = DB_ID();

/************************************/

SELECT CustomerID, ContactID from Sales.StoreContact

/************************************/

SELECT * FROM sys.dm_db_index_usage_stats 
WHERE database_id = DB_ID();

/************************************/

USE AdventureWorks;
GO
ALTER INDEX IX_StoreContact_ContactID
ON Sales.StoreContact
REORGANIZE;
GO

/************************************/

CREATE Database Chapter7;
GO
USE Chapter7;


/************************************/

ALTER DATABASE Chapter7 ADD FILEGROUP Secondary;
ALTER DATABASE Chapter7
ADD FILE (NAME = 'SecondFile', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ SecondFile.ndf',
	SIZE = 1024KB, FILEGROWTH = 1024KB) 
		TO FILEGROUP Secondary

/************************************/

CREATE TABLE dbo.Customer
(
	CustomerID int IDENTITY(1,1) NOT NULL, 
	Notes varchar(50) NOT NULL, 
    	CONSTRAINT [PK_Customer_CustomerID] PRIMARY KEY CLUSTERED 
	(
		CustomerID
	 )
)ON SECONDARY