Home

Introduction to SQL

 

The Microsoft SQL Server Management Studio

 

Introduction

There are many tools you will use to create and manage your databases. The most fundamental is Microsoft SQL server, which is equipped with Microsoft SQL Server Management Studio. To access it, you can open a connection from Start -> (All) Programs -> Microsoft SQL Server 2005 -> SQL Server Management Studio. A dialog box would come up but you can click Cancel on it:

Microsoft SQL Server Management Studio

The top section of the Microsoft SQL Server Management Studio displays the classic title bar of a regular window, with an icon on the left, followed by the title of the application, and the system buttons on the right side. Under the title bar, the menu bar displays categories of menus that you will use to perform the various necessary operations. A toolbar displays under the main menu.

The left side of the interface displays the Object Explorer window, with its title bar labeled Object Explorer. On the right side of the Object Explorer title, there are three buttons. If you click the first button that points down, a menu would appear, which allows you to specify whether you want the window to be floated, docked, or tabbed.

The Object Explorer is a dockable window, meaning you can move it from the left side to another side on the screen. To do this, you can click and drag its title bar to a location of your choice.

The Object Explorer is also floatable, which means you can place it somewhere in the middle of the interface:

IDE

To place the window back to its previous position, you can double-click its title bar.

The Object Explorer can also be tabbed. This means that the window can be positioned either vertically or horizontally.

The right side of the window is equipped by a window whose tab is labeled Summary. This area will be used to display either the contents of what is selected in the Object Explorer, or to show a result of some operation. As you will see later on, many other windows will occupy the right section but they will share the same area. To make each known, it will be represented with a tab and the tab shows the name (or caption) of a window.

Using the Management Studio

After installing Microsoft SQL Server, you can use it to create and manage databases. To assist you with this, you can use Microsoft SQL Server Management Studio.

Microsoft SQL Server Management Studio is a window you will use to create and manage databases. To assist you with this, it comes equipped with various tools, some are graphically-based and some others require code from you. To perform some operations, you will use the main menu. Some other operations will be available from clicking one of the buttons on the toolbar. Many other operations will start from the Object Explorer.

The Object Explorer displays a list of items as a tree-style of window. One of the most regularly used items will be the name of the server you are using. If you are just starting to learn database development or you are a junior database developer, you may use or see only one server. In some cases, you may be dealing with many. Regardless, you should always know what server you are currently connecting to. This is easy to check  with the first node of the Object Explorer. In the following example, the server is named Central:

The name of the server is followed by parentheses.

In previous lessons, we saw that, to establish a connection to a server, you must authenticate yourself. In some cases you may use the same account over and over again. In some other cases you may have different accounts that you use for different scenarios, such as one account for database development, one account for database management, and/or one account for database testing. Some operations cannot be performed by some accounts. When performing some operations, you should always know what account you are using. You can check this in the parentheses of the server name. In the following connection, an account called Administrator is currently connecting to a server named Central:

When the server name is selected in the Object Explorer, the whole caption of its node displays in the Summary window.

For our lessons, probably the most regular node you will be interested in, is labeled Databases. This node holds the names of databases on the server you are connecting to. Also, from that node, you can perform almost any necessary operation of a database. To see most of the regularly available actions, you can expand the Databases node and some of its children. You can then right-click either Databases or one of its child nodes.

Introduction to Code

Although you will perform many of your database operations visually, some other operations will require that you write code. To assist with with this, Microsoft SQL Server provides a code editor and various code templates.

To open the editor:

  • You can press Ctrl + N
  • On the main menu, you can click File -> New -> Query With Current Connection
  • On the Standard toolbar, you can click the New Query button
  • In the Object Explorer, you can right-click the name of the server and click New Query

This would open a new window and position it on the right side of the interface. Whether you have already written code or not, you can save the document of the code editor at any time. To save it:

  • You can press Ctrl + S
  • On the main menu, you can click File -> Save SQLQueryX.sql...
  • On the Standard toolbar, you can click the Save button

You will be required to provide a name for the file. After saving the file, its name would appear on the tab of the document.

Introduction to the Command Prompt

Besides the SQL Server Management Studio, you can also work on Microsoft SQL Server from the DOS command prompt. This is done using an application or command named SQLCMD.EXE. To use it, open the Command Prompt, type SQLCMD (case-insensitive) and press Enter:

Command Prompt

You can then write SQL code.

The Structured Query Language

 

Introduction

