|
A
decimal number is a number that can have a period (or the character used
as the decimal separator as set in the Control Panel) between the digits.
An example would be 12.625 or 44.80. Like an integer, a decimal number can
start with a + or just a digit, which would make it a positive number. A
decimal number can also start with a - symbol, which would make it a
negative number. If the number represents a fraction, a period between the
digits specifies what portion of 1 was cut.
|
Decimal and Numeric Types |
|
If you anticipate such a
number for a field, specify its data type as numeric or decimal
(either decimal or numeric would produce the same effect in
SQL Server). Here is an example:
1> DECLARE @Distance DECIMAL;
2> SET @Distance = 648.16;
3> PRINT @Distance;
4> GO
648
A floating-point number
is a fractional number, like the decimal type. Floating-point numbers can
be used if you would allow the database engine to apply an approximation
to the actual number. To declare such a
variable, use the float or the real keyword. Here is an
example:
1> DECLARE @Radius FLOAT;
2> SET @Radius = 48.16;
3> SELECT @Radius AS Radius;
4> GO
Radius
------------------------
48.159999999999997
(1 rows affected)
A precision is the number of digits
used to display 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 (28 if you are using SQL Server 7).
A decimal number is a number that has a
fractional section. Examples are 12.05 or 1450.4227. The scale of a number
if the number of digits on the right side of the period (or the character
set as the separator for decimal numbers for your language, as specified in Control Panel).
The scale is used only for numbers that have a decimal part, which
includes currency (money and smallmoney) and decimals (numeric
and
decimal). If a variable is declared 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. Here is an
example:
|
 Practical Learning: Using Decimal Variables
