Table: [AdventureWorks2016CTP3].[Production].[Document]

CollapseAll image

Collapse image Table properties


Name  Value 
 Schema  [Production]
 Owner  [dbo]
 Table is replicated   
 Creation date  23.10.2015
 ID  1397580017
 Located on  PRIMARY
 Data size KB  336 KB
 Index size KB  176 KB
 Rows  13
 ChangeTrackingEnabled   
 FileStreamFileGroup   
 FileStreamPartitionScheme   
 LockEscalation  TABLE
 TrackColumnsUpdatedEnabled   
 Table is filetable   
 Filetable directory   
 Filetable collate   
 Filetable primary key  [PK_Document_DocumentNode]
 Filetable streamid unique   
 Filetable fullpath unique   
 Filetable is enabled   
 Is memory optimized   
 Durability  SCHEMA_AND_DATA

Collapse image Creation options


Name  Value 
QUOTED_IDENTIFIER ON
ANSI_NULLS ON
ANSI_PADDING ON

Collapse image Columns


    Name  Description  Data type  Max length  Nullable  Default IsGUID  SPARSE Computed value
Primary key   DocumentNode     hierarchyid 892        
    DocumentLevel   True smallint 2       ([DocumentNode].[GetLevel]())
    Title     nvarchar 50        
  Foreign key Owner     int 4        
    FolderFlag     bit 1   ((0))    
    FileName     nvarchar 400        
    FileExtension     nvarchar 8        
    Revision     nchar 5        
    ChangeNumber     int 4   ((0))    
    Status     tinyint 1        
    DocumentSummary     nvarchar 1073741823      
    Document     varbinary 2147483647      
    rowguid     uniqueidentifier 16   (newid())  
    ModifiedDate     datetime 8   (getdate())    
Total: 14 column(s)

Collapse image Indexes


Index  Description  Primary  Unique  Size KB 
[PK_Document_DocumentNode] DocumentNode   80 0
[UQ__Document__F73921F79196878C] rowguid     16 0
[AK_Document_DocumentLevel_DocumentNode] DocumentLevel     16 0
[AK_Document_rowguid] rowguid     16 0
[IX_Document_FileName_Revision] FileName       16 0
Total: 5 Index/indexes

Collapse image Columnstore indexes


No columnstore indexes exist

Collapse image Fulltext index


Fulltext index  Description  Size KB 
FULLTEXT_INDEX_Document   0

Collapse image Check constraints


Name  Expression 
CK_Document_Status ([Status]>=(1) AND [Status]<=(3))

Collapse image Referencing tables


Table  Foreign key  Primary key or unique constraint 
[Production].[ProductDocument] FK_ProductDocument_Document_DocumentNode PK_Document_DocumentNode
Total: 1 table(s)

Collapse image Referenced tables


Table  Primary key or unique constraint  Foreign key 
[HumanResources].[Employee] PK_Employee_BusinessEntityID FK_Document_Employee_Owner
Total: 1 table(s)

Collapse image Objects that depend on [Production].[Document]


Object name Object type Dep level
[Production].[ProductDocument] Table 1
Total 1 object(s)

Collapse image Objects that [Production].[Document] depends on


Object name Object type Dep level
[AW2014FullTextCatalog] Fulltext Catalog 1
[Production] Schema 1
[HumanResources].[Employee] Table 1
Total 3 object(s)

Collapse image Extended properties


Name  Value 
MS_Description Product maintenance documents.

Collapse image Table options


Name  Value 
Pintable OFF
Table lock on bulk load OFF
Insert row lock OFF
Text in row 0
Large value types out of row OFF

Collapse image SQL


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO
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] NOT NULL ROWGUIDCOL,
        [ModifiedDate]        [datetime] NOT NULL,
        CONSTRAINT [UQ__Document__F73921F79196878C]
        UNIQUE
        NONCLUSTERED
        ([rowguid])
        ON [PRIMARY],
        CONSTRAINT [PK_Document_DocumentNode]
        PRIMARY KEY
        CLUSTERED
        ([DocumentNode])
    ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
EXEC sp_addextendedproperty N'MS_Description', N'Primary key (clustered) constraint', 'SCHEMA', N'Production', 'TABLE', N'Document', 'CONSTRAINT', N'PK_Document_DocumentNode'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Clustered index created by a primary key constraint.', 'SCHEMA', N'Production', 'TABLE', N'Document', 'INDEX', N'PK_Document_DocumentNode'
GO
ALTER TABLE [Production].[Document]
    ADD
    CONSTRAINT [CK_Document_Status]
    CHECK
    ([Status]>=(1) AND [Status]<=(3))
GO
EXEC sp_addextendedproperty N'MS_Description', N'Check constraint [Status] BETWEEN (1) AND (3)', 'SCHEMA', N'Production', 'TABLE', N'Document', 'CONSTRAINT', N'CK_Document_Status'
GO
ALTER TABLE [Production].[Document]
CHECK CONSTRAINT [CK_Document_Status]
GO
ALTER TABLE [Production].[Document]
    ADD
    CONSTRAINT [DF_Document_ChangeNumber]
    DEFAULT ((0)) FOR [ChangeNumber]
GO
EXEC sp_addextendedproperty N'MS_Description', N'Default constraint value of 0', 'SCHEMA', N'Production', 'TABLE', N'Document', 'CONSTRAINT', N'DF_Document_ChangeNumber'
GO
ALTER TABLE [Production].[Document]
    ADD
    CONSTRAINT [DF_Document_FolderFlag]
    DEFAULT ((0)) FOR [FolderFlag]
