A Creative Use of Computed Columns

I love to see a creative solution to a technical problem.

How can I get that user out of my table quickly by JackLi

Great creative solution using indexed persisted computed columns.

The beauty of indexed persisted computed columns is that, yes, you have to pay the cost of doing the computation, but you only pay that cost at the point where you INSERT or UPDATE that particular column. If the data is mostly static as it is in this case, the one-time cost is very small.

SELECT INTO is faster than INSERT SELECT

I had heard that SELECT INTO is faster than INSERT SELECT, so I wanted to prove it.

Here are the results of testing in both FULL and SIMPLE recover models. Each test was run twice, once with INSERT-SELECT first and once with SELECT-INTO first.

The net result is that, at least for this test code on my hardware, using SELECT INTO is an order of magnitude faster in SIMPLE recover mode than INSERT SELECT. Using SELECT INTO is faster in FULL recover mode than INSERT SELECT, but the difference is less significant.

Test Results

Recovery Model: Simple
Order: INSERT-SELECT first
Elapsed Mcs - INSERT-SELECT: 8,391,839
Elapsed Mcs - SELECT-INTO: 962,096

Order: SELECT-INTO first
Elapsed Mcs - INSERT-SELECT:7,205,720
Elapsed Mcs - SELECT-INTO: 918,092

Recovery Model: Full
Order: INSERT-SELECT first
Elapsed Mcs - INSERT-SELECT:7,095,709
Elapsed Mcs - SELECT-INTO:1,289,129

Order: SELECT-INTO first
Elapsed Mcs - INSERT-SELECT: 6,274,628
Elapsed Mcs - SELECT-INTO:1,902,190

Test Script

USE MASTER;
GO
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'SelectInto') ALTER DATABASE SelectInto SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'SelectInto') DROP DATABASE  SelectInto;
GO
CREATE DATABASE SelectInto;
GO
ALTER DATABASE SelectInto SET RECOVERY FULL;
--ALTER DATABASE SelectInto SET RECOVERY SIMPLE;
GO
USE SelectInto;
GO

-- Source table: t1
CREATE TABLE dbo.t1 (
   ColID       INT         NOT NULL,
   Col1        CHAR(30)    NOT NULL DEFAULT 'asdf',
   CONSTRAINT PK_t1 PRIMARY KEY CLUSTERED (ColID)
);

-- Populate t1
DECLARE @MaxRows    INT     = 1000000;
DECLARE @i          INT     = 0;
WHILE @i < @MaxRows
BEGIN
   SET @i += 1;
   INSERT INTO dbo.t1 (ColID) VALUES (@i);
END;

-- Destination heap: t2
CREATE TABLE dbo.t2 (
   ColID       INT         NOT NULL,
   Col1        CHAR(30)    NOT NULL DEFAULT 'asdf'
);

DECLARE @t1     time(7);
DECLARE @t2     time(7);

-- INSERT/SELECT
SET @t1 = SYSUTCDATETIME();

INSERT INTO dbo.t2 (ColID, Col1)
SELECT ColID, Col1 FROM dbo.t1;

SET @t2 = SYSUTCDATETIME();
SELECT DATEDIFF(mcs, @t1, @t2) AS ElapsedMcs;

-- SELECT INTO
SET @t1 = SYSUTCDATETIME();

SELECT ColID, Col1
INTO dbo.t3
FROM dbo.t1;

SET @t2 = SYSUTCDATETIME();
SELECT DATEDIFF(mcs, @t1, @t2) AS ElapsedMcs;


References

• INTO Clause at MSDN

SQL Server CDC: Remember It's Not Free

Someone recently recommended that a developer use SQL Server's CDC (Change Data Capture) to create a history record for a transactional table because doing that work in a stored procedure "makes this stored procedure slow". Remember that there is no magic in CDC; it's not free.

Based on the research I did for this article:

Performance Testing SQL Server 2008's Change Data Capture functionality

"The average penalty for CDC with full recovery is 10.51%."

List All Columns with MAX Length

To get a list of all columns in a database with "MAX" length, use this query:

SELECT
   SCHEMA_NAME(t.[schema_id]) + '.' + t.name + '.' + c.name    AS [Sch.Tbl.Col],
   TYPE_NAME(c.user_type_id)                                   AS [Type]FROM sys.columns    AS cJOIN sys.tables     AS t ON c.[object_id] = t.[object_id]WHERE c.max_length = -1
  AND t.name NOT IN ('sysdiagrams')ORDER BY SCHEMA_NAME(t.[schema_id]) + '.' + t.name + '.' + c.name


Transactions in Teradata

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

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

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 @rgWork1
WHERE flag = 1;

SELECT SUM(CONVERT(INT, flag)) AS FlagSum
FROM @rgWork1 
WHERE flag IS NOT NULL;

SELECT SUM(CONVERT(INT, flag)) AS FlagSum 
FROM @rgWork1
WHERE (flag = 1 OR flag IS NOT NULL);

All of these queries return the correct answer of 1.

SUM with NULL Values in Teradata

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

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

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 6 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
;