Persistence of Vision

Lorem Ipsum (Test String) Generator

2011-08-24T08:28:00.000-07:00

I am working on building test data for a large client and wanted to have strings that were not just 'x' 100 times or something like that.

I used the "Lorem Ipsum Generator" here. It allowed me to build non-repeating strings. Here are a few examples with the choices available:

Generate 25 Words
[Checked] Begin text with "Lorem ipsum dolor sit amet"
All other defaults

Lorem ipsum dolor sit amet phasellus velit placerat nam. Leo cubilia libero. Aliquam ut gravida congue non et. Lectus volutpat justo. Penatibus lectus urna wisi.

Generate 50 Words
[Unchecked] Begin text with "Lorem ipsum dolor sit amet"
All other defaults

Pharetra est scelerisque. Dolor et augue integer et non volutpat maecenas consequat id malesuada turpis. Vestibulum habitasse maecenas quis ultricies vestibulum arcu augue orci. Duis sed non vel mi lacus. Et commodo ac morbi semper commodo. Consequat pretium in. Nulla suscipit tincidunt. Diam id ut. A iaculis enim suspendisse cras.

It's a simple tool, but it creates nice strings.

What Goes Around Comes Around

2011-07-13T16:00:00.000-07:00

My time at WebMD Health Services will come to a close at the end of this week. I am returning to Fiserv as the Data Architect for the development team in Hillsboro.

I am very excited about the new opportunity and look forward to the new challenges.

Second Teradata Article Published

2011-02-16T13:34:00.000-08:00

My second Teradata article has been published: Exploring Teradata 13's PERIOD Functions.

It is the follow-up to Exploring Teradata 13's PERIOD Data Type.

The article is quite long (12 printed pages) and took a long time to write, but I'm pleased with the outcome.

The "Other" Persistence of Vision

2011-02-08T11:56:00.000-08:00

Today marks the opening of my blog at Teradata Developer Exchange. I've named it Persistence of Vision just like this one, but now I will keep this one focused on SQL Server and put anything specific to Teradata in the other blog.

My first post is Detecting a Broken Prior Value/New Value Chain, which I plan to post here also but with SQL Server code.

Transactions in Teradata

2011-01-07T10:18:00.000-08:00

Here is a quick test to show how transactions work in Teradata.

CREATE TABLE t1 (
    Col1    INT

);

INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (2);

INSERT INTO t1 VALUES (3);

SELECT Col1 AS AfterInsert FROM t1;


BEGIN TRANSACTION;


UPDATE t1 SET Col1 = Col1 * 10;


SELECT Col1 AS AfterUpdate FROM t1;


ROLLBACK;

--ABORT;


SELECT Col1 AS AfterRollback FROM t1;

Running this query in Teradata SQL Assistant creates two results panes and highlights the ROLLBACK. The message at the bottom states, "ROLLBACK Failed. 3514: User-generated transaction ABORT."

Running this same query in Nexus creates three results panes.

AfterInsert
-----------
1
2
3

Nexus adds a tab here with no results. It appears to correspond to the ROLLBACK.

AfterUpdate
-----------
20
30
10

Neither returns results for the "AfterRollback" query.

Running that query after running the whole script returns the expected (rolled back) results.

AfterInsert
-----------
1
2
3

There is clearly more to understand here. I will update this post as I learn more.

Update 1 (2011-01-07)

Our system uses Teradata transaction semantics. Look here for a quick explanation of transaction semantics.

To see which mode you are running in a session, use HELP SESSION and look for the column "Transaction Semantics".

(Many thanks to my friend and colleague Joshua Skarphol for the details.)

Teradata Recursive View

2011-01-06T16:52:00.000-08:00

Here is a complete example of building a recursive view in Teradata.

The table stores records that show the current [slightly simplified] hierarchy of two top-level automobile manufacturers.

Table Create

CREATE MULTISET TABLE AutoMarque (
MarqueName VARCHAR(200) NULL,
ParentMarqueName VARCHAR(200) NULL
) UNIQUE PRIMARY INDEX AutoMarque_UPI (MarqueName);

Inserts

INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('Volkswagen AG',                  'Volkswagen AG');

INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('AUDI AG',                        'Volkswagen AG');

INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('Bentley Motors Limited',         'Volkswagen AG');

INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('Bugatti Automobiles S.A.S.', 'Volkswagen AG');

INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('SEAT, S.A.',                     'Volkswagen AG');

INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('Skoda automobilova a.s.',        'Volkswagen AG');

INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('Volkswagen Passenger Cars',      'Volkswagen AG');

INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('NSU Motorenwerke AG',            'AUDI AG');

INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('Automobili Lamborghini S.p.A.', 'AUDI AG');

INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('Tata Motors',                    'Tata Motors');

INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('Jaguar',                         'Tata Motors');

INSERT INTO AutoMarque (MarqueName, ParentMarqueName) VALUES ('Land Rover',                     'Tata Motors');

Recursive View Definition

REPLACE RECURSIVE VIEW AutoMarqueRecurse (
    MarqueName,
    ParentMarqueName,
    TopLevelMarqueName,
    RecurseLevel

) AS LOCKING ROW FOR ACCESS

SELECT
    MarqueName,
    ParentMarqueName,
    MarqueName,  -- TopLevelMarqueName
    1

FROM AutoMarque
WHERE ParentMarqueName = MarqueName


UNION ALL


SELECT
    m.MarqueName,
    m.ParentMarqueName,
    r.TopLevelMarqueName,
    r.RecurseLevel + 1

FROM AutoMarqueRecurse AS r

JOIN AutoMarque        AS m
  ON m.ParentMarqueName = r.MarqueName

WHERE m.ParentMarqueName <> m.MarqueName;

SELECT All Rows From Base Table

MarqueName ParentMarqueName
----------------------------- ----------------

Automobili Lamborghini S.p.A. AUDI AG
NSU Motorenwerke AG           AUDI AG
Jaguar                        Tata Motors
Land Rover                    Tata Motors
Tata Motors                   Tata Motors
AUDI AG                       Volkswagen AG
Bentley Motors Limited        Volkswagen AG
Bugatti Automobiles S.A.S.    Volkswagen AG
SEAT, S.A.                    Volkswagen AG
Skoda automobilova a.s.       Volkswagen AG
Volkswagen AG                 Volkswagen AG
Volkswagen Passenger Cars     Volkswagen AG

SELECT All Rows From Recursive View

MarqueName ParentMarqueName TopLevelMarqueName RecurseLevel
----------------------------- ---------------- ------------------ --------------

