Новые типы данных в SQL Server 2008

Ведение бизнеса в глобальной экономике заставляет компании все больше использовать новые типы данных, новые приложения и сложные расчеты. Семь новых типов данных, встроенных в SQL Server 2008, упрощают работу и управление для более сложных данных.

Дата и время

Используя старый формат datetime, пользователи SQL Server не могли работать с информацией о дате и времени отдельно. Четыре новых типа данных - date, time, datetime2 и datetimeoffset - меняют дело, упрощая работу с данными о дате и времени, обеспечивая больший диапазон дат, точность до долей секунды и поддержку часовых поясов. Новые приложения для баз данных будут использовать эти новые типы данных вместо устаревшего datetime. Посмотрим на эти новые версии вблизи.

Тип данных date сохраняет дату без компонента времени. Диапазон - от 1 января 1000 года до 31 декабря 9999 года (от 0001-01-01 до 9999-12-31). Каждая переменная даты требует трех байтов для хранения и имеет точность в 10 цифр. Точность типа данных ограничена отдельным днем.

На рис. 1 показано, как содавать и инициализировать переменные Date в сценариях T-SQL. Переменная @myDate1 инициализируется строкой в формате 'MM/DD/YYYY'. Переменная @myDate2 не инициализируется, она будет иметь значение NULL. Переменная @myDate3 инициализируется на дату локальной системы компьютера. Значение переменных может быть в любой момент изменено с помощью инструкций SELECT или SET, это показано на примере изменения значения @myDate2. Столбцы дат можно создавать и в таблицах. На рис. 2 показано, как создать таблицу с тремя столбцами дат.

Рис 1 Create and initialize date variables in T-SQL scripts

DECLARE @myDate1 date = '01/22/2005'
DECLARE @myDate2 date
DECLARE @myDate3 date = GetDate()
SELECT @myDate2 = '2007-05-08 12:35:29.1234567 +12:15'
SELECT @myDate1 AS '@myDate1',
       @myDate2 AS '@myDate2',
       @myDate3 AS '@myDate3'
--Results
--@myDate1   @myDate2   @myDate3
------------ ---------- ----------
--2005-01-22 2007-05-08 2007-11-20

Рис 2 Create a table with three date columns

USE TempDB
GO
CREATE TABLE myTable
(
    myDate1 date,myDate2 date,myDate3 date
)
GO
INSERT INTO myTable
VALUES('01/22/2005',
       '2007-05-08 12:35:29.1234567 +12:15',
       GetDate())
SELECT * FROM myTable
--Results
--myDate1    myDate2    myDate3
------------ ---------- ----------
--2005-01-22 2007-05-08 2007-11-20

Тип данных time сохраняет время суток без компонента даты. Он основан на 24-часовом формате, поэтому поддерживаемый диапазон - от 00:00:00.0000000 до 23:59:59.9999999 (часы, минуты, секунды и доли секунды). Точность в долях секунды можно задать при создании типа данных. По умолчанию используется 7 цифр и точность 100 наносекунд. Точность влияет на занимаемый объем, который меняется от 3 байт для двух цифр, 4 байт для от 3 до 4 цифр и до 5 байт для от 5 до 7 цифр.

Сценарий T-SQL на рис. 3 показывает, как неявные преобразования значения инициализации строки влияют на точность переменной. Сперва код T-SQL создает и инициализирует на одинаковые значения восемь отдельных переменных времени. Точность в долях секунды каждой переменной соответствует ее названию. Например, @myTime3 имеет точность в три знака после запятой. Результаты показывают, что точность каждого типа данных time эквивалентна точности в долях, с которой она объявляется. Цифры, не попадающие в диапазон, отбрасываются.

Рис. 3 Display time data type's variable precision

