-- Code and scripts from Chapter 6: Tables

-- You can create a phone_number UDT and use it consistently 
--  for any column in any table that keeps track of 
--  phone numbers to ensure that they all use the same data type. 
--  Here’s how to create this UDT:

USE tempdb
GO
CREATE TYPE phone_number FROM varchar(20) NOT NULL;
GO

-- And here’s how to use the new UDT when you create a table:

CREATE TABLE customer
(
cust_id			smallint 		NOT NULL,
cust_name		varchar(50)		NOT NULL,
cust_addr1		varchar(50)		NOT NULL,
cust_addr2		varchar(50)		NOT NULL,
cust_city		varchar(50)		NOT NULL,
cust_state		char(2)		NOT NULL,
cust_postal_code	varchar(10)		NOT NULL,
cust_phone		phone_number	NOT NULL,
cust_fax			varchar(20)		NOT NULL,
cust_email		varchar(30)		NOT NULL,
cust_web_url		varchar(100)	NOT NULL
);
GO

-- The following query selects all the rows from sys.columns and 
--   displays the column_id, the column name, the data type values, 
--   and the maximum length, and the results are shown immediately after:  

SELECT column_id, name, system_type_id, user_type_id, 
        type_name(user_type_id) as user_type_name, max_length
FROM sys.columns 
WHERE object_id=object_id('customer');
GO



-- Let’s look at a specific example now to see information in these  
--   catalog views. You can create the table in any database, but I suggest 
-- either using tempdb, so the table will be automatically dropped next time 
--   you restart your SQL Server, or creating a new database just for testing.  
USE tempdb
GO
CREATE TABLE dbo.employee	(
			emp_lname  varchar(15)	NOT NULL,
			emp_fname  varchar(10)	NOT NULL,
			address    varchar(30)	NOT NULL,
			phone	     char(12)	NOT NULL,
			job_level  smallint 	NOT NULL
);

-- This table will have one row in sys.indexes and one in sys.partitions, 
--   as we can see when we run the following queries. 
--   I am including only a few of the columns from sys.indexes, but sys.partitions 
--   only has six columns, so I have retrieved them all. 
SELECT  object_id, name, index_id, type_desc
FROM sys.indexes
WHERE object_id=object_id('dbo.employee');
GO
SELECT *
FROM sys.partitions
WHERE object_id=object_id('dbo.employee');
GO

--  Each row in the sys.allocation_units view has a unique 
--   allocation_unit_id value. Each row also has a value in 
--   the column called container_id that can be joined with 
--   partition_id in sys.partitions, as shown in this 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.employee');
GO


--  Now let’s add some new columns to the table that will need to 
--    be stored on other types of pages. 

ALTER TABLE dbo.employee ADD resume_short varchar(8000);
ALTER TABLE dbo.employee ADD resume_long text;
GO


--  If we run the query above that joins sys.partitions and sys.allocation_units, 
--   we’ll get three rows:


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.employee');
GO


--  The following query will join all three views: 
--    sys.indexes, sys.partitions, and sys.allocation_units
--    to show you the table name, index name and type, page type, 
--    and space usage information for the dbo.employee table:

SELECT  convert(char(8),object_name(i.object_id)) AS table_name, i.name AS index_name, 
    i.index_id, i.type_desc as index_type,
    partition_id, partition_number AS pnum,  rows, 
    allocation_unit_id AS au_id, a.type_desc as page_type_desc, total_pages AS pages
FROM sys.indexes i JOIN sys.partitions p  
	  ON i.object_id = p.object_id AND i.index_id = p.index_id
    JOIN sys.allocation_units a
	  ON p.partition_id = a.container_id
WHERE i.object_id=object_id('dbo.employee');
GO



-- First let’s look at the simpler case of an all fixed-length row:
USE tempdb;
GO
CREATE TABLE Fixed 
(
Col1 char(5)     NOT NULL,
Col2 int         NOT NULL,
Col3 char(3)     NULL,
Col4 char(6)     NOT NULL 
);
GO

--  When this table is created, you should be able to execute the 
--   following queries against the sys.indexes and sys.columns views 
 
SELECT object_id,  type_desc, 
    indexproperty(object_id, name, 'minlen') as minlen 
FROM sys.indexes 
WHERE object_id=object_id('fixed');
GO
SELECT  column_id, name, system_type_id, max_length
FROM sys.columns 
WHERE object_id=object_id('fixed');
GO

--  To look at a specific data row in this table, you must first insert a new row:
INSERT Fixed VALUES ('ABCDE', 123, NULL, 'CCCC');
GO

-- To run the DBCC PAGE command, I needed to know what page number was used 
--  to store the row for this table. A value for first_page is stored in an 
--  undocumented view called sys.system_internals_allocation_units, 
--  which is almost identical to the sys.allocation_units view. 
--  The following query gives me the value for first_page for the table called fixed:

SELECT object_name(object_id) AS name, 
    rows, type_desc as page_type_desc,
    total_pages AS pages, first_page