Jaguar                        Tata Motors      Tata Motors        2
Land Rover                    Tata Motors      Tata Motors        2
Tata Motors                   Tata Motors      Tata Motors        1
Automobili Lamborghini S.p.A. AUDI AG          Volkswagen AG      3
NSU Motorenwerke AG           AUDI AG          Volkswagen AG      3
AUDI AG                       Volkswagen AG    Volkswagen AG      2
Bentley Motors Limited        Volkswagen AG    Volkswagen AG      2
Bugatti Automobiles S.A.S.    Volkswagen AG    Volkswagen AG      2
SEAT, S.A.                    Volkswagen AG    Volkswagen AG      2
Skoda automobilova a.s.       Volkswagen AG    Volkswagen AG      2
Volkswagen AG                 Volkswagen AG    Volkswagen AG      1
Volkswagen Passenger Cars     Volkswagen AG    Volkswagen AG      2

SUM with NULL Values in SQL Server

2010-12-29T15:45:00.000-08:00

Follow on the SUM with NULL Values in Teradata post, I wanted to check the same thing in SQL Server.

When you SUM a value that is nullable, does it work correctly if there are null values? Here's the test and the result. The answer is yes, it works fine here too; it just requires some change in syntax.

Note that if you set up your flag column as a bit column, you have to add a CONVERT in the SUM since you can't add bit columns.

It is also interesting to note that SQL Server throws the following warning after the results of the first query: "Warning: Null value is eliminated by an aggregate or other SET operation."

DECLARE @rgWork1 TABLE (
    client      INT         NOT NULL,
    flag        bit

);


INSERT INTO @rgWork1 (client, flag) VALUES (1, 1);

INSERT INTO @rgWork1 (client, flag) VALUES (2, 0);
INSERT INTO @rgWork1 (client, flag) VALUES (3, NULL);


SELECT SUM(CONVERT(INT, flag)) AS FlagSum

FROM @rgWork1;


SELECT SUM(CONVERT(INT, flag)) AS FlagSum

FROM @rgWork1WHERE flag = 1;


SELECT SUM(CONVERT(INT, flag)) AS FlagSum

FROM @rgWork1WHERE flag IS NOT NULL;


SELECT SUM(CONVERT(INT, flag)) AS FlagSum

FROM @rgWork1WHERE (flag = 1 OR flag IS NOT NULL);

All of these queries return the correct answer of 1.

SUM with NULL Values in Teradata

2010-12-29T15:31:00.000-08:00

In Teradata, when you SUM a value that is nullable, does it work correctly if there are null values? Here's the test and the result. The answer is yes, it works fine.

CREATE VOLATILE TABLE rgWork1 (
    client      INT         NOT NULL,
    flag        byteint

) UNIQUE PRIMARY INDEX (client)
ON COMMIT PRESERVE ROWS;


INSERT INTO rgWork1 (client, flag) VALUES (1, 1);

INSERT INTO rgWork1 (client, flag) VALUES (2, 0);
INSERT INTO rgWork1 (client, flag) VALUES (3, NULL);


SELECT SUM(flag)

FROM rgWork1;

SELECT SUM(flag)
FROM rgWork1

WHERE flag = 1;

SELECT SUM(flag)
FROM rgWork1

WHERE flag IS NOT NULL;

SELECT SUM(flag)
FROM rgWork1

WHERE (flag = 1 OR flag IS NOT NULL);

All of these queries return the correct answer of 1.

Volatile (Temp) Tables in Teradata

2010-12-29T15:16:00.000-08:00

I keep having to look this up, so I'm going to record it here. This is the syntax for creating temp tables (volatile tables) in Teradata.

CREATE VOLATILE TABLE rgWork1 (
    client      INT         NOT NULL,
    flag        byteint
) UNIQUE PRIMARY INDEX (client)
ON COMMIT PRESERVE ROWS;

DROP TABLE rgWork1;

The table is linked to the session, so you don't have to drop it if you're going to log out.

Using UNIQUE PRIMARY INDEX (...) or PRIMARY INDEX (...) is optional. It works fine without it, but leaving it off can affect performance.

However, if you leave off ON COMMIT PRESERVE ROWS, you can INSERT rows, but they won't be there when you SELECT from the table.

Teradata Date Dimension

2010-12-14T14:43:00.000-08:00

In our project, we needed a date dimension. I did a quick check on-line, but most of the examples were not specific to Teradata.

Teradata has views and an underlying table that made this relatively easy:

sys_calendar.CALENDAR: This is just a simple view on top of the CALENDARTMP view.
sys_calendar.CALENDARTMP: This is a set of calculations on top of the CALBASICS view.
sys_calendar.CALBASICS: This is a set of calculations on top of the CALDATES table.
sys_calendar.CALDATES: This is a single-column table (cdate) with 73,414 rows and dates from 01/01/1900 to 12/31/2100.

Here is the actual code I used. I'm sure there are more elegant ways to pull this data, but this was straightforward and maintainable.

(Code updated 2010-12-16. All columns are now NOT NULL, and calculation for week_end_dt has been updated to deal with last six days of internal calendar.)

DROP TABLE leap;
CREATE VOLATILE TABLE leap (
    leap_year_nbr   INT
) ON COMMIT PRESERVE ROWS;

INSERT INTO leap (leap_year_nbr)
SELECT year_of_calendar
FROM sys_calendar.CALENDAR
WHERE month_of_year = 2
  AND day_of_month  = 29;

--SELECT * FROM leap;
--SELECT COUNT(*) AS LeapCt FROM leap;

-- sys_calendar.calendar
-- 73,414 entries (01/01/1900 to 12/31/2100)
-- ================================================
-- date_dim
-- ================================================
DROP TABLE date_dim;
CREATE TABLE date_dim (
    calendar_dt                     date                        NOT NULL,
    year_nbr                        smallint                    NOT NULL,
    day_of_week_nbr                 byteint                     NOT NULL,
    day_of_month_nbr                byteint                     NOT NULL,
    day_of_year_nbr                 smallint                    NOT NULL,
    week_of_month_nbr               byteint                     NOT NULL,
    week_of_year_nbr                byteint                     NOT NULL,
    month_of_quarter                byteint                     NOT NULL,
    month_of_year                   byteint                     NOT NULL,
    month_of_calendar               smallint                    NOT NULL,
    quarter_of_year                 byteint                     NOT NULL,
    day_of_week_nm                  CHAR(9)                     NOT NULL,
    day_of_week_abbr2               CHAR(2)                     NOT NULL,
    day_of_week_abbr3               CHAR(3)                     NOT NULL,
    month_nm                        CHAR(15)                    NOT NULL,
    month_abbr2                     CHAR(2)                     NOT NULL,
    month_abbr3                     CHAR(3)                     NOT NULL,
    week_end_dt                     date                        NOT NULL,
    end_of_month_flag               CHAR(1)                     NOT NULL,
    first_of_quarter_flag           CHAR(1)                     NOT NULL,
    end_of_quarter_flag             CHAR(1)                     NOT NULL,
    end_of_year_flag                CHAR(1)                     NOT NULL,
    weekday_flag                    CHAR(1)                     NOT NULL,
    weekend_flag                    CHAR(1)                     NOT NULL,
    leap_year_flag                  CHAR(1)                     NOT NULL
) UNIQUE PRIMARY INDEX (calendar_dt);