GO
ALTER TABLE [Production].[Document]
    ADD
    CONSTRAINT [DF_Document_ModifiedDate]
    DEFAULT (getdate()) FOR [ModifiedDate]
GO
EXEC sp_addextendedproperty N'MS_Description', N'Default constraint value of GETDATE()', 'SCHEMA', N'Production', 'TABLE', N'Document', 'CONSTRAINT', N'DF_Document_ModifiedDate'
GO
ALTER TABLE [Production].[Document]
    ADD
    CONSTRAINT [DF_Document_rowguid]
    DEFAULT (newid()) FOR [rowguid]
GO
EXEC sp_addextendedproperty N'MS_Description', N'Default constraint value of NEWID()', 'SCHEMA', N'Production', 'TABLE', N'Document', 'CONSTRAINT', N'DF_Document_rowguid'
GO
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]

GO
EXEC sp_addextendedproperty N'MS_Description', N'Foreign key constraint referencing Employee.BusinessEntityID.', 'SCHEMA', N'Production', 'TABLE', N'Document', 'CONSTRAINT', N'FK_Document_Employee_Owner'
GO
CREATE UNIQUE NONCLUSTERED INDEX [AK_Document_DocumentLevel_DocumentNode]
    ON [Production].[Document] ([DocumentLevel], [DocumentNode])
    ON [PRIMARY]
GO
EXEC sp_addextendedproperty N'MS_Description', N'Unique nonclustered index.', 'SCHEMA', N'Production', 'TABLE', N'Document', 'INDEX', N'AK_Document_DocumentLevel_DocumentNode'
GO
CREATE UNIQUE NONCLUSTERED INDEX [AK_Document_rowguid]
    ON [Production].[Document] ([rowguid])
    ON [PRIMARY]
GO
EXEC sp_addextendedproperty N'MS_Description', N'Unique nonclustered index. Used to support FileStream.', 'SCHEMA', N'Production', 'TABLE', N'Document', 'INDEX', N'AK_Document_rowguid'
GO
CREATE NONCLUSTERED INDEX [IX_Document_FileName_Revision]
    ON [Production].[Document] ([FileName], [Revision])
    ON [PRIMARY]
GO
EXEC sp_addextendedproperty N'MS_Description', N'Unique nonclustered index.', 'SCHEMA', N'Production', 'TABLE', N'Document', 'INDEX', N'IX_Document_FileName_Revision'
GO
CREATE FULLTEXT INDEX ON [Production].[Document]
    ([DocumentSummary] LANGUAGE 1033, [Document] TYPE COLUMN [FileExtension] LANGUAGE 1033)
    KEY INDEX [PK_Document_DocumentNode]
    ON (FILEGROUP [PRIMARY], [AW2014FullTextCatalog])
    WITH CHANGE_TRACKING AUTO, STOPLIST SYSTEM
GO
EXEC sp_addextendedproperty N'MS_Description', N'Engineering change approval number.', 'SCHEMA', N'Production', 'TABLE', N'Document', 'COLUMN', N'ChangeNumber'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Complete document.', 'SCHEMA', N'Production', 'TABLE', N'Document', 'COLUMN', N'Document'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Depth in the document hierarchy.', 'SCHEMA', N'Production', 'TABLE', N'Document', 'COLUMN', N'DocumentLevel'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Primary key for Document records.', 'SCHEMA', N'Production', 'TABLE', N'Document', 'COLUMN', N'DocumentNode'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Document abstract.', 'SCHEMA', N'Production', 'TABLE', N'Document', 'COLUMN', N'DocumentSummary'
GO
EXEC sp_addextendedproperty N'MS_Description', N'File extension indicating the document type. For example, .doc or .txt.', 'SCHEMA', N'Production', 'TABLE', N'Document', 'COLUMN', N'FileExtension'
GO
EXEC sp_addextendedproperty N'MS_Description', N'File name of the document', 'SCHEMA', N'Production', 'TABLE', N'Document', 'COLUMN', N'FileName'
GO
EXEC sp_addextendedproperty N'MS_Description', N'0 = This is a folder, 1 = This is a document.', 'SCHEMA', N'Production', 'TABLE', N'Document', 'COLUMN', N'FolderFlag'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Date and time the record was last updated.', 'SCHEMA', N'Production', 'TABLE', N'Document', 'COLUMN', N'ModifiedDate'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Employee who controls the document.  Foreign key to Employee.BusinessEntityID', 'SCHEMA', N'Production', 'TABLE', N'Document', 'COLUMN', N'Owner'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Revision number of the document. ', 'SCHEMA', N'Production', 'TABLE', N'Document', 'COLUMN', N'Revision'
GO
EXEC sp_addextendedproperty N'MS_Description', N'ROWGUIDCOL number uniquely identifying the record. Required for FileStream.', 'SCHEMA', N'Production', 'TABLE', N'Document', 'COLUMN', N'rowguid'
GO
EXEC sp_addextendedproperty N'MS_Description', N'1 = Pending approval, 2 = Approved, 3 = Obsolete', 'SCHEMA', N'Production', 'TABLE', N'Document', 'COLUMN', N'Status'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Title of the document.', 'SCHEMA', N'Production', 'TABLE', N'Document', 'COLUMN', N'Title'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Product maintenance documents.', 'SCHEMA', N'Production', 'TABLE', N'Document', NULL, NULL
GO

Collapse image See also


List of tables