-- Code and scripts from Chapter 7: Index Internals and Management


-- Here is a script that creates a table called sp_table_pages with 
--   columns to hold all the returned information from DBCC IND. 
--   Note that any object created in the master database with a 
--   name that starts with sp_ can be accessed from any database, 
--   without having to qualify it with the database name.

USE master;
GO
CREATE TABLE sp_table_pages
(PageFID  tinyint, 
  PagePID int,   
  IAMFID   tinyint, 
  IAMPID  int, 
  ObjectID  int,
  IndexID  tinyint,
  PartitionNumber tinyint,
  PartitionID bigint,
  iam_chain_type  varchar(30),    
  PageType  tinyint, 
  IndexLevel  tinyint,
  NextPageFID  tinyint,
  NextPagePID  int,
  PrevPageFID  tinyint,
  PrevPagePID int, 
  Primary Key (PageFID, PagePID));
GO

-- The following code truncates the sp_table_pages table and then 
--   fills it with DBCC IND results from the Sales.SalesOrderDetail 
--   table in the AdventureWorks database. 

TRUNCATE TABLE sp_table_pages;
INSERT INTO sp_table_pages
    EXEC ('dbcc ind ( AdventureWorks, [Sales.SalesOrderDetail], -1)'  );
GO


--  SQL Server adds the uniqueifier only when necessary, that is, 
--   when duplicate keys are added to the table. As an example, 
--    I’ll create a small table with all fixed-length columns 
--   and then add a clustered, nonunique index to the table. 


USE AdventureWorks;
GO
CREATE TABLE Clustered_Dupes 
  (Col1 char(5)   NOT NULL,
   Col2 int     NOT NULL,
   Col3 char(3)   NULL,
   Col4 char(6)   NOT NULL);
GO
CREATE CLUSTERED INDEX Cl_dupes_col1 ON Clustered_Dupes(col1);
GO
--  If you look at the row in the sysindexes compatibility view 
--   for this table, you’ll notice something unexpected.

SELECT first, indid, keycnt, name FROM sysindexes 
WHERE id = object_id ('Clustered_Dupes');
GO



-- To find the first (or only) data page of the table I can run 
--   DBCC IND and find the page with a PageType of 1 with no 
--   previous page — that will be the first page in logical order 
--   in the data pages, which are the leaf level of the clustered index.  
INSERT Clustered_Dupes VALUES ('ABCDE', 123, null, 'CCCC');
GO
TRUNCATE TABLE sp_table_pages;
INSERT INTO sp_table_pages
    EXEC ('dbcc ind ( AdventureWorks, Clustered_Dupes, -1)'  );
SELECT PageFID, PagePID FROM sp_table_pages
   WHERE PageType = 1 and PrevPageFID = 0 and PrevPagePID = 0;
GO


--The following code creates and populates a table that I’ll use 
--   to show you the structure of clustered index node rows.
USE AdventureWorks;
GO
CREATE TABLE clustered_nodupes (
  id int NOT NULL ,
  str1 char (5) NOT NULL ,
  str2 char (600) NULL ); 
GO
CREATE CLUSTERED INDEX idxCL ON Clustered_Nodupes(str1); 
GO
SET NOCOUNT ON;
GO
DECLARE @i int;
SET @i = 1240;
WHILE @i < 13000 BEGIN
  INSERT INTO Clustered_Nodupes 
   SELECT @i, cast(@i AS char), cast(@i AS char);
  SET @i = @i + 1;
 END; 
GO

TRUNCATE TABLE sp_table_pages;
INSERT INTO sp_table_pages
    EXEC ('dbcc ind ( AdventureWorks, Clustered_Nodupes, -1)'  );
SELECT PageFID, PagePID, IndexLevel, PageType FROM sp_table_pages
WHERE IndexId = 1 and IndexLevel >= 0 
       and PrevPageFID = 0 and PrevPagePID = 0;
GO