INSERT INTO date_dim (
    calendar_dt
    ,year_nbr
    ,day_of_week_nbr
    ,day_of_month_nbr
    ,day_of_year_nbr
    ,week_of_month_nbr
    ,week_of_year_nbr
    ,month_of_quarter
    ,month_of_year
    ,month_of_calendar
    ,quarter_of_year
    ,day_of_week_nm
    ,day_of_week_abbr2
    ,day_of_week_abbr3
    ,month_nm
    ,month_abbr2
    ,month_abbr3
    ,week_end_dt
    ,end_of_month_flag
    ,first_of_quarter_flag
    ,end_of_quarter_flag
    ,end_of_year_flag
    ,weekday_flag
    ,weekend_flag
    ,leap_year_flag
)
SELECT
    --TOP 1000
    calendar_date,
    year_of_calendar,
    day_of_week,
    day_of_month,
    day_of_year,
    week_of_month,
    week_of_year,
    month_of_quarter,
    month_of_year,
    month_of_calendar,
    quarter_of_year,
    CASE day_of_week            -- day_of_week_nm
        WHEN 1 THEN 'Sunday'
        WHEN 2 THEN 'Monday'
        WHEN 3 THEN 'Tuesday'
        WHEN 4 THEN 'Wednesday'
        WHEN 5 THEN 'Thursday'
        WHEN 6 THEN 'Friday'
        WHEN 7 THEN 'Saturday'
    END,
    CASE day_of_week            -- day_of_week_abbr2
        WHEN 1 THEN 'Su'
        WHEN 2 THEN 'Mo'
        WHEN 3 THEN 'Tu'
        WHEN 4 THEN 'We'
        WHEN 5 THEN 'Th'
        WHEN 6 THEN 'Fr'
        WHEN 7 THEN 'Sa'
    END,
    CASE day_of_week            -- day_of_week_abbr3
        WHEN 1 THEN 'Sun'
        WHEN 2 THEN 'Mon'
        WHEN 3 THEN 'Tue'
        WHEN 4 THEN 'Wed'
        WHEN 5 THEN 'Thu'
        WHEN 66 THEN 'Fri'
        WHEN 7 THEN 'Sat'
    END,
    CASE month_of_year          -- month_nm
        WHEN  1 THEN 'January'
        WHEN  2 THEN 'February'
        WHEN  3 THEN 'March'
        WHEN  4 THEN 'April'
        WHEN  5 THEN 'May'
        WHEN  6 THEN 'June'
        WHEN  7 THEN 'July'
        WHEN  8 THEN 'August'
        WHEN  9 THEN 'September'
        WHEN 10 THEN 'October'
        WHEN 11 THEN 'November'
        WHEN 12 THEN 'December'
    END,
    CASE month_of_year          -- month_abbr2
        WHEN  1 THEN 'Ja'
        WHEN  2 THEN 'Fe'
        WHEN  3 THEN 'Mr'
        WHEN  4 THEN 'Ap'
        WHEN  5 THEN 'My'
        WHEN  6 THEN 'Jn'
        WHEN  7 THEN 'Jl'
        WHEN  8 THEN 'Au'
        WHEN  9 THEN 'Se'
        WHEN 10 THEN 'Oc'
        WHEN 11 THEN 'No'
        WHEN 12 THEN 'De'
    END,
    CASE month_of_year          -- month_abbr3
        WHEN  1 THEN 'Jan'
        WHEN  2 THEN 'Feb'
        WHEN  3 THEN 'Mar'
        WHEN  4 THEN 'Apr'
        WHEN  5 THEN 'May'
        WHEN  6 THEN 'Jun'
        WHEN  7 THEN 'Jul'
        WHEN  8 THEN 'Aug'
        WHEN  9 THEN 'Sep'
        WHEN 10 THEN 'Oct'
        WHEN 11 THEN 'Nov'
        WHEN 12 THEN 'Dec'
    END,
    CASE    -- week_end_dt
        WHEN calendar_date BETWEEN DATE '2100-12-26' AND DATE '2100-12-31' THEN DATE '2101-01-01'   -- For these dates, the calculations run off the end of the internal calendar
        WHEN day_of_week = 1 THEN (SELECT calendar_date FROM sys_calendar.CALENDAR AS t WHERE t.day_of_calendar = c.day_of_calendar + 6)
        WHEN day_of_week = 2 THEN (SELECT calendar_date FROM sys_calendar.CALENDAR AS t WHERE t.day_of_calendar = c.day_of_calendar + 5)
        WHEN day_of_week = 3 THEN (SELECT calendar_date FROM sys_calendar.CALENDAR AS t WHERE t.day_of_calendar = c.day_of_calendar + 4)
        WHEN day_of_week = 4 THEN (SELECT calendar_date FROM sys_calendar.CALENDAR AS t WHERE t.day_of_calendar = c.day_of_calendar + 3)
        WHEN day_of_week = 5 THEN (SELECT calendar_date FROM sys_calendar.CALENDAR AS t WHERE t.day_of_calendar = c.day_of_calendar + 2)
        WHEN day_of_week = 6 THEN (SELECT calendar_date FROM sys_calendar.CALENDAR AS t WHERE t.day_of_calendar = c.day_of_calendar + 1)
        WHEN day_of_week = 7 THEN calendar_date
        ELSE DATE '9999-12-31'
    END,
    CASE    -- end_of_month_flag
        WHEN month_of_year =  1 AND day_of_month = 31 THEN 'Y'
        WHEN month_of_year =  2 AND (l.leap_year_nbr IS NOT NULL AND day_of_month = 29) THEN 'Y'
        WHEN month_of_year =  2 AND (l.leap_year_nbr IS     NULL AND day_of_month = 28) THEN 'Y'
        WHEN month_of_year =  3 AND day_of_month = 31 THEN 'Y'
        WHEN month_of_year =  4 AND day_of_month = 30 THEN 'Y'
        WHEN month_of_year =  5 AND day_of_month = 31 THEN 'Y'
        WHEN month_of_year =  6 AND day_of_month = 30 THEN 'Y'
        WHEN month_of_year =  7 AND day_of_month = 31 THEN 'Y'
        WHEN month_of_year =  8 AND day_of_month = 31 THEN 'Y'
        WHEN month_of_year =  9 AND day_of_month = 30 THEN 'Y'
        WHEN month_of_year = 10 AND day_of_month = 31 THEN 'Y'
        WHEN month_of_year = 11 AND day_of_month = 30 THEN 'Y'
        WHEN month_of_year = 12 AND day_of_month = 31 THEN 'Y'
        ELSE 'N'
    END,
    CASE    -- first_of_quarter_flag
        WHEN month_of_year =  1 AND day_of_month = 1 THEN 'Y'
        WHEN month_of_year =  4 AND day_of_month = 1 THEN 'Y'
        WHEN month_of_year =  7 AND day_of_month = 1 THEN 'Y'
        WHEN month_of_year = 10 AND day_of_month = 1 THEN 'Y'
    ELSE 'N' END,
    CASE    -- end_of_quarter_flag
        WHEN month_of_year =  3 AND day_of_month = 31 THEN 'Y'
        WHEN month_of_year =  6 AND day_of_month = 30 THEN 'Y'
        WHEN month_of_year =  9 AND day_of_month = 30 THEN 'Y'
        WHEN month_of_year = 12 AND day_of_month = 31 THEN 'Y'
    ELSE 'N' END,
    CASE WHEN month_of_year = 12 AND day_of_month = 31 THEN 'Y' ELSE 'N' END,   -- end_of_year_flag
    CASE day_of_week WHEN 1 THEN 'N' WHEN 7 THEN 'N' ELSE 'Y' END,              -- weekday_flag
    CASE day_of_week WHEN 1 THEN 'Y' WHEN 7 THEN 'Y' ELSE 'N' END,              -- weekend_flag
    CASE    -- leap_year_flag
        WHEN l.leap_year_nbr IS NOT NULL THEN 'Y' ELSE 'N'
    END
