Good morning friends..As I mentioned in my last post that I will talk about how to create a user database in my next post. So lets start today's session.
There are two ways to create a user database in SQL server. following are the two ways of creating database.
1)Using SQL Server database creation wizard
2)Using T-SQL Scripts.
I will teach you both ways of creating the database.
So lets start with first option(using wizard).
I have opened the SQL Server Management Studio on my laptop and below is the screen shot of the same.
For creating a user database, we need to right click on the databases folder(as shown in the picture) and then a menu with different options will appear in front of us. As we want to create a new database, we will select the "new database" option from the menu. As soon as we click on the option "new database", a new window will pop up in front of us.(Please see the screen shot of the new window)
In the above window there is a text field in front of the "database name" label, we can provide a relevant name to the database by filling that text field. There is one more label underneath "database name" label, "owner", that indicates the owner of the database. Typically if we are logged into the management studio using "Sa" login then owner would be "Sa" and if we are logged into the management studio using windows authentication then our windows login would be the owner of the database. Lets understand different options available in the wizard window. In the above window, there are two rows and the description for each row is given below.
1)First row is having all the information about the primary data file that is also known as(.mdf file).
2)Second row in having all the information about the log file that is also known as (.ldf file).
Each row contains following information :-
1) Logical filename :- This field is automatically filled when we type in name of the database in the database name text field but if we want to change the name we can change it.
2)File Type:- This field tells us whether this file will contain data rows or transaction logs.
3)File Group:- This field tell us about the filegroup associated with the database file(either primary or secondary)
4)Initial Size(In MB):- This field tell us about the initial size of the database when created,we can set the size of the data file and log file at the creation of the database.By default data file size is 2 MB and log file is 1 MB of size.
5)Autogrowth:- To understand autogrowth option, lets have a look on the below screen shot.
We can set this option enabled or disabled. by default it is enabled. You can see in the screen shot there are two options available, first one is for file growth, you can put the file growth rate in percentage or in Megabytes. Let me make you understand in a better way.Suppose you have a database of size 2 MB and you have put file growth size in Megabytes (5 MB) , now your database is online and data rows are getting inserted in the tables. As soon as your data size in the tables reached at 2 MB i.e. your current size of the database, insertion of one more data row will results into grow your database size by 5 MB(as you have put the growth rate 5 MB).
Second option is Maximum file size:- You can limit your database size by selecting "restricted file growth" option.
6)Path :- File path field contains the full physical path of the data file and log file on the disk. You can modify it according to your needs.
7)Filename :- This field contains the physical file name, you can provide any meaning full name to your database files.
Lets try to understand more options available on the database creation wizard window.
At the left side there are two more options available, when we click on options menu, we can see different set of attributes, if I take all the attributes one by one , we will not be able to create database today :)
I will explain these options in my subsequent blogs, But there is one option that I really want to explain now.
Compatibility level :-You can set you database compatibility level using this option and make it backward compatible with SQL older versions. If you set that option as SQL Server 2000, it will allow you to use the older features of version 2000.
Second option at the left side is file group, Please see below screen shot for your reference.
You can see the add button, using add button we can add more file groups and then further we can associate one or more files to that file group. Creation of file group is a different topic that I will cover it in advanced learning, for now you just keep it in your mind that using file group, we can spread our data on multiple drives for optimization of performance.
Now we are read to create the database, As soon as you click on "OK" button, a database will be created in SQL Server. This way we can create a user database using wizard. How to create a user database using T-SQL scripts, I will let you know in my next post. Till then take care bye bye.
There are two ways to create a user database in SQL server. following are the two ways of creating database.
1)Using SQL Server database creation wizard
2)Using T-SQL Scripts.
I will teach you both ways of creating the database.
So lets start with first option(using wizard).
I have opened the SQL Server Management Studio on my laptop and below is the screen shot of the same.
For creating a user database, we need to right click on the databases folder(as shown in the picture) and then a menu with different options will appear in front of us. As we want to create a new database, we will select the "new database" option from the menu. As soon as we click on the option "new database", a new window will pop up in front of us.(Please see the screen shot of the new window)
In the above window there is a text field in front of the "database name" label, we can provide a relevant name to the database by filling that text field. There is one more label underneath "database name" label, "owner", that indicates the owner of the database. Typically if we are logged into the management studio using "Sa" login then owner would be "Sa" and if we are logged into the management studio using windows authentication then our windows login would be the owner of the database. Lets understand different options available in the wizard window. In the above window, there are two rows and the description for each row is given below.
1)First row is having all the information about the primary data file that is also known as(.mdf file).
2)Second row in having all the information about the log file that is also known as (.ldf file).
Each row contains following information :-
1) Logical filename :- This field is automatically filled when we type in name of the database in the database name text field but if we want to change the name we can change it.
2)File Type:- This field tells us whether this file will contain data rows or transaction logs.
3)File Group:- This field tell us about the filegroup associated with the database file(either primary or secondary)
4)Initial Size(In MB):- This field tell us about the initial size of the database when created,we can set the size of the data file and log file at the creation of the database.By default data file size is 2 MB and log file is 1 MB of size.
5)Autogrowth:- To understand autogrowth option, lets have a look on the below screen shot.
We can set this option enabled or disabled. by default it is enabled. You can see in the screen shot there are two options available, first one is for file growth, you can put the file growth rate in percentage or in Megabytes. Let me make you understand in a better way.Suppose you have a database of size 2 MB and you have put file growth size in Megabytes (5 MB) , now your database is online and data rows are getting inserted in the tables. As soon as your data size in the tables reached at 2 MB i.e. your current size of the database, insertion of one more data row will results into grow your database size by 5 MB(as you have put the growth rate 5 MB).
Second option is Maximum file size:- You can limit your database size by selecting "restricted file growth" option.
6)Path :- File path field contains the full physical path of the data file and log file on the disk. You can modify it according to your needs.
7)Filename :- This field contains the physical file name, you can provide any meaning full name to your database files.
Lets try to understand more options available on the database creation wizard window.
At the left side there are two more options available, when we click on options menu, we can see different set of attributes, if I take all the attributes one by one , we will not be able to create database today :)
I will explain these options in my subsequent blogs, But there is one option that I really want to explain now.
Compatibility level :-You can set you database compatibility level using this option and make it backward compatible with SQL older versions. If you set that option as SQL Server 2000, it will allow you to use the older features of version 2000.
Second option at the left side is file group, Please see below screen shot for your reference.
You can see the add button, using add button we can add more file groups and then further we can associate one or more files to that file group. Creation of file group is a different topic that I will cover it in advanced learning, for now you just keep it in your mind that using file group, we can spread our data on multiple drives for optimization of performance.
Now we are read to create the database, As soon as you click on "OK" button, a database will be created in SQL Server. This way we can create a user database using wizard. How to create a user database using T-SQL scripts, I will let you know in my next post. Till then take care bye bye.
No comments:
Post a Comment