To create an SQL Server database table, you must provide a name for the table, and the names and data types of each column in the table. Most tables have a primary key which is always unique, and made up of one or more columns of the table. It's also a good practice to indicate whether null values are allowed in each column of the table.
Creating a Table (Tutorial)
Procedures:
To create a database to contain the new table
• Enter the following code into a Query Editor window.
USE master;
GO
--Delete the TestData database if it exists.
IF EXISTS(SELECT * from sys.databases WHERE name='TestData')
BEGIN
DROP DATABASE TestData;
END
--Create a new database called TestData.
CREATE DATABASE TestData;
Press the F5 key to execute the code and create the database.
Switch the Query Editor connection to the TestData database
• In a Query Editor window, type and execute the following code to change your connection to the TestData database.
USE TestData
GO
To create a table
In a Query Editor window, type and execute the following code to create a simple table named Products. The columns in the table are named ProductID, ProductName, Price, and ProductDescription. The ProductID column is the primary key of the table. int, varchar(25), money, and text are all data types. Only the Price and ProductionDescription columns can have no data when a row is inserted or changed. This statement contains an optional element (dbo.) called a schema. The schema is the database object that owns the table. If you are an administrator, dbo is the default schema. dbo stands for database owner.
CREATE TABLE dbo.Products
(ProductID int PRIMARY KEY NOT NULL,
ProductName varchar(25) NOT NULL,
Price money NULL,
ProductDescription text NULL)
GO
///////// From Microsfot adventureworks ////////////
Use Transact-SQL
1- In Object Explorer, connect to an instance of Database Engine.
2 -On the Standard bar, select New Query.
3 - Copy and paste the following example into the query window and select Execute.
CREATE TABLE dbo.PurchaseOrderDetail (
PurchaseOrderID INT NOT NULL,
LineNumber SMALLINT NOT NULL,
ProductID INT NULL,
UnitPrice MONEY NULL,
OrderQty SMALLINT NULL,
ReceivedQty FLOAT NULL,
RejectedQty FLOAT NULL,
DueDate DATETIME NULL
);
//////////////////////////////////////////////
//////Use table designer in SQL Server Management Studio
1 - In SSMS, in Object Explorer, connect to the instance of Database Engine that contains the database to be modified.
2 - In Object Explorer, expand the Databases node and then expand the database that will contain the new table.
3 - In Object Explorer, right-click the Tables node of your database and then select New Table.
4 - Type column names, choose data types, and choose whether to allow nulls for each column as shown in the following illustration:
5 - To specify more properties for a column, such as identity or computed column values, select the column and in the column properties tab, choose the appropriate properties. For more information about column properties, see Table Column Properties (SQL Server Management Studio).
6 - To specify a column as a primary key, right-click the column and select Set Primary Key. For more information, see Create Primary Keys.
7 - To create foreign key relationships, check constraints, or indexes, right-click in the Table Designer pane and select an object from the list as shown in the following illustration:
8 - By default, the table is contained in the dbo schema. To specify a different schema for the table, right-click in the Table Designer pane and select Properties as shown in the following illustration. From the Schema drop-down list, select the appropriate schema.
9 - From the File menu, choose Save table name.
10 - In the Choose Name dialog box, type a name for the table and select OK.
11 - To view the new table, in Object Explorer, expand the Tables node and press F5 to refresh the list of objects. The new table is displayed in the list of tables.
Breakdown of your Risk Level
Related Content
