SQL Server 2008 offers an impressive array of capabilities for developers that build upon key innovations introduced in SQL Server 2005.  The SQL Server 2008 Developer Training Kit will help you understand how to build web applications which deeply exploit the rich data types, programming models and new development paradigms in SQL Server 2008.  The training kit is brought to you by Microsoft Developer and Platform Evangelism.

Overview and Benefits

The training kit offers the following benefits:

• Learn how to build web applications that exploit the unique features and capabilities of SQL Server 2008.
• Provides a comprehensive set of presentations, demos and hands-on labs
• Designed for web developers who are already familiar with SQL Server application development.
• Easy to download and install and works on free editions of SQL Server 2008 and Visual Studio 2008.

Software Requirements

The following software is not included with the training kit and must be downloaded and installed separately.  I’ve included the links for you here.

• SQL Server 2008 Express with Advanced Services (or higher-level SKU)
• Microsoft Visual Web Developer 2008 Express (or higher-level SKU)  - via the Windows Platform Installer
• AdventureWorks 2008 Sample Databases

Contents

The training kit includes the following content:

• Presentations (6)
  • Filestream
  • Spatial
  • T-SQL
  • Date and Time Types
  • SQLCLR
  • Reporting Services
• Demos (13)
  • AdventureWorks Racing All-Up SQL Server 2008 Demo
  • SQL Server 2008 All-Up Spatial Demo
  • Spatial Types Demo
  • Intro to Filestream Demo
  • SQL CLR Nullable Types Demo
  • Programming with Filestream Demo
  • Reporting Services Web Application Integration Demo
  • Date and Time Support in SQL Server 2008 Demo
  • T-SQL Table-Valued Parameters Demo
  • T-SQL Row Constructors Demo
  • T-SQL Grouping Sets Demo
  • T-SQL Merge Demo
  • Trasact-SQL
• Hands-on Labs   (3)
  • Using Spatial Data in TSQL
  • Using Spatial Data in Managed Code
  • Using SQL CLR in SQL Server 2008

Get the kit here, http://go.microsoft.com/?linkid=9665942

Microsoft just released the SQL Server 2008 Developer Training Kit.  Here’s the overview:

SQL Server 2008 offers an impressive array of capabilities for developers that build upon key innovations introduced in SQL Server 2005. The SQL Server 2008 Developer Training Kit will help you understand how to build web applications which deeply exploit the rich data types, programming models and new development paradigms in SQL Server 2008. The training kit is brought to you by Microsoft Developer and Platform Evangelism.

In that kit you’ll find PowerPoint decks, demos and hands-on-labs on the new SQL Server 2008 features for developers.

Download it here:
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=e9c68e1b-1e0e-4299-b498-6ab3ca72a6d7

When we define a a geography or a geometry most of think in two dimensions.

declare @LALocation geography 
set @LALocation = geography::STPointFromText('Point(-118.24 34.06 )',4326)
declare @LALocation2 Geometry
set @LALocation2 = geometry::STPointFromText('Point(-118.24 34.06 )',4326)

But Did you know that you can also store the Z and M Values may also be stored

Z  Coordinate

The Z Coordinate represents the height of or the elevation of the given point.The Heights are points above or below the surface measured using the vertical datum.

M Coordinate

The M coordinate stores the “measure” value of  a point . This coordinate can be used to represent any additional properties of a point that can expressed as a double-precision number. For instance Time or the position in a route.

So now using the code above we could simple add the z am coordinates as so

declare @LALocation geography 
set @LALocation = geography::STPointFromText('Point(-118.24 34.06  1 1)',4326)
declare @LALocation2 Geometry
set @LALocation2 = geometry::STPointFromText('Point(-118.24 34.06 1 1 )',4326)

The static methods provided by SQL server 2008  is based on WKT syntax , such as STPOINTFromText() support the creation and storage of z and m values as part of POINT. How ever these attributes are not included in the any calculations you do on the point.

Gotcha

example)

    Distance between point (0 0 0 0 ) and (3 4 12 1) is 5 (square root of the sum of the difference in the x and y dimensions) not 13 which account for z as well..

This enables you to not let any other Spatial Reference ID inserted into a spatial column. This is inherently allowed but when you want to do operations on the column they must all be of the same spatial reference.

alter table customers 
add constraint [enforce_SRID_GeogaphyColumn]
check (location.STSrid = 4326)

create TABLE customers
(
  FirstName nvarchar(30),
  LatName nvarchar(30)
)
alter table customers 
add location geography
alter table customers 
add constraint [enforce_SRID_GeogaphyColumn]
check (location.STSrid = 4326)
drop table customers

You just found out that sql server 2008 has spatial support and you want to add a spatail refrence to that the table? Here is how

