If you are new to the database world and haven't had a lot of experience programming, you may be curious about Unicode character strings vs. ANSI character strings. To truly understand this, you must look to the origins of computers. The American National Standards Institute (ANSI) came out with a standard known as American Standard Code for Information Interchange (ASCII). This standard defines letters, numbers, and symbols that are referred to as a character set. The ASCII character set handles 256 different characters generally stored as 1 byte within a computer. With only 26 letters in the English language, you may be wondering why anyone would need more than 256 characters. If the world spoke only English, they wouldn't, but our friends in China struggled to figure out how to represent all their thousands of characters within this 256-character limit. What Unicode allows is the storage of 2 bytes per character. These two bytes allow for more than 65,000 different characters. All modern operating systems use Unicode, and SQL Server supports both the Unicode and ANSI character sets.

NULL is not your annoying cousin; it identifies that a value does not exist in the database. Take the Pets table definition, for example:

CREATE TABLE [Pets]
(pet_id         INT                         PRIMARY KEY,
pet_name    VARCHAR(50)      NOT NULL,
pet_weight  INT                         NULL)
GO

In this scenario, it's OK that the pet weight is not entered. However, it is not acceptable for the pet to not have a name. In the table definition, you see pet_name was defined with a NOT NULL keyword. This means that the absence of a value cannot be a valid value for the pet_name column. Conversely, pet_weight was defined as NULL, meaning that you could insert a value into the table and leave out the pet's weight. If you queried the newly entered data, you would see that pet_weight is NULL.

The SELECT statement is a DML statement that allows users to retrieve data from the database. A simplified version of the syntax is as follows:

SELECT <select_list> FROM <table_source> [WHERE <search_condition>]

where the parameters are defined as follows:

The WHERE clause is optional. If you omit this clause, you will return all the rows from the given table. You can think of the WHERE clause like a Boolean expression. That is, the expression will be evaluated across every row in the table.

Let's look at an example. If you wanted to return all the pets' names and weights, you would issue the following SELECT query:

SELECT pet_name, pet_weight FROM Pets

This statement would return the following result set:

pet_name     pet_weight
Zeus             185
Lady             155
Deno              50
Rex               44
Rover             15

If you wanted to narrow down the result set, you could add a WHERE clause like the following:

SELECT pet_name, pet_weight FROM Pets WHERE pet_weight > 100

This WHERE expression would cause the result set to return only pets whose weight was greater than the value 100.

In the example, you explicitly asked SQL Server to return specific columns from the table. You could have used an asterisk (*) instead, and this would have returned all the columns in the table. To return all the columns in the Pets table, you would issue the following statement:

SELECT * FROM Pets

Sometimes tables have millions or billions of rows, and you don't really want to enumerate all of them at once. There are expressions like TOP that you can use to limit the result set. TOP specifies that only a specific number of results or percentage of results will be returned from the query. For example, SELECT TOP 3 * FROM Pets will return the first three pets stored within the table, as shown here:

Pet_id   pet_name   pet_weight   MicroChipID
1           Zeus          185        398BF49
2           Lady          155        191ABBC
3           Deno           50       790AG441

This limiting capability is also useful when used in conjunction with other clauses defined within the query. The ORDER BY clause is used to instruct SQL Server to order the result set in a particular way. You specify the columns to sort by and the direction of the sort as parameters of the ORDER BY clause. For example, if you wanted to know the top three lightest pets, you would issue the following query:

SELECT TOP 3 * FROM Pets ORDER BY pet_weight ASC

This would yield the following result:

Pet_id   pet_name   pet_weight   MicroChipID
5           Rover         15        A8719841
4           Rex           44        CDD81322
3           Deno          50        790AG441

The ASC parameter instructs SQL Server to order the results in ascending order, or lowest to highest. If you wanted to go the other way and order from highest to lowest, you could have specified DESC, which means descending.

A key value for relational databases is the fact that sometimes different tables hold data that is related to each other. This data can be combined and returned in a single result set. In this example, you have a Pets table and an Owners table. In this scenario, pets have owners, so if you wanted to answer a question like which owners have pets with a weight greater than 100, you could accomplish this by joining the tables within the query. When you join tables together, you have the option of specifying what to do with the rows that are matched and what to do with the ones that are not matched. What happens depends on what kind of JOIN you include in your query. For example, if you wanted to know all the pets who have owners, you could execute this statement:

