Production.Document Table

Description

Product maintenance documents.

Properties

Creation Date	27/10/2017 14:33
File Group	PRIMARY
Text File Group	PRIMARY
System Object
Published for Replication
Rows	13
Data Space Used	336.00 KB
Index Space Used	72.00 KB

Columns

Column Name	Description	Datatype	Length	Default	Formula
DocumentNode	Primary key for Document records.	UserDefined	892
DocumentLevel	Depth in the document hierarchy.	SmallInt	2		([DocumentNode].[GetLevel]())
Title	Title of the document.	VarWChar	50
Owner	Employee who controls the document. Foreign key to Employee.BusinessEntityID	Integer	4
FolderFlag	0 = This is a folder, 1 = This is a document.	Boolean	1	((0))
FileName	File name of the document	VarWChar	400
FileExtension	File extension indicating the document type. For example, .doc or .txt.	VarWChar	8
Revision	Revision number of the document.	WChar	5
ChangeNumber	Engineering change approval number.	Integer	4	((0))
Status	1 = Pending approval, 2 = Approved, 3 = Obsolete	UnsignedTinyInt	1
DocumentSummary	Document abstract.	LongVarWChar	16
Document	Complete document.	LongVarBinary	16
rowguid	ROWGUIDCOL number uniquely identifying the record. Required for FileStream.	GUID	16	(newid())
ModifiedDate	Date and time the record was last updated.	DBTimeStamp	4	(getdate())

Indexes

Index	Description	Primary	Unique
AK_Document_DocumentLevel_DocumentNode	Unique nonclustered index.
AK_Document_rowguid	Unique nonclustered index. Used to support FileStream.
IX_Document_FileName_Revision	Unique nonclustered index.
PK_Document_DocumentNode	Primary key (clustered) constraint
UQ__Document__F73921F7C5112C2E

Check Constraints

Name	Description	Expression
CK_Document_Status	Check constraint [Status] BETWEEN (1) AND (3)	([Status]>=(1) AND [Status]<=(3))

Relationships

Relationship	Description
FK_Document_Employee_Owner	Foreign key constraint referencing Employee.BusinessEntityID.
FK_ProductDocument_Document_DocumentNode	Foreign key constraint referencing Document.DocumentNode.

Objects that depend on Production.Document

	Database Object	Object Type	Description	Dep Level
	Production.ProductDocument	Table	Cross-reference table mapping products to related product documents.	1

Objects that Production.Document depends on

Database Object	Object Type	Description	Dep Level
Person.BusinessEntity	Table	Source of the ID that connects vendors, customers, and employees with address and contact information.	3
HumanResources.Employee	Table	Employee information such as salary, department, and title.	1
dbo.Flag	User Defined Data Type		2
dbo.Name	User Defined Data Type		3
dbo.NameStyle	User Defined Data Type		3
Person.Person	Table	Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.	2

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[Document](
    [DocumentNode] [hierarchyid] NOT NULL,
    [DocumentLevel]  AS ([DocumentNode].[GetLevel]()),
    [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) 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],
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] TEXTIMAGE_ON [PRIMARY]

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]
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]

Related Objects