XML FeedsI 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.
Isnumeric
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.
Ordered 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()
User functions
- 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
Ted Krueger Blog / twitter: http://twitter.com/onpnt
Michelle Ufford Blog / twitter: http://twitter.com/sqlfool
Denny Cherry Blog / twitter: http://twitter.com/mrdenny
Adam Haines Blog / twitter: http://twitter.com/Adam_Haines
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
Oh I have some good ones in mind :-) I'll get mine up later. Thanks for the tag Denis. 
You suggest getting rid of "ordered views". How would you do that? A view is a virtual table, and like a table, has no order. 
Gareth, I have seen many people relying on this 'functionality' since in 2000 it did returned them ordered. I actually meant to say don't allow the order by clause in the view definition period.
One other thing SQl Server could do without is
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.