FROM sys.partitions p  JOIN sys.system_internals_allocation_units a
	ON p.partition_id = a.container_id
WHERE object_id=object_id('dbo.fixed');
GO


-- Unless you particularly enjoy playing with hexadecimal conversions, 
--   you might want to use one of two other options for determining the 
--   actual page numbers associated with your SQL Server tables and indexes. 
--   First you could create the function shown here to convert a 
--   6-byte hexadecimal page number value (such as 0xCF0400000100) 
--   to a file_number:page_number format. 

CREATE FUNCTION convert_page_nums (@page_num binary(6))
   RETURNS varchar(11) 
AS 
  BEGIN
	RETURN(convert(varchar(2), (convert(int, substring(@page_num, 6, 1)) 
          * power(2, 8)) + 
             (convert(int, substring(@page_num, 5, 1)))) + ':' + 
               convert(varchar(11), 
	(convert(int, substring(@page_num, 4, 1)) * power(2, 24)) + 
	(convert(int, substring(@page_num, 3, 1)) * power(2, 16)) + 
	(convert(int, substring(@page_num, 2, 1)) * power(2, 8)) + 
	(convert(int, substring(@page_num, 1, 1)))) )
  END;
GO


-- You can then executing this SELECT to call the function:
SELECT dbo.convert_page_nums(0xF00300000100);

-- Here is a query to return basic column information, including the 
--  offset within the row for each column. I will be using the same query 
--  for other tables later in this chapter, and I will refer to it as 
--  the “column detail query.” 

SELECT  c.name AS column_name, column_id, max_inrow_length, 
         pc.system_type_id, leaf_offset 
 FROM sys.system_internals_partition_columns pc
    JOIN sys.partitions p 
	   ON p.partition_id = pc.partition_id
    JOIN sys.columns c
         ON column_id = partition_column_id 
            AND c.object_id = p.object_id
WHERE p.object_id=object_id('fixed');
GO



-- Now let’s look at the somewhat more complex case of a table with 
--   variable-length data. Each row has three varchar columns and 
--   two-fixed length columns:

USE tempdb;
GO
CREATE TABLE variable 
(
Col1 char(3)       NOT NULL,
Col2 varchar(250)  NOT NULL,
Col3 varchar(5)    NULL,
Col4 varchar(20)   NOT NULL,
Col5 smallint      NULL
);
GO

-- When this table is created, you should be able to execute the 
--   following queries against the sys.indexes, sys.partitions, 
--   sys.system_internals_partition_columns, and sys.columns views  

SELECT object_id,  type_desc, 
    indexproperty(object_id, name, 'minlen') as minlen
    FROM sys.indexes where object_id=object_id('variable');
GO
SELECT  name, column_id, max_inrow_length, pc.system_type_id, leaf_offset 
 FROM sys.system_internals_partition_columns pc
    JOIN sys.partitions p 
	   ON p.partition_id = pc.partition_id
    JOIN sys.columns c
         ON column_id = partition_column_id AND c.object_id = p.object_id
WHERE p.object_id=object_id('variable');
GO

-- Now you insert a row into the table:
INSERT variable VALUES 
    ('AAA', REPLICATE('X', 250), NULL, 'ABC', 123);
GO



-- The following code creates a table with rows that have a maximum defined 
--   length of much greater than 8060 bytes.
USE tempdb;
GO
CREATE TABLE dbo.bigrows 
     (a varchar(3000), 
	b varchar(3000),
	c varchar(3000),
	d varchar(3000) );
GO

-- Not only can the above dbo.bigrows table be created in SQL Server 2005, 
--   but you can insert a row with column sizes that add up to more than 
--   8060 bytes with a simple INSERT, as shown here:

INSERT INTO dbo.bigrows
     SELECT REPLICATE('e', 2100), REPLICATE('f', 2100),
	      REPLICATE('g', 2100),  REPLICATE('h', 2100);
GO

--  To determine whether SQL Server is storing any data in row-overflow 
--   data pages for a particular table, you can run the 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.bigrows');
GO




-- One row can span many row-overflow pages if it contains many large 
--   variable-length columns. For example, you can create the table 
--   dbo.hugerows and insert a single row into it:

CREATE TABLE dbo.hugerows 
     (a varchar(3000), 
	b varchar(8000),
	c varchar(8000),
	d varchar(8000));
GO

INSERT INTO dbo.hugerows
     SELECT REPLICATE('a', 3000), REPLICATE('b', 8000),
	      REPLICATE('c', 8000),  REPLICATE('d', 8000);
GO

-- Now  I run the allocation query shown earlier

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


--  In some cases, if a large variable-length column shrinks, it can be moved 
--   back to the regular row. However, for efficiency reasons, if the decrease 
--   is just a few bytes, SQL Server will not bother checking. 
--   Only when a column stored in a row-overflow page is reduced by more than 
--   1000 bytes will SQL Server even consider checking to see if the column can 
--   now fit on the regular data page. 

--  You can observe this behavior if you previously created the dbo.bigrows 
--   table for the earlier example and inserted only the one row with 
--   2100 characters in each column. 

