SQL SERVER noteS......ip notes....

                               FUNCTION
CREATE FUNCTION Addition()
RETURNS Decimal(6,3)

After specifying the type of value that the function would return, you can create a body for the function. The body of a function starts with the BEGIN and ends with the END keywords. Here is an example:

CREATE FUNCTION Addition()
RETURNS Decimal(6,3)
BEGIN

END

Optionally, you can type the AS keyword before the BEGIN keyword:

CREATE FUNCTION Addition()
RETURNS Decimal(6,3)
AS
BEGIN

END

Between the BEGIN and END keywords, which is the section that represents the body of the function, you can define the assignment the function must perform. After performing this assignment, just before the END keyword, you must specify the value that the function returns. This is done by typing the RETURN keyword followed by an expression. A sample formula is:

CREATE FUNCTION Addition()
RETURNS Decimal(6,3)
AS
BEGIN
   RETURN Expression
END

Here is an example

CREATE FUNCTION GetFullName()
RETURNS varchar(100)
AS
BEGIN
RETURN 'Doe, John'
END

Here is another example:

CREATE FUNCTION CalculateWeeklySalary()
RETURNS Decimal(8, 2)
AS
BEGIN
   RETURN 880.44
END;
GO

Function Calling

After a function has been created, you can use the value it returns. Using a function is also referred to as calling it. To call a function, you must qualify its name. To do this, type the name of the database in which it was created, followed by the period operator, followed by dbo, followed by the period operator, followed by the name of the function, and its parentheses. The formula to use is:

DatabaseName.dbo.FunctionName()

Because a function returns a value, you can use that value as you see fit. For example, you can use either PRINT or SELECT to display the function's value in a query window. Here is an example that calls the above Addition() function:

PRINT Exercise.dbo.GetFullName();

As an alternative, to call a function, in the Object Explorer, right-click its name, position the mouse on Script Function As, SELECT To, and click New Query Editor Window.

Renaming a Function

To rename a function, in the Object Explorer, right-click it and click Rename. Type the desired new name and press Enter.

Modifying a Function

As mentioned already, in the body of the function, you define what the function is supposed to take care of. As a minimum, a function can return a simple number, typed on the right side of the RETURN keyword. Here is an example:

CREATE FUNCTION Addition()
RETURNS int
BEGIN
   RETURN 1
END

You can also declare new variables in the body of the function to help in carrying the assignment. A variable declared in the body of a function is referred to as a local variable. Once such a variable has been declared, it can be used like any other variable. Here is an example:

CREATE FUNCTION Addition()
RETURNS int
BEGIN
   DECLARE @Number1 int
   SET @Number1 = 588
   RETURN @Number1 + 1450
END

A Parameterized Function

To create a function that takes a parameter, specify a name and the type of value of the parameter(s) in its parentheses. Here is an example:

CREATE FUNCTION Addition(@Number1 Decimal(6,2))

When a function takes a parameter, in the body of the function, you can use the parameter as if you knew its value, as long as you respect the type of that value. Here is an example:

CREATE FUNCTION Addition(@Number1 Decimal(6,2))
RETURNS Decimal(6,2)
BEGIN
   RETURN @Number1 + 1450
END

Calling a Parameterized Function

When you call a function that takes one parameter, you must supply a value for that argument. To do this, type the value of the parameter in the parentheses of the function. Here is an example:

A Function With Various Arguments

Instead of only one parameter, you can also create a function that takes more than one parameter. In this case, separate the arguments in the parentheses of the function with a comma. Here is an example:

CREATE FUNCTION Addition(@Number1 Decimal(6,2), @Number2 Decimal(6,2))

Once again, in the body of the function, you can use the parameters as if you already knew their value. You can also declare local variables and involve them with parameters as you see fit. Here is an example:

CREATE FUNCTION Addition(@Number1 Decimal(6,2),
@Number2 Decimal(6,2))
RETURNS Decimal(6,2)
BEGIN
   DECLARE @Result Decimal(6,2)
   SET @Result = @Number1 + @Number2
   RETURN @Result
END;
GO

When calling a function that takes more than one parameter, in the parentheses of the function, provide a value for each parameter, in the exact order they appear in the parentheses of the function. Here is an example:

PRINT Variables1.dbo.Addition(1450, 228);

You can also pass the names of already declared and initialized variables. Here is an example that calls the above function:

DECLARE @Nbr1 Decimal(6,2),
       @Nbr2 Decimal(6,2)
SET @Nbr1 = 4268.55
SET @Nbr2 =26.83
SELECT @Nbr1 As First,
      @Nbr2 As Second,
      Variables1.dbo.Addition(@Nbr1, @Nbr2) AS Result

This would produce:

DROP A FUnCTIOn

Introduction

To delete a function in the Object Explorer, right-click it and click Delete. To programmatically delete a function: In a query window, type DROP FUNCTIONfollowed by the name of the function and execute the statement In the Object Explorer, right-click the name of the function, position the mouse on Script Function As, DROP To, and click New Query Editor Window Open a new query window associated with the database that contains the function. Display the Templates Explorer and expand the Function node. Drag the Drop Function node and drop it in the empty query window

           Create a view

Introduction

The formula to programmatically create a view is:

CREATE VIEW ViewName
AS
SELECT Statement

Here is an example:

USE Exercise;
GO
CREATE SCHEMA Personnel;
GO
CREATE TABLE Personnel.Employees(EmplNbr nchar(10), FirstName nvarchar(20),
LastName nvarchar(20), Salary money, FullTime bit);
GO
INSERT INTO Personnel.Employees
VALUES(N'29730', N'Philippe', N'Addy', 20.05, 1),
     (N'28084', N'Joan', N'Shepherd', 12.72, 0),
     (N'79272', N'Joshua', N'Anderson', 18.26, 0),
     (N'22803', N'Gregory', N'Swanson', 15.95, 0),
     (N'83084', N'Josephine', N'Anderson', 20.02, 1);
GO

CREATE VIEW Personnel.StaffMembers
AS
SELECT FirstName, LastName, Salary
FROM Personnel.Employees;
GO

Here is an example that includes a condition:

CREATE VIEW Personnel.GoodSalaries
AS
SELECT FirstName, LastName, Salary
FROM Personnel.Employees
WHERE Salary >= 16.00;
GO

Here is an example of a view that uses two tables:

CREATE VIEW People.ListOfMen
AS
SELECT People.Genders.Gender,
      People.Persons.FirstName, People.Persons.LastName
FROM   People.Genders INNER JOIN People.Persons
      ON People.Genders.GenderID = People.Persons.GenderID;
GO

Here is an example of a view that uses two tables:

CREATE VIEW People.ListOfMen
AS
SELECT People.Genders.Gender,
      People.Persons.FirstName, People.Persons.LastName
FROM   People.Genders INNER JOIN People.Persons
      ON People.Genders.GenderID = People.Persons.GenderID
WHERE (People.Genders.Gender = N'Male');
GO

Here is an example of a view with alias names:

CREATE VIEW dbo.MenAndWomen([First Name], [Last Name], Gender)
AS
SELECT dbo.Persons.FirstName,
      dbo.Persons.LastName,
      dbo.Genders.Gender
FROM   dbo.Genders INNER JOIN dbo.Persons
ON     dbo.Genders.GenderID = dbo.Persons.GenderID;
GO

Views and Functions

To create more complex or advanced views, you can involve functions. As always, probably the easiest functions to use are those built-in. 

If there is no built-in function that performs the operation you want, you can create your own. Here is an example:

CREATE FUNCTION Registration.GetFullName
(
@FName nvarchar(20),
@LName nvarchar(20)
)
RETURNS nvarchar(41)
AS
BEGIN
RETURN @LName + N', ' + @FName;
END
GO

 Once you have a function you want to use, you can call it in the body of your view as you judge it necessary. Here is an example:

CREATE VIEW Registration.StaffMembers
AS
SELECT Registration.GetFullName(FirstName, LastName) AS [Full Name]
FROM   Registration.Teachers;
GO

SELECT * FROM Registration.StaffMembers;
GO

This would produce:

Alter vew

Introduction

The basic formula to programmatically modify a view is:

ALTER VIEW ViewName
AS
SELECT Statement

Here is an example:

ALTER VIEW dbo.ListOfMen
AS
SELECT dbo.Persons.FirstName, dbo.Persons.LastName
FROM   dbo.Genders INNER JOIN dbo.Persons
ON     dbo.Genders.GenderID = dbo.Persons.GenderID
WHERE  (dbo.Genders.Gender = N'Male');

Drop view

Introduction

The formula to programmatically delete a view is:

DROP VIEW ViewName

Here is an example

DROP VIEW Personnel.StaffMembers;
GO

Data entry with a view

Introduction

Imagine you have a view as follows:

CREATE VIEW Personnel.EmployeesNames
AS
SELECT FirstName,
      LastName,
      LastName + N', ' + FirstName AS FullName FROM Persons;
GO

Here is an example of creating a record using a view:

INSERT INTO Personnel.EmployeesNames(FirstName, LastName)
VALUES(N'Peter', N'Justice');


Foreign key

Introduction

To create a foreign key in the SQL, the basic formula to use is:

FOREIGN KEY REFERENCES ParentTableName(ForeignKeyCcolumn)

Here is an example:

CREATE TABLE Persons
(
   PersonID int identity(1,1) PRIMARY KEY NOT NULL,
   FirstName nvarchar(20),
   LastName nvarchar(20) NOT NULL,
   GenderID int NULL FOREIGN KEY REFERENCES Genders(GenderID)
);

A Constraint on a Foreign Key

To create a foreign key as a constraint, use the CONSTRAINT keyword followed by the desired name and continue the rest as we saw above. Her is an example:

CREATE TABLE Persons
(
   PersonID int identity(1,1) PRIMARY KEY NOT NULL,
   FirstName nvarchar(20),
   LastName nvarchar(20) NOT NULL,
   GenderID int NULL CONSTRAINT FKGenders
                      FOREIGN KEY REFERENCES Genders(GenderID)
);

*	Transact-SQL: Adding a Column to a Table

 

Introduction

To add a new column to a table, follow this formula: ALTER TABLE TableName ADD ColumnName Properties Here is an example: ALTER TABLE StaffMembers ADD Address varchar(100) NULL GO When this code is executed, a new column named Address, of type varchar, with a limit of 100 characters, and that allows empty entries, would be added to the table named StaffMembers. To use sample code, first display an empty query window and display the Templates Explorer. Expand the Table node. Under Table, drag Add Column and drop it in the query window. Delete the undesired sections of code and keep only the part that deals with adding a column. Here is an example: --========================================================================== -- Add column template -- -- This template creates a table, then it adds a new column to the table. --========================================================================== USE <database, sysname, AdventureWorks> GO -- Add a new column to the table ALTER TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table> ADD <new_column_name, sysname, column3>    <new_column_datatype,, datetime>    <new_column_nullability,, NULL> GO

*	An Identity Column

 

Description

An identity column is one whose value is automatically created by the database engine when a new record is added. This makes sure that each record has a unique value for that field. To visually create an identity column, display the table in Design View. In the top section, specify the name of the column. Set its data type to an integer-based type. Usually, the data type used is int.

In the bottom section, click and expand the Identity Specification property. The first action you should take is to set its (Is Identity) property from No to Yes.

Once you have set the value of the (Is Identity) property to Yes, the first time the user performs data entry, the value of the first record would be set to 1. This characteristic is controlled by the Identity Seed property. If you want the count to start to a value other than 1, specify it on this property.

After the (Is Identity) property has been set to Yes, the SQL interpreter would increment the value of each new record by 1, which is the default. This means that the first record would have a value of 1, the second would have a value of 2, and so on. This aspect is controlled by the Identity Increment property. If you want to increment by more than that, you can change the value of the Identity Incrementproperty.

To create an identity column in Transact-SQL, after the name and data type of the column, type identity followed by parentheses. Between the parentheses, enter the seed value, followed by a comma, followed by the increment value. Here is an example:

CREATE TABLE StoreItems(
   ItemID int IDENTITY(1, 1) NOT NULL,
   Category nvarchar(50),
   ItemName nvarchar(100) NOT NULL,
   Size nvarchar(20),
   UnitPrice money);
GO

*	Transact-SQL: Deleting a Column

 

Introduction

To delete a column using code, first open or access an empty query window, and use the following formula: ALTER TABLE TableName DROP COLUMN ColumnName On the right side of the ALTER TABLE expression, type the name of the table. On the right side of the DROP COLUMN expression, enter the name of the undesired column. Here is an example: ALTER TABLE StaffMembers DROP COLUMN CurrentResidence; GO When this code is executed, the interpreter will look for a column named CurrentResidence in a table named StaffMembers of the current or selected database. If it finds that column, it will remove it from the table. Microsoft SQL Server can also generate sample code you can use to delete a column from a table. Before doing this, first display an empty query window and display the Templates Explorer. Expand the Table node. In the Table section, drag Drop Column and drop it in the query window. Delete the undesired sections of code and keep only the part that deals with adding a column. Here is an example: --============================================ -- Drop column template -- -- This template creates a table, then it   -- drops one of the columns of the table. --============================================ USE <database, sysname, AdventureWorks> GO -- Drop a column from the table ALTER TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table> DROP COLUMN <new_column_name, sysname, column3> GO

*	Transact-SQL: Renaming a Column

 

Introduction

To rename a column, first open an empty query window. In a query window, executesp_rename using the following formula: sp_rename 'TableName.ColumnName', 'NewColumnName', 'COLUMN' The sp_rename factor and the 'COLUMN' string are required. The TableName factor is the name of the table that the column belongs to. The ColumnName is the current name of the column. The NewColumnName is the desired name you want to give to the column. Here is an example: sp_rename 'StaffMembers.FullName', 'EmployeeName', 'COLUMN' GO When this code is executed, the interpreter will look for a column named FullName in the StaffMembers table of the current or selected database. If it finds that column in the table, then it renames it EmployeeName.

Creating a User

   

Introduction

 

Here is an example of creating a user:

CREATE USER [Raymond Kouma]
FOR LOGIN rkouma;
GO
USE Exercise1;
GO
GRANT CREATE FUNCTION
TO rkouma;
GO

Databases Permissions: GRANT

 

Introduction to Rights and Permissions

 

Overview

A permission is an action that a user is allowed to perform, or is prevented from performing, on a database or on one of its objects.

Granting a Permission

In order to do something on the server or one of its objects, a user must be given the permission. This is also referred to as granting a permission. To grant permissions, the account you are using must have the ability to do so. This means that, before granting permissions, you must log in with an account that has its own right permissions. You can grant permissions visually or with code.

To visually grant one or more permissions on the server, in the Object Explorer, right-click the name of the server and click Properties. In the left frame of the Server Properties dialog box, click Permissions. In the Logins or Roles list, click the name of the user. In the bottom list, use the options in the Grant column:

The basic formula to programmatically grant one or more permissions is:

GRANT Permission TO Login

You start with the GRANT keyword followed by the name of the permission. After the permission, typeTO, followed by the login name you want to grant the permission to. Here is an example that gives operez to create a database on the server:

