SQL Basics

SQL Data Types

Like in other programming languages, SQL also has certain datatypes available. A brief idea of all the datatypes are discussed below.
 
1. Binary Datatypes :
There are four subtypes of this datatype which are given below :

2. Exact Numeric Datatype :
There are nine subtypes which are given below in the table. The table contains the range of data in a particular type.

3. Approximate Numeric Datatype :
The subtypes of this datatype are given in the table with the range.

4. Character String Datatype :
The subtypes are given in below table –

5. Unicode Character String Datatype :
The details are given in below table –

6. Date and Time Datatype :
The details are given in below table.

Distinction between client server and file server databases

A high-speed computer in a network that stores the programs and data files shared by users. It acts like a remote disk drive. The difference between a file server and an application server is that the file server stores the programs and data, while the application server runs the programs and processes the data.

An introduction to SQL tables

In this article, we will learn the concept of SQL tables and then work on how we can create tables with different techniques in SQL Server.

A relational database model is one of the most used data models to store and process the data. Tables are the essential elements of a database. In this context, tables are the database objects that hold the data in the relational databases. A database contains one or more tables and these tables can be modeled as relational. The tables come into existence from the columns and every column must have a name and a data type. These columns store data according to the defined data types and these data records are called rows. The following illustration shows an example of a table structure.

As we can see, the Persons table consists of Id, Name, Surname, and Age columns. These columns hold either numeric or text data according to defined data types. Now, we will learn how to create tables with different techniques.

Create a table using SQL Server Management Studio (SSMS)

SQL Server Management Studio is an IDE (integrated development environment) that helps to manage SQL Server and building T-SQL queries. So, it is very easy to create a table through the SSMS. After connecting to the database, we right-click on the Tables folder and select the New on the pop-up menu and click the Table option.

A New Table customized window will be shown to create a table quickly. At the same time, we can find the properties of the selected column at the bottom of the same screen.

On the New Table window, we can define the columns with names and data types. Each column must have a data type and a unique name.

Tip: When we checked the Allow Nulls option enables us to store null values to the associated column.

When we right-click on any column, we can set some options for this column. The right-arrow indicates which column we are working on.

Set Primary Key: The primary key is a value or combination of values that help to uniquely identify each row on the table. For this table, we will set the Id column as a primary key. After setting a column as a primary key, a key sign will be appeared on the column.

When we try to check the Allow Nulls option for a column that has been set as a primary key, SSMS returns an error.

As we stated, SQL tables allow us to specify composite primary keys. Only we need to select more than one column with the help of the shift key and click the Set Primary Key option.

The key sign will be shown for these multiple columns.

Relationships: This option provides to define a foreign key relationship with the other tables.

Indexes/Keys: Through this option, we can create indexes or set unique constraints for the columns.

Check Constraints: Check constraints are used to control according to specified rule the data that will be stored by the columns. This option provides to create this type of rules (constraints).

Properties: When we select this option for any column, we can reach table property windows.

On this screen, we can change the table name and other properties of the table. As a final step, we will click the save button or the CTRL+S key combination. The created table will be shown under the Tables folder.

Create a table using T-SQL

The CREATE TABLE statement is used to create a new table in SQL Server. The query below creates the Persons table. After the CREATE TABLE statement, we define the table name. In the brackets, we specify the column names and data types. Additionally, we set the Id column as a primary key.

1234567

CREATE TABLE  [Persons]([Id]      [INT] PRIMARY KEY, [Name]    [VARCHAR](50) NOT NULL, [SurName] [VARCHAR](50) NOT NULL, [Age]     [SMALLINT] NOT NULL)

We get an error after executing the query because a table with the same name exists under the same schema. For this reason, before creating a table, we need to check the existence of the table thus we can avoid this type of errors. The DROP TABLE condition will be executed if the Persons table already exists on the database.

12345678910111213141516

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES    WHERE TABLE_NAME = N’Persons’)BEGINDROP TABLE PersonsENDGOCREATE TABLE  [Persons]([Id]      [INT]PRIMARY KEY, [Name]    [VARCHAR](50) NOT NULL, [SurName] [VARCHAR](50) NOT NULL, [Age]     [SMALLINT] NOT NULL)GOSELECT * FROM Persons

Create a table from an existing table

We can create a new table from the existing table. SELECT INTO statement creates a new table and inserts the result set of the SELECT query to the new table. However, if we want to create an empty copy of a table we can use the following method. This method uses a WHERE condition that causes an empty result set to be returned from the query.

1234	SELECT * INTO CopyPersons FROM Persons WHERE 1=0GOSELECT * FROM CopyPersons

The disadvantage of this method is that it does not copy the indexes and constraints from source table to destination (new) table. Such as, we know that the Id column is the primary key for the Person table but this attribute does not transfer to the CopyPerson table.

Create a table using Azure Data Studio

Azure Data Studio is a new lightweight tool that enables us to execute queries on-premises or cloud databases and it also helps to manage databases. Code Snippets is one of the main advantages of the Azure Data Studio, these templates assist us in typing queries to generate proper syntax easily. After opening a new query window, we type “CREATE TABLE” to create a SQL table and select the sqlCreateTable snippet.

After selecting the template, the query table will be automatically typed into the query window.

After modifying the required fields the template, the CREATE TABLE statement will be completed.

Conclusion

In this article, we learned the fundamentals of the SQL tables in the relational databases and then learned different techniques used to create tables. We can use the method that is easiest and convenient for us.