{"id":358,"date":"2020-10-03T21:52:05","date_gmt":"2020-10-03T21:52:05","guid":{"rendered":"https:\/\/kindsonthegenius.com\/mssql\/?p=358"},"modified":"2020-10-03T21:56:58","modified_gmt":"2020-10-03T21:56:58","slug":"ms-sql-server-database-concepts","status":"publish","type":"post","link":"https:\/\/www.kindsonthegenius.com\/mssql\/ms-sql-server-database-concepts\/","title":{"rendered":"MS SQL Server Database Concepts"},"content":{"rendered":"

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.<\/p>\n

 <\/p>\n

Server Instance<\/strong><\/p>\n

A server instance is an installation of an SQL Server. You can have multiple instances on the same machine<\/p>\n

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<\/em>“. Then proceed<\/p>\n

\"Adding<\/a>
Adding a new instance to SQL server installation<\/figcaption><\/figure>\n

An instance has its own separate data files, log files and security credentials.<\/p>\n

 <\/p>\n

Database\/Database Server<\/strong><\/p>\n

A collection of information organized in such a way that it can easily be managed, accessed and updated.<\/p>\n

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<\/p>\n

 <\/p>\n

Schema<\/strong><\/p>\n

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.<\/p>\n

To create a new schema.<\/p>\n

Expand the database node and right-click on Security > New > Schema<\/p>\n

\"Creating<\/a>
Creating a new schema in MS SQL<\/figcaption><\/figure>\n

To reassign an object from on schema to another, use the alter schema statement as shown below:<\/p>\n

alter<\/span> schema<\/span> [new<\/span>-<\/span>schema<\/span>] transfer [old<\/span>-<\/span>schema<\/span>].[object<\/span>]\r\n<\/pre>\n
 <\/p>\n
Table<\/strong><\/p>\n
A collection of rows and column\/<\/p>\n
To create a new Table, expand the database node, right-click on Table > New > Table<\/p>\n
 <\/p>\n
View<\/strong><\/p>\n
A view is a virtual table which is the result of a stored query.<\/p>\n
To create a view, expand the database and choose View > New > View<\/p>\n
 <\/p>\n
Stored Procedure<\/strong><\/p>\n
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.<\/p>\n
 <\/p>\n
Login<\/strong><\/p>\n
A login is a database object used for authentication into an SQL instance. A login can be associated with multiple users – one per database.<\/p>\n
To create a login, right-click on Security > New > Login.<\/p>\n
This would display the New Login dialog box as shown below:<\/p>\n
\"Creating<\/a>
Creating a New Login in MS SQL Server<\/figcaption><\/figure>\n
For Windows authentication, then enter or select Login Name or choose it using the Search button<\/p>\n
For SQL Server authentication, follow the steps below<\/p>\n
    \n
  • go the server properties > Security. Then select SQL Server and Windows Authentication mode under Server Authentication<\/li>\n
  • enter the Login name<\/li>\n
  • enter the password and confirm it<\/li>\n
  • click on the Server Role node and select sysadmin<\/em> under Server roles<\/li>\n
  • go to User Mapping node. Select the database (demodb) and\u00a0 assign it the db_owner role<\/li>\n
  • click on securable and choose the database under the Add Objects pop up<\/li>\n
  • Click OK.<\/li>\n
  • Now you can disconnect and connect using the login you created.<\/li>\n<\/ul>\n
     <\/p>\n
    Roles<\/strong><\/p>\n
    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.<\/p>\n
     <\/p>\n
    Privileges<\/p>\n
     <\/p>\n
     <\/p>\n","protected":false},"excerpt":{"rendered":"
    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 … <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[23],"tags":[],"class_list":["post-358","post","type-post","status-publish","format-standard","hentry","category-ms-sql-server"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.kindsonthegenius.com\/mssql\/wp-json\/wp\/v2\/posts\/358","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.kindsonthegenius.com\/mssql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.kindsonthegenius.com\/mssql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.kindsonthegenius.com\/mssql\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.kindsonthegenius.com\/mssql\/wp-json\/wp\/v2\/comments?post=358"}],"version-history":[{"count":2,"href":"https:\/\/www.kindsonthegenius.com\/mssql\/wp-json\/wp\/v2\/posts\/358\/revisions"}],"predecessor-version":[{"id":363,"href":"https:\/\/www.kindsonthegenius.com\/mssql\/wp-json\/wp\/v2\/posts\/358\/revisions\/363"}],"wp:attachment":[{"href":"https:\/\/www.kindsonthegenius.com\/mssql\/wp-json\/wp\/v2\/media?parent=358"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.kindsonthegenius.com\/mssql\/wp-json\/wp\/v2\/categories?post=358"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.kindsonthegenius.com\/mssql\/wp-json\/wp\/v2\/tags?post=358"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}