Create the first database - SQL Server Tutorial

- [Instructor] SQL Server is not a database. It's a database management system. That means that it's the application that you use to create and manage your databases. SQL Server is just a tool that you can create databases with, and it does the work of controlling user access to those databases. It does the work of adding data to a database and retrieving information when it's requested. It also automates backups and generally ensures that your data remains in a healthy, secure state. So SQL Server, the database engine, is the application that does all of that work. Your databases, the container that includes all of your actual data, are nothing more than a couple of files that SQL Server manages which are stored on your file system. In this chapter, we're going to start creating a couple of databases that will be managed by SQL Server. Now, there are many different ways that you could do this. We could use Management Studio, in the graphical interface here on the screen. But I'd like to take a step back a moment and return to the command-line tool, SQL Command. We saw this tool very briefly when I first introduced SQL Server and installed it on my machine. I want to show this tool again to simply reinforce the idea that Management Studio is nothing more than an interface to SQL Server's capabilities, and it's one option of many. You could also use a program called Azure Data Studio to perform many of these same tasks. SQL Server largely doesn't care how it receives your instructions, just that they're formatted in a way that it understands. And these separate front-ends, these graphical and text-based interfaces, can all perform the same sorts of tasks on the server. So let's leave the Management Studio graphical interface for just a moment, and I'll come down to my Start menu, and I'll do a search for cmd. Then I'll bring up the Windows Command Prompt application. Then I'll type in sqlcmd to get to the SQL Command tool. I'll follow that with a space hyphen question mark and press Enter. This will reveal all of the different parameters that we can pass to the SQL Command tool. All of these parameters allow you to connect to specific servers or to use different login accounts. Now, since I'm going to be connecting to the default SQL Server instance on this machine and I'm going to use my current Windows user account to authenticate my identity, I actually don't need to use any other parameters to make a connection to my database engine. Let me type cls here at the bottom and press Enter just to clear the screen, and bring my command prompt up here to the top again. Then I'll type sqlcmd and press Enter. That connects me to my default SQL Server instance. And you'll notice that the prompt has changed to indicate that I'm now on line number one of a new command to SQL Server. Now, we have direct access to start telling SQL Server what we want it to do. Let's issue a command. I'll type in CREATE DATABASE KinetEco. This is an example of a Transact-SQL command which is the language that you'll use to communicate with SQL Server, regardless of whether you're using the SQL Command tool like we are now, or through Management Studio, or any other interface. We'll talk a lot about Transact-SQL later on in the course. There's lots of additional options that we could specify here in our CREATE DATABASE command, such as the size of the database that we want to make and where to store those files. But this is enough to build a generic, empty database that we can start filling up with tables and data. So I'll press the Enter button. That takes me down to line number two. I'll issue the command GO and press Enter again. The GO command tells SQL Server to execute my commands, and it creates the empty database files. Notice that the prompt changes back to a line number one again, indicating that the server is ready to accept another instruction. This time, I'll type sp_databases. I'll press Enter. On my number two, I'll type GO again and press Enter again. This will run a stored procedure that returns a list of all of the databases on the server. Here's the four default databases: master, model, msdb, and tempdb. And at the very top is our custom user database for KinetEco that we just created. This second column shows its size. And you can see that that database, even though that it's empty, is taking up about 16 megabytes on my hard drive. The Command Prompt is back to line number one down here at the very bottom of my screen again. And this time, I'll issue the command exit and press Enter, and that quits the SQL Command tool and returns me back to my system's command prompt. Let's now take a quick look and see exactly what happened on the file system when we created our database. I'll close the command tool and return back here to SQL Server Management Studio. Let me just minimize that, and I'll open up my File Explorer. Then I'll find my C drive. We'll go into the Program Files folder, then Microsoft SQL Server. Inside of here, we'll find a folder for our database instance. The folder name is going to be similar to this one right here. It starts with the version of SQL Server that we're working with, and SQL Server 2022 is version 16 of Microsoft SQL Server. Then we have a period, followed by the name of the instance. My instance is named MSSQLSERVER. So this is the folder that I want to go in. Remember that you could have multiple instances installed on a same machine, so you might have several different folders with similar names. I'm going to go into the one that represents the server that I'm working with. Then inside of that, I'll go into the MSSQL folder, and finally, I'll find the DATA folder and go into there. Windows tells me that I don't currently have permission to access this folder. But if you press the Continue button, you should be allowed in. If it doesn't let you in, you might have to right-click on that folder and go into Properties and adjust the security settings for the DATA folder. But once you get in, you'll see all of the data files that make up the various databases on your system. You can see that there are actually multiple files for each database. Here are the two files that we just created for the KinetEco database. There's one for the mdf file and one for file that ends in an ldf extension. The mdf file is the file that stores your actual data and other objects that make up the database. The ldf file is a log file that stores transaction information. So every time a table gets created in the database or a record gets added to a table or a piece of information gets edited, the transaction log will store a time-stamped note about what happened in the database and when. So those are the two files that make up our first user database. If I go back into Management Studio, we could find it there as well. I'll go up here to the Databases folder for the server and expand it. Then I'll right-click on the Databases folder and choose Refresh. Then I'll search through the system again and update the contents that we see here in the Object Explorer. And sure enough, there is the empty KinetEco database waiting for us to fill it up with data. So there's our first database that will be managed by SQL Server. Anything that you can do using the graphical interface, you can also do using the text-based interface as long as you know the correct commands to type.