USE master;
GO
GRANT CREATE ANY DATABASE
TO operez;
GO

If you want to grant more than one permission, separate their names with commas. Here is an example:

GRANT CREATE ANY DATABASE, SHUTDOWN
TO operez;
GO

If you want to grant the same permission(s) to more than one account, list them, separated by commas. Here is an example:

GRANT CREATE ANY DATABASE, ALTER ANY LOGIN
TO pkatts, gdmonay;
GO

The DENY Permission

   

Introduction

A permission is an action that a user is allowed to perform, or is prevented from performing, on a database or on one of its objects.

Denying a Permission

To visually deny one or more permissions, in the Object Explorer, right-click the name of the server and click Properties. In the left frame, click Permissions. In the Logins or Roles list, click the name of the user. Use the options in the Deny column.

The basic formula to programmatically deny one or more permissions is:

DENY Permission1,Permission2, Permission_n
TO Login1, Login2, Login_n

Here is an example:

DENY CREATE ANY DATABASE
TO rkouma;
GO

Databases Permissions: REVOKE

   

Introduction

A permission is an action that a user is allowed to perform, or is prevented from performing, on a database or on one of its objects.

Revoking Permissions

Revoking a permission consists of either denying a permission that was previously granted or granting a permission that was previously denied. To visually do this, open the Properties dialog box of the database (or the object) on which the permission was managed.

To programmatically revoke a permission, the formula to follow is:

REVOKE [ GRANT OPTION FOR ] <permission> [ ,...n ]  
   { TO | FROM } <database_principal> [ ,...n ]
       [ CASCADE ]
   [ AS <database_principal> ]

<permission> ::=  permission | ALL [ PRIVILEGES ]

<database_principal> ::= Database_user
   | Database_role
   | Application_role
   | Database_user_mapped_to_Windows_User
   | Database_user_mapped_to_Windows_Group
   | Database_user_mapped_to_certificate
   | Database_user_mapped_to_asymmetric_key
   | Database_user_with_no_login

Start with the REVOKE keyword followed by the permission(s). This is followed by either TO orFROM and the login name of the account whose permission must be managed. Here is an example:

/*
DENY CREATE ANY DATABASE
TO rkouma;
GO
*/

REVOKE CREATE ANY DATABASE
TO rkouma;
GO

Revoking a permission doesn't give that same permission. Imagine a user with a newly created account didn't have the permission to create new databases. If you deny that person the ability to create new databases, that denial becomes effective. If you revoke the permission, you are asking the server to restore the status of that person with regards to that particular right. That doesn't give that user the permission. The above code doesn't give the user the right to create new databases. If you want the user to have a right, you must explicitly grant the permission. Consider the following code:

REVOKE CREATE ANY DATABASE
TO rkouma;
GO

GRANT CREATE ANY DATABASE
TO rkouma;
GO

This restores the user's denial for creating new databases, then grants the permission to that user. This time, the user has the right to create new databases.

Databases Permissions: WITH GRANT

 

Introduction to Rights and Permissions

 

Overview

A permission is an action that a user is allowed to perform, or is prevented from performing, on a database or on one of its objects.

Extending Permissions

Besides granting or denying permissions to an account, you can give an account the ability to grant or deny permissions to other accounts. To do this visually, open the Database Properties for the database you want to work on. In the Users or Roles section, select the user. In the Persmissions, use the check boxes in the With Grant column.

The formula to programmatically give an account the ability to grant or deny permissions to other accounts is:

GRANT Permission1,Permission2, Permission_n
TO Login1, Login2, Login_n
WITH GRANT OPTION

This follows the same formula as the GRANT right we saw earlier. You must just add the WITH GRANT OPTION expression.