DECLARE @myTime  time = '01:01:01.1234567 +01:01'
DECLARE @myTime1 time(1) = '01:01:01.1234567 +01:01'
DECLARE @myTime2 time(2) = '01:01:01.1234567 +01:01'
DECLARE @myTime3 time(3) = '01:01:01.1234567 +01:01'
DECLARE @myTime4 time(4) = '01:01:01.1234567 +01:01'
DECLARE @myTime5 time(5) = '01:01:01.1234567 +01:01'
DECLARE @myTime6 time(6) = '01:01:01.1234567 +01:01'
DECLARE @myTime7 time(7) = '01:01:01.1234567 +01:01'
SELECT @myTime  AS '@myTime',
       @myTime1 AS '@myTime1',
       @myTime2 AS '@myTime2',
       @myTime3 AS '@myTime3',
       @myTime4 AS '@myTime4',
       @myTime5 AS '@myTime5',
       @myTime6 AS '@myTime6',
       @myTime7 AS '@myTime7'
--Results
--@myTime          @myTime1   @myTime2    @myTime3     @myTime4      
------------------ ---------- ----------- ------------ -------------
--01:01:01.1234567 01:01:01.1 01:01:01.12 01:01:01.123 01:01:01.1235
--
--@myTime5       @myTime6        @myTime7
---------------- --------------- ----------------
--01:01:01.12346 01:01:01.123457 01:01:01.1234567
DROP TABLE myTable

Тип данных time можно создать как столбец в таблице. Сценарий T-SQL DROP TABLE myTable на рис. 4 создает таблицу "myTable1" и добавляет в нее три столбца времени. Затем в таблицу помещается запись; содержимое таблицы отображается с помощью инструкции SELECT.

Рис. 4 Create myTable1

USE TempDB
GO
CREATE TABLE myTable1
(
    myTime1 time(1),
    myTime2 time(2),
    myTime3 time(3)
)
GO
INSERT INTO myTable1
VALUES('01:30:01.1234567',
       '02:34:01.1234567',
       '03:01:59.1234567')
SELECT * from myTable1
--Results
--myTime1    myTime2     myTime3
------------ ----------- ------------
--01:30:01.1000000 02:34:15.1200000 03:01:59.1230000
DROP TABLE myTable1

Datetimeoffset и Datetime2

Тип данных datetimeoffset обеспечивает сведения о часовом поясе. Тип данных time не содержит часового пояса и работает только для местного времени. На глобальых рынках, тем не менее, часто нужно знать, как время в одной части планеты соотносится со временем в другой. Сдвиг часового пояса одзначается как + или - ЧЧ:мм.

Этот код создает переменную datetimeoffset и инициализирует ее на значение времени 8:52 по стандартному тихоокеанскому времени:

DECLARE @date DATETIMEOFFSET = '2007-11-26T08:52:00.1234567-08:00'
PRINT @date
--Results
--2007-11-26 08:52:00.1234567 -08:00

Строка, которая инициализирует переменную datetimeoffset (в сценарии - @date) форматируется особым образом, с расположением от наиболее значимого элемента к наименее. Элементы даты и времени разделяются одной заглавной T. Знак "минус" отделяет элементы времени от часового пояса. Между минусом и элементами времени или часового пояса нет пробелов. Этот формат - один из двух форматов ISO 8601, поддерживаемых типом данных datetimeoffset. (ISO 8601 - это международный стандарт записи даты и времени.)

Точность компонента времени задается так же, как для типа данных time, по умолчанию - те же семь цифр. Диапазон - такой же.

Тип данных datetime2 - это расширение исходного типа datetime. Он поддерживает больший диапазон дат и большую точность в долях секунды, позволяя задавать точность. Диапазон дат типа datetime2 - от 1 января 0001 до 31 декабря 9999, в отличие от диапазона исходного datetime от 1 января 1753 до 31 декабря 9999. Как и в типе time, доступна точность долей секунды в семь знаков. Исходный тип datetime позволял использовать три цифры и диапазон времени от 00:00:00 до 23:59:59.999. Вот как создается и инициализируется на время и дату локального сервера переменная datetime2:

DECLARE @datetime2 DATETIME2 = GetDate();
PRINT @datetime2
--Results
--2007-11-26 09:39:04.1370000

Теперь посмотрим на новый тип данных hierarchyid. Этот тип данных работает с отношением между элементами данных в таблице, а не с конкретными данными о дате или времени.

Тип данных hierarchyid

Тип данных hierarchyid позволяет создавать отношения между элементами данных в таблице, для того, чтобы задать позицию в иерархии. Начнем изучать этот тип данных, создав базу данных MyCompany и заполнив ее данными о сотрудниках с помощью сценария с рис. 5.

