The Intransigent Data Blog

Problem

Table contains two or three columns that should be output as a single column over two or three rows.  Such as [Telephone1], [Telephone2], … columns in a table.

Use Table Values constructor in CROSS APPLY clause

Use the table values constructor, VALUES(), to create a derived table using columns from the other table in the FROM clause.  Literal values can also be inserted. It is nicely flexible.   Best of all it is compact:

SELECT TOP 100 Source
	,TelNo
FROM dbo.ContactBase
CROSS APPLY (
	VALUES (
		[Telephone1]	,'Tel1'
		)
		,(
		Telephone2	,'Tel2'
		)
		,(
		Telephone3	,'Tel3'
		)
	) DT (TelNo, Source)
WHERE TElNo IS NOT NULL
ORDER BY createdon DESC

The UNION method requires three scans of the table to return the same results:

-- Equivalent query using UNION
SELECT TOP 100 Source
	,TelNo
FROM (
	SELECT TOP 100 'Tel1' AS Source
		,Telephone1 AS TelNo
		,CreatedOn
	FROM dbo.ContactBase
	WHERE Telephone1 IS NOT NULL
	ORDER BY CreatedOn DESC

	UNION ALL

	SELECT TOP 100 'Tel2'
		,Telephone2 AS TelNo
		,CreatedOn
	FROM dbo.ContactBase
	WHERE Telephone2 IS NOT NULL
	ORDER BY CreatedOn DESC

	UNION ALL

	SELECT TOP 100 'Tel3'
		,Telephone3 AS TelNo
		,CreatedOn
	FROM dbo.ContactBase
	WHERE Telephone3 IS NOT NULL
	ORDER BY CreatedOn DESC
	) DT
ORDER BY CreatedOn DESC

Efficiency

Does UNION ALL in the FROM clause avoid multiple table scans (suggested by this  Stack Exchange article)?    The following code still caused three scans:

SELECT TOP 100 Source
	,TelNo
FROM (
	SELECT TOP 100 Telephone1, 'Tel1', CreatedOn FROM dbo.ContactBase ORDER BY CreatedOn Desc UNION ALL
	SELECT TOP 100 Telephone2, 'Tel2', CreatedOn FROM dbo.ContactBase ORDER BY CreatedOn Desc UNION ALL
	SELECT TOP 100 Telephone3, 'Tel3', CreatedOn FROM dbo.ContactBase ORDER BY CreatedOn Desc 

	) DT (TelNo, Source, CreatedOn)
WHERE TelNo IS NOT NULL
ORDER BY CreatedOn DESC

It doesn’t seem that the TOP 100 clause is making a difference.  These are the two approaches simplified to return the entire table:

SELECT Source
	,TelNo
FROM dbo.ContactBase
CROSS APPLY (
	VALUES (
		[Telephone1]	,'Tel1'
		)
		,(
		Telephone2		,'Tel2'
		)
		,(
		Telephone3		,'Tel3'
		)
	) V(TelNo, Source)

print 'methond 2'

SELECT Source
	,TelNo
FROM (
	SELECT Telephone1, 'Tel1', CreatedOn FROM dbo.ContactBase UNION ALL
	SELECT Telephone2, 'Tel2', CreatedOn FROM dbo.ContactBase UNION ALL
	SELECT Telephone3, 'Tel3', CreatedOn FROM dbo.ContactBase   

	) DT (TelNo, Source, CreatedOn)

The query plans are still very distinct (even if the timings are not so very different):

Limitations

Table Value constructor introduced version 2008.

References

The article that suggested this is-there-a-better-option-than-union-all-for-multiple-selects-from-the-same-row

Similar approach for aggregating across multiple columns: use-values-clause-to-get-the-maximum-value-from-some-columns

 

T-SQL

What is the most efficient, least intrusive, way of getting table row counts (say, for keeping periodic statistics)?    Select(*) is accurate.  Using index statistics is instant but the statistics could be out of date.

dm_db_index_physical_stats

Dynamic management view.  http://technet.microsoft.com/en-us/library/ms188917.aspx

-- record_count for all tables in current database.
SELECT object_name(object_id) AS TableName
	,record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED')
WHERE index_id IN (
		0
		,1
		)
	AND alloc_unit_type_desc = 'IN_ROW_DATA'

record_count may not match (count(*)) for heaps (see TN article), and indeed I’ve found that to be the case.    Adding a PK to the table and running sp_updatestats changed record_count, but it still didn’t match count(*).

dm_db_partition_stats

