Tables Wizard (SQL Server)

From DBArtisan
Jump to: navigation, search

Go Up to Microsoft SQL Server Object Wizards

A table is a column-based arrangement of data in which the content of one column has a bearing on the other column(s). So, for example, a table might have a column for authors, another column for the books each author has written, and a third for the number of copies each title by a given author has sold. The data moves across the columns in rows.

You must have CREATE TABLE permissions to generate a new table.

Note: Before working with tables, consult Microsoft SQL Server documentation for general information on filegroups as well as specifics on CREATE TABLE... FILESTREAM and FILETABLE arguments. For more information, see Accessing Third Party Documentation.

To create a new table using a wizard:

  1. Open a creation wizard for a table. For details, see Opening an Object Wizard.
  2. Use the following topics as a guide to setting properties and performing tasks as you pass through the wizard panels:
  3. Finally, use the Execute button to create the object.

Tables (SQL Server) - Properties

When creating or editing a table, this tab/panel lets you work with the following settings:

Setting Description

Schema

Select the owner of the table.

Name

Provide a name for the table

ANSI_NULLS option

By setting this option, you are setting ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, and QUOTED_IDENTIFIER to "on" so the table can be used in an Indexed View.

Partitioned

When selected, an ON clause is added to the CREATE TABLE statement, letting you specify a Partition Scheme, partitioning this index.

Partition Scheme

This property is only available if the Partitioned check box is selected. Select the partition scheme that specifies the filegroup mapping for this index. For information on creating partition schemes, see Partition Schemes Wizard (SQL Server).

Filegroup

This property is only available if the Partitioned check box is not selected. Select an existing, named filegroup to have the table stored on the specified filegroup. Select PRIMARY to have the table stored on the default database filegroup.

Text Image Filegroup

Select an existing, named filegroup to have text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max), and CLR user-defined type columns stored on the specified filegroup. Select PRIMARY to have columns of those types stored on the default database filegroup.

Filestream On (Version 2008^)

This lets you specify the filegroup for FileStream data, specifying a FILESTREAM_ON argument to the generated DDL. This control is only available if a filegroup that can contain filestreams is available in the database. For more on filegroups, see Databases Wizard (SQL Server).

In Memory Option

By setting this option, you are setting MEMORY_OPTIMIZED to "on" to optimize performance of transaction processing, data ingestion, data load, and transient data scenarios.

Durability

Lets you select SCHEMA_ONLY or SCHEMA_AND_DATA as the level of optimization desired.

As Filetable and other Filetable... properties (Version 2012^)

As Filetable lets you add an AS FILETABLE argument to the generated DDL, specifying that the table is created as a FileTable. The Columns, Indexes, and Constraints tabs/panels are disabled when this control is selected. Filetable Collate Filename lets you provide a FILETABLE_COLLATE_FILENAME argument value, specifying the name of the collation used. If no value is provided, the default collation for the database is used. Filetable Directory Name lets you provide a FILETABLE_DIRECTORY argument value, specifying a FileTable directory name. If no value is provided, the name of the filetable is used. Filetable PK Constraint Name lets you provide a FILETABLE_PRIMARY_KEY_CONSTRAINT_NAME argument value, specifying the name used for the primary key constraint automatically created on the FileTable. If this value is not provided, a system-generated name is used. Filetable StreamID Unique Constraint Name lets you provide a FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME argument value, specifying the name used for the unique key constraint automatically created on the FileTable. If this value is not provided, a system-generated name is used. Filetable Fullpath Unique Coonstraint Name lets you provide a FILETABLE_FULLPATH_UNIQUE_CONSTRAINT_NAME argument value, specifying the name used for the unique constraint automatically created on the parent_path_locator and name columns in the FileTable. If this value is not provided, a system-generated name is used.

Tables (SQL Server) - Columns

Use the Add Column button to add the columns for the table. After providing a Name for a new column, you can modify column properties in the Property/Value list. Available properties depend on the datatype you choose as well as on the property values you select:

  • Computed and Computed Expression - Let you define a column as a computed column and provide the computed column expression.
  • Type - Lets you select a datatype (depending on the type, additional properties such as Size, Width, and Scale may be available).
  • Identity Column - Select this check box to define the column as an identity column.
  • Allow Nulls - Select this check box to allow nulls in this column.
  • Encrypted - Select this check box to activate encryption in this column.
  • Encryption Algorithm - Select the encryption algorithm you want to apply to this column.
  • Encryption Type - Select the encryption type, DETERMINISTIC or RANDOMIZED that you want to apply in this column.
  • Default Collation - available for text/character datatypes, lets you specify a default collation.
  • Default Value - Lets you type a constant value or select a function returning a constant value to serve as the default for the column
  • Default Binding and Rule Binding - Let you bind a rule or default to a column.
  • Is Sparse - Available to columns that allow NULL values, optimizes storage of the column for null values. This property does apply to the following data types: text, ntext, image, timestamp, user-defined data type, geometry, or geography. Columns with default values, default or rule bindings, cannot be defined as sparse. Computed columns cannot be defined as Sparse, but the columns in the computed expression can be Sparse columns.

Optionally, you can select a column and modify its values or select a column and Delete it.

Note: If you create a table with a Column datatype = text., you can set the storage and image values on the Storage tab of the Tables Editor Storage tab. When you have a text datatype, the Storage tab displays a Text In Row box where you can specify the maximum size to be stored.
Note: Because the smalldatetime datatype stores dates and time with less precision than the datetime datatype, before outputting you use the CAST or CONVERT functions to convert any boxes with the smalldatetime datatype to either VARCHAR or datetime datatypes. For more information, see SQL Server Books Online, Transact-SQL Reference.
Note: This tab/panel is not available if As Filetable is selected. For details, see Tables (SQL Server) - Properties.
Note: When using Data Masking function type of PARTIAL, select the Prefix, Suffix, and Padding to mask only part of your data. Select RANDOM to mask random positions within a set range of characters.

Tables (SQL Server) - Data Compression

Right-click the partition line to change the data compression type and reduce the size of your database. Select Change Compression Type, and then select whether you want to compress to ROW, PAGE, or NONE. Note that page compression automatically implements row compression. You can compress tables and indexes when they are created by using the CREATE TABLE and CREATE INDEX statements.

Tables (SQL Server) - Indexes

Click Add to open the Index Wizard. For details, see Indexes Wizard (SQL Server).

Note: This tab/panel is not available if As Filetable is selected. For details, see Tables (SQL Server) - Properties.

Tables (SQL Server) - Temporal Properties

Selecting a temporal datatype, such as datetime2, allows you to create a temporal table. A system-versioned temporal table is a type of user table designed to keep a full history of data changes and allow easy point in time analysis. This type of temporal table is referred to as a system-versioned temporal table because the period of validity for each row is managed by the system.

A system-versioned temporal table must have a primate key defined and have exactly one PERIOD FOR SYSTEM_TIME defined with two datetime2 columns, declared as GENERATED ALWAYS AS ROW START / END.

The PERIOD columns are always assumed to be non-nullable, even if nullability is not specified. If the PERIOD columns are explicitly defined as nullable, the CREATE TABLE state will fail.

Tables (SQL Server) - Constraints

Selecting a constraint type and clicking Add opens the object wizard for that object type. For details see:

Note: This tab/panel is not available if As Filetable is selected. For details, see Tables (SQL Server) - Properties.