First Article at SQLPASS.org

I wrote an article last week for PASS's web-site, and it's currently their "feature article":

time and datetime2 - Exploring SQL Server 2008's New Date/Time Data Types

I may end up doing some volunteering for them regarding technical articles. Right now, I think everyone is on Christmas vacation.

Also, the 22nd installment of my Database Journal column was published Friday:

Performance Testing – SQL Server 2008 versus SQL Server 2005

Ten Minutes with SQL 2008's Geography Methods

Problem: Figure the distance between two points.

How far is WebMD's office from my home (as the crow flies)? How far will I travel to get to Spokane for New Year's Eve? We just booked a trip to Disney World. How far is that?

Let's use SQL 2008's new Geography methods.

Initially, just using variables, here's how it's done.

DECLARE @MyHome  geography
DECLARE @WebMD   geography
DECLARE @Spokane geography
DECLARE @Disney  geography

DECLARE @MilesPerMeter decimal(19,12)
DECLARE @SRID smallint

SET @MilesPerMeter = 0.000621371192
SET @SRID = 4326

-- Exercise the Point, STDistance and
--   STAsText methods on variables
-- Address-to-Lat/Lon Converter:
--   http://stevemorse.org/jcal/latlon.php
SET @MyHome  =
 Geography::Point(45.55823900, -122.84051000, @SRID)
SET @WebMD   =
 Geography::Point(45.53677100, -122.70774100, @SRID)
SET @Spokane =
 Geography::Point(47.65354400, -117.41126300, @SRID)
SET @Disney  =
 Geography::Point(28.40982500,  -81.58848800, @SRID)

Notice how we create variables and then call methods on those variables. The STAsText method returns this:

SELECT @myHome.STAsText() AS Point

POINT
----------------------------
POINT (-122.84051 45.558239)

Next, let's get the distances:

SELECT
  'My home to Spokane' AS 'From/To',
  (@myHome.STDistance(@Spokane)
    * @MilesPerMeter) AS 'Distance in Miles'
UNION ALL
SELECT
  'My home to WebMD''s office' AS 'From/To',
  (@myHome.STDistance(@WebMD)
    * @MilesPerMeter) AS 'Distance in Miles'
UNION ALL
SELECT
  'My home to Disney World' AS 'From/To',
  (@myHome.STDistance(@Disney)
    * @MilesPerMeter) AS 'Distance in Miles'

Results:

From/To                   Distance in Miles
------------------------- -----------------
My home to Spokane        296.125371694122
My home to WebMD's office 6.61091520932017
My home to Disney World   2533.12214098223

Now let's put this into a table.

CREATE TABLE dbo.Points (
  PointName varchar(32)   NOT NULL,
  Lat       decimal(19,8) NOT NULL,
  Lon       decimal(19,8) NOT NULL,
  Geog      Geography     NULL
)

INSERT INTO dbo.Points (PointName, Lat, Lon) VALUES
  ('Rob''s Home',    45.55823900, -122.84051000),
  ('WebMD Portland', 45.53677100, -122.70774100),
  ('Spokane, WA',    47.65354400, -122.70774100),
  ('Disney World',   28.40982500,  -81.58848800)

-- Populate the Geog column based on
--   the Lat and Lon columns
UPDATE dbo.Points
SET Geog = Geography::Point(Lat, Lon, @SRID)

If you SELECT the Geog column, you'll see something like this:

Geog
----------------------------------------------
0xE6100000010C062D246074C74640807D74EACAB55EC0

Here are the points translated from the Geog column:

SELECT
  PointName,
  Geog.STAsText() AS Point
FROM dbo.Points

PointName      Point
-------------- -----------------------------
Rob's Home     POINT (-122.84051 45.558239)
WebMD Portland POINT (-122.707741 45.536771)
Spokane, WA    POINT (-122.707741 47.653544)
Disney World   POINT (-81.588488 28.409825)

Last, let's get the distance between my home and the other three points using this query:

SELECT
  TravelPoints.PointName,
  (MyHome.Geog.STDistance(TravelPoints.Geog)
    * @MilesPerMeter) AS 'Distance from My Home'