Sum the row_count of each partition.  This gave accurate results in my tests where db_index_physical_stats didn’t. However one shouldn’t rely on it because TN describes row_count as “The approximate number of rows in the partition.”

SELECT 
    t.name, 
    object_schema_name(t.object_id) as schemaName,
    SUM(stat.row_count)
FROM sys.dm_db_partition_stats stat
INNER JOIN sys.tables t 
    on stat.object_id=t.object_id
WHERE 
    (index_id =1 or index_id=0)
   AND t.type='U'
GROUP BY
    t.name,
    object_schema_name(t.object_id) 
ORDER BY t.name

When were Statistics last updated?

List indexes and the date their statistics were last updated – using STATS_DATE(objetc_id, index_id).

SELECT
    t.name AS Table_Name
    ,i.name AS Index_Name
    ,i.type_desc AS Index_Type
    ,STATS_DATE(i.object_id,i.index_id) AS Date_Updated
 FROM
    sys.indexes i JOIN
    sys.tables t ON t.object_id = i.object_id
 WHERE
    i.type > 0
 ORDER BY
    t.name ASC
    ,i.type_desc ASC
    ,i.name ASC

Full statistics details – including usage histogram:

dbcc show_statistics ([Transactions], IX_Transactions_compound)

Updating Statistics

Updating statistics can be resource intensive in itself; it can also cause queries to recompile.

• exec sp_updatestats

Updates stats for all tables in the current database if necessary.  For example, if at least 1 row has been modified since last statistics update.

• DBCC UPDATEUSAGE(0) WITH COUNT_ROWS, NO_INFOMSGS
• UPDATE STATISTICS [table_name]

Query to find rowmodctr (rows modified).  Source:  http://www.littlekendra.com/2009/04/21/how-stale-are-my-statistics/

SELECT DISTINCT tablename = object_name(i.object_id)
	,o.type_desc
	,index_name = i.[name]
	,statistics_update_date = STATS_DATE(i.object_id, i.index_id)
	,si.rowmodctr
FROM sys.indexes i(NOLOCK)
INNER JOIN sys.objects o(NOLOCK) ON i.object_id = o.object_id
INNER JOIN sys.sysindexes si(NOLOCK) ON i.object_id = si.id
	AND i.index_id = si.indid
WHERE o.type = 'U' --ignore system objects 
	AND STATS_DATE(i.object_id, i.index_id) IS NOT NULL
ORDER BY si.rowmodctr DESC

 

 

T-SQL

For ease of maintenance all object permissions should be granted to SQL Server roles and users simply made members of one role or another.    If you’re using SSDT, roles assigned to users don’t work at all well.  I find it best to create users in a post-deployment script outside of the model. This only permits role-based permissions (in any manageable sense, controlled by the SSDT model).

It’s all too easy for permissions to become directly assigned to users.  This code (executed step by step) finds permissions assigned to users, assigns the same permissions to roles, checks that the new and old permissions match, deletes the permissions from users.

Locate permissions assigned directly to users

The process begins with an audit of permissions directly assigned to users.  The results are saved to a table; they could have been simply printed or inserted into a temporary table for the period of the operation; however, it’s important to keep the results of the original audit to ensure that all existing object access is maintained.  It also allows for rollback.

First create a schema for this work and tables for the audit results.

/*
   Create new schema ddl_work for user permission audit tables.
*/
CREATE SCHEMA ddl_work AUTHORIZATION dbo
GO

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
	,@value = N'Objects used during database updates and re-factorings; for example, views, procedures and temporary tables used to manage and confirm data updates. '
	,@level0type = N'SCHEMA'
	,@level0name = N'ddl_work'

/*
   table DirectGrantedPermissions
*/
--DROP TABLE ddl_work.DirectGrantedPermissions
CREATE TABLE ddl_work.DirectGrantedPermissions (
	grantee_name VARCHAR(255)
	,permission VARCHAR(100)
	,state_desc VARCHAR(100)
	,major_object_name VARCHAR(255)
	,[minor_id] INT
	,[replacement_role_name] VARCHAR(100) NULL CONSTRAINT [PK_DirectGrantedPermissions] PRIMARY KEY CLUSTERED (
		grantee_name
		,permission
		,state_desc
		,major_object_name
		,minor_id ASC
		)
	)
GO

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
	,@value = N'Contains permissions directly granted to users. Used for transferring permissions to Roles, and then for confirming. '
	,@level0type = N'SCHEMA'
	,@level0name = N'ddl_work'
	,@level1type = N'TABLE'
	,@level1name = N'DirectGrantedPermissions'
