SQL Server databases are the most commonly used databases, which is in part due to the ease with which they can be created and managed. With a free graphical user interface (GUI) program like SQL Server Management, you don't have to worry about poking around in command lines.
Step 1. Install SQL Server Management Studio software
This software is made available free of charge by Microsoft, it allows you to connect and manage your SQL server from a graphical interface instead of having to use the command lines.
- To connect to a remote SQL server, you will need this software or similar software.
- Mac users can use open source programs such as DbVisualizer or SQuirreL SQL. The interfaces will be different, but the same general principles apply.
- To learn how to create databases using command lines, read this guide.
Step 2. Start SQL Server Management Studio
When you start the program for the first time, you are asked which server you want to connect to. If you already have a running server and 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 with. and the type of authentication on "Windows Authentication".
Click Connect to continue
Step 3. Locate the databases folder
After connecting to the server, whether local or remote, the Object Explorer window will open on the left side of the screen. The server you're connected to is at the top of the explorer tree. If it is not expanded, click on the icon + next to. Locate the “Databases” folder.
Step 4. Create a new database
Right click on the folder Data base and select New database … A window will appear, allowing you to configure the database before creating it. Give it a name that will help you identify it. Most users can keep the rest of the default settings.
- You will notice that when you enter the name of the database, two new files are automatically created: the Data file and the Log file. The Data file houses all the data in your database, while the Log file tracks changes in the database.
- Click OK to create the database. You will see a new database appear in the expanded Databases folder. It has a cylinder-shaped icon.
Step 5. Create a table
A database can only store data if you create a structure for that data. The table contains all the information entered into the database, you will need to create it before you can continue. Expand the new database in the folder Data base and right click on the folder Table before selecting New table …
Windows will open additional windows that will allow you to manipulate your new table
Step 6. Create the primary key
It is strongly recommended that you create the primary key in the first column of your table. It acts as an identification number or registration number, allowing you to easily recall these entries when you need them. To create it, enter ID in the field Column name, type int in the field Data type and uncheck Allow zero numbers. Click on the key icon in the toolbar to set this column as the primary key.
- You should not allow null values, because the inputs must always be at least "1". If you allow zero numbers, your first entry will be "0".
- In the window Column properties, scroll down until you find the option Identity specification. Develop it and tune it (his identity) on Yes. The values in the ID column will automatically increase for each entry and each new entry will be automatically numbered.
Step 7. Understand how tables are structured
Tables are made up of fields or columns. Each column represents one aspect of an entry in the database. For example, if you created an employee database, you should have a Last Name column, a First Name column, an Address column, and a Phone Number column.
Step 8. Create the rest of your columns
When you have finished filling out the fields for the primary key, you will notice that new fields appear below. These allow you to enter your new column. Fill in the fields you see and make sure you choose the correct data type for the information that will be entered in this column.
- nchar (#) - This is the data type you should use for names, addresses, etc. The number in parentheses is the maximum number of characters allocated to this field. Setting a limit ensures that the size of your database remains manageable. Phone numbers should be stored in this format, as you will not perform mathematical operations on these numbers.
- int - This is the data type for integers, typically used for the ID field.
- decimal (x, y) - This data type allows numbers to be stored in decimal form. The numbers in parentheses denote the total number of digits and the number of digits following the decimal point, respectively. For example, decimal (6, 2) stores numbers as 0000, 00.
Step 9. Save your table
When you have finished creating your columns, you will need to save the table before entering the information. Click on the icon Save in the toolbar, then enter a name for the table. Name your table so that you can easily identify it later, especially if you have a large database with multiple tables.
Step 10. Add data to your table
Once you've saved your table, you can start adding data. Expand the folder Tables in the window Object Explorer. If your new table is not listed, right click on the folder Tables and select Refresh. Right click on the table and select Edit the first 200 lines.
- 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 for now. Fill in the rest of the fields with your information. When you click on the next row, you can see the ID field of the first row auto-populate.
- Continue this process until you have entered all the information you need.
Step 11. Run the table to save the data
Click on the button Execute SQL in the toolbar when you have finished entering the information, in order to save it in the table. The SQL server will launch in the background, distributing all the data in the columns you created. The button looks like a red exclamation mark. You can also press Ctrl + R to run it.
If there are any errors, you will see that entries are not correctly populated before the table is executed
Step 12. Make queries on your data
Your database has just been created. You can create as many tables as you want within each database (there is a limit, but most users don't need to worry about this unless they are dealing with large databases. company type). You can now query your data for reporting or other administrative purposes.