Variables and Data Types

Variables Fundamentals

Introduction

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.

1. To launch Microsoft SQL Server, click Start -> (All) Programs -> Microsoft SQL Server 2005 -> SQL Server Management Studio
2. On the Connect to Server dialog box, make the correct selections and provide the appropriate information, then click Connect
3. To open the code editor, on the main menu, click File -> New -> Query With Current Connection

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:

DECLARE Options

The DECLARE keyword lets the interpreter know that you are making a declaration. The DECLARE keyword is followed by a name for the variable. In Transact-SQL, the name of a variable starts with the @ sign. The name of a variable allows you to identify the area of memory where the value of the variable is stored. While other languages like C/C++, Pascal, Java, C#, etc, impose strict rules to names, Transact-SQL is extremely flexible. A name can be made of digits only. Here is an example:

DECLARE @264

Such a name made of digits can create confusion with a normal number. A name can also be made of one or more words.

To avoid confusion, here are the rules we will use in our lessons:

• A name will start with either an underscore or a letter. Examples are @_n, @act, or @Second
• After the first character as an underscore or a letter, the name will have combinations of underscores, letters, and digits. Examples are @_n24 or @act_52_t
• A name will not include special characters such as !, @, #, $, %, ^, &, or *
• If the name is a combination of words, each word will start in uppercase. Examples are @DateHired, @_RealSport, or @DriversLicenseNumber

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.

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.

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.

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:

1. In the Query window, type the following:
    

   DECLARE @IsMarried bit SET @IsMarried = 1 SELECT @IsMarried AS [Is Married?]; GO

2. Execute the statement

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.

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:

(1 rows affected)
1> DECLARE @Category INT;
2> SET @Category = 1450;
3> PRINT @Category;
4> GO
1450

(1 rows affected)

The length of an integer is the number of bytes its field can hold. For an int type, that would be 4 bytes.

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:

1> DECLARE @StudentAge tinyint;
2> SET @StudentAge = 14;
3> SELECT @StudentAge AS [Student's Age];
4> GO
Student's Age
-------------
           14

(1 rows affected)

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:

1> DECLARE @NumberOfPages SMALLINT;
2> SET @NumberOfPages = 16;
3> SELECT @NumberOfPages AS [Number of Pages];
4> GO
Number of Pages
---------------
             16

(1 rows affected)

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:

1> DECLARE @CountryPopulation BigInt;
2> SET @CountryPopulation = 16500000;
3> SELECT @CountryPopulation AS 'Country Population';
4> GO
Country Population
--------------------
            16500000

(1 rows affected)

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.

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:
    
   

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. 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:

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

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.

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
    
   

A DATETIME data type is used for a column whose data would consist of date and/or time values. The entries must be valid date or time values but Microsoft SQL Server allows a lot of flexibility, even to display a date in a non-traditional format. The date value of a datetime field can be comprised between January 1st, 1753 and December 31, 9999.

To initialize a DATETIME variable, include its value between single-quote. If the value is a date, separate the components of the value with the symbol recognized in Control Panel as the Date Separator:

Here is an example:

1> DECLARE @IndependenceDay DATETIME;
2> SET @IndependenceDay = '01/01/1960';
3> SELECT @IndependenceDay AS [Independence Day];
4> GO
Independence Day
-----------------------
1960-01-01 00:00:00.000

(1 rows affected)

If the value is a time period, still include it in single-quotes. Inside of the quotes, follows the rules and formats specified in the Control Panel:

Here is an example:

1> DECLARE @ArrivalTime datetime;
2> SET @ArrivalTime = '18:22';
3> SELECT @ArrivalTime AS [Arrival Time];
4> GO
Arrival Time
-----------------------
1900-01-01 18:22:00.000

(1 rows affected)

The smalldatetime data type is an alternative to datetime. It follows the same rules and principles as the datetime data type except that a date value must be comprised between January 1st, 1900 and June 6, 2079.

1. Change the statement as follows:
    

   DECLARE @DateHired DateTime, @EmplStatus int, @IsMarried bit, @WeeklyHours Decimal(6,2), @HourlySalary SmallMoney, @WeeklySalary SmallMoney; SET @DateHired = '12/05/1998'; SET @IsMarried = 1; SET @EmplStatus = 2; SET @WeeklyHours = 36.50; SET @HourlySalary = 15.72; SET @WeeklySalary = @WeeklyHours * @HourlySalary; SELECT @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
    
   

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)

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 = '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:

1> DECLARE @ShelfNumber char(6);
2> SET @ShelfNumber = "CI-422";
3> SELECT @ShelfNumber AS [Shelf #];
4> GO
Shelf #
-------
CI-422

(1 rows affected)

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.

1. Change the statement as follows:
    

   DECLARE @FirstName varchar(20), @LastName varchar(20), @FullName varchar(40), @DateHired DateTime, @EmplStatus int, @IsMarried bit, @WeeklyHours Decimal(6,2), @HourlySalary SmallMoney, @WeeklySalary SmallMoney; SET @FirstName = 'Samuel'; SET @LastName = 'Weinberg'; SET @FullName = @LastName + ', ' +@FirstName; SET @DateHired = '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. Save the file as Variables in your My Documents folder