SQL Server supports two spatial data types: the geometry data type and the geography data type.
The geometry type represents data in a Euclidean (flat) coordinate system.
The geography type represents data in a round-earth coordinate system.
Simply said, geometry handles 2d shapes, it is PLANAR, while geography handles 3d volumes, GEODETIC ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates.
So, for storing simple shapes such as rectangles, triangles coordinates, shapes on a flat surface we would use the geometry data type, and for storing the exact location on a map one would use geography data type. Geography is for terrestrial spatial data (that is, data on the (curved) surface of the Earth).
If you want to make an exercise you can use the code below:
CREATE DATABASE SpatialData;
USE SpatialData;
CREATE TABLE TblSpatialData(
LocationID INT IDENTITY(1,1),
Flat GEOMETRY,
Geodetic GEOGRAPHY
);
GO
INSERT INTO TblSpatialData (Flat, Geodetic )
VALUES (geometry::STGeomFromText('POLYGON ((0 0, 100 0, 120 110, 50 130, 0 0))', 0),
geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656 )', 4326));
GO
SELECT * FROM TblSpatialData;