FROM dbo.Points AS MyHome
CROSS JOIN dbo.Points AS TravelPoints
WHERE MyHome.PointName = 'Rob''s Home'
  AND TravelPoints.PointName <> 'Rob''s Home'

PointName      Distance from My Home
-------------- ---------------------
WebMD Portland 6.61091520932017
Spokane, WA    144.868086608637
Disney World   2533.12214098223

There is a lot more functionality available. This just scratches the surface.

Listing Column Defaults

I had a need to list all the defaults in a database on columns that are rowguidcols where the default is not NewSequentialID(). This code is useful for joining these tables, regardless of what the WHERE clause is.

Books Online's entry for sys.default_constraints threw me off because the JOIN logic is incomplete. I've highlighted the missing part in the query below. Here is the full query that returned just what I needed:

  SELECT
      t.[name]      AS TableName,
      c.[name]      AS ColumnName,
      i.[name]      AS IndexName,
      ct.[name]     AS TypeName,
      dc.definition AS DefaultDefinition
  FROM sys.tables              AS t
  JOIN sys.indexes             AS i
    ON t.[object_id] = i.[object_id]
  JOIN sys.index_columns       AS ic
    ON i.[object_id] = ic.[object_id]
      AND i.index_id = ic.index_id
  JOIN sys.columns             AS c
    ON ic.[object_id] = c.[object_id]
      AND ic.column_id = c.column_id
  JOIN sys.types               AS ct
    ON c.system_type_id = ct.system_type_id
  JOIN sys.default_constraints AS dc
    ON dc.parent_object_id = t.[object_id]
      AND dc.parent_column_id = c.column_id
  WHERE c.is_rowguidcol = 1
    AND dc.definition NOT LIKE '%newsequentialid%'
  ORDER BY
      t.[name],
      c.[name],
      i.[name]

Parentheses are Cheap (use them in Boolean expressions in SQL)

Here is a simple example of how a lack of parentheses will cause results that were possibly not intended:

Statement 1

  IF (1 = 2 OR 1 = 1 AND 1 = 2)
    PRINT 'True - 1'
  ELSE
    PRINT 'False - 1'

Statement 2

  IF (1 = 1 OR 1 = 2 AND 1 = 2)
    PRINT 'True - 2'
  ELSE
    PRINT 'False - 2'

The only difference between the Boolean expressions is that they evaluate to

Statement 1:

  (false OR true AND false)

Statement 2:

  (true OR false AND false)

Look at the statements. What will the results be? Is it obvious what the results will be?

The first statement is false, and the second is true.

The first statement finds that (1 = 2) is false and is followed by an OR, so it keeps going. (1 = 1) is true, but it's followed by an AND, so it keeps checking. (1 = 2) is false, so the result is false.

The second statement finds that (1 = 1) is true and is followed by an OR. It stops evaluating and returns true.

The lesson? Use parentheses to make things clear.

T-SQL: COUNT(*) without FROM

I just learned that “SELECT COUNT(*)” actually works without a FROM clause (no syntax error).

In a script, I wrote a SELECT and forgot to add the FROM. I was scratching my head as to why the “COUNT(*)” kept returning 1. Apparently it will always return 1 if there is no FROM.

Test Script:

SELECT COUNT(*) AS 'Ct* - Initial'
GO
SET NOCOUNT ON
GO
SELECT COUNT(*) AS 'Ct* - Initial'

SELECT @@SERVERNAME AS '@@SERVERNAME'
SELECT COUNT(*) AS 'Ct* - @@SERVERNAME'

SELECT TOP 10 [name] FROM sys.databases
SELECT COUNT(*) AS 'Ct* - sys.databases'

Results:

Ct* - Initial
-------------
1

(1 row(s) affected)

Ct* - Initial
-------------
1

@@SERVERNAME
---------
[snip]

Ct* - @@SERVERNAME
------------------
1

name
---------
master
tempdb
model
msdb
...

Ct* - sys.databases
-------------------
1

WebMD and Column

I have been a WebMD for awhile now, and I am really enjoying it there. My boss is great; the rest of the Data Architecture team is great. I really enjoy my role there: leadership without management. Perfect.

The 16th entry in my column was published today. Linda, the series editor, is great to work with.