SQL Server databases are among the most common databases, thanks in large part to how easy they are to create and maintain. With a free 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 in a few minutes.
Steps
Step 1. Install the SQL Server Management Studio software
This software can be downloaded free of charge from the Microsoft website. It allows you to connect to and manage your SQL Server from a graphical interface instead of using the command line.
- To connect to a remote SQL Server instance, you need this or similar software.
- Mac users can use open source software such as DbVisualizer or SQuirreL SQL. The interfaces will be different, but the general principles are the same.
- To learn how to create databases using command line tools, see this guide.
Step 2. Start SQL Server Management Studio
When you start the program for the first time, you will be asked to choose which server to connect to. If you already have a server and you are working, have the necessary permissions to connect to it, you can enter the server address and identification information. If you want to create a local database, set the Database Name as. and the authentication type as "Windows Authentication".
Click the Connect button to continue
Step 3. Determine the location for 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 is the server you are connected to. If the tree is not expanded, click on the "+" sign next to it. Determine the location of the Databases folder.
Step 4. Create a new database
Right click on the Databases folder and select "New Database …". A window will appear that allows you to configure the database before creating it. Give the database a name that will help you identify it. Most users can leave the rest of the settings at their default values.
- You will notice that as you enter the database name, two additional files will be created automatically: Data and Log. The Data file holds all the data in your database, while the Log file keeps track of changes in the database.
- Click OK to create the database. You will see your new database appearing in the expanded Databases folder. It will have a cylinder icon.
Step 5. Create a table
A database can only store data if you create a structure for that data. The table contains the information that you enter into your database and you will need to create it before you can proceed. Expand the new database in the Databases folder, and right-click on the Tables folder and select "New Table…".
Windows will open to the rest of the screen, allowing you to manage your new table
Step 6. Create a Primary Key
It is highly recommended that you create the primary key as the first column in your table. It acts as an identification number, or record number, allowing you to easily display those records later. To create it, enter "ID" in the Name field column, int in the Data Type field and uncheck the "Allow Nulls" checkbox. Click on the Key i icon in the toolbar to set this column as the Primary Key.
- You don't want to allow null values, since you always want to have a record of at least "1". If you allow 0, your first entry will be "0".
- In the Column Properties window, scroll down until you find the Identity Specification option. Expand it and set "(ls Identity)" to "Yes". This option will automatically increment the value of the ID column for each record, automatically numbering each new record.
Step 7. Understand how tables are arranged
Tables are made up of fields or columns. Each column represents one aspect of a database record. For example, if you are creating an employee database, you might have a FirstName column, a LastName column, an Address column, and a PhoneNumber column.
Step 8. Create the rest of the columns
When you finish filling in the fields for the Primary Key, you will notice that new fields appear below it. This will take you into your next column. Fill in the fields as you see fit and make sure to select the correct data type for the information to be entered in this column:
- nchar (#) - This data type should be used for text like names, addresses, etc. The number in brackets is the maximum number of characters allowed for this field. Setting a limit ensures that your database size remains manageable. Phone numbers must be stored in this format, since you are not doing math with them.
- int are integers and are commonly used in an identifier field.
- decimal (x, y) - will store numbers in decimal form, and the numbers in brackets indicate the total number of digits and the number of digits after the decimal, respectively. For example decimal (6, 2) will store numbers as 0000.00.
Step 9. Save your spreadsheet
When you are done creating your columns, you need to save the table before entering information. Click the Save icon on the toolbar, and then enter a name for the table. It is recommended that you name the table in a way that helps you recognize the content, especially for large databases with multiple tables.
Step 10. Add data to your table
Once you've saved the table, you can start adding data to it. Open the Tables folder in the Object Explorer window. If your 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 center window will display fields for you to start entering data. Your ID field will be filled in automatically, so you can ignore it right now. Fill in the rest of the fields with information. When you click on the next line, you will see the ID field in the first row, filled in automatically.
- Continue this process until you have entered all the information you need.
Step 11. Run the spreadsheet to save the data
When you are finished entering the information, click the Execute SQL button on the toolbar to save it to a table. The SQL Server will run in the background, parsing all the data into the columns that you have created. The button looks like a red exclamation mark. You can also press Ctrl R to execute.
If there are any errors, you will be shown which records are being filled incorrectly before the table can be started
Step 12. Query your data
At this point, your database has been created. You can create as many tables as you need in each database (there is a limit, but most users won't need to worry if they are running on enterprise-grade databases). Now you can request your data (create a Request) for reports or any other administrative purposes. Review this guide for details on current requests.