Technology articles, eBooks and courses - TechHowTos

How to Create and Alter Tables in SQL Server Databases

by
Share this article!

Hi friends! In a previous article, we’ve talked about how you can create databases in SQL Server. In this article, we’ll talk  about how you can create and alter tables in SQL Server databases.

 

The basic syntax for creating SQL Server database tables

The basic syntax, involves the CREATE TABLESQL statement.

 

Creating a simple table in SQL Server

Therefore, when for example you want to create a table named “tblTest1” that will just have one column named “id” and will be an integer data type, your CREATE TABLESQL statement would look like the below:

USE MyDatabase;
GO

CREATE TABLE tblTest1
    (
        id INT
    );
GO

As you can see in the above code, with the “USE” command, we switch context to the database (“MyDatabase” in our example), into which we want to create our new table.

Also, right after the CREATE TABLESQL statement, we specify the name of the table and then within parentheses, we specify the columns of the table and their data types.

 

Creating a table in SQL Server with a primary key

Now let’s see the scenario where you want to create a new table named “tblTest2” that will have the following columns:

  • id (integer data type. primary key, to be automatically increased by 1 each time a new record is inserted)
  • code (varchar(50)
  • descr (varchar(100))

In a scenario like the above, your CREATE TABLESQL statement would look like the below:

USE MyDatabase;
GO

CREATE TABLE tblTest2
    (
        id INT PRIMARY KEY IDENTITY(1, 1) ,
        code VARCHAR(50) ,
        descr VARCHAR(100)
    );
GO

As you can see, in the above example, after switching the query window’s context to our database (“MyDatabase” in our example), we specified the name of our new table and then within parentheses, we specified the 3 columns of the table, along with their data types and other characteristics (i.e. primary key, etc.)

The CREATE TABLESQL statement in SQL Server, allows you to specify many parameters and build sophisticated tables, for efficiently organizing your data. In the official MS Docs article, you can learn more about creating tables in SQL Server.

 

Tip: Learn more about the benefits of primary keys in database tables.

 

The basic syntax for altering SQL Server database tables

Altering a SQL Server database table, means that you change its structure. This change can take place while the table is empty, or also in the case where the table has data. Of course, in case where you are altering a table that has data, special caution is required, in order not to accidentally lose any data while altering the table. Also, as a best practice recommendation, always make sure that you have recent backups of your SQL Server databases.

The basic syntax, involves the ALTER TABLESQL statement.

Let’s continue our example presented above, by altering the two tables.

 

Adding a column to an existing table in SQL Server

Now let’s add a new column named “code” of the varchar data type in tblTest1.

To this end, in this case, we would write the below SQL statement:

USE MyDatabase;
GO

ALTER TABLE tblTest1
ADD code varchar(50);
GO

As you can see in the above code, after switching the query window’s context to our database (“MyDatabase” in our example), we’ve called the “ALTER TABLE” SQL statement, and then with the “ADD” command, we’ve added the new column named “code” which is of the type varchar and has size 50 characters.

 

Tip: Get started with SQL Server fast and easy – enroll to our course “SQL Server Fundamentals – SQL Database for Beginners” on Udemy, with an exclusive discount!

 

Change the size of an existing column in a table in SQL Server

Now let’s change the size of the column “code” to varchar(100).

To this end, in this case, we would write the below SQL statement:

USE MyDatabase;
GO

ALTER TABLE tblTest1
ALTER COLUMN code varchar(100);
GO

As you can see in the above code, after switching the query window’s context to our database (“MyDatabase” in our example), we’ve called the “ALTER TABLE” SQL statement, and then with the “ALTER COLUMN” command, we’ve changed the size of the “code” column from varchar(50) to varchar(100).

The ALTER TABLESQL statement in SQL Server, allows you to specify many parameters and perform different alterations to existing tables in SQL Server. Of course, as always, you need to be careful when altering existing tables. In the official MS Docs article, you can learn more about altering tables in SQL Server.

 

Learn More – Enroll to the Course!

Enroll to my online course on Udemy, titled “SQL Server Fundamentals – SQL Database for Beginners” and get started with SQL Server on both Windows and Linux in no time!

SQL Server Fundamentals - SQL Database for Beginners (Online Course)
(Lifetime Access, Downloadable Resources, Certificate of Completion and more!)

Enroll Now!

 

 

Featured Online Courses:

 

Read Also:

 

Featured Database Software Tool

SQLNetHub's Snippets Generator

SQLNetHub’s Snippets Generator 

Create and modify T-SQL code snippets for use in SQL Server Management Studio, fast, easy and efficiently.

Learn More

 

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)

Loading...

Reference: TechHowTos.com (https://www.techhowtos.com)

© TechHowTos.com

 



Views: 887