SQL Server databases are the most commonly used databases thanks to their ease of creation and maintenance. With a graphical user interface (GUI) program like SQL Server Management, you don't have to worry about using the command line. See Step 1 below to create a database and start entering information into it in just minutes.
Step
Step 1. Install the SQL Server Management Studio program
This program is available for free from Microsoft, and you can use it to connect to a SQL server and manage it via a graphical interface instead of using the command line.
- To start a remote connection with a SQL server, you will need a SQL Server Management program or similar.
- Mac users can use open-source programs like DbVisualizer or SQuirreL SQL. The interface may be different, but the general principle remains the same.
- To learn how to create a database with command line tools, take a look at this guide.
Step 2. Start SQL Server Management Studio
When you first start the program, you will be asked which server you want to connect to. If you already have a server up and running, and have the necessary permissions to connect to that server, you can enter the server address and permissions information. If you want to create a local database, fill Database Name with. and Authentication Type to "Windows Authentication".
Click Connect to continue
Step 3. Locate the Databases directory
After connecting to a server, either a local server or a remote server, the Object Explorer window will open on the left side of the screen. At the top of the Object Explorer tree, is the server you are connected to. If the server tree doesn't open and expand, click the "+" icon next to it. Find the Databases directory.
Step 4. Create a new database
Right-click the Databases directory, then select "New Database…". A window will appear, and you can manage the database before creating it in that window. Give the database a name so you can identify it. Most users can leave the rest of the settings as default.
- You will notice that when the database name is typed, two additional files will be created automatically: the Data and Log files. The Data File holds all the data in the database, while the Log file is used to monitor changes in the database.
- Click OK to create the database. You will see the new database appear in the expanded Databases directory. The database has a tube icon.
Step 5. Create a table
A database can only store data if you create a structure for the data. The tables hold the information entered into the database, and you need to create them before moving on to the next steps. Expand the new database in the Databases directory, then right-click on the Tables directory and select "New Table…".
A window where you can manipulate the new table will open on the remaining free space on the screen
Step 6. Create a Primary Key
It is highly recommended that you create the Primary Key as the first column in the table. The Primary Key acts as an ID number, or record number, which allows you to easily recall data. To create it, enter "ID" into the Column Name field, then type int into the Data Type field and uncheck "Allow Nulls". Click the lock icon on the toolbar to set the column as Primary Key.
- There should be no null values in the Primary Key column, because the values in the record must start at least "1". If you leave the value blank, the first value entered is "0".
- In the Column Properties window, scroll down until you find the Identity Specification option. Expand the options and set "(Is Identity)" to "Yes". In this way, the value of the ID column will increase each time new data is entered, so that the numbering process for each new record will be carried out automatically and effectively.
Step 7. Understand about table structure
A table consists of fields or columns. Each column represents one aspect in the database record. For example, if you are creating a database for employees, you might have "FirstName", "LastName", "Address" and "Phone Number" columns.
Step 8. Make the rest of the columns you need
When you've finished filling in the fields for the Primary Key, you'll see a new field appear below it. In the new field, you can enter the next column. Fill in the fields that you think need to be filled, and make sure that you select the correct data type for the information to be entered in the column:
- nchar(#) - This data type should be used for text, such as names, addresses, etc. The number in brackets is the maximum number of characters that can be entered in the field. By setting a size limit, you ensure that the database size remains manageable. Phone numbers should be saved in this format, because you don't use math functions with phone numbers.
- int - This data type is used for records containing only numbers, and is usually used for ID fields.
- decimal(x, y) - This data type is used to store numbers in decimal form, and the number in parentheses specifies the number of digits of the number and the number of decimal digits after the number, respectively. For example, decimal(6, 2) will store a number in the form 0000.00.
Step 9. Save your table
When you're done creating columns, you'll need to save the table before entering information into it. Click the Save icon on the toolbar, then enter a table name. It is recommended that you give the table a name that can help to identify the contents of the table, especially for larger databases with many tables.
Step 10. Enter the data into the table
After saving the table, you can start entering data into it. Expand the Tables directory within the Object Explorer window. If your new table is not in the list. right-click the Tables directory, then click Refresh. Right-click on the table, then select "Edit Top 200 Rows".
- The middle window will display fields that you can use to start entering data. Your ID field will be filled in automatically, so you can ignore it now. Fill in the information for the remaining fields available. When you click on the next row, you'll see the ID field in the first row fills in automatically.
- Continue this process until you have entered all the required information.
Step 11. Use the Execute command on the table to save the data
Click the Execute SQL button on the toolbar when you are done entering the information to save it into the table. The SQL server will run behind the scenes to parse the data into the columns you have created. The Execute SQL button looks like a red exclamation mark. You can also press Ctrl+R to do this.
If there are errors in the entered data, the records containing the incorrect data will be shown before the table is executed
Step 12. Call your data with query
At this stage, your database has been created. You can create as many tables as you need in each database (every database has limitations, but most users don't need to worry about them unless the user is working on an enterprise-level database). You can now call up data to generate reports or for other administrative purposes. Look for the WikiHow article for detailed information on calling data with queries.
