Monday, December 29, 2008

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

Thursday, December 04, 2008

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.

Thursday, October 09, 2008

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]

Wednesday, September 10, 2008

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

Friday, May 23, 2008

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.