SELECT pet_name,owner_name FROM Pets
 INNER JOIN Owners
 ON Pets.pet_id=Owners.pet_id

This statement will yield the following results:

pet_name     owner_name
Lady            Bryan
Deno            Rob
Zeus            Rob

If you look at the data in both the Pets table and the Owners table, you can see that there were two additional pets, Rex and Rover, that were not displayed. This is because the Owners table does not have a matching pet_id for these two pets.

If you wanted to list all the pets regardless of whether they had an owner, you could issue a LEFT OUTER JOIN, which will always include the data from the table defined on the left side of the JOIN statement. The following is an example of listing all the pets and their owners, if they have any.

SELECT pet_name,owner_name FROM Pets
 LEFT OUTER JOIN Owners
 ON Pets.pet_id=Owners.pet_id

This statement will yield the following result:

pet_name   owner_name
Zeus           Rob
Lady           Bryan
Deno           Rob
Rex            NULL
Rover          NULL

Notice that NULL values result when owners do not exist. Rex has no owner name on record. The system doesn't make up a name where none exists. When no value exists, the system will return a NULL.

There are other types of joins; RIGHT OUTER joins are similar to LEFT OUTER joins except they include the result set for the table to the right of the JOIN statement. A FULL OUTER join lists all results for both tables to the left and right of the JOIN statement. For more information on the different join types, see the SQL Server Books Online article "Using Joins" at http://msdn.microsoft.com/en-us/library/ms191472.aspx.

SELECT is arguably the most used DML statement. There are many different expressions and clauses that are possible to use with SELECT. Mastering the use of this statement will be important in your daily use of SQL Server or any other database platform. SQL Server Books Online covers the syntax in the article "SELECT (Transact-SQL)" located at http://msdn.microsoft.com/en-us/library/ms189499.aspx.

The INSERT statement is a DML statement that allows users to write data into a database. A simplified syntax is as follows:

INSERT <table_name> [(column list)]
VALUES( { DEFAULT | NULL | <expression> } [ ,...n] )

where the parameters are defined as follows:

The following sample script will add more pets to the Pets table:

INSERT INTO Pets (pet_id, pet_name, pet_weight, MicroChipID)
 VALUES (6,'Roxy',7,'1A8AF59'),
(7,'Champ',95,'81CB910'),
(8,'Penny',80,'C710A6B')

Alternatively, you could have omitted the specific columns you wanted to insert. If you do this, SQL Server will assume that the first value will be for the first column definition, the second value entered will be for the second columns definition, and so on. For example, since you are inserting information into all the columns in the Owners table, you could issue this statement and omit owner_id, pet_id, and owner_name from the statement:

INSERT INTO Owners VALUES (4,5,'Frank')

The UPDATE statement is a DML statement that allows the user to modify existing data within the database. A simplified definition is as follows:

