Part 2: The history of computer
*****************************************
Congratulations for reaching the end of this SQL Server tutorial!
SQL Server is a powerful application, and this tutorial has provided an overview of the main concepts involved in administering SQL Server 2008 Express.
To recap briefly, we've:
There were a couple of areas we didn't touch on during this tutorial. This is mainly due to the fact that it is aimed at the free SQL Server Express version, which has its limitations.
All is not lost though. My SQL Server 2000 tutorial covers some of the areas that weren't included here. For example, you can learn about the SQL Server Agent for scheduling jobs to run automatically. You can also read about the SQL Profiler, which allows you to monitor events within your SQL Server.
The most logical next step from learning SQL Server is to learn SQL itself (if you haven't already done this that is). SQL stands for Structured Query Language and enables you to perform database related tasks programatically. Most of the tasks in this tutorial can be done programatically using SQL.
Probably the most common use for SQL is to work with the data in the database. For example, to insert data, select data, update data, or to delete data. Any database driven website will have many SQL statements doing this stuff.
To learn more about SQL, check out the SQL tutorial.
SQL Server Integration Services (SSIS) allows you to integrate smoothly with other applications and data sources.
You can use Integration Services to copy or download files, send e-mail messages in response to events, update data warehouses, clean and mine data, and manage SQL Server objects and data.
In SQL Server 2005 and higher, Data Transformation Services (DTS) is no longer installed with SQL Server. All DTS related tasks are now performed with SSIS.
If you have been working with earlier versions of SQL Server (SQL Server 2000 and earlier), you are probably familiar with DTS. DTS allowed you to transfer data between disparate sources into one or more destinations.
Well, starting from SQL Server 2005, DTS has been deprecated. DTS is no longer included with your SQL Server installation.
If you need DTS-type functionality in SQL Server 2008, you will need to use SQL Server Integration Services.
For backwards compatibility, Microsoft has provided installation files that provide run-time support for DTS packages. This will be useful if you have already created DTS packages in an earlier version of SQL Server. For more information see Support for Data Transformation Services (DTS) in SQL Server 2008 on the MSDN website.
SQL Server Integration Services is not available in the Express or Workgroup editions of SQL Server. If you need to use Integration Services, you need at least SQL Server Standard edition. And, if you need to use advanced features (such as data mining, text mining and cleansing or fuzzy transforms), you will need SQL Server Enterprise edition.
For more information on SQL Server Integration Services, check out SQL Server Integration Services on the MSDN website.
If you've been navigating around SQL Server Management Studio (SSMS), you may have come across the Linked Servers option.
The Linked Servers option allows you to connect to another instance of SQL Server running on a different machine, perhaps remotely in a different city/country. This can be useful if you need to perform distributed queries (query a remote database). Setting up a linked server is quite straight forward in SSMS, all you need is details of the remote server, and the database that you need to query.
To create a linked server:
Once you have configured your linked server, you will be able to run queries etc against it. When you run a query against a linked server, it is referred to as a distributed query.
When you execute a distributed query against a linked server, you must include a fully qualified, four-part table name for each data source to query. This four-part name should be in the form linked_server_name.catalog.schema.object_name.
Here's an example:
This example is based on the linked server example above. It assumes that the remote database has a schema called "Person" and a table called "Enemy".
Ever since SQL Server 2005 was released, each object in a database has belonged to a database schema. SQL Server 2008 has continued with database schemas, and an explanation follows.
A database schema is a way to logically group objects such as tables, views, stored procedures etc. Think of a schema as a container of objects.
You can assign a user login permissions to a single schema so that the user can only access the objects they are authorized to access.
Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any user, and schema ownership is transferable.
To create a database schema in SQL Server 2008:
Now that we have a new schema, we can add objects such as tables, views, and stored procedures to it. For example, we could transfer the table that we created in the earlier lesson to the new schema.
When we created that table (called "Individual"), it was created in the default database schema ("dbo"). We know this because it appears in our object browser as "dbo.Individual".
To transfer the "Individual" table to the person "schema":
Your table has now been transferred to the "person" schema.
To confirm the change:
When creating a new user login in SQL Server, you get the option of assigning the login one or more server roles.
Server roles (not to be confused with database roles) are available for various database administration tasks. Not everyone should be assigned to a server role. In fact, only advanced users such as database administrators should be assigned a server role.
To access the server roles in SQL Server Management Studio, expand the Security folder:
You view the properties of a server role by right clicking on it. You can then add users to the server role by clicking Add. In the screenshot below, Homer has been added to the securityadmin role.
Here's an explanation of the server roles defined in SQL Server 2008 during setup:
| Server Role | Description |
|---|---|
| sysadmin | Can perform any task in SQL Server. |
| serveradmin | Can set server-wide configuration options, can shut down the server. |
| setupadmin | Can manage linked servers and startup procedures. |
| securityadmin | Can manage logins and database permissions, read logs, change passwords. |
| processadmin | Can manage processes running in SQL Server. |
| dbcreator | Can create, alter, and drop databases. |
| diskadmin | Can manage disk files. |
| bulkadmin | Can execute BULK INSERT statements. |
| public | Every SQL Server user account belongs to this server role. When a server principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. Only assign public permissions on an object when you want the object to be available to all users. |
As you can see, some of these roles allow very specific tasks to be performed. If you don't have many technical users, it's likely that you'll only use one or two of these roles (including sysadmin).
SQL Server allows for the creation of user logins. Each individual who needs access to SQL Server can be given their own user account.
When the administrator configures these user logins, he/she can assign them to any number of roles and schemas, depending on the access that the individual is entitled to.
In this lesson, we will walk through the steps in creating a user login.
************************************************************************
If you get an error that reads "The MUST_CHANGE option is not supported by this version of Microsoft Windows", simply uncheck the "User must change password at next login" option. The error occurs because your operating system doesn't support this option.
Note that these roles are "Database Roles" and are different to the server roles in the previous tab. Server roles are for administering the SQL Server. Database roles are created within each database and specify what the login can do within that database.
Stored procedures are a powerful part of SQL Server. They can assist programmers and administrators greatly in working with the database configuration and its data.
A stored procedure is a precompiled group of Transact-SQL statements, and is saved to the database (under the "Stored Procedures" node). Programmers and administrators can execute stored procedures either from the SQL Server Management Studio or from within an application as required.
Transact-SQL, which is based on SQL (Structured Query Language), is the programming language used to interface between applications and their databases. Transact-SQL is a relatively easy language to learn and I highly recommend becoming familiar with it.
Here are some key benefits in using stored procedures:
| Benefit | Explanation of benefit |
|---|---|
| Modular programming | You can write a stored procedure once, then call it from multiple places in your application. |
| Performance | Stored procedures provide faster code execution and reduce network traffic.
|
| Security | Users can execute a stored procedure without needing to execute any of the statements directly. Therefore, a stored procedure can provide advanced database functionality for users who wouldn't normally have access to these tasks, but this functionality is made available in a tightly controlled way. |
You create stored procedures in the SQL Server Management Studio using the CREATE PROCEDURE statement, followed by the code that makes up the stored procedure.
CREATE PROCEDURE StoredProcedureName AS
...
The following code creates a stored procedure called "MyStoredProcedure":
CREATE PROCEDURE MyStoredProcedure AS
SET ROWCOUNT 10
SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
FROM Products
ORDER BY Products.UnitPrice DESC
Once you run this code in the SQL Server Management Studio, the stored procedure is created and appears under the "Stored Procedures" node.
If you need to modify an existing stored procedure, you simply replace the CREATE with ALTER.
ALTER PROCEDURE MyStoredProcedure AS
...
You can run a stored procedure by using EXECUTE or EXEC. For example, to run the above stored procedure, type the following:
EXEC MyStoredProcedure
If the stored procedure has spaces in its name, enclose it between double quotes:
EXEC "My Stored Procedure"
If your stored procedure accepts any parameters, they are placed after the procedure name:
EXEC MyStoredProcedure @ParameterName="MyParameter"
So, here's an example:
EXEC SalesByCategory @CategoryName ="Beverages"
You can also use the graphical user interface to initiate the execution of a stored procedure.
To initiate a stored procedure this way:
A parameter is a value that your stored procedure uses in order to perform it's task. When you write a stored procedure, you can specify the parameters that need to be supplied from the user. For example, if you write a stored procedure to select the address details about an individual, your stored procedure needs to know which individual to select. In this case, the user can provide an IndividualId or UserId to tell the stored procedure which individual to look up.
SQL Server includes a large number of system stored procedures to assist in database administration tasks. Many of the tasks you can perform via SQL Server Management Studio can be done via a system stored procedure. For example, some of the things you can do with system stored procedures include:
A view can be useful when there are multiple users with different levels of access, who all need to see portions of the data in the database (but not necessarily all of the data). Views can do the following:
Any view that you create ends up being located under the "Views" folder of your database.
The following screenshot shows a number of views that are located within the "AdventureWorks2008" database:
You create a view by using the CREATE VIEW statement, followed by the SELECT statement.
CREATE VIEW ViewName AS
SELECT ...
Example:
CREATE VIEW "Alphabetical list of products" AS
SELECT Products.*, Categories.CategoryName
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE (((Products.Discontinued)=0))
You can modify an existing view by using using ALTER instead or CREATE.
Example:
ALTER VIEW "Alphabetical list of products" AS
SELECT Products.*, Categories.CategoryName
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE (((Products.Discontinued)=0))
You can also right click on the view and select "Design".
You run a view by using a SELECT statement.
SELECT TOP 1000 *
FROM [AdventureWorks2008].[Sales].[vIndividualCustomer]
You can also right-click on the view and select "Select Top 1000 Rows".
Running the above view results in this:
As you can see, it looks just like you've selected rows from a table. The difference is that, each column could potentially be coming from a different table.
The graphical query designer is a graphical user interface that allows you to build queries to run against your SQL Server database. This can be particularly useful when building complex queries that involves many tables, views etc.
The query designer can also be beneficial for those who are learning how to write SQL. This is because you don't need to remember the SQL syntax in order to write queries against your database - the query designer generates the SQL for you.
To build a query with the query gesigner:
Once you've clicked OK, you will find the query has been added to your workspace. You can then run it as you would any other query.
We can use the "Edit Top 200 Rows" option to add data to our table.
Note that you don't need to enter data into the IndividualId and DateCreated columns. This is because the they will be populated automatically (remember, we set IndividualId to "Is Identity" and DateCreated to "GetDate()"))
The above method is fine if you only have a small amount of data to enter or update. If you have a lot of data to enter, this could become very tedious. Also, if you have multiple environments (for example, a development environment, staging environment, and production environment), with duplicate databases configured in each environment, you will need to re-enter the same data into each environment.
When you're first learning SQL Server, this may not be a major concern. However, in an environment such as described, entering data directly into the table becomes quite inefficient.
In most cases, you will probably find it more efficient to write a SQL script. Using a script enables you to re-enter the data as many times as you like. If you need to rebuild your database for any reason, you can simply run your script to enter the data. If you have multiple environments, once again you can run your script against each environment.
Once you get used to writing and running scripts, you will probably find it quicker than entering data directly into the table.
When you create a database driven website, you actually write SQL scripts to insert data into the database. You also use SQL scripts to read data, update data, and delete data. These SQL scripts can be placed directly into your website code (PHP, ColdFusion etc), or they can be stored in the database as Stored Procedures or Views.