Ms SQL Server
Creating Table and
Relationship
Lecturer: Heng Bora Page 1
Ms SQL Server
I- DataType Of Table
The columns 8, 9 and 10 indicates SQL Server version data type support
8 = SQL Server 2000
9 = SQL Server 2005
10 = SQL Server 2008
DATATYPE MIN MAX STORAGE 8 9 10 TYPE NOTES
Bigint -2^63 2^63-1 8 bytes Exact
Int -2,147,483,648 2,147,483,647 4 bytes Exact
Smallint -32,768 32,767 2 bytes Exact
Tinyint 0 255 1 bytes Exact
Bit 0 1 1 to 8 bit Exact
columns in the
same table
requires a total
of 1 byte, 9 to
16 bits = 2
bytes, etc...
Decimal and numeric data type is
Decimal -10^38+1 10^38–1 Precision 1-9 = Exact exactly the same. Precision is the
5 bytes, total number of digits. Scale is the
precision 10-19 number of decimals. For both the
= 9 bytes, minimum is 1 and the maximum is
precision 20-28 38.
= 13 bytes,
precision 29-38
= 17 bytes
Numeric no
Money -2^63 / 10000 2^63-1 / 10000 8 bytes Exact
Smallmoney -214,748.3648 214,748.3647 4 bytes Exact
Float -1.79E + 308 1.79E + 308 4 bytes when Approx Precision is specified from 1 to 53.
precision is less
than 25 and 8
bytes when
precision is 25
through 53
Real -3.40E + 38 3.40E + 38 4 bytes Approx Precision is fixed to 7.
Datetime 1753-01-01 9999-12-31 8 bytes Datetime If you are running SQL Server
00:00:00.000 23:59:59.997 2008 and need milliseconds
precision, use datetime2(3) instead
to save 1 byte.
Lecturer: Heng Bora Page 2
Ms SQL Server
Smalldatetime 1900-01-01 00:00 2079-06-06 23:59 Datetime
Date 0001-01-01 9999-12-31 3 bytes no no Datetime
Time 00:00:00.0000000 23:59:59.9999999 no no Datetime Specifying the precision is
possible. TIME(3) will have
milliseconds precision. TIME(7) is
the highest and the default
precision. Casting values to a lower
precision will round the value.
Datetime2 0001-01-01 9999-12-31 Presicion 1-2 = no no Datetime Combines the date datatype and the
00:00:00.0000000 23:59:59.9999999 6 bytes time datatype into one. The
precision 3-4 = precision logic is the same as for
7 bytes the time datatype.
precision 5-7 =
8 bytes
Datetimeoffset 0001-01-01 9999-12-31 Presicion 1-2 = no no Datetime Is a datetime2 datatype with the
00:00:00.0000000 - 23:59:59.9999999 8 bytes UTC offset appended.
14:00 +14:00 precision 3-4 =
9 bytes
precision 5-7 =
10 bytes
Char 0 chars 8000 chars Defined width String Fixed width
Varchar 0 chars 8000 chars 2 bytes + String Variable width
number of chars
Varchar(max) 0 chars 2^31 chars 2 bytes + no String Variable width
number of chars
Text 0 chars 2,147,483,647 4 bytes + String Variable width
chars number of chars
Nchar 0 chars 4000 chars Defined width x Unicode Fixed width
2
Nvarchar 0 chars 4000 chars Unicode Variable width
Nvarchar(max) 0 chars 2^30 chars no Unicode Variable width
Ntext 0 chars 1,073,741,823 Unicode Variable width
chars
Binary 0 bytes 8000 bytes Binary Fixed width
Varbinary 0 bytes 8000 bytes Binary Variable width
Varbinary(max) 0 bytes 2^31 bytes no Binary Variable width
Image 0 bytes 2,147,483,647 Binary Variable width
bytes
Sql_variant Other Stores values of various SQL
Server-supported data types, except
text, ntext, and timestamp.
Timestamp Other Stores a database-wide unique
number that gets updated every
time a row gets updated.
Uniqueidentifier Other Stores a globally unique identifier
(GUID).
Xml no Other Stores XML data. You can store
xml instances in a column or a
Lecturer: Heng Bora Page 3
Ms SQL Server
variable.
Cursor Other A reference to a cursor.
Table Other Stores a result set for later
processing.
II- Creating Table
1. Using SQL Server Management Studio
To create a table with Table Designer
1. 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 click 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, click 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.
Lecturer: Heng Bora Page 4
Ms SQL Server
For more information about these objects, see Create Foreign Key Relationships, Create Check
Constraints and Indexes.
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.
Lecturer: Heng Bora Page 5
Ms SQL Server
For more information about schemas, see Create a Database Schema.
9. From the File menu, choose Save table name.
10. In the Choose Name dialog box, type a name for the table and click 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.
2. Using Transact-SQL
To create a table in the Query Editor
1. In Object Explorer, connect to an instance of Database Engine.
2. On the Standard bar, click New Query.
Syntax
Create Table Table_Name
(
FieldName DataType [Constraints],
…………………………
) [ON FileGroup]
Constraint
- NULL | NOT NULL
- DEFAULT values
- INDENTITY(Increment, start)
- PRIMARY KEY|UNIQUE
- CHECK
- FOREIGN KEY REFERENCES table(fieldname) ON UPDATE | DELETE CASCADE
Ex1: Create Table Student, Course, Study
create Table Student
(
StuID Int constraint pk_Student Primary Key,
StuName varchar(30) NOT NULL,
Gender varchar(10) NOT NULL,
DoB DateTime NULL,
Salary Float CHECK(Salary>=0) Default 0
)
Lecturer: Heng Bora Page 6
Ms SQL Server
create Table Course
(
CID Int constraint pk_Course Primary Key,
CName varchar(30) NOT NULL,
Cdescription varchar(50) NULL,
)
create Table Study
(
StuID int constraint FK_Study_Student
foreign key references Student(StuID),
CID Int constraint FK_Study_Course
foreign key references Course(CID),
score varchar(30) NOT NULL,
Constraint pk_Study Primary key(StuID,CID)
III. The Modify TABLE
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
SQL ALTER TABLE Syntax
To add a column in a table, use the following syntax:
ALTER TABLE table_name ADD column_name datatype
To delete a column in a table, use the following syntax (notice that some database systems don't
allow deleting a column):
ALTER TABLE table_name DROP COLUMN column_name
Lecturer: Heng Bora Page 7
Ms SQL Server
To change the data type of a column in a table, use the following syntax:
ALTER TABLE table_name ALTER COLUMN column_name datatype
Rename Table Name
sp_rename OldTableName, NewTableName
ReName Column Name
sp_rename 'TableName.OldFieldNew', 'NewFieldName'
Describe structure of table
sp_help TableName
Drop Table
drop table tablename
Change Filegroup
ALTER TABLE TableName DROP CONSTRAINT ConstraintName WITH
(MOVE TO [GroupName])
Lecturer: Heng Bora Page 8
Ms SQL Server
IV. Working Constraint
1- Add Constraint to tableName
alter table TableName add constraint ConstraintName
primary key(FieldName)
2- Rename Constraint
sp_rename 'Student.Old_Constraint','New_Constraint'
3- Delete Constraint
alter table Tbl_Name drop constraint ConstraintName
Example
Look at the "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
Now we want to add a column named "DateOfBirth" in the "Persons" table.
We use the following SQL statement:
ALTER TABLE Persons ADD DateOfBirth date
Notice that the new column, "DateOfBirth", is of type date and is going to hold a date. The data
type specifies what type of data the column can hold. For a complete reference of all the data
types available in MS Access, MySQL, and SQL Server.
Lecturer: Heng Bora Page 9
Ms SQL Server
The "Persons" table will now like this:
P_Id LastName FirstName Address City DateOfBirth
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
Change Data Type Example
Now we want to change the data type of the column named "DateOfBirth" in the "Persons" table.
We use the following SQL statement:
ALTER TABLE Persons ALTER COLUMN DateOfBirth year
Notice that the "DateOfBirth" column is now of type year and is going to hold a year in a two-
digit or four-digit format.
DROP COLUMN Example
Next, we want to delete the column named "DateOfBirth" in the "Persons" table.
We use the following SQL statement:
ALTER TABLE Persons DROP COLUMN DateOfBirth
The "Persons" table will now like this:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
Lecturer: Heng Bora Page 10
