AdventureWorks2008 - [Production].[Document]

Description

Product maintenance documents.

Table properties

name	value
name	[Production].[Document]
created	Mar 19 2009 9:09PM
modified	Mar 19 2009 9:10PM
ansi nulls	on
quoted identifier	on
row count	13
Size of data	8 kb
Size of indexes	72 kb
Maximum size of a single row	NaN bytes

Columns

column	datatype	length	bytes	default	nulls	PK	FK	UQ	computed	comment
DocumentNode	hierarchyid	892	variable		no	yes			no	Primary key for Document records.
DocumentLevel	smallint	5	2		yes				yes	Depth in the document hierarchy.
Title	nvarchar(50)	50	200		no				no	Title of the document.
Owner	int	10	4		no		Employee.BusinessEntityID		no	Employee who controls the document. Foreign key to Employee.BusinessEntityID
FolderFlag	bit	1	1	((0))	no				no	0 = This is a folder, 1 = This is a document.
FileName	nvarchar(400)	400	1600		no				no	File name of the document
FileExtension	nvarchar(8)	8	32		no				no	File extension indicating the document type. For example, .doc or .txt.
Revision	nchar(5)	5	20		no				no	Revision number of the document.
ChangeNumber	int	10	4	((0))	no				no	Engineering change approval number.
Status	tinyint	3	1		no				no	1 = Pending approval, 2 = Approved, 3 = Obsolete
DocumentSummary	nvarchar(max)	max	2147483647		yes				no	Document abstract.
Document	varbinary	max	2147483647		yes				no	Complete document.
rowguid	uniqueidentifier	36	16	(newid())	no			yes	no	ROWGUIDCOL number uniquely identifying the record. Required for FileStream.
ModifiedDate	datetime	23	8	(getdate())	no				no	Date and time the record was last updated.

Full text index

name	columns
Document	Document, DocumentSummary

Indexes

name	description	column	comment
AK_Document_DocumentLevel_DocumentNode	nonclustered, unique located on PRIMARY	DocumentLevel, DocumentNode	Unique nonclustered index.
AK_Document_rowguid	nonclustered, unique located on PRIMARY	rowguid	Unique nonclustered index. Used to support FileStream.
IX_Document_FileName_Revision	nonclustered located on PRIMARY	FileName, Revision	Unique nonclustered index.
PK_Document_DocumentNode	clustered, unique, primary key located on PRIMARY	DocumentNode	Clustered index created by a primary key constraint.
UQ__Document__F73921F730F848ED	nonclustered, unique, unique key located on PRIMARY	rowguid

References

name
Employee

Referenced by

name
ProductDocument

Foreign key graph

EmployeeDocument ProductDocument

Foreign keys

name	columns	foreign columns	type	comment
FK_Document_Employee_Owner	Owner	Employee.BusinessEntityID		Foreign key constraint referencing Employee.BusinessEntityID.

Check constraints

name	column	comment
CK_Document_Status ([Status]>=(1) AND [Status]<=(3))	Status	Check constraint [Status] BETWEEN (1) AND (3)

Defaults

default name	column	comment
DF_Document_FolderFlag	FolderFlag
DF_Document_ChangeNumber	ChangeNumber	Default constraint value of 0
DF_Document_rowguid	rowguid	Default constraint value of NEWID()
DF_Document_ModifiedDate	ModifiedDate	Default constraint value of GETDATE()

Dependency graph

DF_Document_ChangeNumberDF_Document_FolderFlagDF_Document_ModifiedDateDF_Document_rowguidDocument CK_Document_StatusDocumentAK_Document_DocumentLevel_DocumentNode...AK_Document_rowguidIX_Document_FileName_Revision...PK_Document_DocumentNodeUQ__Document__F73921F730F848ED...

Objects that depend on [Production].[Document]

name	object type	database	server	level
Document	full text index	AdventureWorks2008	SPRING\KATMAI	1

Sample rows

