SQL 2008 Core Studies - Index Internals

/*
05 - Index Internal.sql, by review Employee table
Remark: Learned from sqlskills.com
*/


USE MCM2008;
GO

-- Creating the Employee table, its clustered index and the
-- index on SSN
CREATE TABLE Employee
(
 EmployeeID  INT   NOT NULL IDENTITY,
 LastName  NCHAR(30) NOT NULL,
 FirstName  NCHAR(29) NOT NULL,
 MiddleInitial NCHAR(1) NULL,
 SSN    CHAR(11) NOT NULL,
 OtherColumns CHAR(258) NOT NULL DEFAULT 'Junk'
);
GO

-- Add the clustered index
ALTER TABLE Employee
 ADD CONSTRAINT EmployeePK
  PRIMARY KEY CLUSTERED (EmployeeID)
ON [PRIMARY];
GO

-- Add the nonclustered unique key
ALTER TABLE Employee
 ADD CONSTRAINT SSNUK
  UNIQUE NONCLUSTERED (SSN);
GO

SP_HELP EMPLOYEE
GO
/*
Name       Owner  Type         Created_datetime
---------- ------ ------------ -----------------------
Employee   dbo    user table   2011-01-15 15:31:25.047

 
Column_name      Type    Computed  Length      Prec  Scale Nullable  TrimTrailingBlanks      FixedLenNullInSource                Collation
---------------- ------- --------- ----------- ----- ----- --------- ----------------------- ----------------------------------- ------------------------------
EmployeeID       int     no        4           10    0     no        (n/a)                   (n/a)                               NULL
LastName         nchar   no        60                      no        (n/a)                   (n/a)                               SQL_Latin1_General_CP1_CI_AS
FirstName        nchar   no        58                      no        (n/a)                   (n/a)                               SQL_Latin1_General_CP1_CI_AS
MiddleInitial    nchar   no        2                       yes       (n/a)                   (n/a)                               SQL_Latin1_General_CP1_CI_AS
SSN              char    no        11                      no         no                     no                                  SQL_Latin1_General_CP1_CI_AS
OtherColumns     char    no        258                     no         no                     no                                  SQL_Latin1_General_CP1_CI_AS

 
Identity          Seed       Increment      Not For Replication
----------------- ---------- -------------- -------------------
EmployeeID        1          1              0

 
RowGuidCol
------------------------------------
No rowguidcol column defined.

 
Data_located_on_filegroup
-----------------------------------
PRIMARY

 
index_name           index_description                                                                 index_keys
-------------------- --------------------------------------------------------------------------------- ------------------------
EmployeePK           clustered, unique, primary key located on PRIMARY                                 EmployeeID
SSNUK                nonclustered, unique, unique key located on PRIMARY                               SSN

 
constraint_type                           constraint_name                       delete_action update_action status_enabled status_for_replication constraint_keys
------------------------------------ ---- ------------------------------------- ------------- ------------- -------------- ---------------------- ---------------------
DEFAULT on column OtherColumns            DF__Employee__OtherC__00551192        (n/a)         (n/a)         (n/a)          (n/a)                  ('Junk')
PRIMARY KEY (clustered)                   EmployeePK                            (n/a)         (n/a)         (n/a)          (n/a)                  EmployeeID
UNIQUE (non-clustered)                    SSNUK                                 (n/a)         (n/a)         (n/a)          (n/a)                  SSN

 
No foreign keys reference table 'EMPLOYEE', or you do not have permissions on referencing tables.
No views with schema binding reference table 'EMPLOYEE'.
*/

USE IndexInternals;
GO

SELECT index_depth AS D,
 index_level AS L,
 record_count AS 'Count',
 page_count AS PgCnt,
 avg_page_space_used_in_percent AS 'PgPercentFull',
 min_record_size_in_bytes AS 'MinLen',
 max_record_size_in_bytes AS'MaxLen',
 avg_record_size_in_bytes AS 'AvgLen'
FROM sys.dm_db_index_physical_stats 
 (DB_ID('IndexInternals'),
 OBJECT_ID('IndexInternals.dbo.Employee'),
 1,
 NULL,
 'DETAILED');
GO
/*
D    L    Count                PgCnt                PgPercentFull          MinLen      MaxLen      AvgLen
---- ---- -------------------- -------------------- ---------------------- ----------- ----------- ----------------------
3    0    80000                4000                 99.3081294786261       400         400         400
3    1    4000                 7                    91.7540400296516       11          11          11
3    2    7                    1                    1.09957993575488       11          11          11

(3 row(s) affected)
*/

USE master;
go

IF OBJECTPROPERTY(object_id('sp_tablepages'), 'IsUserTable') IS NOT NULL
    DROP TABLE sp_tablepages;
go

CREATE TABLE sp_tablepages
(
    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,
    CONSTRAINT sp_tablepages_PK
        PRIMARY KEY (PageFID, PagePID)
);
GO
 
------------------------------------------------------------------------------
-- How do you use sp_tablepages?
-- Just truncate the table before insert and then select!
------------------------------------------------------------------------------
USE IndexInternals;
GO

TRUNCATE TABLE sp_tablepages;
INSERT INTO sp_tablepages
--EXEC ('DBCC IND (AdventureWorks2008, [Sales.SalesOrderDetail], -1)');
EXEC ('DBCC IND (IndexInternals, Employee, 1)');
GO

SELECT IndexLevel
    , PageFID
    , PagePID
    , PrevPageFID
    , PrevPagePID
    , NextPageFID
    , NextPagePID
FROM sp_tablepages
ORDER BY IndexLevel DESC, PrevPagePID;
GO


sp_helpindex 'Employee'
GO
/*
index_name         index_description                                            index_keys
-----------------  -----------------------------------------------------------  ---------------
EmployeePK          clustered, unique, primary key located on PRIMARY           EmployeeID
EmployeeSSNUK       nonclustered, unique, unique key located on PRIMARY         SSN
*/

SELECT index_depth AS D,
 index_level AS L,
 record_count AS 'Count',
 page_count AS PgCnt,
 avg_page_space_used_in_percent AS 'PgPercentFull',
 min_record_size_in_bytes AS 'MinLen',
 max_record_size_in_bytes AS'MaxLen',
 avg_record_size_in_bytes AS 'AvgLen'
FROM sys.dm_db_index_physical_stats 
 (DB_ID('IndexInternals'),
 OBJECT_ID('IndexInternals.dbo.Employee'),
 2,
 NULL,
 'DETAILED');
/*
D    L    Count                PgCnt                PgPercentFull          MinLen      MaxLen      AvgLen
---- ---- -------------------- -------------------- ---------------------- ----------- ----------- ----------------------
2    0    80000                179                  99.3661106992834       16          16          16
2    1    179                  1                    44.2055843834939       18          18          18

(2 row(s) affected)
*/

SET STATISTICS IO ON
SELECT e.*
FROM dbo.Employee AS e
WHERE e.SSN = '123-07-9980';
GO
/*
EmployeeID  LastName                       FirstName                     MiddleInitial SSN         OtherColumns
----------- ------------------------------ ----------------------------- ------------- ----------- -----------------------
37281       Ugo                            A. Scott                                    123-07-9980 Junk                                                                                                                                                                                                                                                            

(1 row(s) affected)

Table 'Employee'. Scan count 0, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/