SQL Server Database Tables for Beginners

by Manzoor Siddiqui · Published June 6, 2015 · Updated September 24, 2015

We will see how we can work on database tables with T-SQL and SQL Server Management Studio (SSMS) in a simple and easy way so that any beginner or learner who is interested in learning SQL irrespective of relevant experience and domain knowledge can understand and work on it easily. Today we are going to learn about database tables like create table, insert into table, select table, rename table and drop table by referring T-SQL, Template Explorer and SQL Server Management Studio.

Basically a table is a combination of rows and columns filled with data in cells. This data is arranged in a manner that it becomes valuable information for us and collection of information altogether makes a database.

For demonstration purpose we will refer AdventureWorks2012 database.

Create a Table

We can create table by T-SQL or SSMS as follows.

Syntax:

CREATE TABLE table_name
(
column_name1 datatype (size),
column_name2 datatype (size),
.
.
.
column_nameN datatype (size)
)

Below you can refer a demo table named as tbl_Person.

CREATE TABLE [tbl_Person](
[BusinessID] [int],
[Title] [nvarchar](8),
[FirstName] [nvarchar](50),
[MiddleName] [nvarchar](50),
[LastName] [nvarchar](50),
[Country] [nvarchar](50)
)

Suppose if you have forgot syntax for writing create table then no need to worry, SQL Server Management Studio provides Template Browser where you can drag Create Table template and drop to your query window. [refer Figure-1]

Figure 1: Template Explorer

You can access Template Explorer by pressing short key Ctrl+Alt+T or go to View –> click Template Explorer in SQL Server Management Studio. Then navigate to Table –> Create Table template and drag and drop to your query window as shown below.

-- =========================================
-- Create table template
-- =========================================
USE <database, sysname, AdventureWorks2012>
GO
IF OBJECT_ID('<schema_name, sysname, dbo>.<table_name, sysname, sample_table>', 'U') IS NOT NULL
DROP TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
GO
CREATE TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
(
<columns_in_primary_key, , c1> <column1_datatype, , int> <column1_nullability,, NOT NULL>,
<column2_name, sysname, c2> <column2_datatype, , char(10)> <column2_nullability,, NULL>,
<column3_name, sysname, c3> <column3_datatype, , datetime> <column3_nullability,, NULL>,
CONSTRAINT <contraint_name, sysname, PK_sample_table> PRIMARY KEY (<columns_in_primary_key, , c1>)
)
GO

or you can also create table by SSMS, you can right click on Tables under database and click on New Table… where you have to specify column name, data type and size etc. [refer Figure-2]

Figure 2: Create Table by SSMS

You can check table details by T-SQL as in Figure-3.

sp_help tbl_Person

Figure 3: Table Details

Insert Data in Table

You can insert data into table by following query.

INSERT INTO tbl_Person
VALUES (
 100
 ,'Mr.'
 ,'Roberto'
 ,'M'
 ,'Tamburello'
 ,'USA'
 )

Or you can insert records manually by SSMS as below.

Figure 4: Insert Rows

Note: You can change values for ‘Edit Top 200 Rows’ by changing settings in Tools Menu–> Options –> SQL Server Object Explorer –> Commands –> Value for Edit Top <n> Rows command

Select Records

You can view all available records in the table by querying.

SELECT *FROM tbl_Person

Figure 5: Select Rows

Rename a Table

If you want to rename a table at any point of time due to some reason, you can do the same by T-SQL or by SSMS as mentioned below.

Syntax:

sp_rename OldTableName, NewTableName

Here we will rename our existing table tbl_Person to new table name Person.

sp_rename tbl_Person, Person

You can do the same by SSMS also navigating to Object Explorer, click Databases node then expand your database, go to Tables, right click on table and select Rename where you can type the new name. [refer Figure-6]