by In this tutorial, we would examine all the MS SQL Server database concepts. We would take a look at all the objects that make up MS SQL server, their definitions and how to create them.
Server Instance
A server instance is an installation of an SQL Server. You can have multiple instances on the same machine
To add a new instance to SQL server, run the installer and choose “New SQL Server stand-alone installation or add features to existing installation“. Then proceed
An instance has its own separate data files, log files and security credentials.
Database/Database Server
A collection of information organized in such a way that it can easily be managed, accessed and updated.
To create a database, right-click on the database node and select New > Database. Give it a name and OK. In this demo, I called it demodb
Schema
A schema is a collection of database objects such as tables, views, stored procedures, indexes etc. A schema is associated with a user with a username. A schema must always belong to one database. MS SQL has some built in schemas with the same name as the built-in user and roles. For instance: sys, dbo, and INFORMATION_SCHEMA.
To create a new schema.
Expand the database node and right-click on Security > New > Schema
To reassign an object from on schema to another, use the alter schema statement as shown below:
alter schema [new-schema] transfer [old-schema].[object]
Table
A collection of rows and column/
To create a new Table, expand the database node, right-click on Table > New > Table
View
A view is a virtual table which is the result of a stored query.
To create a view, expand the database and choose View > New > View
Stored Procedure
A stored procedure is a set of SQL statements stored and executed as group under a given new. Executing a view would return the result of the query execution. A stored procedure can receive input as well as produce outputs.
Login
A login is a database object used for authentication into an SQL instance. A login can be associated with multiple users – one per database.
To create a login, right-click on Security > New > Login.
This would display the New Login dialog box as shown below:
For Windows authentication, then enter or select Login Name or choose it using the Search button
For SQL Server authentication, follow the steps below
- go the server properties > Security. Then select SQL Server and Windows Authentication mode under Server Authentication
- enter the Login name
- enter the password and confirm it
- click on the Server Role node and select sysadmin under Server roles
- go to User Mapping node. Select the database (demodb) and assign it the db_owner role
- click on securable and choose the database under the Add Objects pop up
- Click OK.
- Now you can disconnect and connect using the login you created.
Roles
A role is a security principal that is used to manage database or database objects. Some fixed-server roles are provided by MS SQL Server.
Privileges