-- The following update reduces the size of the first column by 500 bytes, 
--  reducing the row size to 7900 bytes, which should all fit on the one data page. 

UPDATE bigrows
SET a = replicate('a', 1600);
GO


-- However, if you run the allocation query again, you’ll see that there are 
--  two row-overflow pages. 

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.bigrows');
GO

-- Now reduce the size of the first column by more than 1000 bytes, 
-- and run the allocation query once more. 
UPDATE bigrows
SET a = 'aaaaa';
GO
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.bigrows');
GO



--  Let’s create a table very similar to the one we created to look at row structures, 
--    but we’ll change the varchar(250) column to the text data type. 
--    We’ll use almost the same insert statement to insert one row into the table.
USE tempdb;
GO
CREATE TABLE HasText 
(
Col1 char(3)       NOT NULL,
Col2 varchar(5)    NOT NULL,
Col3 text	       NOT NULL,
Col4 varchar(20)   NOT NULL
);
GO

INSERT HasText VALUES 
    ('AAA', 'BBB', REPLICATE('X', 250), 'CCC');
GO

--  Now let’s find the basic information for this table using the allocation query 

SELECT convert(char(7), object_name(object_id))  AS name, 
    partition_id, partition_number AS pnum,  rows, 
    allocation_unit_id AS au_id, convert(char(17), 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.HasText');
GO

-- Now let’s enable text data in the row, for up to 500 bytes:

EXEC sp_tableoption HasText, 'text in row', 500;
GO

--  Enabling this option does not force the text data to be moved into the row. 
--   We have to update the text value to actually force the data movement:

UPDATE HasText 
SET col3 =  REPLICATE('Z', 250);
GO



--  I’ll create a simple table with a sql_variant column and insert a
--    few rows into it so we can observe the structure of the sql_variant storage.
USE tempdb
GO
CREATE TABLE variant (a int, b sql_variant)
GO
INSERT INTO variant VALUES (1, 3)
INSERT INTO variant VALUES (2, 3000000000)
INSERT INTO variant VALUES (3, 'abc')
INSERT INTO variant VALUES (4, current_timestamp);
GO




--  Create a table with all fixed-length columns, including a smallint in the 
--   first position. 
CREATE TABLE change
(col1 smallint, col2 char(10), col3 char(5));
GO

-- Now look at the column offsets:

SELECT  c.name AS column_name, column_id, max_inrow_length, pc.system_type_id, leaf_offset 
 FROM sys.system_internals_partition_columns pc
    JOIN sys.partitions p 
	   ON p.partition_id = pc.partition_id
    JOIN sys.columns c
         ON column_id = partition_column_id 
            AND c.object_id = p.object_id
WHERE p.object_id=object_id('change');
GO


--  Now change the smallint to int:

ALTER TABLE change
   ALTER COLUMN col1 int;
GO

-- And finally, run the column detail query again to see that col1 now starts much 
--    later in the row and that no column starts at offset 4 immediately 
--    after the row header information. This new column creation due to an 
--    ALTER TABLE takes place even before any data has been placed in the table. 

SELECT  c.name AS column_name, column_id, max_inrow_length, pc.system_type_id, leaf_offset 
 FROM sys.system_internals_partition_columns pc
    JOIN sys.partitions p 
	   ON p.partition_id = pc.partition_id
    JOIN sys.columns c
         ON column_id = partition_column_id 
            AND c.object_id = p.object_id
WHERE p.object_id=object_id('change');
GO


-- Another drawback to SQL Server’s behavior in not actually dropping the 
--   old column is that we are now more severely limited in the size of the row. 
--   The row size now includes the old column, which is no longer usable or visible 
--   (unless you use DBCC PAGE). 
--   For example, if I create a table with a couple of large fixed-length 
--   character columns, as shown here, I can then ALTER the char(2000) column 
--   to be char(3000)
 
CREATE TABLE bigchange
(col1 smallint, col2 char(2000), col3 char(1000));
GO
ALTER TABLE bigchange 
   ALTER COLUMN col2 char(3000);
GO

-- At this point, the length of the rows should be just over 4000 bytes 
--   because there is a 3000-byte column, a 1000-byte column, and a smallint. 
--   However, if I try to add another 3000-byte column, it will fail.

ALTER TABLE bigchange 
   ADD col4 char(3000);
GO
SELECT  c.name AS column_name, column_id, max_inrow_length, pc.system_type_id, leaf_offset 
 FROM sys.system_internals_partition_columns pc
    JOIN sys.partitions p 
	   ON p.partition_id = pc.partition_id
    JOIN sys.columns c
         ON column_id = partition_column_id 
            AND c.object_id = p.object_id
WHERE p.object_id=object_id('bigchange');
GO


-- However, if I just create a table with two 3000-byte columns 
--  and a 1000-byte column, there will be no problem.

CREATE TABLE nochange
(col1 smallint, col2 char(3000), col3 char(1000), col4 char(3000));
GO