-- First we’ll look at the leaf level of a nonclustered index built 
--   on a heap. I’ll use the same code I used to build the clustered 
--   index with no duplicates in the previous example, but the index 
--   I build on the str1 column will be nonclustered. I’ll also 
--   put only a few rows in the table so the root page will be the 
--   entire index.

USE AdventureWorks;
SET NOCOUNT ON;
CREATE TABLE NC_Heap_Nodupes (
  id int NOT NULL ,
  str1 char (5) NOT NULL ,
  str2 char (600) NULL ); 
GO
CREATE UNIQUE INDEX idxNC_heap ON NC_Heap_Nodupes (str1); 
GO
SET NOCOUNT ON;
GO
DECLARE @i int;
SET @i = 1240;
WHILE @i < 1300 BEGIN
  INSERT INTO NC_Heap_Nodupes 
   SELECT @i, cast(@i AS char), cast(@i AS char);
  SET @i = @i + 1;
 END; 
GO

TRUNCATE TABLE sp_table_pages;
INSERT INTO sp_table_pages
    EXEC ('dbcc ind ( AdventureWorks, NC_Heap_Nodupes, -1)'  );
SELECT PageFID, PagePID, IndexID, IndexLevel, PageType 
FROM sp_table_pages
WHERE IndexLevel >= 0;
GO


-- I’ll build a nonclustered index on a similar table, but first I’ll 
--   build a clustered index on a varchar column so we can see what an 
--   index looks like when a bookmark is a clustered index key. Also, 
--   in order not to have the same values in the same sequence in both 
--   the str1 and str2 columns, I’ll introduce a bit of randomness into 
--   the generation of the values for str2. If you run this script, the 
--   randomness might end up generating some duplicate values for the 
--   unique clustered index column str2. Because each row is inserted 
--   in its own INSERT statement, a violation of uniqueness will cause 
--   only that one row to be rejected. If you get error messages about 
--   PRIMARY KEY violation, just ignore them. You’ll still end up with 
--   enough rows.

USE AdventureWorks;
GO
SET NOCOUNT ON;
GO
CREATE TABLE NC_Nodupes (
  id int NOT NULL ,
  str1 char (5) NOT NULL ,
  str2 varchar (10) NULL 
); 
GO
CREATE UNIQUE CLUSTERED INDEX idxcl_str2 on NC_Nodupes (str2);
CREATE UNIQUE INDEX idxNC ON NC_Nodupes (str1); 
GO
SET NOCOUNT ON;
GO
DECLARE @i int;
SET @i = 1240;
WHILE @i < 1300 BEGIN
  INSERT INTO NC_Nodupes 
   SELECT @i, cast(@i AS char), 
       cast(cast(@i * rand() AS int) as char);
  SET @i = @i + 1;
 END; 
GO

TRUNCATE TABLE sp_table_pages;
INSERT INTO sp_table_pages
    EXEC ('dbcc ind ( AdventureWorks, NC_Nodupes, -1)'  );
SELECT PageFID, PagePID, IndexID, IndexLevel, PageType 
FROM sp_table_pages
WHERE IndexLevel >= 0;
GO


-- The last example will show a composite nonclustered index on str1 
--   and str2 and an overlapping clustered index on str2. I’ll just 
--   show you the code to create the table and indexes; the code to 
--   populate the table and to find the root of the index is identical 
--   to the code in the previous example.

USE AdventureWorks; 
CREATE TABLE NC_Overlap (
  id int NOT NULL ,
  str1 char (5) NOT NULL ,
  str2 char (10) NULL 
); 
GO
CREATE UNIQUE CLUSTERED INDEX idxCL_overlap ON NC_Overlap (str2);
CREATE UNIQUE INDEX idxNC_overlap ON NC_Overlap (str1, str2); 
GO


-- I’ll create a table called hugerows_with_text that contains regular 
--   in-row data, row-overflow data, and LOB data. 

USE AdventureWork;
CREATE TABLE dbo.hugerows_with_text 
     (a varchar(3000), 
	b varchar(8000),
	c varchar(8000),
	d text);
