SQLServer - Database Creation
SQL Server - Database Creation
- Database Creation in SQL Server is nothing but the creation of the database in the form of tables, where we can able to store data.
- In general, in order to begin the SQL Server database creation, we will be using the “SQL Server Management Studio” from the Windows Start Menu, select “Microsoft SQL Server”, and then we need to proceed with “SQL Server Management Studio”.
What is database?
- In general, the term database is considered to be an organized collection of data.
- A database is being considered to be a whole box which will be holding up the mix-up of collection of objects such as,
- Schemas – Schemas is a structure and diagrams.
- Tables – Table is specifying the collection of data.
- Queries – Stored procedures and functions.
- Reports – in SQL Server, rdl (Report Definition Language) reports to show up the data.
- Views – It is an alternative for tables in displaying the data.
- Other objects – Other Objects are used for security logins and management related objects.
- Entire data in the database were typically organized so that, it is flexible to retrieve any kind and any way of data, for example, if we provide the student information into the database, we are able to retrieve any kind of data such as rank of the student which is not served into the database but manipulate from the data stored in the database.
What is database management system?
- DBMS (Database Management System) is a computer software application or a set of programs which will take care of managing the databases.
- This will enable us to achieve some of the functionalities like,
- Who is updating the data into the database – which we need to Keep track of everything
- What is updated into the database – which we need to Keep track of everything
- When it is updated into the database – which we need to Keep track of everything
- How it is updated into the database – which we need to Keep track of everything
- DBMS is designed to allow the definition, creation, querying, update, and administration of databases.
- Familiar DBMSs include :
- Microsoft SQL Server,
- SAP HANA, and
- IBM DB2.
- In conventional system, we got Files which will be used to store the data,
- Accessing data from the file becomes real complex and to overcome this scenario, DATABASE world comes into the picture to solve this complex problem.
- In general, “database" means “set of related data and the way it is organized“.
- Access to these data is usually provided and controlled by “database management system" (DBMS) and this can be achieved through an integrated set of computer software that allows users or database developers to interact with one or more databases and provides access to all of the data contained in the database.
- The DBMS software enables the end user to access various functions that allow some of the key features which is shown below :
- Storage and
- Retrieval of large quantities of information,
- Finally, provides ways to manage how that information needs to be organized.
- Four main functional groups of DBMS software which we have to note it down are as follows,
- Data definition – Creation, modification and removal of objects inside the database.
- Update – The UPDATE statement is used to update the records in a table. Common table expressions also can be used with the Insertion, modification, and deletion of the actual data inside the table.
- Retrieval – Fetching the data from the databases using select statement. We can join multiple tables to retrieve data from different tables too.
- Administration – Monitoring performance, maintaining data integrity, Registering and monitoring users, enforcing data security, dealing with concurrency control, and recovering information that has been corrupted by some event such as an unexpected system failure.
Here is the structure of the database :
- Server will have multiple databases and databases will have multiple tables in it.
Sample Code :
CREATE DATABASE abWikiTechy_SQLServer GO
Step by step procedure :
Steps to create the database in SQL Server :
Step 1: Database creation and the entire process of sql server can be achieved through the below GUI that’s, SQL Server Management Studio.
|server name listed in the top -> databases -> Your database will be listed under it -> Objects in the databases|
Step 2 : We will be popped up with a new window here. Here are some of the options you can see it in this new window.
- General - General tab will provide the details about the database like database name and where it needs to be stored and database connection.
- Options – This tab will provide more preferences to maintain the database such as collation, recovery model, auto update, auto shrink, compatibility mode.
- Filegroups – provides the information of collating this database under which file group the administrator wants to put. This is for each filtering or controlling the database.
- Master file – (mdf file) Master file is a collection of records that is connecting to one of the main subjects of an information system, such as customers, employees, products and vendors.
- Log file – (ldf file) LDF is a file extension for a log file used with Microsoft SQL Server. LDF files contain logging information for all transactions completed by the server. The main purpose of using log file in SQL Server is to rollback the database in case of data is lost. Log file uses the same filename as its associated MDF file.
- Option 4 is to provide the master file and its location and size.
- By default, database name will be taken as its name and it can be changed.
- Entire data will be stored in this file.
of the file can be specified. This file is used to store all the DML operations happening in the database.
Step 3 : Database is created in the sqlserver and we are able to see the database in the Server Explorer window.
Step 4 : Renaming the database in sql server can be done by right clicking the database for which the name should be renamed and select the option “rename“. Now specify the new name which we want to provide to the database.
Step 7 : Below is the sample query given in order to create the database.