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  /
cheap cialis online
--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 ???????? ??????.

?c??????: TechNet Magazine

• Vpn сервер Windows 2008
• Планировщик заданий Windows server 2008
• Посмотреть фрагментацию таблиц ms sql
• Установить сертификат на компьютер
• Локальная группа размеры

Tags: mpd, SQL, SQL Server