After establishing a connection, you can take actions, such as creating a database and/or manipulating data. The Structured Query Language, known as SQL, is a universal language used on various computer systems to create and manage databases.

Author Note SQL can be pronounced Sequel or S. Q. L. In our lessons, we will consider the Sequel pronunciation. For this reason, the abbreviation will always be considered as a word, which would result in “A SQL statement” instead of "An SQL statement". Also, we will regularly write, “The SQL” instead of “The SQL language, as the L already represents Language.

Like other non-platform specific languages such as C/C++, Pascal, or Java, the SQL you learn can be applied to various database systems. To adapt the SQL to Microsoft SQL Server, the company developed Transact-SQL as Microsoft's implementation of SQL. Transact-SQL is the language used internally by Microsoft SQL Server and MSDE. Although SQL Server highly adheres to the SQL standards, it has some internal details that may not be applied to other database systems like MySQL, Oracle, or even Microsoft Access, etc; although they too fairly conform to the standard.

The SQL we will learn and use here is Transact-SQL. In other words, we will assume that you are using Microsoft SQL Server as your platform for learning about databases. This means that, unless specified otherwise, most of the time, on this site, the word SQL refers to Transact-SQL or the way the language is implemented in Microsoft SQL Server.

The SQL Interpreter

As a computer language, the SQL is used to give instructions to an internal program called an interpreter. As we will learn in various sections, you must make sure you give precise instructions. SQL is not case-sensitive. This means that CREATE, create, and Create mean the same thing. It is a tradition to write SQL's own words in uppercase. This helps to distinguish SQL instructions with the words you use for your database.

As we will learn in this and the other remaining lessons, you use SQL by writing statements. To help you with this, Microsoft SQL Server provides a window, also referred to as the Query Window, that you can use to write your SQL code. To access it, on the left side of the window, you can right-click the name of the server and click New Query. In the same way, you can open as many instances as the New Query as you want.

When the Query window comes up, it display a blank child window in which you can write your code:

Query Window

The code you write is a document and it can be saved as a file. The file would have the extension .sql. Every time you open a new query, it is represented with a tab. To switch from one code part to another, you can click its tab. To dismiss an instance of the query, first access it (by clicking its tab), then, on the right side, click the close button Close. If you had written code in the query window, when you close it, you would be asked to save your code. If you want to preserve your code, then save it. If you had already executed the code in the window (we will learn how to write and execute SQL code), you do not have to save the contents of the window.

If you are working in a Windows Application, you can write the exact same code you would in a query window. Once your code is ready, you can pass it to a SqlCommand object you would have created as we saw already. This would be done as follows:

Imports System.Data.SqlClient

Public Class Exercise

    Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
        Dim connection As SqlConnection = _
        New SqlConnection("Data Source=(local);Integrated Security=yes")
        Dim command As SqlCommand = New SqlCommand(SQL Code, connection)
    End Sub
End Class

In this example, the SQL Code factor represents a SQL statement you would write and pass it as a string.

Executing a Statement

In the next sections and lessons, we will learn various techniques of creating SQL statements with code. By default, when a new query window appears, after writing a statement, you can execute it, either to make it active or simply to test it. To execute a statement:

  • You can press F5
  • On the main menu, you can click Query -> Execute
  • On the SQL Editor toolbar, you can click the Execute button
  • You can right-click somewhere in the code editor and click Execute

When you execute code, the query window becomes divided into two horizontal sections:

IDE

Also, when you execute code, the interpreter would first analyze it. If there is an error, it would display one or more red lines of text in its bottom section. Here is an example:

Microsoft SQL Server Management Studio: An Error

If there is no error in the code, what happens when you execute a statement depends on the code and the type of statement.

If you are working in a Windows Forms Application, after passing the SQL code to a command, to execute it, as we saw in the previous lesson, you can call the ExecuteNonQuery() method of your SqlCommand object. This would be done as follows:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Dim connection As SqlConnection = _
    New SqlConnection("Data Source=(local);Integrated Security=yes")
    Dim command As SqlCommand = New SqlCommand(SQL Code, connection)

    connection.Open()
    command.ExecuteNonQuery()
    connection.Close()
End Sub

Accessories for SQL Code Writing

 

Comments

A comment is text that the SQL interpreter would not consider as code. As such, a comment is written any way you like. What ever it is made of would not be read. Transact-SQL supports two types of comments. The style of comment that starts with /* and ends with */ can be used. To apply it, start a line with /*, include any kind of text you like, on as many lines as you want. To close the commented section, type */. Here is an example of a line of comment:

