I've spent hours trying to find a rounding error, only to discover that someone (public execution follows ...) defined a #TEMP table's column as DECIMAL (no Precision / Scale provided).
Is there NO way to tell SQL that it is completely crap to allow that (and VARCHAR with no size parameter) and would the parser PLEASE complain rather than silently assume that I really don't care?
LOL! My motto is "Explicit is better than implicit" and it applies all over the place, including this example
I am guessing that Microsoft is keeping that crap around for compatibility reasons. The very least they can do is to generate a warning or alert for these, or provided a STRICT option where the parser would generate errors. Microsoft said they will fix this issue in a "future" release, way back in 2006 here
But I guess future is not here yet!!
Never come across this before. I've always moaned about people using VARCHAR without a SIZE, but in practice the chances of a side effect on that are rare (can't remember when SQL decides that VARCHAR(1) is the sensible default, but when folk for CONVERT(varchar, @MyNumeric) the default is big enough ...
... but ...
I had never realised with DECIMAL that the default SCALE is 0 ... the consequences of that are dire (unless the value is an INT but then WTF would I be using decimal? 
)
I continue to find it staggering that STRICT is not yet available. I don't use a SQL Lint routinely (and not sure I would bother to have done so in this instance, as I would not have thought it would be a coding issue). [Just tried SQL Enlight on this - it failed to raise an alert )
The situation was:
VIEW that maps Remote Table column names to Local Table's names, and aligning some other things like COLLATION
2nd VIEW that is used specifically for Import which does a few other things (such as treating blank strings as NULL, and a few conversion-y things)
Then a local Table which receives the imported data
The Importer SQL has a Temporary table which does things like VARCHAR(8000) and Nvarchar(4000) to then be able to check that incoming data has not exceeded the local table's column width (e.g. an upgrade on the Remote System has widened a column and All Hell Breaking Loose needs avoiding ... and thus the Importer is designed to be the First Responder 
)
All of which disguised the problem because it was the #TEMP table which was wrongly defined, so I am looking at Remote Table having a correct value, all the VIEWs on it likewise were correct but then the actual imported table's data was wrong.
Further compounded because most of the figures in the remote data are not what the client bills out anyway, they separately multiple Hours by Rate and round UP, and throw away the Value that the remote is holding ... which only leaves a few Expenses items which are not rounded and the original is preserved exactly as was. With all the Rounding Up I was looking in the wrong place for why the Expenses were being rounded, - when they shouldn't have been
Mine too, we never use IMPLICIT for anything like this ... but trouble is when you accidentally do there is NOTHING [that I know of] that watches your back
I would send the coder reviewer to the gallows too ... true is that was me!
So SQL should disallow: decimal(10)? I don't think so. Of course the default decimal places is 0, couldn't really be anything else.
That said, I wouldn't have any problem with SQL preventing varchar without the length, although I don't see it as critical. Careless coding errors are not really MS's fault, after all .
Microsoft can do more to help people avoid coding errors. Most people would not expect the behavior demonstrated in the example below. While the behavior is well-documented, it does not seem intuitive, at least to me.
DECLARE
@x1 VARCHAR = REPLICATE('a',100),
@x2 VARCHAR(100) = REPLICATE('a',100);
SELECT
DATALENGTH(@x1),
DATALENGTH(@x2),
DATALENGTH(CAST(@x2 AS VARCHAR));
-- returns the following
1 100 30Agreed, it's not intuitive. But then again, no default rules for interpreting a missing length would be. By definition, the default is an arbitrary value. And, yes, it would be better if MS didn't even allow it.
I wonder if this code would be sufficient to find most / all(?) of such occurrences in db object code:
--Make sure at least one object has a "varchar" with no length specified.
--I've deliberately added spaces, a LF and tab after "varchar" but before the ")".
CREATE PROCEDURE dbo.proc1 AS SELECT CAST('abc' as varchar
)
GO
--Same spaces, LF and tab after "varchar", but this time there *IS* a length after it.
CREATE PROCEDURE dbo.proc2 as select CAST('abc' as varchar
(30) )
go
SELECT OBJECT_NAME(sm.object_id) AS object_name, varchar_with_no_length_start,
SUBSTRING(definition_stripped, varchar_with_no_length_start - 30, 100) AS string
FROM sys.sql_modules sm
CROSS APPLY (
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(definition,
SPACE(1), ''), CHAR(9), ''), CHAR(10), ''), CHAR(13), ''),
'varchar]', 'varchar') AS definition_stripped
) AS ca1
CROSS APPLY (
SELECT PATINDEX(''%varchar[^(]%'', definition_stripped) AS varchar_with_no_length_start
) AS ca2
WHERE varchar_with_no_length_start > 0
GO
DROP PROCEDURE dbo.proc1
DROP PROCEDURE dbo.proc2
Edit:
After some testing, some false positives, but overall it gives you a good chance of finding objects with "varchar" with no length.
The #TEMP column definition in the code didn't have DECIMAL(19, 2), nor DECIMAL(10) but rather just DECIMAL
Personally I wouldn't allow DECIMAL(10) because it is an accident waiting to happen that the SCALE parameter is left off by mistake, but a STRICT options could easily allow pragmas that allow me to say that I don't want to allow DECIMAL(10), and you to say that you do want to allow them ... but DECIMAL on its own is utterly useless
That's like saying that UNDO isn't necessary. I ain't blaming Microsoft, but it would be much easier for them to build a STRICT option than anyone else trying to build an add-on or, even more work, a standalone. The collective time, in the industry, that is lost to snafus like this is huge. If I have to can the rollout that we have scheduled for Wednesday as a consequence of the time lost to this the $cost, for something as trivial as this, will be huge.
Probably some legacy, or perhaps even standards-compliance, issue? (If not for this then probably for other, similar issues).
Only just occurred to me, but apart from other warnings it would be nice if STRICT could also alert for anything deprecated - much cheaper to get rid of things like that over 4 or 5 years when the announcement is first made if the Parser starts whinging about it straight away 
If it made it easier for people to upgrade, because their code was already "New Version Ready" MS could sell more SQL upgrades - there ... that ought to be $reason $enough