FROM sys_calendar.CALENDAR  AS c
LEFT OUTER JOIN leap        AS l ON c.year_of_calendar = l.leap_year_nbr
;

Using Teradata's CURRENT_TIMESTAMP(n)

2010-12-07T15:15:00.000-08:00

CURRENT_TIMESTAMP can take an argument stating the precision. This allows the result to easily be passed to a column with matching precision.

CREATE MULTISET TABLE test1,
    NO FALLBACK,
    NO BEFORE JOURNAL,
    NO AFTER JOURNAL (
    id          INT           NOT NULL,
    start_dtm0  TIMESTAMP(0)  NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
    start_dtm1  TIMESTAMP(1)  NOT NULL DEFAULT CURRENT_TIMESTAMP(1),
    start_dtm2  TIMESTAMP(2)  NOT NULL DEFAULT CURRENT_TIMESTAMP(2),
    start_dtm3  TIMESTAMP(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    start_dtm4  TIMESTAMP(4)  NOT NULL DEFAULT CURRENT_TIMESTAMP(4),
    start_dtm5  TIMESTAMP(5)  NOT NULL DEFAULT CURRENT_TIMESTAMP(5),
    start_dtm6  TIMESTAMP(6)  NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
);

Results:

Column	Value
start_dtm0	12/07/2010 3:10:30
start_dtm1	12/07/2010 3:10:30.8
start_dtm2	12/07/2010 3:10:30.82
start_dtm3	12/07/2010 3:10:30.820
start_dtm4	12/07/2010 3:10:30.8200
start_dtm5	12/07/2010 3:10:30.82000
start_dtm6	12/07/2010 3:10:30.820000

Does Teradata Support BIT Columns? Yes and No.

2010-12-07T09:40:00.000-08:00

In Teradata 13.0, I can create a column with a type of bit.

CREATE MULTISET TABLE test1,
    NO FALLBACK,
    NO BEFORE JOURNAL,
    NO AFTER JOURNAL

(
    id         INT  NOT NULL,
    bitColumn  bit

);

But did it really create a bit column? No.

Query

SELECT RequestText
FROM DBC.Tables

WHERE DatabaseName = 'rgarrison'
  AND TableName    = 'test1';

Result

CREATE MULTISET TABLE test1,
NO FALLBACK,
NO BEFORE JOURNAL,
NO AFTER JOURNAL
(
id INT NOT NULL,
bitColumn BYTEINT FORMAT '9'
);

Query

SELECT ColumnType, ColumnLength
FROM DBC.Columns

WHERE DatabaseName = 'rgarrison'
  AND TableName    = 'test1'
  AND ColumnName   = 'bitColumn';

Result

ColumnType	ColumnLength
I1	1

So, Teradata will allow you to specify bit as a datatype, but it will silently translate that request to byteint.

First Teradata-Related Article is Live

2010-11-16T13:27:00.000-08:00

My first article has gone live here: Exploring Teradata 13's PERIOD Data Type.

I am very excited to be writing in this new role. A follow-up article should be up within two weeks.

First Teradata-Related Article

2010-11-02T10:21:00.000-07:00

I just sent the draft of my first Teradata-related article to the publisher.

It will be published on Teradata Developer Exchange. The title of the article is "Exploring Teradata 13's PERIOD Data Type". It is the first of at least two parts. The second part, on PERIOD functions in 13, should publish fairly closely behind the first.

I am very excited to be writing in my new role. There is so much to learn about data warehousing and business intelligence. I am just scratching at the surface.

Major Changes

2010-09-16T13:52:00.000-07:00

Last week, I changed roles and teams here at WebMD. I am now working on the Business Intelligence team as a Data Architect. This is a big switch, but it is at my request. I'll be learning BI principles, Cognos, and Teradata. Lots to learn.

Microsoft Fixing a Bug I Reported

2010-06-02T08:31:00.000-07:00

In February of 2009, I reported a bug in SQL Server Management Studio: SSMS 2008 Uncomment Bug using connect.microsoft.com.

This week, I received an e-mail stating "I am happy to say that this issue has been fixed and it will be part of a future release of Sql Server."

It's nice to know that they actually fix things that are reported this way.

Index Columns

2010-01-29T14:57:00.000-08:00

I seem to always have to rediscover how this works, so I'm going to record it.

This is how to join sys.indexes, sys.index_columns, and sys.columns.

SELECT ...
FROM sys.indexes        AS i
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

Find All Functions and Procedures That Have No Parameters

2009-11-16T15:52:00.000-08:00

To find all functions that have no parameters, use this code:

SELECT o.name AS FunctionName FROM sys.objects AS o LEFT OUTER JOIN sys.all_parameters AS p ON o.[object_id] = p.[object_id] WHERE o.type IN ('AF', 'FN', 'IF', 'TF') AND p.parameter_id IS NULL ORDER BY o.name

To find all stored procedures that have no parameters, use this code:

SELECT o.name AS ProcedureName FROM sys.procedures AS o LEFT OUTER JOIN sys.all_parameters AS p ON o.[object_id] = p.[object_id] WHERE 1 = 1 AND p.parameter_id IS NULL ORDER BY o.name

Download PASS Summit Session Presentations

2009-11-13T14:29:00.000-08:00

I couldn't figure out how to get access to the PASS Summit session presentations. Here's the scoop. Thanks to Elena Sebastiano at PASS for the instructions.

To download session presentations:

Log onto the PASS Summit Itinerary Planner using the email address you used when registering for the conference
Click on Presentation Decks from the top menu
From there you can search by track, session code or speaker to choose a session
Once you have located your session(s) of interest, click on the session title
From the session info page scroll to the bottom and click on the View button
Then click on the link provided to gain access to the handout(s)

Where are my SSMS templates?

2009-10-27T10:59:00.001-07:00

I'll be moving to Windows 7 at home shortly, and I understand that the templates folder has changed. Here's what my coworker Sopheap found:

C:\Users\\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql

Function and Procedure Parameters

2009-10-23T10:23:00.000-07:00

My task was to find all table-valued functions that had no parameters. I used the DMV sys.parameters.

SELECT o.name AS FunctionName FROM sys.objects AS o LEFT OUTER JOIN sys.parameters AS p ON o.[object_id] = p.[object_id] WHERE o.[type] = 'IF' AND p.[object_id] IS NULL ORDER BY o.name

This could be easily switched to stored procedures.

SELECT s.name AS ProcedureName FROM sys.procedures AS s LEFT OUTER JOIN sys.parameters AS p ON s.[object_id] = p.[object_id] WHERE p.[object_id] IS NULL ORDER BY s.name

To find all functions that have a particular parameter, you could do this:

SELECT o.name AS FunctionName, p.parameter_id AS ParameterOrder FROM sys.objects AS o JOIN sys.parameters AS p ON o.[object_id] = p.[object_id] WHERE o.[type] = 'IF' AND p.name = '@PersonID' ORDER BY o.name

Stopping a Script with Embedded GO Statements

2009-09-17T10:21:00.000-07:00

I’m sure you’ve run into this issue before: you have a script that needs GO statements, but you want to stop running if there’s an error.

PRINT 'Before 19'
GO
RAISERROR ('Fatal error', 19, 1) WITH LOG
RETURN
GO
PRINT 'After 19'
GO

PRINT 'Before 20'
GO
RAISERROR ('Fatal error', 20, 1) WITH LOG
RETURN
GO
PRINT 'After 20'
GO

Before 19
Msg 50000, Level 19, State 1, Line 1
Fatal error
After 19 -- It kept going after RAISERROR/RETURN
Before 20
Msg 2745, Level 16, State 2, Line 1
Process ID 110 has raised user error 50000, severity 20. SQL Server is terminating this process.
Msg 50000, Level 20, State 1, Line 1
Fatal error
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

Bailing out requires both a severity of 20 or higher and the “WITH LOG” option and running with special permissions.

Notes from BOL:

“Only a member of the sysadmin fixed server role or a user with ALTER TRACE permissions can specify WITH LOG.”
“Severity levels from 19 through 25 can only be specified by members of the sysadmin fixed server role or users with ALTER TRACE permissions. For severity levels from 19 through 25, the WITH LOG option is required.”
“Severity levels from 20 through 25 are considered fatal. If a fatal severity level is encountered, the client connection is terminated after receiving the message, and the error is logged in the error and application logs.”

Source: SQL Server - stop or break execution of a SQL script at stackoverflow.com.

Find All Public Role Permissions

2009-09-01T09:30:00.001-07:00

A standard best-practice in SQL Server is that the public role should not have any permissions. Here is a script that will show you all permissions granted to the public role in all databases on an instance.

-- Find all permissions in all databases granted to the public role
SET NOCOUNT ON
GO

IF OBJECT_ID('tempdb..#DBList') IS NOT NULL
    DROP TABLE #DBList
IF OBJECT_ID('tempdb..#ObjectList') IS NOT NULL
    DROP TABLE #ObjectList

CREATE TABLE #DBList (
    EntryID         int             IDENTITY,
    DbName          sysname
)