/* First find out if the database we want to create exists already */

A comment can also be spread on more than one line, like a paragraph. Here is an example:

/* First find out if the MotorVehicleDivision database we 
   want to create exists already.
   If that database exists, we don't want it anymore. So,
   delete it from the system. */

Transact-SQL also supports the double-dash comment. This comment applies to only one line of text. To use it, start the line with --. Anything on the right side of -- is part of a comment and would not be considered as code. Here is an example:

-- =============================================
-- Database: MotorVehicleDivision
-- =============================================

/* First find out if the MotorVehicleDivision database we 
   want to create exists already.
   If that database exists, we don't want it anymore. So,
   delete it from the system. */


-- Now that the database is not in the system, create it

The End of a Statement

In SQL, after writing a statement, you can end it with a semi-colon. In fact, if you plan to use many statements in one block, you should end each with a semi-colon. When many statements are used, some of them must come after others.

Time to GO

To separate statements, that is, to indicate when a statement ends, you can use the GO keyword (in reality and based on SQL standards, it is the semi-colon that would be required, but the Microsoft SQL Server interpreter accepts GO as the end of a statement).

Database Creation

 

Introduction

Before using a database, you must first have one. A database is primarily a group of computer files that each has a name and a location. Just as there are different ways to connect to a server, there are also different ways to create a database. You can create a new database in Microsoft SQL Server Management Studio, in Microsoft Visual Studio, or on the Command Prompt.

To create a database in Microsoft SQL Server Management Studio, you can right-click the Databases node and click New Database...

New Database

If you are working from Microsoft Visual Studio, to create a new database, in the Server Explorer, you can right-click Data Connections and click Create New SQL Server Database...

To programmatically create a database, pass the necessary SQL code as the command text of the SqlCommand object:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Dim connection As SqlConnection = _
    	New SqlConnection("Data Source=(local);Integrated Security=yes")
    Dim command As SqlCommand = _
	New SqlCommand(Database Creation Code, connection)

    connection.Open()
    Command.ExecuteNonQuery()
    connection.Close()
End Sub

To create a database from the Command Prompt, open the DOS window and use the SQLCMD program. Then write code as we will learn next.

CREATE a DATABASE

The command used to create a database in SQL uses the following formula:

CREATE DATABASE DatabaseName

The CREATE DATABASE (remember that SQL is not case-sensitive) expression is required. The DatabaseName factor is the name that the new database will carry.

A statement in SQL can be terminated with a semi-colon. Although this is a requirement in many implementations of SQL, in Microsoft SQL Server, you can omit the semi-colon. Otherwise, the above formula would be

CREATE DATABASE DatabaseName;

Instead of manually writing all of your code, the Microsoft SQL Server Management Studio provides a code template you can use and customize. To access the Template Explorer, on the main menu, you can click View -> Template Explorer. Before creating a database, open a new query window. Then:

  • To create a new database using sample code, in the Template Explorer, expand the Databases node, then drag the Create Database node and drop it in the query window. The new database would be created in the server that holds the current connection
  • If you have access to more than one server, to create a database in another server or using a different connection, in the Template Explorer, expand the Databases node, right-click Create Database and click Open. In the Connect to Database Engine dialog box, select the appropriate options, and can click OK

With any of these actions, Microsoft SQL Server would generate sample code for you:

-- =============================================
-- Create database template
-- =============================================
USE master
GO

-- Drop the database if it already exists
IF  EXISTS (
	SELECT name 
		FROM sys.databases 
		WHERE name = N'<Database_Name, sysname, Database_Name>'
)

CREATE DATABASE <Database_Name, sysname, Database_Name>
GO

You would then need to edit the code and execute it to create the database. If some sections of this code are not familiar to you, we will cover them in subsequent lessons

The Name of a Database

Probably the most important requirement of creating a database is to give it a name. The SQL is very flexible when it comes to names. In fact, it is very less restrictive than most other computer languages. Still, there are rules you must follow when naming the objects in your databases:

  • A name can start with either a letter (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x, y, z, A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, or Z), a digit (0, 1, 2, 3, 4, 5, 6, 7, 8, or 9), an underscore (_) or a non-readable character. Examples are _n, act, %783, Second
  • After the first character (letter, digit, underscore, or symbol), the name can have combinations of underscores, letters, digits, or symbols. Examples are _n24 or act_52_t
  • A name can include spaces. Example are c0untries st@ts, govmnt (records), or gl0b# $urvey||

Because of the flexibility of SQL, it can be difficult to maintain names in a database. Based on this, there are conventions we will use for our objects. In fact, we will adopt the rules used in C/C++, C#, Pascal, Java, and Visual Basic, etc languages. In our databases:

  • Unless stated otherwise (we will mention the exception, for example with variables, tables, etc), a name will start with either a letter (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x, y, z, A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, or Z) or an underscore
  • After the first character, we will use any combination of letters, digits, or underscores
  • A name will not start with two underscores
  • If the name is a combination of words, at least the second word will start in uppercase. Examples are Countries Statistics, Global Survey, _RealSport, FullName, or DriversLicenseNumber

After creating an object whose name includes space, whenever you use that object, include its name between [ and ]. Examples are [Countries Statistics], [Global Survey], or [Date of Birth]. Even if you had created an object with a name that does not include space, when using that name, you can still include it in square brackets. Examples are [UnitedStations], [FullName], [DriversLicenseNumber], and [Country].

Practical LearningPractical Learning: Creating a Database

  1. Start Microsoft Visual Basic and create a new Windows Application named DatabaseManagement
  2. In the Server Explorer, right-click Data Connections and click Create New SQL Server Database...
  3. In the Server Name of the Create New SQL Server Database, type (local)
  4. In the New Database Name text box, type World Hunger Statistics
     
    Create Database
  5. Click OK (if you receive an error that the database was not created, open the Services in the Administrative Tools, check that SQL Server (server name) and SQL Server (MSSQLSERVER) services had started)
  6. Design the form as follows:
     
    Form
    Control Text Name
    Label Database Name:  
    TextBox   txtCreateDatabase
    Button Create btnCreateDatabase
  7. Double-click the Create button
  8. Implement the Click event of the button as follows:
     
    Imports System.Data.SqlClient
    
    Public Class Exercise
    
        Private Sub btnCreateDatabase_Click(ByVal sender As System.Object, _
                                            ByVal e As System.EventArgs) _
                                            Handles btnCreateDatabase.Click
            ' Make sure the user enters the name of the database
            If txtCreateDatabase.Text.Length = 0 Then
                MsgBox("You must specify the name of " & _
                     "the database you want to create")
                Exit Sub
            End If
    
            Dim strConnection As String = _
                "Data Source=(local);Integrated Security=yes"
    
            Using connection As SqlConnection = New SqlConnection(strConnection)
                Dim strDatabase As String = txtCreateDatabase.Text
                Dim cmd As SqlCommand = _
                    New SqlCommand("CREATE DATABASE [" & strDatabase & "];", _
                    connection)
    
                connection.Open()
    
                cmd.ExecuteNonQuery()
    
                MsgBox("A database named """ & _
                                txtCreateDatabase.Text & _
                                """ has been created on the " & _
                                connection.DataSource & " server.")
    
                txtCreateDatabase.Text = ""
            End Using
        End Sub
    End Class
  9. Execute the application
  10. In the Database text box, type Red Oak High School1
  11. Click the Create button
     
    Database Maintenance
  12. Close the form and return to your programming environment

The Location of a Database

As you should be aware already from your experience on using computers, every file must have a path. The path is where the file is located in one of the drives of the computer. This allows the operating system to know where the file is, so that when you or another application calls it, the operating system would not be confused.

By default, when you create a new database, Microsoft SQL Server assumed that it would be located at Drive:C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data folder. If you use the New Database dialog box of the SQL Server Management Studio, if you specify the name of the database and click OK, the interpreter automatically creates a new file, and appends the .MDF extension to the file: this is the (main) primary data file of your database.

The Primary Size of a Database

When originally creating a database, you may or may not know how many lists, files, or objects the project would have. Still, as a user of computer memory, the database must use a certain portion, at least in the beginning. The amount of space that a database is using is referred to as its size. If you use the New Database dialog box in the Microsoft SQL Server Management Studio, after specifying the name of the database and clicking OK, the interpreter automatically specifies that the database would primarily use 2MB. This is enough for a starting database. Of course, you can either change this default later on or you can increase it when necessary.

If you want to specify a size different from the default, if you are using the New Database to create your database, in the Database Files section and under the Initial Size column, change the size as you wish.

This series of lessons is intended for (junior) beginning database developers. The target is for those who are learning to create applications and design graphical database applications, not for those who manage databases. Therefore, in our lessons, unless stated otherwise and unless we have to, we will use the default settings of both Microsoft SQL Server and of a regular database. Microsoft SQL Server is so huge that it would be hard to cover both database development (database creation, graphical application design, functions, stored procedures, parameters, views, etc) and database administration (file locations, file sizes, backups, triggers, distribution, user access, client/server issues, etc) in these lessons.
 

Database Maintenance

 

Connecting to a Database

Once a database exists on the server, to use it, as we saw in the previous lesson, you must first establish a connection to it. We saw that, to programmatically connection to a Microsoft SQL Server database, you could use a SqlConnection variable. In the connection string, to specify the database, assign its name to the Database attribute. Here is an example:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Dim connection As SqlConnection = _
    New SqlConnection("Data Source=(local);" & _
                      "Database='Exercise';" & _
                      "Integrated Security=yes;")
End Sub

Once you have established a connection, you can then open it and perform the desired actions.

Renaming a Database

Database maintenance consists of renaming one database or removing another. To change the name of a database, Transact-SQL provides sp_renamedb. (Notice that the name starts with sp_. This is called a stored procedure. We will learn how to create them. For now, you can trust that they work and do what they are supposed to do). The formula used would be:

EXEC sp_renamedb 'ExistingName', 'NewName'

The EXEC sp_renamedb expression is required. The ExistingName factor is the name of the database that you want to rename. The NewName factor is the name you want the database to have after renaming it.

Here is an example of renaming a database:

EXEC sp_renamedb 'RentalCars', 'BethesdaCarRental
GO

To rename a table in a Visual Basic code, pass the EXEC sp_renamedb code as string to a SqlCommand object and call the SqlCommand.ExecuteNonQuery() method.

Deleting a Database

If you have created a database but do not need it anymore, you can delete it. It is important to know, regardless of how you create a database, whether using Microsoft SQL Server Management Studio, the Command Prompt, or Microsoft Visual Studio, every database can be accessed by any of these tools and you can delete any of the databases using any of these tools. As done with creating a database, every tool provides its own means.

To delete a database in Microsoft SQL Server Management Studio, locate it in the left frame, right-click it and click Delete:

Delete Object

The Delete Object dialog box would come up. If you still want to delete the database, you can click OK.

To delete a database in SQL, you use the DROP DATABASE instruction followed by the name of the database. The formula used is:

DROP DATABASE DatabaseName

Before deleting a database in SQL, you must make sure the database is not being used or accessed by someone else or by another object.

If you are in the Microsoft SQL Server Management Studio, you can delete a database using a code template. To get that code, display the Template Explorer. From the Template Explorer, expand the Databases node, then drag the Create Database node and drop it in the query window. You can then customize the generated code:

-- =============================================
-- Create database template
-- =============================================
USE master
GO

-- Drop the database if it already exists
IF  EXISTS (
	SELECT name 
		FROM sys.databases 
		WHERE name = N'<Database_Name, sysname, Database_Name>'
)
DROP DATABASE <Database_Name, sysname, Database_Name>
GO

Practical LearningPractical Learning: Deleting a Database

  1. To allow the user to delete a database, change the design of the form as follows:
      
    Database
    Control Text Name
    Label Database Name:  
    TextBox   txtDatabaseCreate
    Button Create btnCreateDatabase
    Label Database Name:  
    TextBox   txtDatabaseDelete
    Button Create btnDeleteDatabase
  2. Double-click the Delete button
  3. Implement the Click event of the button as follows:
     
    Private Sub btnDeleteDatabase_Click(ByVal sender As System.Object, _
                                            ByVal e As System.EventArgs) _
                                            Handles btnDeleteDatabase.Click
            ' Make sure the user enters a database
            If txtDeleteDatabase.Text.Length = 0 Then
                MsgBox("You must specify the name of " & _
                                "the database you want to delete")
                Exit Sub
            End If
    
            Dim strConnection As String = _
                    "Data Source=(local);Integrated Security=yes"
    
            Using connection As SqlConnection = New SqlConnection(strConnection)
                Dim strDatabase As String = txtDeleteDatabase.Text
                Dim cmd As SqlCommand = _
                        New SqlCommand("DROP DATABASE [" & strDatabase & "];", _
                        connection)
    
                connection.Open()
    
                cmd.ExecuteNonQuery()
    
                MsgBox("A database named """ & _
                                txtDeleteDatabase.Text & _
                                """ has been deleted from the " & _
                                connection.DataSource & " server.")
    
                txtDeleteDatabase.Text = ""
            End Using
    End Sub
  4. Execute the application
  5. In the Database text box, type Red Oak High School1
  6. Click the Delete button
     
    Deleting a Database
  7. Click OK
  8. In the same way, delete the World Hunger Statistics database
  9. Close the form and return to your programming environment

Database Routines

 

The Current Database

While writing SQL code, you should always know what database you are working on, otherwise you may add code to the wrong database. To programmatically specify the current database, use the USE keyword followed by the name of the database. The formula to use is:

USE DatabaseName;

Here is an example:

USE GovernmentStatistics;

Refreshing the List of Databases

Some of the windows that display databases, like the Microsoft SQL Server Management Studio, do not update their list immediately if an operation occurred outside their confinement. For example, if you create a database in the query window or in a Windows Application, its name would not be updated in the Object Explorer. To view such external changes, you can refresh the window that holds the list.

In Microsoft SQL Server Management Studio, to update a list, you can right-click its category in the Object Explorer and click Refresh. Only that category may be refreshed. For example, to refresh the list of databases, in the Object Explorer, you can right-click the Databases node and click Refresh.

Microsoft SQL Server Primary Settings

 

Introduction

When you install Microsoft SQL Server, it also installs 5 databases named master, model, msdb, and tempdb. These databases will be for internal use. This means that you should avoid directly using them, unless you know exactly what you are doing.

One of the databases installed with Microsoft SQL Server is named master. This database holds all the information about the server on which your Microsoft SQL Server is installed. For example, we saw earlier that, to perform any operation on the server, you must login. The master database identifies any person, called a user, who accesses the database, about when and how.

Besides identifying who accesses the system, the master database also keeps track of everything you do on the server, including creating and managing databases.

You should not play with the master database; otherwise you may corrupt the system. For example, if the master database is not functioning right, the system would not work.

A Namespace

As you should know from the Visual Basic language, a namespace is a technique of creating a series of items that each has a unique name. For example, if you start creating many databases, there is a possibility that you may risk having various databases with the same name. If using a namespace, you can isolate the databases in various namespaces. In reality, to manage many other aspects of your database server, you use namespaces and you put objects, other than databases, within those namespaces. Therefore, a namespace and its content can be illustrated as follows:

Namespace

Notice that there are various types of objects within a namespace but each object has a distinct name.

The Schema of a Database

Within a namespace, you can create objects as you wish. To further control and manage the objects inside of a namespace, you can put them in sub-groups called schemas. Therefore, a schema is a group of objects within a namespace. This also means that, within a namespace, you can have as many schemas as you want:

Schema

To manage the schemas in a namespace, you need a way to identify each schema. Based on this, each schema must have a name. In our illustration, one schema is named Schema1. Another schema is named Schema2. Yet another schema is named Schema_n.

Inside of a schema, two objects cannot have the same name, but an object in one schema can have the same name as an object in another schema. Based on this, if you are accessing an object within its schema, you can simply use its name, since that name would be unique. On the other hand, because of the implied possibility of dealing with objects with similar names in your server, when accessing an object outside of its schema, you must qualify it. To do this, you would type the name of the schema that contains the object you want to use, followed by the period operator, followed by the name of the object you want to use. From our illustration, to access the Something1 object that belongs to Schema1, you would type:

Schema1.Something1

There are two types of schemas you can use, those built-in and those you create. When Microsoft SQL Server is installed, it also creates a few schemas. One of the schemas is called sys.

The sys schema contains a list of some of the objects that exist in your system. One of these objects is called databases (actually, it's a view). When you create a database, its name is entered in the databases object using the same name you gave it.

The Database Owner

In the previous lesson, we saw that, before using a database, you must establish a connection with the server. You do this using a user account that can use the server. Once the connection exists, you can create a database. In Microsoft SQL Server, the user who creates a database is referred to as the database owner. To identify this user, when Microsoft SQL Server is installed, it also creates a special user account named dbo. This account is automatically granted various permissions on the databases of the server.

Because the dbo account has default access to all databases, to refer to an object of a database, you can qualify it by typing dbo, followed by the period operator, followed by the name of the object.

 

Home Copyright © 2008-2012 FunctionX