GO

/*
    table RolePermissions
*/
-- DROP TABLE ddl_work.RolePermissions
CREATE TABLE ddl_work.RolePermissions (
	role_name VARCHAR(255)
	,permission VARCHAR(100)
	,state_desc VARCHAR(100)
	,major_object_name VARCHAR(255)
	,[minor_id] INT CONSTRAINT [PK_RolePermissions] PRIMARY KEY CLUSTERED (
		role_name
		,permission
		,state_desc
		,major_object_name
		,minor_id ASC
		)
	)
GO

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
	,@value = N'Contains of role permissions. used for checking the transfer of permissions from Users to Roles.'
	,@level0type = N'SCHEMA'
	,@level0name = N'ddl_work'
	,@level1type = N'TABLE'
	,@level1name = N'RolePermissions'

Find the Errant Permissions

This script finds all permissions granted directly to users and inserts them into the newly-created table.  It then does a few row counts and summarises the situation for you.

/*
Load existing directly-applied permissions into table.
Look up the object name. 
minor_id can contain reference to column; I am assuming that there is no column-level security; if there is alert the user.

grantee.type:  sys.database_principals.type.  S = SQL_USER, U = WINDOWS_USER, G = WINDOWS_GROUP (all types of principal that we _don't_ want to assign direct permissions to.)
*/
DECLARE @ErrorCount INT = 0

PRINT 'Clearing DirectGrantedPermissions table.'

DELETE
FROM ddl_work.DirectGrantedPermissions

PRINT 'Inserting all direct-assigned permissions into DirectGrantedPermissions.'

INSERT INTO [ddl_work].[DirectGrantedPermissions] (
	[grantee_name]
	,[permission]
	,[state_desc]
	,[major_object_name]
	,[minor_id]
	)
SELECT GranteeName = grantee.NAME
	,dp.permission_name
	,dp.state_desc
	,ObjectName = OBJECT_NAME(major_id)
	,dp.minor_id
FROM sys.database_permissions dp
INNER JOIN sys.database_principals grantee ON dp.grantee_principal_id = grantee.principal_id
INNER JOIN sys.database_principals grantor ON dp.grantor_principal_id = grantor.principal_id
WHERE grantee.type IN (
		'S'
		,'U'
		,'G'
		)
	AND dp.permission_name <> 'CONNECT'
ORDER BY grantee.NAME

PRINT 'Checking whether any permission is assigned to a minor object.'

DECLARE @Count AS INT = 0

SELECT @Count = Count(*)
FROM ddl_work.DirectGrantedPermissions
WHERE minor_id <> 0

IF (@Count > 0)
BEGIN
	SET @ErrorCount += 1

	PRINT 'One or more permission(s) have been assigned to a minor object - e.g. a column. Check'
END

DECLARE @UniqueLoginCount INT = 0
DECLARE @PermissionCount INT = 0

PRINT 'Counting number of Users.'

SELECT @UniqueLoginCount = Count(*)
FROM ddl_work.DirectGrantedPermissions
GROUP BY grantee_name

PRINT 'Counting all rows.'

SELECT @PermissionCount = Count(*)
FROM ddl_work.DirectGrantedPermissions

PRINT 'There are ' + CAST(@PermissionCount AS VARCHAR) + ' Permissions assigned to ' + CAST(@UniqueLoginCount AS VARCHAR) + ' different Users.'

Set the replacement Roles

We want to assign every user-permission to a role.  Note the column [replacement_role_name] in the DirectGrantedPermissions table.  We simply have to enter the name of the role into each row.

That might be a tall order.  If there are a heaps of user-permissions, the best we might be able to do is create a role per user and then tidy the roles later.  The main aim is to get the permissions off of the users !

Special value: set replacement_role_name to <DELETE>  to ignore a particular row; no role-based permission will be created.

One or more simple updates like the following will be needed

PRINT 'Setting replacement role MyUserRole for user MyUser.'

UPDATE ddl_work.DirectGrantedPermissions
SET replacement_role_name = 'MyUserRole'
WHERE grantee_name = 'MyUser'

Grant Permissions to Roles

The first thing this next script does is check that all rows in DirectGrantedPermissions have a replacement_role_name value.

In my database any ‘WITH GRANT OPTION’ was a mistake caused by ticking too many options in the SSMS permissions UI!  This script removes any WITH GRANT options.  Of course, WITH GRANT may be intentional.

