/************************************
Chapter 7 Scripts
For MCITP SQL Server 2005 Database Developer 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.

Be aware that some of the scripts fail on purpose. For example, the book may be showing how a script works, and then after a constraint is added, the book may show how the same script fails.  The failure is not an error. In other words, just running the scripts without the benefit of the book won't be much help.

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

SELECT Price from Parts WHERE PartID = 101;

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

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

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

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

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

USE AdventureWorks;
GO
SELECT LastName, FirstName, EmailAddress 
FROM Person.Contact;

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

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

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

USE AdventureWorks;
GO
CREATE NONCLUSTERED INDEX idx_LastFirstNameEmail
   ON Person.Contact (LastName)
      INCLUDE ( FirstName, EmailAddress);

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

USE AdventureWorks;
GO
SELECT LastName, FirstName, EmailAddress 
FROM Person.Contact;

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

SELECT Price from Parts WHERE PartID = 101;

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

SELECT * FROM Employees ORDER BY LastName

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

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

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

USE AdventureWorks
SELECT Count(*) FROM Person.Contact

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

SELECT * FROM FamilyTable WHERE LastName = 'Toth' AND FirstName = 'William';

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

SELECT * FROM FamilyTable WHERE FamilyID > 990;

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

SELECT * FROM FamilyTable WHERE FamilyID < 900;

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

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

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

DBCC SHOW_STATISTICS (tableName, indexName)

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

ALTER DATABASE AdventureWorks
SET AUTO_CREATE_STATISTICS ON
ALTER DATABASE AdventureWorks
SET AUTO_UPDATE_STATISTICS ON

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

ALTER DATABASE AdventureWorks
  SET AUTO_CREATE_STATISTICS ON
ALTER DATABASE AdventureWorks
  SET AUTO_UPDATE_STATISTICS ON

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

USE AdventureWorks;
GO
DBCC SHOW_STATISTICS ('Person.Contact', AK_Contact_rowguid);

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

USE AdventureWorks;
GO
UPDATE STATISTICS Person.Contact;
GO
DBCC SHOW_STATISTICS ('Person.Contact', AK_Contact_rowguid );

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

USE AdventureWorks;
GO
sp_updatestats;

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

SET STATISTICS IO on;
GO
SELECT * FROM Person.Contact;

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

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

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

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

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

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

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

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" ');

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

SELECT *
FROM HumanResources.JobCandidate
WHERE CONTAINS(Resume, ' FORMSOF (INFLECTIONAL, sell, sale) ');

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

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;

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

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

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