CREATE TABLE #ObjectList (
    EntryID         int             IDENTITY    PRIMARY KEY,
    DatabaseName    nvarchar(128),
    ObjectType      nvarchar(60),
    ObjectName      sysname,
    Permission      nvarchar(128),
    GrantState      nvarchar(60)
)

DECLARE @dbCt           int
DECLARE @objCt          int
DECLARE @dbName         nvarchar(128)
DECLARE @sql            nvarchar(4000)

INSERT INTO #DBList (
    DbName
)
SELECT name
FROM sys.databases
ORDER BY name

SET @dbCt = 0
WHILE @dbCt < (SELECT COUNT(*) FROM #DBList) BEGIN
    SET @dbCt = @dbCt + 1
    SELECT @dbName = DbName
    FROM #DBList
    WHERE EntryID = @dbCt

    SET @sql =
        N'USE ' + QUOTENAME(@dbName) + ';
        INSERT INTO #ObjectList (
            DatabaseName,
            ObjectType,
            ObjectName,
            Permission,
            GrantState
        )
        SELECT
            DB_NAME(),
            o.type_desc,
            o.name,
            dp.permission_name,
            dp.state_desc
        FROM sys.database_permissions   AS dp
        JOIN sys.objects                AS o  ON dp.major_id = o.[object_id]
        WHERE 1 = 1
          AND dp.grantee_principal_id = (
            SELECT principal_id
            FROM sys.database_principals
            WHERE name = ''public''
          )
          AND dp.[state] IN (''G'', ''W'')
          AND o.is_ms_shipped = 0
          AND o.name NOT LIKE ''MSmerge%''
          AND o.name NOT LIKE ''sysmerge%''
          AND o.name NOT IN (
            ''MSsubscription_properties''
          )
        ORDER BY
            o.type_desc,
            o.name,
            dp.permission_name'

    EXEC sp_executesql
        @stmt           = @sql,
        @params         = N'@dbNameParam nvarchar(128)',
        @dbNameParam    = @dbName;
END

SELECT
    ROW_NUMBER() OVER (
        PARTITION BY DatabaseName
        ORDER BY EntryID
    ) AS [DB Row],
    DatabaseName,
    ObjectType,
    ObjectName,
    Permission,
    GrantState
