Custom Error Messages

My understanding is that in a stored procedure (or any code for that
matter) if an error occurs you can detect it by checking @.@.error
variable and raise your own error with raiserror statement.

The problem is that the original error is not suppressed. For example
I received the following output from a stored procedure from the same
error:

Server: Msg 547, Level 16, State 1, Procedure spUpdateSecurityMaster,
Line 49
INSERT statement conflicted with COLUMN FOREIGN KEY constraint
'FK_SM_mm_Exchange_Exchanges'. The conflict occurred in database
'Trading', table 'Exchanges', column 'IsoCode'.
Server: Msg 50000, Level 14, State 1, Procedure
spUpdateSecurityMaster, Line 57
Unable to insert into "SM_mm_Exchange" table
The statement has been terminated.

So why should we bother to use raiseerror if the orginal error is
going to be given to the client anyways? The end result is two error
messages.On 1 Oct 2003 06:41:42 -0700, JayCallas@.hotmail.com (Jason) wrote:

>My understanding is that in a stored procedure (or any code for that
>matter) if an error occurs you can detect it by checking @.@.error
>variable and raise your own error with raiserror statement.
>The problem is that the original error is not suppressed. For example
>I received the following output from a stored procedure from the same
>error:
>Server: Msg 547, Level 16, State 1, Procedure spUpdateSecurityMaster,
>Line 49
>INSERT statement conflicted with COLUMN FOREIGN KEY constraint
>'FK_SM_mm_Exchange_Exchanges'. The conflict occurred in database
>'Trading', table 'Exchanges', column 'IsoCode'.
>Server: Msg 50000, Level 14, State 1, Procedure
>spUpdateSecurityMaster, Line 57
>Unable to insert into "SM_mm_Exchange" table
>The statement has been terminated.
>So why should we bother to use raiseerror if the orginal error is
>going to be given to the client anyways? The end result is two error
>messages.
The reason is that SQL Server will not allow you to trap errors of a
certain severity.

If the error was less severe then what you want to happen will work.|||Jason (JayCallas@.hotmail.com) writes:
> My understanding is that in a stored procedure (or any code for that
> matter) if an error occurs you can detect it by checking @.@.error
> variable and raise your own error with raiserror statement.
> The problem is that the original error is not suppressed. For example
> I received the following output from a stored procedure from the same
> error:
> Server: Msg 547, Level 16, State 1, Procedure spUpdateSecurityMaster,
> Line 49
> INSERT statement conflicted with COLUMN FOREIGN KEY constraint
> 'FK_SM_mm_Exchange_Exchanges'. The conflict occurred in database
> 'Trading', table 'Exchanges', column 'IsoCode'.
> Server: Msg 50000, Level 14, State 1, Procedure
> spUpdateSecurityMaster, Line 57
> Unable to insert into "SM_mm_Exchange" table
> The statement has been terminated.
> So why should we bother to use raiseerror if the orginal error is
> going to be given to the client anyways? The end result is two error
> messages.

It's actually even three. That last "The statement has been terminated"
is a separate message.

No, there is not much with a RAISERROR here. But there might be
occasions where RAISERROR is your sole choice. Say that you have a
procedure that has an parameter that controls the logic, and it
have have the values A, B and C. You procedure would look like:

IF @.action = 'A'
BEGIN
...
END
ELSE IF @.action = 'B'
BEGIN
...
END
ELSE IF @.action = 'C'
BEGIN
...
END
ELSE
BEGIN
RAISERROR ('Illegal action "%s" passed!', 16, 1, @.action)
RETURN 1
END

And, no there is no way to suppress the error message from SQL. You
need a client to do that.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9408F0145FFYazorman@.127.0.0.1>...
> It's actually even three. That last "The statement has been terminated"
> is a separate message.
> No, there is not much with a RAISERROR here. But there might be
> occasions where RAISERROR is your sole choice. Say that you have a
> procedure that has an parameter that controls the logic, and it
> have have the values A, B and C. You procedure would look like:
> IF @.action = 'A'
> BEGIN
> ...
> END
> ELSE IF @.action = 'B'
> BEGIN
> ...
> END
> ELSE IF @.action = 'C'
> BEGIN
> ...
> END
> ELSE
> BEGIN
> RAISERROR ('Illegal action "%s" passed!', 16, 1, @.action)
> RETURN 1
> END
> And, no there is no way to suppress the error message from SQL. You
> need a client to do that.

I figured that. So basically it makes no sense to use raiserror when a
database statement (delete, insert, update) is used since one will
already be thrown.|||Jason (JayCallas@.hotmail.com) writes:
> I figured that. So basically it makes no sense to use raiserror when a
> database statement (delete, insert, update) is used since one will
> already be thrown.

