I have been tagged by Aaron Bertrand in the latest SQL meme: Tagged: 5 things SQL Server should drop. This meme is about five thing that you wished that were dropped from SQL Server. Actually I prefer that SQL Server adds stuff instead of removes stuff, I can always ingore the things I don’t like. Anyhow since this is about things that should be removed, here is my list.
The isnumeric function is something that can bring you into trouble without you even knowing it, for example run these queries
select ISNUMERIC(CHAR(9)), ISNUMERIC('1D2'), ISNUMERIC('.'), ISNUMERIC('-'), ISNUMERIC('+')
Really a tab (Char(9) is numeric? I would be much better if SQL Server had IsInteger, IsFloat,IsTinyInteger functions or perhaps the ability to pass in the the type to isnumeric
Something like this
SELECT ISNUMERIC('123', 'TinyInt')
Restrictions on Indexed Views
Lookup indexed views and you will see more restrictions than you can fathom, you can’t memorize these things either, there are too many. First you need a bunch of setting
SET NUMERIC_ROUNDABORT OFF; SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON;
Why not replace that with
SET CREATE_INDEXED_VIEW_SETTINGS ON
This would be similar to ANSI_DEFAULTS, that sets 7 other settings to ON or OFF
After you are done with all these settings you have a whole laundry list of restrictions. Some of these restriction seems a little restricting to me, I have heard that other RDBMS vendors don’t have some of these restrictions in their materialized views.
Take away the ability to create an ordered view since it is ignored anyway.
So for example if you create this view, which was working nicely in SQL Server 2000
CREATE VIEW vTestSort AS SELECT TOP 100 PERCENT id FROM TestSort ORDER BY id
This doesn’t work in 2005 or 2008
OF course you can do this and it will ‘work’ and I wrote about it here: Create a sorted view in SQL Server 2005 and SQL Server 2008
CREATE VIEW vTestSort2 AS SELECT TOP 99.99 PERCENT id FROM TestSort ORDER BY id
I say get rid of that, if people want the view to be in a specific order then specify ORDER BY…how lazy can you be?
Unique constraints with one NULL value
This is a great interview question and that is the only usefulness of this constraint. Either allow multiple NULL values or disallow NULL values.
Of course you can now use a Filtered Index in 2008 to accomplish this
create unique nonclustered index IX_LookMaMultipleNullValues on dbo.SomeTable(SomeColumn) where SomeColumn is not null;
But not everyone is on 2008
Functions that are very similar
Aaron already touched upon this with timestamp and rowversion in his post. SQL Server has a bunch of function that are very similar, I think we should retire some of those. Here is a list.
ISNULL and COALESCE
Those are not really the same, COALESCE allows for more than 1 expressions, preserves the datatype and is ANSI standard. So why do we need ISNULL? I know, I know..someone will post that ISNULL performs better……..
CURRENT_TIMESTAMP and GETDATE()
Banish GETDATE() and use CURRENT_TIMESTAMP instead, the only reason people (me included) use GETDATE() is because we are lazy and it is shorter. As a matter of fact use CURRENT_TIMESTAMP when you create a table and then script the table out…what do you see? It will be GETDATE()
SELECT SYSTEM_USER, SUSER_SNAME(), USER, USER_NAME(), CURRENT_USER, SESSION_USER
When I tell you to capture a user which of these six will you use?
That is it for me, I am tagging the following people