Create and Alter Views
Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.
Tip: You can hide system tables from the list of tables in Tools | Options | Database | SQL Server. See SQL Server Options for more information.
To create or alter a view
- Select Views node under the Databases node in the Object Explorer.
- Select one of the following:
- Click
to create a new view.
- Select a view and click
to create a view like the selected one.
- Select a view and click
to alter it.
- Click
-
Complete the fields as necessary. Review the following for additional information:
Attributes
Description Encryption
Select True if you do not want the view published during SQL Server replication.
Schema binding
Select True to bind the view to the schema.
Note: If you select this checkbox, you cannot drop any tables or views that are included in this view, unless you drop this view or remove the schema binding.
In addition, you cannot alter tables included in this view if altering the table would affect the view.
View metadata
Select True to include the VIEW_METADATA clause in the SQL statement.
With check option
Select True to include the WITH CHECK OPTION clause in the SQL statement.
-
Enter a SELECT statement to define the view in the text field. Your statement can include views as well as tables.
-
Click Check Syntax to validate the syntax for the view.
Indexes
Create and Alter Indexes
This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.To create or alter index
-
Select the Indexes node in the Object Explorer.
Note: You can also right-click a table in the Tables node and select View Details | Indexes.
-
Select one of the following:
- Click
to create an index.
- Select an index and click
to create an index like the selected one.
- Select an index and click
to alter it.
- Click
-
Complete the fields as necessary. Review the following for additional information:
General Page Description Available columns
Select a column and click
to add it to the index.
Note: You can also modify these columns from the Included Columns page.
Options Page
Description Ignore duplicate values
Select True to insert any non-duplicate key values into the table and reject any duplicate values. If this option set to False, all values are rejected if any duplicate values are found.
Pad index
Select True to fill index according to specified fill factor. If this option set to False, the index is filled to near capacity, leaving at least one row that is the maximum size the index supports. Fill factor
Select True and enter a percentage for the fill factor.
Filegroup
Select the filegroup where this index is stored.
Tips:
- Right-click the object to see additional actions and tasks available for it.
- Right-click an index and select Tasks | <action> to enable, disable, check indexes or update their statistics.
- Right-click an index and select Tasks | Defragment Index to receive index statistics. See Defragment Indexes for more information.
- (SQL Server 2012 and above) You can select Columnstore from the Index Type list in the General page. See the Microsoft® Developer Network for more information on columnar indexes. Columnstore indexes are marked with
icon.
Partition Indexes
Partitioning an index arranges the data into smaller, more manageable pieces. The CPU can perform operations on more than one partition simultaneously, which improves performance. A very large database performs best if the partitions use multiple disks, separate filegroups, and preferably multiple CPUs.
Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.
To partition an index
-
Right-click a table from the Tables node in the Object Explorer and select View Details | Indexes.
- Select an index and click
.
-
Complete the wizard. Review the following for additional information:
Select Partition Key
Description
Column name
Select a column to use to define the data on which to key the partition. The column must be unique and it must be a column in an existing cluster index. The data type of the key column determines the data type of the partition function that separates the data into partitions.
Number of partitions
Enter the number of partitions to create. The number of partitions determines the recommended number of filegroups. Although you can use the same filegroup for several partitions, best-practice is to use a separate filegroup for each partition. Notes:
- The corresponding table becomes a partitioned table and is partitioned using the same filegroups and boundaries as the index.
- You can right-click a partitioned index and select an option to manage or undo the index partition.