The script then loops through all rows in DirectGrantedPermissions adding each permission to the specified replacement role (it generates and executes the GRANT DDL statements).  The roles must already exist, naturally.

PRINT 'Checking that all users have a replacement role defined.'

DECLARE @blank_replacement_role_name_count INT = 0

SELECT @blank_replacement_role_name_count = Count(*)
FROM ddl_work.DirectGrantedPermissions
WHERE replacement_role_name IS NULL
GROUP BY grantee_name

IF @blank_replacement_role_name_count > 0
BEGIN
	SET @ErrorCount += 1

	PRINT 'No replacement role has been specified for ' + CAST(@blank_replacement_role_name_count AS VARCHAR) + ' user name(s).'
END

/*
Handle the GRANT_WITH_GRANT_OPTION oddity. 
Comment this out if WITH GRANT is required

Some permissions may have been assigned as GRANT_WITH_GRANT_OPTION.
Replace these with simple GRANT.  

*/
PRINT 'Replacing GRANT_WITH_GRANT_OPTION with GRANT in DirectGrantedPermissions.'

UPDATE ddl_work.DirectGrantedPermissions
SET state_desc = 'GRANT'
WHERE state_desc = 'GRANT_WITH_GRANT_OPTION'

/*
   Add permissions to the replacement roles
*/
PRINT 'Add permissions to replacement roles'

DECLARE @sql NVARCHAR(255)

DECLARE @SetPermissionCursor CURSOR SET @SetPermissionCursor = CURSOR
FOR
SELECT state_desc + ' ' + [permission] + ' ON [' + major_object_name + '] TO ' + replacement_role_name
FROM ddl_work.DirectGrantedPermissions
WHERE replacement_role_name <> '<DELETE>'

OPEN @SetPermissionCursor

FETCH NEXT
FROM @SetPermissionCursor
INTO @sql

WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT @sql

	EXECUTE sp_executesql @sql

	FETCH NEXT
	FROM @SetPermissionCursor
	INTO @sql
END

CLOSE @SetPermissionCursor

DEALLOCATE @SetPermissionCursor

Confirm that we have functionally-equivalent replacement permissions

So now we have created all the permissions on roles.  Have any been missed out?

This next script fills the second table we created (RolePermissions) with all the permissions granted to each role.   It then checks that all the permissions we said we wanted to create in DirectGrantedPermissions actually exist on the role.

If any don’t exist the script gives you the SQL to run to locate the missing permissions.   Again I had fun with “WITH GRANT OPTION” permissions – and you can see the effect in the script.

/*
 Check for each user that all directly-assigned permissions exist on the replacement role.

 Insert all Role permissions into ddl_work.RolePermissions
*/
PRINT 'Clearing RolePermissions table.'

DELETE
FROM ddl_work.RolePermissions

INSERT INTO [ddl_work].RolePermissions (
	role_name
	,[permission]
	,[state_desc]
	,[major_object_name]
	,[minor_id]
	)
SELECT grantee.NAME
	,dp.permission_name
	,dp.state_desc
	,ObjectName = OBJECT_NAME(major_id)
	,dp.minor_id
FROM sys.database_permissions dp
INNER JOIN sys.database_principals grantee ON dp.grantee_principal_id = grantee.principal_id
INNER JOIN sys.database_principals grantor ON dp.grantor_principal_id = grantor.principal_id
WHERE grantee.type IN ('R')
	AND dp.permission_name <> 'CONNECT'
ORDER BY grantee.NAME

/*
Replace any GRANT_WITH_GRANT_OPTION with GRANT
(otherwise the comparison will fail for such permissions)
*/
PRINT 'Replacing GRANT_WITH_GRANT_OPTION with GRANT in RolePermissions.'

UPDATE ddl_work.RolePermissions
SET state_desc = 'GRANT'
WHERE state_desc = 'GRANT_WITH_GRANT_OPTION'

/*
Do the users have any direct permissions that are not in their replacement roles?
*/
DECLARE @MissingPermissionCountr INT = 0

SELECT @MissingPermissionCountr = Count(*)
FROM ddl_work.DirectGrantedPermissions AS DGP
LEFT JOIN ddl_work.RolePermissions AS RP ON DGP.permission = RP.permission
	AND DGP.state_desc = RP.state_desc
	AND DGP.major_object_name = RP.major_object_name
	AND DGP.minor_id = RP.minor_id
	AND DGP.replacement_role_name = RP.role_name
WHERE (RP.role_name IS NULL)
	AND DGP.replacement_role_name <> '<DELETE>'