UPDATE <object>
SET { column_name = { <expression> | DEFAULT | NULL }
[ WHERE <search_condition> ]

where the parameters are defined as follows:

As an example, let's update the Owners table and change the ownership of dogs owned by Frank to Courtney. You can do this with the following UPDATE statement:

UPDATE Owners SET owner_name='Courtney' WHERE owner_name='Frank'

The WHERE clause is optional. However, if you do not specify a search condition, you will update every row. So, unless updating all rows is what you intend to do, you should always specify a WHERE clause.

The DELETE statement is a DML statement that allows users to remove data from the database. A simplified definition is as follows:

DELETE FROM <table_source> [ WHERE <search_condition> ]

where the parameters are defined as follows:

The following example uses the DELETE statement to remove the pet named Penny:

DELETE FROM Pets WHERE pet_name = 'Penny'

The WHERE clause is optional. If you omit it, you will delete all rows in the specified table. Be very careful about omitting it. Omitting the WHERE clause and inadvertently deleting all data from a table is a common mistake. It's a mistake that you really want to avoid making.

The following is an example showing how to begin a transaction, execute some statements, and then commit the transaction. The example swaps the names of the two pets, Lady and Deno:

BEGIN TRANSACTION
UPDATE Pets SET pet_name='Lady' WHERE pet_id=3
UPDATE Pets SET pet_name='Deno' WHERE pet_id=2
COMMIT

Notice how the two UPDATE statements are bracketed by the statements BEGIN TRANSACTION and COMMIT. The two UPDATE statements form a transaction. Either they will both succeed or they will both fail. And if they succeed, they will both appear to other database users to have executed simultaneously. No other user but you will ever see both pets having the same name of Lady. You as the person executing the transaction are the only user with the ability to query the intermediate state between the two UPDATE statements. I'll talk more about this in the "Transaction Isolation" section.

If you are executing a transaction and change your mind or make a mistake, you can issue a ROLLBACK statement to undo the damage. For example:

BEGIN TRANSACTION
DELETE FROM Pets
ROLLBACK

This example deletes all data from the Pets table and then issues a ROLLBACK statement to undo that deletion. You can protect yourself by following this pattern. Wrap critical DELETE and UPDATE statements in a transaction. Then you can roll back in the event you mistype, omit the WHERE clause, or otherwise make a mistake.

When you issue a BEGIN TRANSACTION statement, you are telling SQL Server that from this point in the T-SQL batch, isolate the effect of the statements from the rest of the user connections. Each transaction lasts either until it completes without errors and a COMMIT TRANSACTION statement is issued or until errors are encountered and all modifications are erased with a ROLLBACK TRANSACTION statement.

To help illustrate the transactional isolation from the rest of user connections, consider the following case where there are two different connections to SQL Server. User1 is connected and issues the following statement:

BEGIN TRANSACTION
UPDATE Pets SET pet_name='Big Boy' WHERE pet_id=5
SELECT pet_name FROM Pets WHERE pet_id=5

At this point, the result set for the SELECT statement is as follows:

pet_name
Big Boy

User2 is connected to the same SQL Server and issues the following statement:

SELECT pet_name FROM Pets WHERE pet_id=5

The result set for User2's SELECT statement is as follows:

pet_name
Rover

User2 still sees the old value until User1 issues a COMMIT TRANSACTION statement.

This isolation is valuable and critical to maintain consistency of the database. As a user, you can change the behavior of the isolation. If User2 wanted to know the value even though User1 did not commit the transaction yet, User2 could manually set the transaction isolation level using the SET TRANSACTION ISOLATION statement.

To read the uncommitted data in the Pets table, User2 would issue the following code:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
SELECT pet_name FROM Pets WHERE pet_id=5
GO

This would return the uncommitted "Big Boy" value instead of the original "Rover" value. It is important to note that it is not a best practice to keep transactions open; they take up valuable server resources, and other users could be depending on the data being used within the transaction. In this example, User1 should commit or roll back the transaction as soon as possible.

There are many different isolation levels that can be set. SQL Server Books Online describes all of these in detail in the article "Set Transaction Isolation Level (Transact-SQL)" at http://msdn.microsoft.com/en-us/library/ms173763.aspx.

Any time you have multiple users trying to access and update the same piece of data, you will run into problems. One user may have read a value and, based on that value, performed some work, when in reality the value was updated right after the original read and now causes problems for the user. SQL Server places locks at different levels within the database such as rows or the entire table itself. This makes the multiuser scenario possible.

A lock is a way to synchronize multiple user access to the same piece of data. Locks have different modes such as shared or exclusive. Depending on the lock type and the actions each user is trying to perform, you may end up in a deadlock situation. Deadlocks occur when User1 holds a lock on a resource and is requesting access to another resource that User2 holds a lock on. User1 has to wait for User2 to release the lock. However, User2 is also requesting a lock on User1's resource. Thus, User1 and User2 are both waiting for each other to release the locks. In the end, SQL Server will choose one to be the victim and roll back that user's transaction. You can find an in-depth discussion on deadlocking in the SQL Server Books Online article called "Deadlocking" at http://msdn.microsoft.com/en-us/library/ms177433.aspx.

SQL Server does a good job of providing tools to debug deadlock situations. You can use SQL Server Profiler to capture a SQL trace. Figure 5-1 shows the output of SQL Server Profiler when a deadlock was encountered.

Figure 5.1. SQL Server Profiler trace showing a deadlock event

For a deadlock event, SQL Server Profiler displays the server process IDs that were involved with the deadlock as well as who the deadlock victim was. Figure 5-2 shows a detailed view of the deadlock graph showing the actual T-SQL statement that was issued by the deadlock victim.

Figure 5.2. Deadlock event showing T-SQL statement issued by deadlock victim

Alternatively, the Performance Data Collector feature in SQL Server 2008 also captures locking as part of the Server Activity collection set. You'll learn more about SQL Server Profiler and the Performance Data Collector in Chapter 11.

SSMS provides a template to use when creating a new stored procedure. To view this template, navigate to the Stored Procedures node within the Programmability node of the VetClinic database. Select New Stored Procedure from the context menu. This will open a Query Editor window with the following template:

-- ================================================
-- Template generated from Template Explorer using:

-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:         <Author,,Name>
-- Create date:  <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
  -- Add the parameters for the stored procedure here
  <@Param1, sysname, @p1> <Datatype_For_Param1, , int> =
     <Default_Value_For_Param1, , 0>,
  <@Param2, sysname, @p2> <Datatype_For_Param2, , int> =
     <Default_Value_For_Param2, , 0>
AS
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;

    -- Insert statements for procedure here
   SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

Having the syntax already present is helpful, but SSMS makes working with templates easy by giving you a dialog box to use to fill in the values. To launch the Specify Values for Template Parameters dialog box, select this option from the Query menu in SSMS. Figure 5-3 shows this dialog box.

Figure 5.3. Input dialog box for templates

Once you fill the values in the dialog box and click OK, the template automatically updates the CREATE STORED PROCEDURE text in the Query Editor with the data you provided in the dialog box.

Templates make it really easy to create procedures. Many templates are available for SQL Server. To access the list of templates, select Template Explorer from the View menu in SSMS. This will launch a Template Explorer window, as shown in Figure 5-4.

Figure 5.4. Template Explorer window in SSMS

Note that there are three buttons on the top of this dialog box; the first one shows SQL Server templates, the second shows Analysis Services templates, and the last one shows templates for SQL Server Compact Edition. To use a template, simply double-click it, and the template will load in a new Query Editor window.

SSMS does a great job of enumerating all the stored procedures within the database. They are all listed under the Programmability node, which is under a specific database. To modify a stored procedure, you need to issue an ALTER STORED PROCEDURE statement. To modify the GetID procedure created earlier using SSMS, select Modify from the context menu of the GetID stored procedure. This will launch a Query Editor window with the ALTER STORED PROCEDURE text in it. The actual script generated by SSMS is as follows:

USE [VetClinic]
GO
/** Object:  StoredProcedure [dbo].[GetID]    Script Date: 04/29/2009 **/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetID]
@name VARCHAR(50)
AS
BEGIN
SELECT MicroChipID FROM Pets WHERE pet_name=@name
END