create TABLE customers
(
  FirstName nvarchar(30),
  LatName nvarchar(30)
)
alter table customers 
add location geography

Overview

The most important feature of the geography data type is  that stores Geodetic Spatial Data.

this is geodetic model.

This model takes account of the curved shape of the earth. When you perform operations on spatial data using the geography data type, SQL server uses angular computations to work out the result.These computations are calculated based on the ellipsoid model of the earth defined by the spatial reference system of the data in question. For example, if you were to to define a line that connects two points on earths surface in the geography data type the line would curve to follow the surface of the earth

Every line drawn in the geography data types are actually great elliptic arc.

Coordinate System

The geography data type is based on a three dimensional, round model so you must use latitude and longitude for coordinates.

Units of Measure

Since we are using Latitude and Longitude to define our points the unit of measure is in degrees. Being degrees is helpful for locating a objects, but not very helpful telling us what the actual distance is between the them. For instance lets say we wanted to know the distance between Los Angeles and Dallas. It would not be very helpful to know that it is 13 degrees away. It would be much more valuable to know that it is 1439 miles away.

To account for this the clever folks at Microsoft tell us to put a spatial reference or SRID in of 4326

So lets take a look what kind of measurement we get when we take the distance for Los Angeles To Dallas

Declare @LosAngeles as geography = geography::Point(33.56,118.24,4326)
Declare @Dallas as geography = geography::Point(32.47,96.47,4326)
Select @LosAngeles.STDistance(@Dallas)

The Result  is

2033990.01214834 metes

2 033 990.01214834 meters = 1 263.8628 miles

We notice that this is different that the first measurement given that is because the first measurement didn't account for the measurement of the earth.

Size Limitations

Due to technical Limitations SQL Server limits the size of any single object must fit inside a single hemisphere of the earth. This also include any computation on these objects. To work around this you can break down these objects into smaller polygons and then and then combine those together.

Sweet so there is the first in a series of posts on SQL Server Spatial that are coming out.

almost forgot.

Ring Orientation

If are drawing a ellipsoid everything is stored counter clock wise.

One of the new things that happens in SQL Server 2008 is that it prevents saving table structure changes that require the table to be dropped and re-created. While this is a great feature to prevent accidents from occurring, on a developer machine it can be quite frustrating. This is the dialog you get when trying to make changes in a table design.

Unfortunately, this dialog doesn’t tell you where to turn this feature off! Clicking on the small “?” on the title bar does get you to a help page that tells you how to do it.

Anyway, the place to do it is Tools > Options > Designers > Table and Database Designers > Prevent saving changes that require table re-creation. Turn this option off and you will be able to save the tables again.

I am getting ready to do bunch of presentations this weekend and I realized half my computers were running the rc of Sql 2008

So I decided to got to the site and downloaded and selected to install SQL Exrpress 2008.

Went to look for SQL Server Express Management Studio Express 2008 annnnnd nothing!

Few search engine searches later and no clear direction found this on SQL Express home page:

You can manually download and install any edition of SQL Server 2008 directly from the Microsoft Download Center:

• SQL Server 2008 Express
• SQL Server 2008 Express with Tools
• SQL Server 2008 Express with Advanced Services

Which edition of SQL Server 2008 Express is right for you?

SQL Server 2008 Express is available in the following 3 editions (each is available from the Install Wizard):

• SQL Server 2008 Express
  • SQL Server database engine - create, store, update and retrieve your data
• SQL Server 2008 Express with Tools
  • SQL Server database engine - create, store, update and retrieve your data
  • SQL Server Management Studio Basic - visual database management tool for creating, editing and managing databases
• SQL Server 2008 Express with Advanced Services
  • SQL Server database engine - create, store, update and retrieve your data
  • SQL Server Management Studio Basic - visual database management tool for creating, editing and managing databases
  • Full-text Search - powerful, high-speed engine for searching text-intensive data
  • Reporting Services - integrated report creation and design environment to create reports

So make sure you download the SQL Server 2008 Express with ToolsSql

Though I save some people some time.

One last thing:

Step 1: Download and install Microsoft .Net Framework 3.5 SP1.
Step 2 Download and install Windows Installer 4.5.
Step 3 Download and install Windows PowerShell 1.0.

I love data...It holds so many interesting secrets just waiting to be mined out and taken advantage of. If you haven't looked into space before the time is now and its never been easier.

Start to read Lynn Langit's Blog She is the BI Queen, and posts solid information that you will need like this great post on  BI.

http://blogs.msdn.com/socaldevgal/archive/2008/08/27/sql-server-2008-data-mining-in-the-cloud.aspx

Her book a great place to start as well

http://www.amazon.com/Foundations-Server-2005-Business-Intelligence/dp/1590598342