DocumentNode	DocumentLevel	Title	Owner	FolderFlag	FileName	FileExtension	Revision	ChangeNumber	Status	DocumentSummary	Document	rowguid	ModifiedDate
/	0	Documents	217	True	Documents		0	0	2	NULL	NULL	27cf33af-c338-4842-966c-75ca11aaa6a3	1/14/2008 1:58:03 PM
/1/	1	Overview	217	True	Overview		0	0	2	NULL	NULL	26a266f1-1d23-40e2-af48-6ab8d954fe37	1/14/2008 1:58:03 PM
/1/1/	2	Introduction 1	219	False	Introduction 1.doc	.doc	4	28	2	NULL	System.Byte[]	48265174-8451-4967-973a-639c2276cfaf	7/1/2003 12:00:00 AM
/1/2/	2	Repair and Service Guidelines	220	False	Repair and Service Guidelines.doc	.doc	0	8	2	It is important that you maintain your bicycle and keep it in good repair. Detailed repair and servi...	System.Byte[]	7ed4def5-d5bb-4818-8748-5bb5f8315fa2	5/2/1998 12:00:00 AM
/2/	1	Maintenance	217	True	Maintenance		0	0	2	NULL	NULL	5184d96a-ee8c-499a-9316-625496784de6	1/14/2008 1:58:04 PM
/2/1/	2	Crank Arm and Tire Maintenance	219	False	Crank Arm and Tire Maintenance.doc	.doc	0	0	1	NULL	System.Byte[]	d11b82b0-eb40-4a66-8193-15f765a61f05	11/20/2002 9:56:38 AM
/2/2/	2	Lubrication Maintenance	219	False	Lubrication Maintenance.doc	.doc	2	11	1	Guidelines and recommendations for lubricating the required components of your Adventure Works Cycle...	System.Byte[]	2a8bd319-36fb-4fba-9e07-e303839f1974	7/1/2003 12:00:00 AM
/3/	1	Assembly	217	True	Assembly		0	0	2	NULL	NULL	f4f544ae-381a-4323-9a59-f5a629e21f46	1/14/2008 1:58:04 PM
/3/1/	2	Front Reflector Bracket and Reflector Assembly 3	220	False	Front Reflector Bracket and Reflector Assembly 3.doc	.doc	3	25	2	NULL	System.Byte[]	82f61d73-0bf5-44ff-ab99-dd73aee9b9cc	6/1/2002 12:00:00 AM
/3/2/	2	Front Reflector Bracket Installation	220	False	Front Reflector Bracket Installation.doc	.doc	1	15	2	Reflectors are vital safety components of your bicycle. Always ensure your front and back reflectors...	System.Byte[]	52f6be23-5363-4d93-96ae-f714b851e52c	6/1/2002 12:00:00 AM

Code

SET ARITHABORT ON
CREATE TABLE [Production].[Document](
    [DocumentNode] [hierarchyid] NOT NULL,
    [DocumentLevel]  AS ([DocumentNode].[GetLevel]()),
    [DocumentNode] [hierarchyid] NOT NULL,
    [Title] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Owner] [int] NOT NULL,
    [FolderFlag] [bit] NOT NULL,
    [FileName] [nvarchar](400) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [FileExtension] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Revision] [nchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [ChangeNumber] [int] NOT NULL,
    [Status] [tinyint] NOT NULL,
    [DocumentSummary] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Document] [varbinary](max) FILESTREAM  NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Document_DocumentNode] PRIMARY KEY CLUSTERED 
(
    [DocumentNode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] FILESTREAM_ON [DocumentFileStreamGroup],
UNIQUE NONCLUSTERED 
(
    [rowguid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] FILESTREAM_ON [DocumentFileStreamGroup]

ALTER TABLE [Production].[Document]  WITH CHECK ADD  CONSTRAINT [FK_Document_Employee_Owner] FOREIGN KEY([Owner])
REFERENCES [HumanResources].[Employee] ([BusinessEntityID])
ALTER TABLE [Production].[Document] CHECK CONSTRAINT [FK_Document_Employee_Owner]
ALTER TABLE [Production].[Document]  WITH CHECK ADD  CONSTRAINT [CK_Document_Status] CHECK  (([Status]>=(1) AND [Status]<=(3)))
ALTER TABLE [Production].[Document] CHECK CONSTRAINT [CK_Document_Status]
ALTER TABLE [Production].[Document] ADD  CONSTRAINT [DF_Document_FolderFlag]  DEFAULT ((0)) FOR [FolderFlag]
ALTER TABLE [Production].[Document] ADD  CONSTRAINT [DF_Document_ChangeNumber]  DEFAULT ((0)) FOR [ChangeNumber]
ALTER TABLE [Production].[Document] ADD  CONSTRAINT [DF_Document_rowguid]  DEFAULT (newid()) FOR [rowguid]
ALTER TABLE [Production].[Document] ADD  CONSTRAINT [DF_Document_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]

AdventureWorks2008 - tables - [Production].[Document]

Description

Table properties

Columns

Full text index

Indexes

References

Referenced by

Foreign key graph

Foreign keys

Check constraints

Defaults

Dependency graph

Objects that depend on [Production].[Document]

Sample rows

Code