TRY_CONVERT broken by WITH SCHEMABINDING in SQL Server 2012 Data Tools build

Here's a bug you might find if attempting to schemabind an object that uses the new TRY_CONVERT function.

To reproduce, run the following code in a SSDT database solution:

CREATE VIEW [dbo].[aview] WITH SCHEMABINDING 
AS 
SELECT id = TRY_CONVERT(int, id) 
FROM dbo.arealtable

The database project should build with this error:

Error 3 SQL70561: Cannot schema bind view '[dbo].[aview]' because name 'int' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself. C:\Users\...\Documents\Visual Studio 2010\Projects\...\Views\dbo.aview.sql

This view is perfectly valid in SQL Server, and can be created without a problem. In a Data Tools database solution, it prevents the solution from being built with the above error.

In the above screenshot, "try_convert(int" is underlined in red because SSDT isn't recognizing the syntax correctly.  Again, this only appears when the view is created with schemabinding.

Note that in my bug report, the project's target platform is SQL 2012 and the project's database settings compatibility mode is SQL 2012, using Microsoft Visual Studio 2010 Version 10.0.40219.1 SP1 and SQL Server Data Tools 10.3.21101.1.

This has been marked as "resolved" on Microsoft Connect, and the fix "will appear in an upcoming release of DACFx/SSDT", as of March 2013: https://connect.microsoft.com/SQLServer/feedback/details/772804/try-convert-broken-by-with-schemabinding-in-sql-server-2012-data-tools-build#details

UPDATE: Confirmed that this is now resolved in recent releases of SSDT.

Conversation History: The Continuing Case Against GUIDs

We had a cooperative relational database design exercise at the office last week as part of our regular department "Lunch and Learn" series, and inevitably one topic that came up is the use of GUIDs as the unique key for a table.

And before long, I had been goaded onto my soapbox to deliver my hellfire-and-brimstone sermon against the use of GUIDs as the primary key, much less the clustered index, of a table. (If you haven't heard this, you need to attend more Baton Rouge SQL Server User Group meetings.)

The case for GUIDs traces back to an oil rig case study, an actual design example we (at Sparkhound) encountered here in the Gulf South. There are hundreds of oil rigs out in the middle of the Gulf of Mexico, each with a sometimes-available Internet connection, and the need to sync back home to a onshore server.  By using GUIDs, each server can write a unique key to the same table, so the argument says, without any conflicts.

(This is not my complete soap-box against GUIDs.  Another day.)

Why not use a compound key of integers?  Surely, you must still identify what oil rig is sending the data, then using a rig-side identity column would provide a compound primary key with guaranteed uniqueness and at half the cost (4 bytes x 2) of a GUID (16 bytes).  That storage space adds up, and has a big impact on nonclustered indexes, fragmentation, page splits, etc.

After that lunch and learn ended, one of our bright new hires engaged me afterwards...

YYYYMM Math

I had a project recently where I had to do math on a number that represented a date, YYYYMM.

For example, February 2013 would be stored as an integer, 201302.

As an aside, YYYYMM makes WAY more sense than ever storing anything MMYYYY.  Why?  For one, it sorts correctly.  And the biggest reason - no need to worry about leading zeroes, and therefore the data can be stored as NUMERIC, not VARCHAR data.

I needed to do month math on this data. For example, give me the previous month, six months ago, one year ago, nine months in the future, etc.  This gets tricky because though it is tempting to do -1, -6, -12, +9, the rollover of years does not work at all.

The following function solves the problem.

ALTER FUNCTION dbo.[YearMonth_Math]
(
@Yearmonth int, @months smallint
)
RETURNS INT
AS
BEGIN
RETURN
 CASE WHEN @months THEN CASE --This case handles flipping January to the previous years December.
 WHEN convert(int, Right(@Yearmonth,2)) <= abs(@months) --will we need to flip a year?
 THEN convert(int,
  convert(char(4), Left(@Yearmonth,4) + (((@Months)) / 12)  - case when right(@YearMonth, 2) + (@months % 12) < 1 Then 1 else 0 end)
  + convert(char(2), right('0' +  convert(varchar(3), (right(@yearmonth, 2) + (@months % 12) 
  + case when right(@YearMonth, 2) + (@months % 12) < 1 Then 12 else 0 end)),2))
  )
  --Otherwise, this previous month calculation is very simple.
  ELSE @Yearmonth + @months
 END
 WHEN @months >0
 THEN CASE --This case handles flipping December to the next years January.
  WHEN 12 - convert(int, Right(@Yearmonth,2)) <= @months --will we need to flip a year?
  THEN convert(int,
   convert(char(4), left(@YearMonth,4) + ((@months + right(@yearMonth,2) -1) / 12) ) +   
   convert(char(2), right('0' + convert(varchar(3), (right(@YearMonth, 2) + (@months % 12)  
   - case when right(@YearMonth, 2) + (@months % 12) > 12 THen 12 else 0 end)),2))
   )
   --Otherwise, this previous month calculation is very simple.
   ELSE @Yearmonth + @months
  END
 ELSE @YearMonth
 END
END

Here's some testing to verify all the different possibilities.

select  dbo.[YearMonth_Math] (201212,1), 201301
select  dbo.[YearMonth_Math] (201212,2), 201302
select  dbo.[YearMonth_Math] (201212,7), 201307
select  dbo.[YearMonth_Math] (201212,12), 201312
select  dbo.[YearMonth_Math] (201212,13), 201401
select  dbo.[YearMonth_Math] (201212,24), 201412
select  dbo.[YearMonth_Math] (201212,25), 201501
select  dbo.[YearMonth_Math] (201212,36), 201512
select  dbo.[YearMonth_Math] (201201,-1), 201112
select  dbo.[YearMonth_Math] (201201,-2), 201111
select  dbo.[YearMonth_Math] (201201,-7), 201106
select  dbo.[YearMonth_Math] (201201,-12), 201101
select  dbo.[YearMonth_Math] (201201,-13), 201012
select  dbo.[YearMonth_Math] (201201,-24), 201001
select  dbo.[YearMonth_Math] (201201,-25), 200912
select  dbo.[YearMonth_Math] (201212,-1), 201211
select  dbo.[YearMonth_Math] (201212,-2), 201210
select  dbo.[YearMonth_Math] (201212,-7), 201205
select  dbo.[YearMonth_Math] (201212,-12), 201112
select  dbo.[YearMonth_Math] (201212,-13), 201111
select  dbo.[YearMonth_Math] (201212,-24), 201012
select  dbo.[YearMonth_Math] (201212,-25), 201011
select  dbo.[YearMonth_Math] (201212,-36), 200912
select  dbo.[YearMonth_Math] (201206,1), 201207
select  dbo.[YearMonth_Math] (201206,-1), 201205
select  dbo.[YearMonth_Math] (201206,2), 201208
select  dbo.[YearMonth_Math] (201206,-2), 201204
select  dbo.[YearMonth_Math] (201201,1), 201202
select  dbo.[YearMonth_Math] (201201,13), 201302
select  dbo.[YearMonth_Math] (201201,25), 201402