Manipulating UDT Data
Transact-SQL provides no specialized syntax for INSERT, UPDATE, or DELETE statements when modifying data in user-defined type (UDT) columns. The Transact-SQL CAST or CONVERT functions are used to cast native data types to the UDT type.
Inserting Data in a UDT Column
The following Transact-SQL statements insert three rows of sample data into the Points table. The Point data type consists of X and Y integer values that are exposed as properties of the UDT. You must use either the CAST or CONVERT function to cast the comma-delimited X and Y values to the Point type. The first two statements use the CONVERT function to convert a string value to the Point type, and the third statement uses the CAST function:
INSERT INTO dbo.Points (PointValue) VALUES (CONVERT(Point, '3,4'));
INSERT INTO dbo.Points (PointValue) VALUES (CONVERT(Point, '1,5'));
INSERT INTO dbo.Points (PointValue) VALUES (CAST ('1,99' AS Point));
Selecting Data
The following SELECT statement selects the binary value of the UDT.
SELECT ID, PointValue FROM dbo.Points
To see the output displayed in a readable format, call the ToString method of the Point UDT, which converts the value to its string representation.
SELECT ID, PointValue.ToString() AS PointValue
FROM dbo.Points;
This produces the following results.
IDPointValue
----------
13,4
21,5
31,99
You can also use the Transact-SQL CAST and CONVERT functions to achieve the same results.
SELECT ID, CAST(PointValue AS varchar)
FROM dbo.Points;
SELECT ID, CONVERT(varchar, PointValue)
FROM dbo.Points;
The Point UDT exposes its X and Y coordinates as properties, which you can then select individually. The following Transact-SQL statement selects the X and Y coordinates separately:
SELECT ID, PointValue.X AS xVal, PointValue.Y AS yVal
FROM dbo.Points;
The X and Y properties return an integer value, which is displayed in the result set.
IDxValyVal
----------
134
215
3199
Working with Variables
You can work with variables using the DECLARE statement to assign a variable to a UDT type. The following statements assign a value using the Transact-SQL SET statement and display the results by calling the UDT's ToString method on the variable:
DECLARE @PointValue Point;
SET @PointValue = (SELECT PointValue FROM dbo.Points
WHERE ID = 2);
SELECT @PointValue.ToString() AS PointValue;
The result set displays the variable value:
PointValue
----------
-1,5
The following Transact-SQL statements achieve the same result using SELECT rather than SET for the variable assignment:
DECLARE @PointValue Point;
SELECT @PointValue = PointValue FROM dbo.Points
WHERE ID = 2;
SELECT @PointValue.ToString() AS PointValue;
The difference between using SELECT and SET for variable assignment is that SELECT allows you to assign multiple variables in one SELECT statement, whereas the SET syntax requires each variable assignment to have its own SET statement.
Comparing Data
You can use comparison operators to compare values in your UDT if you have set the IsByteOrdered property to true when defining the class. For more information, see Creating a User-Defined Type.
SELECT ID, PointValue.ToString() AS Points
FROM dbo.Points
WHERE PointValue > CONVERT(Point, '2,2');
You can compare internal values of the UDT regardless of the IsByteOrdered setting if the values themselves are comparable. The following Transact-SQL statement selects rows where X is greater than Y:
SELECT ID, PointValue.ToString() AS PointValue
FROM dbo.Points
WHERE PointValue.X < PointValue.Y;
You can also use comparison operators with variables, as shown in this query that searches for a matching PointValue.
DECLARE @ComparePoint Point;
SET @ComparePoint = CONVERT(Point, '3,4');
SELECT ID, PointValue.ToString() AS MatchingPoint
FROM dbo.Points
WHERE PointValue = @ComparePoint;
Invoking UDT Methods
You can also invoke methods that are defined in your UDT in Transact-SQL. The Point class contains three methods, Distance, DistanceFrom, and DistanceFromXY. For the code listings defining these three methods, see Coding User-Defined Types.
The following Transact-SQL statement calls the PointValue.Distance method:
SELECT ID, PointValue.X AS [Point.X],
PointValue.Y AS [Point.Y],
PointValue.Distance() AS DistanceFromZero
FROM dbo.Points;
The results are displayed in the Distance column:
IDXYDistance
------------------------
1345
2155.09901951359278
319999.0050503762308
The DistanceFrom method takes an argument of Point data type, and displays the distance from the specified point to the PointValue:
SELECT ID, PointValue.ToString() AS Pnt,
PointValue.DistanceFrom(CONVERT(Point, '1,99')) AS DistanceFromPoint
FROM dbo.Points;
The results display the results of the DistanceFrom method for each row in the table:
ID PntDistanceFromPoint
---------------------
13,495.0210502993942
21,594
31,990
The DistanceFromXY method takes the points individually as arguments:
SELECT ID, PointValue.X as X, PointValue.Y as Y,
PointValue.DistanceFromXY(1, 99) AS DistanceFromXY
FROM dbo.Points
The result set is the same as the DistanceFrom method.
Updating Data in a UDT Column
To update data in a UDT column, use the Transact-SQL UPDATE statement. You can also use a method of the UDT to update the state of the object. The following Transact-SQL statement updates a single row in the table:
UPDATE dbo.Points
SET PointValue = CAST('1,88' AS Point)
WHERE ID = 3
You can also update UDT elements separately. The following Transact-SQL statement updates only the Y coordinate:
UPDATE dbo.Points
SET PointValue.Y = 99
WHERE ID = 3
If the UDT has been defined with byte ordering set to true, Transact-SQL can evaluate the UDT column in a WHERE clause.
UPDATE dbo.Points
SET PointValue = '4,5'
WHERE PointValue = '3,4';
Updating Limitations
You cannot update multiple properties at once using Transact-SQL. For example, the following UPDATE statement fails with an error because you cannot use the same column name twice in one UDATE statement.
UPDATE dbo.Points
SET PointValue.X = 5, PointValue.Y = 99
WHERE ID = 3
To update each point individually, you would need to create a mutator method in the Point UDT assembly. You can then invoke the mutator method to update the object in a Transact-SQL UPDATE statement, as in the following:
UPDATE dbo.Points
SET PointValue.SetXY(5, 99)
WHERE ID = 3
Deleting Data in a UDT Column
To delete data in a UDT, use the Transact-SQL DELETE statement. The following statement deletes all rows in the table that match the criteria specified in the WHERE clause. If you omit the WHERE clause in a DELETE statement, all rows in the table will be deleted.
DELETE FROM dbo.Points
WHERE PointValue = CAST('1,99' AS Point)
Use the UPDATE statement if you want to remove the values in a UDT column while leaving other row values intact. This example sets the PointValue to null.
UPDATE dbo.Points
SET PointValue = null
WHERE ID = 2
See Also
Other Resources
Working with User-Defined Types in SQL Server