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, 
    • MySQL, 
    • PostgreSQL, 
    • Oracle, 
    • Sybase,
    • 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 :
    • Entry, 
    • 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.
    • RetrievalFetching the data from the databases using select statement. We can join multiple tables to retrieve data from different tables too.
    • AdministrationMonitoring 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.


    Object explorer – This panel in sql server management studio will provide us the list of databases and associated objects for each database. We can able to see the :
server name listed in the top -> databases -> Your database will be listed under it -> Objects in the databases
    Here is the Databases folder which will have all the databases and object for each database under it in the tree view format.
    Right click on the databases icon and select “new database“option to create a new database.

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.

    3 tabs will be there in the new database 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.
    Here in this output WikiTechy_SQLServer specify the name of the database. In this case, we provide it as “WikiTechy_SQLServer”.
    In this output <default> specify owner of the database. By default, the value is “default“which indicates the logged in user who creates the database is the owner of the database.
    Generally, for a database, there will be two files created.
    • 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
  1. Option 4 is to provide the master file and its location and size. 
  2. By default, database name will be taken as its name and it can be changed. 
  3. Entire data will be stored in this file.
    Here WikiTechy_SQLServer_log specifies the Log file. In details

of the file can be specified. This file is used to store all the DML operations happening in the database.
    In the above example Connection specifies the server name and its connection properties.
    Here Add button is specifying that we can add any number of ldf files for the database. By default, a database will have 1 mdf and any number of ldf files.
    Clicking the OK button database is created.

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 5 : Here we can able to see the new name is reflected in the server. In this case, we created a database as WikiTechy_SQLServer and we renamed it as aaWikiTechy_SQLServer. We are able to see the same in the server.


Step 6 : We can create the database via sql query too.

    Here in this output New query button specifies to create the query window which is specified as 4 in the above picture.
    We can select the database at this drop down so that, the query executed in window 4 will be executed in the selected databases.
    Here execute specifies the query execution in the selected database as specified in option 2.
    In this New query window, we can type our queries.

Step 7 : Below is the sample query given in order to create the database.

Sample Code :

    Here we created the database “abwikiTechy_SQLServer“using the create database syntax.

Output :

    Here CREATE DATABASE abwikitechy_SQLServer Query specifies the database creation in the query window.
    Here by clicking the execute button ,  the query execution is done by executing the query.
    Here in this output we display the message “Command(s) completed successfully.“ which specifies the query execution was successfully executed.
    Now, we are able to see the newly created database in the server explorer. Or else, click refresh button in the top of server explorer window to see the new databases created.