SQL Server is not a database, it’s a database engine. That means that it is the application that you use to create and manage databases. In this video, learn how to use the sqlcmd command line tool to connect to the database engine and create a new, empty database on the server.
[Instructor] Strictly speaking, SQL server is not a database. It's a database engine. And that means that it's an application, which you'll use to create and manage databases. SQL server does the work of controlling user access to a database, it does the work of adding data to database, and retrieving information when it's requested. It 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 the work. Your databases, the container that contains all of your actual data, are nothing more than a couple of files stored on your file system.
In this chapter we're going to start creating a simple database that will be managed by SQL server. Now there are many different ways to do this. We could use Management Studio, the graphical user interface, but I'd like to step back a moment and return to the command line tool called SQL command. I'm doing this to simply reinforce the idea that Management Studio is nothing more than an interface to SQL server's capabilities, and it's actually one of many. You could also use a program called Azure Data Studio to largely do the same things. SQL server largely doesn't care how it receives your instructions, just that they're formatted in a way that it understands.
In these separate front-ends, these graphical and text-based interfaces, can all perform these same sorts of tasks on the server. So to access the SQL command tool, we're going to use the command prompt, I'll just go ahead and search for it here. When it finds it you can click on this icon here, to enlarge the command prompt. You can also use an alternative program called Windows PowerShell. Let me show you what that one looks like, too. Once again, I'll come down to the search box and I'll simply search for PowerShell. And I'll click on the match here, to launch PowerShell. Both PowerShell and the command prompt will work in exactly the same way.
We'll start here with our command prompt, and I'll type in the word sqlcmd. And this time I'm going to follow it with a space, dash and a question mark. When I press enter, I'll see all the usage information for the SQL command tool. Previously we saw the dash S flag. We specified the server we want to connect to. If you want to use a specific log-in name, you can use dash U, with the log-in ID, and then dash P with the password. As you can see, there's lots of different flags we can incorporate into our connection instruction. I'm going to come down here to the prompt, which is now at the bottom of the line here, and we'll type in sqlcmd again.
This time followed by a space, and a dash S. And the server I want to connect to. That'll be the computer name, and for me that's Windows10-PC. Followed by a backslash, and then the server instance name that we want to connect to. And again for me, that's SQLSERVER2019. Now if I just press enter right now, it's going to connect me using my Windows account. If I want it to connect to a different account, say, the SA account, I can type in a space, a dash U, for the user name, and then the user name is SA. Then a space and a dash P, and the super-secret strong password.
When I press enter SQL command will move me into my SQL server instance, and I can start issuing commands directly to the server. You can see my command prompt is changed now to a line number instead. So let's issue a command called CREATE DATABASE. After that, I'll give my database a name, and I'll call mine 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 SQL command, like we are now, or through Management Studio, or any other interface. We'll talk a lot about the transact SQL language later on in the course.
Now there's lots of additional options we can specify here, in our CREATE DATABASE command, such as the size of the database, and where to stored the files, but this actually enough to build a generic empty database, that we can start filling up with our tables of data. I'll press enter on my keyboard. That'll move me down to line number two, and I'll issue the command GO. That'll execute all the previous lines. SQL server executes the CREATE DATABASE statement, and moves me back to line number one, where I can type in a new statement. This time I want to execute a stored procedure, called SP_databases.
This is an internal routine that SQL server uses that will return the name of all the different databases that currently exist on the server. I'll press enter, that moves me down to line number two, and I'll issue the command GO again. Now we can see that what's returned is a listing of all those databases, including the master, model, MSDB, and temp system databases, as well as our new KinetEco database here at the top. When we're done working with the SQL command tool, I can simply issue the command exit. And press enter, and that will return me back to my Windows command prompt. So now let's take a look at what exactly happened on the file system when we created our database.
I'm going to close PowerShell here, and open up a new file explorer window. And then I'm going to browse out into my C: drive. We'll go into the program files folder. Then inside of there we'll go into Microsoft SQL Server. Then we'll find a folder that corresponds to our server instance. And for me that's going to be the name or the version number of SQL server that we're using, followed by SQL server 2019. Inside of there you'll find a folder called MSSQL, and then finally we'll find the folder called DATA. Windows is going to tell me that I currently don't have permission to access this folder.
But you can simply press the continue button, and you'll be allowed in. Now here are all the actual files that make up all of our databases. You can see that there's actually multiple files for each database. Here we have the ones for master, model, MSDB, and tempdb. And then here at the top we have our new KinetEco database. These include files that end in a dot mdf extension, and one that ends in a dot ldf extension. The .mdf is a file that stores your actual data. The .ldf 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. And there's our first database. It'll be managed by the SQL server database engine.
- Installing Management Studio
- Enabling features with SQL Server Configuration Monitor
- Creating a database
- Creating tables with Transact-SQL (T-SQL)
- Creating relationships between tables
- Creating views
- Creating user-defined scalar functions
- Backing up and restoring SQL Server
- Monitoring and management