GO
INSERT INTO dbo.hugerows_with_text 
     SELECT REPLICATE('a', 3000), REPLICATE('b', 8000),
	      REPLICATE('c', 8000),  REPLICATE('d', 8000);
GO


-- We can compare the results of my 'allocation query' with the 
--    results of selecting from sys.db_dm_partition_stats:

-- Here is my 'allocation query'

SELECT object_name(object_id) AS name, 
    partition_id, partition_number AS pnum,  rows, 
    allocation_unit_id AS au_id, type_desc as page_type_desc,
    total_pages AS pages
FROM sys.partitions p  JOIN sys.allocation_units a
	ON p.partition_id = a.container_id
WHERE object_id=object_id('dbo.hugerows_with_text ');
GO

-- Here are the results of selecting from sys.db_dm_partition_stats
SELECT * FROM sys.dm_db_partition_stats
WHERE object_id=object_id('dbo.hugerows_with_text');
GO

-- The index_name() function returns the name of an index when pass an 
--    object id and an index id

USE AdventureWorks;
GO
CREATE FUNCTION dbo.index_name (@object_id int, @index_id tinyint)
RETURNS sysname
AS
BEGIN
  DECLARE @index_name sysname;
  SELECT @index_name = name FROM sys.indexes
     WHERE object_id = @object_id and index_id = @index_id;
  RETURN(@index_name);
END;
GO


--  Now I will use this function to return just a few selected columns 
--    from sys.db_dm_partition_stats.

SELECT convert(char(70),dbo.index_name(object_id, index_id)) AS index_name, 
     used_page_count, reserved_page_count, row_count
FROM sys.dm_db_partition_stats
WHERE object_id=object_id('Sales.SalesOrderDetail');
GO


-- The following query returns the values for five of the six set 
--   options discussed above for the current session:
SELECT quoted_identifier, arithabort, ansi_warnings, 
       ansi_padding, ansi_nulls, concat_null_yields_null
FROM sys.dm_exec_sessions
WHERE session_id = @@spid;
GO

-- The following code adds a computed column called Final that shows 
--   the total price for an item after the discount is applied. 
--   The statement to build an index on Final will fail because the 
--   resultant column involving the real value is imprecise. 
USE Northwind;
GO
ALTER TABLE [Order Details] 
   ADD Final AS 
    (Quantity * UnitPrice) - Discount * (Quantity * UnitPrice);
GO
CREATE INDEX OD_Final_Index on [Order Details](Final);
GO


-- With persisted columns, all you need to do is drop the computed 
--   column, which is a metadata-only operation, and then redefine it 
--   as a persisted column. You can then build the index on the 
--   computed, persisted column. 

ALTER TABLE [Order Details] 
   DROP COLUMN Final;
GO
ALTER TABLE [Order Details] 
   ADD Final AS 
   (Quantity * UnitPrice) - Discount * (Quantity * UnitPrice) PERSISTED;
GO
CREATE INDEX OD_Final_Index on [Order Details](Final);


-- When determining whether you have to use the PERSISTED option, use 
--   the COLUMNPROPERTY function and the IsPrecise property to 
--   determine whether a deterministic column is precise.

SELECT COLUMNPROPERTY (object_id('Order Details'), 'Final', 'IsPrecise'); 
GO



-- The first step in building an index on a view is to create the view 
--   itself. Here’s an example from the AdventureWorks database:
 
USE AdventureWorks;
GO
CREATE VIEW Vdiscount1 WITH SCHEMABINDING 
  AS 
SELECT SUM(UnitPrice*OrderQty) AS SumPrice, 
   SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS SumDiscountPrice, 
   COUNT_BIG(*) AS Count, ProductID 
FROM Sales.SalesOrderDetail 
GROUP BY ProductID;
GO