|
|
- Change the statement as follows:
DECLARE @IsMarried bit,
@EmplStatus int,
@WeeklyHours Decimal(6,2);
SET @IsMarried = 1;
SET @EmplStatus = 2;
SET @WeeklyHours = 36.50;
SELECT @IsMarried AS [Is Married?],
@EmplStatus AS [Employment Status],
@WeeklyHours AS Hours;
GO
|
- Execute the statement
|
Currency and Monetary Values |
|
If a variable would hold monetary values, you can
declare it with the money keyword.
A variable 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. Here is an
example:
1> DECLARE @YearlyIncome Money;
2> SET @YearlyIncome = 48500.15;
3> SELECT @YearlyIncome AS [Yearly Income];
4> GO
Yearly Income
---------------------
48500.1500
(1 rows affected)
While the money data type
can be used for a variable that would hold large quantities of currency
values, the smallmoney data type can be applied for a variable whose
value cannot be lower than -214,748.3648 nor higher than 214,748.3647.
The precision and scale of a money or smallmoney
variable are fixed by Microsoft SQL Server. The scale is fixed to 4.
|
Practical Learning: Using Currency Variables
|
|
- Change the statement as follows:
DECLARE @EmplStatus int,
@IsMarried bit,
@WeeklyHours Decimal(6,2),
@HourlySalary SmallMoney,
@WeeklySalary SmallMoney;
SET @IsMarried = 1;
SET @EmplStatus = 2;
SET @WeeklyHours = 36.50;
SET @HourlySalary = 15.72;
SET @WeeklySalary = @WeeklyHours * @HourlySalary;
SELECT @EmplStatus AS [Empl Status],
@IsMarried AS [Married?],
@WeeklyHours AS Hours,
@HourlySalary AS Hourly,
@WeeklySalary AS Weekly;
GO
|
- Execute the statement
A field of characters can consist of any kinds of alphabetical symbols in any combination, readable or not. If you
want a variable to hold a fixed number of characters, such as the book shelf
numbers of a library, declare it with the char data type. Here is
an example:
DECLARE @Gender char;
By default, the char data type can be applied to a
variable that would hold one character at a time. After declaring the
variable, when initializing it, include its value in single-quotes. Here
is an example:
1> DECLARE @Gender char;
2> SET @GENDER = 'M';
3> SELECT @Gender AS Gender;
4> GO
Gender
------
M
(1 rows affected)
By default, when you initialize a character variable, the
interpreter reserves 8 bits of memory for the variable. This could be a problem
if you want to store characters other than those used in US English. The
alternative is to ask the interpreter to reserve 16 bits of space and follow
Unicode rules. To do this, when initializing the variable, precede its value
with N. Here is an example:
1> DECLARE @Gender char;
2> SET @GENDER = N'M';
3> SELECT @Gender AS Gender;
4> GO
Gender
------
M
(1 rows affected)
If you include more than one character in the
single-quotes, only the first (most left) character would be stored in the
variable. Here is an example:
1> DECLARE @Gender char;
2> SET @Gender = N'Male';
3> SELECT @Gender AS Gender;
4> GO
Gender
------
M
(1 rows affected)
A string is a
character or a combination of characters. If a
variable will hold strings of different lengths, declare it with the varchar data
type. The maximum length of text that a field of varchar type
can hold is equivalent to 8 kilobytes.
In some circumstances, you will need to change or
specify the number of characters used in a string variable. Although a First Name and a Book
Title variables should use the varchar type, both variables would not
have the same length of entries. As it happens, people hardly have a first
name that is beyond 20 characters and many book titles go beyond 32
characters. In this case, both variables would use the same data type but
different lengths.
To specify the maximum number of characters that can be
stored in a string variable, on the right side of char or varchar, type an
opening and a closing parentheses. Inside of the parentheses, type the
desired number. To initialize the variable, if you are using the Command
Prompt (SQLCMD.EXE), include its value between double-quotes. Here is an example:
If you are using a query window, don't include the string
value in double-quotes; otherwise, you would receive an error:
Therefore, if using the query window, include the
string in single-quotes:
The text data type can be used on a variable whose data would consist of ASCII characters. As opposed to a varchar
type of field, a text type of field can hold text that is longer than 8
kilobytes.
The nchar, nvarchar, and ntext types follow the same rules as the char, varchar, and text
respectively, except that they can be applied to variables that would hold
international characters, that is, characters of languages other than US
English. This is done following the rules of Unicode formats.
When initializing the variable, to follow Unicode rules,
precede its value with N follow by single-quotes. This rule applies to both the
Query window and PowerShell:
1> DECLARE @FirstName nchar(20),
2> @LastName nchar(20);
3> SET @FirstName = N'Philomène';
4> SET @LastName = N'Açore';
5> SELECT @FirstName As "Prénom",
6> @LastName As "Nom de Famille";
7> GO
Prénom Nom de Famille
-------------------- --------------------
Philomène Açore
(1 rows affected)
1>
Notice that, in Powershell, if you are not using Unicode
rules, the string must be included in double-quotes and if you are using
Unicode, the string must be included in single-quotes.
|
Practical
Learning: Using String Variables
|
|
- Change the statement as follows:
DECLARE @FirstName nvarchar(20),
@LastName nvarchar(20),
@FullName nvarchar(40),
@EmplStatus int,
@IsMarried bit,
@WeeklyHours Decimal(6,2),
@HourlySalary SmallMoney,
@WeeklySalary SmallMoney;
SET @FirstName = N'Samuel';
SET @LastName = N'Weinberg';
SET @FullName = @LastName + N', ' + @FirstName;
SET @IsMarried = 1;
SET @EmplStatus = 2;
SET @WeeklyHours = 36.50;
SET @HourlySalary = 15.72;
SET @WeeklySalary = @WeeklyHours * @HourlySalary;
SELECT @FullName As [Full Name],
@EmplStatus AS [Empl Status],
@IsMarried AS [Married?],
@WeeklyHours AS Hours,
@HourlySalary AS Hourly,
@WeeklySalary AS Weekly;
GO
|
- Execute the statement
- Save the file as Variables in your My Documents folder
A time is a non-spatial measure used to count a certain
number of lapses that have occurred from a non-spatial starting point. The
primary starting point is called midnight.
The primary unit of measure of time is called the second. A
second is identified by an integer. In reality, the second is divided in 1000
fractions, counted from 0 to 999 and called milliseconds. A millisecond is
identified by an integer.
Starting with the second, a measure of 60 seconds,
counted from 0 to 59, is called a minute. A minute is identified by an
integer.
A group of 60 minutes, counted from 0 to 59, is called an
hour. An hour is identified by an integer.
To represent a time value, there are rules you must follow.
The rules can be verified in the Time tab of the Customize Regional Options of
the Regional and Language Options of the Control Panel:
To support time values, Transact-SQL provides the TIME data
type. To declare a variable that would hold a time value, use TIME as
the data type. To initialize the variable, use the following formula:
hh:mm
hh:mm:ss
hh:mm:ss[.fractional seconds]
The first part includes the hour with a value between 1 and
23. If the value is less than 10, you can write it with a leading 0, as in 08.
The second
part represents the minutes and holds a value between 1 and 59. If the value is
less than 10, you can type it with a leading 0, as in 04. The values are separated by :.
The value is included in single-quotes. To indicate that you want to follow
Unicode rules, precede the value with N. Here is an example:
1> DECLARE @ArrivalTime time;
2> SET @ArrivalTime = N'18:22';
3> SELECT @ArrivalTime AS [Arrival Time];
4> GO
Arrival Time
----------------
18:22:00.0000000
(1 rows affected)
1>
The third part of our formula is optional and represents the seconds portion
of the time and holds a value between 1 and 59. If the value is less than 10,
you can provide it with a leading 0. This part is separated from the previous
one with :.
The last part also is optional. It allows you to provide the
milliseconds part of the time. If you want to provide it, enter a value between
1 and 999. This is separated from the seconds part with a period ".".
A group of 24 hours, counted from 1 to 23, is called a day.
In reality, a day is made of 24 hours and a few more seconds. Those are various
ways used to identify a day. We will mention them below.
Above the day, the unit of measure is called a year. A year
is identified by a numeric value. Normally, a year is represented with 4 digits,
from 0 to 9999. To make it easy to manage years, they are grouped in some units.
The years that occurred before Jesus Christ are identified as BC. Then, there is
a starting point referred to as 0.
A group of 1000 years is called a millennium. The years in a
millennium must each be identified with 4 digits. An example is 1608. Another
example is 1978. Yet another example is 2118.
A group of 100 years is called a century. The years in a
century can be identified with 2 digits. An example is 08. Another example is
78. One more example is 18 (do you predict some confusion here?).
Within a year, each day can be identified by a numeric
value. The first day is 1, or can be referred to as Day 1. Each of the other
days in a year can be identified with a natural number, such as 216; that would
be Day 216 starting from the beginning of the year.
The number of days in a year depends on various factors. For
example, in some scenarios, such as some commercial or accounting procedures, a
year would count for 360 days. In most calendars, a year can have 365 days every
year except that, after 4 years, the year would have 366 days (remember, we
mentioned that a day is actually made of 24 hours and a few seconds; these
seconds are grouped every 4 years to count as a whole day). This is referred
to as a leap year.
To help manage the days of a year, a year is divided in 12
units each called a month. Each month can be identified by a number or a name.
When a month is identified with a number, it can use a value between 1 and 12.
When it comes to names, a month can use a long and/or a short name. The long names are
January, February, March, April, May, June, July, August, September, October,
November, and December. The short names are Jan, Feb, Mar, Apr, May, Jun, Jul,
Aug, Sep, Oct, Nov, and Dec.
Each month has a certain number of days. A day in a month
can be identified with an integer. The first day of the month is 1. The 15th day
would be identified as 15 or Day 15. The number of days in a month depends on
various factors.
We mentioned that a day in a month can be identified with
a number and a month has a name, within a year. A day can be identified by its
number, its month, and the numeric value of the year. There are various rules
you must follow to represent a date. The rules can be checked in
the Date tab of the Customize Regional Options accessible from the Regional and
Language Options of the Control Panel:
To help manage the months of a year, a year can be divided
in either quarters or semesters. A year has 4 quarters that each contains 3
months. A year also has 2 semesters that each has 6 months.
To help manage the days in a month, the month in divided in
parts each called a week. Normally, each week has 7 days and each month should
have 4 weeks.
To assist you with date values, Microsoft SQL Server
provides the DATE data type. This data type counts dates starting from
January 1st, 0001 up to December 31st, 9999. Therefore, to declare a variable that would hold a date
value, use the DATE data type.
To initialize a DATE variable, use one of the following formulas:
YYYYMMDD
YYYY-MM-DD
MM-DD-YY
MM-DD-YYYY
MM/DD/YY
MM/DD/YYYY
You can start the value with a 4-year digit. If you use the
first formula, YYYYMMDD, you must provide 4 digits for the year, immediately
followed by 2 digits for the month, immediately followed by 2 digits for the day.
An example would be
DECLARE @OneDay DATE;
SET @OneDay = N'10360610';
SELECT @OneDay AS [Day to Prevail];
GO
In US English, this represents October 6th, 1036:
You can provide the value in one unit with 6 digits. In this
case, the left 2 digits would be considered the year in the current century.
Consider the following example:
Instead of providing the whole value in one combination of
digits, you can use the second formula. Once again you must provide 4 digits for
the year, followed by the "-" separator, followed by 1 or 2 digits for the month,
followed by the "-" separator, followed by 1 or 2 digits for the day. An example
would be
DECLARE @EventDay date;
SET @EventDay = N'1914-4-7';
SELECT @EventDay AS [Event Day];
GO
In US English, this represents October 6th, 1036
If you are using a command prompt or PowerShell, make sure you include the
value in single-quotes. To apply Unicode rules, start with the N prefix. Here is an example:
1> DECLARE @IndependenceDay DATETIME;
2> SET @IndependenceDay = N'01/01/1960';
3> SELECT @IndependenceDay AS [Independence Day];
4> GO
Independence Day
-----------------------
1960-01-01 00:00:00.000
(1 rows affected)
We saw that, if you use the MM-DD-YY or MM/DD/YY, you can
provide a year with 2 digits. In this case:
- If the number representing the year is less than
50, the year would be considered as belonging to the current
century
- If the number representing the year is greater than 50,
the year is considered as belonging to the previous century
Here are examples:
DECLARE @SomeDate Date;
SET @SomeDate = N'5-7-05';
PRINT @SomeDate;
GO
PRINT N'-----------';
GO
DECLARE @SomeDate Date;
SET @SomeDate = N'5/7/05';
PRINT @SomeDate;
GO
PRINT N'-----------';
GO
DECLARE @SomeDate Date;
SET @SomeDate = N'5-7-41';
PRINT @SomeDate;
GO
PRINT N'-----------';
GO
DECLARE @SomeDate Date;
SET @SomeDate = N'5/7/41';
PRINT @SomeDate;
GO
PRINT N'-----------';
GO
DECLARE @SomeDate Date;
SET @SomeDate = N'5-7-81';
PRINT @SomeDate;
GO
PRINT N'-----------';
GO
DECLARE @SomeDate Date;
SET @SomeDate = N'5/7/81';
PRINT @SomeDate;
GO
PRINT N'-----------';
GO
Here are examples of results
Once again, it is better to provide a year with 4 digits.
|
Practical
Learning: Using Date/Time Variables
|
|
- Change the statement as follows:
DECLARE @FirstName nvarchar(20),
@LastName nvarchar(20),
@FullName nvarchar(40),
@DateHired date,
@EmplStatus int,
@IsMarried bit,
@WeeklyHours decimal(6,2),
@HourlySalary SmallMoney,
@WeeklySalary SmallMoney;
SET @FirstName = N'Samuel';
SET @LastName = N'Weinberg';
SET @FullName = @LastName + N', ' + @FirstName;
SET @DateHired = N'12/05/1998';
SET @IsMarried = 1;
SET @EmplStatus = 2;
SET @WeeklyHours = 36.50;
SET @HourlySalary = 15.72;
SET @WeeklySalary = @WeeklyHours * @HourlySalary;
SELECT @FullName As [Full Name],
@DateHired AS [Date Hired],
@EmplStatus AS [Empl Status],
@IsMarried AS [Married?],
@WeeklyHours AS Hours,
@HourlySalary AS Hourly,
@WeeklySalary AS Weekly;
GO
|
- Execute the statement
- Close the query window
- If asked whether you want to save the file, click No
|
Combining Date and Time Values |
|
Instead of singly declaring a date or a time value, you may
want to combine both values into one. To support this, Transact-SQL provides the DATETIME2
data type.
This data type counts dates from January 1st, 0001 and ends on December 31st,
9999. Therefore, to declare a variable that supports a date value, a time value,
or a combination of a date and time values, use the DATETIME2 data type.
To initialize the variable, use one of the following formulas:
YYYYMMDD
YYYYMMDD hh:mm:ss
YYYYMMDD hh:mm:ss[.fractional seconds]
YYYY-MM-DD
YYYY-MM-DD hh:mm:ss
YYYY-MM-DD hh:mm:ss[.fractional seconds]
MM-DD-YY
MM-DD-YY hh:mm:ss
MM-DD-YY hh:mm:ss[.fractional seconds]
MM-DD-YYYY
MM-DD-YYYY hh:mm:ss
MM-DD-YYYY hh:mm:ss[.fractional seconds]
MM/DD/YY
MM/DD/YY hh:mm:ss
MM/DD/YY hh:mm:ss[.fractional seconds]
MM/DD/YYYY
MM/DD/YYYY hh:mm:ss
MM/DD/YYYY hh:mm:ss[.fractional seconds]
Remember to include the value in single-quotes. Here are
examples:
DECLARE @FullName nvarchar(60),
@DateOfBirth date,
@DateRegistered datetime2
SET @FullName = N'John Summons';
SET @DateOfBirth = N'19960426';
SET @DateRegistered = N'20090629';
SELECT @FullName AS [Full Name],
@DateOfBirth AS [Date of Birth],
@DateRegistered AS [Date Registered];
SET @FullName = N'James Haans';
SET @DateOfBirth = N'1994-10-25';
SET @DateRegistered = N'2009-08-02';
SELECT @FullName AS [Full Name],
@DateOfBirth AS [Date of Birth],
@DateRegistered AS [Date Registered];
SET @FullName = N'Gertrude Monay';
SET @DateOfBirth = N'06-16-92';
SET @DateRegistered = N'2009-12-24 12:36';
SELECT @FullName AS [Full Name],
@DateOfBirth AS [Date of Birth],
@DateRegistered AS [Date Registered];
SET @FullName = N'Philomène Guillon';
SET @DateOfBirth = N'1996-10-16';
SET @DateRegistered = N'10/14/08 09:42:05.136';
SELECT @FullName AS [Full Name],
@DateOfBirth AS [Date of Birth],
@DateRegistered AS [Date Registered];
SET @FullName = N'Eddie Monsoon';
SET @DateOfBirth = N'08/10/96';
SET @DateRegistered = N'2009-06-02 12:36';
SELECT @FullName AS [Full Name],
@DateOfBirth AS [Date of Birth],
@DateRegistered AS [Date Registered];
SET @FullName = N'Peter Mukoko';
SET @DateOfBirth = N'03-10-1994';
SET @DateRegistered = N'7/22/2009 10:24:46.248';
SELECT @FullName AS [Full Name],
@DateOfBirth AS [Date of Birth],
@DateRegistered AS [Date Registered];
SET @FullName = N'Chritian Allen';
SET @DateOfBirth = N'06/16/1995';
SET @DateRegistered = N'02-09-2009 12:36';
SELECT @FullName AS [Full Name],
@DateOfBirth AS [Date of Birth],
@DateRegistered AS [Date Registered];
GO
If you start the value with
two digits, the first part is considered a month and not the year.
Besides the DATE, the TIME, and the DATETIME2
data types, Transact-SQL supports the smalldatetime and the datetime
data types. These are old data types. Although still available, they are kept
for backward compatibility and you should stop using them.
If you have programmed in languages like C/C++ or Pascal,
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.
|
Creating a User-Defined Type |
|
Before creating a user-defined data type, you must be
familiar with the existing type. Those are the types we have seen so far. If you
want, you can create an alias name for one of these. You can do this visually or
programmatically.
To visually create a UDT, in the Object Explorer, expand a
database, expand its Programmability node, and expand the Types item. Under
Types, right-click User-Defined Data Types and click New User-Defined Data
Type...
This would open:
The first piece of information you must provide is the
schema that will own the new type. Normally, a default schema is provided and
you can just accept it. The two most important pieces of information you must
provide are a name for the new type as alias and the Transact-SQL type on which
it will be based. In the Name text box, enter a name of your choice. The name
must follow the rules of names in Transact-SQL. In the Data Type combo box,
select the data type of your choice. Of course, you must know what type you want
to use.
After entering and selecting the desired information, click
OK.
To create a UDT with code, the basic formula to use is:
CREATE TYPE AliasName FROM BaseType
To get assistance from template code, open a Query
window. From the Templates Explorer, expand the User-Defined Data Type node.
Drag Create User-Defined Data Type and drop it in the Query window. Skeleton
code will be generated for you:
-- ================================
-- Create User-defined Data Type
-- ================================
USE <database_name,sysname,AdventureWorks>
GO
-- Create the data type
CREATE TYPE <schema_name,sysname,dbo>.<type_name,sysname,Phone>
FROM <base_type,,nvarchar> (<precision,int,25>) <allow_null,,NULL>
-- Create table using the data type
CREATE TABLE <table_name,sysname,test_data_type>
(
ID int NOT NULL,
Phone <schema_name,sysname,dbo>.<type_name,sysname,Phone> NULL
)
GO
You 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 NaturalNumber FROM int;
GO
CREATE TYPE ShortString FROM nvarchar(20);
GO
CREATE TYPE ItemCode FROM nchar(10);
GO
CREATE TYPE LongString FROM nvarchar(80);
GO
CREATE TYPE Salary FROM decimal(8, 2);
GO
CREATE TYPE Boolean FROM bit;
GO
|
Using a User-Defined Type |
|
After creating a UDT, you can use it as you see fit. For
example, you can declare a variable for it. Then, before using it, you must
initialize it with the appropriate value. Here are examples:
DECLARE @EmployeeID NaturalNumber,
@EmployeeNumber ItemCode,
@FirstName ShortString,
@LastName ShortString,
@Address LongString,
@HourlySalary Salary,
@IsMarried Boolean;
SET @EmployeeID = 1;
SET @EmployeeNumber = N'28-380';
SET @FirstName = N'Gertrude';
SET @LastName = N'Monay';
SET @Address = N'1044 Alicot Drive';
SET @HourlySalary = 26.75;
SET @IsMarried = 1;
SELECT @EmployeeID AS [Empl ID], @EmployeeNumber AS [Empl #],
@FirstName AS [First Name], @LastName AS [Last Name],
@Address, @HourlySalary AS [Hourly Salary],
@IsMarried AS [Is Married ?];
GO
Of course, you can mix Transact-SQL data types and your own
defined type in your code.
- Write a statement that, when given the yearly salary of a person,
can evaluate the hourly salary (consider that the yearly salary is based on
40 hours a week)
- Write a statement so that, given the base and the height of a triangle, it
calculates and displays the area (the area of a triangle is b * h / 2)
|
Exercise: Utility Company |
|
- Open a query window from the UtilityCompany1 database
- Declare a currency variable named BaseCharge and initialize it at 8.50
- Declare an integral variable named First700 and initialize it at 6.50
- Create and execute a statement that will multiply a number such as 224
to the First700 variable but the value of First700 must be considered in a
percentage
- Create and execute an statement that will add 8.50 to the previous
result to get the total invoice for the month
- Get your research papers on US regions and New England
- Connect to the server from the Command Prompt and access the
UnitedStatesRegions1 database
- Declare a variable name Region1 and initialize it with zero
- Write a statement that adds the populations of the different states of
New England, then calculates the average population of New England, and
assign this value to the variable declared previously
- Display the result under the column Average New England Population
- Exit the Command Prompt
|
|
