{"id":2208,"date":"2014-01-26T22:38:43","date_gmt":"2014-01-26T17:08:43","guid":{"rendered":"http:\/\/sqlhints.com\/?p=2208"},"modified":"2014-02-03T00:22:59","modified_gmt":"2014-02-02T18:52:59","slug":"working-with-databases-in-sql-server","status":"publish","type":"post","link":"https:\/\/sqlhints.com\/2014\/01\/26\/working-with-databases-in-sql-server\/","title":{"rendered":"Working with Databases in Sql Server"},"content":{"rendered":"

Sql Server Tutorial<\/a> Lesson 2: Working with Databases<\/h2>\n

DATABASE<\/h2>\n

A Database in Sql Server consists of mainly database objects like Tables, Stored Procedures, User Defined Functions, Views and so on. Let us first understand how to Create Database, once we create the Database we can add other database objects to it which we learn over the course of this Tutorial.<\/p>\n

CREATE DATABASE<\/h3>\n

Below is the Basic Syntax for Creating a Database in Sql Server<\/p>\n

CREATE DATABASE DataBaseName<\/pre>\n
Demo 1:<\/strong> Let us Create a DataBase with Name SqlHintsTutorial<\/p>\n
CREATE DATABASE SqlHintsTutorial<\/pre>\n
USE DATABASE<\/h3>\n
A database instance will usually be having multiple System Defined databases and User created databases. We can use the USE statement to select the Database on which we want to perform the database operations. Basically USE statement changes the database context to the Specified Database. Below is the basic syntax of the USE statement.<\/p>\n
USE DatabaseName<\/pre>\n
Demo 2:<\/strong>Let us make the SqlHintsTutorial database as the current contextual database.<\/p>\n
USE SqlHintsTutorial<\/pre>\n
DROP DATABASE<\/h3>\n
We can drop a database using DROP Statement. Dropping a database will permanently removes all the information stored in it, so be careful before using it. Below is the basic syntax for Dropping the Database.<\/p>\n
DROP DATABASE DatabaseName<\/pre>\n
We can’t drop the database which is the current contextual database. To do it we have to change the current contextual database to some-other database and then issue the DROP statement as shown below.<\/p>\n
DEMO 3:<\/strong> Let us try to drop a database which is the current Contextual database<\/p>\n
USE SqlHintsTutorial\r\nGO\r\nDROP DATABASE SqlHintsTutorial<\/pre>\n
RESULT:
\nMsg 3702, Level 16, State 3, Line 1
\nCannot drop database “SqlHintsTutorial” because it is currently in use.<\/span><\/p>\n
DEMO 4: Below script demonstrate how to delete the current contextual database by changing the Current Context DB to some other database and then drop it<\/p>\n
 -- Change Current db Context to System Database TempDB\r\nUSE TempDB\r\nGO\r\nDROP DATABASE SqlHintsTutorial<\/pre>\n
System Databases<\/h3>\n
Below are the list of System Databases which shipped with Sql Server by default. These databases are required for the Sql Server to function smoothly.<\/p>\n\n\n\n\n\n\n\n
System Database<\/strong><\/td>\nDescription<\/strong><\/td>\n<\/tr>\n
MASTER<\/strong><\/td>\nRecords all system-level information for an instance of SQL Server.<\/td>\n<\/tr>\n
MSDB<\/strong><\/td>\nUsed by SQL Server Agent for scheduling alerts and jobs.<\/td>\n<\/tr>\n
MODEL<\/strong><\/td>\nUsed as the template for all databases created on the instance of SQL Server. Modifications made to the model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward.<\/td>\n<\/tr>\n
TEMPDB<\/strong><\/td>\nIt is used for holding temporary objects or intermediate result-sets.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":"
Sql Server Tutorial Lesson 2: Working with Databases DATABASE A Database in Sql Server consists of mainly database objects like Tables, Stored Procedures, User Defined Functions, Views and so on. Let us first understand how to Create Database, once we create the Database we can add other database objects to it which we learn over … Continue reading Working with Databases in Sql Server<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[3],"tags":[254,256,257,255,258],"class_list":["post-2208","post","type-post","status-publish","format-standard","hentry","category-sql-server","tag-database","tag-sql-create-database","tag-sql-drop-database","tag-sql-system-databases","tag-sql-use-statement"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p3xNAz-zC","_links":{"self":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/2208","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/comments?post=2208"}],"version-history":[{"count":11,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/2208\/revisions"}],"predecessor-version":[{"id":2323,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/2208\/revisions\/2323"}],"wp:attachment":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/media?parent=2208"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/categories?post=2208"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/tags?post=2208"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}