-- Note the WITH SCHEMABINDING clause and the specification of the 
--   schema name (dbo) for the table. At this point, we have a normal 
--   view—a stored SELECT statement that uses no storage space. 
--   In fact, if we look at the data in sys.dm_db_partition_stats for 
--   this view, we’ll see that we get no rows returned.

SELECT convert(char(25),dbo.index_name(object_id, index_id)) AS index_name, 
     used_page_count, reserved_page_count, row_count
FROM sys.dm_db_partition_stats
WHERE object_id=object_id('dbo.Vdiscount1');
GO


-- To create an indexed view, you must create an index. The first index 
--   you create on a view must be a unique clustered index. 
--   Clustered indexes are the only type of SQL Server index that 
--   contains data; the clustered index on a view contains all the 
--   data that makes up the view definition. This statement defines a 
--   unique clustered index for the view:

CREATE UNIQUE CLUSTERED INDEX VDiscount_Idx ON Vdiscount1 (ProductID);
GO



-- The scripts to set up the partitioning tables, and sample data 
--   use to populate those tables, are in their own separate files. 
--   Please download the zip file called 'Partitioning Scripts.zip'



-- You can use the following query to determine whether a table 
--   is partitioned, by replacing Production.TransactionHistoryArchive 
--   with the name of the table you’re interested in:

USE AdventureWorks;
GO
SELECT DISTINCT object_name(object_id) as TableName, 
        ISNULL(ps.name, 'Not partitioned') as PartitionScheme
FROM (sys.indexes i LEFT  JOIN sys.partition_schemes ps
          ON (i.data_space_id = ps.data_space_id))
WHERE (i.object_id = object_id('Production.TransactionHistoryArchive')) 
       AND   (i.index_id IN (0,1));
GO




-- This view returns information about each partition of each 
--   partitioned table. The WHERE clause filters out partitioned 
--   indexes (other than the clustered index), but you can change that 
--   condition if you desire. When selecting from the view, you can 
--   add your own WHERE clause to find information about just the 
--   table you’re interested in. 

USE AdventureWorks;
GO
CREATE VIEW Partition_Info AS
SELECT OBJECT_NAME(i.object_id) as Object_Name, dbo.INDEX_NAME(i.object_id,i.index_id) AS Index_Name, 
	   p.partition_number, fg.name AS Filegroup_Name, rows, au.total_pages,
	   CASE boundary_value_on_right 
                   WHEN 1 THEN 'less than' 
                   ELSE 'less than or equal to' END as 'comparison', value
FROM sys.partitions p JOIN sys.indexes i
	ON p.object_id = i.object_id and p.index_id = i.index_id
     JOIN sys.partition_schemes ps 
              ON ps.data_space_id = i.data_space_id
     JOIN sys.partition_functions f 
			  ON f.function_id = ps.function_id
     LEFT JOIN  sys.partition_range_values rv    
ON f.function_id = rv.function_id 
                    AND p.partition_number = rv.boundary_id
      JOIN sys.destination_data_spaces dds
			ON dds.partition_scheme_id = ps.data_space_id 
                    AND dds.destination_id = p.partition_number
	  JOIN sys.filegroups fg 
			   ON dds.data_space_id = fg.data_space_id
	  JOIN (SELECT container_id, sum(total_pages) as total_pages 
				   FROM sys.allocation_units
				   GROUP BY container_id) AS au
			   ON au.container_id = p.partition_id	  
  WHERE i.index_id <2;
GO


-- This query will use the view above to get information about my 
--   TransactionHistory table’s partitions:

SELECT * FROM Partition_Info 
WHERE Object_Name = 'TransactionHistory';
GO


--  The following query returns information about each allocation unit i
--   in the first two partitions of my TransactionHistory and 
--   TransactionHistoryArchive tables, including the number of rows, 
--   the number of pages, the type of data in the allocation unit, 
--   and the page where the allocation unit starts.

SELECT convert(char(25),object_name(object_id)) AS name, 
    rows, convert(char(15),type_desc) as page_type_desc,
    total_pages AS pages, first_page, index_id, partition_number
