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:
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. Transact-SQL is very flexible when it comes to names. For example, a name can be made of digits only. Here is an example:
There are rules and suggestions you will use for the names:
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.
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
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:
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 1450:
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 @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 natural numbers. The value is considered as a series of bits and each bit can be treated, checked or used individually. A binary value is usually used to carry varous pieces of information where the pieces must be treated as a group; that is, they must be carried together. For example, consider the characteristics of a file. A file can be hidden, compressed, private, locked, etc. A file can have only one, only two, or many of these characteristics. A single binary value can be used to carry of these pieces of information. In most cases, the person (or the system) who (or that) creates the binary value must specify what piece of information each bit holds. Then, when it comes time to use the value, the person (or the system) who (or that) wants it must check the value of a bit or of a combination of bits to see what it produces and retrieve the desired value (if you want to get more information, try to find out how the message in Microsoft Windows (Win32) works or how file attributes are stored in a file).
The value of a binary type can be stored as a normal integer or in hexadecimal format. 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.
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 a column to hold decimal values, 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:
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.
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)
As an alternative, you can declare the variable using a data type named varchar. Here is an example:
DECLARE @FirstName varchar;
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, precede the data type with n, as in either nchar or nvarchar. When initializing the variable, precede its value with N. Here is an example:
1> DECLARE @Gender nchar; 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 are examples:
DECLARE @Gender char; DECLARE @Code nchar; DECLARE @FirstName varchar; DECLARE @LastName nvarchar; SET @Gender = N'Male'; SET @Code = N'7HHF-294'; SET @FirstName = 'Yolanda'; SET @LastName = N'Williamson';;
Notice that you can initialize the variable with N' whether it was declared as char, nchar, varchar, or nvarchar. You will not receive an error.
In the above introduction, we saw how to declare a variable that would hold one character or one symbol. A string is a combination of characters or symbols of any kind. When you declare a variable of type char, the database engine reserves enough memory space that can hold up to 8000 characters. If you declare the variable as varchar, the database engine makes it possible to store up to 231 characters. In both cases, the database engine doesn't assume that you will use all the space that was allocated. If fact, you should indicate how much space your variable will need. Thie is referred to as the maximum length of a string. To provide this information, after the name of the data type, add the parentheses. In the parentheses, enter the desired number. Here are examples:
DECLARE @Gender char(1); DECLARE @FirstName varchar(20);
You can then initialize the variable(s) as we have done earlier. Here are examples:
DECLARE @Gender char(1); DECLARE @FirstName varchar(20); SET @Gender = 'Male'; SET @FirstName = 'Yolanda';
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 a Query window, include the string in single-quotes:
Text is consider a group of strings, lines, or paragraphs that must be stored beyond the amount memory of a normal string. To store such text, Transact-SQL provides many alternatives.
Transact-SQL originally supported a data type named text (and ntext) used for long text. A better alternative is to use either varchar() or nvarchar() and specify the length using the max keyword. Here is an example:
declare @TermPaper nvarchar(max);
In this case, varchar(max) or nvarchar(max) is called a large-value data type. You can initialize the variable using any of the rules we reviewed for strings.
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:
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, YYYY-MM-DD, which is the default date format in Microsoft SQL Server. 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:
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.
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.
Transact-SQL (and many other languages) provides data types that be used to hold almost any type of value, including values of types we have already reviewed. Because these types can be vague and difficult to identify, you should hardly use them.
Transact-SQL provides the sql_variant data type. It can be used in place of any of the data types we have seen so far. This means that you can declare its variable like any other. When initializing the variable, you should use the appropriate format, whether it is a string, a number, or a date, etc. Here are examples:
DECLARE @FullName SQL_VARIANT, @DateHired Sql_Variant, @IsMarried SQL_variant, @YearlyIncome sql_variant; SET @FullName = N'Paul Yamo'; SET @DateHired = N'20110407'; SET @IsMarried = 1; SET @YearlyIncome = 48500.15; SELECT @FullName AS [Full Name]; SELECT @DateHired AS [Date Hired]; SELECT @IsMarried AS [Is Married?]; SELECT @YearlyIncome AS [Yearly Income]; GO
All of the data types we have used so far provided their values in a single and simple format. In fact, those values were based on types we have studied since elementary school. Transact-SQL provides some values that are based on coordinates of a geometrical shape or on space. These are referred to as spatial types.
Transact-SQL provides support for coordinates of a geometric figures. It does this through a data type named geometry. Based on this, you can declare a variable of this type. Here is an example:
DECLARE @Location geometry;
Unlike the data types we have used so far, the geometry data is in fact a type of object (in an object-oriented language, we would call it a class). This means that it has characteristics (called properties) and actions (called member functions or methods; a method is function created as a member of a class). The characteristics of the geometry type are defined by the Open Geospatial Consortium (OGC). To adapt the data type to Transact-SQL, Microsoft added some functionalities to the type.
As is usual for all data types, after declaring a geometry variable, you must initialize it before using it. As mentioned already, geometry is in fact a class and it has methods that allow it to perform actions. The most fundamental action is to initialize the variable. To support this, the class is equipped with a method named STGeomFromText. Its syntax is:
static geometry STGeomFromText('geography_tagged_text', SRID)
The static keyword indicates that this is a static method. This means that, to access it, you use geometry::STGeomFromText. This method returns a value or type geometry. Here is an example:
DECLARE @Location geometry; SET @Location = geometry::STGeomFromText(. . .)
This method takes two arguments. The first argument is holds a value identified as a Well-Known Text (WKT) value. The value follows a format defined by OGC. There are various you can specify this value. As you may know already, a geometric point is an object that has two values: the horizontal coordinate x and the vertical coordinate y. The value can be integers or flowing-point numbers.
If you know the coordinates of a point and you want to use it as the value of the geometry object, type point() (or POINT(), this is not case-sensitive) and, in the parentheses, type both values separated by a space. Here is an example:
DECLARE @Location geometry; SET @Location = geometry::STGeomFromText('point(6 4)', . . .
Instead of just one point, you may want to use a geometric value that is a line. In this case, specify the shape as linestring(, ). In the parentheses and on both sides of the comma, type each point as x and y. Here is an example:
DECLARE @Location geometry; SET @Location = geometry::STGeomFromText('linestring(1 4, 5 2)', . . .);
You can also use a complex geometric, in which case you can pass the argument as a polygon. Use polygon(()) (or POLYGON(())) and pass the vertices in the parentheses. Each vertext should specify its x and y coordinates. The vertices are separated by commas. A last vertex should be used to close the polygon, in which case the first and the last vertices should be the same. Here is an example:
DECLARE @Location geometry; SET @Location = geometry::STGeomFromText('polygon((1 2, 2 5, 5 5, 4 2, 1 2))', . . );
The second argument of the geometry::STGeomFromText method is a contant integer known as the spatial reference ID (SRID).
After declaring and initializing the value, you can use a SELECT statement to display its value. Here is an example:
DECLARE @Location geometry; SET @Location = geometry::STGeomFromText('point(6 4)', 0); SELECT @Location;
Transact-SQL supports geographical locations.
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.
Before creating a user-defined data type, you must be familiar with the existing types. 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.
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
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.
A composite operation consists of performing the operation from a variable to itself. For example, suppose you have a variable a that has a value and you want to change the value of that variable by adding its own value to itself. Composite operations use an operator that is in fact a combination of two operators. The variable can be almost any type that supports the type of operation you want to perform.
The composite operation uses the += operator. Using it, to add the value of a variable to itself, type the variable and insert this operation between both operands. Here is an example:
DECLARE @Variable int; SET @Variable = 248; SET @Variable += @Variable;
Once you have performed the operation, the variable holds the new value. Consider this:
As mentioned already, a variable that is involved in a composite operation can be of any type as long as the type supports that operation. For example, strings in Transact-SQL support the addition. This means that the variable can be of type char or any of its variants.
One variant of the composite operation is to add oone variable to another. To do this, include the += operator between the operants. Here is an example:
DECLARE @Name nvarchar(50); DECLARE @LastName nvarchar(20); SET @Name = N'Paul'; SET @LastName = N' Yamaguchi'; SET @Name += @LastName;
When the operation has been performed, the left operand now holds its value and that of the other variable:
Another variant of the composite operation consists of adding a constant to a variable. In this case, on the right side of the += operator, use the constant. Here is an example:
DECLARE @FullName nvarchar(50); SET @FullName = N'Paul'; SET @FullName += N' Motto'; SELECT @FullName;
Once again, remember that when the operation has been performed, the variable holds the new value. Here is an example:
In the same way, you can perform this operation as many time as you want by adding right operands to a left operands. Here are examples:
DECLARE @Name nvarchar(50); DECLARE @MiddleName nvarchar(20); DECLARE @LastName nvarchar(20); SET @Name = N'Paul'; SET @MiddleName = N' Bertrand'; SET @LastName = N' Yamaguchi'; SET @Name += @MiddleName; SELECT @Name; SET @Name += @LastName; SELECT @Name;
One important thing you must keep in mind is the storage capacity of the left operand: It must be able to hold all values added to it.
The concept of composite operation can be applied to all arithmetic binary operations. As seen above, strings also support the addition composite operation. Composite operations are also available on all bit manipulation operations. The most important thing to remember is that not all data types support all operations. Overall:
You should know that these operations can be performed on natural or decimal numbers.