FROM #ObjectList
ORDER BY
    EntryID
GO

SQL Saturday in Redmond, WA

2009-08-13T10:38:00.000-07:00

I just signed up to speak at SQL Saturday in Redmond on October 3rd. I will be presenting two sessions:

I really enjoyed the SQL Saturday here in Portland and look forward to Redmond.

[Not SQL-related] Networking Through Existing House Wires

2009-08-12T09:52:00.001-07:00

I seldom write about non-SQL topics, but this product was just too great. Last night I purchased and installed a Linksys Powerline AV Network Kit (model PLK300).

Problem: We moved to a new house about two months ago. Our wireless (N) router is at one end of the house on the main floor. I was trying to connect a computer in the basement on the other end of the house. That room receives zero cell service, and I found that the wireless signal was too weak. I purchased two different receivers, but neither worked.

Solution: After talking with someone at Best Buy about what options we had, we purchased the "Powerline Network Kit". It routes the network signals over your home's existing power wires.

Installation was dead simple. Plug one unit into the router; plug the other unit into the receiving computer. They both plug into the regular wall outlet. Bingo. It worked. The only thing I had to do was plug both into the outlet without going through a surge suppressor. That was explained in the brief installation instructions.

The receiver has four ethernet ports, but I'm only using one. The speed seems quite good.

Nice product. Great solution to my problem.

[Slight] Caveat: The little stands are a waste. The units are so light that the cables make them fall over. Pitch the stands and just lay them down flat. That works great.

Table Variables and Transactions

2009-08-03T09:47:00.000-07:00

Did you know that table variables are not affected by ROLLBACKs? After I discovered it in my testing, I searched Google and found many references to it. Apparently everyone knew about this besides me.

Here is a simple example:

-- Create two tables: one a temp table and one a table variable
IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t
CREATE TABLE #t  (Col1 int NOT NULL)
DECLARE @t TABLE (Col1 int NOT NULL)

-- Begin a transaction
BEGIN TRAN

-- INSERT two values into each table
INSERT INTO @t VALUES (1)
INSERT INTO @t VALUES (2)
INSERT INTO #t VALUES (1)
INSERT INTO #t VALUES (2)

-- ROLLBACK and check values
ROLLBACK TRAN

PRINT '#t'
SELECT * FROM #t
PRINT '@t'
SELECT * FROM @t

-- Just in case
IF @@TRANCOUNT > 0 ROLLBACK TRAN

Result:

#t
Col1
----

@t
Col1
----
   1
   2

The table variable (@t) is not affected by the ROLLBACK.

References:

"dt_*" Stored Procedures

2009-07-24T10:55:00.000-07:00

I was recently trying to find all objects in our production databases that had GRANTs to public. I found a number of "dt_*" stored procedures. In researching them, I found some outdated information.

This post states that "They are safe to delete, provided you are not using full-blooded source control and Visual Studio integration, and you do not have any diagrams that you want to keep around."

I won't comment about the VSS issues, but the diagram issues appear to be outdated. (That post is from March of 2005.)

There is similar information in this post. Jens Suessmeyer, a moderator, comments, "Yes, if you need created database diagrams you should leave the dbpoperties.[sic]" My assumption is that he meant the dtpoperties table. In another post, Steve Twitchell states,

You can delete the dt_* procedures if you're sure no one is using the database designer, table designer, view designer, or query designer in Visual Studio, SQL Server Management Studio, or SQL Enterprise Manager. The database tools functionality will recreate them (after prompting for permission) if it thinks it needs them. If you delete the dt_* tables, you'll delete all the database diagrams stored on the server.

Time to Test

In SQL Server 2005 (Developer Edition), I first created a new database and checked for stored procedures. No diagram-related SPs existed. I right-clicked on the "Database Diagrams" folder and got a message about the database not having support objects. I clicked Yes to create them.

I then created a diagram and re-checked for new objects. I found a dbo.sysdiagrams system table and seven dbo.sp_*diagram* system stored procedures.

These objects appear to be the SQL Server 2005 versions of the old dt_ objects.

Conclusion

This does not answer all possible questions regarding dt_ stored procedures or their SQL Server 2005 equivalents, but hopefully it is useful.

As always, test in your own environment. For us, we're going to remove all the dt_* procedures.

What Objects Have GRANTs to public?

2009-07-24T10:43:00.000-07:00

This query returns any objects that have GRANTs to public.

In case you see them, my next post will be about the "dt_*" stored procedures.

SELECT
 o.[type_desc]       AS ObjType,
 o.name,
 dp.permission_name  AS Perm
FROM sys.database_permissions   AS dp
JOIN sys.objects                AS o
ON dp.major_id = o.[object_id]
WHERE dp.grantee_principal_id = (
 SELECT principal_id
 FROM sys.database_principals
 WHERE name = 'public'
)
AND dp.[state] = 'G'
AND o.name NOT LIKE 'MSmerge%'
AND o.name NOT LIKE 'sys%'
ORDER BY
 o.[type_desc],
 o.name,
 dp.permission_name

Example Output:

ObjType     name   Perm
----------- ------ ----------
USER_TABLE  t1     DELETE
USER_TABLE  t1     INSERT
USER_TABLE  t1     REFERENCES
USER_TABLE  t1     SELECT
USER_TABLE  t1     UPDATE
VIEW        vw_t1  DELETE
VIEW        vw_t1  INSERT
VIEW        vw_t1  REFERENCES
VIEW        vw_t1  SELECT
VIEW        vw_t1  UPDATE

Who Has db_owner Rights?

2009-07-02T10:45:00.000-07:00

I wanted to see who in my database has db_owner rights. Here is the script that worked:

SELECT m.name
FROM sys.database_role_members AS rm
JOIN sys.database_principals AS p
  ON rm.role_principal_id = p.principal_id
JOIN sys.database_principals AS m
  ON rm.member_principal_id = m.principal_id
WHERE p.name = N'db_owner'
  AND m.name NOT IN (N'dbo', N'SQLServiceAct')
ORDER BY m.name

name
-----------
[result1]
[result2]

Based on a script pulled from an entry here by "The Scary DBA".

Using JOIN, NOLOCK, and Table (Index) Hints Together

2009-06-29T16:00:00.000-07:00

Using JOIN, NOLOCK, or table hints by themselves is straightforward, but what if you need to use them all at once? What's the order?

I tried and failed with a number of different permutations. I finally figured out that the NOLOCK has an implied WITH*. That brought me to the solution.

SELECT ...
FROM xx AS x WITH (NOLOCK)
JOIN yy AS y WITH (NOLOCK, INDEX (yIdx))
ON x.col = y.col

* I always write NOLOCKs using the the WITH and "()", but I didn't write this query originally.

