April 28, 2025 at 12:00 am
Comments posted to this topic are about the item Is SQL Server Feature Complete?
April 28, 2025 at 12:58 am
No... it's not "feature" complete. First, I don't know what they heck they did to it but there was a massive slowdown when 2019 came out and it's still slow in 2022. It killed us when we upgraded and we're still trying to recover.
The newer temporal functions that came out in 2008 suck for performance and they don't allow ANSI functionality like subtracting one datetime2 from another to easily determine a duration nor do the new date serial numbers make it easy to sum durations, etc. They're finally coming out with DATEADD_BIG(). They didn't come out with DATEDIFF_BIG() until 8 years after the messed up on simple date calculations and they waited another 9 years after that (promised in 2025) to come out with DATEADD_BIG().
Then, there's PIVOT, which still makes horrible sucking sounds when you compare it to the PIVOT available in MS Access.
They still haven't modernized index maintenance... I'm mean seriously... the Peter Norton algorithm has been out for longer than SQL Server.
And they still don't have an easy to use BULK_EXPORT() and SSIS doesn't make up for that or I/O with spreadsheets.
SQL is not feature complete, error free, or performance worthy and it's a real shame because it was a gold standard (especially for code simplicity) for a long time.
And, no... not everyone wants to put their stuff in the cloud, either.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 28, 2025 at 5:29 am
First, I don't know what they heck they did to it but there was a massive slowdown when 2019 came out and it's still slow in 2022[\quote]
We are contemplating moving from SS 2017 to 2022. Your statment now has me worried. Is it that bad?
As to the question of completeness, that is not easy to answer without a hard list of what makes something complete. I think the features available are enough for most of the projects I've come across. Other newer buggy items meant to try to help can just be avoided if they casuse more issues than they are worth.
----------------------------------------------------
April 28, 2025 at 5:34 am
First, I don't know what they heck they did to it but there was a massive slowdown when 2019 came out and it's still slow in 2022
We are contemplating moving from SS 2017 to 2022. Your statment now has me worried. Is it that bad?
As to the question of completeness, that is not easy to answer without a hard list of what makes something complete. I think the features available are enough for most of the projects I've come across. Other newer buggy items meant to try to help can just be avoided if they casuse more issues than they are worth.
----------------------------------------------------
April 28, 2025 at 8:19 am
I notice that BULK INSERT can be used with OPENROWSET too import Parquet and DataBricks Delta tables. I just wish there was a BULK EXPORT command. The bcp utility is an old faithful but these days not all data comes in tabular form. BULK EXPORT seems a strange omission.
There are a number of DB platforms that use a COPY FROM, COPY TO command to import/export data.
For external tables, being able to use Delta and Iceberg tables would be useful.
The Parquet format is great for tabular data but can actually hold nested structures too. A number of tools can read tabular data from Parquet but few can handle nested data directly.
I'm fully onboard with Jeff's comments on PIVOT. My SQL DB career started with MS Access 2.0 which had a much more elegant approach.
My observations over the past 10 years are that data interoperability beyond ODBC/JDBC and tabular data has exploded. Y0u won't put that genie back in the bottle nor should you seek to do so.
sp_addextendedproperty and sp_updateextendedproperty have always offered immense potential. I would not get rid of them but I would certainly add COMMENT ON <object> IS '<Useful business description>'; as a native SQL command.
Perhaps CREATE OR REPLACE PROPERTY <property name> ON <object> TYPE <string/int/date etc> IS '<whatever>' would be a useful SQL synonym for those stored procs?
There are some undocumented and unsupported stored procedures that have been undocumented and unsupported since SQL Server 7.0 (possibly since 6.5). Perhaps these should be formally documented and supported now? sp_msforeachdb, sp_msforeachtable I'm looking at you!
April 28, 2025 at 11:38 am
How can something be called feature complete, when it has a feature, but it is still clumsy? Is a person with a crippled limb "feature complete"?
Examples: see Brent Ozars 1st April Newsletter: https://www.brentozar.com/archive/2025/04/whats-new-in-sql-server-2025-2/
(f.e. DISTINCT support in STRING_AGG)
Or the inability to run a SQL Agent Job from step 2 to 4 (of 10) without modifying the job and manually set a quit after this step (and hopefully not forget to set it back after your tests).
Or the almost nonexisting permission management for SQL Agent Jobs.
Or the need to implicit CAST() a BIT column always to INT to be able to use it in a SUM(), MIN() or MAX() or even AVG(), when I want the percentage of set flags).
God is real, unless declared integer.
April 28, 2025 at 2:28 pm
If the features exist, however clunky, they're there. In that case, it's feature complete, but the feature perhaps need definement. Feature complete doesn't mean that there isn't work to be done, but rather there isn't anything new needed.
There are plenty of commands that need refinement and performance work to improve them. I would love Bulk export, but honestly xp_cmdshell and bcp work, though clunky. If you have job steps you need to run, they could be second jobs, and you could call one job from another.
The Delta/Iceberg stuff is certainly a new feature, and worth developing.
April 29, 2025 at 5:16 am
There are a great many features missing from SQL Server but they are missing from the competition as well and some of the things are integral to SQL so it would be unreasonable to criticise a product called SQL server on those grounds. However I still will.
SQL server doesn't implement assertions. Create Assertion has been in the SQL standard since 1992. There are situations where a fully flexible approach to multi-table constraints would be useful. To some extent this can be achieved through triggers but not completely. This isn't the right place to go into the details. No other SQL product has implemented assertions. I suspect that the problem is not the logic (which is quite straightforward) but rather performance and locking issues - maybe Microsoft should be working harder to overcome these problems.
Is the problem of achieving adequate performance at serializable isolation solvable? Is there a mathematical solution or can brute force solve the problem? Parallel processing for obvious reasons won't work. Serialization is the only level of isolation that ensures logical consistency. On the positive side SQL Server's locking strategy is well ahead of the competition. That other products run permanently with MVCC leads inevitably to errors in consistency.
For a thorough examination of SQL's failings as a relational language I recommend reading Chris Date. It is unfortunate that Microsoft's efforts to provide an alternative to SQL usually turn out to be worse than SQL. R's dplyr package looks more like a step in the right direction.
April 29, 2025 at 8:18 am
There are situations where a fully flexible approach to multi-table constraints would be useful. To some extent this can be achieved through triggers but not completely.
Seconded. We've had to implement multi-table constraints via a number of not entirely satisfactory ways (e.g. triggers, views with unique indexes and FKs on calculated columns). It would be great if SQL had a feature to do this natively.
April 29, 2025 at 4:47 pm
When your business is selling new versions, no version is ever feature-complete.
In a world with changing requirements, something is only feature complete until someone finds new use-cases.
April 29, 2025 at 5:17 pm
In a world with changing requirements, something is only feature complete until someone finds new use-cases.
I really wonder, why nobody ever has tried (or better was sucessfull) of running Doom on SQL Server 🙂
God is real, unless declared integer.
April 29, 2025 at 5:33 pm
I think you would be better off with Zork in SQL Server than Doom
April 30, 2025 at 7:43 am
How about having SQL Server analyse and intelligently propose new indexes, based on missing indexes information in the plan cache or Query Store.
Nowadays we still have to collect all missing indexes information and filter and test potential solutions, not only for the given query, but also for the common good of all database consumers.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 30, 2025 at 7:59 am
How about having SQL Server analyse and intelligently propose new indexes, based on missing indexes information in the plan cache or Query Store.
No, you really don't want this - given the bad quality of the recommendations which is often enough a result of bad queries. And you don't want a index on a very large write heavy query that contains almost all columns in the INCLUDE just because a controller runs a stupid analyzing query without any optimization once a day / week / month while he is getting a fresh coffee from the kitchen.
And even with all AI - this is stuff that the SQL server simply doesn't know
God is real, unless declared integer.
April 30, 2025 at 2:39 pm
Notably, your concerns are coverd by "intelligent"
Ofcourse a dba will still need to interprete and test all this .
We still do not want indexes like "[<Name of Missing Index, sysname,>]" or indexes which include all clumns.
The missing indexes mentioned in SQLPlans only go for that query plan at that given moment in time.
MS should at least provide better solutions than this
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply