建立、改變及卸除 FileTableCreate, Alter, and Drop FileTables

適用於: 是SQL Server 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

描述如何建立新的 FileTable,或是改變或卸除現有的 FileTable。Describes how to create a new FileTable, or alter or drop an existing FileTable.

建立 FileTableCreating a FileTable

FileTable 是一種特殊化使用者資料表,它具有預先定義且固定的結構描述。A FileTable is a specialized user table that has a pre-defined and fixed schema. 這個結構描述會儲存 FILESTREAM 資料、檔案和目錄資訊,以及檔案屬性。This schema stores FILESTREAM data, file and directory information, and file attributes. 如需有關 FileTable 結構描述的詳細資訊,請參閱< FileTable Schema>。For information about the FileTable schema, see FileTable Schema.

您可以使用 Transact-SQL 或 SQL Server Management StudioSQL Server Management Studio來建立新的 FileTable。You can create a new FileTable by using Transact-SQL or SQL Server Management StudioSQL Server Management Studio. 因為 FileTable 具有固定的結構描述,所以您不需要指定資料行的清單。Since a FileTable has a fixed schema, you do not have to specify a list of columns. 用於建立 FileTable 的簡單語法可讓您指定:The simple syntax for creating a FileTable lets you specify:

  • 目錄名稱。A directory name. 在 FileTable 資料夾階層中,這個資料表層級目錄會成為在資料庫層級中指定之資料庫目錄的子系,以及儲存在資料表中之檔案或目錄的父系。In the FileTable folder hierarchy, this table-level directory becomes the child of the database directory specified at the database level, and the parent of the files or directories stored in the table.

  • 要用於 FileTable Name 資料行中之檔案名稱的定序名稱。The name of the collation to be used for file names in the Name column of the FileTable.

  • 要用於 3 個自動建立的主索引鍵條件約束和唯一條件約束的名稱。The names to be used for the 3 primary key and unique constraints that are automatically created.

如何:建立 FileTableHow To: Create a FileTable

使用 Transact-SQL 建立 FileTableCreate a FileTable by Using Transact-SQL
您可以使用 AS FileTable 選項來呼叫 CREATE TABLE (Transact-SQL) 陳述式,藉以建立 FileTable。Create a FileTable by calling the CREATE TABLE (Transact-SQL) statement with the AS FileTable option. 因為 FileTable 具有固定的結構描述,所以您不需要指定資料行的清單。Since a FileTable has a fixed schema, you do not have to specify a list of columns. 您可以針對新的 FileTable 指定下列設定:You can specify the following settings for the new FileTable:

  1. FILETABLE_DIRECTORYFILETABLE_DIRECTORY. 指定目錄,以做為 FileTable 中儲存之所有檔案和目錄的根目錄。Specifies the directory that serves as the root directory for all the files and directories stored in the FileTable. 在資料庫的所有 FileTable 目錄名稱之間,此名稱必須是唯一的。This name should be unique among all the FileTable directory names in the database. 不論目前的定序設定為何,唯一性的比較都不區分大小寫。Comparison for uniqueness is case-insensitive, regardless of the current collation settings.

    • 此值的資料類型為 nvarchar(255) ,並使用 Latin1_General_CI_AS_KS_WS的固定定序。This value has a data type of nvarchar(255) and uses a fixed collation of Latin1_General_CI_AS_KS_WS.

    • 您所提供的目錄名稱必須符合有效目錄名稱的檔案系統需求。The directory name that you provide must comply with the requirements of the file system for a valid directory name.

    • 在資料庫的所有 FileTable 目錄名稱之間,此名稱必須是唯一的。This name should be unique among all the FileTable directory names in the database. 不論目前的定序設定為何,唯一性的比較都不區分大小寫。Comparison for uniqueness is case-insensitive, regardless of the current collation settings.

    • 如果您在建立 FileTable 時未提供目錄名稱,則會使用 FileTable 本身的名稱做為目錄名稱。If you do not provide a directory name when you create the FileTable, then the name of the FileTable itself is used as the directory name.

  2. FILETABLE_COLLATE_FILENAMEFILETABLE_COLLATE_FILENAME. 指定定序名稱,用於套用至 FileTable 中的 Name 資料行。Specifies the name of the collation to be applied to the Name column in the FileTable.

    1. 指定的定序必須 不區分大小寫 ,以符合 Windows 檔案命名語意。The specified collation must be case-insensitive to comply with Windows file naming semantics.

    2. 如果您未提供 FILETABLE_COLLATE_FILENAME的值,或指定 database_default,則資料行會繼承目前資料庫的定序。If you do not provide a value for FILETABLE_COLLATE_FILENAME, or you specify database_default, the column inherits the collation of the current database. 如果目前的資料庫定序區分大小寫,則會引發錯誤,而且 CREATE TABLE 作業會失敗。If the current database collation is case-sensitive, an error is raised and the CREATE TABLE operation fails.

  3. 您也可以指定要用於 3 個自動建立的主索引鍵條件約束和唯一條件約束的名稱。You can also specify the names to be used for the 3 primary key and unique constraints that are automatically created. 如果您未提供名稱,則系統會產生名稱,如本主題稍後所述。If you do not provide names, then the system generates names as described later in this topic.

    • FILETABLE_PRIMARY_KEY_CONSTRAINT_NAMEFILETABLE_PRIMARY_KEY_CONSTRAINT_NAME

    • FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAMEFILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME

    • FILETABLE_FULLPATH_UNIQUE_CONSTRAINT_NAMEFILETABLE_FULLPATH_UNIQUE_CONSTRAINT_NAME

