SQL server databases are one of the most used today. This is due to the ease they offer to create and maintain databases. With a free graphical user interface (GUI) program, such as SQL Server Management, you don't need to worry about testing with the command line. Read the steps below to create a database and start entering your information in just a few minutes.
Steps
Step 1. Install the SQL Server Management Studio program
This software is freely available from Microsoft, and it allows you to connect and manage your SQL server from a graphical interface instead of having to use the command line.
- To connect to a remote instance of an SQL server, you will need this or similar software.
- Mac users can use open source programs like DbVisualizer or SQuirrel SQL. The interfaces will be different but the same general principles apply.
- To learn how to create databases using command line tools, check out this guide.
Step 2. Start the SQL Server Management Studio
When you start the program, you will be asked which server you would like to connect to. If you already have a running server and you have the necessary permissions to connect to it, you can enter the server address and authentication information. If you want to create a local database, set the database name to "." and the authentication type as "Windows Authentication".
Click "Connect" to continue
Step 3. Locate the "Database" folder
After the connection to the server is made (either local or remote), the "Object Explorer" window will open on the left side of the screen. At the top of the "Object Explorer" tree, there will be the server you are connected to. If it's not expanded, click the "+" icon next to it. You will find the "Database" folder.
Step 4. Create a new database
Right-click on the "Database" folder and select "New Database." A window will appear allowing you to configure the database before creating it. Give the database a name to help you identify it. Most users leave the default settings.
- As you type in the name of the database, you will notice that two additional folders will be created automatically: the "Data" and "Registry" folders. The "Data" folder houses all the data in your database, while the "Registry" folder controls changes to the database.
- Click the "OK" button to create the database. Your new database will appear in the expanded "Database" folder. It will have an icon of a cylinder.
Step 5. Create a table
A database can only store data if you create a structure for that data. A table contains the information that you enter into your database and you will have to create it before you can proceed. Expand the new database in your "Database" folder, right-click on the "Tables" folder and select "New Table."
Windows will open on the rest of the screen, allowing you to manipulate your new table
Step 6. Create the primary key
It is highly recommended that you create a primary key in the first column of the table. This acts as an identity number or registration number, which will allow you to remember these entries later. To create it, enter "Identity" in the "Column name" field, type "int" in the "Data type" field, and uncheck "Allow null". Click the key icon on the toolbar to set this column as the primary key.
- You don't need to allow null values because the inputs must always be at least "1". If you allow null values, your first entry will be "0".
- In the "Column Properties" window, scroll down until you find the "Identity Specification" option. Expand it and set "(It's identity)" to "Yes." This will automatically increment the value in the "Identity" column for each entry, effectively automatically listing each new entry.
Step 7. Understand how tables are structured
Tables are made up of fields or columns. Each column represents one aspect of a database entry. For example, if you create an employee database, you can have a "First Name" column, a "Last Name" column, an "Address" column, and a "Phone" column.
Step 8. Create the rest of your columns
When you finish filling in the primary key fields, you will notice that new fields will appear below. These will allow you to enter your next columns. Fill in the fields as desired and be sure to choose the correct data type for the information to be entered in that column:
- nchar (#): this is the data type you should use for texts (like names, addresses, etc.). The number in parentheses is the maximum number of characters allowed by this field. Setting a limit ensures that the size of your database remains controllable. Telephone numbers must be stored in this format, as you should not perform any mathematical functions on them.
- int: this is for all numbers and is normally used in the "Identity" field.
- decimal (x, y): this will allow you to store numbers in decimal form and the numbers in parentheses denote respectively the total number of digits and the number of digits that follow the decimal. For example, decimal (6, 2) will store numbers that have the form 0000.00.
Step 9. Save your table
When you finish creating your columns, you will need to save the table before entering the information. Click the save icon in the toolbar and then enter a name for the table. It is recommended that you give the table a name that helps you recognize the content, especially for large databases that contain many tables.
Step 10. Add data to your table
Once you've saved your table, you can start adding data to it. Expand the "Tables" folder in the "Object Explorer" window. If your new table is not in the list, right-click on the "Tables" folder and select "Refresh." Right click on the table and select "Edit the first 200 rows".
- The central window will show you the fields so you can start entering the data. The "Identity" field will be filled in automatically, so you can ignore it. Fill in the information in the rest of the fields. When you click on the next row, the "Identity" field, in the first row, will be filled in automatically.
- Continue with this process until you have entered all the information you need.
Step 11. Run the table to save the data
Click the "Run SQL" button on the toolbar when you finish entering the information to save it in the table. The SQL server will run in the background and analyze all the data in the columns you created. The button looks like a red exclamation point. You can also press Ctrl + R to run it.
If there are any errors, you will be shown the entries that were filled incorrectly before the table can be executed
Step 12. Check your data
At this point, your database should have been created. You can create as many tables as you need within each database (there is a limit, but most users don't have to worry about that, unless they work for a database company). Now you can query your data for reports or any other administrative purpose. For detailed information on execution queries, search for related articles.