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 a 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. And 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 go ahead and just search for it here. When it finds it you can click on this icon here to launch 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 tool 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 when we specified the server that we want to connect to. If you want to use a specific login name, you can use dash U with the login ID. And then dash P with the password. As you can see, there's lots of different flags that 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 that I want to connect to. That'll be the computer name, and for me that's Windows10-PC, followed by a back slash and 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 wanted 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 has 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. I'll call mine Kinetico. 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 that we could specify here in our create database command, such as the size of the database and where to store the files. But this is 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'll 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 what's returned is a listing of all those databases, including the master, model, msdd, and temp system databases, as well as our new Kinectico 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'll 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 I'll put 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 SQLSERVER2019. 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 Kinectico database. These include files that include in a .mdf extension and one that ends in a .ldf extension. The .mdf is a file that stores your actual data. The .ldf file is 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 that'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