IF (@MissingPermissionCountr = 0)
BEGIN
	PRINT 'SUCCESS.  All directly assigned permissions now exist on the relevant replacement roles.'
	PRINT 'Next step: remove all permissions from users.'
END
ELSE
BEGIN
	PRINT 'FAILURE. ' + CAST(@MissingPermissionCountr AS VARCHAR) + ' permission(s) exist on user but not on replacement role.
run following query to discover which.'
	PRINT '
SELECT     DGP.grantee_name, DGP.permission, DGP.state_desc, DGP.major_object_name, DGP.minor_id, DGP.replacement_role_name, RP.role_name
FROM         ddl_work.DirectGrantedPermissions AS DGP LEFT OUTER JOIN
 ddl_work.RolePermissions AS RP ON DGP.permission = RP.permission AND DGP.state_desc = RP.state_desc AND 
 DGP.major_object_name = RP.major_object_name AND DGP.minor_id = RP.minor_id AND DGP.replacement_role_name = RP.role_name
WHERE     (RP.role_name IS NULL)
AND DGP.replacement_role_name <> ''<DELETE>''
'
END

Lastly – Revoke the User Permissions

Still with me?   So now we have re-created all users’ permissions on roles (of which the users must, of course, be members – but that’s outside this script), and we’ve verified them (i.e. the result of the preceding script was SUCCESS).

At last we can do what we’ve wanted to do all along:  revoke the user permissions.

This script uses the DirectGrantedPermissions table again to create Revoke statements for every user-permission, then executes it.

Once again, WITH GRANT OPTION permissions are so fiddly to script that I have left those for manual deletion – you can run the very first script again to detect any user-permissions that have escaped this cull.

/*
If above code succeeded, REVOKE permissions assigned to users.

We also delete the permissions whose replacement role is '<DELETE>'
- basically, we want to delete ALL user-based permissions.

Any GRANT_WITH_GRANT_OPTION permissions will have to be removed manually
(the syntax is hard work to create for the couple of occasions it appears!)

*/
PRINT 'Revoke permissions from users.'

DECLARE @sql NVARCHAR(255)

DECLARE @RevokePermissionCursor CURSOR SET @RevokePermissionCursor = CURSOR
FOR
SELECT 'REVOKE ' + [permission] + ' ON [' + major_object_name + '] TO ' + grantee_name
FROM ddl_work.DirectGrantedPermissions

OPEN @RevokePermissionCursor

FETCH NEXT
FROM @RevokePermissionCursor
INTO @sql

WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT @sql

	EXECUTE sp_executesql @sql

	FETCH NEXT
	FROM @RevokePermissionCursor
	INTO @sql
END

CLOSE @RevokePermissionCursor

DEALLOCATE @RevokePermissionCursor

PRINT 'Done'

Finally

So that’s done.  You now have the pleasure of a database with no permissions directly assigned to users.   You may drop the two tables and the ddl_work schema (or leave it around for a while as evidence of the previous state in case of any permission-related problems).

A few slight changes could script the DDL statements, rather than executing them directly.  I think I couldn’t wait.

The manual labour part is deciding on the roles that will be used for each user or permission.   The degree of difficulty depends on how extensive the user-permission allocation was in the first place.

 

 

 

 

 

 

T-SQL

E. Sommarskog is the important source on this subject with a set of supremely readable articles here http://www.sommarskog.se/error-handling-I.html – covering up to Sql Server 2005.

ERROR_HandleCaughtError stored procedure

This is  ES’s error handler in a form that suits me: I usually want to add some additional information to aid debugging – e.g. the values of parameters.

• I seem to be thinking of an “error” schema with the prefix I’ve given it.  Probably dbo is the appropriate location (since little else would ideally end up there).
• I think I have solved the question of whether the LOG option of RAISERROR is always allowed; I’ll update this post.
• I’ve gone with ES’s 5555 return value.   Mostly I don’t use the return value, but I’ve yet to form a complete stance on them.  I wouldn’t trust them to indicate errors, largely because of the ambivalence with which they are treated.
• I am no longer certain why I added Execute As Caller.  It might be important if I were capturing the login running the procedure (and indeed, that might be what appears in the LOG).  On the other hand, it might expose the procedure to the vagaries of Caller’s permissions.  Again, I will confirm and update the post.

CREATE PROCEDURE [dbo].[ERROR_HandleCaughtError] 

        @DebugInfo varchar(2000) = NULL

