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:
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
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 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.
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:
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, 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. 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:
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.
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
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.
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:
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.
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.
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:
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.
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.
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 Increment property.
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
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
If you provide a value for the identity column, you would receive an error:
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
Learning: Introducing Expressions
