This post is in response to Top 10 Things I Hate About SQL Server found at http://weblogs.sqlteam.com/jeffs/archive/2005/05/24/5248.aspx
1. SQL is too complicated!
This comment doesn't make a lot of sense. If you design your database to be properly NORMALIZED, you wouldn't be storing "JAN, FEB, MAR" in one column. Learn how to NORMALIZE your tables and it will make your life a lot easier.
MS didn't make this complicated. It's a standard, mathematically-proven concept that has been around since Dr. Codd introduced the Relational Model.
2. Datatype conversions!
Using VARCHAR for everything is very poor strategy. If you have a column that's supposed to hold only INTEGER values, but use a VARCHAR, how do you keep people from entering "ZDFGEH34" in it?
Different Data Types and conversions are a standard in any language. Additionally, SQL Server has some implicit conversions; although it's best to tell SQL Server exactly what you want. After all, you probably learned a long time ago that computers can only do *exactly* what you tell them to do. They used to call it "GIGO" - Garbage In, Garbage Out.
By the way, what do you get when you add "A" and "1"? Is it "B"? Or is it "A1"? Probably depends on whether you're using your VARCHARs to represent hexadecimal numbers or hard text. Unfortunately SQL is only as smart as the developer. For every person who wants "12" + "5" to equal "17", there is someone else who just wants to concatenate the strings.
3. Uh .... Formatting?
Uh... If you haven't figured it out yet, SQL Server is not a presentation platform. It is a data storage and retrieval platform; what we often refer to in the biz as the "back end". You can write functions to do the formatting for you, or you can do it on the "front end", as God and ANSI intended. After all, if your SQL Server is located on a box in the next state, how do you properly right-justify or center the data using a non-fixed-width font at the server? Keep in mind that your SQL Server might have different display settings than your workstation. Are you going to pass in the various required hardware settings with each query? Query Analyzer is a developer's tool, not an end-user database access application. (REF: "front end", "back end")
4. Sorting?
Really simple. Add an "ORDER BY" clause to your orders. If you want SQL Server to *know* that results are to be sorted by Last Name/First Name without telling it, set up a CLUSTERED INDEX. Otherwise, how will the next user get a result set ordered by ADDRESS? Work location? Date of Birth??? Unfortunately having the options to bring the data back in ANY ORDER means you have to actually type in "ORDER BY Lastname, Firstname". P.S. - You can copy and paste the ORDER BY clause I just gave you to avoid all that tedious typing.
Oh yeah, that months out of order thing is one of those great benefits of storing ALL your data as VARCHARs instead of doing it the RIGHT WAY.
5. The IN() operator never works!
IN works just fine. It does not split up lists, like it appears you want. There are plenty of ways to do that though; if you PROPERLY NORMALIZE your data that is. But it sounds like that's not really an option for you anyway.
6. Foreign Keys Constraints !
It's called DRI (Data Referential Integrity), and it's built into SQL Server.
7. Primary Keys don't work!
LMMFAO. You need to use the NATURAL KEY, which would be StateName or StateAbbreviation in your "tblStatesTable" (REF: Redundant Department of Redundancy). Set your PRIMARY KEY on the NATURAL KEY. Study up on the differences between NATURAL and SURROGATE KEYS.
8. S-Q-L is S-L-O-W !!!
LMMFATFO. It's SLOW because you're using LIKE '%JAN%' in your query. It's SLOW because the WHERE clause below cannot use an INDEX. It's SLOW because you did not properly NORMALIZE your data.
Just for comparison, pull out a phone book, and locate every last name entry that begins with "JOH" (i.e., "JOH%"). Now locate every last name entry that has "TL" anywhere in it (i.e., "%TL%"). Which one will take you longer? SQL Server is only as smart as the developer.
This is all the more ridiculous because you name your tables "tblTable" (how redundantly redundant) and your columns tblTable_colMonthList (extreme, over-the-top, excessive redundantly redundant redundancy).
9. Cryptic Error Messages!
That means that you don't have an Order in the Orders table to match the row you're trying to put into the Order Details table. Really simple stuff. Your GROUP BY problem is caused because you're using aggregate functions and not specifying the correct columns in the GROUP BY. The Error Messages are for Developers, and they make perfect sense; although you could always make your own error messages. Look it up in Books Online sometime.
You're trying to truncate tables in the MASTER database? Are you on medication?? Bill Gates could probably afford to buy bad developers brain transplants; but why in the world would he?
10. Table and Column referencing!
Look up Dynamic SQL sometime. It's called sp_executesql or EXEC. There are a bazillion ways to dynamically build SQL statements, but they're very dangerous. I'd be wary of letting anyone who TRUNCATES Master database system tables write dynamic SQL against my databases. Geez.
INSERT works just fine. And you can modify the design of your tables with ALTER TABLE. But then again if you need to dynamically alter your tables it's a pretty good sign that you don't know what the heck you're doing anyway. A properly NORMALIZED database makes SELECTING the data easier, and alleviates the need for DYNAMIC SQL. SQL Server doesn't really need to address this, since Database Design 101 usually does a pretty good job of addressing it. For those who didn't sleep through it anyway.