Skip to main content

How to Manage Database with Creating and Storing information in SQL

As a database developer, programmer might need to create databases to store information. At times, you might also delete a database, if it is not required. Therefore, it is essential to know how to create and delete a database.
The SQL Server contains some standard system databases. Before creating a database, it is important to identify the system databases supported by SQL Server and their importance.

System databases are the standard databases that exist in every instance of SQL Server. These databases contain a specific set of tables that are used to store server-specific configurations, templates for other databases. In addition, these databases contain a temporary storage area required to query the database.

SQL Server contains the following system databases:

master Database

The master database records all the server-specific configuration information, including authoried users, databases, system configuration settings, and remote servers. In addition, it records the instance-wide metadata, such as logon accounts, endpoints, and system configuration settings.

The master database contains critical data that controls the SQL Server operations. It is advisable not to give any permission to users on the master database. It is also important to update the backups of the master database to reflect the changes that take place in the database as the master database records the existence of all other databases and the location of those database files.

The master database also stores the initialization information of the SQL Server. Therefore if the master database is unavailable, the SQL Server database engine will not be started.

tempdb database

The tempdb database is a temporary database that holds all temporary tables and sor procedures. It is automatically used by the server to resolve large or nested queries or to sort data before displaying the results to the user.
All the temporary tables and results generated by the GROOUP BY, ORDER BY, and DISTINCT clauses are stored in the tempdb database. You should not save any database object in the tempdb database because this database is recreated every times the SQL Server starts. This results in loosing data you saved earlier.

model database

The model database acts as a template or a prototype for the new databases. Whenever a database is created, the contents of the model database are copied to the new database.
In this database, you can set the default values for the various arguments to be specified in the Data Definition Language (DDL) statements to create database objects. In addition, if you want every new database to contain a particular database object, you can add the object to the model database. After this, whenever you create a new database the object will also be added to the database.

msdb Database

The msdb database supports the SQL Server Agent. The SQL Server Agent is a tool that schedules periodic activities of the SQL Server, such as backup and database mailing. The msdb database contains task scheduling, exception handling, alert management, and system operator information needed for the SQL Executive Service. The msdb database contains a few system-defined tables that are specific to the database.
As a database developer, you can query this database to retrieve information on alerts, exceptions, and schedules. For example, you can query this database to know the schedule for the next backup and to know the history of previously scheduled backups. You can also query this database to know how many database e-mail messages have been sent to the administrator. However, there are tools available to perform these database administration tasks.

resource Database

The Resource database is a read-only database that contains all the system objects, such as system-defined procedures and view that are included with SQL Server. The Resource database does not contain user data or user metadata.


Popular posts from this blog

difference between structure and union in C Language

In c language article we will see the difference between union and structure. Both are the user define datatype in c language. See the table which is mentioned below: ASP.NET Video Tutorial Series Structure Union1.The keywordstruct is used to define a structure 1. The keyword union is used to define a union. 2. When a variable is associated with a structure, the compiler allocates the memory for each member. The size of structure is greater than or equal to the sum ofsizes of its members. The smaller members may end with unused slack bytes. 2. When a variable is associated with a union, thecompiler allocates thememory by considering the size of the largest memory. So, size of union is equal to the size of largest member. 3. Each member within a structure is assigned unique storage area of location. 3. Memory allocated is shared by individual members of union. 4. The address of each member will be in ascending order This indicates that memory for each member will start at different offset v…

Difference between Linear search and Binary Search in c language

SQL Video Channel : Download all SQL Video

Binary Search Linear Search Works only on sorted items. such as  1,2,3,4,5,6  etc
Works on sorted as well as unsorted items. 12,4,5,3,2,1 etc Very efficient if the items are sorted Very efficient if the items are less and present in the beginning of the list. such as Suppose your list items are : 12,3,4,5,1 and you want to search 12 number then you get beginning in the list. Works well with arrays and not on linked lists. Works with arrays and linked lists.
Number of comparisons are less More number of comparisons are required if the items are present in the later part of the array or its elements are more.

Memory representation of Linked List Data Structures in C Language

Memory representation of Linked List

             In memory the linked list is stored in scattered cells (locations).The memory for each node is allocated dynamically means as and when required. So the Linked List can increase as per the user wish and the size is not fixed, it can vary.

               Suppose first node of linked list is allocated with an address 1008. Its graphical representation looks like the figure shown below:

      Suppose next node is allocated at an address 506, so the list becomes,

  Suppose next node is allocated with an address with an address 10,s the list become,

The other way to represent the linked list is as shown below:

 In the above representation the data stored in the linked list is “INDIA”, the information part of each node contains one character. The external pointer root points to first node’s address 1005. The link part of the node containing information I contains 1007, the address of next node. The last node …