Enjoy!

Spatial it taking root.. Well more like people are starting to realize that spatial rocks and that all data takes on new meaning once you take in its spatial aspect. Representing your data is like seeing your 3D after viewing the world in the 2D for your entire life. Some may say that this is exaggerating but wait until your data tells a 3D story.

So Lets start to dig into spatial.

3D vs 2D

The world is three dimensional object yet most of things we use to view are in 2d. For instance a paper map or a computer screen. Luckily for us we have Virtual Earth and SQL Spatial which takes in both 3D(geography) and 2D(geometry) shapes and types and displays in them in ways that we can visualize them both.

A great visualization from Hanes that shoes the 2D vs 3D visualization

Coordinate System

The next thing that never keep in mind is the coordinate system. I never am always amazed at the amounts of different coordinate systems that are out there. I have worked with at least 5 different coordinate systems and there are some that are so complex and so ridiculous that i never seem to start laughing.

For these articles we are going to be using the "WGS84 coordinate system have to consider an ellipsoidal shape of the earth and they can be described as either Cartesian coordinates or through latitude and longitude. Latitudes can have values between +90 and -90 degrees where 0 is at the equator and longitudes can have values between +180 and -180 degrees where 0 has been defined as a place in the Royal Observatory in Greenwich / United Kingdom. As mentioned earlier Virtual Earth in 2D-mode can only work between +85 and -85 degrees because of the Mercator projection."

So now that we have our basic understanding of 2d vs 3d maps we have or coordinate system lets dig into the SQL spatial

I am presenting at FREE day-long event at the Microsoft Irvine,CA office all around SQL Server 2008.  There will content for both Developers and for ITPros.  If you are interested in attending, please register at www.firestarterevents.com

My presentation will be a two part one on virtual earth and the sql server 2008 geo spatail types. Its going to rock!

I  have been burned by jumping into betas and CTP's before, but I am having major problems with not being on top of the geo-spatial data types in SQL server plus I think I might be doing a doing a presentation on this soon. So i took the plunge.

First Step install the thing..   You can download it here
http://download.microsoft.com/download/3/1/5/315b8683-3765-4426-96ec-179360abb82f/Download_Instructions_ENU.htm

Well that's really more of a download page, but you'll get the idea once you get there. I am running a 32 bit version of vista so it x86 version for me.

It rather big so it took me about 20 minutes to download at my office.

The next couple of shots are my installation.

Step 1) Installation Center

The installation Center? This totally new.  I am guessing this is due to the fact that its a CTP..But in a way i like it.

Step 2) Feature Selection

Yeah I don't need the replication settings. But the rest is like being in a candy shop..Mmm BI Studio...That sounds tasty..

Step 3) Instance Configuration

MSMSQL 0 Wow just when I thought I was getting good with 9.2.3042.00... I am all about the default on this one. I know I am going to uninstall this in a matter of a month when they release the RTM.

Man that was such a great build way better then 9.2.3041.00..

4) Ok this party was a bit tricky...My thoughts on this are this. I am installing this on my laptop. On one hand i want to have it all so I can get all the goodies at the same time I don't want my laptop to take a hour to start up so I disabled all but the browser and the Engine. Why on my laptop? Well I need to presentation on this i am going to need it. I tried all the service accounts use the sa account .. Yes i know this is not the good, but at this point I am just trying to get this up. It didn’t really work anyway's. So then i just set the username to nt Authority and it seemed  to take it all. Something about this is fishy.

5) Database Engine Configuration

Pretty Standard Stuff here

6)  Analysis Services Configuration

Pretty Standard stuff i am just setting up real basic permissions here.

7) Reporting Services

I really need to get more into this.. Its about 4th on my list to do a deep dive.

8)   Errors and reporting....

Something about anonymous never really feels anonymous...Just kidding i checked it before i continued. Its a CTP they need all the help the can get to make the product awesome.

9) Sweet Lets do it...

10)  Install Away

About 45 minutes or so later...I looked over and it was done.. You have to restart but all in all it was pretty painless...

The Only New Screen that i wasn't comfortable with was the server configuration screen. I have a feeling that is going to come back to bite me.

What's New...more to come but i wanted to see what was up with the BI Set

Business Intelligence

Clicking on the Business Intelligence Dev Brought up Visual Studio....

But Inside my favorite IDE was this little edition

T

This of course Makes sense.

Object Explorer

I am already loving the built in Intellisence its awesome. Minus the case sensitive part. i am going to have to turn that off.

You can see all the new stuff in here...Loving the resource Governor already..Now if only i could govern my resources more tightly..I can see it now..Sorry baby your limited to 20% of the clock cycles..I would give more but my main job is keeping me busy right now..lol

Well that's it for now..