FROM sys.partitions p JOIN sys.system_internals_allocation_units a
	ON p.partition_id = a.container_id
WHERE (object_id=object_id('[Production].[TransactionHistory]')
   OR object_id=object_id('[Production].[TransactionHistoryArchive]'))
  AND index_id <= 1 AND partition_number <= 2;
GO


--  Now let’s move one of my partitions. My ultimate goal is to add 
--   a new partition to TransactionHistory to store a new month’s 
--   worth of data and to move the oldest month’s data into 
--   TransactionHistoryArchive. 

ALTER PARTITION SCHEME TransactionsPS1 
NEXT USED [PRIMARY];
GO

ALTER PARTITION FUNCTION TransactionRangePF1() 
SPLIT RANGE ('20040901');
GO

-- Next, I’ll do something similar for the function and partition 
--   scheme used by TransactionHistoryArchive. In this case, I’ll 
--   add a new boundary point for October 1, 2003. 
ALTER PARTITION SCHEME TransactionArchivePS2 
NEXT USED [PRIMARY];
GO

ALTER PARTITION FUNCTION TransactionArchivePF2() 
SPLIT RANGE ('20031001');
GO



-- To SWITCH a partition from one table to another, I have to guarantee 
--   that all the data to be moved meets the requirements for the new 
--   location. So I add a CHECK constraint that guarantees that no data 
--   in TransactionHistory is earlier than September 1, 2003. 
--   After adding the CHECK constraint, I run the ALTER TABLE command 
--   with the SWITCH option to move the data in partition 1 of 
--   TransactionHistory to partition 2 of TransactionHistoryArchive. 
--   (For your testing purposes, you could try leaving out the next 
--    step that adds the constraint and try just executing the 
--   ALTER TABLE / SWITCH command. You’ll get an error message, and then 
--   you add the constraint and run the SWITCH command again. )


ALTER TABLE [Production].[TransactionHistory] 
ADD CONSTRAINT [CK_TransactionHistory_DateRange] 
CHECK ([TransactionDate] >= '20030901');
GO
ALTER TABLE [Production].[TransactionHistory] 
SWITCH PARTITION 1 
TO [Production].[TransactionHistoryArchive] PARTITION 2;
GO


-- Now we run the metadata query that examines the size and location 
--   of the first two partitions of each table:
SELECT convert(char(25),object_name(object_id)) AS name, 
    rows, convert(char(15),type_desc) as page_type_desc,
    total_pages AS pages, first_page, index_id, partition_number
FROM sys.partitions p JOIN sys.system_internals_allocation_units a
	ON p.partition_id = a.container_id
WHERE (object_id=object_id('[Production].[TransactionHistory]')
   OR object_id=object_id('[Production].[TransactionHistoryArchive]'))
  AND index_id <= 1 AND partition_number <= 2;



-- Let’s look at what happens to a page when it splits. The following 
--   script creates a table with large rows—so large, in fact, that 
--   only five rows will fit on a page. Once the table is created and 
--   populated with five rows, we’ll find its first (and only, in this 
--  case) page by inserting the output of DBCC IND in the sp_table_pages 
--   table, finding the information for the data page with no previous 
--   page, and then using DBCC PAGE to look at the contents of the page. 
 
-- First create the table

USE AdventureWorks;
GO
CREATE TABLE bigrows
(
    a int  primary key,
    b varchar(1600)
);
GO
/* Insert five rows into the table */
INSERT INTO bigrows 
    VALUES (5, REPLICATE('a', 1600));
INSERT INTO bigrows 
    VALUES (10, replicate('b', 1600)); 
INSERT INTO bigrows 
    VALUES (15, replicate('c', 1600)); 
INSERT INTO bigrows 
    VALUES (20, replicate('d', 1600)); 
INSERT INTO bigrows 
    VALUES (25, replicate('e', 1600)); 
GO
TRUNCATE TABLE sp_table_pages;
INSERT INTO sp_table_pages
    EXEC ('dbcc ind ( AdventureWorks, bigrows, -1)'  );