Рис. 5 Create and populate the MyCompany database

USE MASTER
GO
CREATE DATABASE MyCompany
GO
USE MyCompany
GO
--Create a table called employee that will store
--the data for the employees for MyCompany.
    
CREATE TABLE employee
(
    EmployeeID int NOT NULL,
    EmpName    varchar(20) NOT NULL,
    Title      varchar(20) NULL,
    Salary     decimal(18, 2) NOT NULL,
    hireDate   datetimeoffset(0) NOT NULL,
)
GO
--These statements will insert the data for the employees of MyCompany.
INSERT INTO employee
VALUES(6,   'David',  'CEO', 35900.00, '2000-05-23T08:30:00-08:00')
INSERT INTO employee
VALUES(46,  'Sariya', 'Specialist', 14000.00, '2002-05-23T09:00:00-08:00')
INSERT INTO employee
VALUES(271, 'John',   'Specialist', 14000.00, '2002-05-23T09:00:00-08:00')
INSERT INTO employee
VALUES(119, 'Jill',   'Specialist', 14000.00, '2007-05-23T09:00:00-08:00')
INSERT INTO employee
VALUES(269, 'Wanida', 'Assistant', 8000.00, '2003-05-23T09:00:00-08:00')
INSERT INTO employee
VALUES(272, 'Mary',   'Assistant', 8000.00, '2004-05-23T09:00:00-08:00')
GO
--Results
--EmployeeID  EmpName Title      Salary   hireDate
------------- ------- ---------- -------- --------------------------
--6           David   CEO        35900.00 2000-05-23 08:30:00 -08:00
--46          Sariya  Specialist 14000.00 2002-05-23 09:00:00 -08:00
--271         John    Specialist 14000.00 2002-05-23 09:00:00 -08:00
--119         Jill    Specialist 14000.00 2007-05-23 09:00:00 -08:00
--269         Wanida  Assistant  8000.00  2003-05-23 09:00:00 -08:00
--272         Mary    Assistant  8000.00  2004-05-23 09:00:00 -08:00

Получается показанная на рис. 6 простая база данных, состоящая из одной таблицы сотрудников. Эта таблицы в базе данных MyCompany не имеет определенной структуры. Это нормально для реляционной базы данных, где структура определяется динамически за счет приложения через его его запрос и код обработки.

Рис. 6 Таблица сотрудников MyCompany 

Однако деловая информация чаще всего имеет некую внутренную структуру. Например, каждая фирма имеет некую схему отчетности, как та, которая показана для MyCompany на рис. 7. Все сотрудники MyCompany отчитываются перед директором (CEO) Дэвидом (David). Некоторые отчитываются напрямую, как Джилл (Jill). Некоторые, как Мэри (Mary), отчитываются через промежуточное звено. В терминах программирования, структура отчетности MyCompany можно назвать деревом, она напоминает его по форме. Дэвид, на самом верху, ни перед кем не отчитывается; он "родитель" или "предок". Сотрудники, отчитывающиеся перед Дэвидом, находятся ниже. Такие узлы называются "дочерними" или "потомками". У Дэвида может быть столько "потомков", сколько нужно, чтобы обозначить прямые отчеты.

Рис. 7 Организационная структура MyCompany 

Сценарий на рис. 8 строит базу данных MyCompany с помощью типа данных hierarchyid, создавая отношения, соответствующие структуре отчетности MyCompany. Дял добавления столбца типа hierarchyid используется инструкция ALTER TABLE. Затем, с помощью метода hierarchyid GetRoot вставляется узел Дэвида. Затем в дерево добавляются отчитывающиеся перед Дэвидом напрямую с помощью метода GetDescendant.

Рис. 8 Rebuild the database using hierarchyid

DELETE employee
GO
ALTER TABLE employee ADD OrgNode hierarchyid NOT NULL
GO
DECLARE @child hierarchyid,
@Manager hierarchyid = hierarchyid::GetRoot()
--The first step is to add the node at the top of the
--tree. Since David is the CEO his node will be the
--root node.
INSERT INTO employee
VALUES(6,   'David',  'CEO', 35900.00,
       '2000-05-23T08:30:00-08:00', @Manager)