Triggers   Triggers Fundamentals   Introduction to Triggers When an action has been performed on a table, such as adding a new record, changing (editing/updating) an existing record, or deleting a (or some) record(s), the table produces a notification. We say that the table fires an event. You can use that occurring event to take some action. A trigger is an action that is performed behind-the-scenes when an event occurs on a table or a view. *Practical Learning: Introducing Triggers To better follow this lesson, you should use many user accounts as described in Lesson 1: If you are using one computer: If you are using Microsoft Windows XP or Vista, you can use one account to perform a section of an exercise, log off, then log on with a different account, and perform another section of the exercise with that other account If you are using Microsoft Windows 7 or Windows Server 2008, you can use an account to perform a section of an exercise, and log in with a different account (you don't need to log off as done in Windows XP) to perform another part of the exercise. In fact, there is a different version of this lesson for that If you are using one computer (a client workstation) to connect to Microsoft SQL Server installed in another computer (a server), you will connect using one account to perform a certain section of the exercise, then connect using another account to perform another part of the exercise If you are following the lesson in a classroom, there are a different version of this lesson for it Start the computer and log in with an account that has administrative rights Launch Microsoft SQL Server In the Authentication combo box, select Windows Authentication Click Connect Open the codes/KoloBank3.sql file (codes/KoloBank3.txt) Examine the file to see the names of schemas, tables, views, and logins To execute it, on the main menu, click Query -> Execute Close the KoloBank3 window In the Object Explorer, expand Databases Right-click KoloBank3 and click New Query Creating a Trigger You create a trigger using SQL code. Later on, we will start analyzing the code necessary to do this. To assist you with skeleton code, open a Query window. Then, in the Template Explorer, expand the Triggers node. Drag Create T-SQL Trigger (New Menu) and drop it in the window: -- ================================================ -- Template generated from Template Explorer using: -- Create Trigger (New Menu).SQL -- -- Use the Specify Values for Template Parameters -- command (Ctrl-Shift-M) to fill in the parameter -- values below. -- -- See additional Create Trigger templates for more -- examples of different Trigger statements. -- -- This block of comments will not be included in -- the definition of the function. -- ================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name,      sysname,      Trigger_Name>   ON  <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name>   AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE> AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;    -- Insert statements for trigger here END GO Executing a Trigger Unlike a stored procedure, you never have to execute a trigger. The operating system (through the object(s) event(s)) and the database engine take care of this. A trigger acts behind the scenes when the object to which it is associated fires the appropriate event. In fact, the event fires whether the object received a change or not (whether a record was created, edited, deleted, or not). Microsoft SQL Server supports three types of triggers: DML, DDL, and logon. DML Triggers   Introduction A DML trigger is a procedure that acts as a result of a data manipulation language (DML) event occurring on a table. This means that the trigger must be created in connection to a table of a non-system database. AFTER/FOR INSERT Triggers An insert trigger is a DML trigger that acts when a new record is added to its intended table. Such a trigger uses the INSERT keywork. The primary formula to create an INSERT DML trigger on a table is: CREATE TRIGGER Schema_Name.TriggerName ON TableName AFTER/FOR INSERT AS    TriggerCode The statement starts with CREATE TRIGGER. If you want to specify a schema, type it, followed by a a period and the name of the trigger. The name follows the rules we have applied so far to database objects. After specifying the name of the trigger, write ON followed by the name of the table on which the trigger will apply. Of course, the table must exist in the database. If the table belongs to a schema other than dbo, the trigger must use the same schema. Put it another way, if you precede the name of the a trigger withe a schema, the table must belong to the same schema and you must precede the name of the table with the same schema. Of course, you must have previously created the schema and the table must have been assigned to that schema. In our formula, we assume that the trigger will apply when a record has been added to the table. Therefore, you use either the AFTER INSERT or the FOR INSERT expression. To start the SQL code that constitutes the trigger, write AS, and then write your code. After creating an INSERT trigger, at the right time (when its intended event fires), it will execute. When this happens, the database engine automatically and internally creates a temporary table named inserted. This tableholds a copy of the records that were created. You can access those records if you want. *Practical Learning: Creating a DML Trigger To create a new trigger, type the following: USE KoloBank3; GO -- ======================================================== -- DML Triggers: -- Description: These triggers update the Management.DatabaseOperations -- by letting it know that a new operation has -- taken place on a table of the database table. The trigger -- also specifies the name of the employee -- who performed the operation and the time -- this occurred -- ======================================================== CREATE TRIGGER Management.ForCustomers ON Management.Customers AFTER INSERT AS BEGIN    INSERT INTO Management.DatabaseOperations    VALUES(default, N'Customers', SUSER_SNAME(),       N'Processed a deposit', GETDATE()) END GO -- ======================================================== CREATE TRIGGER Transactions.ForDeposits ON Transactions.Deposits AFTER INSERT AS BEGIN    INSERT INTO Management.DatabaseOperations    VALUES(default, N'Deposits', SUSER_SNAME(),       N'Processed a deposit', GETDATE()) END GO -- ======================================================== CREATE TRIGGER Transactions.ForWithdrawals ON Transactions.Withdrawals AFTER INSERT AS BEGIN    INSERT INTO Management.DatabaseOperations    VALUES(default, N'Withdrawals', SUSER_SNAME(),   N'Processed a withdrawal', GETDATE()) END GO -- ======================================================== CREATE TRIGGER Transactions.ForChecksCashed ON Transactions.CheckCashing AFTER INSERT AS BEGIN    INSERT INTO Management.DatabaseOperations    VALUES(default, N'CheckCashing', SUSER_SNAME(),   N'Cashed a check', GETDATE()) END GO Press F5 to execute In the Object Explorer, expand KoloBank3 and expand Tables Right-click Management.Customers and click Edit Top 200 Rows Enter the records as follows:  DateCreated: 01/12/11 AccountNumber: 288-3782-848AccountTypeID: 1CustomerName: James Carlton BrokeridgeAddress: 1022 Arlington RdCity: ArlingtonState: VAZIPCode: 22202HomePhone: (703) 645-1492WorkPhone: (703) 450-5577 Enter another record as follows:  DateCreated: 01/12/11 AccountNumber: 920-3782-493AccountTypeID: 1CustomerName: Chrissy Arlene McMahonAddress: 845 Arcadia Ave. #1512City: RockvilleState: MD ZIPCode: 20872HomePhone: (301) 684-2828WorkPhone: (301) 723-1882 Close the table In the Object Explorer, under the Tables node of KoloBank3, right-click Transactions.Deposits and click Edit Top 200 Rows Enter a record as follows:   LocationID EmployeeID CustomerID DepositDate DepositAmount Notes 1 1 1 01/12/11 250.00 Deposit for a new bank account In the Object Explorer, right-click Management.Customers and click Edit Top 200 Rows Enter a new record as follows:  DateCreated: 01/12/11 AccountNumber: 803-4654-747AccountTypeID: 2CustomerName: Herbert SpampinatoAddress: 8254 12th St. N.E.City: WashingtonState: DCZIPCode: 20164HomePhone: (202) 927-1040WorkPhone: (301) 726-8426 Close the table In the Object Explorer, right-click Transactions.Withdrawals and click Edit Top 200 Rows Enter a new record as follows:   LocationID EmployeeID CustomerID WithdrawalDate WithdrawalAmount Notes 3 5 1 01/15/11 225.00 Withdrawal from cashier 2 1 1 01/15/11 20.00 Drive-in withdrawal Close the table In the Transactions.Deposits tab, enter two new records as follows:   LocationID EmployeeID CustomerID DepositDate DepositAmount Notes 1 4 1 01/14/11 500.00 Deposit for a new bank account 1 1 3 01/16/11 735.25 Deposit for a new bank account In the Object Explorer, right-click Management.Customers and click Edit Top 200 Rows Enter a nee record as follows:  DateCreated: 01/18/11 AccountNumber: 497-4783-295AccountTypeID: 1CustomerName: Gloria Jasmine WrightAddress: 15328 Crystal St.City: HyattsvilleState: MDZIPCode: 20782HomePhone: (301) 723-5656 Close the Customers table In the Transactions.Deposits tab, create two new records as follows:   LocationID EmployeeID CustomerID DepositDate DepositAmount Notes 3 5 4 01/18/11 1450.00 Deposit for a new bank account 1 4 1 01/18/11 75.95   In the Object Explorer, right-click Management.Customers and click Edit Top 200 Rows Create a new record with the following values:  DateCreated: 1/20/11 AccountNumber: 682-3763-264AccountTypeID: 1CustomerName: Liliana Wellie OrtezAddress: 4445 Blue Oak St. #6ACity: Chevy ChaseState: MD ZIPCode: 20875HomePhone: (301) 821-4990 Close the table In the Transactions.Deposits tab, enter a new record as follows:   LocationID EmployeeID CustomerID DepositDate DepositAmount Notes 1 1 5 01/20/11 50.00 Deposit for a new bank account In the Object Explorer, right-click Transactions.Withdrawals and click Edit Top 200 Rows Create two new records as follows:   LocationID EmployeeID CustomerID WithdrawalDate WithdrawalAmount 2 1 4 01/20/11 522.75 4 2 3 01/21/11 440.00 Close the Transactions.Withdrawals table Click the first SQLQuery1.sql tab Click inside the top section of the Query window and press Ctrl + A Type the following: SELECT * FROM Management.DatabaseOperations; GO Right-click the Query window and click Execute   AFTER/FOR UPDATE Triggers Instead of record insertion time, a DML trigger can act when a record has been updated on a table. To support this operation, you can use the following formula: CREATE TRIGGER TriggerName ON TableName AFTER/FOR UPDATE AS    TriggerCode The new keyword in this formula is UPDATE. This indicates that the DML trigger will act when the record has been updated. Everything else is as described for the INSERT operator. Remember to use either AFTER UPDATEor FOR UPDATE. *Practical Learning: Updating With a DML Trigger Click the top section of the Query window and press Ctrl + A To create new triggers, type the following: USE KoloBank3; GO -- ======================================================== -- DML Triggers:Records Updates -- Description: These trigger adds a new record to the -- Management.DatabaseOperations when an existing record -- of a table has been updated. -- ======================================================== CREATE TRIGGER Management.CustomerUpdated ON Management.Customers AFTER UPDATE AS BEGIN    INSERT INTO Management.DatabaseOperations    VALUES(default, N'Customers', SUSER_SNAME(),   N'Some detail changed about a customer record.',   GETDATE()) END GO CREATE TRIGGER Transactions.DepositUpdated ON Transactions.Deposits AFTER UPDATE AS BEGIN    INSERT INTO Management.DatabaseOperations    VALUES(default, N'Deposits', SUSER_SNAME(),   N'Changed some information about a deposit of a bank account.',   GETDATE()) END GO CREATE TRIGGER Transactions.WithdrawalUpdated ON Transactions.Withdrawals AFTER UPDATE AS BEGIN    INSERT INTO Management.DatabaseOperations    VALUES(default, N'Withdrawals', SUSER_SNAME(),   N'Updated a withdrawal of a bank account.', GETDATE()) END GO Press F5 to execute In the Object Explorer, right-click Management.Customers and click Edit Top 200 Rows Locate the customer whose account number is 803-4654-747 Change the ZIP Code to 20008 Close the Management.Customers table In the Object Explorer, right-click Transactions.Deposits and click Edit Top 200 Rows Change the first two record as follows (in the first record, you will change the EmployeeID, the DepositAmount, and the Notes fields; in the second record, you will change the EmployeeID and the Notes):   DepositID LocationID EmployeeID CustomerID DepositDate DepositAmount Notes 1 1 4 1 01/12/11 650.00 There was a mistake in the amount deposited. Instead of 250.00, it was 650.00 2 1 5 2 01/14/11 500.00 Oops, the money was deposited in the wrong bank account. It has been corrected with no incident Close the table In the Object Explorer, right-click Transactions.Withdrawals and click Edit Top 200 Rows Change the records as follows (change the EmployeeID, the DepositAmount, and the Notes fields):   WithdrawalID LocationID EmployeeID CustomerID WithdrawalDate WithdrawalAmount Notes 3 2 2 4 1/20/11 522.75 The withdrawal was actually processed by 662-286. Everything else is the same. Close the table In the SQLQuery1.sql window, click inside the top area and press Ctrl + A Type the following: SELECT * FROM Management.DatabaseOperations; GO Right-click the Query window and click Execute

 

AFTER/FOR DELETE Triggers When a record has been removed from a table, you can apply a DML trigger in response. To make it possible, you can use the following formula: CREATE TRIGGER TriggerName ON TableName AFTER/FOR DELETE AS    TriggerCode This time, the formula uses the DELETE operator as in AFTER DELETE or FOR DELETE. The other things follow the same description we saw for the INSERT operator. When a DELETE trigger has acted on a table, the database engine creates a special temporary table named deleted. This table holds a copy of the records that were deleted. Eventually, if necessary, you can access this table to find out about those records. *Practical Learning: Deleting With a DML Trigger Click somewhere in the top window to give it focus, then press Ctrl + A to select everything To create a new trigger, type the following: USE KoloBank3; GO -- ======================================================== -- Description: These triggers add a new record to the -- Management.DatabaseOperations when an existing record -- of a table has been deleted. -- ======================================================== CREATE TRIGGER DepositDeleted ON Transactions.Deposits AFTER DELETE AS BEGIN    INSERT INTO Management.DatabaseOperations    VALUES(default, N'Deposits', SUSER_SNAME(),   N'Deleted a deposit from a bank account.', GETDATE()) END GO CREATE TRIGGER WithdrawalDeleted ON Transactions.Withdrawals AFTER DELETE AS BEGIN    INSERT INTO Management.DatabaseOperations    VALUES(default, N'Withdrawals', SUSER_SNAME(),   N'Deleted a withdrawal from a bank account.', GETDATE()) END GO Press F5 to execute In the Object Explorer, right-click Transactions.Deposits and click Edit Top 200 Rows To delete a record, right-click the row header of the 5th record   Click Delete   To confirm that you want to delete, click Yes Close the table Click the top section of the SQLQuery1.sql tab and press Ctrl + A Type the following: SELECT * FROM Management.DatabaseOperations; GO Right-click the Query window and click Execute   Trigger Management   Modifying a Trigger   A trigger is a database object. As such, it has a name. It can be modified. It can also be deleted. If the behavior of a trigger is not appropriate, you can change it. The formula to modify a trigger is: ALTER TRIGGER schema_name.trigger_name ON  schema_name.table_name AFTER , UPDATE> AS    statement To get skeleton code generated for you, open a Query window. In the Templates Explorer, expand the Triggers node, drag the Alter option and drop it in the Query window: --====================================== --  Alter T-SQL Trigger Template --====================================== USE <database_name, sysname, AdventureWorks> GO ALTER TRIGGER <schema_name, sysname, Sales>.<trigger_name, sysname, uStore> ON  <schema_name, sysname, Sales>.<table_name, sysname, Store> AFTER <data_modification_statements, , UPDATE> AS <T-SQL_statement,   ,   UPDATE Sales.Store   SET ModifiedDate = GETDATE()   FROM inserted WHERE inserted.CustomerID = Sales.Store.CustomerID> GO Deleting a Trigger If you do not need a trigger anymore, you can remove it. The formula to do this is: DROP TRIGGER TriggerName After the DROP TRIGGER expression, enter the name (of the trigger). Executing a Trigger As Somebody As you may know already, Microsoft SQL Server 2008 (unlike some previous versions) requires that a person logs in order to access a database or one of its objects. In the same way, as we mentioned already, when a user uses an object like a table, a view, etc, the database engine must authenticate who the user is, what permissions that user has and on what objects. Transact-SQL allows you to specify an alternate accont under which a trigger must execute. To provide this information, when creating the trigger, after the ON expression and before specifing the type of trigger, type WITH EXECUTE AS followed by the login name of the alternate account you want to use. The formula to follow: ALTER TRIGGER schema_name.trigger_name ON  schema_name.table_name WITH EXECUTE AS LoginName AFTER , UPDATE> AS    statement Here us an example: CREATE TRIGGER Management.WithCustomers ON Management.Customers WITH EXECUTE AS N'Pat Kat' AFTER INSERT AS BEGIN    INSERT INTO Management.DatabaseOperations    VALUES(default, N'Customers', SUSER_SNAME(),       N'Processed a deposit', GETDATE()); END GO As you can imagine, this solution allows you to let a user with more restrictive permissions to run a trigger on behalf of a user with less restrictive rights. Characteristics of DML Triggers   Introduction Although we created only one trigger for a table so far, you can go farther than that: You can create many DML triggers (using different names, of course) that perform the same action on a table. This means that you can create many INSERT triggers (or many UPDATE triggers or many DELETE triggers) that act on the same table and that target the same action You can create different triggers that act on a table DML triggers present many other characteristics. DML Triggers and Constraints You know that, to assist a user with data entry, you can specify that a column would allow or not allow null values. If a column is marked as NOT NULL, during data entry, if the user does not or cannot provide a value for that column, the record cannot be created. If you create a DML trigger that must act against that table, if the nullity rule is violated, the trigger will not run. You know that you can create a check constraint on a table to make sure that every new record, or a record that is being edited, follows a certain rule. You know that if the record does not abide by that rule, the record will not be created or changed. If a DML trigger is supposed to act on the table and if this rule is not respected, the trigger would fail. One of the limitations of a check constraint is that it applies only to the table that owns it. A DML trigger can be created to perform a check constraint on more than one table. This provides its advantage over the normal check constraint. You probably know already about data relationships and referential integrity. This makes sure that, when a record is edited in a parent table, the change is also made on the child table. This also means that the integrity is applied to more than one table. When a DML trigger runs, if a referential rule is violated, the trigger, which also checks the referential integrity, fails. Instead of DML Triggers   Introduction Consider the following tables in a database: CREATE DATABASE SmallBusiness; GO USE SmallBusiness; GO CREATE TABLE Customers (    CustomerID int identity(1, 1) primary key not null,    AccountNumber nchar(10),    FullName nvarchar(50) ); GO CREATE TABLE Management.DatabaseOperations (    EmployeeName nvarchar(50),    ActionPerformed nvarchar(50),    TimePerformed time ); GO From what we have seen so far, when a user opens a table or a view to perform data entry, when a new record has been created, the table or view fires an event. We saw that, using a DML trigger, you can make a notification. For example, you can fill out a log to keep track of the changes. By default, when a record is submitted, it gets saved. In some cases, when a user has opened a table and tried to make a change, such as adding a new record, editing an existing record, or deleting a record, instead of accepting the change, you can dismiss it. You can then use a DML trigger to make a note. This is the basis of another category of DML triggers: an "instead of" trigger. Creating an INSTEAD OF Trigger While an AFTER/FOR trigger acts on a table after the action has occurred, you may want to do something before the event fires. For example, you may want to prevent the user from adding a new record on a table, or from changing an existing record, or from deleting a record. Of course, it is better to take care of this before the action is performed. One way you can do this is by creating an "instead of" trigger. While an AFTER trigger can be applied to a table only, an "instead of" trigger can be associated with either a table or a view. Therefore, to create an "instead of" trigger, use the following formula: CREATE TRIGGER TriggerName ON TableOrView INSTEAD OF INSERT/UPDATE/DELETE AS    TriggerCode You start with the CREATE TRIGGER expression followed by a name for the trigger. After the name of the trigger, type ON followed by the name of either a table or a view on which the trigger will act. From our review of the AFTER trigger, the new expression here is INSTEAD OF. This expression is followed by the type of operation to perform: If you want to catch the creation of a new record, use the INSERT operator If you want to catch the editing operation of an existing record, use the UPDATE operator If you want to catch the removal of a record, use the DELETE operator To start the triggering code, type AS and write the desired code. If you use the INSTEAD OF expression, the trigger starts when the table or view is opened but before a change has taken place. The difference with the AFTER trigger is that, this time, you can perform some action(s) before the change is made on the table or view. This also implies that, if the code of the trigger is to create a new record, at this time, the record doest not yet exist, which means you cannot catch that record. At this time also, you can prevent the record from being created (since it has not yet been created anyway). For example, the following code will not accept that a new record be added to the table: USE SmallBusiness; GO CREATE TRIGGER CreateCustomer ON Customers INSTEAD OF INSERT AS    BEGIN        INSERT INTO Management.DatabaseOperations        VALUES(SUSER_SNAME(),       N'Attempt to create new record', GETDATE())    END GO If you want to get a copy of the record that was affected by the event, you can access it from the inserted (for anINSERT or UPDATE trigger) or from the deleted (for a DELETE) trigger. Here is an example: USE SmallBusiness; GO DROP TRIGGER CreateCustomer; GO CREATE TRIGGER CreateCustomer ON Customers INSTEAD OF INSERT AS BEGIN    INSERT INTO Customers    SELECT AccountNumber, FullName FROM inserted END GO *Practical Learning: Creating INSTEAD OF Triggers Click somewhere in the top section of the SQLQuery1.sql window and press Ctrl + A to select everything To create a new trigger, type the following code: USE KoloBank3; GO -- ======================================================== -- This trigger acts on a view to update the -- Management.DatabaseOperations to let it know that an -- attempt was made to create a new room -- ======================================================== CREATE TRIGGER Transactions.AttemptedDeposit ON Transactions.DepositsSummary INSTEAD OF INSERT AS BEGIN    INSERT INTO Management.DatabaseOperations    VALUES(N'View', N'DepositsSummary', SUSER_SNAME(),   N'Attempted to make a new deposit.', GETDATE()) END GO -- ======================================================== -- This trigger acts on a view to update the -- Management.DatabaseOperations to let it know that an -- attempt was made to create a new room -- ======================================================== CREATE TRIGGER Transactions.AttemptedWithdrawal ON Transactions.WithdrawalsSummary INSTEAD OF INSERT AS BEGIN    INSERT INTO Management.DatabaseOperations    VALUES(N'View', N'WithdrawalsSummary', SUSER_SNAME(),   N'An attempt was made to withdraw money.', GETDATE()) END GO Press F5 to execute In the Object Explorer, under KoloBank3, expand Views Right-click Transactions.DepositsSummary and click Edit Top 200 Rows Create a new record as follows:   LocationID EmployeeID CustomerID DepositDate DepositAmount 1 1 2 01/22/11 137.85 Close the table In the Object Explorer, under the Views node of KoloBank3, right-click Transactions.WithdrawalsSummary and click Edit Top 200 Rows Create a new record as follows:   LocationID EmployeeID CustomerID WithdrawalDate WithdrawalAmount 4 2 4 01/24/11 744.25 Close the table Click the top section of the SQLQuery1.sql tab and press Ctrl + A Type the following: SELECT * FROM Management.DatabaseOperations; GO Right-click the Query window and click Execute   Close the Query window When asked whether you want to save, click No Characteristics of INSTEAD OF Triggers An AFTER/FOR and an INSTEAD OF triggers have many differences. For example: If a table has a relationship to another table and the referential integrity on that relationship is marked with eitherON DELETE or ON UPDATE, an INSTEAD OF UPDATE or an INSTEAD OF DELETE trigger cannot act on that table You can create only one type of INSTEAD OF trigger for each table. For example, a table cannot have more than one INSTEAD OF INSERT trigger DDL Triggers   Introduction You probably know that the creation of a database uses a Data Definition Language (DDL) command. You may also know another example of a DDL command that involves creating a table. Each one of these creation operations fires an event. A DDL trigger is a trigger that acts when a certain type of DDL event fires. These include the creation, modification, and removal of an object, not its records. This is the primary difference with a DML trigger that fires when a record is added or acted upon. A DDL trigger gives you the opportunity to do some administrative work in response to the event. For example, you can get a notification, or notify someone else using an automatically generated email, that an object (and what object) has been created, or you can use a DDL trigger to discard the operation. Creating a DDL Trigger You create a DDL trigger using code. The basic formula is: CREATE TRIGGER TriggerName ON DATABASE/ALL SERVER FOR/AFTER WhatEvent AS    TriggerCode You start a DDL trigger with the CREATE TRIGGER expression followed by a name for the new trigger. The name follows the same rules we have applied to objects so far. After the name of the trigger, type the ON keyword: If you want the trigger to act on the current database, type DATABASE. When the intended event occurs on the current database, the trigger will execute If you want the trigger to act on the server, follow the ON operator with ALL SERVER. In this case, when the intended event occurs on any part of the server, the trigger executes After specifying the object (the whole server or only the current database) on which the trigger will act, type eitherFOR or AFTER. This is followed by the event against which the trigger will act. As mentioned already, the events are DDL commands. To specify the event, use the formula of the command with the words separated by an underscore. For example, if you want the trigger to act when a CREATE TABLE command is executed, specify the event asCREATE_TABLE. After specifying the event that will fire, type AS followed by the normal code of the trigger. Here is an example that makes a note and adds it (the note) to a table when a new table has been created: USE SmallBusiness; GO CREATE TRIGGER LogNewTableCreation ON DATABASE FOR CREATE_TABLE AS BEGIN    INSERT INTO Management.DatabaseOperations    VALUES(SUSER_SNAME(),   N'A new table was created', GETDATE()) END GO Whenever a new table is created in the current database, the trigger runs, gets the name of the user who created the table, the date and time the table was created, and a small message. These pieces of information are then stored in a log table. As mentioned for DML triggers, you manage DDL triggers by modifying or deleting them. These are done using the same description we saw for DML triggers. *Practical Learning: Ending the Lesson Close Microsoft SQL Server Re-start Microsoft SQL Server and log in with an account that has administrative rights In the Object Explorer, expand Databases Right-click KoloBank3 and click Delete In the Delete Object dialog box, click OK Exercises     Lesson Summary Questions What other database object can a trigger be compared to? Variable Table view Index Stored Procedure How or when does a trigger get executed? When an event occurs By calling EXECUTE followed by the name of the trigger When a table is created When a SELECT operation is launched By setting its name ON What statements cause a DML trigger to execute on a table or view (Select 3)? SELECT INSERT UPDATE CREATE DELETE What statements cause a DDL trigger to execute on a table or view (Select 3)? CREATE SELECT ALTER DROP DELETE What are the two means of creating triggers (Select 2)? In the Object Explorer, in the desired database, right-click Database Triggers and click New Trigger... Using Transact-SQL Using the .NET Framework Trough the CTGCMD utility Using a view Answers Answers Wrong Anwer Wrong Anwer Wrong Anwer Wrong Anwer Right Anwer Answers Right Anwer Wrong Anwer Wrong Anwer Wrong Anwer Wrong Anwer Answers Wrong Anwer Right Anwer Right Anwer Wrong Anwer Right Anwer Answers Right Anwer Wrong Anwer Right Anwer Right Anwer Wrong Anwer Answers Wrong Anwer Right Anwer Right Anwer Wrong Anwer Wrong Answer

Schemas

   

Introduction

A schema is an object that contains other objects. Before using it, you must create it or you can use an existing schema. There are two types of schemas you can use, those built-in and those you create. When MicrosoftSQL Server is installed, it also creates a few schemas. One of the schemas is named sys. Another is called dbo.

The sys schema contains a list of some of the objects that exist in your database 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 list using the same name you gave it.

To access the schemas of a database, in the Object Explorer, expand the Databases node, expand the database that will hold or own the schema, and expand the Security node.

To visually create a schema, right-click Schemas and click New Schema...

This would open the Schema - New dialog box. In the Schema Name text box, enter a one-word name. Here is an example:

After providing a name, you can click OK.

The formula to programmatically create a schema is:

CREATE SCHEMA schema_name_clause [ <schema_element> [ ...n ] ]

<schema_name_clause> ::=
   {
   schema_name
   | AUTHORIZATION owner_name
   | schema_name AUTHORIZATION owner_name
   }

<schema_element> ::=
   {
       table_definition | view_definition | grant_statement |
       revoke_statement | deny_statement
   }

The most important part is:

CREATE SCHEMA schema_name_clause [ <schema_element> [ ...n ] ]

In this case, start with the CREATE SCHEMA expression and add a name to it. Here is an example:

1> CREATE SCHEMA PrivateListing;
2> GO
1>

The other parts deal with issues we have not studied yet.

Accessing an Object From a Schema

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

As mentioned already, when Microsoft SQL Server is installed, it creates a schema named dbo. This is probably the most common schema you will use. In fact, if you don't create a schema in a database, the dbo schema is the default and you can apply it to any object in your database.

Introduction to Records    Introduction The records of a database are stored in tables. To visually add a record to a table, in the Object Explorer, expand the database and the Tables node. Right-click the table and click Edit Top 200 Rows. If no record exists in the table, it would appear with an empty row of cells marked NULL: * To perform data entry on a table, you can click in a field and type the appropriate value. To programmatically perform data entry, you use a Data Definition Language (DDL) command known as INSERT. The DDL command to perform data entry is INSERT combined withVALUES. The primary statement uses the following syntax: INSERT TableName VALUES(Column1, Column2, Column_n); An alternative is to add the INTO keyword after the INSERT keyword: INSERT INTO TableName VALUES(Column1, Column2, Column_n) The TableName factor must be a valid name of an existing table in the database you are using. The VALUES keyword is followed by parentheses. In the parentheses, type the desired values: If the column is a BIT data type, you must specify one of its values as 0 or 1. If the column is an integer type, provide a valid natural number without the decimal separator. If the column is a decimal type, type the value with its character separator If the column is a time or a date type, provide a valid date Adjacent Data Entry To perform adjacent data entry, you must follow the sequence of fields of the table. Here is an example: USE Exercise; Go CREATE TABLE Employees (    EmployeeNumber nchar(10),    EmployeeName nvarchar(50),    DateHired date,    HourlySalary money ); GO INSERT INTO Employees VALUES(N'593705', N'Frank Somah', N'20061004', 26.15); GO In the same way, you can create different INSERT sections for each record. Here is an example: USE Exercise; Go INSERT INTO Employees VALUES(N'204815', N'Jeanne Swanson', N'19980802', 18.48); GO INSERT INTO Employees VALUES(N'824460', N'Ponce Valley', N'20041208', 22.25); GO INSERT INTO Employees VALUES(N'495007', N'Gina Sow', N'20000622', 16.85); GO Instead of writing the INSERT expression for each record, you can write it once, followed by VALUES. Then enter each record in its parentheses. The parentheses are separated by commas. Here is an example: USE Exercise; Go INSERT INTO Employees VALUES(N'595002', N'John Meah', N'20000212', 32.25),      (N'928375', N'Chuck Stansil', N'20080628', 20.05),      (N'792764', N'Orlando Perez', N'20000616', 12.95),      (N'290024', N'Anne Nguyen', N'20090428', 25.75); GO Random Data Entry To perform data entry in an order of your choice, provide a list of the fields of the table. Here is an example: USE Exercise; Go CREATE TABLE Employees (    EmployeeNumber nchar(10),    EmployeeName nvarchar(50),    DateHired date,    HourlySalary money ); GO INSERT INTO Employees(EmployeeNumber, EmployeeName, DateHired, HourlySalary) VALUES(N'927957', N'Helen Gooding', N'19961220', 22.65); GO In the same way, you can create different INSERT sections for each record and each INSERTexpression can have its own list of columns. Here are examples: USE Exercise; Go INSERT INTO Employees(DateHired, EmployeeName, EmployeeNumber, HourlySalary) VALUES(N'20081028', N'June Santos', N'729475', 24.85); GO INSERT INTO Employees(EmployeeName, EmployeeNumber, DateHired) VALUES(N'Ann Pulley', N'300293', N'20020520'); GO INSERT INTO Employees(EmployeeName, EmployeeNumber, DateHired) VALUES(N'Annie Pastore', N'972405', N'19941122'); GO INSERT INTO Employees(EmployeeNumber, HourlySalary) VALUES(N'490007', 12.95); GO If you want to create a series of records that use the same sequence of fields, write theINSERT keyword or INSERT INTO expression, followed by the name of the table, followed by parentheses that contain the list of fields, and followed by  VALUES. Then enter each record in its parentheses. The parentheses are separated by commas. Here is an example: USE Exercise; Go INSERT INTO Employees(EmployeeName, EmployeeNumber, DateHired) VALUES(N'Clarice Simms', N'971403', N'20011112'),      (N'Paul Handsome', N'720947', N'20000802'),      (N'Gina Palau', N'247903', N'20080612'); GO INSERT INTO Employees(DateHired, EmployeeName, EmployeeNumber, HourlySalary) VALUES(N'20091124', N'Arnold Futah', N'222475', 22.75); GO Outputting the Insertion Result In the techniques we have used so far, when or if the records have been added to a table, whether the operation was successful or not, we had no way of finding out. One way you can get this information is to store the inserted records in another table. To support this, Transact-SQL provides the OUTPUT operator. The formula to use it is: INSERT INTO TableName OUTPUT INSERTED.Columns VALUES(Value_1, Value_2, Value_X) You start with the normal record insertion with the INSERT INTO TableName expression. This is followed by the OUTPUT operator followed by the INSERTED operator and a period. If you are adding a value for each record, follow the period with *. The statement continues with the VALUES operator that is followed by parentheses in which you list the values to be added to the table. Here is an example: USE VideoCollection; GO CREATE TABLE Videos ( Title nvarchar(50), Director nvarchar(50), WideScreen bit, Rating nchar(10), YearReleased int ) GO INSERT INTO Videos OUTPUT inserted.* VALUES(N'War of the Roses (The)', N'Dany de Vito', 0, N'R', 2001),      (N'Memoirs of a Geisha', N'Rob Marshall', 1, N'PG-13', 2006),      (N'Last Castle (The)', N'Rod Lurie', 1, N'', 2001),      (N'Sneakers', N'Phil Alden Robinson', 1, N'PG-13', 2003); GO When this statement executes, if you are working in the Microsoft SQL Server Management Studio, the lower part would display a list of the records that were added: If you use the above formula, when you close the database, the reference is lost. If you want to store the list of newly created in a table, on the right side of the INSERTED operator and its period, type INTO followed by the name of the table that will receive the values. The table must have been created; that is, it must exist at the time this inserted operation is taking place. Here is an example: USE VideoCollection; GO CREATE TABLE Archives ( Title nvarchar(50), Director nvarchar(50), WideScreen bit, Rating nchar(10), YearReleased int ) GO INSERT INTO Videos OUTPUT inserted.* INTO Archives VALUES(N'Two for the Money', N'D.J. Caruso', 1, N'R', 2006),      (N'Wall Street', N'Oliver Stone', 0, N'R', 2000); GO In this case, a copy of the newly created records would be stored in the indication table. The above techniques assume that you are adding a complete record; that is, you are providing a value for each column of the table. We already saw that if you want to provide values for only some columns, after the name of the table, provide the list of columns in parentheses. To get the list of newly inserted records, after the OUTPUT keyword, typeINSERTED followed by a period and followed by the name of the first column. Do this for each column. The formula to use is: INSERT INTO TableName(Column_1, Column_2, Column_X) OUTPUT INSERTED.Column_1, INSERTED.Column_2, INSERTED.Column_X VALUES(Value_1, Value_2, Value_X) Of course, you can list the columns in an order of your choice, as long as both the TableNameand the OUTPUT section use the exact same order. Here is an example: USE VideoCollection; GO INSERT INTO Videos(Director, Rating, Title) OUTPUT inserted.Director, inserted.Rating, inserted.Title VALUES(N'Jonathan Lynn', N'R', N'Distinguished Gentleman (The)'),      (N'Paul Anderson', N'R', N'Soldier'); GO In this case, when the statement has executed, the result would display in the lower portion of the Microsoft SQL Server Management Studio. If you want to store the result in a table, use the following formula INSERT INTO TableName(Column_1, Column_2, Column_X) OUTPUT INSERTED.Column_1, INSERTED.Column_2, INSERTED.Column_X INTO TargetTable VALUES(Value_1, Value_2, Value_X) Here is an example: USE VideoCollection; GO CREATE TABLE Entertainment ( Title nvarchar(50), Director nvarchar(50) ) GO INSERT INTO Videos(Title, Director) OUTPUT inserted.Title, inserted.Director INTO Entertainment VALUES(N'Michael Jackson Live in Bucharest', N'Andy Morahan'),      (N'Outfoxed', N'Robert Greenwald'); GO Assistance With Data Entry: Using Expressions   Introduction There are various ways you can assist the user with data entry. Besides using a function, you can create an expression using operators such as those we reviewed in lessons 3 and 5. You can create an expression when creating a table, whether in the Table window or using SQL in a query window. Visually Creating an Expression To create an expression when visually creating a table, in the top section, specify the column's name (only the column name is important). In the bottom section, expand the Computed Column Specification field and, in its (Formula) field, enter the desired expression. Here is an example: Creating a SQL Expression You can also create an expression in SQL expression you are using to create a table. To do this, in the placeholder of the column, enter the name of the column, followed by AS, and followed by the desired expression. Here is an example: CREATE TABLE Circle (    CircleID int identity(1,1) NOT NULL,    Radius decimal(8, 3) NOT NULL,    Area AS Radius * Radius * PI() ); GO   Using an Expression During Data Entry When performing data entry, you must not provide a value for a column that has an expression; the SQL interpreter would provide the value automatically. Here is an example of entering data for the above Circle table: INSERT INTO Circle(Radius) VALUES(46.82); GO INSERT INTO Circle(Radius) VALUES(8.15); GO INSERT INTO Circle(Radius) VALUES(122.57); GO

 

Assistance with Data Entry: The Nullity of a Field   Introduction During data entry, users of your database will face fields that expect data. Sometimes, for one reason or another, data will not be available for a particular field. An example would be an MI (middle initial) field: some people have a middle initial, some others either don't have it or would not (or cannot) provide it. This aspect can occur for any field of your table. Therefore, you should think of a way to deal with it. A field is referred to as null when no data entry has been made to it: Saying that a field is null doesn't mean that it contains 0 because 0 is a value Saying that a field is null doesn't mean that it is empty. A field being empty could mean that the user had deleted its content or that the field itself would not accept what the user was trying to enter into that field, but an empty field can have a value A field is referred to as null if there is no way of determining the value of its content (in reality, the computer, that is, the operating system, has its own internal mechanism of verifying the value of a field) or its value is simply unknown. As you can imagine, it is not a good idea to have a null field in your table. As a database developer, it is your responsibility to always know with certainty the value held by each field of your table. A field is referred to as required if the user must provide a value for it before moving to another record. In other words, the field cannot be left empty during data entry. To solve the problem of null and required fields, Microsoft SQL Server proposes one of two options: allow or not allow null values on a field. For a typical table, there are pieces of information that the user should make sure to enter; otherwise, the data entry would not be validated. To make sure the user always fills out a certain field before moving to the next field, that is, to require the value, if you are visually creating the table, clear the Allow Nulls check box for the field. On the other hand, if the value of a field is not particularly important, for example if you don't intend to involve that value in an algebraic operation, check its Allow Nulls check box. NULL or NOT NULL? If creating a table using SQL, to specify that it can allow null values, type NULL on the right side of the column. To specify that the values of the column are required, on the right side, typeNOT NULL. If you don't specify NULL or NOT NULL, the column will be created as NULL. Here are examples: CREATE TABLE Persons (    FirstName varchar(20) NULL,    LastName varchar(20) NOT NULL,    Gender smallint ); GO If the table was already created and it holds some values already, you cannot set the Allow Nulls option on columns that don't have values. After specify that a column would NOT allow NULL values, if the user tries creating a record but omits to create a value for the column, an error would display. Here is an example: This error message box indicates that the user attempted to submit a null value for a column. To cancel the action, you can press Esc. *Practical Learning: Applying Fields Nullity In the Object Explorer, right-click Countries in the WorldStatistics node and click Modify Apply the nullity of fields as follows:   Save the table Assistance with Data Entry: The Default Value of a Column   Introduction Sometimes most records under a certain column may hold the same value although just a few would be different. For example, if a school is using a database to register its students, all of them are more likely to be from the same state. In such a case, you can assist the user by automatically providing a value for that column. The user would then simply accept the value and change it only in the rare cases where the value happen to be different. To assist the user with this common value, you create what is referred to as a default value. Visually Creating a Default Value You can create a default value of a column when creating a table. To specify the default value of a column, in the top section, click the column. In the bottom section, click Default Value or Binding, type the desired value following the rules of the column's data type: It the Data Type is Intructions Text-based (char,varchar, text, and their variants) Enter the value in single-quotes Numeric-based Enter the value as a number but following the rules of the data type. For example, if you enter a value higher than 255 for a tinyint, you would receive an error Date or Time Enter the date as either MM/DD/YYYY or YYYY/MM/DD. You can optionally include the date in single-quotes. Enter the time following the rules set in the Control Panel (Regional Settings). Bit Enter True or False   Programmatically Creating a Default Value To specify the default value in a SQL statement, when creating the column, after specifying the other pieces of information of the column, type DEFAULT followed by an empty space and followed by the desired value. Here are examples: CREATE TABLE Employees (    FullName VARCHAR(50),    Address VARCHAR(80),    City VARCHAR(40),    State VARCHAR(40) DEFAULT 'NSW',    PostalCode VARCHAR(4) DEFAULT '2000',    Country VARCHAR(20) DEFAULT 'Australia' ); GO After creating the table, the user does not have to provide a value for a column that has a default. If the user does not provide the value, the default would be used when the record is saved. * If the user provides a value for a column that has a default value and then deletes the value, the default value rule would not apply anymore: The field would simply become empty   *Practical Learning: Assigning a Default Value to a Column Display the PowerShell window To change the database, type the following and press Enter at the end:   USE CeilInn1; GO To create a new table whose columns have default values, type the following and press Enter at the end:   CREATE TABLE Rooms (    RoomNumber nvarchar(10),    RoomType nvarchar(20) default N'Bedroom',    BedType nvarchar(40) default N'Queen',    Rate money default 75.85,    Available bit default 0 ); GO To perform data entry on the new table, type the following and press Enter at the end:   INSERT INTO Rooms(RoomNumber) VALUES(104); GO To add another record to the new table, type the following:   INSERT INTO Rooms(RoomNumber, BedType, Rate, Available)           VALUES(105, N'King', 85.75, 1), (106, N'King', 85.75, 1) GO To add another record, type the following:   INSERT INTO Rooms(RoomNumber, Available) VALUES(107, 1) GO To add another record, type the following:   INSERT INTO Rooms(RoomNumber, BedType, Rate) VALUES(108, N'King', 85.75) GO To add another record, type the following:   INSERT INTO Rooms(RoomNumber, Available) VALUES(109, 1) GO To add one more record, type the following:   INSERT INTO Rooms(RoomNumber, RoomType, BedType, Rate, Available)   VALUES(110, N'Conference', N'', 450.00, 1) GO Return to Microsoft SQL Server Management Studio Assistance with Data Entry: Identity Columns   Introduction One of the goals of a good table is to be able to uniquely identity each record. In most cases, the database engine should not confuse two records. Consider the following table: Category Item Name Size Unit Price Women Long-sleeve jersey dress Large 39.95 Boys Iron-Free Pleated Khaki Pants S 39.95 Men Striped long-sleeve shirt Large 59.60 Women Long-sleeve jersey dress Large 45.95 Girls Shoulder handbag   45.00 Women Continental skirt Petite 39.95 Imagine that you want to change the value of an item named Long-sleeve jersey dress. Because you must find the item programmatically, you can start looking for an item with that name. This table happens to have two items with that name. You may then decide to look for an item using its category. In the Category column, there are too many items named Women. In the same way, there are too many records that have a Large value in the Size column, same thing problem in the Unit Price column. This means that you don't have a good criterion you can use to isolate the record whose Item Name is Long-sleeve shirt. To solve the problem of uniquely identifying a record, you can create a particular column whose main purpose is to distinguish one record from another. To assist you with this, the SQL allows you to create a column whose data type is an integer type but the user doesn't have to enter data for that column. A value would automatically be entered into the field when a new record is created. This type of column is called an identity column. You cannot create an identity column one an existing table, only on a new table. Visually Creating an Identity Column To create an identity column, if you are visually working in the design view of the table, in the top section, specify the name of the column. By tradition, the name of this column resembles that of the table but in singular. Also, by habit, the name of the column ends with _id, Id, or ID. After specifying the name of the column, set its data type to an integer-based type. Usually, the data type used is int. In the bottom section, click and expand the Identity Specificationproperty. The first action you should take is to set its (Is Identity) property from No to Yes. Once you have set the value of the (Is Identity) property to Yes, the first time the user performs data entry, the value of the first record would be set to 1. This characteristic is controlled by the Identity Seed property. If you want the count to start to a value other than 1, specify it on this property. After the (Is Identity) property has been set to Yes, the SQL interpreter would increment the value of each new record by 1, which is the default. This means that the first record would have a value of 1, the second would have a value of 2, and so on. This aspect is controlled by theIdentity Increment property. If you want to increment by more than that, you can change the value of the Identity Increment property. *Practical Learning: Creating an Identity Column In the Object Explorer, under WorldStatistics, right-click Tables and click New Table... Set the name of the column to ContinentID and press Tab Set its data type to int and press F6. In the lower section of the table, expand Identity Specification and double-click (Is Identity) to set its value to Yes Complete the table as follows:   Column Name Data Type Allow Nulls ContinentID     Continent varchar(80) Unchecked Area bigint   Population bigint   Save the table as Continents Creating an Identity Column Using SQL If you are programmatically creating a column, to indicate that it would be used as an identity column after its name and data type, type identity followed by parentheses. Between the parentheses, enter the seed value, followed by a comma, followed by the increment value. Here is an example: CREATE TABLE StoreItems( ItemID int IDENTITY(1, 1) NOT NULL, Category varchar(50), [Item Name] varchar(100) NOT NULL, Size varchar(20), [Unit Price] money); GO *Practical Learning: Creating an Identity Column Using SQL Display the PowerShell window Type the following:   USE CeilInn1; GO To create a table with an identity column, type the following and press Enter after each line:   DROP TABLE Rooms; GO CREATE TABLE Rooms (    RoomID int identity(1, 1) NOT NULL,    RoomNumber nvarchar(10),    RoomType nvarchar(20) default N'Bedroom',    BedType nvarchar(40) default N'Queen',    Rate money default 75.85,    Available bit default 0 ); GO To perform data entry on the new table, type the following and press Enter at the end:   INSERT INTO Rooms(RoomNumber) VALUES(104); GO To add another record to the new table, type the following:   INSERT INTO Rooms(RoomNumber, BedType, Rate, Available)           VALUES(105, N'King', 85.75, 1), (106, N'King', 85.75, 1) GO To add another record, type the following:   INSERT INTO Rooms(RoomNumber, Available) VALUES(107, 1) GO To add another record, type the following:   INSERT INTO Rooms(RoomNumber, BedType, Rate) VALUES(108, N'King', 85.75) GO To add another record, type the following:   INSERT INTO Rooms(RoomNumber, Available) VALUES(109, 1) GO To add one more record, type the following:   INSERT INTO Rooms(RoomNumber, RoomType, BedType, Rate, Available)   VALUES(110, N'Conference', N'', 450.00, 1) GO Return to Microsoft SQL Server Management Studio Assistance with Data Entry: The Uniqueness of Records   Introduction One of the primary concerns of records is their uniqueness. In a professional database, you usually want to make sure that each record on a table is unique. Microsoft SQL Server provides many means of taking care of this. These include the identity column, the primary key, and the indexes. We will review these issues in later lessons. Still, one way to do this is to apply a uniqueness rule on a column. Creating a Uniqueness Rule To assist you with creating a columns whose values will be distinguishable, Transact-SQL provides the UNIQUE operator. To apply it on a column, after the data type, type UNIQUE. Here is an example: USE Exercise; GO CREATE TABLE Students (    StudentNumber int UNIQUE,    FirstName nvarchar(50),    LastName nvarchar(50) NOT NULL ); GO When a column has been marked as unique, during data entry, the user must provide a unique value for each new record created. If an existing value is assigned to the column, this would produce an error: USE Exercise; GO CREATE TABLE Students (    StudentNumber int UNIQUE,    FirstName nvarchar(50),    LastName nvarchar(50) NOT NULL ); GO INSERT INTO Students VALUES(24880, N'John', N'Scheels'),      (92846, N'Rénée', N'Almonds'),      (47196, N'Peter', N'Sansen'),      (92846, N'Daly', N'Camara'),      (36904, N'Peter', N'Sansen'); GO By the time the fourth record is entered, since it uses a student number that exists already, the database engine would produce an error: Msg 2627, Level 14, State 1, Line 2 Violation of UNIQUE KEY constraint 'UQ__Students__DD81BF6C145C0A3F'. Cannot insert duplicate key in object 'dbo.Students'. The statement has been terminated. *Practical Learning: Applying Uniqueness to a Column In the PowerShell window, to create a new table that has a uniqueness rule on a column, type the following:   CREATE TABLE Customers (    CustomerID int identity(1, 1) NOT NULL,    AccountNumber nchar(10) UNIQUE,    FullName nvarchar(50) ); GO To perform data entry on the table, type the following and press Enter at the end of each line:   INSERT INTO Customers(AccountNumber, FullName)       VALUES(395805, N'Ann Zeke'),             (628475, N'Peter Dokta'),             (860042, N'Joan Summs') GO To try adding another record to the table, type the following and press Enter at the end of each line:   USE CeilInn1; GO INSERT INTO Customers(AccountNumber, FullName)       VALUES(628475, N'James Roberts') GO Notice that you receive an error Assistance With Data Entry: Check Constraints   Introduction When performing data entry, in some columns, even after indicating the types of values you expect the user to provide for a certain column, you may want to restrict a range of values that are allowed. In the same way, you can create a rule that must be respected on a combination of columns before the record can be created. For example, you can ask the database engine to check that at least one of two columns received a value. For example, on a table that holds information about customers, you can ask the database engine to check that, for each record, either the phone number or the email address of the customer is entered. The ability to verify that one or more rules are respected on a table is called a check constraint. A check constraint is a Boolean operation performed by the SQL interpreter. The interpreter examines a value that has just been provided for a column. If the value is appropriate: The constraint produces TRUE The value gets accepted The value is assigned to the column If the value is not appropriate: The constraint produces FALSE The value gets rejected The value is not assigned to the column You create a check constraint at the time you are creating a table. Visually Creating a Check Constraint To create a check constraint, when creating a table, right-click anywhere in (even outside) the table and click Check Constraints... This would open the Check Constraints dialog box. From that window, you can click Add. Because a constraint is an object, you must provide a name for it. The most important piece of information that a check constraint should hold is the mechanism it would use to check its values. This is provided as an expression. Therefore, to create a constraint, you can click Expression and click its ellipsis button. This would open the Check Constraint Expression dialog box. To create the expression, first type the name of the column on which the constraint will apply, followed by parentheses. In the parentheses, use the arithmetic and/or SQL operators we studied already. Here is an example that will check that a new value specified for the Student Number is greater than 1000: After creating the expression, you can click OK. If the expression is invalid, you would receive an error and given the opportunity to correct it. You can create as many check constraints as you judge necessary for your table: After creating the check constraints, you can click OK. Programmatically Creating a Check Constraint To create a check constraint in SQL, first create the column on which the constraint will apply. Before the closing parenthesis of the table definition, use the following formula: CONSTRAINT name CHECK (expression)  The CONSTRAINT and the CHECK keywords are required. As an object, make sure you provide aname for it. Inside the parentheses that follow the CHECK operator, enter the expression that will be applied. Here is an example that will make sure that the hourly salary specified for an employee is greater than 12.50: CREATE TABLE Employees ( [Employee Number] nchar(7), [Full Name] varchar(80), [Hourly Salary] smallmoney, CONSTRAINT CK_HourlySalary CHECK ([Hourly Salary] > 12.50) ); It is important to understand that a check constraint it neither an expression nor a function. A check constraint contains an expression and may contain a function as part of its definition. After creating the constraint(s) for a table, in the Object Explorer of Microsoft SQL Server Management Studio, inside the table's node, there is a node named Constraints and, if you expand it, you would see the name of the constraint. With the constraint(s) in place, during data entry, if the user (or your code) provides an invalid value, an error would display. Here is an example: Instead of an expression that uses only the regular operators, you can use a function to assist in the checking process. You can create and use your own function or you can use one of the built-in Transact-SQL functions. *Practical Learning: Creating a Check Constraint In the PowerShell window, to create a table that has a check mechanism, type the following:   DROP TABLE Customers; GO CREATE TABLE Customers (    CustomerID int identity(1, 1) NOT NULL,    AccountNumber nchar(10) UNIQUE,    FullName nvarchar(50) NOT NULL,    PhoneNumber nvarchar(20),    EmailAddress nvarchar(50),    CONSTRAINT CK_CustomerContact CHECK ((PhoneNumber IS NOT NULL) OR (EmailAddress IS NOT NULL)) ); GO To add records to the new table, type the following:   INSERT INTO Customers(AccountNumber, FullName,                      PhoneNumber, EmailAddress) VALUES(N'395805', N'Ann Zeke', N'301-128-3506', N'azeke@yahoo.jp'),      (N'628475', N'Peter Dokta', N'(202) 050-1629',       N'pdorka1900@hotmail.com'),      (N'860042', N'Joan Summs', N'410-114-6820', N'jsummons@emailcity.net'); GO To try adding a new record to the table, type the following:   INSERT INTO Customers(AccountNumber, FullName)       VALUES(N'228648', N'James Roberts') GO Notice that you receive an error. Close the PowerShell window Assistance With Data Entry: Using Functions   Introduction You can involve a function during data entry. As an example, you can call a function that returns a value to assign that value to a column. You can first create your own function and use it, or you can use one of the built-in functions. Using Functions In order to involve a function with your data entry, you must have and identity one. You can use one of the built-in functions of Transact-SQL. You can check one of the functions we reviewed in Lesson 8. Normally, the best way is to check the online documentation to find out if the assignment you want to perform is already created. Using a built-in function would space you the trouble of getting a function. For example, imagine you have a database named AutoRepairShop and imagine it has a table used to create repair orders for customers: CREATE TABLE RepairOrders (  RepairID int Identity(1,1) NOT NULL,  CustomerName varchar(50),  CustomerPhone varchar(20),  RepairDate datetime2 ); GO When performing data entry for this table, you can let the user enter the customer name and phone number. On the other hand, you can assist the user by programmatically entering the current date. To do this, you would call the GETDATE() function. Here are examples: INSERT INTO RepairOrders(CustomerName, CustomerPhone, RepairDate)    VALUES(N'Annette Berceau', N'301-988-4615', GETDATE()); GO INSERT INTO RepairOrders(CustomerPhone, CustomerName, RepairDate)    VALUES(N'(240) 601-3795', N'Paulino Santiago', GETDATE()); GO INSERT INTO RepairOrders(CustomerName, RepairDate, CustomerPhone)    VALUES(N'Alicia Katts', GETDATE(), N'(301) 527-3095'); GO INSERT INTO RepairOrders(RepairDate, CustomerPhone, CustomerName)    VALUES(GETDATE(), N'703-927-4002', N'Bertrand Nguyen'); GO You can also involve the function in an operation, then use the result as the value to assign to a field. You can also call a function that takes one or more arguments; make sure you respect the rules of passing an argument to a function when calling it. If none of the Transact-SQL built-in functions satisfies your requirements, you can create your own, using the techniques we studied in Lesson 7. Other Features of Data Entry   Is RowGuid This property allows you to specify that a column with the Identity property set to Yes is used as a ROWGUID column. Collation Because different languages use different mechanisms in their alphabetic characters, this can affect the way some sort algorithms or queries are performed on data, you can ask the database to apply a certain language mechanism to the field by changing the Collation property. Otherwise, you should accept the default specified by the table. To find out what language your server is currently using, in a Query window or from PowerShell, you can type: SELECT @@LANGUAGE; GO Lesson Summary     Topics Reviewed   Topics Reviews Record Row Table Navigation Visual Data Entry SQL Data Entry Adjacent Data Entry Random Data Entry Default Values Identity Columns Expressions Check Constraints Collation Data Import Selecting Records Editing Records Updating Records Deleting Records Keywords, Operators, and Properties NULL NOT NULL DEFAULT IDENTITY Identity Specification (Is Identity) Identity Seed property Identity Increment CONSTRAINT CHECK Collation databases EXISTS UPDATE DELETE

*	Records Operations: Updating a Record

 

Introduction

Updating a record consists of changing or more of its values. To support this operation, the SQLprovides the UPDATE keyword you can use in the following formula: UPDATE TableName SET ColumnName = Expression

 

Examples Here is an example: USE VideoCollection; GO UPDATE Videos SET Rating = 'R'; GO Here is an example: UPDATE TableName SET ColumnName = Expression WHERE Condition(s) Here is another example: UPDATE Videos SET YearReleased = 1996 WHERE Director = 'Rob Reiner'; Here is another example: UPDATE Videos SET YearReleased = 1996 WHERE VideoID = 5; GO Here is an example: UPDATE Videos SET Director = 'Jonathan Lynn' WHERE VideoTitle = 'The Distinguished Gentleman';
SQL Operations: Deleting a Record

 

Description

To delete a record in SQL, you can use theDELETE operator. To delete all records from a table, type DELETE followed by the name of the table, using this formula: DELETE TableName; Here is an example: DELETE FROM Employees; Removing a Record To delete a record, use this formula: DELETE FROM TableName WHERE Condition(s) Here is an example: DELETE FROM Employees WHERE EmployeeNumber = 24685;

The Default Value of a Column

 

Description

A default value is one that a column would apply to its record if a value is not provided. You can assign a default value when creating a table in Design View or programmatically. To visually specify the default value of a column, in the top section of the table in Design View, click the column. In the bottom section, click Default Value or Binding, type the desired value following the rules of the column's datatype.

To specify the default value in a SQL statement, when creating the column, before the semi-colon or the closing parenthesis of the last column, assign the desired value to the DEFAULT keyword. Here are examples:

CREATE TABLE Employees
(
   FullName NVARCHAR(50),
   Address NVARCHAR(80),
   City NVARCHAR(40),
   State NVARCHAR(40) DEFAULT N'NSW',
   PostalCode NVARCHAR(4) DEFAULT N'2000',
   Country NVARCHAR(20) DEFAULT N'Australia'
);
GO

The default value can also come from a function. Here is an example:

CREATE TABLE Employees
(
   EmployeeName nvarchar(50),
   DateHired date default GETDATE(),
   Address nvarchar(50),
   City nvarchar(40),
   State nchar(2) DEFAULT 'VA',
   PostalCode NVARCHAR(4),
   Country NVARCHAR(20)
);
GO

If the table exists already and you want to add a column that has a default value, use the formula:

ALTER TABLE TableName ADD ColumnName Options

Here is an example:

USE Exercise1;
GO

ALTER TABLE Employees
ADD HomePhone nvarchar(20) default N'(000) 000-0000';
GO

After creating the table, the user does not have to provide a value for a column that has a default. If the user does not provide the value, the default would be used when the record is saved.

A Column With Expression

  

Description

An expression used on a column is a combination of operators and operands used to produce the value of the column. The user will not enter a value for such a column. To visually specify an expression for a column, display the table in Design View. In the top section, enter or select the column name. In the bottom section, expand the Computed Column Specification field and, in its (Formula) field, enter the desired expression. Here is an example:

 

To create an expression in SQL, in the placeholder of the column, enter the name of the column, followed by AS, and followed by the desired expression. Here is an example:

CREATE TABLE Circle
(
   CircleID int identity(1,1) NOT NULL,
   Radius decimal(8, 3) NOT NULL,
   Area AS Radius * Radius * PI()
);
GO

When performing data entry, you must not provide a value for a column that has an expression; the SQL interpreter would provide the value automatically. Here is an example of entering data for the above Circle table:

INSERT INTO Circle(Radius) VALUES(46.82);
GO
INSERT INTO Circle(Radius) VALUES(8.15);
GO
INSERT INTO Circle(Radius) VALUES(122.57);
GO

Null Values on a Column

  

Description

A field is referred to as null if it has not received a value or its value is not clearly identified. To visually create a null field, display the table in Design View. In the top section, click the column name. In its corresponding Allow Nulls section, check or clear the check box.

*

To specify the nullity of a column using SQL, on the right side of the column creation, type NULL. To specify that the values of the column are required, on the right side, type NOT NULL. If you don't specify NULL or NOT NULL, the column will be created as NULL. Here are examples:

CREATE TABLE Persons
(
   FirstName varchar(20) NULL,
   LastName varchar(20) NOT NULL,
   Gender smallint
);
GO

If the table was already created and it holds some values already, you cannot set the Allow Nulls option on columns that don't have values.

After specify that a column would NOT allow NULL values, if the user tries creating a record but omits to create a value for the column, an error would display. Here is an example:

This error message box indicates that the user attempted to submit a null value for a column. To cancel the action, you can press Esc.

Unique Values on a Column    Description You may want a column to receive a unique value for each of its records. To specify that a column will require unique values, when creating it in SQL, use the UNIQUE keyword. Here is an example: USE Exercise; GO CREATE TABLE Students (    StudentNumber int UNIQUE,    FirstName nvarchar(50),    LastName nvarchar(50) NOT NULL ); GO When a column has been marked as unique, during data entry, the user must provide a unique value for each new record created. If an existing value is assigned to the column, this would produce an error: USE Exercise; GO CREATE TABLE Students (    StudentNumber int UNIQUE,    FirstName nvarchar(50),    LastName nvarchar(50) NOT NULL ); GO INSERT INTO Students VALUES(24880, N'John', N'Scheels'),      (92846, N'Rénée', N'Almonds'),      (47196, N'Peter', N'Sansen'),      (92846, N'Daly', N'Camara'),      (36904, N'Peter', N'Sansen'); GO By the time the fourth record is entered, since it uses a student number that exists already, the database engine would produce an error: Msg 2627, Level 14, State 1, Line 2 Violation of UNIQUE KEY constraint 'UQ__Students__DD81BF6C145C0A3F'. Cannot insert duplicate key in object 'dbo.Students'. The statement has been terminated.

 

Assistance With Data Entry: Check Constraints   Introduction When performing data entry, in some columns, even after indicating the types of values you expect the user to provide for a certain column, you may want to restrict a range of values that are allowed. In the same way, you can create a rule that must be respected on a combination of columns before the record can be created. For example, you can ask the database engine to check that at least one of two columns received a value. For example, on a table that holds information about customers, you can ask the database engine to check that, for each record, either the phone number or the email address of the customer is entered. The ability to verify that one or more rules are respected on a table is called a check constraint. A check constraint is a Boolean operation performed by the SQL interpreter. The interpreter examines a value that has just been provided for a column. If the value is appropriate: The constraint produces TRUE The value gets accepted The value is assigned to the column If the value is not appropriate: The constraint produces FALSE The value gets rejected The value is not assigned to the column You create a check constraint at the time you are creating a table. Visually Creating a Check Constraint To create a check constraint, when creating a table, right-click anywhere in (even outside) the table and click Check Constraints... This would open the Check Constraints dialog box. From that window, you can click Add. Because a constraint is an object, you must provide a name for it. The most important piece of information that a check constraint should hold is the mechanism it would use to check its values. This is provided as an expression. Therefore, to create a constraint, you can click Expression and click its ellipsis button. This would open the Check Constraint Expression dialog box. To create the expression, first type the name of the column on which the constraint will apply, followed by parentheses. In the parentheses, use the arithmetic and/or SQL operators we studied already. Here is an example that will check that a new value specified for the Student Number is greater than 1000: After creating the expression, you can click OK. If the expression is invalid, you would receive an error and given the opportunity to correct it. You can create as many check constraints as you judge necessary for your table: After creating the check constraints, you can click OK. Programmatically Creating a Check Constraint To create a check constraint in SQL, first create the column on which the constraint will apply. Before the closing parenthesis of the table definition, use the following formula: CONSTRAINT name CHECK (expression)  The CONSTRAINT and the CHECK keywords are required. As an object, make sure you provide aname for it. Inside the parentheses that follow the CHECK operator, enter the expression that will be applied. Here is an example that will make sure that the hourly salary specified for an employee is greater than 12.50: CREATE TABLE Employees ( [Employee Number] nchar(7), [Full Name] varchar(80), [Hourly Salary] smallmoney, CONSTRAINT CK_HourlySalary CHECK ([Hourly Salary] > 12.50) ); It is important to understand that a check constraint it neither an expression nor a function. A check constraint contains an expression and may contain a function as part of its definition. After creating the constraint(s) for a table, in the Object Explorer of Microsoft SQL Server Management Studio, inside the table's node, there is a node named Constraints and, if you expand it, you would see the name of the constraint. With the constraint(s) in place, during data entry, if the user (or your code) provides an invalid value, an error would display. Here is an example: Instead of an expression that uses only the regular operators, you can use a function to assist in the checking process. You can create and use your own function or you can use one of the built-in Transact-SQL functions. *Practical Learning: Creating a Check Constraint In the PowerShell window, to create a table that has a check mechanism, type the following:   DROP TABLE Customers; GO CREATE TABLE Customers (    CustomerID int identity(1, 1) NOT NULL,    AccountNumber nchar(10) UNIQUE,    FullName nvarchar(50) NOT NULL,    PhoneNumber nvarchar(20),    EmailAddress nvarchar(50),    CONSTRAINT CK_CustomerContact CHECK ((PhoneNumber IS NOT NULL) OR (EmailAddress IS NOT NULL)) ); GO To add records to the new table, type the following:   INSERT INTO Customers(AccountNumber, FullName,                      PhoneNumber, EmailAddress) VALUES(N'395805', N'Ann Zeke', N'301-128-3506', N'azeke@yahoo.jp'),      (N'628475', N'Peter Dokta', N'(202) 050-1629',       N'pdorka1900@hotmail.com'),      (N'860042', N'Joan Summs', N'410-114-6820', N'jsummons@emailcity.net'); GO To try adding a new record to the table, type the following:   INSERT INTO Customers(AccountNumber, FullName)       VALUES(N'228648', N'James Roberts') GO Notice that you receive an error. Close the PowerShell window Assistance With Data Entry: Using Functions   Introduction You can involve a function during data entry. As an example, you can call a function that returns a value to assign that value to a column. You can first create your own function and use it, or you can use one of the built-in functions. Using Functions In order to involve a function with your data entry, you must have and identity one. You can use one of the built-in functions of Transact-SQL. You can check one of the functions we reviewed in Lesson 8. Normally, the best way is to check the online documentation to find out if the assignment you want to perform is already created. Using a built-in function would space you the trouble of getting a function. For example, imagine you have a database named AutoRepairShop and imagine it has a table used to create repair orders for customers: CREATE TABLE RepairOrders (  RepairID int Identity(1,1) NOT NULL,  CustomerName varchar(50),  CustomerPhone varchar(20),  RepairDate datetime2 ); GO When performing data entry for this table, you can let the user enter the customer name and phone number. On the other hand, you can assist the user by programmatically entering the current date. To do this, you would call the GETDATE() function. Here are examples: INSERT INTO RepairOrders(CustomerName, CustomerPhone, RepairDate)    VALUES(N'Annette Berceau', N'301-988-4615', GETDATE()); GO INSERT INTO RepairOrders(CustomerPhone, CustomerName, RepairDate)    VALUES(N'(240) 601-3795', N'Paulino Santiago', GETDATE()); GO INSERT INTO RepairOrders(CustomerName, RepairDate, CustomerPhone)    VALUES(N'Alicia Katts', GETDATE(), N'(301) 527-3095'); GO INSERT INTO RepairOrders(RepairDate, CustomerPhone, CustomerName)    VALUES(GETDATE(), N'703-927-4002', N'Bertrand Nguyen'); GO You can also involve the function in an operation, then use the result as the value to assign to a field. You can also call a function that takes one or more arguments; make sure you respect the rules of passing an argument to a function when calling it.

Indexes   Indexes Fundamentals   Introduction If you take a look at the last pages of a non-fictional book (such as a book about history, economics, mathematics, sociology, or statistics, etc), you may find a series of pages that start in a section label Index. The words in that series allow you to locate a section of the book that mentions, explains, or describes the word and related topics. An index in a book makes it easy and fast to get to a section of a book that deals with a particular topic. Like a book, a table or a view can use the mechanism provided by an index. In a table or a view, an index is a column (or many columns) that can be used to locate records and take a specific action based on some rule reinforced on that (those) column(s). *Practical Learning: Introducing Indexes Start the computer and log in with an account that has administrative rights Launch Microsoft SQL Server In the Authentication combo box, select Windows Authentication Click Connect To create a new database, in the Object Explorer, right-click Databases and click New Database... Set the name to DepartmentStore2 Click OK In the Object Explorer, right-click Databases and click Refresh Expand DepartmentStore2 Right->click Tables and click New Table... Click the fields as follows:   Column Name Data Type Allow Nulls EmployeeNumber int Unchecked FirstName nvarchar(20) Checked MI nchar(1) Checked LastName nvarchar(20) Unchecked Username nvarchar(20) Unchecked Password nvarchar(20) Checked Close the table When asked whether you want to save it, click Yes Set the name to Employees Click OK On the Standard toolbar, click the New Query button  To create a new database and a table, type the following: IF EXISTS(SELECT name FROM sys.databases  WHERE name = N'HotelManagement1') DROP DATABASE HotelManagement1; GO CREATE DATABASE HotelManagement1; GO USE HotelManagement1; GO IF OBJECT_ID('Rooms', 'U') IS NOT NULL  DROP TABLE Rooms GO CREATE TABLE Rooms (    RoomID int identity(1, 1) primary key not null,    RoomNumber nvarchar(10),    LocationCode nchar(10) default N'Silver Spring',    RoomType nvarchar(20) default N'Bedroom',    BedType nvarchar(40) default N'Queen',    Rate money default 75.85,    Available bit default 0 ); GO Press F5 to execute Visually Creating an Index The database engine is already equipped with a default mechanism to automatically make up an index on a table depending on how the table is created. For example, if you create a primary key on a table, the database engine automatically creates an index for the column(s) used as the primary key. Otherwise, you can still explicitly create an index. You can create an index visually or using SQL code. To visually create an index, you can use the Indexes/Keys dialog box. To display it, in the Object Explorer, expand the database that holds the table or view and expand the Tables node. Right-click the table or view for which you want to create the index and click Design. In the window, right-click Indexes/Keys... This would open the Indexes/Keys dialog box: To create an index, click the Add button. The first piece of information you should provide is the name. Normally, the database engine provides a default name. If you want to change it, click (Name) and type the desired name. The other very important piece of information you should provide is at least one column. By default, the database engine selects the first column of the table. If this is not the column you want to use, click Columns and click its ellipsis button . This would open the Index dialog box: From the boxes under Column Name, you can select each column. Once you are ready, click OK. *Practical Learning: Visually Creating an Index In the Object Explorer, under DepartmentStore2, right-click Tables and click Refresh Expand the Tables node. Right-click dbo.Employees and click Design Right-click anywhere in the white area and click Indexes/Keys... Click Add Click Columns and click its ellipsis button Click the combo box of the default EmployeeNumber and select LastName Click the box under LastName Click the arrow of its combo box and select Username   Click OK Click (Name) and type IX_Credentials Click Close Close the table When asked whether you want to save, click Yes Creating an Index With SQL To create an index in SQL, the basic formula to follow is: CREATE INDEX IndexName ON Table/View(Column(s)) Alternatively, open a Query window. Then, in the Templates Explorer, expand the Index node. Drag Create Index Basis (or another sub-node) and drop it in the window. Skeleton code would be generated for you: -- ============================================= -- Create index basic template -- ============================================= USE <database_name, sysname, AdventureWorks> GO CREATE INDEX <index_name, sysname, ind_test> ON <schema_name, sysname, Person>.<table_name, sysname, Address> ( <column_name1, sysname, PostalCode> ) GO The creation on an index starts with the CREATE INDEX expression, followed by a name for the index, followed by the ON keyword. In the Table/View placeholder, enter the name of the table or view for which you want to create the index, followed by parentheses in which you enter at least one column. Here is an example: -- ============================================= -- Database: Exercise -- ============================================= USE master GO -- Drop the database if it already exists IF  EXISTS ( SELECT name FROM sys.databases WHERE name = N'Exercise' ) DROP DATABASE Exercise GO CREATE DATABASE Exercise GO USE Exercise; GO -- ============================================= -- Database: Exercise -- Table;    Employees -- ============================================= CREATE TABLE Employees ( EmployeeNumber int NOT NULL, LastName nvarchar(20) NOT NULL, FirstName nvarchar(20), Username nchar(8) NOT NULL, DateHired date NULL, HourlySalary money ); GO INSERT INTO Employees VALUES(62480, N'James', N'Haans', N'jhaans', N'1998-10-25', 28.02),      (35844, N'Gertrude', N'Monay', N'gmonay', N'2006-06-22', 14.36),      (24904, N'Philomène', N'Guillon', N'pguillon', N'2001-10-16', 18.05),      (48049, N'Eddie', N'Monsoon', N'emonsoon', N'08/10/2009',   26.22),      (25805, N'Peter', N'Mukoko', N'pmukoko', N'03-10-2004', 22.48),      (58405, N'Chritian', N'Allen', N'callen', N'06/16/1995', 16.45); GO CREATE INDEX IX_Employees ON Employees(EmployeeNumber); GO If the index will include more than one column, list them separated by commas. Here is an example: CREATE INDEX IX_Employees ON Employees(LastName, Username); GO Index Maintenance   Introduction In Microsoft SQL Server (and most database systems), an index is treated as an object. That is, an index can be checked or deleted at will. Deleting an Index If you don't need an index anymore, you can delete it. You can do this visually or manually. To visually delete an index, open its table in Design view. Right-click somewhere in the table window and click Indexes/Views. In the left frame, click the name of the index to select it, and click the Delete button. You will not receive a warning. Then click Close. If you want to change your mind and keep the index, don't save the table. The basic syntax to delete an index in Transact-SQL is: DROP INDEX IndexName ON TableName; In this formula, replace the TableName with the name of the table that contains the index. Replace the IndexName with the name of the index you want to get rid of. Here is an example: USE Exercise; GO DROP INDEX IX_Employees ON Employees; GO Checking the Existence of an Index Before performing an operation on an index, you may want to check first that it exists. For example, if you try creating an index and giving it a name that exists already, you would receive an error. The following code USE Exercise; GO CREATE INDEX IX_Employees ON Employees(EmployeeNumber); GO would produce: Msg 1913, Level 16, State 1, Line 2 The operation failed because an index or statistics with name 'IX_Employees' already exists on table 'Employees'. To visually check the existence of an index, open the table or view in design view, right-click the middle of the window and click Indexes/Keys. The list of indexes should appear on the left side. Here is an example: To assist you with checking the existence of an index, Transact-SQL provides the following formula: IF EXISTS (SELECT name FROM sys.indexes WHERE name = IndexName) Do something here The primary thing you need to provide in this formula is the name of the index. Once you have checked, you can take the necessary action. Here is an example: USE Exercise; GO IF EXISTS (SELECT name FROM sys.indexes  WHERE name = N'IX_Employees') DROP INDEX IX_Employees ON Employees GO CREATE INDEX IX_Employees ON Employees(EmployeeNumber); GO The Types of Indexes   Introduction Microsoft SQL Server supports various types of indexes. The two broadest categories are clustered and non-clustered. Clustered Indexes In our introduction, we saw that an index is primarily created using one or more columns from a designated table. This means that, when it comes to using the index, we would use the values stored in the column(s) that was (were) selected for the index. Such an index is referred to as clustered. The columns that were made part of an index are referred to as keys. To visually create a clustered index, display the Indexes/Keys dialog box. In the dialog box, when creating a new indexed or after clicking the name of an existing index, in the right list, click Create As Clustered and select Yes: Once you are ready, click Close. To create a clustered index in SQL, use the following formula: CREATE CLUSTERED INDEX IndexName ON Table/View(Column(s)) From the description we gave previously, the only new keyword here is CLUSTERED. Based on this, here is an example: -- ============================================= -- Database: Exercise -- ============================================= USE master GO -- Drop the database if it already exists IF  EXISTS(SELECT name   FROM sys.databases   WHERE name = N'Exercise' ) DROP DATABASE Exercise GO CREATE DATABASE Exercise GO USE Exercise; GO -- ============================================= -- Database: Exercise -- Table;    Employees -- ============================================= CREATE TABLE Employees ( EmployeeNumber int NOT NULL, LastName nvarchar(20) NOT NULL, FirstName nvarchar(20), Username nchar(8) NOT NULL, DateHired date NULL, HourlySalary money ); GO INSERT INTO Employees VALUES(62480, N'James', N'Haans', N'jhaans', N'1998-10-25', 28.02),      (35844, N'Gertrude', N'Monay', N'gmonay', N'2006-06-22', 14.36),      (24904, N'Philomène', N'Guillon', N'pguillon', N'2001-10-16', 18.05),      (48049, N'Eddie', N'Monsoon', N'emonsoon', N'08/10/2009',   26.22),      (25805, N'Peter', N'Mukoko', N'pmukoko', N'03-10-2004', 22.48),      (58405, N'Chritian', N'Allen', N'callen', N'06/16/1995', 16.45); GO CREATE CLUSTERED INDEX IX_Employees ON Employees(LastName); GO A table that contains a clustered index is called a clustered table. There are various aspects to a clustered index: To make it easy to search the records, they (the records) are sorted. This makes it possible for the database engine to proceed in a top-down approach and quickly get to the desired record Without this being a requirement, each record should be unique (we have already seen how to take care of this, using check constraints; later on, we will see again how to create unique records) There must be only one clustered index per table. This means that, if you (decide to) create a clustered index on a table, the table becomes equipped with one. If you create another clustered index, the previous one (clustered index) is deleted Non-Clustered Indexes While a clustered index uses a sorted list of records of a table or view, another type of index can use a mechanism not based on the sorted records but on a bookmark. This is called a non-clustered index. As opposed to a clustered table that can contain only one clustered index, you can create not only one, but as many as 249 non-clustered indexes. To visually create a non-clustered index, display the Indexes/Keys dialog box. To create a new index, click the Add button. If an index was always created or set as clustered and you want to change it, you can change its Create As Clustered property from Yes to No. To create a non-clustered index in SQL, use the following formula: CREATE NONCLUSTERED INDEX IndexName ON Table/View(Column(s)) The new keyword in this formula is NONCLUSTERED. Everything is the same as previously described. Based on this, here is an example: -- ============================================= -- Database: Exercise -- ============================================= USE master GO -- Drop the database if it already exists IF  EXISTS(SELECT name   FROM sys.databases   WHERE name = N'Exercise' ) DROP DATABASE Exercise GO CREATE DATABASE Exercise GO USE Exercise; GO -- ============================================= -- Database: Exercise -- Table;    Employees -- ============================================= CREATE TABLE Employees ( EmployeeNumber int NOT NULL, LastName nvarchar(20) NOT NULL, FirstName nvarchar(20), Username nchar(8) NOT NULL, DateHired date NULL, HourlySalary money ); GO INSERT INTO Employees VALUES(62480, N'James', N'Haans', N'jhaans', N'1998-10-25', 28.02),      (35844, N'Gertrude', N'Monay', N'gmonay', N'2006-06-22', 14.36),      (24904, N'Philomène', N'Guillon', N'pguillon', N'2001-10-16', 18.05),      (48049, N'Eddie', N'Monsoon', N'emonsoon', N'08/10/2009',   26.22),      (25805, N'Peter', N'Mukoko', N'pmukoko', N'03-10-2004', 22.48),      (58405, N'Chritian', N'Allen', N'callen', N'06/16/1995', 16.45); GO CREATE NONCLUSTERED INDEX IX_Employees ON Employees(LastName, FirstName); GO If you create an index without specifying CLUSTERED or NONCLUSTERED, the database engine automatically makes it non-clustered. *Practical Learning: Creating a Non-Clustered Index With SQL Click in the top section of the Query window,  press Ctrl + A to select the whole text To create an index using Transact-SQL, type the following: USE HotelManagement1; GO IF EXISTS(SELECT name FROM sys.indexes  WHERE name = N'IX_RoomsIdentities') DROP INDEX IX_RoomsIdentities ON Rooms CREATE NONCLUSTERED INDEX IX_RoomsIdentities ON Rooms(RoomNumber, LocationCode); GO Press F5 to execute

 

Indexes and Table Creation We already know how to create a primary key on a table. Here is an example: USE Exercise; GO CREATE TABLE Students ( StudentID int PRIMARY KEY, FirstName nvarchar(50) NOT NULL, LastName nvarchar(50)); GO When you create a primary key, the database engine automatically creates an index on the table and chooses the primary key column as its key. You have the option of indicating the type of index you want created. To do this, on the right side of the name of the column, enter CLUSTERED orNONCLUSTERED. If you don't specify the type of index, the CLUSTERED option is applied. *Practical Learning: Creating a Clustered Tables Click the top section of the Query window and press Ctrl + A To complete the database, type the following: USE HotelManagement1; GO CREATE TABLE Customers (    CustomerID int identity(1, 1) primary key CLUSTERED NOT NULL,    AccountNumber nchar(10) UNIQUE,    FullName nvarchar(50) NOT NULL,    PhoneNumber nvarchar(20),    EmailAddress nvarchar(50),    CONSTRAINT CK_CustomerContact CHECK ((PhoneNumber IS NOT NULL) OR (EmailAddress IS NOT NULL)) ); GO Press F5 to execute Data Entry and Analysis With Indexes   Introduction In our introduction, we saw that an index can make it possible to take some action during data entry, such as making sure that a column have unique values for each record or making sure that the combination of values of a group of columns on the same record produces a unique value. Besides this characteristic of indexes, they are actually very valuable when it comes to data analysis. As mentioned for a book, the primary goal of an index is to make it easy to locate the records of a table or view. *Practical Learning: Entering Data Click the top area of the Query window and press Ctrl + A Type the following: USE HotelManagement1; GO INSERT INTO Rooms(RoomNumber, LocationCode) VALUES(104, N'SLSP'); GO INSERT INTO Rooms(RoomNumber, LocationCode, BedType, Rate, Available)           VALUES(105, N'SLSP', N'King', 85.75, 1), (106, N'SLSP', N'King', 85.75, 1) GO INSERT INTO Rooms(RoomNumber, LocationCode, Available)   VALUES(107, N'SLSP', 1) GO INSERT INTO Rooms(RoomNumber, LocationCode, BedType, Rate)   VALUES(108, N'SLSP', N'King', 85.75) GO INSERT INTO Rooms(RoomNumber, LocationCode, Available)   VALUES(109, N'SLSP', 1) GO INSERT INTO Rooms(RoomNumber, LocationCode, RoomType, Rate, Available)   VALUES(110, N'SLSP', N'Conference', 450.00, 1) GO Press F5 to execute Introduction to Index Uniqueness An index is made valuable in two ways. On one hand, the records should be sorted. A clustered index itself takes care of this aspect because it automatically and internally sorts its records. What if the records are not unique? For example, in a bad data entry on a list of employees, you may have two or more employees with the same employee's records. If you create an index for such a table, the database engine would create duplicate records on the index. This is usually not good because when it comes time to select records, you may have too many records and take a wrong action. When creating a table, you can create index for it and let the index apply a rule that states that each record would be unique. To take care of this, you can apply a uniqueness rule on the index. If you are visually creating an index, in the Indexes/Keys dialog box, select the index on the left side. On the right list, set the Is Unique field to Yes. On the other hand, if you want to remove this rule, set the Is Unique field to No. To create a uniqueness index in SQL, apply the UNIQUE keyword in the formula: CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]       INDEX index_name ON Table/View(Column(s)) Start with the CREATE UNIQUE expression, then specify whether it would be clustered or not. The rest follows the descriptions we saw previously. Here is an example: -- ============================================= -- Database: Exercise -- ============================================= USE master GO -- Drop the database if it already exists IF  EXISTS ( SELECT name FROM sys.databases WHERE name = N'Exercise' ) DROP DATABASE Exercise GO CREATE DATABASE Exercise GO USE Exercise; GO -- ============================================= -- Database: Exercise -- Table;    Employees -- ============================================= CREATE TABLE Employees ( EmployeeNumber int NOT NULL, LastName nvarchar(20) NOT NULL, FirstName nvarchar(20), Username nchar(8) NOT NULL, DateHired date NULL, HourlySalary money ); GO CREATE UNIQUE CLUSTERED INDEX IX_Employees ON Employees(EmployeeNumber); GO *Practical Learning: Using Index Uniqueness Click the top area of the Query window and press Ctrl + A Type the following: USE HotelManagement1; GO INSERT INTO Customers(AccountNumber, FullName,                      PhoneNumber, EmailAddress) VALUES(N'395805', N'Ann Zeke', N'301-128-3506', N'azeke@yahoo.jp'),      (N'628475', N'Peter Dokta', N'(202) 050-1629',          N'pdorka1900@hotmail.com'),      (N'860042', N'Joan Summs', N'410-114-6820',  N'jsummons@emailcity.net'),      (N'228648', N'James Roberts',      N'(301) 097-9374', N'jroberts13579@gmail.net') GO Press F5 to execute Unique Indexes and Data Entry Once you have specified the uniqueness of an index on a table, during data entry, if the user enters a value that exists in the table already, an error would be produced. Here is an example: USE Exercise; GO INSERT INTO Employees(EmployeeNumber, FirstName,      LastName, HourlySalary) VALUES(92935, N'Joan', N'Hamilton', 22.50) GO INSERT INTO Employees(EmployeeNumber, FirstName,      LastName, HourlySalary) VALUES(22940, N'Peter', N'Malley', 14.25) GO INSERT INTO Employees(EmployeeNumber, FirstName,      LastName, HourlySalary) VALUES(27495, N'Christine', N'Fink', 32.05) GO INSERT INTO Employees(EmployeeNumber, FirstName,      LastName, HourlySalary) VALUES(22940, N'Gertrude', N'Monay', 15.55) GO INSERT INTO Employees(EmployeeNumber, FirstName,      LastName, HourlySalary) VALUES(20285, N'Helene', N'Mukoko', 26.65) GO This would produce: (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) Msg 2601, Level 14, State 1, Line 1 Cannot insert duplicate key row in object 'dbo.Employees' with unique index 'IX_Employees'. The statement has been terminated. (1 row(s) affected) Table and Index Partitioning   Introduction Data in your database may involve many records, in thousands or millions, so much that at one time, it may become difficult to manage. One way you can deal with this is to store the records of a table in different file groups. This makes it possible to store one section of records in one file group, another section in another file group, possibly another section in another file group, and so on. As a result, when it comes time to look for one or a few records among thousands or millions of records, it would be easier to locate it or to locate them. Of course, the data still belongs to one database and to the same table. *Practical Learning: Introducing Partitioning Open a file utility, such as Windows Explorer Display the contents of the drives On the C: drive, create a folder named FunDS2 Main Repository If you have another partition or another drive such as D:, create a folder on it and name itFunDS2 Secondary Repository. Then, in the code below, replace the indicated drive of FunDS2 Secondary Repository to that drive If you don't have another drive, create another folder on the C: drive and name it FunDS2 Secondary Repository If you have one more partition or another drive such as E:, create a folder on it and name itFunDS2 Third Repository. Then, in the code below, replace the indicated drive of FunDS2 Third Repository to that drive If you don't have another drive, on the C: drive, create another folder FunDS2 Third Repository Check each of those folders and notice that they are empty Return to Microsoft SQL Server Management Studio Click inside the top area of the Query window and press Ctrl + A To create a database and the accompanying file groups, type the following (this is theFunDS2a.sql (FunDS2a.txt) file): USE master; GO -- ============================================= -- Database: FunDS2 -- Author:   FunctionX -- Date:     Sunday 06 February 2011 - 18:08 -- ============================================= USE master GO -- Drop the database if it already exists IF  EXISTS ( SELECT name FROM sys.databases WHERE name = N'FunD2' ) DROP DATABASE FunDS2; CREATE DATABASE FunDS2 ON PRIMARY  ( NAME = N'FunDS2Primary',    FILENAME = N'C:\FunDS2 Main Repository\FunDS2Main.mdf',    SIZE = 4MB,    MAXSIZE = 10MB,    FILEGROWTH = 1MB), FILEGROUP FunDS2GroupRecords1  ( NAME = N'FunDS2Records1',    FILENAME = N'C:\FunDS2 Main Repository\FunDS2First.ndf',    SIZE = 1MB,    MAXSIZE = 10MB,    FILEGROWTH = 1MB), FILEGROUP FunDS2GroupRecords2  ( NAME = N'FunDS2Records2',    FILENAME = N'C:\FunDS2 Secondary Repository\FunDS2Second.ndf',    SIZE = 1MB,    MAXSIZE = 10MB,    FILEGROWTH = 1MB), FILEGROUP FunDS2GroupRecords3  ( NAME = N'FunDS2Records3',    FILENAME = N'C:\FunDS2 Third Repository\FunDS2Third.ndf',    SIZE = 1MB,    MAXSIZE = 10MB,    FILEGROWTH = 1MB) LOG ON  ( NAME = N'FunDS23Log',    FILENAME = N'C:\FunDS2 Main Repository\FunDS2Logger.ldf',    SIZE = 1MB,    MAXSIZE = 10MB,    FILEGROWTH = 1MB); GO Press F5 to execute Return to the file utilities such as Windows Explorer and check the content of each of the previously created folders. Also check their sizes Return to Microsoft SQL Server Management Studio Partitioning a Table Before partitioning a table, you must create the necessary file groups. This can be done when creating the database since it is at that time that you specify how the database will be stored; that is, what files will hold the information of the database. After creating the database and creating its file groups, before partitioning a table, you must create a partition function and a partition scheme. A Partition Function A partition function is used to define the ranges of records that will be stored in what file group. The SQL formula to create a partition function is: CREATE PARTITION FUNCTION PartitionFunctionName ( ParameterType ) AS RANGE [ LEFT | RIGHT ] FOR VALUES (StartRange1, StartRange2, StartRange_n) To use from a template, open a Query window. In the Templates Explorer, expand the Partition Function node. Drag Create Partition Function and drop it in the Query window. Skeleton code will be generated for you: -- ===================================== -- Create Partition Function template -- ===================================== USE <database_name, sysname, AdventureWorks> GO CREATE PARTITION FUNCTION <partition_function_name, sysname, myRangePF> ( <data_type_name, sysname, int> ) AS RANGE LEFT FOR VALUES (<data_value1,,1>, <data_value2,,100>, <data_value3,,1000>); -- Partition function on a partitioning column col1 would be partitioned as follows: -- Partition 1: col1 less than or equal to <data_value1,,1> -- Partition 2: col1 greater than <data_value1,,1> AND col1 less than or equal to <data_value2,,100> -- Partition 3: col1 greater than <data_value2,,100> AND col1 less than or equal to <data_value3,,1000> -- Partition 4: col1 greater than <data_value3,,1000> The creation of a partition function starts with the CREATE PARTITION FUNCTION expression followed by a name. The name follows the rules for names in Microsoft SQL Server. Because you are creating a function, the name is followed by parentheses. In the parentheses of the function, you must specify the data type of the column that will be used to create a range of records. The values of that column will be used to distinguish ranges of records. This means that the values of this column must allow the database engine to predict a range of records. This is called the partitioning column. For example, you can use a column that has an incremental count of values. This is the case for an identity primary key column. As another example, you can use a column that holds a category of values, such as female customers vs male and child customers. As one more example, you can use a column that holds dates so that you can isolate ranges of records from one date to another. After closing the parenthesis, type AS RANGE, which indicates that you are going to specify the ranges of values. This is followed by either LEFT or RIGHT. When the partition function will have been created and when the table itself will have been created, when the database engine is asked to look for a record or a range of records, it may have to sort the records. If you want it to sort the records from left to right, use the LEFT keyword. If you want the records sorted from right to left, use the RIGHT keyword. The AS RANGE LEFT or AS RANGE RIGHT expression is followed by FOR VALUES that is followed by parentheses. When creating a partition function, you must provide a way for the database engine to get a range of records. For example, you can use records from number 1 to number 1000, then another range from 1001 to 5000, and so on. Or you can specify that a range of records would go from February 11th, 2000 to June 26th, 2005. Then another range would go from June 26th 2005 to December 14th, 2006, and so on. You specify the range in the parentheses that follow the FOR VALUES expression. Type the first value of the first range, followed by a comma, followed by the first value of the second range, and so on. *Practical Learning: Creating a Partition Function Click the top section of the Query window and press Ctrl + A Type the following: USE FunDS2; GO CREATE PARTITION FUNCTION FunDS2Segmentation(int) AS RANGE LEFT FOR VALUES(1, 10); GO Press F5 to execute A Partition Scheme A partition scheme specifies the names of the file groups, in their order that will store the ranges of records that were created in the partition function. The formula to create a partition scheme is: CREATE PARTITION SCHEME PartitionSchemeName AS PARTITION PartitionFunctionName [ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] ) You start with the CREATION PARTITION SCHEME expression do indication your intention. This is followed by a name. The name follows the rules of objects. After the name of the partition scheme, type AS PARTITION followed by the name of the partition function you should have previously created. If you are planning to use only one file group, after the name of the partition function, enter ALL, followed by parentheses, in which you will type PRIMARY. If you are planning to use different file groups, after the name of the partition function, enter TO, followed by parentheses. We saw that, in the parentheses of the FOR VALUES of the partition function, you entered the starting value of the first range. In the parentheses of the TO keyword, type the name of the file group that will hold the records of the first range of the partition function. We also saw how to specify the second range in the partition function. In the parentheses of the TO clause, after the name of the first file group, type a comma followed by the name of the file group that will hold the records of the second range. *Practical Learning: Creating a Partition Function Scheme Click the top section of the Query window and press Ctrl + A Type the following: USE FunDS2; GO CREATE PARTITION SCHEME FunDS2DistributionScheme AS PARTITION FunDS2Segmentation TO (FunDS2GroupRecords1, FunDS2GroupRecords2, FunDS2GroupRecords3); GO On the SQL Editor toolbar, click the Execute button Partitioning a Table After creating the partition scheme, you can create the table. The formula to specify a partition scheme when creating a table is: CREATE TABLE What We Have Learned So Far (    What We Have Learned So Far ) ON PartitionSchemeName(ColumnName) You start with the CREATE TABLE expression, followed by things we have learned so far: an optional schema and a required name. After the name of the table, you open and close the parentheses, in which you include other things we have seen so far: the columns, the constraints, and their options. Outside the parentheses, type the ON keyword, followed by the name of the partition scheme you will have created, followed by an opening and a closing parentheses. Inside the parentheses of the schema name, enter the name of the table's column that is the partitioning column. After creating the table, you can use it, like any normal table. *Practical Learning: Partitioning a Table Click the top section of the Query window and press Ctrl + A Type the following (the red parts simply indicates something that is new to us; otherwise, you should type everything) (this is the FunDS2b.sql file (FunDS2b.txt)): USE FunDS2; GO CREATE SCHEMA Inventory; GO CREATE FUNCTION Inventory.SetDateEntered(@days int) RETURNS Date AS BEGIN    RETURN DATEADD(d, @days, SYSDATETIME()); END GO CREATE TABLE Inventory.Manufacturers (    ManufacturerID int identity(1, 1) not null,    Name nvarchar(40),    OtherName1 nvarchar(40),    OtherName2 nvarchar(40),    CONSTRAINT PK_Manufacturers PRIMARY KEY(ManufacturerID) ) ON FunDS2DistributionScheme(ManufacturerID); GO CREATE TABLE Inventory.Categories (    CategoryID int identity(1, 1) not null,    Category nvarchar(40),    CONSTRAINT PK_Categories PRIMARY KEY(CategoryID) ) ON FunDS2DistributionScheme(CategoryID); GO CREATE TABLE Inventory.SubCategories (    SubCategoryID int identity(1, 1) not null,    SubCategory nvarchar(40),    CONSTRAINT PK_SubCategories PRIMARY KEY(SubCategoryID) ) ON FunDS2DistributionScheme(SubCategoryID); GO -- ======================================================= -- Table:    StoreItems -- Description: This table holds an inventory of all the -- merchandise sold in the store -- ======================================================= CREATE TABLE Inventory.StoreItems ( ItemNumber int unique, DateEntered date, ManufacturerID int null Constraint FK_Manufacturers References Inventory.Manufacturers(ManufacturerID), CategoryID int null Constraint FK_Categories References Inventory.Categories(CategoryID), SubCategoryID int null Constraint FK_SubCategories References Inventory.SubCategories(SubCategoryID), ItemName nvarchar(80) null, Size nvarchar(32), UnitPrice money, DiscountRate decimal(6, 2),    Constraint PK_StoreItems Primary Key(ItemNumber) ) ON FunDS2DistributionScheme(ItemNumber); GO On the SQL Editor toolbar, click the Execute button Open the FunDS2c.sql file (FunDS2c.txt) To execute it, right-click inside the document and click Execute In the Object Explorer, right-click FunDS2 and click Refresh. Expand FunDS2 Expand its Tables node Right-click Inventory.StoreItems and click Edit Top 200 Rows On the Query Designer toolbar, click the Show Diagram Pane button , the Show Criteria Pane button , and the Show SQL Pane button  In the Criteria pane, click Column and press Delete In the SQL pane, delete Top (200) In the Diagram pane, click the check boxes of ItemNumber, DateEntered, Name, Category, SubCategory, ItemName, Size, UnitPrice, DiscountRate   To see the result, on the main menu, click Query Designer -> Execute SQL   To see women's items, in the Criteria pane, click the box at the intersection of Category and Filter Type women To see the result, on the main menu, click Query Designer -> Execute SQL   Close the Query window Close Microsoft SQL Server Exercises   Lesson Summary Questions Consider the following table: CREATE TABLE Students ( StudentNumber nchar(8), [Full Name] nvarchar(50), ); GO Which one of the following codes will create an index? CREATE INDEX SomeIndex ON COLUMN StudentNumber FROM Students; GO CREATE INDEX SomeIndex FROM Students ON COLUMN StudentNumber; GO FROM Students CREATE INDEX SomeIndex ON StudentNumber; GO ON StudentNumber CREATE INDEX SomeIndex FROM Students; GO CREATE INDEX SomeIndex ON Students(StudentNumber); GO What's the basic formula to create an index? CREATE OBJECT::INDEX IndexName ON Table/View(Column(s)) CREATE IndexName ON Table/View(Column(s)) AS INDEX CREATE INDEX IndexName ON Table/View(Column(s)) WITH INDEX CREATE IndexName ON Table/View(Column(s)) CREATE IndexName ON Table/View(Column(s)) = INDEX What is the syntax of creating a partition function? CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type ) AS RANGE [ LEFT | RIGHT ] FOR VALUES ( [ boundary_value [ ,...n ] ] ) [ ; ] CREATE OBJECT::partition_function_name ( input_parameter_type ) AS RANGE [ LEFT | RIGHT ] FOR VALUES ( [ boundary_value [ ,...n ] ] ) [ ; ] AS PARTITION FUNCTION CREATE FUNCTION partition_function_name ( input_parameter_type ) AS RANGE [ LEFT | RIGHT ] FOR VALUES ( [ boundary_value [ ,...n ] ] ) IN PARTITION PartitionName[ ; ] CREATE PARTITION partition_function_name ( input_parameter_type ) WITH RANGE [ LEFT | RIGHT ] FOR VALUES ( [ boundary_value [ ,...n ] ] ) [ ; ] AS FUNCTION CREATE PARTITION FUNCTION AS partition_function_name ( input_parameter_type ) FOR RANGE [ LEFT | RIGHT ] SET VALUES = ( [ boundary_value [ ,...n ] ] ) [ ; ] Answers Answers Wrong Answer: The COLUMN keyword is not used in the formula to create an index Wrong Answer: The FROM keyword is not used to specify the table of an index Wrong Answer: That whole formula is wrong Wrong Answer: The creation of an index starts with CREATE INDEX Right Answer: That code will create an index Answers Wrong Answer: You don't need OBJECT:: to create an index Wrong Answer: An index is created with the CREATE INDEX expression, not CREATE ... AS INDEX Right Answer Wrong Answer Wrong Answer Answers Right Answer Wrong Answer Wrong Answer Wrong Answer Wrong Answer