Here is an example that you can run:

IF OBJECT_ID('tempdb..#t1') IS NOT NULL
DROP TABLE #t1
IF OBJECT_ID('tempdb..#t2') IS NOT NULL
DROP TABLE #t2
GO
CREATE TABLE #t1 (ColID int IDENTITY, Col2 int)
CREATE TABLE #t2 (ColID int IDENTITY, Col2 int)
GO
CREATE INDEX t1Col2 ON #t2 (Col2)
GO
INSERT INTO #t1 (Col2) VALUES (1)
INSERT INTO #t1 (Col2) VALUES (2)
INSERT INTO #t2 (Col2) VALUES (3)
INSERT INTO #t2 (Col2) VALUES (4)
GO
SELECT t1.Col2, t2.Col2
FROM #t1 AS t1 WITH (NOLOCK)
JOIN #t2 AS t2 WITH (NOLOCK, INDEX (t1Col2))
ON t1.ColID = t2.ColID
Col2 Col2
---- ----
1 3
2 4

SSMS 2008 Uncomment Bug

2009-06-22T09:04:00.000-07:00

In SQL Server Management Studio 2008, there is a bug in the way it uncomments lines of code.

If you start with this code:

--Code line 1
--Code line 2
--Code line 3
--Code line 4
Code line 5

and want to uncomment lines 1, 2, and 3, you would select the first three lines and click [ctl]K/U.

The problem is that in SSMS 2008, if the line immediately after the line or block you are trying to uncomment is also commented, that line will be uncommented.

I know that's hard to follow, but after selecting lines 1, 2, and 3 to uncomment, you will end up with lines 1, 2, 3, and 4 uncommented.

Instead of this:

Code line 1
Code line 2
Code line 3
--Code line 4
Code line 5

You'll see this:

Code line 1
Code line 2
Code line 3
Code line 4 -- whoops
Code line 5

I entered this issue at Microsoft Connect in February. They logged it as bug ID 418596.

Today, they replied and wrote that they would fix it (someday). This is great news.

Hello rgarrison,

Thank you for reporting this problem. We understand the usability issue wrt uncommenting and will fix this issue in a future release.

Thanks,
-Karthik

Speaking at PASS Summit 2009

2009-06-15T12:34:00.000-07:00

I received word over the weekend (in the middle of moving) that one of my abstracts had been accepted for PASS.

Accepted: Working in Management Will Rot Your Brain - How to Stay Technical and Advance Your Career
Alternate: Exploring Filtered Indexes: How They Work, When They are Used

I am very excited to be able to speak there. Now I just have to do all the work to get that talk ready.

CodeCamp and SQL Saturday

2009-05-29T09:54:00.000-07:00

I will be speaking tomorrow at CodeCamp. My session is "Exploring SQL Server Column-Level Encryption".

Next Saturday I'll be speaking at SQL Saturday #12. I'll have two sessions there: "Understanding SQL Injection (DB Devs and Admins) and "Perf Testing 2008’s Transparent Data Encryption".

Should be fun. I'm still waiting to hear anything from PASS about speaking there. So far there have been zero updates to the speaker's resource page besides saying that the "call for speakers" has closed.

PASS 2009's "Call for Speakers"

2009-04-13T19:37:00.000-07:00

Well, PASS's 2009 Summit "Call for Speakers" deadline got moved out two weeks, so I had a bit more time to get everything ready. I submitted four abstracts tonight. They are posted here.

Now ... I wait.

Using REPLICATE to Build Long Strings

2009-02-03T10:28:00.000-08:00

REPLICATE is a very useful tool, but there are limitations to its "out of the box" functionality.

Problem
This code shows the problem:

SELECT LEN(REPLICATE('X',  1000))
SELECT LEN(REPLICATE('X',  5000))
SELECT LEN(REPLICATE('X', 10000))

The result is:

----
1000

----
5000

----
8000

REPLICATE will not, by default, create a string longer than 8,000 characters. Here is the test that shows that the longest string is exactly 8,000 characters:

DECLARE @ct  int
DECLARE @s1  varchar(max)

SET @ct = 0
WHILE @ct < 10000 BEGIN
  SET @ct = @ct + 1
  SET @s1 = REPLICATE('X', @ct)

  IF len(@s1) <> @ct BEGIN
    SELECT @ct AS '@ct', len(@s1) AS 'Len @s1'
    BREAK
  END
END

@ct   Len @s1
----  -------
8001     8000

Fix
How can you make it build a longer string? Use CAST.

DECLARE @ct  int
DECLARE @s1  varchar(max)

SET @ct = 0
WHILE @ct < 10000 BEGIN
  SET @ct = @ct + 1
  SET @s1 = REPLICATE(CAST('X' AS varchar(max)), @ct)

  IF LEN(@s1) <> @ct BEGIN
    SELECT @ct AS '@ct', LEN(@s1) AS 'LEN @s1'
    BREAK
  END
END
SELECT LEN(@s1) AS 'Final String Length'

Final String Length
-------------------
              10000

Skip the Splash Screen on SQL Server Management Studio Start-Up

2009-01-09T16:42:00.000-08:00

Simply add "-nosplash" to the target line in your SSMS shortcut, and it will skip displaying the splash screen.

According to a SQL Server Magazine article, "disabling the splash screen can cut the load time for SSMS in half."

Cut-and-Paste from Grid-Mode Output in SSMS

2009-01-08T09:31:00.001-08:00

If you've ever cut-and-pasted output from the grid-mode output of SSMS to Excel, you've probably had to insert a row above the results and type in the column headings.

Well, you can get SSMS to include those headings in the cut-and-paste output.

Go to Tools, Options, Query Results, SQL Server, Results to Grid. Check "Include column headers when copying or saving the results".

Without that set, copying this from SSMS

and pasting it into Excel gives you this:

With it set, you get this:

Much better.

(Thanks to Roman Rehak's tip in SQL Server Magazine.)

First Article at SQLPASS.org

2008-12-29T11:22:00.000-08:00

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

2008-12-04T15:06:00.000-08:00

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

2008-10-09T15:43:00.000-07:00

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)

2008-09-10T11:21:00.000-07:00

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

2008-09-10T10:18:00.000-07:00

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

2008-05-23T15:48:00.000-07:00

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.

New Job - WebMD

2007-11-14T08:42:00.001-08:00

It's been a long time since my last blog entry, but that doesn't mean I've been idle.

I recently left Corillian after more than eight years and accepted a position with WebMD Health Services as a Data Architect. I am really enjoying it and looking forward to contributing to WebMD's continued success.

On the writing front, the eleventh installment of the SQL Credit series on Database Journal will be published November 23rd. I am also working on an article on leadership development.

