Microsoft SQL Server provides various means and tools to create tables. First of all, a table must belong to a database. This means that you must first choose the database that will own the table.
One way to create a table is to design it. To do this, in the Object Explorer, expand the Databases node and expand the database to which the table will belong. Right-click the Tables node and click New Table...
This would create a Table window.
After creating a table, it becomes part of its database and you can use that table over and over again. In some cases, you may want to create a table to test something and you would not need to use that table the next time you connect to the server. Such a table is referred to as a temporary table.
To create a temporary table, use the poind sign, #, after CREATE TABLE. Once the table has been created, it would be available as long as you are using the same connection to the server. If you close Microsoft SQL Server, the table would be automatically deleted.
To assist you with creating a table, Microsoft SQL Server can generate sample code for you. You can then simply modify or customize it. First display or open an empty Query Editor. To display the Templates Explorer, on the main menu, click View -> Templates Explorer. In the Templates Explorer, expand the Table node. Under Table, drag Create Table and drop it in the Query Editor. Sample code would be generated for you.
When creating a table, you must give it a name. If you are visually creating a table, if you decide to close it, you would be asked whether you want to save it. If you click Yes, you would be asked to give it a name.
The name of a table:
Besides these rules, you can make up yours. To avoid confusion, here are the rules we will use to name our tables:
As mentioned already, a schema is an object that contains other objects, such as tables. In fact, every table in Microsoft SQL Server must belong to a schema. When you are creating a table, you have the option of specifying or not indicating its schema. If you don't specify a schema, the default dbo schema would own it.
To visually specify the schema that will contain a table, in the Properties window, click Schema, then click the arrow of its combo box and select the desired schema:
To specify the schema of a table using code, precede its name with the name of the schema followed by a period. The formula to use is:
CREATE TABLE SchemaName.TableName....
An example would be:
CREATE SCHEMA Registration; GO CREATE TABLE Registration.Students . . .
Remember that if you don't specify a particular schema, the default dbo schema takes ownership of the table.
After creating a table, you can change its schema. To do this visually, open the table in Design view. In the Properties window, click the arrow of the Schema combo box and select the desired schema. You will receive a message box (based on the permissions):
If you still want to change the schema, click Yes.
Table maintenance consists of reviewing or changing some of its aspects. This includes reviewing the list of tables of a database, renaming a table, or deleting it.
Like every other object of a database or of the computer, a table possesses some characteristics that are proper to it. To view these characteristics, in the Object Explorer, right-click the table and click Properties.
Most operations require that you open a table before using it. There are various ways a table displays, depending on how you want to examine it:
Probably the most routine operation you will perform on a table is to change its design. This is usually easily done after you have opened the table in Design view. You can then change what you want. After making the change(s), you must save the table. Depending on how your database is configured, you may receive an error as "Saving changes is not permitted...":
To avoid this error and make it possible to modify tables, on the main menu of SQL Server Management Studio, click Tools -> Options... In the left tree list of the Options dialog box, click Designers. In the Table Options, clear the Prevent Saving Changes That Require Table Re-Creation check box.
To see the list of tables of a database in the Object Explorer, you can click the Tables node:
To see the list of tables of a database using SQL, in a Query Editor, specify the database (using a USE statement). On the next line, type sp_help and execute it. Here is an example:
If you have an undesired table in a database, you can remove it:
You can also use sample code that Microsoft SQL Server can generate for you. First display an empty Query Editor. Also display the Templates Explorer and expand the Table node. Under Table, drag Drop Table and drop it in the empty Query Editor. Sample code would be generated for you. You can then simply modify it and execute the statement.
In your database development assignments, you will write expressions that involve the names of tables. In those expressions, you will need to specify a particular table you want to use. There are three main ways you can do this. To refer to, or to indicate, a table: