Transact-SQL Data Types
Transact-SQL Data Types
A Re-Introduction to Tables
The Schema of a Table
In our first introduction to tables, we saw that the primary formula to create a table is:
CREATE TABLE table-name(. . .)
When creating a table, you may want it to belong to a certain schema. To specify the schema of a table when creating a table using code, precede its name with the name of the schema followed by a period. When it comes to schemas, the primary formula to create a table is:
CREATE TABLE [schema-name.]table-name(. . .)
An example is:
CREATE SCHEMA Registration; GO CREATE TABLE Registration.Students . . .
When creating a table, if you don't specify a particular schema, the default dbo schema takes ownership of the table.
Referring to a Table
After creating a schema, you can create tables in it. After creating a table, to refer to the table in an expression, if you didn't create the table in a particular schema:
If you add created the name in a specific schema:
Visually Creating a Table Locally
To visually create a table, in the Object Explorer of Microsoft SQL Server Management Studio, expand the database. Right-click the Tables node -> New -> Table... To visually create a tableclick Server Explorer, expand the connection, right-click Tables and click Add New Table. A new window that will call the Table window will display. You can then add the necessary options.
After visually creating a table, to let you add it to the database, the toolbar of the Table window is equipped with the Update button 
. Click that button to actually create the table. If there is a problem, the bottom section will display an error. Otherwise, if everything is alright, the table will be created and its name will be added to the Tables node in the Server Explorer.
Practical Learning: Introducing Databases
CREATE SCHEMA Accessories;
GOTable Maintenance
Tables Review
To get the list of tables of a database, execute sp_help (it is a stored procedure). To do this, in a Query window, type sp_help and click the Execute button 
.
Renaming a Table
If you find out that the name of a table is not appropriate, you can change it. To change the name of a table with code, execute sp_rename, followed by the current name of the table, a comma, and the new desired name of the table. The formula to use is:
sp_rename ExistingTableName, TableNewName;
The names of tables should be included in single-quotes. Here is an example:
sp_rename 'StaffMembers', 'Employees'; GO
In this case, the SQL interpreter would look for a table named StaffMembers in the current or selected database. If it finds it, it would rename it Employees. If the table does not exist, you would receive an error.
Deleting a Table
If you have an undesired table in a database, you can remove it. To visually delete a table, in the Server Explorer, right-click the table and click Delete
You will receive a warning giving you a chance to confirm your intentions. If you still want to remove the table, click OK. To delete a table using SQL, use the following formula:
DROP TABLE table-name
The DROP TABLE expression is required and it is followed by the name of the undesired table.
Introduction to the Columns of a Table
Adding a Column to a Table
In our first introduction to tables, we saw how to add columns, as categories of data, to a table. The primary formula we saw was:
CREATE TABLE table-name(column_1, column_2, column_n)
If applying a schema to a table, the primary formula to create a table is:
CREATE TABLE [schema-name.]table-name(column_1, column_2, column_n)
In this case, we included all columns on the same line. Remember that, to make your code easy to read, you can put each column creation on its own line:
CREATE TABLE [schema-name.]table-name
(
column_1,
column_2,
column_n
)
The Type of a Column
Remember that the creation of a column uses the following formula:
column-name data-type options
This means that you start with a name for the column. The name can be included in []. The name is followed by a type of value is case-insensitive. This means that you can use VARCHAR, varchar, and VarChar are the same.
Referring to a Column
To refer to a column in an expression, you have various options:
Columns Maintenance
Introduction
Column maintenance consists of reviewing or changing any of its aspects. This includes reviewing the structure of columns of a table, renaming a column, deleting a column, changing the data type or the nullity of a column, etc.
The Collation of a Column
Because different languages use different mechanisms in their alphabetical characters, this can affect the way some algorithms are performed on data, you can ask the database engine to apply a certain language mechanism to the field by changing the Collation property.
To specify the collation option, when programmatically creating the table, type COLLATE, followed by the desired collation code. Here is an example:
CREATE TABLE Customers(
FullName varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
);
Modifying a Column
When making a change on a column, you are also said to alter the table. To programmatically support this operation, the SQL provides a formula that starts as follows:
ALTER TABLE table-name
When using this statement, the ALTER TABLE expression is required and it is followed by the name of the table.
Adding a New Column
After a table has already been created, you can still add a new column to it. In SQL, the basic formula to add a new column to an existing table is:
ALTER TABLE table-name ADD ColumnName Properties
The column-name factor is required. In fact, on the right side of the ADD operator, define the column by its name and use all the options we reviewed for columns. Here is an example:
ALTER TABLE Customers
ADD EmaillAddress varchar(100);
GO
When this code is executed, a new column named Address, of type varchar, with a limit of 100 characters, that allows empty entries, will be added to a table named StaffMembers in the current database.
Renaming a Column
If you find out that the name of a column is not appropriate, you can change it. To rename a columns, execute sp_rename using the following formula:
sp_rename 'table-name.column-name', 'NewColumnName', 'COLUMN'
The sp_rename factor and the 'COLUMN' string are required. The table-name factor is the name of the table that the column belongs to. The column-name 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 executes, the interpreter looks for a column named FullName in the StaffMembers table of the current or selected database. If it finds that column in the table, it renames it EmployeeName.
Deleting a Column
If you have an undesired column that you don't want anymore in a table, you can remove it. To programmatically delete a column, use the following formula:
ALTER TABLE table-name DROP COLUMN column-name
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 Customers DROP Column DateIssued;; GO
When this code executes, the interpreter looks for a column named CurrentResidence in a table named StaffMembers of the current. If it finds that column, it will remove it from the table.
A Re-Introduction to Data Entry
Visual Data Entry
A table is an object that holds the data of a database. You can add records to a table visually or using code. To open a table for visual data entry:
In the window that appears, click a cell under a column and type the desired value
SQL Data Entry
To perform data entry using SQL, as seen in previous lessons, use the INSERT...VALUES formula:
INSERT table-name VALUES(column_1, column_2, column_n);
Inserting INTO a Table
Alternatively, or to be more precise, you can use the INTO keyword between the INSERT keyword and the table-name. This is done with the following formula:
INSERT INTO table-name VALUES(Column1, Column2, Column_n)
The table-name must be a valid name of an existing table in the database you are using. If the table was created in a particular schema, you must precede the name of the table with the schema. If the schema or the name of the table is wrong or they don't exist, you would receive an error.
The VALUES keyword is required. The values of the columns must be included in its parentheses.
Character-Based Columns
Introduction
A column can be made to receive a character or symbol. The character can be of any kind of alphabetical symbol, readable or not. To let you create a column where the records will hold a character, the SQL provides a data type named CHAR. As an alternative, you can use the VARCHAR data type. Here are examples:
CREATE TABLE Seminars
(
EventCode CHAR,
OccurrenceType VARCHAR
);
GO
Remember that you can include the name of a column (as well as the name of a table) between square brackets. Here is an example:
CREATE TABLE Seminars
(
[EventCode] CHAR,
OccurrenceType VARCHAR
);
GO
If you anticipate that the values of the column may use ASCII characters or Unicode (or international characters, or characters other than US English), precede the character type with N, as in NCHAR or NVARCHAR.
Remember that the SQL is not case-sensitive. Therefore, you can specify the data type in lowercase, as in char, nchar, varchar, or nvarchar.
Data Entry for Character-Based Columns
If the data type of a column is a character (CHAR, VARCHAR, NCHAR, or NVARCHAR), to specify its value in SQL, type that character in single-quotes. If you provide more than one character, you would receive an error and the record would not be saved.
Data Entry for Unicode Characters
We saw that Microsoft SQL Server supports Unicode characters using the NCHAR and the NVARCHAR() data type. If you are providing a value for such a column, you can precede the first single-quote with N, but this is only an option.
CREATE TABLE Presidents
(
FirstName nvarchar(24),
LastName nvarchar(24)
);
GO
INSERT INTO Presidents VALUES(N'Julius', N'Nyerere');
GO
If you apply any of the variants of the char data type, the field can store up to 231 characters.
String-Based Columns
Introduction
A string is a character or a combination of characters. To let you create a column whose records hold strings, the SQL and Transact-SQL provide the CHAR(), the VARCHAR(), the NCHAR(), and the NVARCHAR() data type. You must specify the maximum number of charactersfor the column. This is done in the parentheses of the data type.
If you are creating a model for a table, in the Designer, click the name of the column. In the Properties window, click the Max Length field and type the desired value.
Practical Learning: Creating a Table
CREATE SCHEMA Accessories;
GO
CREATE TABLE Accessories.MeterCounter
(
MeterNumber NCHAR(7),
Make NVARCHAR(40),
Model nvarchar(30),
MeterSize nvarchar(20)
);
GO
or Long Text-Based Columns
If you want a column that can hold long pieces of text, set its data type as either varchar() or nvarchar() and specify the length using the max keyword. Here is an example:
CREATE TABLE Sports
(
SportName nvarchar(50),
[Description] nvarchar(max)
);
GO
In this case, varchar(max) or nvarchar(max) is called a large-value data type (Transact-SQL originally supported a data type named text (and ntext for Unicode) used for long text).
Practical Learning: Creating a Column for Long Text
CREATE SCHEMA Personel; GO CREATE TABLE Personel.Contractors ( FirstName NVARCHAR(25), LastName NVARCHAR(25), Biography nvarchar(max) ); GO
or Data Entry for String-Based Columns
To provide a value for a string-based column, in SQL, include the value in single quotes. If the string may contain Unicode characters, precede the first single-quote with N. Here are examples:
CREATE TABLE Presidents
(
FirstName nvarchar(24),
LastName nvarchar(24)
);
GO
INSERT INTO Presidents VALUES(N'Julius', N'Nyerere');
GO
A string-based column can store up to 231 characters.
Fundamentals of String-Based Functions
Introduction
The string is the most basic or the primary value that is presented to a database. To assist you with managing strings or performing operations on them, Transact-SQL provides various functions. Some of them use names similar to those of the String class of the .NET Framework. Most, if not all, functions behave like those of the String class.
The Length of a String
The length of a string is the number of characters or symbols it contains. To let you get the length of a string, Transact-SQL provides the LEN() function. Its syntax is:
int LEN(String)
This function takes one argument as the string to be considered. It returns the number of characters in the string.
Concatenating Two Strings
Concatenating two strings consists of adding one string to the other. You can do this using the addition operator '+'. Here are examples:
DECLARE @FirstName nvarchar(24),
@LastName nvarchar(24),
@FullName nvarchar(50),
@CompleteName nvarchar(50);
SET @FirstName = N'Jerome ';
SET @LastName = N'Ansfield';
SET @FullName = @FirstName + @LastName;
SET @CompleteName = N'Anne ' + N'Sanders';
SELECT N'Jeffrey ' + N'Erschbamer';
SELECT @FullName [Full Name];
SELECT @CompleteName [Complete Name];
GOIn the same way, you can concatenate various strings by using the addition operator between them.
Transact-SQL provides a function that performs string concatenation. It is named CONCAT and its syntax is:
CONCAT(string string_value1,
string string_value2
[, string string_value_N]) RETURNS string;
This function taks an unlimited number of characters and/or strings as arguments and behaves like the String.Concat() method.
To let you convert all characters of a string to lowercase, Transact-SQL provides the LOWER() function. Its syntax is:
LOWER(String) RETURNS varchar;
This function takes a character or a string as argument. This function behaves like the String.ToLower() method except that the value or expression is passed as argument.
Sub-Strings
The Starting Characters of a String
A left sub-string is one or a group of characters retrieved from the left side of a known string. To let you get the left sub-string of a string, Transact-SQL provides the LEFT() function. Its syntax is:
LEFT(String, NumberOfCharacters) RETURNS varchar
This function takes two arguments. The first argument specifies the original string. The second argument specifies the number of characters from the most-left that will constitute the sub-string. After the operation, the LEFT() function returns a new string made of the left character + the NumberOfCharacters on its right from the String. This function works like the String.SubString(int startIndex) method.
The Ending Characters of a String
To let you get the most-right characters of an existing string, Transact-SQL provides the RIGHT() function. Its syntax is:
RIGHT(String, NumberOfCharacters) RETURNS varchar;
This function takes two arguments. The first argument specifies the original string. The second argument specifies the number of characters from the most-right side.
Replacing Occurrences in a String
To let you replace a character or a sub-string from a string, Transact-SQL provides the REPLACE() function. Its syntax is:
REPLACE(String, FindString, ReplaceWith) RETURNS varchar;
or
REPLACE(String, FindString, ReplaceWith) RETURNS binary;
This function takes three arguments. This function is equivalent to the String.Replace() method that takes two argument. The first argument of the REPLACE() function is the string that will be used as reference. This argument is equivalent to a C# string variable (or expression) that calls the String.Replace() method. The second and the third arguments of the REPLACE() function (FindString and ReplaceWith) are the same as the arguments of the String.Replace() method.
In Transact-SQL and Microsoft Windows, there is a default way that each type of value should display. To assist you in specifying a customized way to display a value, Transact-SQL provides the FORMAT() function. Its syntax is:
FORMAT(Type value, nvarchar format [, nvarchar culture ] ) RETURNS nvarchar
This function is equivalent to a method of the same name in the String class of the .NET Framework. The main difference is that, in a C# application, the string.Format() method is called as a static method (which means the method must be qualified by its class) but in Transact-SQL, the function is called directly.
Introduction to Metadata Functions
A metadata is a piece of information held by an object. Microsoft SQL Server (or rather Transact-SQL) provides many functions that use the concept of metadata to provide information about many objects of a database or of a database server.
Getting the Identifier of an Object
A database contains many types of objects such as tables, etc. When you create one of them, the database engine creates an internal identifier for it (and stores that name somewhere). This allows the database engine to identify all objects stored in all databases. To let you know the identifier of an object, Transact-SQL provides a function named OBJECT_ID. Its syntax is:
OBJECT_ID('[ database_name . [ schema_name ] . | schema_name . ]
object_name' [ ,'object_type' ]);
This function takes two arguments that are passed as strings. The first argument is required and the second is optional. The first argument, object_name, is the name of an object such as a table.
If the object belongs to a schema (other than dbo), precede its name with it. You can also completely qualify the name of the object by preceding the schema with the name of its database.
The second argument of the OBJECT_ID() function allows you to specify the type of object of the object_name argument. It is passed as one or two letters depending on the object. For a table, the argument is passed as 'U'.
Integer-Based Columns
Regular Integers
Like C#, the SQL supports integers. If a column would use natural numbers in the range of -2,147,483,648 to 2,147,483,647, to let you create such a column, the SQL propvides two data types named INTEGER and DECIMAL. In Transact-SQL, you can also use the INT data type.
If you are creating a model and want to apply an integer to a column, select that column in the Designer. In the Properties window, click the Type field, then click the arrow of its combo box and select Int32.
If you want a column to use very small numbers, apply the TINYINT data type to such a column. A column with the tinyint data type can hold positive numbers that range from 0 to 255.
The SMALLINT data type follows the same rules and principles as the C#'s short data type to store numbers that range between -32,768 and 32,767.
If you are creating a model and want to apply a small integer to a column, select that column in the Designer. In the Properties window, select Int16 in the Type field.
To create a column that can hold large numbers from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807, apply the BIGINT data type to it. This type follows the same rules and principles as the C#'s long data type.
If you are creating a model and want to apply a large integer to a column, select that column in the Designer. In the Properties window, select Int64 in the Type field.
Practical Learning: Creating Integer-Based Columnt
CREATE SCHEMA Personel; GO CREATE TABLE Personel.Contractors ( EmployeeNumber INTEGER, FirstName NVARCHAR(25), LastName NVARCHAR(25), Biography nvarchar(max), MaritalStatus TINYINT, Exemptions SMALLINT ); GO
or Data Entry for an Integral Column
If a column is a natural numeric type (INT, INTEGER, BIGINT, SMALLINT, or TINYINT), simply provide a valid natural number for it.
Remember that you can also use the DECIMAL data type for natural numbers. In this case, provide the value as a natural number. Here are examples:
CREATE TABLE Distances
(
FromCity nvarchar(40),
ToCity nvarchar(40),
Distance decimal
);
GO
INSERT INTO Distances VALUES(N'Baltimore, MD', N'Richmond, VA', 130);
GO
INSERT INTO Distances VALUES(N'New York, NY', N'Las Vegas, NV', 2232);
GO
Binary Integers
Introduction
The binary data type is used for a variable that would hold hexadecimal numbers. Use the binary data type if all values of the variable would have the exact same length (or quantity).
If you anticipate that some entries would be different than others, then use the alternative varbinary data type. The varbinary type also is used for hexadecimal numbers but allows dissimilar entries, as long as all entries are hexadecimals.
Data Entry for Binary Integers
When providing the value of a column that is of binary type, type its as a normal integer.
A Column for a Little Bit
Like C#, the SQL supports Boolean values. In this case, a column can have a value considered true or false. No other value is allowed. Unlike C#, the SQL doesn't have a direct Boolean data type. Instead, to support Boolean values, the SQL provides a data type named BIT.
Visual Data Entry on Bit Values
When creating a record for a column that uses a BIT data type, in the Query window, you can specify the value as true (case-insensitive), false (case-insensitive), 1 (which is the same as True), or 0 (which is the same as False). No other string or number is allowed.
SQL Data Entry for a Bit Type
The SQL supports the Boolean type through the BIT data type, which is an integer. Therefore, to set the value of this type in SQL, specify it as 0 for False or any number (positive or negative) for True. Here are examples:
CREATE TABLE Employees ( EmployeeNumber NCHAR(6), IsFullTime BIT ); GO INSERT Employees VALUES(N'28-400', 0); GO INSERT Employees VALUES(N'99284', 1); GO INSERT Employees VALUES(N'72844', -586); GO INSERT Employees VALUES(N'40-050', 82605); GO
Alternative Bits
The BIT data in SQL is primarily a natural number. When you specify the value of its column, behind the scense, the database engine converts the value appropriately. Because the SQL doesn't have a data type that strictly or directly corresponds to a Boolean type, you can use alternatives to the BIT data type. One choice is to use an integer type such as an INT, an INTEGER, or a TINYINT. Another option is to use a string-based type. Here is an example:
CREATE TABLE Contractors
(
EmployeeNumber NCHAR(6),
IsFullTime NVARCHAR(6)
);
This time, when creating a form for the table, you can create a group of radio buttons or a combox. This allows you to restrict the options on the values the user can provide for the column since the user can only select from the available options. In this case, you can provide just two options as True and False, or On and Off.
Decimal Number-Based Columns
Introduction
Like C#, Transact-SQL suports decimal numbers with single or double-precistion. If you want to use a column whose values will be decimal numbers, specify its data type as numeric or decimal.
Transact-SQL supports decimal numbers whose precision is not particularly important. To create a column for such values, set its data type as either FLOAT or REAL.
The Precision of a Decimal Number
A precision is the number of digits used to represent a numeric value. For example, the number 42005 has a precision of 5, while 226 has a precision value of 3. If the data type is specified as an integer (the int and its variants) or a floating-point number (float and real), the precision is fixed by the database and you can just accept the value set by the Microsoft SQL Server interpreter. For a decimal number (decimal or numeric data types), Microsoft SQL Server allows you to specify the amount of precision you want. The value must be an integer between 1 and 38.
The Scale of a Decimal Number
The scale of a number is the number of digits on the right side of the period (or the character set as the separator for decimal numbers). The scale is used only for numbers that have a decimal part, which includes decimals (numeric and decimal). If a column is created with the decimal or numeric data type, you can specify the amount of scale you want. The value must be an integer between 0 and 18.
Data Entry for Floating-Point Numbers
If you create a column that would use decimal numbers (FLOAT, REAL, DECIMAL, or NUMERIC), to specify the value of the column, you have two options. If the value needs or uses a precision, include the decimal separator (the period for US English) and the precision. Here are examples:
/*
Imagine a credit counseling company that assists people
with some financial discipline issues.
This table is used to keep track of credit card rates
and certificates of deposits rates of various banks to direct
customers as to where to apply to get good rates or better ROI.
*/
CREATE TABLE BanksRates
(
TypeOfCredit nvarchar(40),
InstitutionName nvarchar(50),
InterestRate decimal(6),
CompoundFrequency nvarchar(32)
);
GO
INSERT INTO BanksRates
VALUES(N'Certificate of Deposit', N'Leandro Investments', 15.984, N'Monthly');
GO
To specify the value of a scale-based decimal or numeric-based column, you can provide a fractional part to the value of the column. Here are examples:
DROP TABLE StudentsGradeScale; GO CREATE TABLE StudentsGradeScale ( LetterGrade nvarchar(2), MinRange decimal(5, 2), MaxRange decimal(5, 2), MinPercent decimal, MaxPercent decimal, Descriptor nvarchar(20) ); GO
If the type was created like an integer, when creating a record, if you specify a precision section for a value, the SQL interpreter would round the number. Consider the following examples:
CREATE TABLE StudentsGradeScale ( LetterGrade nvarchar(2), MinRange decimal, MaxRange decimal, MinPercent decimal, MaxPercent decimal, Descriptor nvarchar(20) ); GO INSERT INTO StudentsGradeScale(LetterGrade, MinRange, MinPercent, MaxPercent, Descriptor) VALUES(N'A', 4.0, 95, 100, N'Excellent'); GO INSERT INTO StudentsGradeScale VALUES(N'A-', 3.67, 3.99, 90, 94, N'Excellent'); GO INSERT INTO StudentsGradeScale VALUES(N'B+', 3.33, 3.66, 85, 89, N'Good'); GO INSERT INTO StudentsGradeScale VALUES(N'B', 3.0, 3.32, 80, 84, N'Good'); GO INSERT INTO StudentsGradeScale VALUES(N'B-', 2.67, 2.99, 75, 79, N'Good'); GO INSERT INTO StudentsGradeScale VALUES(N'C+', 2.33, 2.66, 70, 74, N'Satisfactory'); GO INSERT INTO StudentsGradeScale VALUES(N'C', 2.0, 2.32, 65, 69, N'Satisfactory'); GO INSERT INTO StudentsGradeScale VALUES(N'C-', 1.67, 1.99, 60, 64, N'Satisfactory'); GO INSERT INTO StudentsGradeScale VALUES(N'D+', 1.33, 1.66, 55, 59, N'Satisfactory'); GO INSERT INTO StudentsGradeScale VALUES(N'D', 1.0, 1.32, 50, 54, N'Satisfactory'); GO INSERT INTO StudentsGradeScale VALUES(N'F', 0, 0.99, 0, 49, N'Unsatisfactor'); GO
This would produce:
| Letter Grade | Min Range | Max Range | Min % | Max % | Descriptor |
| A | 4 | 95 | 100 | Excellent | |
| A- | 4 | 4 | 90 | 94 | Excellent |
| B+ | 3 | 4 | 85 | 89 | Good |
| B | 3 | 3 | 80 | 84 | Good |
| B- | 3 | 3 | 75 | 79 | Good |
| C+ | 2 | 3 | 70 | 74 | Satisfactory |
| C | 2 | 2 | 65 | 69 | Satisfactory |
| C- | 2 | 2 | 60 | 64 | Satisfactory |
| D+ | 1 | 2 | 55 | 59 | Satisfactory |
| D | 1 | 1 | 50 | 54 | Satisfactory |
| F | 0 | 1 | 0 | 49 | Unsatisfactor |
Currency Values
Introduction
If a column will hold monetary values, you can set its data type as MONEY or SMALLMONEY. A column with a money data type can hold positive or negative values from -922,337,203,685,477.5808 to +922,337,203,685,477.5807. While the money data type can be used for a column that would hold large quantities of currency values, the smallmoney data type can be applied for a column whose value cannot be lower than -214,748.3648 nor higher than 214,748.3647.
The precision and scale of a money or smallmoney column are fixed by Microsoft SQL Server. The scale is fixed to 4.
Monetary Values and Data Entry
If a column is made for currency values, if the value doesn't use a decimal portion, simply type it. If the number requires a fractional part, after the natural section, type the decimal separator and the appropriate number for the fraction. This is one of the types that highly depends on local rules (called culture). For example, in US English, the number after the decimal separator is between 0 and 99 and of course can use only two digits.
Date-Based Columns
Introduction
Both the SQL and Transact-SQL (Microsoft SQL Server databases) support date values. To assist you with date values, Transact-SQL provides the DATE data type. This data type counts dates starting from January 1st, 0001 up to December 31st, 9999. Therefore, to create a column that would hold date values, you can use the DATE data type. Here is an example:
CREATE SCHEMA Inventory;
GO
CREATE TABLE Inventory.StoreItems
(
ItemNumber INTEGER,
DateArrived DATE
);
GO
Data Entry for Date-Based Columns
If a column is made for date values (DATE), if you are visually providing its value in a Table window, type the sections of the date using one of the following formulas:
YYYY-MM-DD MM-DD-YYYY MM/DD/YY MM/DD/YYYY DD-MMM-YY DD-MMMM-YY DD-MMM-YYYY DD-MMMM-YYYY
Here is an example:
CREATE SCHEMA Inventory;
GO
CREATE TABLE Inventory.StoreItems
(
ItemNumber INTEGER,
DateArrived DATE,
);
GO
INSERT INTO Inventory.StoreItems(ItemNumber, DateArrived) VALUES(1001, '2018-06-17');
GO
If your date value may include international formats, you can precede the value with N.
If you are creating the record using SQL, you can also use the YYYYMMDD format. Here is an example:
INSERT INTO Inventory.StoreItems(ItemNumber, DateArrived) VALUES(1002, N'19880617'); GO
Time-Based Columns
Introduction
To help you create a column for time values, Transact-SQL provides a data type named TIME. Here is an example of applying it:
CREATE SCHEMA Inventory; GO CREATE TABLE Inventory.StoreItems ( ItemNumber INTEGER, DateArrived DATE, TimeArrived TIME ); GO
Data Entry on Time-Based Columns
To specify the time value of a TIME-based column, use one of the following formulas:
HH:MM HH:MM:SS HH:MM:SS[.fractional-seconds]
If you are typing the value in a Table window, enter the time using one of the above formulas.
If you are creating the record using SQL code, include the value in single-quotes. If you want to handle international formats, start the value with N.
A Date/Time-Based Column
Some situations want you combine a date and time values as one for the record of a column. To support this, the Transact-SQL provides the smalldatetime, the DATETIME, and DATETIME2 data types. The DATETIME and the DATETIME2 data types consider spacial values between January 1st, 0001 at midnight (00:00 AM) and December 31st, 9999 at 11:59PM. To create a column that can hold a combination of a date and a time values, apply either the datetime or the datetime2 data type.
Visual Data Entry for a Combination of Date and Time
If the column was created for a date/time combination that uses the DATETIME, the DATETIME2, or the SMALLDATETIME, start with the date follow by the time. Both sections must follow the formulas we reviewed for date and time values.
Data Entry on a Date/Time-Based Column
To specify the value of a column that uses a combination of date and time values (datetime, DATETIME2, or smalldatetime), apply one of the formats we reviewed for dates, followed by an empty space, followed by one of the formats we reviewed for time values.
Data Entry for a Combination of Date and time
If a column uses a combination of date and time values, provide a valid combination of the values in single-quote, optionally preceded by N. Each section of the date and time must follow the rules we reviewed for their types. Both sections must be separaterd by an empty space.
Other Data Types
Transact-SQL provides the sql_variant data type that can be used in place of the other types we had used so far. After specifying it as a column's data type, to perform data entry, use the appropriate format depending on the intended type, whether it is a string, a number, or a date, etc. Here are examples:
USE Exercise;
GO
CREATE TABLE SeasonalWorkers
(
FirstName SQL_VARIANT,
LastName sql_variant,
DateHired Sql_Variant,
IsMarried SQL_variant,
YearlyIncome sql_variant
);
GO
INSERT INTO SeasonalWorkers(FirstName, LastName, DateHired, IsMarried, YearlyIncome)
VALUES(N'Paul', N'Yamo', N'04-07-2012', 1, 48500.15);
GO
A User-Defined Type
If you have programmed in languages like C/C++, Pascal, F#, or ADA, etc, you are probably familiar with the ability to give a friendly name to a known data type. Transact-SQL also gives you this option. A user-defined data type (UDT) is a technique of creating a data type based on an existing Transact-SQL data type.
Before creating a user-defined data type, you must be familiar with the existing types. If you want, you can create an alias name for one of these. To create a UDT in Transact-SQL, the basic formula to follow is:
CREATE TYPE AliasName FROM BaseType
Start with the CREATE TYPE expression, followed by the desired name for the new type. After the FROM keyword, type an existing Transact-SQL data type. Here is an example:
CREATE TYPE NaturalNumber FROM int; GO
In the same way, you can create as many aliases of known data types as you want. You must also be aware of rules that govern each data type. Here are examples:
CREATE TYPE ShortString FROM nvarchar(20); CREATE TYPE ItemCode FROM nchar(10); CREATE TYPE LongString FROM nvarchar(80); CREATE TYPE FloatingPoint FROM decimal(8, 2); CREATE TYPE Boolean FROM bit; GO
After creating a UDT, you can use it as you see fit. For example, you can apply it tp a columnt. Here are examples:
CREATE TABLE Houses
(
PropertyNumber ItemCode,
PropertyType ShortString,
FinishedBasement Boolean,
MarketValue FloatingPoint,
Characteristics LongString,
);
GO
You can mix Transact-SQL data types and your own defined type in your code.
Spatial Types
Transact-SQL provides the geometry data type to support geometric coodinates. Unlike the other data types, the geometry type is in fact a class, like a class in C#. As a result, it has properties and methods. The properties of the geometry type are defined by the Open Geospatial Consortium (OGC). To adapt the data type to Transact-SQL, Microsoft added some functionalities to the type.
After specifying geometry as the data type of a column, you can set its values. To support this, the class is equipped with a method named STGeomFromText. Its syntax is:
static geometry STGeomFromText('geography_tagged_text', SRID)
This is a static method. To access it, use geometry::STGeomFromText. This method returns a value or type geometry. Here is an example:
CREATE TABLE PictureLocation
(
PictureName nvarchar(50),
Coordinate geometry
);
GO
INSERT INTO PictureLocation(PictureName, Coordinate)
VALUES(N'IMG001.jpg', geometry::STGeomFromText(. . .);
GO
This method takes two arguments. The first argument holds a value identified as a Well-Known Text (WKT) value. The value follows a format defined by OGC. There are various ways you can specify this value. As you may know already, a geometric point is an object that has two values: the horizontal coordinate x and the vertical coordinate y. The value can be integers or flowing-point numbers.
If you know the coordinates of a point and you want to use it as the value of the geometry object, type point() (or POINT(), this is not case-sensitive) and, in the parentheses, type both values separated by a space. Here is an example:
geometry::STGeomFromText('point(6 4)', . . .);
Instead of just one point, you may want to use a geometric value that is a line. In this case, specify the shape as linestring(, ). In the parentheses and on both sides of the comma, type each point as x and y. Here is an example:
geometry::STGeomFromText('linestring(1 4, 5 2)', . . .);
You can also use a complex geometric value, in which case you can pass the argument as a polygon. Use polygon(()) (or POLYGON(())) and pass the vertices in the parentheses. Each vertext should specify its x and y coordinates. The vertices are separated by commas. A last vertex should be used to close the polygon, in which case the first and the last vertices should be the same. Here is an example:
geometry::STGeomFromText('polygon((1 2, 2 5, 5 5, 4 2, 1 2))', . . );
The second argument of the geometry::STGeomFromText method is a contant integer known as the spatial reference ID (SRID). Here is an example:
CREATE TABLE PictureLocation
(
PictureName nvarchar(50),
Coordinate geometry
);
GO
INSERT INTO PictureLocation(PictureName, Coordinate)
VALUES(N'IMG001.jpg', geometry::STGeomFromText('point(6 4)', 0));
GO
Topics on Data Entry
Adjacent Data Entry
The most common technique of performing data entry requires that you know the sequence of the columns of the table in which you want to enter data. In this case, provide the value of each column in the order they were created in the table. Here is an example we used previously:
CREATE TABLE SaleItems
(
Manufacturer VARCHAR(40),
Category VARCHAR(28),
SubCategory VARCHAR(25),
ItemName VARCHAR(120),
Size VARCHAR(20)
);
GO
INSERT SaleItems
VALUES('Ralph Lauren', 'Girls', 'Shirts', 'Girls 2-6X Short-Sleeved Mesh Polo Shirt', '3T');
GO
During data entry on adjacent columns, if you don't have a value for a text-based column, type two single-quotes '' to indicate emptiness.
Random Data Entry
To perform data entry in an order of your choice, you must provide your list of the columns of the table. You can either use all columns or provide a list of the same columns but in your own order. In the same way, you don't have to provide data for all fields, just those you want, in the order you want. Here are examples:
CREATE SCHEMA Inventory;
GO
CREATE TABLE Inventory.StoreItems
(
ItemNumber VARCHAR(8) PRIMARY KEY,
Manufacturer VARCHAR(40),
Category VARCHAR(28),
SubCategory VARCHAR(25),
ItemName VARCHAR(120),
Size VARCHAR(20)
);
GO
INSERT Inventory.StoreItems
VALUES('927059', 'Ralph Lauren', 'Girls', 'Shirts', 'Girls 2-6X Short-Sleeved Mesh Polo Shirt', '3T');
GO
INSERT Inventory.StoreItems(ItemNumber, Manufacturer, Category, SubCategory, ItemName, Size)
VALUES('318495', 'Kenneth Cole', 'Women', 'Dresses', 'Three-Quarter Sleeved Dress', 'Medium');
GO
INSERT Inventory.StoreItems(Manufacturer, SubCategory, ItemName, Category, Size, ItemNumber)
VALUES('Polo Ralph Lauren', 'Pants', 'Classic Straight-Leg Jeans', 'Men', '30W - 30L', '397495');
GO
INSERT Inventory.StoreItems(Category, Size, ItemNumber, SubCategory, ItemName, Manufacturer)
VALUES('Girls', '3T', '830405', 'Shirts', 'Girls 2-6X Short-Sleeved Mesh Polo Shirt', 'Ralph Lauren');
GO
Creating Multiple Records
In previous sections, we added one record at a time. You can add various records with one call to INSERT. If you are adding a value to each column of the table, after the name of the table, type VALUES, open and close the first parentheses. Inside the parentheses, include the desired values. To add another record, type a comma after the closing parenthesis, open a new parenthesis, list the new values, and close the parenthesis. Do this as many times as you need to add records. Here is an example:
CREATE SCHEMA Inventory;
GO
CREATE TABLE Inventory.StoreItems
(
ItemNumber VARCHAR(8) PRIMARY KEY,
Manufacturer VARCHAR(40),
Category VARCHAR(28),
SubCategory VARCHAR(25),
ItemName VARCHAR(120),
Size VARCHAR(20)
);
GO
INSERT Inventory.StoreItems
VALUES('840633', 'Nautica', 'Women', 'Shirts', 'Short-Sleeved Pima Cotton V-Neck Tee', 'M'),
('724377', 'Polo Ralph Lauren', 'Men', 'Shirts', 'Short-Sleeved Knit Polo', 'S'),
('679340', 'Kenneth Cole Reaction', 'Women', 'Coats', 'Double Breasted Peacoat', '8');
GO
This is valid for adjacent data entry. If you want to follow your own order of columns, on the right side of the name of the table, include a list of columns in parentheses. Then, when giving the values, for each record, follow the order in which you listed the columns. Here is an example:
INSERT Inventory.StoreItems(ItemNumber, Manufacturer, Category, SubCategory, ItemName, Size) VALUES('292915', 'Kenneth Cole', 'Women', 'Dresses', 'Three-Quarter Sleeved Dress', 'Large'), ('242930', 'Ralph Lauren', 'Boys', 'Sweaters', 'Boys 2-7 Long-Sleeved Cable Crewneck T-Shirt', '3/3T'), ('506513', 'Lauren by Ralph Lauren', 'Baby Girls', 'Skirts', 'Infants Tiered Skirt', '18 Months'); GO INSERT Inventory.StoreItems(Manufacturer, ItemName, SubCategory, Category, ItemNumber, Size) VALUES('CK Calvin Klein', 'Two-Piece Slim Fit Suit', 'Suits', 'Men', '245443', '42'), ('Anne Klein', 'Drape Front Jacket', 'Jackets', 'Women', '957257', '4'); GO
Inserting a Number or Percentage of Records
Imagine you have a series of records and you want to add them to a table. Transact-SQL allows you to specify whether to insert all of the records, a certain number of records, or a portion of the records.
Based on a number of records, to insert a fraction of the records, after the INSERT keyword, type TOP (number) followed by the name of the table and the rest of the formula we have used so far. Here is an example:
INSERT TOP (2) INTO Videos(Rating, Title, Director)
VALUES('PG-13', 'Big Momma''s House ', 'Raja Gosnell'),
('G', 'Annie', 'John Huston'),
('PG', 'Incredibles (The)', 'Brad Bird'),
('PG-13', 'Mission: Impossible', 'Brian De Palma'),
('R', 'Negotiator (The)', 'F. Gary Gray');
GO
This code instructs the database engine to insert only 2 records from the list, regardless of the number of records that are provided.
Instead of specifying a fixed number of records, you can ask the database engine to insert a certain percentage of records. In this case, after TOP (Number), add the PERCENT word. Here is an example:
INSERT TOP (40) PERCENT INTO Videos(Rating, Title, Director)
VALUES('', 'Professionals (The)', 'Richard Brooks'),
('R', 'Trading Places', 'John Landis'),
('PG-13', 'Cellular', 'David R. Ellis'),
('R', 'Negotiator (The)', 'F. Gary Gray'),
('PG-13', 'Big Momma''s House ', 'Raja Gosnell'),
('G', 'Annie', 'John Huston');
GO
The code provides six records but asks the database engine to add 40% of them. That is 6 / (100/40) = 6 / 2.5 = 2.4. The closest higher integer to this number is 3. Therefore, 3 records are added.
Built-Functions
Parsing an Expression or Value
Parsing consists of scanning an expression to check a pattern, a word, or a match. An example is to find out whether an expression or a string is or contains a number, a date, a time, etc.
To assist you with parsing, Transact-SQL provides the PARSE function. Its syntax is:
PARSE( string_value AS data_type [ USING culture ])
This function takes one argument, passed as a string and accompanied by its data type preceded by the AS keyword. The function is equivalent to the method of the same name used in the primitive data types of the .NET Framework (int, short, etc).
When calling the PARSE function, if the argument may include international characters or formats (Unicode), you should indicate the language, called a culture, that the argument follows.
If the PARSE() function is not able to determine the type or if the value of the argument doesn't follow the rule of the data_type, this function produces (throws) an error. As an alternative to PARSE(), Transact-SQL provides the TRY_PARSE() function. Its syntax is:
TRY_PARSE ( string_value AS data_type [ USING culture ] )
As you can see, this function uses the same argument as PARSE. The difference is that, while PARSE() produces an error if the parsing operation fails, TRY_PARSE produces NULL (if the parsing operation fails). This means that, in most cases, you should prefer TRY_PARSE() instead of PARSE().
Casting a Value
In a C# application, to convert a value, you can use either the Convert class or a Parse() method. In Transact-SQL, to convert a value, you can use either the CAST() or the CONVERT() function. The syntax of the CAST() function is:
CAST(expression AS data-type)
The expression is the value that needs to be cast. The data-type factor is the type of value you want to convert the expression to.
If the CAST() function is not able to cast the expression (if it fails), it produces (throws) an error. As an alternative to CAST(), Transact-SQL provides a function named TRY_CAST. Its syntax is:
TRY_CAST ( expression AS data_type [ ( length ) ] )
This function takes the same arguments as CAST. The difference is that, instead of producing an error, if TRY_CAST() fails, it returns NULL. This also means that, in most cases, you should use TRY_CAST() instead of CAST.
Converting a Value
To let you convert a value to a type of your choice, Transact-SQL provides the CONVERT() function. Unlike CAST(), CONVERT can be used to convert a value from its original type into a non-similar type. For example, you can use CONVERT to cast a number into a string and vice-versa.
The syntax of the CONVERT() function is:
CONVERT(DataType [ ( length ) ] , Expression [ , style ])
The first argument must be a known data type, such as those we reviewed already. If you are converting the value into a string (varchar, nvarchar, char, nchar) or a binary type, you should specify the number of allowed characters in the data type's own parentheses, as the length argument.
As reviewed for the CAST() function, the expression is the value that needs to be converted.
If the CONVERT() function is not able to perform its operation, it produces an error.
Because of some difficulties that can result from conversion, Transact-SQL provides a function named TRY_CONVERT. Its syntax is:
TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )
The arguments are the same used in the CONVERT() function. The difference is that if the TRY_CONVERT() function fails, it returns NULL instead of producing (throwing) an error.
Transact-SQL Macros and Metadata Functions
Introduction to Macros
A macro is an action that can be performed on a database or certain parts of a database or of a database server. A macro resembles a function without being exactly one. Transact-SQL provides various macros to assist you with managing databases and their objects.
Checking the Existence of a Record
One of the simplest operations a user can perform on a table consists of looking for a record. As the database developer, you too can look for a record. There are various techniques you can use. To assist you with this, Transact-SQL provides a macro named EXISTS. Its syntax is:
BIT EXISTS(SELECT Something)
This macro takes one argument. The argument must be a SELECT statement that would be used to get the value whose existence would be checked. For example, we know a system database named databases that contains a record of all databases stored on your server. You can use the EXISTS() macro to check the existence of a certain database.
Assistance with Strings
The Minimum Number of Characters
When creating or adding a property in a class or for an entity, you specify that you want the user to enter a certain minimum number of character in the corresponding text box. To support this, the System.ComponentModel.DataAnnotations namespace provides an attribute named MinLength. When applying this attribute to a property in a class, add the parentheses to it and enter the desired number in the parentheses.
If you are designing an entity, after adding a string-based Scalar Property, if you want to require a certain minimum number of characters, in the Properties window, click the Min Length and type the desired number.
The Maximum Number of Characters
As seen in our introduction to text-based fields, databases support a maximum number of characters for a column. We have already seen many examples of how to specify this number in the parentheses of CHAR, VARCHAR, NCHAR and NVARCHAR. To let you specify the maximum number of characters that a column can hold, the System.ComponentModel.DataAnnotations namespace provides an attribute named MaxLength. This attribute is used or applied like its MinLength counterpart.
As seen in our introduction to the entity framework, if you are designing an entity, after adding a string-based Scalar Property, to specify the maximum number of characters for a field, in the Properties window, click the Max Length and type the desired number.
To let you specify both the minimum and maximum number of characters for a field, the System.ComponentModel.DataAnnotations namespace provides an attribute named StringLength. To support the minimum string length, the StringLengthAttribute class is equipped with a property named MinimumLength. To use it in a class, apply it to a proproperty and add parentheses to it. In the parentheses, assign the desired value to MinimumLength. The he StringLengthAttribute class also supports a maximum number of characters through a property named MaximumLength. It is used like its counterpart.
An Identity Column
Introduction to Surrogate Keys
A surrogate key is a column whose values are provided, or generated, automatically, by the database engine. A surrogate key is used when there is a clear and justified need to have unique records but you don't want to spend time designing an algorithm that fulfills this condition. The values of a surrogate key are usually integers with no obvious or clear meaning. This also means that the values of a surrogate key mean nothing to the user and in fact the user doesn't have to know or care about them.
Microsoft SQL Server provides many ways to create a surrogate key. Two of the solutions are identify keys and sequences.
Practical Learning: Introducing Databases
CREATE SCHEMA Resources; GO CREATE TABLE Resources.WaterBills ( [InvoiceNumber] INT NOT NULL PRIMARY KEY ); GO
Introduction to the Identity Columns
One of the goals of a good table is to have to unique records. 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 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, in the top section, specify the name of the column. By tradition, the name of the column ends with _id, Id, or ID; but you can give any name you want.
After specifying the name of the column, set its data type to an integer-based type. Usually, the data type used is INTEGER. In the Properties section, click and expand the Identity Specification property. The first action you should take is to set its (Is Identity) property from False to True.
If you are creating a model, when you add a new Entity, it automatically receive a columnn named Id and that has the identity behavior. Otherwise, if you want to apply the identity behavior to a column, select that column in the Designer. In the Properties window, click the StoreGeneratedPattern field, then click the arrow of its combo box and select Identity.
Practical Learning: Creating an Identity Column
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.
Practical Learning: Setting the Identity Seed
| Column Name | Data Type |
| InvoiceNumbmer | int |
| MeterNumber | nvarchar(10) |
| FirstName | nvarchar(25) |
| LastName | nvarchar(25) |
| Address | nvarchar(100) |
| City | nvarchar(50) |
| State | nvarchar(2) |
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. 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.
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
(
StoreItemID int IDENTITY(1, 1) NOT NULL,
Category varchar(50),
[Item Name] varchar(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:
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.
Creating a Value for an Identity Column
If you want to specify a value for the identity column, set 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 used 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 to which the identity column belongs. After specifying the name of the table, set the flag to ON to allow a value for the identity column, or OFF to disallow it.
If you decide to use the SET IDENTITY_INSERT flag, you must provide a list of columns after the name of the table in the INSERT or INSERT INTO statement. Here is an example:
@{
using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(. . .))
{
System.Data.SqlClient.SqlCommand command =
new System.Data.SqlClient.SqlCommand("SET IDENTITY_INSERT StoreItems ON;" +
"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);",
connection);
connection.Open();
command.ExecuteNonQuery();
}
}
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:
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.
Creating an Identity Column in the Entity Framework
If you create an entity framework using the Empty EF Designer Model option of the Entity Data Model Wizard, when you add an entity in the designer, that entity receives a column whose type is an integer and is an identity column. Otherwise, to create an identity column, add a scalar property. In the Properties window, set the data Type to an integer. Set the StoreGeneratedPattern to Identity. Here is an example:
Introduction to Sequences
Overview
A sequence is a series of numbers that are continually generated and assigned to a column of a table. This works like an identity column. The difference is that, if you need an identity, you must create it in a column of a table and if you need the same type of identity on a column of another table, you must create the identity in the column of the other table. On the other hand, a sequence is a programmatic object, like a function, that you create at the database level and you can apply that sequence to any table you want.
Visually Creating a Sequence
To visually create a sequence, in the Object Explorer of Microsoft SQL Server Management Studio, expand the desired database and the Programmability nodes. Right-click Sequences and click New Sequence... This would present the New Sequence dialog box with some default (basic) values.
Programmatically Creating a Sequence
The Transact-SQL syntax to create a sequence is:
CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]
[ START WITH <constant> ]
[ INCREMENT BY <constant> ]
[ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
[ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ <constant> ] } | { NO CACHE } ]
[ ; ]
You start with the CREATE SEQUENCE expression.
Characteristics of a Sequence
A sequence shares many characteristics with an identity column but adds some others. Therefore, the characteristics of a sequence are:
Using a Sequence
After creating a sequence, it becomes an object you can use in any new table. Because a sequence generates (unique increment/decrement) values that a column would use, when creating the field on a table, specify its data type as the same or compatible type that the sequence is using. Here is an example:
CREATE TABLE Inventory.StoreItems
(
ItemNumber int,
ItemName nvarchar(60),
UnitPrice money
);
GO
A sequence is used during data entry. When specifying the value of its column, type a formula as:
NEXT VALUE FOR [schema_name . ] sequence_name
The database engine would then find the next number in the sequence and assign it to the column. Here are examples:
USE DepartmentStore;
GO
CREATE SCHEMA Inventory;
GO
CREATE TABLE Inventory.StoreItems
(
ItemNumber int,
ItemName nvarchar(60),
UnitPrice money
);
GO
CREATE SEQUENCE Inventory.ItemsCodes
AS int
START WITH 10001
INCREMENT BY 1;
GO
INSERT INTO Inventory.StoreItems
VALUES(NEXT VALUE FOR Inventory.ItemsCodes, N'Short Sleeve Shirt', 34.95),
(NEXT VALUE FOR Inventory.ItemsCodes, N'Tweed Jacket', 155.00),
(NEXT VALUE FOR Inventory.ItemsCodes, N'Evaded Mini-Skirt', 72.45),
(NEXT VALUE FOR Inventory.ItemsCodes, N'Lombardi Men''s Shoes', 79.95);
GO
Details on Sequences
Sharing a Sequence
A sequence can be shared by many tables. This means that, after creating a sequence, you can apply it on any table that needs that series of numbers. When using a sequence from one table to another, if you use the NEXT VALUE FOR routine, the series would continue from where it left up. This is not an anomaly. It is by design, so that various tables can share the same sequence.
Resetting a Sequence
Resetting a sequence consists of restarting it from a certain point. To do this, use the following formula:
ALTER SEQUENCE [schema_name. ] sequence_name
[ RESTART [ WITH constant ] ]
[ INCREMENT BY constant ]
[ { MINVALUE constant } | { NO MINVALUE } ]
[ { MAXVALUE constant } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ constant ] } | { NO CACHE } ]
[ ; ]
Setting a Sequence as Default
So far, to specify the value of a column with sequence, we were calling NEXT VALUE FOR. If you know that you will keep caling a sequence to provide the values of a column, you can set that sequence as the default value of the column. If you do this, you can omit the column in the INSERT statement. Here is an example:
USE ApartmentBuilding;
GO
CREATE TABLE Rentals.Registrations
(
RegistrationID int
DEFAULT (NEXT VALUE FOR Rentals.SeqRegistrations),
EmployeeNumber int, -- Processed By
TenantCode int, -- Processed For
UnitNumber int,
RentStartDate date,
Notes nvarchar(max)
);
GO
After doing this, you can create the values of the column as done for an identity, by omiting the name of the column in the INSERT statement.
INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, MiddleName, LastName, BirthDate, Gender, EmailAddress, MajorID, MinorID, Username)
VALUES(N'88130480', N'Marie', N'Annette', N'Robinson', Administration.SetDateOfBirth(-6817), N'F', N'mrobinson@yahoo.com', 1021, 1004, N'mrobinson'),
(N'24795711', N'Roger', N'Dermot', N'Baker', Administration.SetDateOfBirth(-6570), N'M', N'rbaker2020@hotmail.com', 1005, 1002, N'rbaker');
GO
INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, LastName, BirthDate, Gender, EmailAddress, MajorID, MinorID, Username)
VALUES(N'18073572', N'Patrick', N'Wisne', Administration.SetDateOfBirth(-11012), N'M', N'pwisdom@attmail.com', 1001, 1008, N'pwisne');
GO
The Nullity of a Field
Setting the Nullity of a Field
A field is referred to as null when no data entry has been made to it. A field is referred to as null if there is no way to determine the value of its content. As you can imagine, it is not a good idea to have a null field in 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.
If you are programmatically creating the table using SQL, to specify that a column can allow null values, type NULL on the right side of the column definition. 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 varchar(20)
);
GO
If the table was created already and it holds some values, you cannot change its nullity option.
Practical Learning: Allowing NULL Records
Data Entry for a Null Column
When a column has been marked as NULL or its Allow Null check box was cleared, it can be skipped during data entry. Otherwise, a NOT NULL column must always have a value.
Requiring a Value for a Field
The Entity Designer
If you are designing an entity, to specify whether a column must always hold a value, after adding its Scalar Property, in the Properties, use the Nullable field. If you set it to True, the user must provide a value for the column, otherwise the record will not be saved.
A Required Property
If you are creating a class that will be used in an entity framework, to let you indicate that the user must provide a value for the corresponding column of the table, the System.ComponentModel.DataAnnotations namespace provides an attribute named Required and that is reqpresented by the RequiredAttributre class.
To indicate that the user must provide a value for a column, mark its property by the Required attribute. Here are two examples:
using System.ComponentModel.DataAnnotations;
namespace DepartmentStore02.Models
{
public class StoreItem
{
[Key]
public int ItemNumber { get; set; }
[Required]
public string ItemName { get; set; }
public string Size { get; set; }
[Required]
public double UnitPrice { get; set; }
}
}
After creating a record in this scenario, if the user doesn't provide a value for the required field and tries to save the record:
The controls that are associated to a required field are configured to display a default error message. If you want, you can display an error message of your choice. To support this, the RequiredAttribute class is equipped with a property named ErrorMessage. Based on this, to specify your own error message, add the parentheses to the RequiredAttribute attribute. In the parentheses, assign your message is double-quotes to the ErrorMessage property. Here is an example:
public class Employee
{
[Key]
public int EmployeeNumber { get; set; }
public string FirstName { get; set; }
[Required(ErrorMessage = "You must provide a last name for the employee.")]
public string LastName { get; set; }
public double HourlySalary { get; set; }
}
The Default Value of a Column
Introduction
When creating or modifying a column, you can specify its default value so that it would be applied if the user does not specify one.
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, type the DEFAULT keyword 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
Practical Learning: Setting a Default Value for a Field
Data Entry for a Default Value
After creating the table, the user does not have to provide a value for a column that has a default value. 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 become empty. |
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:
CREATE TABLE Employees
(
EmployeeNumber varchar(6),
FirstName varchar(20),
LastName varchar(20),
[State] VARCHAR(2) default 'NC'
);
GO
INSERT INTO
VALUES('28-404', 'Amadou', 'Sulleyman', 'TX'),
('82-948', 'Frank', 'Arndt', DEFAULT),
('27-749', 'Marc', 'Engolo', 'TN');
GO
In this example, the second record would receive the default value, which is NC. In the same way, you can force the default keyword for each column whose default value was indicated when the table was created.
Unique Records
Introduction to the Uniqueness of Records
One of the primary concerns of records is their uniqueness. You usually want to make sure that each record on a table can be uniquely identified. One way to do this is to apply a uniqueness rule on a column.
To assist you with creating a column 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:
CREATE TABLE Students
(
StudentNumber VARCHAR UNIQUE,
FirstName varchar(50),
LastName varchar(50) NOT NULL
);
GO
Practical Learning: Adding a Unique Column
CREATE SCHEMA Resources;
GO
CREATE TABLE Resources.WaterBills
(
[InvoiceNumber] INT PRIMARY KEY IDENTITY(100000, 1),
[MeterNumber] NVARCHAR(10) UNIQUE NULL,
[FirstName] NVARCHAR(25) NULL,
[LastName] NVARCHAR(25) NULL,
[Address] NVARCHAR(50) NULL,
[City] NVARCHAR(50) NULL,
[State] NCHAR(2) NULL DEFAULT 'GA'
);
GOData Entry with a Unique Field
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.
An Expression for a Column
Introduction
To assist the user with data entry, you can create an expression using one or a combination of arithmetic and/or SQL operators. You can create an expression when creating a table.
To programmatically create an expression when creating a table, 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(
CircleNbr int NOT NULL,
Radius decimal(8, 3) NOT NULL,
Area AS Radius * Radius * PI()
);
GO
Data Entry for an Expression Column
When performing data entry, you must not provide a value for a column that uses an expression; the SQL interpreter would provide the value automatically. Here is an example of entering data for the above Circle table:
INSERT Circle(CircleNbr, Radius) VALUES(200, 46.82); GO INSERT Circle(CircleNbr, Radius) VALUES(128, 8.15); GO INSERT Circle(CircleNbr, Radius) VALUES(432, 122.57); GO
Practical Learning: Ending the Lesson
|
|
||
| Previous | Copyright © 2001-2021, FunctionX | Next |
|
|
||