-- Here’s the basic syntax for creating the customer table in the dbo schema,
-- with three fixed-length character (char) columns.
CREATE TABLE dbo.customer
(
name char(30),
phone char(12),
emp_id char(4)
);
-- From the system’s standpoint, the following CREATE TABLE example is identical to the preceding one,
-- but it’s harder to read from a user’s standpoint:
CREATE TABLE customer (name char(30), phone char(12), emp_id char(4));
-- The third statement is legal in any circumstance:
CREATE TABLE dbo.customer(name char(30), column char(12), emp_id char(4));
CREATE TABLE dbo.customer(name char(30), "column" char(12), emp_id char(4));
CREATE TABLE dbo.customer(name char(30), [column] char(12), emp_id char(4));
-- You can determine whether this option is on or off for your session by executing the
-- following query:
SELECT quoted_identifier
FROM sys.dm_exec_sessions
WHERE session_id = @@spid;
-- Because we’re storing current date and time in a local variable, we can be sure we’re using
-- the same value for all the CONVERT operations:
DECLARE @today datetime
SELECT @today = CURRENT_TIMESTAMP
SELECT @today AS [CURRENT TIMESTAMP];
SELECT CONVERT (varbinary(8), @today) AS [INTERNAL FORMAT];
SELECT CONVERT (int, SUBSTRING (CONVERT (varbinary(8), @today), 1, 4))
AS [DAYS AFTER 1/1/1900];
SELECT CONVERT (int, SUBSTRING (CONVERT (varbinary(8), @today), 5, 4))
AS [TICKS AFTER MIDNIGHT];
-- For a quick look at what the information in Table 5-4 means, you can run the
-- following three conversions:
SELECT CAST(CURRENT_TIMESTAMP AS time);
SELECT CAST(CURRENT_TIMESTAMP AS time(2));
SELECT CAST(CURRENT_TIMESTAMP AS time(7));
-- To see the available collations, you can run the query
SELECT * FROM fn_helpcollations();
-- For instance, if you run the code
CREATE TABLE NepaleseTest
(abc char(5) COLLATE Nepali_100_CI_AS NOT NULL);
-- To view the code page for a collation, you can use the collationproperty function,
-- as in this example:
SELECT collationproperty('Latin1_General_CS_AS', 'CodePage');
-- To illustrate this, consider this table:
CREATE TABLE #words (word nvarchar(20) NOT NULL,
wordno tinyint PRIMARY KEY CLUSTERED);
INSERT #words
VALUES(N'cloud', 1), (N'CSAK', 6), (N'cukor', 11),
(N'Oblige', 2), (N'Opera', 7), (N'Öl', 12),
(N'résumé', 3), (N'RESUME', 8), (N'RÉSUMÉ', 13),
(N'resume', 4), (N'resumes', 9), (N'résumés', 14),
(N'ŒIL', 5), (N'œil', 10);
-- We start by looking at the commonly used collation Latin1_General_CI_AS:
WITH collatedwords (collatedword, wordno) AS (
SELECT word COLLATE Latin1_General_CI_AS, wordno
FROM #words
)
SELECT collatedword, rank = dense_rank() OVER(ORDER BY collatedword),
wordno
FROM collatedwords
ORDER BY collatedword;
-- For instance, note the following code:
SELECT * FROM #words
WHERE word COLLATE Latin1_General_CI_AS > 'opera';
SELECT * FROM #words
WHERE word COLLATE Latin1_General_CS_AS > 'opera';
-- If you are used to character ranges from regular expressions in other languages, you may fall
-- into the following trap when trying to select the words that start with an uppercase letter:
SELECT * FROM #words WHERE word LIKE '[A-Z]%';
-- To illustrate the difference between the two types of binary collations and also true byte-sort,
-- here is an example where we use the characters Z (U+005A) and N (N with grave accent; U+0143):
SELECT n, str, convert(binary(6), str) AS bytestr,
row_number() OVER(ORDER BY convert(varbinary, str))
AS bytesort,
row_number() OVER(ORDER BY str COLLATE Latin1_General_BIN)
AS collate_BIN,
row_number() OVER(ORDER BY str COLLATE Latin1_General_BIN2)
AS collate_BIN2
FROM (VALUES(1, N'ZZZ'), (2, N'ZNN'), (3, N'NZZ'), (4, N'NNN'))
AS T(n, str)
ORDER BY n;
-- To study the tertiary collations, we use a different table with different words as follows:
CREATE TABLE #prefwords
(word char(3) COLLATE SQL_Latin1_General_Pref_CP1_CI_AS
NOT NULL,
wordno int NOT NULL PRIMARY KEY NONCLUSTERED,
tert AS tertiary_weights(word));
CREATE CLUSTERED INDEX word_ix ON #prefwords (word);
--CREATE INDEX tert_ix on #prefwords(word, tert)
go
INSERT #prefwords (word, wordno)
VALUES ('abc', 1), ('abC', 4), ('aBc', 7),
('aBC', 2), ('Abc', 5), ('ABc', 8),
('AbC', 3), ('ABC', 6);
go
SELECT word, wordno, rank = dense_rank() OVER(ORDER BY word),
rowno = row_number() OVER (ORDER BY word)
FROM #prefwords
ORDER BY word--, wordno;
-- And see what happens if we uncomment wordno from the ORDER BY clause, so that the query now reads:
SELECT word, wordno
FROM #prefwords
ORDER BY word, wordno;
-- Consider the following:
SELECT col FROM tbl WHERE indexedcol = @value;
-- For instance, look at the following:
SELECT COUNT(*) FROM tbl WHERE longcol LIKE '%abc%';
-- If you have a varchar column, you can speed this up by forcing the collation as follows:
SELECT COUNT(*) FROM tbl
WHERE longcol COLLATE SQL_Latin1_General_CP_CI_AS LIKE '%abc%';
-- The following query shows the values for all the SET options in your current session,
-- and if you have VIEW SERVER STATE permission, you can change or remove the WHERE clause
-- to return information about other sessions as follows:
SELECT * FROM sys.dm_exec_sessions
WHERE session_id = @@spid;
-- Here’s how to create this UDT:
CREATE TYPE phone_number FROM varchar(20) NOT NULL;
-- 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
);
-- 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 then displays the results:
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');
-- If not otherwise specified, the seed value starts at 1 and increments by 1, as shown in this example:
CREATE TABLE customer
(
cust_id smallint IDENTITY NOT NULL,
cust_name varchar(50) NOT NULL
);
-- Take a look at this statement:
SELECT IDENT_SEED('customer'), IDENT_INCR('customer')
-- This next example explicitly starts the numbering at 100 (seed) and increments the value by 20:
CREATE TABLE customer
(
cust_id smallint IDENTITY(100, 20) NOT NULL,
cust_name varchar(50) NOT NULL
);
-- For example, the following two statements work identically and return the same data:
SELECT IDENTITYCOL FROM customer;
SELECT cust_id FROM customer;
-- Here are two valid INSERT statements for the customer table shown previously:
INSERT customer VALUES ('ACME Widgets');
INSERT customer (cust_name) VALUES ('AAA Gadgets');
-- To you, it might look like you’re inserting and then immediately checking the value, as follows:
INSERT customer (cust_name) VALUES ('AAA Gadgets');
SELECT @@IDENTITY;
-- If an INSERT trigger also inserted a row that contained an identity column, it would be in a
-- different scope, like this:
INSERT customer (cust_name) VALUES ('AAA Gadgets');
SELECT SCOPE_IDENTITY();
-- You can get this value using the IDENT_CURRENT function, which takes a table name as an argument:
SELECT IDENT_CURRENT('customer');
-- Many of my examples assume a database called test:
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
);
-- 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');
SELECT *
FROM sys.partitions
WHERE object_id=object_id('dbo.employee');
-- 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');
-- We’ll look at the details of row-overflow and text data storage later in this section,
-- and we’ll look at ALTER TABLE at the end of this chapter, but now I just want to look at the
-- additional rows in sys.allocation_units:
ALTER TABLE dbo.employee ADD resume_short varchar(8000);
ALTER TABLE dbo.employee ADD resume_long text;
-- The following query joins 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');
-- Without this traceflag, no output is returned for the DBCC PAGE command.
DBCC TRACEON(3604);
GO
DBCC PAGE (pubs, 1, 157, 1);
GO
-- For example, suppose a table is created with the following statement:
CREATE TABLE Test1
(
Col1 int NOT NULL,
Col2 char(25) NOT NULL,
Col3 varchar(60) NULL,
Col4 money NOT NULL,
Col5 varchar(20) NOT NULL
);
-- First, let me create a table (that will be used in the following section) and
-- insert a single row into it:
USE tempdb;
CREATE TABLE Fixed
(
Col1 char(5) NOT NULL,
Col2 int NOT NULL,
Col3 char(3) NULL,
Col4 char(6) NOT NULL
);
INSERT Fixed VALUES ('ABCDE', 123, NULL, 'CCCC');
-- The following query gives me the value for first_page in the Fixed table:
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');
-- First you can create the function shown here to convert a six-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;
-- You can then execute this SELECT to call the function:
SELECT dbo.convert_page_nums(0xCF0400000100);
-- However, for a sneak preview, you can run the following command and note the values in
-- the first two columns of output (labeled PageFID and PagePID) in the row where PageType = 1,
-- which indicates that the page is a data page:
DBCC IND(tempdb, fixed, -1);
-- We can use this function to get the pages used by our data in the table Fixed, as follows:
SELECT sys.fn_PhysLocFormatter (%%physloc%%) AS RID, * FROM Fixed;
GO
-- First, let’s look at the simpler case of an all fixed-length row using the table I just
-- built in the preceding section:
CREATE TABLE Fixed
(
Col1 char(5) NOT NULL,
Col2 int NOT NULL,
Col3 char(3) NULL,
Col4 char(6) NOT NULL
);
-- When this table is created, you should be able to execute the following queries against the
-- sys.indexes and sys.columns views to receive the information similar to the results shown:
SELECT object_id, type_desc,
indexproperty(object_id, name, 'minlen') as min_row_len
FROM sys.indexes where object_id=object_id('Fixed');
SELECT column_id, name, system_type_id, max_length as max_col_len
FROM sys.columns
WHERE object_id=object_id('Fixed');
-- If you didn’t insert this row in the preceeding section, insert it now:
INSERT Fixed VALUES ('ABCDE', 123, NULL, 'CCCC');
-- I was able to get the page contents by running the DBCC PAGE command, using the file and
-- page number obtained using one of the methods that I described previously:
DBCC PAGE(tempdb, 1, 1231, 1);
-- I will use 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');
-- Each row has three varchar columns and two fixed-length columns:
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
);
-- 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
-- to receive the information similar to the results shown here:
SELECT object_id, type_desc,
indexproperty(object_id, name, 'minlen') as minlen
FROM sys.indexes where object_id=object_id('variable');
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');
-- Now you can insert a row into the table as follows:
INSERT variable VALUES
('AAA', REPLICATE('X', 250), NULL, 'ABC', 123);
-- The only fixed-length column is the integer identity column:
CREATE TABLE dbo.null_varchar
(
id INT PRIMARY KEY IDENTITY(1,1),
col1 VARCHAR(10) NULL,
col2 VARCHAR(10) NULL,
col3 VARCHAR(10) NULL,
col4 VARCHAR(10) NULL,
col5 VARCHAR(10) NULL,
col6 VARCHAR(10) NULL,
col7 VARCHAR(10) NULL,
col8 VARCHAR(10) NULL,
col9 VARCHAR(10) NULL,
col10 VARCHAR(10) NULL
);
GO
-- The fourth has a single character in the first varchar column, and empty strings in all the others:
SET NOCOUNT ON
INSERT INTO null_varchar(col10)
SELECT 'a';
INSERT INTO null_varchar(col1)
SELECT 'b';
INSERT INTO null_varchar
SELECT '','','','','','','','','','c';
INSERT INTO null_varchar
SELECT 'd','','','','','','','','','';
GO
-- The table also includes single-column character values, which I use just so I can find
-- the other values easily in the single row of hex data that DBCC PAGE gives me:
CREATE TABLE times (
a char(1),
dt1 datetime,
b char(1),
sd smalldatetime,
c char(1),
dt2 datetime2,
d char(1),
dt date,
e char(1),
dto datetimeoffset,
f char(1),
t time,
g char(1),
t0 time(0),
h char(1),
t1 time(1),
i char(1),
t2 time(2),
j char(1),
t3 time(3),
k char(1),
t4 time(4),
l char(1),
t5 time(5),
m char(1),
t6 time(6),
n char(1),
t7 time(7));
GO
-- The data types that need a date component assume a default date of January 1, 1900:
INSERT INTO times
SELECT
'a', '01:02:03.123',
'b', '01:02:03.123',
'c', '01:02:03.123',
'd', '01:02:03.123',
'e', '01:02:03.123',
'f', '01:02:03.123',
'g', '01:02:03.123',
'h', '01:02:03.123',
'i', '01:02:03.123',
'j', '01:02:03.123',
'k', '01:02:03.123',
'l', '01:02:03.123',
'm', '01:02:03.123',
'n', '01:02:03.123';
-- To compute the corresponding date, you can use the dateadd function:
SELECT DATEADD(dd, 693595, CAST('0001/1/1' AS datetime2));
-- SQL Server uses the following conversions to determine the hours, minutes, and seconds from 3723:
SELECT hours = (3723 / 60) / 60;
SELECT minutes = (3723 / 60) % 60;
SELECT seconds = 3723 % 60;
-- 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 testdb;
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);
-- We can now use DBCC IND to find the first page of the table and use DBCC PAGE to see its
-- contents as follows:
DBCC IND (testdb, variant, -1);
-- (I got a value of file 1, page 2508 for the data page in this table)
GO
DBCC TRACEON (3604);
DBCC PAGE (testdb, 1, 2508, 1);
-- The following simple CREATE TABLE statement, which includes a primary key on the table,
-- creates a PRIMARY KEY constraint along with the table, and the constraint has a very cryptic-looking name:
CREATE TABLE customer
(
cust_id int IDENTITY NOT NULL PRIMARY KEY,
cust_name varchar(30) NOT NULL
);
-- EXAMPLE:
/* Change the length of the emp_lname column in the employee
table from varchar(15) to varchar(30) */
ALTER TABLE employee
ALTER COLUMN emp_name varchar(30);
-- In the following example, we’re adding a constraint to validate that cust_id in orders matches
-- a cust_id in customer, but we don’t want the constraint applied to existing data:
ALTER TABLE orders
WITH NOCHECK
ADD FOREIGN KEY (cust_id) REFERENCES customer (cust_id);
-- In this example, we enable all the constraints on the employee table:
ALTER TABLE employee
CHECK CONSTRAINT ALL;
-- First, 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));
-- 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');
-- Now change smallint to int:
ALTER TABLE change
ALTER COLUMN col1 int;
-- 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));
ALTER TABLE bigchange
ALTER COLUMN col2 char(3000);
-- However, if I try to add another 3,000-byte column, it fails:
ALTER TABLE bigchange
ADD col4 char(3000);
-- However, if I just create a table with two 3,000-byte columns and a 1,000-byte column,
-- there is no problem:
CREATE TABLE nochange
(col1 smallint, col2 char(3000), col3 char(1000), col4 char(3000));
-- You can see this in the following example, which deletes a row from the middle of a page
-- and then inspects that page using DBCC PAGE:
USE testdb;
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
DBCC IND (testdb, smallrows, -1);
-- Note the FileID and PageID from the row where PageType = 1
-- and use those values with DBCC PAGE (I got FileID 1 and PageID 4536)
DBCC TRACEON(3604);
GO
DBCC PAGE(testdb, 1, 4536,1);
-- Now we’ll delete the middle row (WHERE a = 3) and look at the page again:
DELETE FROM smallrows
WHERE a = 3;
GO
DBCC PAGE(testdb, 1, 4536,1);
GO
-- I can use DBCC IND to get the page numbers used by the table as follows:
USE testdb;
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
DBCC IND (testdb, bigrows, -1);
DBCC IND (testdb, bigrows, -1);
-- Note the FileID and PageID from the rows where PageType = 1
-- and use those values with DBCC PAGE (I got FileID 1 and
-- PageID values of 2252 and 4586.
DBCC TRACEON(3604);
GO
DBCC PAGE(testdb, 1, 2252, 1);
GO
