Tables in Sql Server

Tables in Sql Server which stores the data in the formate of Rows and columns.

In SQL Server, tables can be created graphically using SQL Server Management Studio (SSMS) or using a query.

Syntax for a creating Table using Query

CREATE  TABLE  TableName ( ColumnName1 Datatype [CONSTRAINT], ColumnName2 Datatype [CONSTRAINT], ColumnName3 Datatype [CONSTRAINT], ColumnNameN Datatype [CONSTRAINT] ) Note:- We will understand “CONSTRAINT” in next tutorial

For example create table for student

CREATE TABLE Student ( ID INT NOT NULL, Name NVARCHAR(50) NULL, SchoolName NVARCHAR(50) NULL, Gender TINYINT NULL )

To create Student table, graphically, using SQL Server Management Studio
1. Right click on Tables folder in Object explorer window
2. Select New Table
3. Fill Column Name, Data Type and Allow Nulls, as shown below and save the table as Student.

WHAT IS NULL

NULL represents unknown data. A Column with NULL value means column doesn’t have data/value. NULL is different from a value 0, spaces or empty string, they are not equivalent.

Reterieveing Data From Table

We can use the SELECT statment like below to fetch records from the Customer Table.

SELECT * FROM student

Inserting Records

We can add records to the Table by using INSERT statement as shown in the below DEMOs

1: Adding records to the Student Table with all the column values.

INSERT INTO Student(ID, Name, SchoolName,Gender) VALUES (1, ‘Munesh’, ‘ABC’,0)

No Need to mention Column Names if we are passing all the column values and values are specified in the same order as the columns position in the Table.

INSERT INTO Student VALUES (2, ‘Rahul’, ‘PQR’,1)

Column names need be specified if we are not inserting all the column values and also if the order of the values is different from the actual column position in the table.

Let us verify the Student Table Data.

SELECT * FROM Student

 2: Insert without Optional column Gender value.

In student table there are some column which accept Null like name ,school name, gender. lets insert null to Gender column

INSERT INTO Student (ID,Name,SchoolName) VALUES (2, ‘Anshuman’, ‘AAA’)

Output

3: Sql Server will Give an exception if we don’t pass NOT NULL column value.

INSERT INTO dbo.Student ( ID, Name )VALUES  ( 3, ‘Anshuman’ )

— here if SchoolName is Not Null column and if you do not insert value for it ,it will give exception

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column ‘SchoolName, table ‘Student; column does not allow nulls. INSERT fails.

The statement has been terminated.

ALTER TABLE

We can modify the Table structure using ALTER TABLE Statement.

ALTER TABLE Student

ADD DateOfBirth DATETIME NULL

SELECT * FROM student

And see the table which contain one more column with name DateOfBirth

Add one record to table with dateOfBirth

INSERT INTO dbo.Student ( ID, Name, SchoolName, Gender,DateOfBirth ) VALUES  ( 4, — ID – int ‘Govind’, — Name – nvarchar(50) ‘GIT’, — SchoolName – nvarchar(50) 0,  — Gender – tinyint ’01/01/1991′ — DateOfBirth )

SELECT * FROM student

Try to add a NOT NULL column City to the Student Table which having four rows.

ALTER TABLE Student

ADD City NVARCHAR(50) NOT NULL

ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column ‘CITY’ cannot be added to non-empty table ‘Student because it does not satisfy these conditions.

From the above DEMO result it is clear that we can add NOT NULL column to only an Empty Table.

DROP TABLE

DROP TABLE statement deletes both data and table definition permanently from the database. This action cannot be undone, so always double verify before issuing this command.

 Drop the Student Table

DROP TABLE Student

Try to access a Table which is Dropped.

SELECT * FROM Student

RESULT:
Msg 208, Level 16, State 1, Line 1
Invalid object name ‘Student.

Author : Dot Net Office

Share this

Related Posts