In the previous lesson, we used some values such as 242 or 'James Knight'. These types of values are referred to as constant because we certainly know them before their use and we don't change them in our statements.
If you intend to use a certain category of value over and over again, you can reserve a section of memory for that value. This allows you to put the value in an area of the computer's memory, easily change the value for another, over and over.
To use the same area of memory to store and remove values as needed, the SQL interpreter needs two primary pieces of information: a name and the desired amount of space in memory capable of storing the value.
A variable is an area of memory used to store values that can be used in a program. Before using a variable, you must inform the interpreter. This is also referred to as declaring a variable. To declare a variable, use the DECLARE keyword using the following formula:
A name cannot be one of the following words reserved for Transact-SQL internal keywords:
To declare a variable, as we will see in the next sections, after giving a name to a variable, you must also specify the amount of memory that the variable would need. The amount of memory is also called a data type. Therefore, the declaration of a variable uses the following formula:
DECLARE @VariableName DataType;
You can also declare more than one variable. To do that, separate them with a comma. The formula would be:
DECLARE @Variable1 DataType1, @Variable2 DataType2, @Variable_n DataType_n;
Unlike many other languages like C/C++, C#, Java, or Pascal, if you declare many variables that use the same data type, the name of each variable must be followed by its own data type.
Initializing a Variable
After declaring a variable, the interpreter reserves a space in the computer memory for it but the space doesn't necessarily hold a recognizable value. This means that, at this time, the variable is null. One way you can change this is to give a value to the variable. This is referred to as initializing the variable.
Remember that a variable's name starts with @ and whenever you need to refer to the variable, you must make sure you include the @ sign. To initialize a variable, in the necessary section, type the SELECT or the SET keyword followed by the name of the variable, followed by the assignment operator "=", followed by an appropriate value. The formula used is:
SELECT @VariableName = DesiredValue
or
SET @VariableName = DesiredValue
Once a variable has been initialized, you can make its value available or display it. This time, you can type the name of the variable to the right side of PRINT or SELECT.
Data Types
Introduction
After setting the name of a variable, you must specify the amount of memory that the variable will need to store its value. Since there are various kinds of information a database can deal with, SQL provides a set of data types.
Boolean Variables
A Boolean value is a piece of information stated as being true or false, On or Off, Yes or No, 1 or 0. To declare a variable that holds a Boolean value, you can use the BIT or bit keyword. Here is an example:
DECLARE @IsOrganDonor bit;
After declaring a Boolean variable, you can initialize it with 0 or another value. If the variable is initialized with 0, it receives the Boolean value of False. If it is initialized with any other number, it receives a True value. Here is an example of using a Boolean variable:
Practical Learning: Using Boolean Variables
1. In the Query window, type the following:
- DECLARE @IsMarried bit
- SET @IsMarried = 1
- SELECT @IsMarried AS [Is Married?];
- GO
2. Execute the statement
Natural Numbers Types
Introduction
An integer, also called a natural number, or a whole number, is a number that can start with a + or a - sign and is made of digits. Between the digits, no character other than a digit is allowed. In the real world, when a number is (very) long and becomes difficult to read, such as 79435794, you are allowed to type a symbol called the thousand separator in each thousand increment. An example is 79,435,794. In your SQL expressions, never include the thousand separator: you would receive an error.
When the number starts with +, such as +44 or +8025, such a number is referred to as positive and you should omit the starting + sign. This means that the number should be written as 44 or 8025. Any number that starts with + or simply a digit is considered as greater than 0 or positive. A positive integer is also referred to as unsigned. On the other hand, a number that starts with a - symbol is referred to as negative.
Normal Integers
If a variable would hold natural numbers in the range of -2,147,483,648 to 2,147,483,647, you can declare it with the int keyword as data type. Here is an example:
- DECLARE @Category int;
- SET @Category = 1450;
- PRINT @Category;
- GO
This would produce 1450:
The length of an integer is the number of bytes its field can hold. For an int type, that would be 4 bytes.
Small Integers
If you want to use very small numbers such as student's ages, or the number of pages of a brochure or newspaper, use the tinyint data type. A variable with the tinyint data type can hold positive numbers that range from 0 to 255. Here is an example:
The smallint data type follows the same rules and principles as the int data type except that it is used to store smaller numbers that would range between -32,768 and 32,767. Here is an example:
- DECLARE @NumberOfPages SMALLINT;
- SET @NumberOfPages = 16;
- SELECT @NumberOfPages AS [Number of Pages];
- GO
Number of Pages
---------------------
16
(1 rows affected)
Long Integers
The bigint data type follows the same rules and principles as the int data type except that it can hold very large numbers from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. Here is an example:
- DECLARE @CountryPopulation BigInt;
- SET @CountryPopulation = 16500000;
- SELECT @CountryPopulation AS 'Country Population';
- GO
Country Population
------------------------
16500000
(1 rows affected)
Binary Integers
The binary data type is used for a variable that would hold hexadecimal numbers. Examples of hexadecimal numbers are 0x7238, 0xFA36, or 0xAA48D. 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.
Practical Learning: Using Integer Variables
1. Change the statement as follows:
- DECLARE @IsMarried bit, @EmplStatus int;
- SET @IsMarried = 1;
- SET @EmplStatus = 2;
- SELECT @IsMarried AS [Is Married?],
- @EmplStatus AS [Employment Status];
- GO
2.Execute the statement:
Decimal Numbers Types
Introduction
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:
- DECLARE @Distance DECIMAL;
- SET @Distance = 648.16;
- PRINT @Distance;
- GO
648
Real Numeric Types
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:
- DECLARE @Radius FLOAT;
- SET @Radius = 48.16;
- SELECT @Radius AS Radius;
- 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
1. 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
2. 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:
- DECLARE @YearlyIncome Money;
- SET @YearlyIncome = 48500.15;
- SELECT @YearlyIncome AS [Yearly Income];
- 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
1. 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
2. Execute the statement
Characters and Strings
Character Values
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:
- DECLARE @Gender char;
- SET @GENDER = 'M';
- SELECT @Gender AS Gender;
- 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:
- DECLARE @Gender char;
- SET @Gender = N'Male';
- SELECT @Gender AS Gender;
- GO
Gender
--------
M
(1 rows affected)
Strings
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:
- DECLARE @FirstName nchar(20),
- @LastName nchar(20);
- SET @FirstName = N'Philomène';
- SET @LastName = N'Açore';
- SELECT @FirstName As "Prénom",
- @LastName As "Nom de Famille";
- 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
1. 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
2 Execute the statement
3 Save the file as Variables in your My Documents folder
Date and Time Types
Time Values
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:
- DECLARE @ArrivalTime time;
- SET @ArrivalTime = N'18:22';
- SELECT @ArrivalTime AS [Arrival Time];
- 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 ".".
Date Values
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:
- DECLARE @IndependenceDay DATETIME;
- SET @IndependenceDay = N'01/01/1960';
- SELECT @IndependenceDay AS [Independence Day];
- 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
1. 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
2. Execute the statement
3. Close the query window
4. 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.
User-Defined Types
Introduction
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...
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.