範例Examples

下列範例會建立新的 FileTable,並指定 FILETABLE_DIRECTORYFILETABLE_COLLATE_FILENAME的使用者定義值。The following example creates a new FileTable and specifies user-defined values for both FILETABLE_DIRECTORY and FILETABLE_COLLATE_FILENAME.

CREATE TABLE DocumentStore AS FileTable  
    WITH (   
          FileTable_Directory = 'DocumentTable',  
          FileTable_Collate_Filename = database_default  
         );  
GO  

下列範例也會建立新的 FileTable。The following example also creates a new FileTable. 因為未指定使用者定義值,所以 FILETABLE_DIRECTORY 的值會成為 FileTable 的名稱, FILETABLE_COLLATE_FILENAME 的值會成為 database_default,而主索引鍵和唯一條件約束則會接受系統產生的名稱。Since user-defined values are not specified, the value of FILETABLE_DIRECTORY becomes the name of the FileTable, the value of FILETABLE_COLLATE_FILENAME becomes database_default, and the primary key and unique contraints receive system-generated names.

CREATE TABLE DocumentStore AS FileTable;  
GO  

使用 SQL Server Management Studio 建立 FileTableCreate a FileTable by Using SQL Server Management Studio
在物件總管中,展開選取之資料庫底下的物件、以滑鼠右鍵按一下 [資料表] 資料夾,然後選取 [New FileTable (新增 FileTable)] 。In Object Explorer, expand the objects under the selected database, then right-click on the Tables folder, and then select New FileTable.

此選項會開啟新的指令碼視窗,其中包含您可以自訂及執行以建立 FileTable 的 Transact-SQL 指令碼範本。This option opens a new script window which contains a Transact-SQL script template that you can customize and run to create a FileTable. 使用 [查詢] 功能表上的 [指定範本參數的值] 選項,輕鬆地自訂指令碼。Use the Specify Values for Template Parameters option on the Query menu to customize the script easily.