SELECT PageFID, PagePID
FROM sp_table_pages
WHERE PageType = 1;



-- SQL Server 2005 doesn’t automatically compress space on a page 
--   when a row is deleted. As a performance optimization, the 
--   compaction doesn’t occur until a page needs additional contiguous 
--   space for inserting a new row. You can see this in the following 
--   example, which deletes a row from the middle of a page

USE AdventureWorks;
GO
CREATE TABLE smallrows
(
    a int identity,
    b char(10)
);
GO
INSERT INTO smallrows 
    VALUES ('row 1'); 
INSERT INTO smallrows 
    VALUES ('row 2');
INSERT INTO smallrows  
    VALUES ('row 3');
INSERT INTO smallrows  
    VALUES ('row 4');
INSERT INTO smallrows  
    VALUES ('row 5');
GO
TRUNCATE TABLE sp_table_pages;
INSERT INTO sp_table_pages
    EXEC ('dbcc ind (AdventureWorks, smallrows, -1)'  );
SELECT PageFID, PagePID
FROM sp_table_pages
WHERE PageType = 1;
GO


-- The following example builds the same table used in the previous 
--   DELETE example, but this time the table has a primary key declared,
--   which means a clustered index will be built. The data is the leaf 
--   level of the clustered index, so when the row is removed, it will 
--   be marked as a ghost. 

USE AdventureWorks;
GO
DROP TABLE smallrows;
GO
CREATE TABLE smallrows
(
    a int IDENTITY PRIMARY KEY,
    b char(10)
);
GO
INSERT INTO smallrows 
    VALUES ('row 1'); 
INSERT INTO smallrows
    VALUES ('row 2');
INSERT INTO smallrows
    VALUES ('row 3');
INSERT INTO smallrows
    VALUES ('row 4');
INSERT INTO smallrows
    VALUES ('row 5');
GO
TRUNCATE TABLE sp_table_pages;
GO
INSERT INTO sp_table_pages
    EXEC ('dbcc ind (AdventureWorks, smallrows, -1)'  );
SELECT PageFID, PagePID
FROM sp_table_pages
WHERE PageType = 1;
GO



-- I’ll create a table a lot like the one we created for doing inserts, 
--   but this table will have a third column of variable length. After 
--   I populate the table with five rows, which will fill the page, I’ll 
--   update one of the rows to make its third column much longer. The 
--   row will no longer fit on the original page and will have to move. 
--   I can then load the output from DBCC IND into the sp_table_pages 
--   table to get the page numbers used by the table.

USE AdventureWorks;
GO
DROP TABLE bigrows;
GO
CREATE TABLE bigrows
(   a int IDENTITY ,
    b varchar(1600),
    c varchar(1600));
GO
INSERT INTO bigrows 
    VALUES (REPLICATE('a', 1600), ''); 
INSERT INTO bigrows 
    VALUES (REPLICATE('b', 1600), '');
INSERT INTO bigrows 
    VALUES (REPLICATE('c', 1600), '');
INSERT INTO bigrows 
    VALUES (REPLICATE('d', 1600), '');
INSERT INTO bigrows 
    VALUES (REPLICATE('e', 1600), '');
GO
UPDATE bigrows 
SET c = REPLICATE('x', 1600)
WHERE a = 3;
GO

TRUNCATE TABLE sp_table_pages;
INSERT INTO sp_table_pages
    EXEC ('dbcc ind (AdventureWorks, bigrows, -1)'  );
SELECT PageFID, PagePID
FROM sp_table_pages
WHERE PageType = 1;
GO


-- The following code builds a table with a unique clustered index on 
--   column X and then updates that column in both rows:
USE pubs;
GO
CREATE TABLE T1(X int PRIMARY KEY, Y int);
INSERT T1 VALUES(1, 10);
INSERT T1 VALUES(2, 20);
GO
UPDATE T1 SET X = 3 - X;
GO