/*
        2013-01-06

        Reports details of an error to user.
        Logs error to SQL Server Log.

        IN
                @DebugInfo varchar(200) - optional.  Additional 
                                          debug information to append to error message.

	VERSION
		2014-08-19 includes database name.

*/
WITH EXECUTE AS CALLER 
AS
DECLARE @errmsg   nvarchar(2048),
        @severity tinyint,
        @state    tinyint,
        @errno    int,
        @proc     sysname,
        @lineno   int,
		@dbname   varchar(50)

SELECT @errmsg = error_message(), @severity = error_severity(),  
       @state  = error_state(), @errno = error_number(),
       @proc   = error_procedure(), @lineno = error_line(),
	   @dbname = db_name()

IF @errmsg NOT LIKE N'***%'                                         
        BEGIN 
                -- This is the first time the error has been handled in the stack.
                SELECT @errmsg = '*** Proc. ' + @dbname + '..' + coalesce(quotename(@proc), '<dynamic SQL>') + 
                                        ', Ln. ' + ltrim(str(@lineno)) + '. Err. ' + 
                                        ltrim(str(@errno)) + ': ' + @errmsg +
                                        ISNULL(' [' + @DebugInfo + ']', '')
                RAISERROR(@errmsg, @severity, @state) WITH LOG
        END    
ELSE
        BEGIN     
          -- The error information has already been concatenated; rethrow error. No logging required.
           RAISERROR(@errmsg, @severity, @state)

        END

Implementing the Error Handler

Here is a pattern showing the error handler in use in an outline stored procedure.  Copy and Paste.

CREATE PROCEDURE schema.StoredProcedure_Action

        /*
                This is what it does.

                PARAMETERS

                RETURNS

                0/0/0000  Initials : created        
        */

AS

SET NOCOUNT ON
SET XACT_ABORT ON
DECLARE @DebugInfo varchar(2000)

BEGIN TRY

        SELECT @DebugInfo = 'int_parameter=' + ISNULL(CONVERT(varchar(8), @int_parameter), 'NULL') +
                           ' date_parameter=' + ISNULL(CONVERT(varchar(20), @date_parameter, 120), 'NULL');

        -- Raise errors for any parameters that must not be Null.
        IF (@int_parameter IS NULL) RAISERROR('@int_parameter is required', 16, 1)
        -- Procedure Actions Here --

END TRY
BEGIN CATCH
        EXECUTE dbo.ERROR_HandleCaughtError @DebugInfo
        RETURN 5555
END CATCH

 Discussion

All exceptions are re-thrown and take the error number of 50000.   Sometimes I would prefer to raise more specific errors to help the application using the procedure – e.g. differentiating between invalid parameters (which could be corrected by the application) and invalid data like a missing lookup value (which couldn’t).     Custom error numbers have to be added to sys.messages: it allows you to specify a custom formatted error message (good), but the custom entries would apply to all databases on the instance, and I don’t care to take that step just yet. A universal set of conventionalised custom errors would be the answer (which ones would you need?), but I haven’t devised one yet, and I have found little evidence of anyone else using them yet.   What would happen if you loaded a third party database that defined its own conflicting custom errors?

 Revisions

1. Aug 2014.   Knowing the database that the error came from is undeniably useful when trawling logs.  Added to this version.  I haven’t yet found a way of returning the schema of the error procedure.

SQL-Pattern, T-SQL

I have twice now been wrong-footed by this SQL Server error message:

“Microsoft SQL Server Native Client 10.0” Hresult: 0x80040E07 Description: “Conversion failed when converting the varchar value ‘abcd123’ to data type int.”.

The statement causing this distress was:

ISNULL(View1.long_name, View1.short_name) AS output_column

Reasonably, ISNULL will expect both arguments to be the same type will attempt conversion if they aren’t.   But in this example both columns long_name and short_name are the same: varchar – aren’t they?

Well, no they aren’t actually.   View1 was hiding some changes that were made to the source table:  long_name was dropped, and to keep things working View1 merely aliased NULL as long_name.     With no other clue, SQL Server interprets the literal NULL as type int, hence the attempted conversion.

T-SQL Pattern: No un-cast literals in views

It’s necessary for numeric literals, fairly common-sense for string literals, but less natural for literal NULL to be cast to the appropriate data type – though a moment’s thought makes it clear why it must.

       SELECT
           NULL as short_name,  -- wrong : what data type will it be? 
           CAST(NULL AS varchar(50)) as long_name  -- the correct pattern

The first line should stand out as unacceptably vague about the data type.

SQL-Pattern, T-SQL