Sql Server Tutorial Lesson 3: Working with Tables

TABLE

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

CREATE TABLE

Below is the basic syntax for a creating Table in Sql Server.

CREATE TABLE TableName
(
  ColumnName1 Datatype [CONSTRAINT],
  ColumnName2 Datatype [CONSTRAINT],
                .
                .
                .
  ColumnNameN Datatype [CONSTRAINT]
)

Let us first create a database SqlHintsTableTutorial for demonstrating this lesson’s examples

CREATE DATABASE SqlHintsTableTutorial
GO
USE SqlHintsTableTutorial
GO

Let us create a simple Customer table with three columns CustomerId, Name and PhoneNumber.

CREATE TABLE Customer
( 
	CustomerId INT NOT NULL, 
	Name NVARCHAR(100) NOT NULL,
	PhoneNumber VARCHAR(15) NULL  
)

Here CustomerId and Name columns have NOT NULL constraint, which enforces that each row in this table needs to have a value for this column. Where as PhoneNUmber Column in this table is optional, it allows NULL values for this column. By default if we don’t specify the NOT NULL constraint for column during table creation, then it is considered as Null-able column.

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 Customer

RESULT:

Inserting Records

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

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

INSERT INTO Customer(CustomerId, Name, PhoneNumber) 
VALUES (1, 'Shree', '1234567890')

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 Customer
VALUES (2, 'Ganesh', '1212121212')

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 Customer Table Data.

SELECT * FROM Customer

DEMO 2: Insert without Optional column PhoneNumber value.

INSERT INTO Customer(CustomerId, Name) 
VALUES (3, 'Kalpana')

Let us verify the Customer Table Data.

SELECT * FROM Customer

RESULT:

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

INSERT INTO Customer(Name, PhoneNumber) 
VALUES ('Patil', '1313131313')

RESULT:

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column ‘CustomerId’, table ‘SqlHintsTableTutorial.dbo.Customer’; column does not allow nulls. INSERT fails.
The statement has been terminated.

ALTER TABLE

We can modify the Table structure using ALTER TABLE Statement.

DEMO 1: Add One more column DateOfBirth to the Customer table created above.

ALTER TABLE Customer
ADD DateOfBirth DATETIME NULL

Verify the Customer Table Data

SELECT * FROM Customer

DEMO 2: Insert one more customer record which has DateOfBirth column value

INSERT INTO dbo.Customer
(CustomerId, Name, PhoneNumber, DateOfBirth)
VALUES (4, ‘Basavaraj’, ‘1414141414’, ’01/01/1984′)
Verify the Customer Table Data

SELECT * FROM Customer

DEMO 3: Try to add a NOT NULL column City to the Customer Table having four rows.

ALTER TABLE Customer
ADD City NVARCHAR(50) NOT NULL

RESULT:
Msg 4901, Level 16, State 1, Line 1
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 ‘Customer’ 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.

DEMO 1: Drop the Customer Table

DROP TABLE Customer

DEMO 2: Try to access a Table which is Dropped.

SELECT * FROM Customer

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