建立 FileTable 的需求和限制Requirements and Restrictions for Creating a FileTable

  • 您無法改變現有的資料表,以便將它轉換成 FileTable。You cannot alter an existing table to convert it into a FileTable.

  • 先前在資料庫層級中指定的上層目錄必須具有非 Null 值。The parent directory previously specified at the database level must have a non-null value. 如需指定資料庫層級目錄的相關資訊,請參閱 啟用 FileTable 的必要條件For information about specifying the database-level directory, see Enable the Prerequisites for FileTable.

  • 因為 FileTable 包含 FILESTREAM 資料行,所以 FileTable 需要使用有效的 FILESTREAM 檔案群組。A FileTable requires a valid FILESTREAM filegroup, since a FileTable contains a FILESTREAM column. 您可以選擇性地指定有效的 FILESTREAM 檔案群組,當做建立 FileTable 之 CREATE TABLE 命令的一部分。You can optionally specify a valid FILESTREAM filegroup as part of the CREATE TABLE command for creating a FileTable. 如果您沒有指定檔案群組,則 FileTable 就會使用資料庫的預設 FILESTREAM 檔案群組。If you do not specify a filegroup, then the FileTable uses the default FILESTREAM filegroup for the database. 如果資料庫沒有 FILESTREAM 檔案群組,則系統會引發錯誤。If the database does not have a FILESTREAM filegroup, then an error is raised.

  • 您無法將資料表條件約束建立成 CREATE TABLE…AS FILETABLE 陳述式的一部分。You cannot create a table constraint as part of a CREATE TABLE...AS FILETABLE statement. 不過,您之後可以使用 ALTER TABLE 陳述式來加入條件約束。However you can add the constraint later by using an ALTER TABLE statement.

  • 您無法在 tempdb 資料庫或任何其他系統資料庫中建立 FileTable。You cannot create a FileTable in the tempdb database or in any of the other system databases.

  • 您無法將 FileTable 建立成暫存資料表。You cannot create a FileTable as a temporary table.

改變 FileTableAltering a FileTable

因為 FileTable 具有預先定義且固定的結構描述,所以您無法加入或變更其資料行。Since a FileTable has a pre-defined and fixed schema, you cannot add or change its columns. 不過,您可以在 FileTable 中加入自訂索引、觸發程序、條件約束及其他選項。However, you can add custom indexes, triggers, constraints, and other options to a FileTable.

如需使用 ALTER TABLE 陳述式啟用或停用 FileTable 命名空間 (包括系統定義的條件約束) 的相關資訊,請參閱 管理 FileTableFor information about using the ALTER TABLE statement to enable or disable the FileTable namespace, including the system-defined constraints, see Manage FileTables.

如何:變更 FileTable 的目錄How To: Change the Directory for a FileTable

使用 Transact-SQL 變更 FileTable 的目錄Change the Directory for a FileTable by Using Transact-SQL
呼叫 ALTER TABLE 陳述式,並提供有效的新 FILETABLE_DIRECTORY SET 選項值。Call the ALTER TABLE statement and provide a valid new value for the FILETABLE_DIRECTORY SET option.

範例Example

ALTER TABLE filetable_name  
    SET ( FILETABLE_DIRECTORY = N'directory_name' );  
GO  

使用 SQL Server Management Studio 變更 FileTable 的目錄Change the Directory for a FileTable by Using SQL Server Management Studio
在物件總管中,以滑鼠右鍵按一下 [FileTable],並選取 [屬性] 開啟 [資料表屬性] 對話方塊。In Object Explorer, right-click on the FileTable and select Properties to open the Table Properties dialog box. [FileTable] 頁面上,輸入 [FileTable 目錄名稱] 的新值。On the FileTable page, enter a new value for FileTable directory name.

改變 FileTable 的需求和限制Requirements and Restrictions for Altering a FileTable

  • 您無法改變 FILETABLE_COLLATE_FILENAME的值。You cannot alter the value of FILETABLE_COLLATE_FILENAME.

  • 您無法變更、卸除或停用 FileTable 的系統定義資料行。You cannot change, drop, or disable the system-defined columns of a FileTable.

  • 您無法將新的使用者資料行、計算資料行或已保存的計算資料行加入至 FileTable。You cannot add new user columns, computed columns, or persisted computed columns to a FileTable.

卸除 FileTableDropping a FileTable

您可以使用 DROP TABLE (Transact-SQL) 陳述式的一般語法來卸除 FileTable。You can drop a FileTable by using the ordinary syntax for the DROP TABLE (Transact-SQL) statement.

當您卸除 FileTable 時,也會一併卸除下列物件:When you drop a FileTable, the following objects are also dropped:

  • 也會一併卸除 FileTable 的所有資料行以及與資料表相關聯的所有物件,例如索引、條件約束及觸發程序。All the columns of the FileTable and all the objects associated with the table, such as indexes, constraints, and triggers, are also dropped.

  • FileTable 目錄和其所含的子目錄會從資料庫的 FILESTREAM 檔案及目錄階層中消失。The FileTable directory and the sub-directories that it contained disappear from the FILESTREAM file and directory hierarchy of the database.