SqlCredit - Part 2

2007-02-23T10:55:00.000-08:00

The second installment in the SqlCredit series was just published this morning. This article is entitled "Creating the Database, Tables, CRUD Procedures".

My hope is that it will generate much more discussion in the forum.

Rob

TSqlTest - Getting the Word Out

2007-02-21T15:29:00.000-08:00

TSqlTest has been added to lists at opensourcetesting.org and testingReflections.com.

I am working on updating the documentation. I hope to have it out soon.

The web is awesome: Shopping for a cell phone

2007-02-21T14:58:00.000-08:00

I've been wanting to get a new cell phone for awhile now since mine started having issues. I have not wanted to go stand in line to talk to somebody at the Cingular store. That process is far too time-consuming.

So today I logged onto Cingular's on-line site and clicked on "upgrade phone". That took me to a long list of phones that were available to me at significant discounts. Since I just ordered a new car with Bluetooth, I had it show me only phones that had Bluetooth and then sorted by price.

What I could get from my desk and not in the store was reviews on every phone I was interested in. cnet's site was great. I looked up probably twelve different phones. They had all but one. I ended up purchasing a Samsung D807 for just $50. Check out this link for an example of their excellent reviews.

I was not completely sure of the pricing, so I clicked on the chat link at Cingular's site and was on-line with a real person in just a few seconds. He gave me the info I needed quickly, and I made the order.

Lots of choices, expert reviews, filtering and sorting, all that from the comfort of my desk chair. Slick.

CheckFree to Acquire Corillian

2007-02-14T16:14:00.000-08:00

CheckFree announced today that they will be acquiring Corillian.

I haven't been through an acquisition before, but I think this will go well. It's clear that they are purchasing Corillian for their technology and their technologists. It will be interesting to look back on this day six or twelve months from now.

SQL Prompt 3

2007-01-31T08:32:00.000-08:00

I pre-ordered a copy of SQL Prompt 3 and just loaded it yesterday. So far, I really like it. It will take some getting used to, but I think it will eventually help me write code much faster. The coolest thing I see right away is the suggestion when you type "ON" in a JOIN.

If you haven't used it, or if you used the old version and rejected it as slow and buggy, give the new version a try.

First SqlCredit Article

2007-01-30T12:35:00.000-08:00

My first article for Database Journal was posted yesterday. I am very excited about this and hope that I get reader feedback. The scary part is that the feedback is important to the process, but I don't have any control over it.

Rob

Comments on Architecture

2007-01-02T16:35:00.000-08:00

"Architecture". Why does it seem so difficult to get people to understand the need for architecture in software systems?

No one would build a building by hiring eight different architects to design individual rooms and then try to put them together after-the-fact. Why do we build software systems without having architectural plans, goals, and guidelines?

I have been thinking quite a bit about systems architecture lately and will jot down some of my thoughts. I reserve the right to add to or modify this list in the future (updated 2007-01-17).

Either designate (promote from within) or hire an overall system architect (a single person) who works closely with specialists such as a data architect and a UI architect. This person needs technical skills and abilities, but that's not all. This position also requires the ability to lead and build consensus among teams.
Do build a committee around this architect, but give this one person both the responsibility and the authority to design and implement your organization's architecture.
Have this person, with the help of the architecture team, create a comprehensive enterprise architecture. It should include guidance for all parts of the system (UI, mid-tier, database, messaging, security, installation and implementation, application cohesion and coupling, etc.).
Have a five-years-out goal and require that every new bit of software written somehow gets you closer to that goal.
Learn from each project and update the overall architecture and the plans and guidelines as necessary. Technology, your customers, and your competition will all change. Your architecture should change in response.

Also, if you haven't already seen it, check out issue 8 of The Architecture Journal. There are some great articles there about overall system architecture, with a theme of "Data by Design".

New Column in Database Journal

2006-12-18T16:25:00.000-08:00

In an earlier post, I commented that I have been "looking for alternatives" in my writing. Well, I found it.

Starting January 29, 2007, I will be writing a "column" for Database Journal. Committing to write a column for them means that I will be writing a series of articles about a specific subject. My first series is called "SqlCredit – Developing a Complete SQL Server OLTP Database Project".

I am really looking forward to this.

TSqlTest - "RTM"

2006-12-01T16:11:00.000-08:00

After developing it, using it, and refining it for years, I have finally released my SQL Server testing framework into the wild. It is available now at http://www.tsqltest.org/.

Source Control is Your Friend

2006-11-28T14:02:00.000-08:00

I use source control religiously in my development. I would never think of writing code without saving it in a source control system and checking in "early and often".

So why was I hand-editing the template for my blog without first saving the old version? Misplaced confidence, that's why. Of course I made one too many changes and completely screwed it up.

Since I had no source, I had to rebuild it from another blog's template. It is now under source control.

This is a lesson I learned many years ago. I just keep learning it over and over.

Rob

Back from PASS 2006

2006-11-20T14:05:00.000-08:00

I had a great time at PASS 2006. The classes were great. The keynotes were great. I got to have lunch with two great people from Microsoft, Cihangir Biyikoglu (Cihan) and Ward Pond.

I have been working with Cihan for quite awhile now and always value his knowledge and advice. His class on Friday was very well attended and quite interesting.

I had been reading Ward's blog but had never met him. I just commented in his blog that I would like to meet him. We spent an hour and a half talking Friday at lunch about design, Microsoft, the plight of development-focused database people*, and many other topics.

Overall, it was a great week and definitely worth all the money Corillian paid for me to be there.

Rob

* The question: Why do so many organizations have database administrators to clean up the problems in their databases. Why not just hire more (and/or better) database developers so that the databases are designed and programmed better? Then there wouldn't be such a mess to clean up.

Is SqlJunkies.com Abandoned?

2006-11-09T12:27:00.000-08:00

So much for my idea of continuing to write for SqlJunkies.com. All indications are that the site has been abandoned. I sent e-mail to someone at CMP Media, but I have not received a response.

So, I am looking for alternatives. I do want to continue to write for someone.

Wish me luck.

Rob

Writing Frustration

2006-10-31T13:48:00.000-08:00

I have been frustrated trying to decide whether I should start a blog or continue writing articles for SqlJunkies.com. The reason I don't like blogs is that there is no barrier to entry. Anyone can write anything and post it on a blog. Is it worth reading? No matter; it gets posted on the vast Internet anyway.

When you want to publish to a magazine, even an on-line magazine, there is an expectation that you have done a good amount of research and testing before publishing.

So, I plan to continue to write for SqlJunkies, but I have now caved and started a blog. We'll see where it goes.

Rob