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.
Note: To create a database you need at least one primary data file and one log file.
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 –> clickTemplate Explorer in SQL Server Management Studio. Then navigate to Table –> Create Tabletemplate 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 onNew 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]
Figure 6: Rename Table
Drop a Table
If you want to completely drop a table, you can do the same by T-SQL or right click on table and click Delete in Object Explorer.
Additional Information
Database name is defined as nvarchar(128) with data type as ‘sysname’, you can simply create database with default values in a single line as:CREATE DATABASE BooksInfo
It will create the database with default path and values but if you want to create as per your requirements then you can create the same as given below. Here each file has it’s logical and physical file name.
CREATE DATABASE BooksInfo
ON PRIMARY
(
NAME = BooksInfo_Primary,
FILENAME = 'C:\Database\BooksInfo.mdf',
SIZE = 50MB,
MAXSIZE = 500MB,
FILEGROWTH = 10%
)
LOG ON
(
NAME = BooksInfo_Log,
FILENAME = 'C:\DatabaseLog\BooksInfo_log.ldf',
SIZE = 10MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10MB
)
It will create primary data file as BooksInfo.mdf and log file as BooksInfo_log.ldf but if you want to include one file group also then you can create the database as given below.
CREATE DATABASE BooksInfo
ON PRIMARY
(
NAME = BooksInfo_Primary,
FILENAME = 'C:\Database\BooksInfo.mdf',
SIZE = 50MB,
MAXSIZE = 500MB,
FILEGROWTH = 10%
),
FILEGROUP BooksInfo_G1
(
NAME = BooksFileGroup1,
FILENAME = 'C:\Database\BooksInfo_G1.ndf',
SIZE = 50MB,
MAXSIZE = 500MB,
FILEGROWTH = 10% )
LOG ON
(
NAME = BooksInfo_Log,
FILENAME = 'C:\DatabaseLog\BooksInfo_log.ldf',
SIZE = 10MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10MB
)
Now your database will be created with one file group also as BooksInfo_G1.ndf. You can also mention collation with create database command but if you have not specified it then it will consider collation of your SQL Server instance as default.
Create Database by SSMS
To create database with SQL Server Management Studio go to Object Explorer –> right click on Databases –> select New Database –> enter a database name and click OK.
It will create a database with default values and path. If you want you can change the path and default values in new database window and add filegroup as well, by default primary filegroup is set.
Permissions required to Create Database
You need following permissions to create a database.
– CREATE DATABASE,
– CREATE ANY DATABASE
– ALTER ANY DATABASE