如果 FileTable 的檔案命名空間中存在開啟的檔案控制代碼,DROP TABLE 命令就會失敗。The DROP TABLE command fails if there are open file handles in the FileTable's file namespace. 如需關閉開啟之控制代碼的相關資訊,請參閱 管理 FileTableFor information about closing open handles, see Manage FileTables.

當您建立 FileTable 時也會建立其他資料庫物件Other Database Objects Are Created When You Create a FileTable

當您建立新的 FileTable 時,也會建立一些系統定義的索引和條件約束。When you create a new FileTable, some system-defined indexes and constraints are also created. 您不能改變或卸除這些物件,只有當 FileTable 本身卸除時它們才會消失。You cannot alter or drop these objects; they disappear only when the FileTable itself is dropped. 若要查看這些物件的清單,請查詢目錄檢視 sys.filetable_system_defined_objects (Transact-SQL)To see the list of these objects, query the catalog view sys.filetable_system_defined_objects (Transact-SQL).

--View all objects for all filetables, unsorted  
SELECT * FROM sys.filetable_system_defined_objects;  
GO  
  
--View sorted list with friendly names  
SELECT OBJECT_NAME(parent_object_id) AS 'FileTable', OBJECT_NAME(object_id) AS 'System-defined Object'  
    FROM sys.filetable_system_defined_objects  
    ORDER BY FileTable, 'System-defined Object';  
GO  

建立新 FileTable 時所建立的索引Indexes that are created when you create a new FileTable
當您建立新的 FileTable 時,也會建立下列系統定義的索引:When you create a new FileTable, the following system-defined indexes are also created:

資料行Columns 索引類型Index type
[path_locator] ASC[path_locator] ASC 主索引鍵,非叢集Primary Key, nonclustered
[parent_path_locator] ASC、[parent_path_locator] ASC,

[name] ASC[name] ASC
唯一,非叢集Unique, nonclustered
[stream_id] ASC[stream_id] ASC 唯一,非叢集Unique, nonclustered

建立新 FileTable 時所建立的條件約束Constraints that are created when you create a new FileTable
當您建立新的 FileTable 時,也會建立下列系統定義的條件約束:When you create a new FileTable, the following system-defined constraints are also created:

條件約束Constraints 強制執行Enforces
下列資料行的預設條件約束:Default constraints on the following columns:

creation_timecreation_time

is_archiveis_archive

is_directoryis_directory

is_hiddenis_hidden

is_offlineis_offline

is_readonlyis_readonly

is_systemis_system

is_temporaryis_temporary

last_access_timelast_access_time

last_write_timelast_write_time

path_locatorpath_locator

stream_idstream_id
系統定義的預設條件約束會針對指定的資料行強制執行預設值。The system-defined default constraints enforce default values for the specified columns.
檢查條件約束Check constraints 系統定義的檢查條件約束會強制執行下列需求:The system-defined check constraints enforce the following requirements:

有效的檔案名稱。Valid filenames.

有效的檔案屬性。Valid file attributes.

父物件必須是目錄。Parent object must be a directory.

在檔案操作期間,會鎖定命名空間階層。Namespace hierarchy is locked during file manipulation.

系統定義之條件約束的命名慣例Naming convention for the system-defined constraints
上述之系統定義條件約束的命名格式為 <條件約束類型>_<資料表名稱>[_<資料行名稱>]_<唯一碼> ,其中:The system-defined constaints described above are named in the format <constraintType>_<tablename>[_<columnname>]_<uniquifier> where:

  • 其中的 <條件約束類型> 是 CK (檢查條件約束)、DF (預設條件約束)、FK (外部索引鍵)、PK (主索引鍵) 或 UQ (唯一條件約束)。<constraint_type> is CK (check constraint), DF (default constraint), FK (foreign key), PK (primary key), or UQ (unique constraint).

  • <唯一碼> 是讓名稱成為唯一名稱的系統產生字串。<uniquifier> is a system-generated string to make the name unique. 這個字串可能會包含 FileTable 名稱和唯一識別碼。This string may contain the FileTable name and a unique identifier.

另請參閱See Also

管理 FileTableManage FileTables