(upbeat music)
- 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 in 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.
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 servers 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 that 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 will work
in exactly the same way.
We'll start here with our command prompt
and I'll type in the word SQL CMD.
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 specify the server that we want to connect to.
If you want to use a specific login name.
You can use -U with the login ID
and then -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 SQL CMD again.
This time followed by a space and a -S
and the server that I want to connect to.
That'll be the computer name.
And for me that's windows 10-PC.
Followed by a backslash and then these server instance name
that we want to connect to.
And again for me, that's SQL server 2019.
Now if I just press enter right now
it's going to connect to 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 -U for the username.
And then the username is SA.
Then I space and a -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.
And I'll call mine KinetEco.
This is an example of a transact SQL command.
Which is the language that you 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.
Now there's lots of additional options that we can 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 could 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.
Off our center, 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 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'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 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 at end 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 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 timestamps note
about what happened in the database and when.
And there's our first database.
It will be managed by the SQL server database engine.
(upbeat music)