I asked a two-part question on Twitter: if you could only remove one feature from SQL, why would it be cursors?
If you could only remove one feature from SQL, why would it be cursors?
— Brent Ozar (@BrentO) January 20, 2021
The responses were great:
Auto-shrink does not agree
— Warner Chaves (@warchav) January 20, 2021
service broker, bro.
— ? Andy Mallon #BlackLivesMatter (@AMtwo) January 20, 2021
Lightweight pooling
— Gianluca Sartori (@spaghettidba) January 20, 2021
Because SQLCLR was too popular.
— Marc Gravell (@marcgravell) January 20, 2021
Autoshrink,
— Gail Shaw (@SQLintheWild) January 20, 2021
Merge statement for me
— Justin Bird ??????? #mtfbwy (@jbmetricy) January 20, 2021
Justin’s referring to MERGE’s hot mess.
Hmmm, cursors or triggers? ?
— DapperDweeb (@DapperDweeb) January 20, 2021
Can we add linked servers to this list?
— Timmy the SQL (@TimmySQL) January 20, 2021
my personal pain point in SQL Server: OLE Automation.
All OA Objects are bad, but especially "in process" OLE objects.
CLR bothers me too, but CLR is not not OA bad.
Cursors are just garden grade evil. They annoy me, but I have seen use cases that support cursors.
— Pat Phelan (@YetAnotherSQL) January 21, 2021
But out of all of ’em, this one intrigues me the most:
SSAS, SSRS(almost there on that one), not sure about SISS. Make em seperate products and lower the price of SQL server editions.
— John Cas (@moresunshine_1) January 21, 2021
How about you?
47 Comments. Leave new
I responded with “select statements” though mine was, clearly, a joke.
As far as John’s response:
Aren’t SSIS and SSRS technically “free” in that you can run them on any number of machines… the only one that needs to have paid licensing is the server that is running whichever SQL instances? I honestly hate that they split off SSRS from the main install suite… It seems more of a pain to upgrade and keep up to date now.
If SSIS is installed on a server, that server needs to be licensed for SQL Server. I don’t believe it needs enterprise edition licenses though so you might be able to get away with standard on servers where you don’t have SQL installed.
Oh interesting. I thought it just could only connect to licensed servers. Good thing I just run it on my primary DB servers…
I would say ‘Database Engine Tuning Advisor’, sometimes people following it blindly.
Ability to store images in the database (whether of image type or varbinary max).
1 200 Gig table full of pdfs in a 400Gig database makes many things needlessly complicated.
As with regard to SSAS/SSRS/SSIS, they are certainly not the future with Power Bi/ADF. And I’ve used SSRS extensively. But I get that all you want is a database, it seems like your paying for something you don’t want. (They aren’t free, they are included. Like the T-shirt the tour company gives you on vacation).
And I want my cursors. Don’t use them for data manipulation, but do use them for more administrative level things.
There is a lot of good and bad in sql server. Fully separating SSAS, SSRS, and SSIS and adjusting the price of SE AND EE is possible, makes logical sense, allowing Microsoft to enhance each based on the votes of people via orders. Don’t get me wrong each product has a purpose and need, but how is Microsoft determining the amount of the expensive cost cost to the side car products? With any luck it will deduce the number of people that want to run it all on a single VM.
I manage a 13 TB DWH database with tables containing multi billion of rows.
Without cursors (of course not on row basis but e.g. over dates (day or month) or import file ids) I would not be able to handle serveral tasks because you just can not simply copy 100 billons of rows at once (or at least you shouldn’t, it would blow up your log file and it would be extremly annyoing if there occurs an error after 4 h processing in the one of last few records that causes a HUUUGE rollback (and rollbacks takes usually much longer than the operation itself).
So tell me .. how are you going to access data in another disparate SQL environment withOUT a linked server ?
Pretty stupid idea
By implementing a data warehouse…
Poly Base
That work should be done in the mid-tier.
SSIS
Yea.. how do you expect to get data from different separate data silos ( different SQL Servers ) to create the data warehouse ? Without using a “linked server” ?
SSIS would be a good start… unless that was a rhetorical question. We do a ton of this and I refuse to set up linked servers.
Side note: use the reply button to reply to an existing thread.
SSIS also adds considerable time to development and for some reason seems completely alien to a lot of people for how easy it is to use.
I myself, am pretty good with SSIS, but my SSIS jobs are all heavily driven by stored procedures run in an execute TSQL step and only use SSIS for things it is objectively better at, like derived columns, XML, dealing with other media types like other DBMSes, flat files, etc. It’s faster to develop TSQL than SSIS packages, its faster to run, you don’t deal with SSIS buffers, and can be updated without writing the query somewhere else, pasting it into visual studio and then deploying. Its also quicker to commit changes to a text file to TFS than a query step in visual studio. I can also delegate some tasks to someone else who can write me a query that I can put into a job somewhere else, instead of needing to be the only person who does everything for SSIS. Nevermind applications and reports that use lookups in a sql server dedicated to one application by application requirement with dynamic data that you could never extract and store with enough freshness.
Constrained Kerberos delegation is not hard to configure and a proper architecture avoids a maze of linked servers.
Not a good answer Wesley
Ok .. you have your reasons .. I have mine .. SSIS is to limited ..
Full Text, xml indexing, geo
Geo spatial data can be important. For my previous employer (a billboard advertising company) I used them to find the distance between billboard or to ensure that there are no tabac ads placed next to a school or kindergarden. Of course you could do it with your own C# / .NET routines (and this is what geospatial do internal), but why should I reinvent the wheel every time?
good point !
Excluding the MSDN documentation, is there any otherwise-competent DBA who suggests that auto-shrink should *ever* be turned on?
I can only think of a two legitimate reasons to use it:
• Working with a partner who requires it to be on (if anyone is in that unfortunate position, I’d love to hear your story!)
• In a non-production database, for pedagogical reasons.
Only thing I have seen are some databases on very limited, super fast SSD that cycle through a datawarehouse job, expand and then empty out before going on to the next. ideally they’d just have enough space to run every day, but hundreds of thousands of dollars of SSD may not be in the budget.
Filestream/Filetables – Just use a *real* object based storage system (S3 / Azure blob)
Yes! Please. 🙂 Maybe add VARBINARY(MAX). Luckily I got to remove 160TB of BLOB data from one DB. Yeah!
Maybe it’s time to replace read committed with RCSI as the default isolation level. Not sure if this counts as removing a feature?
Cursors I would keep – some legitimate uses though it’s open to abuse. DEVs kind of seem to be aware of cursors = bad for the most part. Scalar functions – less so. Maybe remove NESTED cursors! Give us nested INSERT EXEC instead. 🙂
David – I’m really, really with you on that. I loooove RCSI.
I wouldn’t wish for any particular feature to ‘go away’ – every feature is a tool that (in most cases) can be leveraged for a particular circumstance; – you either choose to use it or not, and whether you do comes down to experience. People hate on cursors and I never use them, but part of the learning process of how and why to avoid them is to be stung by them.
On the other hand are there features I wish they had not bothered to include and focussed time and resource elsewhere instead – that’s a different question!
Autoclose is a particularly bad idea, I only used it once to try and find out if a database was in use (which it was), but that wasn’t the bad part, during the autoclose process the database got corrupted – just as well I’d taken a backup.
My two most-hated keywords in SQL: UNION and DISTINCT.
I wouldn’t remove them, because they have legitimate usefulness. I’d just change the query optimiser, so that any plan generated for a statement that contains them spits out a message something like “Do you actually know what (TF) you’re doing, or are you just throwing SQL at a wall until something sticks?” Maybe with an animation of Brent wagging his finger.
If QueryStore stored login information, could integrate that into this new feature to make it more selective. “Sorry, your last 10 queries tied up 90% of the server memory for 3 hours, each. I’m not running this one, no sirree. Maybe you’d benefit from one of Brent’s free courses?”.
All too often, DISTINCT means “oh dear, I’m getting duplicates. Dunno why, stick a DISTINCT in there”. And UNION means
SELECT bla FROM MassiveHeap WHERE [some logic]
UNION
SELECT bla FROM SameMassiveHeap WHERE [some marginally-different logic]
UNION
SELECT bla FROM LetsHammerThatHeapAgain WHERE [more logic]
UNION
SELECT bla FROM YepThatSameHeapAgainWhyIsThisTakingHoursToRun? WHERE [more logic]
I’m going to have to disagree with you on UNION – it can be a really useful performance tuning technique, and I talk about that in Mastering Query Tuning. If you have two sargable things in your WHERE clause, like WHERE DisplayName LIKE ‘Alex%’ OR Location LIKE ‘India%’, and SQL Server refuses to use the DisplayName and Location indexes because it (incorrectly) thinks it’d be past the tipping point, you can break them into two queries with a UNION that will each use their respective indexes.
I tested the above case (just to check if UNION actually makes any positive difference).
SELECT [DisplayName]
,[Location]
FROM [dbo].[AAATest]
WHERE DisplayName Like ‘Alex%’
OR Location Like ‘India%’
Does index scan.
SELECT [DisplayName]
,[Location]
FROM [dbo].[AAATest]
WHERE Name Like ‘Alex%’
UNION
SELECT [DisplayName]
,[Location]
FROM [dbo].[AAATest]
WHERE Location Like ‘India%’
Does two index seeks. However, the latter query also does DISTINCT sort which makes it more than four times more ‘expensive’ than the former query.
Did I miss something in your explanation?
It’s a little tough to see without seeing the actual execution plans involved.
I’d disagree on union as well. As Brent pointed out, if you are taking data from one table using several separate filters you can start losing use of indexes. It can also just be used for code tidiness if the set of filters are particularly complex, you can break each select statement into separate sets of logic that won’t break when you need to make a change to something else.
I also use them extensively in ETLs where I take data from several separate sources to be combined into a single final reporting product. (HR and time card data in particular)
Distinct I can get on board with a bit more, but I also find it useful, again in ETLs. I don’t care how costly it is to run a query using distinct when I only run it once or maybe a few times per day, and in cases where the data is being significantly transformed, it is vastly more efficient to use a distinct that to group by function1(x,y,z), function2(1), function3(y) etc, or to write the data to a temp table first and then read it out again and use group by to get rid of duplicates.
UNION by itself is not an issue, but I saw a lot of people who needed UNION ALL, but forgot the ALL. How many times did I explain that UNION means in fact “UNION DISTINCT”?
Imagine if it was the same with SELECT: SELECT / SELECT ALL instead of SELECT DISTINCT / SELECT. It would be a nightmare? So, why UNION doesn’t work the same way?
Union is an awesome approach to dealing with convoluted predicates and honestly with dense WHERE requirements its waaaay easier to read in a UNION format as opposed to janky OR’s. I actually have a tendency to break up any query with OR in the WHERE clause just to see if performs better as an UNION. Its probably a bad habit though.
DELETE statement not requiring you to type something like DELETE ALL when you actually want to delete all the rows. How many countless people have been burnt by forgetting to add their where clause..
I agree. If the delete statement doesn’t have a where clause or join it should just not work. More often than not you are NEVER trying to delete the whole table and if you are, use a truncate statement.
On the off chances you need to delete the entire table, use where 1=1.
I’d vote for (NOLOCK).
“READ UNCOMMITED” is a little more alarming, and would appear much less in queries (at least at my company).
+1
seeing nolock hints all over the place makes me want to punch a baby lamb. I’d love a query hint that causes it to not interpret
Keith > like a readuncommitted snapshot isolation level RUSI , go for broke and make it turn transactions under both nolock/readuncommitted and read committed use snapshot isolation
SQL Server Licensing…
read uncomitted isolation, SQL authentication, CEIP, MS Access functions.
Wouldn’t get rid of cursors. I have a few uncommon production things where they are useful and have also used them for some administration things.
If we could ADD something… why don’t they add “Clippy”?
Feature difference between Enterprise edition and other editions.
User defined data types begs to differ. Think of the pain of changing the size of a data type on a large database.
I wish nolock did not exist. this way it would force those who find using it an advantage to fix their databases. oh, I mean in a production sense and in an every query I encounter sense.
Couple with the inbound requests for data inconsistency – I found hell
Untrained users.
I’ll admit, they’re not entirely MS’ fault, but we’ve probably all seen tables made of nvarchar(50) for every column, and filled with TheWrongDataType (TM). MS is partly to blame for have a table designer that does dumb stuff by default, instead of requiring users to have at least a bit of a think about what data crimes they’re about to commit.
To go all weird, SQL_VARIANT could be cleverly leveraged into big fat error messages like “dude, you meant a BIT not a VARCHAR!” – and I’d buy that for a dollar!