As we mentioned in the previous post, take a look here: a database is an organized collection of data for quick search and access. Together with the maintenance and administration system, organizing and storing this data constitutes the database system. Simply put, think of it as a set of objects with some relationship between them. Databases are much more than what developers perceive, and working with databases is multifaceted. This means that there are multiple ways to accomplish the same task with a database. The simplest way to create a database in Microsoft SQL Server is to do it through SSMS - Microsoft SQL Server Management Studio in its graphical environment.
Once you launch SSMS and successfully log in, you’ll see the Object Explorer on your left. Right-click the Databases node and select New Database. In the Database name field, enter the name of the database you want to create. If you want everything to remain at the default settings for creating a database, simply press OK, and SSMS will create a new database for you. If you don’t see your newly created database in the Database directory in Object Explorer, right-click the Database directory and click Refresh. Keep in mind that you’ll often need to use Refresh in Object Explorer to see changes when you create them.
In principle, you can create multiple databases or consolidate all your data into one database. This mostly depends on the type of data you’re storing and whether those data are related. Sometimes it’s desirable to place all data in a single database, and sometimes it’s not. When creating a database for practice and learning, accepting default values is fine. However, if you’re intentionally designing a database, you should specify values for the initial database size, its growth, or even set a maximum limit for the database size. It’s advisable to always set the largest possible size you expect your data to reach.
How do I define my values for the database I want to create?
When you right-click the Database node and select New Database, a dialog form will open where you can specify additional values alongside the database name. When assigning a name to the database, treat it similarly to naming a variable in programming. The same rules for variable names apply to database names. This means that you cannot use special characters in the database name, and I also recommend avoiding spaces. When you assign a name to a database, it serves as a logical name and is often used for the physical files that constitute your database on the storage device. Frequently, the database name and the log file name are the same, even though you can specify all three names differently.
In the Owner option, you can set the owner of the database and their administrative rights. If you leave the value as <default>, the database owner will be the Windows user who created it. Additionally, you can change the location where you store the database files, set the initial size, and define a maximum size. Regarding database growth, you can adjust it in either MB - megabytes or percentages. Typically, the transaction log file is slightly smaller than the data file, and it’s often limited even when the database isn’t.
Mastering Database Operations in SSMS: The Basics of Database Creation, Renaming, and Removal
You can most easily change the logical name of your database in Object Explorer, just as you would rename any file in File Explorer; just keep in mind that by changing the logical name of your database, you have not changed the name of the files on the hard disk. As for deleting a database, the simplest way to delete a database is to click on the Databases node in Object Explorer, then right-click on your database and then click Delete. To learn how to create, set some values, rename, and delete a database in the simplest way, you can also watch the following video.
T-SQL Guide: How to Create, Rename, and Delete Databases Efficiently
It is much easier to work with a database in a graphical environment, but sometimes, as a developer working in a large company, you won’t have that privilege; server and database administrators do not allow it. Meanwhile, knowledge of T-SQL language is assumed for programming and for creating SQL scripts. T-SQL, short for Transact-SQL, where SQL stands for Structured Query Language, is a structured query language and is easy to learn. For experienced programmers and database administrators, it is sometimes even easier to use T-SQL for database operations than a graphical environment. Open SSMS, click on File, then on New, and on Query with Current Connection, or click the New Query button on the Toolbar. This will open the SQL Editor. Then enter the following T-SQL code.
-- My first T-SQL
-- Create Database Files
USE master;
GO
CREATE DATABASE Students
ON
( NAME = Sales_dat,
FILENAME = 'D:\Program Files\Microsoft
SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\students.mdf',
SIZE = 100MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 100MB)
LOG ON
( NAME = Sales_log,
FILENAME = 'D:\Program Files\Microsoft
SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\studentslog.ldf',
SIZE = 50MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 80MB);
GO
Please note that the path name where the Students database files are located is not the same as yours, unless you have also named your server instance MSSQL, i.e., left the name as default and changed the directory path to D. If you haven’t assigned a name to your server at all, the path is probably as follows:
'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\students.mdf'
The first thing you can do in this code is to write comments with -- . You can also conclude that the database creation is executed by a T-SQL command:
CREATE DATABASE database_name
Since we are setting our values for the database we are creating, we write the properties in small brackets ( ) separated by commas. Otherwise, if we wanted to create a database by default for creating a database, we could have done it like this.
USE master;
GO
CREATE DATABASE Students
When you have correctly written code in the SQL Editor, the code is executed by selecting the part of the code that you want to execute and pressing the button ! Execute. Select the code and execute it and SSMS will create a database for you with the values you specified. In the Object Explorer, click Refresh to see the Students database created. If you managed to do everything successfully, add the following code below the written code:
-- Rename Database Files
USE master;
GO
ALTER DATABASE Students
Modify Name = School;
GO
When you select the following code and execute it, it will change the name of your database from Students to School. Note that this change is only related to the logical name of your database. Which means your database files are still named the same as before. To change values or modify databases, we always use the T-SQL keyword:
ALTER DATABASE database_name
And if you want to delete the database then the following command is used:
DROP DATABASE database_name
So, if you want to delete a database that uses the logical name School, you must specify the changed logical name of the database.
-- Delete Database Files
USE master;
GO
DROP DATABASE School;
GO
You can save all the T-SQL code you wrote in a textual file with the existence *.sql and use it whenever you want. We often use a T-SQL code file when we want to transfer our database from our server to another server or for example: to a home computer if you work with the database both at work and at home. You can also see what work with the specified code looks like in the following video:
When you start learning databases, you often need an already built database like AdvantureWorks or Northwind to study how these databases are built or to use them for your practice in your programs. That's why we call these databases the Sample Database, and you should definitely have these databases. You can download both from our blog, here. By including an existing database on Microsoft SQL Server, we work by right-clicking on the Database node in Object Explorer and then clicking on Attach or Restore if you are using a backup of a database. Enter the path with our database files that we want to include in our server and press OK. Disconnecting the database from your server is definitely a better option than deleting the database. You do this by clicking on the database on your server and then clicking Detach. But note that you may get an error that way because you have to run SSMS as Run as administrator for detach to work. It's best to watch the following video to see how it all works.
How to Effectively Use SQL Script to Create a Database with Data
If you have a database with data converted to SQL script, it is a much better way to create the same database with all its data than to have a database created or even backed up. The reason? Experience! Imagine that you archived 5 years of effort and work with the database and then you cannot use the archive because it contains some error that you didn't even know existed because everything was archived without a single error. Simply, archives often suffer because of bad sectors on the hard disk. Whereas an SQL script is just a text file. That's why we think that from time to time you should create a SQL script with all the data of your database. See in the following video how easy it is to create a Northwind database when you have saved it as an SQL script. Perhaps one of you esteemed blog readers can think who would write the thousands of lines of T-SQL code that a Northwind database script contains? For large databases, Generate Scripts is used to generate SQL scripts. You have this wizard in SSMS. Watch in the following video how we created the Northwind database with SQL Script.
No comments:
Post a Comment