Hello everybody. Today we will directly start creating a table in the Customers database created yesterday.
Open the SQL Server Management Studio and click on New Query (see Figure1). You can see a blank screen. This is the place where we write queries to communicate with the database.
 |
| Figure1: Click on New Query |
To insert a Customer record, we need a table made up of rows and columns to occupy the record. Let’s start creating a table by writing the following query in the editor (see Figure2),
 |
| Figure2: Creating a table CustomerDetails |
1. use [Customers]
2. --Creating a table to store customer records
3. CREATE TABLE CustomerDetails
4. (
5. Cid int,
6. Cname varchar(15),
7. Address varchar(50),
8. City varchar(15),
9. Country varchar(15)
10. )
We can run the query by highlighting the code and pressing the “Execute” or “F5” button.
The code is numbered for a line by line explanation.
1. Mentioning the database to tell the queries where it is pointing to
2. Commenting in order for others to understand
3. Creating a table CustomerDetails
4. Mentioning the start of the loop
5. Customer ID is of Integer datatype
6. Customer Name is of varchar datatype
7. Address is of varchar datatype
8. City is of varchar datatype
9. Country is of varchar datatype
10. Mentioning the end of the loop
The fields can be mentioned in short forms. For Example, Customer ID as Cid and Customer name as Cname. In the above table, int, varchar are data types that are used to store the fields like Customer ID and Customer Name in a particular format. For Example, Customer ID will be a numeric value and therefore it is saved as Integer(int). Customer Name is a string and therefore stored as varchar. In varchar(x), x denotes the size. There are several data types in SQL Server. Those are listed below.
Exact Numeric Data Types
DATA TYPE
|
FROM
|
TO
|
bigint
|
-9,223,372,036,854,775,808
|
9,223,372,036,854,775,807
|
int
|
-2,147,483,648
|
2,147,483,647
|
smallint
|
-32,768
|
32,767
|
tinyint
|
0
|
255
|
bit
|
0
|
1
|
decimal
|
-10^38 +1
|
10^38 -1
|
numeric
|
-10^38 +1
|
10^38 -1
|
money
|
-922,337,203,685,477.5808
|
+922,337,203,685,477.5807
|
smallmoney
|
-214,748.3648
|
+214,748.3647
|
Approximate Numeric Data Types
DATA TYPE
|
FROM
|
TO
|
float
|
-1.79E + 308
|
1.79E + 308
|
real
|
-3.40E + 38
|
3.40E + 38
|
Date and Time Data Types
DATA TYPE
|
FROM
|
TO
|
datetime
|
Jan 1, 1753
|
Dec 31, 9999
|
smalldatetime
|
Jan 1, 1900
|
Jun 6, 2079
|
date
|
Stores a date like June 30, 1991
|
|
time
|
Stores a time of day like 12:30 P.M.
|
|
Note − Here, datetime has 3.33 milliseconds accuracy whereas smalldatetime has 1-minute accuracy.
Character Strings Data Types
Sr.No.
|
DATA TYPE & Description
|
1
|
char
Maximum length of 8,000 characters. ( Fixed length non-Unicode characters)
|
2
|
varchar
Maximum of 8,000 characters. (Variable-length non-Unicode data).
|
3
|
varchar(max)
Maximum length of 2E + 31 characters, Variable-length non-Unicode data (SQL Server 2005 only).
|
4
|
text
Variable-length non-Unicode data with a maximum length of 2,147,483,647 characters.
|
Unicode Character Strings Data Types
Sr.No.
|
DATA TYPE & Description
|
1
|
nchar
Maximum length of 4,000 characters. (Fixed length Unicode)
|
2
|
nvarchar
Maximum length of 4,000 characters. (Variable length Unicode)
|
3
|
nvarchar(max)
Maximum length of 2E + 31 characters (SQL Server 2005 only).( Variable length Unicode)
|
4
|
ntext
Maximum length of 1,073,741,823 characters. ( Variable length Unicode )
|
Binary Data Types
Sr.No.
|
DATA TYPE & Description
|
1
|
binary
Maximum length of 8,000 bytes(Fixed-length binary data )
|
2
|
varbinary
Maximum length of 8,000 bytes. (Variable length binary data)
|
3
|
varbinary(max)
Maximum length of 2E + 31 bytes (SQL Server 2005 only). ( Variable length Binary data)
|
4
|
image
Maximum length of 2,147,483,647 bytes. ( Variable length Binary Data)
|
Misc Data Types
Sr.No.
|
DATA TYPE & Description
|
1
|
sql_variant
Stores values of various SQL Server-supported data types, except text, ntext, and timestamp.
|
2
|
timestamp
Stores a database-wide unique number that gets updated every time a row gets updated
|
3
|
uniqueidentifier
Stores a globally unique identifier (GUID)
|
4
|
xml
Stores XML data. You can store xml instances in a column or a variable (SQL Server 2005 only).
|
5
|
cursor
Reference to a cursor object
|
6
|
table
Stores a result set for later processing
|
Now that we have created a table, let’s see how to insert a record (see Figure3),
 |
| Figure3: Inserting a record |
INSERT INTO CustomerDetails VALUES(001,'Shathish','64, First Street','Chennai','India')
We can alternatively use the below query,
INSERT INTO CustomerDetails(Cid,Cname,Address,City,Country) VALUES (001,'Shathish','64, First Street','Chennai','India')
These perform insertion operation to the table with values that are defined in the table. For the former query, we need to mention in the right order with respect to the table. However, in the latter, we can change the order as we are mentioning it.
We have successfully inserted the data, now to view the record that is inserted (see Figure4),
 |
| Figure4: Select Statement |
--Viewing the record inserted
SELECT * FROM CustomerDetails
This will display all the records that are inserted. ‘*’ refers to all in SQL. We can also see selected records if there is a large number of records using the WHERE clause. We will see about that in the next blog.
Commenting is very important in SQL like any other programming languages. In order for somebody to understand the logic we are creating, we need to comment the code with proper meaning (see Figure5)
 |
| Figure5: Proper commenting |
We should comment using ‘--‘ for a single line and /*…*/ for multiline comments( see Figure6)
 |
| Figure6: Multiline commenting for creating a table |
That’s it for today. See you in the next blog with more stuff. Thank you and Bye.
Source: https://www.tutorialspoint.com/sql/sql-data-types.htm (for details related to datatypes)
Comments
Post a Comment