Home

Assistance With Data Entry

 

Computed Columns

 

Introduction

There are various ways you can assist the user with data entry. For example, you can create an expression and apply it to a column so that the value of the column would come from other value. Such a column is called a computed column.

You can create the expression of a computed column when creating a table, whether in the Table window or using SQL in a Query window.

   

Practical LearningPractical Learning: Introducing Expressions

  1. Start the computer and log in
  2. Launch Microsoft SQL Server and click Connect
  3. In the Object Explorer, right-click Databases and click New Database...
  4. Set the Database Name to WorldStatistics2
  5. In the Path column, click each browse button and select the C:\Microsoft SQL Server Database Development folder
  6. Click OK
  7. In the Object Explorer, right-click Databases and click Refresh
  8. Expand the WorldStatistics2 node
  9. Right-click Tables and click New Table...

Visually Creating a Computed Column

To visually create an expression when creating a table, in the top section, specify the column's name (only the column name is needed). 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 code. 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

Persisted Columns

When you create a column that has an expression, the column doesn't have actual values. It is only a representative of values from other columns or constants. The column is referred to as a virtual column. Consider the following table:

CREATE TABLE Employees
(
    EmployeeNumber nchar(60),
    FirstName nvarchar(20),
    LastName nvarchar(20),
    HourlySalary money,
    TimeWorkedInAWeek decimal(6, 2),
    FullName AS LastName + N', ' + FirstName,
    WeeklySalary AS HourlySalary * TimeWorkedInAWeek
);
GO

The FullName only shows values that are from the FirstName and the LastName columns. This means that, unlike the FirstName and the LastName columns that have actual values, there is no real value in the FullName column. Transact-SQL allows you to actually store the value of the expression in the column. Storing the value in the column is referred to as persisting the value.

To ask the database engine to store the value of the expression (as an actual value), when creating the column, add a flag named PERSISTED at the end of the column definition. Here is an example:

CREATE TABLE Employees
(
    EmployeeNumber nchar(60),
    FirstName nvarchar(20),
    LastName nvarchar(20),
    HourlySalary money,
    TimeWorkedInAWeek decimal(6, 2),
    FullName AS LastName + N', ' + FirstName PERSISTED,
    WeeklySalary AS HourlySalary * TimeWorkedInAWeek
);
GO

Now, when you create a record, the result of the expression is saved in its own memory area. If you change the record, that is, if you change the value of (one of) the column(s) involved in the expression, the database engine would update the saved value of the expression.

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.

The Nullity of a Field

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.

Visually Creating a NULL or NOT NULL Column

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, 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.

Programmatically Creating a NULL or NOT NULL Column

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, 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 nvarchar(20) NULL,
    LastName nvarchar(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 specifying 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:

No row was updated

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 LearningPractical Learning: Applying Fields Nullity

  1. To apply the nullity of fields, create the fields as follows:
     
    Countries Table
  2. To save the table, on the Standard toolbar, click the Save button Save
  3. Set the Name to Countries
  4. Click OK

Data Entry With a NULL Column

If you specify that a column will allow null values, during data entry, if you don't have a value for that column, you can leave its placeholder empty:

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

INSERT Persons(FirstName, LastName) -- The Gender is left out
VALUES(N'Martin', N'Binam');
GO
INSERT Persons(LastName, Gender) -- The FirstName is left out
VALUES(N'Howley', 2);
GO
INSERT Persons(LastName) -- The FirstName and the Gender are left out
VALUES(N'Crouch');
GO

When performing data entry, if the table has columns that allow nulls and whenever you don't have a value for a null column, you should provide a list of the columns as see in the above examples. For the columns that are not included in the list, the database engine would automatically set their values to NULL. As an alternative, Transact-SQL allows you to keep the list of columns or to use any list of columns, including columns that allow null. Then, in the placeholder of a column, specify its value as NULL. Here are examples:

INSERT Persons -- All columns are used
VALUES(N'Alex', N'Hough', NULL);
GO
INSERT Persons(LastName, Gender, FirstName) -- The Gender will be set to null
VALUES(N'Kousseusseu', NULL, N'Tchipseu');
GO
INSERT Persons -- All columns are used
VALUES(NULL, N'Beltram', NULL),
      (NULL, N'Ohari', 1),
      (N'Jamrah', N'Belhassen', NULL);
GO

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, varchar(max), 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 NVARCHAR(50),
    Address NVARCHAR(80),
    City NVARCHAR(40),
    State NVARCHAR(40) DEFAULT L'NSW',
    PostalCode NVARCHAR(4) DEFAULT L'2000',
    Country NVARCHAR(20) DEFAULT L'Australia'
);
GO

When performing data entry on 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.

Author Note

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 LearningPractical Learning: Assigning a Default Value to a Column

  1. In the Object Explorer, right-click Databases and click Start PowerShell
  2. Type SQLCMD and press Enter
  3. Type USE master; and press Enter
  4. Type GO and press Enter
  5. To create a new database, type CREATE DATABASE CeilInn1 and press Enter
  6. Type ON PRIMARY and press Enter
  7. Type ( NAME = DataRepository, FILENAME = 'C:\Microsoft SQL Server Database Development\CeilInn1.mdf') and press Enter
  8. Type LOG ON and press Enter
  9. Type ( NAME = DataLog, FILENAME = 'C:\Microsoft SQL Server Database Development\CeilInn1.ldf'); and press Enter
  10. Type GO and press Enter
  11. To change the database, type USE CeilInn1; and press Enter
  12. Type GO and press Enter
  13. To create a schema, type CREATE SCHEMA Hotel; and press Enter
  14. Type GO and press Enter
  15. To create a new table with some columns that have default values, type the following and press Enter at the end:
    1> CREATE TABLE Hotel.Rooms
    2> (
    3>    RoomNumber nvarchar(10),
    4>    RoomType nvarchar(20) default N'Bedroom',
    5>    BedType nvarchar(40) default N'Queen',
    6>    Rate money default 75.85,
    7>    Available bit default 0
    8> );
    9> GO
  16. To perform data entry on the new table, type INSERT Hotel.Rooms(RoomNumber) VALUES(104); and press Enter
  17. Type GO and press Enter
  18. To add another record to the new table, type the following and press Enter at the end of each line:
    1> INSERT INTO Hotel.Rooms(RoomNumber, BedType, Rate, Available)
    2> VALUES(105, N'King', 85.75, 1),
    3>       (106, N'King', 85.75, 1);
    4> GO
  19. To add another record, type the following INSERT Hotel.Rooms(RoomNumber, Available) VALUES(107, 1); and press Enter
  20. Type GO and press Enter

Hotel Rooms

Forcing a Default Value During Data Entry

During programmatic data entry, if you don't list a column that has a default value, its default value would be assigned to the column. On the other hand, if you add such a column in the list of an INSERT statement, you must provide a value or give an empty value. Fortunately, Transact-SQL makes it possible to force the default value for the column. To do this, in the placeholder of the value of the column, use the DEFAULT keyword. Here is an example:

USE Exercise;
GO
CREATE TABLE Employees
(
    EmployeeNumber int,
    FirstName nvarchar(20),
    LastName nvarchar(20),
    HourlySalary money default 12.50
);
GO

INSERT INTO Employees
VALUES(28404, N'Amadou', N'Sulleyman', 18.85),
      (82948, N'Frank', N'Arndt', DEFAULT),
      (27749, N'Marc', N'Engolo', 14.50);
GO

In this example, the second record would receive the default value, which is 12.50. In the same way, you can force the default keyword for each column whose default value was indicated when the table was created.

Practical LearningPractical Learning: Assigning a Default Value to a Column

  1. To add a few records, type the following and press Enter after each line:
    1> INSERT Hotel.Rooms
    2> VALUES(108, N'King', default, 85.75, default),
    3>       (109, default, default, default, 1);
    4> GO
  2. To add one more record, type the following:
    1> INSERT Hotel.Rooms(RoomNumber, RoomType, BedType, Rate, Available)
    2> VALUES(110, N'Conference', N'', 450.00, 1)
    3> GO
  3. Return to Microsoft SQL Server Management Studio

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 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 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 on 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 Specification property. The first action you should take is to set its (Is Identity) property from No to Yes.

The Seed of an Identity Column

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.

The Identity Increment

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 Increment property.

Practical LearningPractical Learning: Creating an Identity Column

  1. In the Object Explorer, under WorldStatistics2, right-click Tables and click New Table...
  2. Set the name of the column to ContinentID and press Tab
  3. 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
  4. Complete the table as follows:
     
    Column Name Data Type Allow Nulls
    ContinentID    
    Continent nvarchar(80) Unchecked
    Area bigint  
    Population bigint  

    Creating an Identity Column

  5. To save the table, on the Standard toolbar, click the Save button
  6. Set the name of the table to Continents
  7. Click OK
  8. Close the Continents window
  9. Close the Countries window
  10. Return to the PowerShell window

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 nvarchar(50),
    [Item Name] nvarchar(100) NOT NULL,
    Size varchar(20),
    [Unit Price] money);
GO

Data Entry With an Identity Column

After creating an identity column, when performing data entry, don't specify a value for that column. Here is an example:

USE Exercise;
GO

CREATE TABLE StoreItems
(
	ItemID int identity(1, 1) NOT NULL, 
	Category nvarchar(50),
	[Item Name] nvarchar(100) NOT NULL,
	Size nvarchar(20),
	[Unit Price] money
);
GO

INSERT INTO StoreItems(Category, [Item Name], Size, [Unit Price])
VALUES(N'Men', N'Simplicity Leather Dress Oxfords', N'9', 65.85);
GO

Identity Column

If you provide a value for the identity column, you would receive an error:

Identity Column

Creating a Value for an Identity Column

If you want to specify a value for the identity column, call the SET IDENTITY_INSERT flag. The formula it uses is:

SET IDENTITY_INSERT [ database_name.[schema_name].] table { ON | OFF }

The database_name is the optional name of the database that owns the table. If you previously use the USE statement, in most cases, you can omit the name of the database. The schema_name is the (optional) name of the schema in which the table was created. The table factor is the name of the table the identity column belongs to. After specifying the name of the table, set the flag as ON to allow a value for the identity column, or OFF to disallow it.

If you decide to use the SET IDENTITY_INSERT, you must provide a list of columns after the name of the table in the INSERT or INSERT INTO statement. Here is an example:

USE Exercise1;
GO

SET IDENTITY_INSERT StoreItems ON;
GO
INSERT INTO StoreItems(ItemID, Category, [Item Name], Size, [Unit Price])
VALUES(10, N'Girls', N'Girls 2-6X Short-Sleeved Mesh Polo Shirt', N'2T', 18.95);
GO

This time, the data entry would not produce an error.

As mentioned already, after setting the IDENTITY_INSERT ON, you can put it back OFF and add a record without a value for the identity column. Here is an example:

USE Exercise1;
GO

SET IDENTITY_INSERT StoreItems ON;
GO
INSERT INTO StoreItems(ItemID, Category, [Item Name], Size, [Unit Price])
VALUES(50, N'Men', N'Simplicity Leather Dress Oxfords', N'10.5', 65.85);
GO
SET IDENTITY_INSERT StoreItems OFF;
GO
INSERT INTO StoreItems(Category, [Item Name], Size, [Unit Price])
VALUES(N'Women', N'Fiona High Heel Boot', N'6.50', 295.00);
GO
INSERT INTO StoreItems(Category, [Item Name], Size, [Unit Price])
VALUES(N'Women', N'Saddle East/West Leather Hobo Bag', N'Medium', 345.50);
GO

If you do this, the next value of the identity column would be the increment from the previous value.

Practical LearningPractical Learning: Creating an Identity Column Using SQL

  1. Type DROP TABLE Hotel.Rooms; and press Enter
  2. Type GO and press Enter
  3. To create a table with an identity column, type the following and press Enter after each line:
    1> CREATE TABLE Hotel.Rooms
    2> (
    3>    RoomID int identity(1, 1) NOT NULL,
    4>    RoomNumber nvarchar(10),
    5>    RoomType nvarchar(20) default N'Bedroom',
    6>    BedType nvarchar(40) default N'Queen',
    7>    Rate money default 75.85,
    8>    Available bit default 0
    9> );
    10> GO
  4. To perform data entry on the new table, type INSERT INTO Hotel.Rooms(RoomNumber) VALUES(104); and press Enter
  5. Type GO and press Enter
  6. To add two other records to the new table, type the following and press Enter after each line:
    1> INSERT INTO Hotel.Rooms(RoomNumber, BedType, Rate, Available)
    2> VALUES(105, N'King', 85.75, 1),
    3> 	 (106, N'King', 85.75, 1);
    4> GO
  7. To add other records, type the following and press Enter at the end of each line:
    1> INSERT Hotel.Rooms(RoomNumber, RoomType, BedType, Rate, Available)
    2> VALUES(107, default, default, default, 1),
    3>       (108, N'King', default, 85.75, default),
    4>       (109, default, default, default, 1);
    5> GO
  8. To add one more record, type the following and press Enter after each line:
    1> INSERT INTO Hotel.Rooms(RoomNumber,
    2>		           RoomType,
    3>			   BedType,
    4>			   Rate,
    5>			   Available)
    6> VALUES(110, N'Conference', N'', 450.00, 1);
    7> GO

Hotel Rooms

 
 
 
.

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 can be uniquely identified. 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.

Visually Creating a Uniqueness Rule

To visually specify that each value of a column must be unique, in the Table window, right-click the desired column and click Indexes/Keys... In the Indexes/Keys dialog box, click Add. On the right side, set the Is Unique field to Yes:

Uniqueness

After doing this, click Close.

Programmatically Creating a Uniqueness Rule

To assist you with creating a columns whose values will be distinguishable, Transact-SQL provides the UNIQUE keyword. 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 LearningPractical Learning: Applying Uniqueness to a Column

  1. In the PowerShell window, to create a new table that has a uniqueness rule on a column, type the following and press Enter after each line:
    1> CREATE TABLE Hotel.Customers
    2> (
    3>    CustomerID int identity(1, 1) NOT NULL,
    4>    AccountNumber nchar(10) UNIQUE,
    5>    FullName nvarchar(50)
    6> );
    7> GO
  2. To perform data entry on the table, type the following and press Enter at the end of each line:
    1> INSERT INTO Hotel.Customers(AccountNumber, FullName)
    2> VALUES(395805, N'Ann Zeke'),
    3>	 (628475, N'Peter Dokta'),
    4>	 (860042, N'Joan Summs');
    5> GO
  3. To try adding another record to the table, type the following and press Enter at the end of each line:
    1> INSERT INTO Hotel.Customers(AccountNumber, FullName)
    2> VALUES(628475, N'James Roberts');
    3> GO
  4. Notice that you receive an error

    Error

  5. Type Quit and press Enter
  6. Type Exit and press Enter to return to Microsoft SQL Server

Other Features of Records

 

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

Data Entry and Functions

 

Introduction

You can involve a function during data entry. As an example, you can call a function that returns a value and 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. 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 spare you the trouble of writing your own function.

Using Functions

Imagine you have a database named AutoRepairShop and 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 SYSDATETIME() or 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.

Practical LearningPractical Learning: Introducing Data Entry and Functions

  1. On the Standard toolbar, click the New Query button
  2. In the Query window, type the following:
    -- =============================================
    -- Database:     BankOne
    -- Author:       FunctionX
    -- Date Created: Saturday 03 July 2010
    -- =============================================
    USE master
    GO
    
    CREATE DATABASE BankOne
    GO
    
    USE BankOne
    GO
    
    CREATE USER Orlando FOR LOGIN operez;
    GO
    CREATE USER Ray FOR LOGIN rkouma;
    GO
    
    CREATE TABLE Employees
    (
        EmployeeNumber nchar(10),
        FirstName nvarchar(20),
        MI nchar(2), -- Middle Initial
        LastName nvarchar(20) not null,
        Title nvarchar(50),
        Username nvarchar(20),
        UserPassword nvarchar(20),
        EmailAddress nvarchar(100),
        CONSTRAINT PK_Employees PRIMARY KEY (EmployeeNumber)
    );
    GO
    
    GRANT INSERT, UPDATE, SELECT, DELETE
    ON OBJECT::dbo.Employees 
    TO Ray
    WITH GRANT OPTION; -- Raymong Kouma will be the main administrator of
    		   -- this database with the ability to grant rights to other users
    GO
    
    -- Give Orlando Perez the ability to create employees
    GRANT SELECT, INSERT
    ON OBJECT::dbo.Employees
    TO Orlando;
    GO
  3. Press F5 to execute
  4. Click somewhere inside the Query window and press Ctrl + A
  5. To create a test function, type the following:
    USE BankOne;
    GO
    
    CREATE FUNCTION CreateUsername(@fname nvarchar(20),
                                   @lname nvarchar(20),
                                   @initial nchar(2) = N'')
    RETURNS nvarchar(14)
    AS
    BEGIN
        RETURN REPLACE(LOWER(LEFT(@fname, 1) + @initial + @lname), N'', N'');
    END;
    GO
  6. Press F5 to create the function

Data Entry, Functions, and Permissions

You should always pay close attention to how you manage rights for data entry. You may know already that you must grant the INSERT permission on a table to any user who will create records. The involvement of functions brings its own set of issues. Remember that, if you want a user to be able to call a function, you must grant him the EXECUTE permission. On the other hand, if you want to prevent a person from using a function, you should deny that right.

When a function is involved in the data entry of a table, you must grant the EXECUTE right on that function to any user who will be asked to enter values.

Practical LearningPractical Learning: Performing Data Entry

  1. Click inside the Query window and press Ctrl + A
  2. Type the following code:
    USE BankOne;
    GO
    
    INSERT INTO Employees(EmployeeNumber, FirstName, LastName, Username)
    VALUES(N'825-937', N'Nichole', N'Rowlands',
           dbo.CreateUsername(N'Nichole', N'Rowlands', default));
    GO
    
    INSERT INTO Employees(EmployeeNumber, FirstName, MI, LastName, Username)
    VALUES(N'440-117', N'Robert', N'P', N'Noble', 
           dbo.CreateUsername(N'Robert', N'Noble', N'P')),
          (N'928-428', N'James', N'D', N'Beaning',
           dbo.CreateUsername(N'James', N'Beaning', N'D'));
    GO
  3. Press F5 to execute
  4. Click inside the Query window and press Ctrl + A
  5. Type the following code:
    USE BankOne;
    GO
    
    GRANT EXECUTE 
    ON OBJECT::dbo.CreateUsername
    TO Ray;
    GO
    
    DENY EXECUTE
    ON OBJECT::dbo.CreateUsername
    TO Orlando;
    GO
  6. Press F5 to execute
  7. In the Object Explorer, click Connect and click Database Engine...
  8. Set the Authentication to SQL Server Authentication and press Tab
  9. Set the Login name to rkouma and press Tab
  10. Enter the password as P@ssword1
  11. Click Connect
  12. In the Object Explorer, under ComputerName (SQL Server ... - rkouma), expand Databases
  13. Right-click  BankOne and click New Query
  14. To create two records, type the following:
    USE BankOne;
    GO
    
    INSERT INTO Employees(EmployeeNumber, FirstName, LastName, Username)
    VALUES(N'975-026', N'Ernest', N'Jones',
           dbo.CreateUsername(N'Ernest', N'Jones', default));
    GO
    
    INSERT INTO Employees(EmployeeNumber, FirstName, MI, LastName, Username)
    VALUES(N'939-415', N'Benjamin', N'T', N'Edwards',
           dbo.CreateUsername(N'Benjamin', N'Edwards', N'T'));
    GO
  15. Press F5 to execute
  16. In the Object Explorer, right-click ComputerName (SQL Server ... - rkouma) and click Disconnect
  17. In the Object Explorer, click Connect and click Database Engine...
  18. Make sure the Authentication is set to SQL Server Authentication.
    In the Login combo box, type operez and press Tab
  19. Enter the password as P@ssword1
  20. Click Connect
  21. In the Object Explorer, under ComputerName (SQL Server ... - operez), expand Databases
  22. Right-click  BankOne and click New Query
  23. To create a record, type the following:
    USE BankOne;
    GO
    
    INSERT INTO Employees(EmployeeNumber, FirstName, LastName, Username)
    VALUES(N'279-575', N'Mary', N'Biggs',
           dbo.CreateUsername(N'Mary', N'Biggs', default));
    GO
  24. Press F5 to execute.
    Notice that the user cannot perform data entry
    The EXECUTE permission was denied on the object 'CreateUsername', database 'BankOne', schema 'dbo'.
  25. In the Object Explorer, right-click ComputerName (SQL Server ... - operez) and click Disconnect
  26. In the Available Databases combo box of the Standard toolbar, select master
  27. In the Object Explorer, right-click BankOne and click Delete
  28. In the Delete Object dialog box, click OK
  29. In the Object Explorer, right-click WorldStatistic2 and click Delete
  30. Click OK
  31. Close Microsoft SQL Server

Exercises

   

Lesson Summary Questions

  1. If you add an expression to a column but want the value to be stored in the table, what keyword would you add to the column?
    1. PERSISTED
    2. DEFAULT
    3. PERSISTANT
    4. EXPRESSION
    5. SERIALIZE
  2. Which of the following is a valid way to create an expression?
    1. CREATE TABLE Circle
      (
          CircleID int identity(1,1) NOT NULL,
          Radius decimal(8, 3) NOT NULL,
          Area DEFAULT Radius * Radius * PI()
      );
      GO
    2. CREATE TABLE Circle
      (
          CircleID int identity(1,1) NOT NULL,
          Radius decimal(8, 3) NOT NULL,
          Area = Radius * Radius * PI()
      );
      GO
    3. CREATE TABLE Circle
      (
          CircleID int identity(1,1) NOT NULL,
          Radius decimal(8, 3) NOT NULL,
          Area AS Radius * Radius * PI()
      );
      GO
    4. CREATE TABLE Circle
      (
          CircleID int identity(1,1) NOT NULL,
          Radius decimal(8, 3) NOT NULL,
          Area(Radius * Radius * PI())
      );
      GO
    5. CREATE TABLE Circle
      (
          CircleID int identity(1,1) NOT NULL,
          Radius decimal(8, 3) NOT NULL,
          Area HAVING(Radius * Radius * PI())
      );
      GO
  3. Which of the following is a valid way to specify the default value of a column?
    1. CREATE TABLE Students
      (
          FirstName nvarchar(20),
          LastName nvarchar(20),
          Gender DEFAULT = N'Unknown'
      );
      GO
    2. CREATE TABLE Students
      (
          FirstName nvarchar(20),
          LastName nvarchar(20),
          Gender SET DEFAULT = N'Unknown'
      );
      GO
    3. CREATE TABLE Students
      (
          FirstName nvarchar(20),
          LastName nvarchar(20),
          Gender WITH DEFAULT AS N'Unknown'
      );
      GO
    4. CREATE TABLE Students
      (
          FirstName nvarchar(20),
          LastName nvarchar(20),
          Gender nvarchar(20) DEFAULT N'Unknown'
      );
      GO
    5. CREATE TABLE Students
      (
          FirstName nvarchar(20),
          LastName nvarchar(20),
          Gender nvarchar(20),
          CONSTRAINT D_Gender DEFAULT(N'Unknown')
      );
      GO
  4. Which of the following codes will create a column that uses an expression?
    1. CREATE TABLE Employees
      (
          FirstName nvarchar(20),
          LastName nvarchar(20),
          FullName = FirstName + N' ' + LastName
      );
    2. CREATE TABLE Employees
      (
          FirstName nvarchar(20),
          LastName nvarchar(20),
          FullName AS FirstName + N' ' + LastName
      );
    3. CREATE TABLE Employees
      (
          FirstName nvarchar(20),
          LastName nvarchar(20),
          FullName SET AS FirstName + N' ' + LastName
      );
    4. CREATE TABLE Employees
      (
          FirstName nvarchar(20),
          LastName nvarchar(20),
          FullName nvarchar(41) WITH FullName = FirstName + N' ' + LastName
      );
    5. CREATE TABLE Employees
      (
          FirstName nvarchar(20),
          LastName nvarchar(20),
          FullName nvarchar(41),
          CONSTRAINT E_FullName EXPRESSION(FullName = FirstName + N' ' + LastName)
      );
  5. Which of the following are keywords in Transact-SQL?
    1. EACH
    2. PRESERCE
    3. DEFAULT
    4. UNIQUE
    5. DO
  6. What's the name of the permission that allows a user to successfully call a function?
    1. SELECT
    2. CALL
    3. PRODUCE
    4. GET
    5. EXECUTE
  7. Which of the following two are permissions of a function (Select 2)?
    1. DELETE
    2. STORE
    3. ALTER
    4. CREATE
    5. REFERENCES
  8. Which of the following three are permissions of a function (Select 3)?
    1. CONTROL
    2. TAKE OWNERSHIP
    3. INSERT
    4. VIEW DEFINITION
    5. UPDATE

Answers

  1. Answers
    1. Right Answer: The PERSISTED keyword asks the database engine to save the value of a computed column
    2. Wrong Answer: The DEFAULT keyword has nothing to do with storing a
    3. Wrong Answer: There is no PERSISTANT keyword in Transact-SQL
    4. Wrong Answer: There is no EXPRESSION keyword in Transact-SQL
    5. Wrong Answer: There is no SERIALIZE keyword in Transact-SQL
  2. Answers
    1. Wrong Answer: The DEFAULT keyword is not used
    2. Wrong Answer: The assignment operator cannot be used
    3. Right Answer: That will work
    4. Wrong Answer: The parentheses are not used
    5. Wrong Answer: There is no place for HAVING here
  3. Answers
    1. Wrong Answer: You don't assign the value
    2. Wrong Answer: The SET keyword is not used
    3. Wrong Answer
    4. Right Answer: After the DEFAULT keyword, simply provide the desired value
    5. Wrong Answer
  4. Answers
    1. Wrong Answer: The assignment operator is not valid
    2. Right Answer: The FullName column will combine the first name, a space, and the last name for its value
    3. Wrong Answer: The SET AS expression has no place here
    4. Wrong Answer: The WITH keyword in not valid in this context
    5. Wrong Answer: There is no reason to use HAVING in this type of expression
  5. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Right Answer
    4. Right Answer
    5. Wrong Answer
  6. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Right Answer
    4. Wrong Answer
    5. Right Answer
  7. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Right Answer
    4. Wrong Answer
    5. Right Answer
  8. Answers
    1. Right Answer
    2. Right Answer
    3. Wrong Answer
    4. Right Answer
    5. Wrong Answer
 
 
   
 

Previous Copyright © 2009-2011 FunctionX.com Next