SSMS saves you from a lot of typing when you are working with stored procedures.

If you navigate around SSMS and the Programmability node as described earlier, you may have noticed a System Stored Procedures folder. There are quite a number of these that all serve their own purpose within SQL Server. For example, SQL Server Agent job scheduler does not have native DDL statements for creating jobs, so it uses system stored procedures to create and manage jobs. For a complete list, see the SQL Server Books Online article called "System Stored Procedures (Transact-SQL)" at http://msdn.microsoft.com/en-us/library/ms187961.aspx.

To illustrate, let's create a function that accepts a pet weight and returns a table that lists the pets who exceed that weight and their owners. The sample script is as follows:

CREATE FUNCTION GiantPets (@minWeight INTEGER)
RETURNS @retGiantPetsTable TABLE
(
pet_name     VARCHAR(50) NOT NULL,
pet_weight    INT                   NOT NULL,
owner_name VARCHAR(50) NOT NULL
)
AS
BEGIN
     INSERT @retGiantPetsTable
     SELECT p.pet_name, p.pet_weight,o.owner_name
     FROM Pets p, Owners o
     WHERE p.pet_id=o.pet_id AND p.pet_weight > @minWeight

     RETURN
END

Functions are designed with the idea that they will send their output to a query or T-SQL statement. With the exception of scalar functions, functions are called differently than stored procedures. Since the example function returns a table, you can call it as part of a SELECT statement by referencing it in the FROM clause. For example:

SELECT * FROM GiantPets(50)

This will return the following result set:

pet_name   pet_weight      owner_name
Lady          155              Bryan
Zeus          185                Rob

SELECT GETDATE()

SELECT AVG(pet_weight) FROM Pets

SELECT IS_SRVROLEMEMBER('sysadmin')