Managing Database Objects on the Objects Tab

On the Objects tab, you can add and drop databases, tables, and indexes; and define many of the most common properties of tables and indexes when you create them. For more specific table and index definition requirements, however, you must execute an SQL statement on the SQL tab.

To create a database

1. On the Objects tab, select the Databases folder, and then tap the database connection button. The button must show a green arrow. If the button shows a red square, you are already connected to a database. Tap the button to disconnect. Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition) permits only one database connection at a time.

2. In the Connect to SQL Server Compact Edition dialog box, enter the path and name of the database, and then tap New Database.

3. In the Password box, enter a password if you want the database to be password-protected. In the Sort box, select a database collation. The default collation, General, corresponds to Latin1_general. To encrypt the database, select Encrypt.

   Note

   Encryption must be used in conjunction with a password. Other conditions apply to implement encryption. For more information about passwords and encryption in SQL Server Compact Edition, see Securing Databases (SQL Server Compact Edition).

4. Tap Create.

To create a table

1. On the Objects tab, select either the database to which you are connected or the Tables folder of the database connection, and then tap the create table button.

2. In the Table Definition dialog box, enter a name for the table, and then tap Insert Column.

3. In the Column Definition dialog box, specify the properties of a column. The properties include the following: column name, data type, length (if applicable), nullability, precision, scale, and identity. You can also define a UNIQUE or PRIMARY KEY constraint and a default value for the column.

   Note

   When you create a table with a UNIQUE or PRIMARY KEY constraint, SQL Server Compact Edition automatically creates a unique index on that table. For more information, see Viewing and Modifying Index Properties.

   For information about the data types that SQL Server Compact Edition supports, see Data Types

4. To return to the Table Definition dialog box, tap OK. Either repeat the process for additional columns, or tap OK to finish.

Tap to create a table.

To add a column to a table

1. On the Objects tab, connect to a database, and expand the Tables folder.

2. Expand the table you want to add a column to, select the Columns folder, and then tap the add column definition button.

3. In the Column Definition dialog box, specify the properties of a column. The properties include the following: column name, data type, length (if applicable), nullability, precision, scale, and identity. You can also define a UNIQUE or PRIMARY KEY constraint and a default value for the column. To create the column, tap OK.

   Note

   When you create a table with a UNIQUE or PRIMARY KEY constraint, SQL Server Compact Edition automatically creates a unique index on that table. For more information, see Viewing and Modifying Index Properties.

Tap to add a column to a table.

For information about the data types that SQL Server Compact Edition supports, see Data Types

To execute a SELECT * FROM table_name statement

1. On the Objects tab, connect to a database, expand the Table folder, and then select a table.
2. Tap the execute button. The results of the SELECT * FROM table_name statement appear on the Grid tab, and the messages appear on the Notes tab.

Tab to execute a SELECT * FROM table_name statement.

To show/hide system tables

• On the Objects tab, connect to a database, select the Tables folder, and then tap the system table button.

	Tap to view system tables.
	Tap to hide system tables.

To create an index

1. On the Objects tab, connect to a database, and then expand the Tables folder.
2. In the Tables folder, select the table you want to index, or expand that table and select the Indexes folder, and then tap the create index button.
3. In the Index Definition dialog box, enter a name for the index. If the index is unique, select Unique.
4. In the Columns list, select the columns you want to base the index on. If you want the index sorted on that column in ascending order, tap A->. If you want the index sorted on that column in descending order, tap D->. To remove a column from the list of index keys, select the column and tap <-.
5. Tap OK.

Tab to create an index.

For information about how to view and modify index properties, see Viewing and Modifying Index Properties.

To delete databases and database objects

• To delete databases and database objects on the Objects tab, select the object you want to delete, and then tap the delete button. For example, if you want to drop the Territories table from the Northwind sample database, select Territories and tap the delete button. If you only want to drop the TerritoryDescription column of the Territories table, select TerritoryDescription and tap the delete button. In both cases, a warning message appears prompting whether you are sure you want to drop the object.

Tap to delete a database object.

In This Section