--The next step is to insert the records for
--the employees that report directly to David.
SELECT @child = @Manager.GetDescendant(NULL, NULL)
INSERT INTO employee
VALUES(46,  'Sariya', 'Specialist', 14000.00,
       '2002-05-23T09:00:00-08:00', @child)
SELECT @child = @Manager.GetDescendant(@child, NULL)
INSERT INTO employee
VALUES(271, ‚John',   ‚Specialist', 14000.00,
       '2002-05-23T09:00:00-08:00', @child)
SELECT @child = @Manager.GetDescendant(@child, NULL)
INSERT INTO employee
VALUES(119, ‚Jill',   ‚Specialist', 14000.00,
       ‚2007-05-23T09:00:00-08:00', @child)
--We can now insert the employee that reports to
--Sariya.
SELECT @manager = OrgNode.GetDescendant(NULL, NULL)
FROM employee WHERE EmployeeID = 46
INSERT INTO employee
VALUES(269, ‚Wanida', ‚Assistant', 8000.00,
       ‚2003-05-23T09:00:00-08:00', @manager)
--Next insert the employee that report to John.
SELECT @manager = OrgNode.GetDescendant(NULL, NULL)
FROM employee WHERE EmployeeID = 271
INSERT INTO employee
VALUES(272, ‚Mary',   ‚Assistant', 8000.00,
       ‚2004-05-23T09:00:00-08:00', @manager)

После добавления записей базы данных и создания структуры, содержимое таблицы сотрудников можно вывести с помощью такого запроса:

SELECT EmpName, Title, Salary, OrgNode.ToString() AS OrgNode
FROM employee ORDER BY OrgNode
GO
--Results
--EmpName  Title      Salary    OrgNode
---------- ---------- --------- -------
--David    CEO        35900.00  /
--Sariya   Specialist 14000.00  /1/
--Wanida   Assistant  8000.00   /1/1/
--John     Specialist 14000.00  /2/
--Mary     Assistant  8000.00   /2/1/
--Jill     Specialist 14000.00  /3/

OrgNode - столбец hierarchyid. Каждый символ косой черты (/) в результатах означает узел в дереве иерархии. Дэвид находится в корне, что обозначено одной чертой. Сария (Sariya), Джон (John) и Джил отчитываются перед Дэвидом и имеют по две черты, это значит, что они - второй узел иерархии. Числа 1, 2 и 3 показывают порядковый номер соответствующего дочернего узла. Эта система обладает большой гибкостью. Дочерние узлы можно удалять, добавлять и вставлять как угодно. Если мы, например, добавим сотрудника между Джоном и Джилл, этот сотрудник будет обозначен в результатах как /2.1/.

Чтобы найти ответ на вопрос, положим, "Кто отчитывается перед Сарией?", можно создать запрос вроде этого:

DECLARE @Sariya hierarchyid
SELECT @Sariya = OrgNode
FROM employee WHERE EmployeeID = 46
SELECT EmpName, Title, Salary, OrgNode.ToString() AS 'OrgNode'
FROM employee
WHERE OrgNode.GetAncestor(1) = @Sariya
GO
--Results
--EmpName Title     Salary  OrgNode
--------- --------- ------- -------
--Wanida  Assistant 8000.00 /1/1/

Этот запрос использует метод hierarchyid GetAncestor, который возвращает родительский узел текущего узла hierarchyid. В приведенном коде, переменная @Sariya установлена на узел иерархии Сарии. Так получается потому, что Сария - прямой "предок" любого сотрудника, отчитывающегося перед ней. Поэтому запрос, возвращающий сотрудников, отчитывающихся прямо перед Сарией, должен состоять из получения ее узла из дерева, а затем выбора всех сотрудников, для которых узел-предок - это узел Сарии.

Столбцы hierarchyid обычно очень компактные, потому что количество бит, требующихся для представления узла дерева, зависит от среднего числа дочерних узлов (которое обычно называют ветвлением узла). Например, новый узел в организационной иерархии 100000 сотрудников со средним уровнем ветвления шесть уровней, займет примерно пять байт места.

Тип данных hierarchyid обеспечивает несколько методов, облегачающих работу с иерархическими данными. Они обобщены на рис. 9. Подробную информацию о всех методах можно найти на SQL Server Books Online (электронные книги по SQL Server) (msdn2.microsoft.com/ms130214).

Рис. 9 Methods provided by the hierarchyid data type

Пространственные типы данных

Пространнственные данные - это данные, определяющие географические расположения и формы, преимущественно на Земле. Это могут быть ориентиры, дороги и даже расположение фирмы. В SQL Server 2008 есть географические (geography) и геометрические (geometry) типы данных для работы с этой информацией.

Тип данных geography работает с инфорацией для шарообразной земли. Модель шарообразной земли использует при расчетах кривизну земной поверхности. Информация о положении задается широтой и долготой. Эта модель хорошо годится для приложений, связанных с морскими перевозками, военным планированием и краткосрочными приложениями, имеющими привязку к земной поверхности. Эту модель нужно использовать, если данные хранятся в виде широт и долгот.

Тип данных geometry работает с планарной моделью или моделью плоской земли. В этой модели земля считается плоской проекцией из определенной точки. Модель плоской земли не принимает в расчет кривизну поверхности земли, поэтому используется, в первую очередь, для описания коротких расстояний, например, в базе данных приложения, описывающего внутреннюю часть строения.

Типы geography и geometry создаются из векторных объектов, заданных в форматах Well-Known Text (WKT) или Well-Known Binary (WKB). Это форматы для перенесения пространственных данных, описанные в простых функциях открытого геопространственного консорциума (Open Geospatial Consortium (OGC) Simple Features) для спецификаций SQL (SQL Specification). На рис. 10 перечислены семь типов векторных объектов, поддерживаемых SQL Server 2008.

Рис. 10 Vector objects supported by SQL Server 2008

Чтобы создать тип geography с одним или несколькими векторными объектами, сперва надо объявить тип geography в своем сценарии T-SQL, как на рис. 11. Затем нужно вызвать один из методов с рис. 12 и передать строку символов своего векторного объекта и идентификатор пространственного отношения (Spatial Reference ID) (SRID). SRID - это система идентификации пространственного отношения, созданная европейской группой нефтепоисковых исследований (European Petroleum Survey Group). Это часть набора стандартов, разработанных для картографии, геодезии и хранения геодезических данных. Каждый SRID задает для географических расчетов определенный тип эллипсоида. Это важно, потому что земля - не идеальная сфера. SQL Server 2008 может выполнять расчеты только на идентичных SRID.

Рис. 12 Construct objects for geography and geometry

Рис. 11 Create points, lines, and polygon geometry

DECLARE @geo1 geometry
SELECT @geo1 = geometry::STGeomFromText('POINT (3 4)', 0)
PRINT @geo1.ToString()
DECLARE @geo2 geometry
SELECT @geo2 = geometry::Parse('POINT(3 4 7 2.5)')
PRINT @geo2.STX;
PRINT @geo2.STY;
PRINT @geo2.Z;
PRINT @geo2.M;
DECLARE @geo3 geography;
SELECT @geo3 = geography::STGeomFromText(
    'LINESTRING(47.656 -122.360, 47.656 -122.343)', 4326);
SELECT @geo3.ToString();
--Results
--POINT (3 4)
--3
--4
--7
--2.5
DECLARE @gx geometry; 
SET @gx = geometry::STPolyFromText(
    'POLYGON ((5 5, 10 5, 10 10, 5 5))', 0);
PRINT @gx.ToString();
--Results
--POLYGON ((5 5, 10 5, 10 10, 5 5))

Различия между geography и geometry

Типы данных geography и geometry созданы для работы с различными видами данных, поэтому следует знать о некоторых ключевые отличиях между ними. В типе данных geometry, расстояния и площади задаются в тех же единицах измерения, что и координаты объектов. Например, расстояние между точками (0,0) и (6,8) всегда будет 10 единиц. В типа geography иначе, он использует эллиптические координаты, выражающиеся в градусах широты и долготы.

Если координаты выражены парами широты и долготы, тип данных GEOMETRY возвращает некорректные результаты. Следующий код T-SQL расчитывает расстояние между POINTS (точками) (90 0) и (90 180). Обе они находятся на северном полюсе, поэтмоу расстояние должно быть равным 0. В случае GEOMETRY расчетное расстояние оказывается 180.

DECLARE @g1 GEOMETRY, @g2 GEOMETRY, @g3 GEOGRAPHY, @g4 GEOGRAPHY
SELECT @g1 = GEOMETRY::STGeomFromText('POINT (90 0)', 0)
SELECT @g2 = GEOMETRY::STGeomFromText('POINT (90 180)', 0)
SELECT @g3 = GEOGRAPHY::STGeomFromText('POINT (90 0)', 4326)
SELECT @g4 = GEOGRAPHY::STGeomFromText('POINT (90 180)', 4326)
SELECT @g2.STDistance(@g1) AS 'GEOMETRY',
       @g4.STDistance(@g3) AS 'GEOGRAPHY';
--Results
--GEOMETRY               GEOGRAPHY
------------------------ ----------------------
--180                    0

Ориентация пространственных данных для этих типов данных тоже различается. В планарной системе, используемой типом данных geometry, ориентация многоугольника не является важным фактором. Например, многоугольник с координатами ((0, 0), (10, 0), (0, 20), (0, 0)) - то же самое, что и многоугольник ((0, 0), (0, 20), (10, 0), (0, 0)). Напротив, тип данных geography использует модель, в которой необходимо определять ориентацию многоугольника. Возьмем, например, кольцо вдоль экватора. Относится ли многоугольник, описываемый этим кольцом, к северному или южному полушарию? Это значит, что при работе с данными geography, ориентация и расположение должны описываться точно.

Есть и дополнительные ограничения, накладываемые SQL Server 2008 на тип данных geography. Например, каждый элемент должен попадать в одно полушарие. Большие пространственные объекты недопустимы и вызовут ArgumentException. Типы данных geography, требующие двух вводов, возвращают NULL, если результаты методов не попадают в одно полушарие.

В SQL Server есть несколько методов, позволяющих выполнять операции с объектами geography и geometry. На рис. 13 показаны некоторые примеры использования методов работы с пространственными данными в SQL Server 2008. Я не могу в ограниченном объеме статьи рассказать об этом подробнее, но вы сможете найти полные описания в SQL Server Books Online.

Рис. 13 Working with spatial data

DECLARE @gm geometry;
DECLARE @gg geography;
DECLARE @h geography;
SET @gm = geometry::STGeomFromText('POLYGON((0 0, 13 0, 3 3, 0 13, 0 0),(2 2, 2 1, 1 1, 1 2, 2 2))', 0);
SELECT @gm.STArea();
--Results
--38
SET @gg = geography::STGeomFromText('LINESTRING(0 0, 5 5)', 4326);
--Calculate the distance to a point slightly offset from the LINESTRING.
SET @h = geography::STGeomFromText('POINT(4 4)', 4326);
SELECT @gg.STDistance(@h);
--Results
-- 430.182777043046
--Calculate the distance to a point on the LINESTRING.
SET @h = geography::STGeomFromText('POINT(5 5)', 4326);
SELECT @gg.STDistance(@h);
--Results
-- 0
DECLARE @temp table ([name] varchar(10), [geom] geography);
INSERT INTO @temp values ('Point', geography::STGeomFromText('POINT(
5 10)', 4326));
INSERT INTO @temp values ('LineString', geography::STGeomFromText(
'LINESTRING(13 5, 50 25)', 4326));
--Calculate the distance to a point on the LINESTRING.
--Display the number of dimensions for a geography object stored in a --table variable.
INSERT INTO @temp values ('Polygon', geography::STGeomFromText(
'POLYGON((47.653 -122.358, 47.649 -122.348, 47.658 -122.348, 47.658 -122.358, 47.653 -122.358))', 4326));
SELECT [name], [geom].STDimension() as [dim]
FROM @temp;
--Results
--name       dim
------------ -----------
--Point      0
--LineString 1
--Polygon    2

Надеюсь, что эта информация о семи новых типах данных в SQL Server 2008 принесла пользу.