When you create a database, the first thing you need to do is to think carefully about what information your database needs to contain and how that information should be stored in tables. Your ability to discern which information to store in the database, which not; some can be recalculated; and how to properly arrange them in tables so that they can be properly connected and used is primarily a professionalism of years of experience working with databases. That’s why this job in companies is not left to just anyone but to professional database administrators. But that doesn’t mean they will make tables for you when you need them.
You as a developer must be able to professionally build an entire database with all objects at any time of day or night and properly connect it to your application that you are programming. Otherwise, even the smallest irregularity in your database can cause fatal errors, ruin all your programming work, and cost you dearly. Therefore, being a programmer without professional knowledge of working with databases is like being a lifeguard who can’t swim in a pool. Another important thing to understand is that strict rules when creating everything related to databases cannot be ignored. Instead, all rules must be known and applied in the best possible way with every business segment. And no, it’s not a question of what database administrators will do if you as a developer also create databases; they have work to do even when your database functions excellently.
Before you familiarize yourself with data types, for
almost every table you create, you need to define one column as the primary key
or, in most cases, create a special column, often the first column, specifically
for generated integers. This column automatically increments by 1 each time a
new row is created in the table, and you designate it as the primary key. You
can enter the same data into a column if you haven’t restricted the column to
disallow duplicates, but you can’t do that with a column that you want to set
as the primary key. With such a unique column, one that cannot contain empty
values or duplicates, simply put, by using the primary key, you uniquely
identify a row in the table.
Your primary key should be short, stable, and straightforward to facilitate faster and easier searching of all data in the table. Therefore, it’s not a good solution to use a string-type column as your primary key; instead, consider using a positive integer or a UNIQUEIDENTIFIER column which, again, consumes more memory than an Int type. Your table can also contain other columns defined as foreign keys. Even alongside a primary key, you can have multiple columns defined as foreign keys, but more about foreign keys and table relationships will be discussed in the next lesson of this tutorial. For now, focus exclusively on creating tables and thoroughly studying SQL data types.
Different data types in Microsoft SQL Server and their allowed values
Integer Data Types
Bigint: Numbers ranging from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 and occupies 8 bytes.
Int: Numbers ranging from -2,147,483,648 to 2,147,483,647 and occupies 4 bytes.
SmallInt: Numbers ranging from -32,768 to 32,767 and occupies 2 bytes.
TinyInt: Numbers ranging from 0 to 255 and occupies 1 byte.
Not recommended for use.
Money: Numbers ranging from -922,337,203,685,477.5808 to 922,337,203,685,477.5807 and occupies 8 bytes.
SmallMoney: Numbers ranging from -214,748.3648 to 214,748.3647 and occupies 4 bytes.
Decimal Data Types
Real: Numbers ranging from -3.40E+38 to 3.40E+38 with precision up to 7 digits and occupies 4 bytes.
Float: Numbers ranging from -1.79E+308 to 1.79E+308 with precision up to 15 digits and occupies 8 bytes.
Decimal: Numbers ranging from -10^38-1 to 10^38+1 with precision up to 38 digits and occupies from 2 to 17 bytes depending on the specified precision.
Character Data Types
Char(n): Allows input of 1 to 8000 non-Unicode characters of fixed length, 1 byte per character.
VarChar(n): Allows input of 1 to 8000 non-Unicode strings of variable length, 1 byte per character.
Text: Allows writing up to 2,147,483,647 non-Unicode characters.
NChar(n): Allows input of 1 to 4000 Unicode characters of fixed length, 2 bytes per character.
NVarChar(n): Allows input of 1 to 4000 Unicode strings of variable length, 2 bytes per character.
NText: Allows writing up to 1,073,741,823 Unicode characters.
Date and Time Data Types
SmallDateTime: 4 bytes for date and time from January 1, 1900, to June 6, 2079.
DateTime: 8 bytes for date and time from January 1, 1753, to December 31, 9999.
DateTime2: Same as DateTime, but with greater accuracy up to 100 nanoseconds.
Date: 3 bytes for date from January 1, 0001, to December 31, 9999.
Time: From 3 to 5 bytes for time from 00:00:00 to 23:59:59.9999999.
DateTimeOffset: From 8 to 10 bytes for time from 00:00:00 to 23:59:59.9999999 plus UTC time zone offset.
Other Data Types
Binary: Allows input of 1 to 8000 bits in hexadecimal form where the size increases by 4 bytes.
VarBinary: Allows input of 1 to 8000 bits in hexadecimal form where the size is variable.
Bit: Allows input of 0 or 1 and occupies 1 byte.
Image: Designed for large binary objects such as images, with a maximum size of 2,147,483,647 bytes.
TABLE: Used to store the results of functions and as a type for local variables.
UNIQUEIDENTIFIER: A globally unique identifier, 128-bit generated value.
CURSOR: Records references for a cursor in a variable or an output parameter of a SAVE procedure.
ROWVERSION: Indicates the version of a row when the value in the observed row changes.
HIERARCHYID: A system data type representing a position in a hierarchy.
XML: Can model data with a complex structure.
Not recommended for use:
SQLVARIANT: Can contain Int, Binary, and Char data types.
When working with tables, you should ensure that you have as few columns as possible, use the smallest data type, avoid fixed-size types, and always sort data using an integer value.
Create tables interactively: Use the power of SQL Management Studio
The easiest way to learn and understand how tables work and how they are created is to frequently create tables and experiment. You can create tables using the SSMS graphical interface or by coding in T-SQL - Transact-SQL. The first method is easier, but the second one will also be frequently required when you use the First Code Entity Approach while creating and linking databases with entities. Therefore, let's do a practical example.
- Start your server and log in to SSMS. To create a table, you first need to create a database or use an already created database in which you want to create your table. So, in SSMS's Object Explorer, right-click on the Databases directory. In the menu, click on Create Database… and in the New Database form, simply enter the database name School and click the OK button.
- Once you have created the database, in the Object Explorer, expand the directories and under the Tables subdirectory of the School directory, right-click and in the menu, click on the Table… item; the Design form will open, which is used exclusively for designing tables. In the Design form, enter the following information under Column Name and Data Type:
ID_Student int
[First Name] nvarchar(15)
[Last Name] nvarchar(25)
Subject varchar(30)
Marks
tinyint
We created the first column, ID_Student, to serve as the primary key. So, right-click on the first row and in the menu, click on Set Primary Key. A yellow key icon will appear at the beginning of your row. This means you have defined the ID_Student column as the primary key. However, this is not enough. You do not want to manually enter numbers 1, 2, 3 … every time you add a new student to the table; you want the column to be auto-generated and automatically write the numbers 1, 2, 3 … in the ID_Student column whenever you add a new student to the table. To achieve this, find the Identity Specification property in the Column Properties and set (IsIdentity) to Yes.
Save the table and name it Students, and the Students directory will appear as a subdirectory under the Tables directory. Always strive to name all your tables in the plural form. The table should be named Students, not Student. Tables are always named in the plural, while columns are named in the singular; this is the practice. If you do not see the subdirectory of your created table Students, click on the Tables directory, then right-click to open the menu and click Refresh.
If you have done everything as mentioned here, congratulations! You have created your first table. You might wonder why the First Name and Last Name columns are written in brackets [ ]. In practice, column names are usually written without brackets and without spaces, like FirstName and LastName, but we intentionally used brackets here so that when you look at the column names in the table we created, you see readable column names.
- Open the Design form by right-clicking on the table in the Object Explorer or click on the Columns directory, then New Column… and the Design form for modifying your table will open again. Name it, for example, NewColumn.
- You can rename your column either through the menu and the Rename option or through the Design form. Change the name NewColumn to RenameColumn.
- Similarly, you can delete your added column by clicking Delete in the menu. So, delete the RenameColumn column. It was added to the table just to demonstrate adding, renaming, and deleting a column in a table.
- However, let's enter some data into our table. Right-click on the table and in the menu, click on Edit Top 200 Rows and enter the following data. As you can see, the ID_Student column in the table is read-only; you cannot enter data into it because it is auto-generated, as we have already mentioned. Therefore, skip this column and enter data into the next three columns; for example:
Manuel Radovanović English 10
Bill Gates Mathematics 90
- Save the data by clicking on the Save icon in the Toolbar or simply close the table. When you open it again, your data will be in the table.
- The table can also be renamed. Right-click on your table and in the menu, select Rename. Change Students to Students2016 and press ENTER on your keyboard.
- If you want to completely delete the table from the database, simply right-click on the name of your table in the Object Explorer directory and in the menu, click on Delete. A Delete Object form will open. Click OK and your table will be deleted.
You can also watch a video to see how this entire process
from creating to deleting a table looks:
T-SQL: Advanced techniques for creating tables and managing data
We will do the same previous example; except that we will use the T-SQL query structure language. Start SSMS and click the New Query button in the ToolBar to open the SQL Editor. Type the following code:
-- Create database
USE master
CREATE DATABASE School
GO
Select the specified commands and you will create the School database. Then type the following code:
USE School
GO
CREATE TABLE Students
(
ID_Student int IDENTITY NOT NULL,
[First
Name] nvarchar(15),
[Last
Name] nvarchar(20),
[Subject] varchar(30),
Marks tinyint
)
GO
The preceding code will create the Students table with columns for student ID, first name, last name, subject, and grade. Pay attention, since we are working with a table in which data will be entered in English. Only the first and last name fields are of the nvarchar(n) type, and that is why it is possible to enter Uncode characters in these columns so that you can use names in all languages. But this is not the case for the subject, since all subjects will be written in English.
So we use the varchar(n) type for that column. This data type takes twice as many bytes as the nvarchar(n) type, and in this way you have successfully optimized the subject column. From the previous example, it is clear to you why we use brackets [ ]. Simply that we can use a blank space in the column name to make the column names in the tables look more readable. But why the subject? Because Subject is a keyword in T-SQL and without the brackets [ ] you would get an error when executing the above code. You select the code and click on Execute. Look at the table you created. How to add a new column to an existing table?
- Add column into the table
USE School
GO
ALTER TABLE Students
ADD NewColumn varchar(10)
GO
As you can see in the given code; very simple. The ALTER TABLE command allows you to modify a table, while the ADD command will add a new column to your table. The next question is, how to change the name of an already existing column? Run the following code:
-- Rename column into the table
USE School
GO
EXEC sp_rename 'Students.NewColumn','RenameColumn','COLUMN';
GO
As you can see, sp_rename is not a command, but a built-in SQL function. Functions are started with the EXEC command. Given that the same function is used to change the name of other database objects, we must also specify the name of the object on which the function is executed. In this case it is a column. 'COLUMN' Look at the following code:
-- Delete column into the table
USE School
GO
ALTER TABLE Students
DROP COLUMN RenameColumn
GO
The preceding code is used to delete a column from a table. For this functionality we use the DROP command. By the way, we use T-SQL not only to modify objects in the database, but with it you can also enter data into tables. For this purpose, we use the command INSERT INTO.
-- Insert data into the
table
USE School
GO
INSERT INTO Students
VALUES ('Manuel','Radovanović','English', 90),
('Bill','Gates','Mathematics', 10)
GO
Run the above code to execute, right click on your table; then in the menu click on Edit Top 200 Rows and you will see that the data has been entered and saved in the table. Changing the table name is also easy:
-- Rename table
USE School
GO
EXEC sp_rename 'Students','Students2016'
GO
When you run the above code, the Students table will be renamed to Students2016. Deleting a table is also not complicated. Simply use the DROP command:
-- Delete table
USE School
GO
DROP TABLE Students2016
GO
How this whole example that we have explained works; you can also watch it in the following video.
No comments:
Post a Comment