SQL Server Database Tables for Beginners
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.
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]
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]
You can check table details by T-SQL as in Figure-3.
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.
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
You can view all available records in the table by querying.
SELECT *FROM tbl_Person
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.
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]
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.
DROP TABLE table_name
CLICK HERE to watch live practical.
Reference: Manzoor Siddiqui [www.SQLServerLog.com]