Yes, it would be fairly redundant. I guess there might be occassions
where it could make sense, for instance convey information about what
was going on, like "Error when adding account 98989". But as a matter
of routine, it would be pointless.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Cursors vs Table Variables vs Views

Hi people,
I'm checking about which option is the most efficient: Cursors, Table
Variables or Views.
The theory says that cursors are not so good to use, if it is possible to
avoid them, just do it. But, in my case I have better results using them than
using Table Variables or Views.
The case is I need to insert in a table some rows which are not there yet
(so I use the NOT EXISTS clause). If I make it by cursors the speed as well
as the cost are better. But the other two options are slower (using Views is
faster than Table Variables).
In conclusion, I'm confused: is it good to use cursors or not? why is this
option better than the others?
Thanks,
Without some examples of what exactly you are doing I don't know that
anyone will be able to respond constructively. It is true that
cursors are generally frowned upon for performance reasons, but there
is no automatic way the they substitute for views or table variables.
Perhaps if you post DDL, sample data and SQL commands someone will be
able to comment.
Roy Harvey
Beacon Falls, CT
On Wed, 3 Jan 2007 10:10:01 -0800, Anahi
<Anahi@.discussions.microsoft.com> wrote:

>Hi people,
>I'm checking about which option is the most efficient: Cursors, Table
>Variables or Views.
>The theory says that cursors are not so good to use, if it is possible to
>avoid them, just do it. But, in my case I have better results using them than
>using Table Variables or Views.
>The case is I need to insert in a table some rows which are not there yet
>(so I use the NOT EXISTS clause). If I make it by cursors the speed as well
>as the cost are better. But the other two options are slower (using Views is
>faster than Table Variables).
>In conclusion, I'm confused: is it good to use cursors or not? why is this
>option better than the others?
>Thanks,
|||Thanks for your reply.
I'm sending two options (it is simpler situation than mine, but it is
possible to see the behaviour):
Option1: using CURSORS
DECLARE @.ProjectID int, @.SetID INT, @.VarID INT, @.Table int, @.Mode INT,
@.PhaseID int, @.ValueOpt int, @.Element int, @.ValueEst int
SELECT @.ValueEst = v.ValueID FROM t_Options v
WHERE v.ProjectID = @.ProjectID AND v.OptionID = 7 AND v.Mode = 1
SELECT @.ValueOpt = v.ValueID FROM t_Options v
WHERE v.ProjectID = @.ProjectID AND v.OptionID = 7 AND v.Mode = 3
DECLARE set_cursor CURSOR LOCAL READ_ONLY FOR
SELECT setID, Element, Mode FROM t_Values WHERE ProjectID = @.ProjectID
OPEN set_cursor
FETCH NEXT FROM set_cursor INTO @.SetID, @.Element, @.Mode
WHILE @.@.FETCH_STATUS = 0
BEGIN
DECLARE var_cursor CURSOR LOCAL FOR
SELECT c.VariableID, 2
FROM t_Variables2 c
WHERE c.ProjectID = @.ProjectID
OPEN var_cursor
FETCH NEXT FROM var_cursor INTO @.VarID, @.Table
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO t_Init (ProjectID, SetID, Element, Point, VariableID,
StoreTable, Mode)
SELECT @.ProjectID, @.SetID, @.Element, P.P, @.VarID, @.Table, @.Mode
FROM (SELECT 1 AS P
UNION ALL
SELECT 2
UNION ALL
SELECT 0
UNION ALL SELECT
3) P
WHERE (P.P <> 3 or (p.p = 3 AND ((@.Mode = 1 AND @.ValueEst = 18) or (@.Mode
= 3 and @.ValueOpt = 18))))
AND not exists (SELECT 1
FROM t_Init
WHERE ProjectID = @.ProjectID AND Mode = @.Mode
AND SetID= @.SetID AND Element = @.Element
AND Point = p.p AND VariableID = @.VarID
AND StoreTable = @.Table)
FETCH NEXT FROM var_cursor INTO @.VarID, @.Table
END
CLOSE var_cursor
DEALLOCATE var_cursor
FETCH NEXT FROM set_cursor INTO @.SetID, @.Element, @.Mode
END
CLOSE set_cursor
DEALLOCATE set_cursor
go
----
Option2: without CURSORS
DECLARE @.ProjectID int
INSERT INTO t_Init (ProjectID, SetID, Element, Point, VariableID,
StoreTable, Mode)
SELECT d.ProjectID, d.SetID, d.Element, P.P, c.VarID, c.[Table], d.Mode
FROM t_Values d
INNER JOIN t_Options v ON v.ProjectID = d.ProjectID AND v.Mode = d.mode AND
v.OptionID = 7
INNER JOIN (SELECT 0 AS P UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) P
ON (p.p <> 3 OR (p.p = 3 AND v.ValueID = 18))
INNER JOIN (SELECT c.VariableID AS VarID, 2 as [Table], 0 as Mode
FROM t_Variables2 c
WHERE c.ProjectID = @.ProjectID) c ON (c.[Table] = 2 OR (c.[Table] <> 2
AND c.Mode = d.Mode))
WHERE d.ProjectID = @.ProjectID
AND NOT EXISTS (SELECT ProjectID FROM t_Init WHERE ProjectID = d.ProjectID
AND SetID = d.SetID AND Element = d.Element AND Point = p.p AND VariableID =
c.VarID AND StoreTable = c.[Table] AND Mode = d.Mode)
----
It is needed you know that the variables, where I'm using t_Variables2,
could be extracted from 4 different tables: t_Variables1, t_Variables2,
t_Variables4 and t_Variables5. But, in order to simplify the situation, I
only used t_Variables2.
Also, I've been testing it using Tables Variables and Views, but neither
were better than CURSORS.
Thanks very much,
Anahi
----
The structure of my database is (it is a part):
CREATE TABLE [dbo].[t_Init] (
[ProjectID] [int] NOT NULL ,
[SetID] [int] NOT NULL ,
[Element] [int] NOT NULL ,
[Point] [int] NOT NULL ,
[StoreTable] [int] NOT NULL ,
[VariableID] [int] NOT NULL ,
[Value] [float] NOT NULL ,
[Mode] [int] NOT NULL ,
[ID] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[t_Options] (
[ProjectID] [int] NOT NULL ,
[OptionID] [int] NOT NULL ,
[ValueID] [int] NOT NULL ,
[Mode] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[t_Projects] (
[ProjectID] [int] IDENTITY (1, 1) NOT NULL ,
[Project] [nvarchar] (100) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[Active] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[t_Variables2] (
[ProjectID] [int] NOT NULL ,
[VariableID] [int] NOT NULL ,
[Variable] [nvarchar] (50) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[Abbreviation] [nvarchar] (50) COLLATE Modern_Spanish_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[t_Values] (
[ProjectID] [int] NOT NULL ,
[SetID] [int] NOT NULL ,
[Element] [int] NOT NULL ,
[Size] [float] NOT NULL ,
[Mode] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[t_Init] WITH NOCHECK ADD
CONSTRAINT [PK_E_Initialization_Total_Moles] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[t_Options] WITH NOCHECK ADD
CONSTRAINT [PK_E_Solver_Options_Values] PRIMARY KEY CLUSTERED
(
[ProjectID],
[OptionID],
[Mode]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[t_Variables2] WITH NOCHECK ADD
CONSTRAINT [PK_C_Compounds] PRIMARY KEY CLUSTERED
(
[ProjectID],
[VariableID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[t_Values] WITH NOCHECK ADD
CONSTRAINT [PK_E_Discretization_Values] PRIMARY KEY CLUSTERED
(
[ProjectID],
[SetID],
[Element],
[Mode]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[t_Init] ADD
CONSTRAINT [DF_E_Initialization_Total_Moles_Value] DEFAULT (0) FOR [Value],
CONSTRAINT [DF_E_Initialization_Total_Moles_Mode] DEFAULT (1) FOR [Mode]
GO
CREATE INDEX [IX_E_Initialization_Total_Moles] ON
[dbo].[t_Init]([ProjectID], [SetID], [Element], [Point], [StoreTable],
[VariableID], [Mode]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[t_Options] ADD
CONSTRAINT [DF_E_Solver_Options_Values_ValueID] DEFAULT (0) FOR [ValueID],
CONSTRAINT [DF_E_Solver_Options_Values_Mode] DEFAULT (0) FOR [Mode]
GO
ALTER TABLE [dbo].[t_Values] ADD
CONSTRAINT [DF_E_Discretization_Values_Mode] DEFAULT (0) FOR [Mode]
GO

Cursors vs Table Variables vs Views

Hi people,
I'm checking about which option is the most efficient: Cursors, Table
Variables or Views.
The theory says that cursors are not so good to use, if it is possible to
avoid them, just do it. But, in my case I have better results using them tha
n
using Table Variables or Views.
The case is I need to insert in a table some rows which are not there yet
(so I use the NOT EXISTS clause). If I make it by cursors the speed as well
as the cost are better. But the other two options are slower (using Views is
faster than Table Variables).
In conclusion, I'm confused: is it good to use cursors or not? why is this
option better than the others?
Thanks,Without some examples of what exactly you are doing I don't know that
anyone will be able to respond constructively. It is true that
cursors are generally frowned upon for performance reasons, but there
is no automatic way the they substitute for views or table variables.
Perhaps if you post DDL, sample data and SQL commands someone will be
able to comment.
Roy Harvey
Beacon Falls, CT
On Wed, 3 Jan 2007 10:10:01 -0800, Anahi
<Anahi@.discussions.microsoft.com> wrote:

>Hi people,
>I'm checking about which option is the most efficient: Cursors, Table
>Variables or Views.
>The theory says that cursors are not so good to use, if it is possible to
>avoid them, just do it. But, in my case I have better results using them th
an
>using Table Variables or Views.
>The case is I need to insert in a table some rows which are not there yet
>(so I use the NOT EXISTS clause). If I make it by cursors the speed as well
>as the cost are better. But the other two options are slower (using Views i
s
>faster than Table Variables).
>In conclusion, I'm confused: is it good to use cursors or not? why is this
>option better than the others?
>Thanks,|||Thanks for your reply.
I'm sending two options (it is simpler situation than mine, but it is
possible to see the behaviour):
----
Option1: using CURSORS
----
DECLARE @.ProjectID int, @.SetID INT, @.VarID INT, @.Table int, @.Mode INT,
@.PhaseID int, @.ValueOpt int, @.Element int, @.ValueEst int
SELECT @.ValueEst = v.ValueID FROM t_Options v
WHERE v.ProjectID = @.ProjectID AND v.OptionID = 7 AND v.Mode = 1
SELECT @.ValueOpt = v.ValueID FROM t_Options v
WHERE v.ProjectID = @.ProjectID AND v.OptionID = 7 AND v.Mode = 3
DECLARE set_cursor CURSOR LOCAL READ_ONLY FOR
SELECT setID, Element, Mode FROM t_Values WHERE ProjectID = @.ProjectID
OPEN set_cursor
FETCH NEXT FROM set_cursor INTO @.SetID, @.Element, @.Mode
WHILE @.@.FETCH_STATUS = 0
BEGIN
DECLARE var_cursor CURSOR LOCAL FOR
SELECT c.VariableID, 2
FROM t_Variables2 c
WHERE c.ProjectID = @.ProjectID
OPEN var_cursor
FETCH NEXT FROM var_cursor INTO @.VarID, @.Table
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO t_Init (ProjectID, SetID, Element, Point, VariableID,
StoreTable, Mode)
SELECT @.ProjectID, @.SetID, @.Element, P.P, @.VarID, @.Table, @.Mode
FROM (SELECT 1 AS P
UNION ALL
SELECT 2
UNION ALL
SELECT 0
UNION ALL SELECT
3) P
WHERE (P.P <> 3 or (p.p = 3 AND ((@.Mode = 1 AND @.ValueEst = 18) or (@.Mode
= 3 and @.ValueOpt = 18))))
AND not exists (SELECT 1
FROM t_Init
WHERE ProjectID = @.ProjectID AND Mode = @.Mode
AND SetID= @.SetID AND Element = @.Element
AND Point = p.p AND VariableID = @.VarID
AND StoreTable = @.Table)
FETCH NEXT FROM var_cursor INTO @.VarID, @.Table
END
CLOSE var_cursor
DEALLOCATE var_cursor
FETCH NEXT FROM set_cursor INTO @.SetID, @.Element, @.Mode
END
CLOSE set_cursor
DEALLOCATE set_cursor
go
----
----
----
Option2: without CURSORS
----
DECLARE @.ProjectID int
INSERT INTO t_Init (ProjectID, SetID, Element, Point, VariableID,
StoreTable, Mode)
SELECT d.ProjectID, d.SetID, d.Element, P.P, c.VarID, c.[Table], d.Mode
FROM t_Values d
INNER JOIN t_Options v ON v.ProjectID = d.ProjectID AND v.Mode = d.mode AND
v.OptionID = 7
INNER JOIN (SELECT 0 AS P UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) P
ON (p.p <> 3 OR (p.p = 3 AND v.ValueID = 18))
INNER JOIN ( SELECT c.VariableID AS VarID, 2 as [Table], 0 as Mode
FROM t_Variables2 c
WHERE c.ProjectID = @.ProjectID) c ON (c.[Table] = 2 OR (c.[Table] <>
2
AND c.Mode = d.Mode))
WHERE d.ProjectID = @.ProjectID
AND NOT EXISTS (SELECT ProjectID FROM t_Init WHERE ProjectID = d.ProjectID
AND SetID = d.SetID AND Element = d.Element AND Point = p.p AND VariableID =
c.VarID AND StoreTable = c.[Table] AND Mode = d.Mode)
----
----
It is needed you know that the variables, where I'm using t_Variables2,
could be extracted from 4 different tables: t_Variables1, t_Variables2,
t_Variables4 and t_Variables5. But, in order to simplify the situation, I
only used t_Variables2.
Also, I've been testing it using Tables Variables and Views, but neither
were better than CURSORS.
Thanks very much,
Anahi
----
----
The structure of my database is (it is a part):
CREATE TABLE [dbo].[t_Init] (
[ProjectID] [int] NOT NULL ,
[SetID] [int] NOT NULL ,
[Element] [int] NOT NULL ,
[Point] [int] NOT NULL ,
[StoreTable] [int] NOT NULL ,
[VariableID] [int] NOT NULL ,
[Value] [float] NOT NULL ,
[Mode] [int] NOT NULL ,
[ID] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[t_Options] (
[ProjectID] [int] NOT NULL ,
[OptionID] [int] NOT NULL ,
[ValueID] [int] NOT NULL ,
[Mode] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[t_Projects] (
[ProjectID] [int] IDENTITY (1, 1) NOT NULL ,
[Project] [nvarchar] (100) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[Active] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[t_Variables2] (
[ProjectID] [int] NOT NULL ,
[VariableID] [int] NOT NULL ,
[Variable] [nvarchar] (50) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[Abbreviation] [nvarchar] (50) COLLATE Modern_Spanish_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[t_Values] (
[ProjectID] [int] NOT NULL ,
[SetID] [int] NOT NULL ,
[Element] [int] NOT NULL ,
[Size] [float] NOT NULL ,
[Mode] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[t_Init] WITH NOCHECK ADD
CONSTRAINT [PK_E_Initialization_Total_Moles] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[t_Options] WITH NOCHECK ADD
CONSTRAINT [PK_E_Solver_Options_Values] PRIMARY KEY CLUSTERED
(
[ProjectID],
[OptionID],
[Mode]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[t_Variables2] WITH NOCHECK ADD
CONSTRAINT [PK_C_Compounds] PRIMARY KEY CLUSTERED
(
[ProjectID],
[VariableID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[t_Values] WITH NOCHECK ADD
CONSTRAINT [PK_E_Discretization_Values] PRIMARY KEY CLUSTERED
(
[ProjectID],
[SetID],
[Element],
[Mode]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[t_Init] ADD
CONSTRAINT & #91;DF_E_Initialization_Total_Moles_Valu
e] DEFAULT (0) FOR [
Value],
CONSTRAINT & #91;DF_E_Initialization_Total_Moles_Mode
] DEFAULT (1) FOR [M
ode]
GO
CREATE INDEX [IX_E_Initialization_Total_Moles] ON
[dbo].[t_Init]([ProjectID], [SetID], [Element], [Poi
nt], [StoreTable],
[VariableID], [Mode]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[t_Options] ADD
CONSTRAINT [DF_E_Solver_Options_Values_ValueID] DEFAULT (0) FOR [Val
ueID],
CONSTRAINT [DF_E_Solver_Options_Values_Mode] DEFAULT (0) FOR [Mode]
GO
ALTER TABLE [dbo].[t_Values] ADD
CONSTRAINT [DF_E_Discretization_Values_Mode] DEFAULT (0) FOR [Mode]
GO|||On Thu, 4 Jan 2007 06:42:01 -0800, Anahi wrote:

>Thanks for your reply.
>I'm sending two options (it is simpler situation than mine, but it is
>possible to see the behaviour):
(snip)
Hi Anahi,
Based on the code you posted, I would be VERY surprised if the cursor
based solution would outperform the setbased version. However, I noticed
that there were some differences between the two, so I'm not even sure
if they both produce the same results (and I can't spend any more time
on it at the moment). You'll have to fix that before any performance
comparison starts to make sense.

>Also, I've been testing it using Tables Variables and Views, but neither
>were better than CURSORS.
If you were using table variables to mimic a cursor (i.e. insert the
data into a table variable, then process row by row), then this is not
surprising. What makes cursor performance slow is not the cursor by
itself, but the fact that you process a single row at a time on a
database that is optimized towards handling all rows at once.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis