SQL Server - Creating Table? - Asked By Prabhakaran on 21-Dec-11 02:43 AM

hi friends,


am new to creating tables on in my project? so i need some tips from u all to create a efficient table structure using different types?


thanks in advance.....
Chintan Vaghela replied to Prabhakaran on 21-Dec-11 02:46 AM

Hello,

CREATE TABLE table_name

   ( { < column_definition > | < table_constraint > } [ ,...n ]

   )

< column_definition > ::=

   { column_name data_type }

   [ { DEFAULT constant_expression

    | [ IDENTITY [ ( seed , increment ) ]

    ]

    } ]

   [ ROWGUIDCOL ]

   [ < column_constraint > [ ...n ] ]

< column_constraint > ::=

   [ CONSTRAINT constraint_name ]

   { [ NULL | NOT NULL ]

    | [ PRIMARY KEY | UNIQUE ]

    | REFERENCES ref_table [ ( ref_column ) ]

    [ ON DELETE { CASCADE | NO ACTION } ]

    [ ON UPDATE { CASCADE | NO ACTION } ]

    }

< table_constraint > ::=

   [ CONSTRAINT constraint_name ]

   { [ { PRIMARY KEY | UNIQUE }

    { ( column [ ,...n ] ) }

    ]

   | FOREIGN KEY

   ( column [ ,...n ] )

    REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]

    [ ON DELETE { CASCADE | NO ACTION } ]

    [ ON UPDATE { CASCADE | NO ACTION } ]

   }

 

The following examples show how to:

  1. Create a two-column table with an identity column as the PRIMARY KEY.
  2. Create a one-column table with a PRIMARY KEY constraint
  3. Create a table with one of its columns referencing a column in another table

 

 

CREATE TABLE MyCustomers (CustID int IDENTITY (100,1) PRIMARY KEY, CompanyName nvarchar (50))

 

CREATE TABLE MyCustomers2 (CustID int CONSTRAINT pkCustId PRIMARY KEY)

 

CREATE TABLE MyOrders (OrderID int, CustID int REFERENCES MyCustomers(CustID))

 

 

Hope this is helpful !

Thanks

 

 

 

 

 

dipa ahuja replied to Prabhakaran on 21-Dec-11 02:52 AM
There is no any special tip to create a table, just make sure you are taking appropriate Data Type for the data.

Like for the fields which required calculation should have data type as int, double etc and which show text should have varchar or if you are adding symbols then use nvarchar 

for ex:
Syntax:
 
CREATE TABLE "table_name"
("column 1" "data_type_for_column_1",
"column 2" "data_type_for_column_2",
... )
 
Example :
 
CREATE TABLE customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)
kalpana aparnathi replied to Prabhakaran on 21-Dec-11 03:04 AM
hi,

Try this :http://msdn.microsoft.com/en-us/library/aa258255%28v=sql.80%29.aspx
Jitendra Faye replied to Prabhakaran on 21-Dec-11 03:15 AM

The CREATE TABLE statement is used to create a table in a database.

SQL CREATE TABLE Syntax

CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
)

follow these links for more help-

http://msdn.microsoft.com/en-us/library/ms174979.aspx
http://msdn.microsoft.com/en-us/library/aa258255%28v=sql.80%29.aspx
smr replied to Prabhakaran on 21-Dec-11 03:18 AM
hi

The CREATE TABLE statement is used to create a table in a database.

syntax:

CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
)

The create table statement is used to create a new table. Here is the format of a simple create table statement:

create table "tablename"




("column1" "data type", "column2" "data type", "column3" "data type");

Format of create table if you were to use optional constraints:


create table "tablename" ("column1" "data type" [constraint], "column2" "data type" [constraint], "column3" "data type" [constraint]); [ ] = optional

Note: You may have as many columns as you'd like, and the constraints are optional.


Example:

create table employee
(first varchar(15),
 last varchar(20),
 age number(3),
 address varchar(30),
 city varchar(20),
 state varchar(20));

Here are the most common Data types:

char(size)
varchar(size)
number(size)
number(size,d)
date

char(size) Fixed-length character string. Size is specified in parenthesis. Max 255 bytes.
varchar(size) Variable-length character string. Max size is specified in parenthesis.
number(size) Number value with a max number of column digits specified in parenthesis.
date Date value
number(size,d) Number value with a maximum number of digits of "size" total, with a maximum number of "d" digits to the right of the decimal.

follow
http://www.techonthenet.com/sql/tables/create_table2.php
http://www.sqlcourse.com/create.html
http://www.1keydata.com/sql/sqlcreate.html

char(size) Fixed-length character string. Size is specified in parenthesis. Max 255 bytes.
varchar(size) Variable-length character string. Max size is specified in parenthesis.
number(size) Number value with a max number of column digits specified in parenthesis.
date Date value
number(size,d) Number value with a maximum number of digits of "size" total, with a maximum number of "d" digits to the right of the decimal.
Riley K replied to Prabhakaran on 21-Dec-11 03:24 AM


In yourSQL Server he CREATE TABLE statement is used to create a table in a database.
CREATE TABLE Persons
(
P_Id int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

The empty "Persons" table will now look like this:

P_Id LastName FirstName Address City
         

you can refer basics here

http://www.w3schools.com/sql/sql_create_table.asp


Regards
DL M replied to Prabhakaran on 21-Dec-11 03:44 AM

Hi..
Check here table structure using different types.

Regular User Tables:-
Regular user table is the actually table which holds data of user for later on processing and reporting purpose. These are also called physical tables at they physically resides at hard drive until you DROP them intentionally.

 

CREATE TABLE [dbo].[TestTable]

    (

    [TestTableID] [int] NOT NULL,

    [FirstCol] [varchar](200) NULL,

    [SecondCol] [int] NULL

    )

ON  [PRIMARY] --  This part indicates, where (Database FileGroup) table will be created physically

 

 

Temporary Tables:-Temporary tables and created to hold temporary data regarding intermediate results of different quires. These tables will be drooped automatically once the store procedure is executed (if they are used in stored procedure) or once the session is over. But as good programming practice will must drop these tables once they are not required.

 

CREATE TABLE #Temp_TestTable

    (

    [TestTableID] [int] NOT NULL,

    [FirstCol] [varchar](200) NULL,

    [SecondCol] [int] NULL

    )

   

GO

-- DROP TABLE #Temp_TestTable --(Drop temporary table when not required)   

GO

 

Global Temporary Tables:-These are just like simple temporary tables but are available to all sessions and will only be dropped automatically when last session of database will be closed. If single session is active, global temporary tables will remain available.

 

CREATE TABLE ##GTemp_TestTable

    (

    [TestTableID] [int] NOT NULL,

    [FirstCol] [varchar](200) NULL,

    [SecondCol] [int] NULL

    )

 

GO

-- DROP TABLE ##GTemp_TestTable      

--(Drop global temporary table when not required)

 

These were three types of tables that can be created in SQL Server. Lets talk about some tricks about tables.

 

Tables Cloning:-Existing regular, temporary or global temporary tables can be cloned (structure as well as their data). Following statement will create a new table, exactly same in structure to existing one.

SELECT  *

INTO    [dbo].[NewTestTable]

FROM    [dbo].[TestTable]

WHERE   1 = 2   -- Remove WHERE clause if you want to copy both structure and data

 

 

Inserting Data On Basis of Stored Procedure Result:-Table can be populated with data from result set of stored procedure. Table variables can not be populated in this fashion.

 

INSERT INTO [YourTableName](CommaSeparatedColumnsName)

 EXECUTE YourStoredProcedureNameHere CommaSeparatedParameterValues

 

Table Variables:-Table variables are just like scalar variables which possess structure of a table and can hold records for intermediate results of different quires. These are the best alternative for temporary tables as there is no need to worry about demolition of table variables after use.

 

DECLARE @VarTestTable TABLE

(

    [TestTableID] [int] NOT NULL,

    [FirstCol] [varchar](200) NULL,

    [SecondCol] [int] NULL

)

 

Anoop S replied to Prabhakaran on 21-Dec-11 03:58 AM
One more method is there other that all the above, you can also Create Tables from SQL Management Studio
1 Right-click the database in which you want the table to appear. A menu is shown. Select "New Table" from the list of menu items.

2Type column names on the left side of the table viewer. The table view is the place where you add your column names and data types.

3 Select a data type for each column. The data type is a drop-down menu item. The data type should be carefully considered. For instance, if you set the data type as numeric but enter string values, the database engine returns an error.

4 Right-click one of the columns that is unique to the table and select "Primary Key." Every table should contain a primary key, or your performance will plummet. The primary key is a unique value within the table. Some people choose to create an ID field that increments each time a new record is entered, which ensures there is always a unique value in the table.

5 Click the "Save" button on the SQL Server Management Studio toolbar. A window pops up asking for a table name. Enter it into the text box and press the "Ok" button. The table is created and saved in your database.

Refer this for a nice tutorial

http://mrbool.com/p/Creating-databases-using-SQL-Server-Management-Studio-Express/4672