SQL Server databases are among the most widely used, in part due to the ease with which they are created and maintained. Along with a free graphical user interface (GUI) like SQL Server Management, you don't have to mess with commands through the Command Prompt. Continue reading Step 1 below to create a database in minutes and start entering your data.
Step 1. Install the SQL Server Management Studio software
This software is free to download from the Microsoft website, and provides the ability to connect to and manage a SQL server through a graphical interface, rather than the command prompt.
- To be able to connect to an external SQL server, you need this or equivalent software.
- If you have a Mac, you can use open source software such as DbVisualizer or SQuirreL SQL. The interfaces are different, but the principles are the same.
- See wikiHow for articles on creating databases using command prompt tools.
Step 2. Start SQL Server Management Studio
When you start the program for the first time, you will be asked which server you want to connect to. If you already have a server running and the rights to connect to it, you can enter the server address and login details. If you want to create a local database, enter a. as the name for the database and set the login method as "Windows Authentication".
Click Connect to continue
Step 3. Locate the Databases folder
After connecting to the server, local or remote, the Object Explorer window will open on the left side of the screen. At the top of the Object Explorer tree you will find the server you are connected to. If it isn't expanded, click on the "+" next to it. Now you have found the Databases folder.
Step 4. Create a new database
Right click on the Databases folder and select "New Database…". A window will appear, allowing you to configure the database before configuring it. Give the database a name that allows you to recognize it. Most users can leave the rest of the settings as they are.
- You will notice that if you enter the name of the database, two additional files will be created: the Data and Log file. The data file contains all the data of your database and the log file keeps track of changes to the database.
- Click OK to create the database. You will see the new database appear in the expanded Databases folder. It has a cylinder as its icon.
Step 5. Create a table
A database can only store data if you first create a structure for that data. A table contains the information you enter into your database, and you will need to create such a table before continuing. Expand the new database in your Databases folder, and right click on the Tables folder and select "New Table…".
Several windows will appear that allow you to edit the new table
Step 6. Create the Primary Key
It is highly recommended that you create a primary key as the first column of your table. This behaves like an ID, or record (row) number, with which you can easily recall these entries later. Create it and enter an "ID" in the Column Name field, type int in the Data Type field and uncheck the "Allow Nulls" box. Click the key icon in the menu bar to set this column as the primary key.
- You don't want to accept zero values because an input must always be at least "1". If you do allow a zero value, your first entry will be a "0".
- In the Column Properties window, scroll down until you see the Identity Specification option. Expand it and set "(ls Identity)" to "Yes". This automatically increments the value of ID for each newly added row, meaning each new entry is automatically numbered.
Step 7. Understand how tables are structured
Tables consist of fields, also known as columns. Each column is a representation of a database entry. For example, if you create a database of employees, you would have for example a column "First name", "Last name" and "Address", and a column "Phone number".
Step 8. Create the rest of your columns
When you're done filling in the primary key fields, you'll notice that new fields have appeared below them. This gives you the opportunity to enter the next row of data. Fill in the fields as you see fit and make sure you use the correct data type for the data you enter in that column:
- nchar(#) – This is the data type you use for text, such as names, addresses, etc. The numbers in parentheses indicate the maximum number of characters allowed in a given field. By setting a limit you can be sure that the size of your database remains manageable. Phone numbers should be stored in this format, because you don't perform arithmetic on them.
- int – This is for integers and is usually used for the ID field.
- decimal(x, y) – Stores numbers in decimal form, and the numbers in parentheses indicate the total number of digits and the number of decimal places respectively. For example: decimal(6, 2) stores numbers as 0000.00.
Step 9. Save your table
When you have finished creating columns, you will first have to save the table before you can enter data. Click on the Save icon in the toolbar and enter a name for the table. It's best to give your table a name that makes it clear what its contents are, especially with larger databases with multiple tables.
Step 10. Add data to your table
Once you have saved the table, you can start entering data. Expand the Tables folder in the Object Explorer window. If the new table is not listed, right click on the Tables folder and select Refresh. Right click on the table and select "Edit Top 200 Rows".
- The middle window will show fields where you can start entering data. The ID field will be filled automatically, so you can ignore it for now. Fill in the information in the rest of the fields. When you click on the next row you will see that ID in the first row is automatically filled.
- Continue with this until you have entered all the necessary information.
Step 11. Process the table to save the data
Click Execute SQL in the toolbar when you are done entering the data to save it. The SQL server continues to run in the background, processing all data contained in the columns. The button looks like a red exclamation mark. You can also press Ctrl+R to run it.
If errors are discovered, you will receive an overview of which entries have been entered incorrectly before the table can be processed
Step 12. Consult your details
At this point, your database has been created. You can create as many tables as needed within each database (there is a limit, but most users won't have to worry about that unless they're working with enterprise-level databases). You can now request your data for reports or other administrative purposes. Read articles on wikiHow for more detailed information about running queries.