|
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:
![Declaring decimal variables]()
|
![Practical Learning]() 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
![Declaring currency variables]()
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:
![Initializing string variables]()
Therefore, if using the query window, include
the
string in single-quotes:
![Variables]()
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
![Initializing string variables]() - 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:
![Date]()
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:
![Date]()
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
![Date]()
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
![Date]()
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
![Declaring string variables]()
- 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. |