Rudy Panigas wrote:
My thought is more like a question. Why can't more features and functionality be added to SQL language and stop creating more languages to learn? If not, let's all go back to C++ and call it a day.
Lordy no. Look at what they've done with some of the new features in the past...
PIVOT... totally crippled compared to what MS Access has.
FORMAT... an average of 43 times slower than CONVERT.
DATE, TIME, and DATETIME2... all direct date math that still exists and is very valuable in DATETIME doesn't work.
DATEDIFFBIG... the "Fix" for the shambles they made of DATETIME2.
XML... always the worst performing thing in any execution plan where it appears.
HIERARCHYID... ridiculous compared to what it could have been.
STRING_SPLIT... Seriously? A splitter with no element ordinals? SERIOUSLY???
MERGE... lots of problems for years and is reportedly still slower than writing an UPSERT.
RECURSIVE CTEs... slower than a speeding WHILE loop.
ALTER INDEX REORGANIZE... removes free space on one side of the FILL FACTOR and does nothing for the other side which, like a bad drug, requires you to use it more the more you use it not to mention explosive log file growth (much worse than REBUILD). It, alone, is why most people think random GUIDs are the worst when it comes to bad page splits when it's actually one of the best (an index based on random GUIDs can easily go for literally months of inserts without even a good page split. GUIDs suck for other reasons, though).
ALTER INDEX REBUILD... it wasn't done right when it was DBCC DBREINDEX and they made little change with the "new" syntax. You can put a new wrapper on it but it's still the same ol' cracker. Peter Norton isn't jealous of this mistake at all.
SHRINKFILE... this is where Peter Norton started to laugh out loud. It could have been done so right and, instead, they used the worst method possible.
MAX DATATYPES... the principle is great but defaulting to INROW was absolutely stupid (and I wrote a presentation to demonstrate).
DEFAULTING TEMPDB TO TF1117 WITH NO WAY TO OPT OUT... I almost broke out my postal uniform and drove to Redmond on this one thanks to the next problem.
IDENTITY_INSERT... try that with a big Clustered Index on a box where TEMPDB is defaulted to TF1117 and discover how fast all your files grow because the whole table is sorted in <drum roll please> TEMPDB.
DTS/SSIS... an easy way for people that know squat about importing and exporting to really mess up especially if you want to move packages through Dev, Test, and Prod or when migrating to a new box with an upgraded version of SQL. And, no, that's not a slam on the good folks that took the time to actually learn how to use it the right way.
WINDOWING FUNCTIONS... great tools but it took them 7 years to make them fully functional.
sys.INDEXES... sys.SYSINDEXES is so much more functional but has been deprecated for years. Why the hell can't they leave useful tools the hell alone?
The list goes on and on.
They're supposed to finally fix BULK INSERT so that it will easily import TRUE CSV and the joke version of CSV that MS EXCEL exports. BULK INSERT is currently the fastest method to import data there is in SQL Server. Let's hope they don't screw that up by using RegEx Replace or something stupid that will slow it down.
And there's still no high performance "Tally-Table-Like" intrinsic function even though it's been held open on CONNECT and the Azure Feedback. Seeing what they did with String_Split(), they'd probably screw that up anyway by doing something stupid like converting it to an rCTE behind the scenes.
And still no BULK EXPORT or supported method to read a directory.
And remember the problems in 2012 where doing an ONLINE REBUILD of a Clustered Index could and did corrupt data?
I've gotten to the point where I actually fear each an every SP and CU and new release. I wish they'd spend a lot more time on fixing a the bastard new functionality they produced as well as fixing a whole lot of the old stuff.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
When you put the right degree of spin on it, the number 3|8
is also a glyph that describes the nature of a DBAs job. 😉
How to post code problems
Create a Tally Function (fnTally)