6 ms·

Hardly-used SQL Server functions that should be used more often

50 points by RmDen 10y ago

rodionos 10y ago
Here's a counter argument. Continue ignoring functions which are non-standard or are confusing. http://stackoverflow.com/search?q=nullif+%5Bsql-server%5D http://stackoverflow.com/search?q=nullif+%5Bsql-server%5D 1500+ questions on NULLIF on SO...
- kbenson 10y ago
  The same argument can be made for not using non-standard data types in your schema. I'm actually torn about that, because as time goes on I prefer to put more and more constraints in the schema to ensure the data is valid, and remove errors in how it's accessed. Should IP, JSON or geolocation data types (to name a few from Postgres) be ignored for portability if you are targeting Postgres during development and have no plans to port your app to another database? I suspect the answer relies on the specifics of your situation, and how likely it is you actually will need to target another DB. In some ways, non-standard functions are easier, because you could probably recreate most of those from scratch if you had to.
  - derefr 10y ago
    I constantly wish we had had some sort of standardized DBMS wire protocol. Then the answer to "should you use higher-level datatypes" would be obvious: sure, use them, and then if you switch to a DBMS that doesn't support them, just stand up a proxy that has polyfills for those types, exposing them on its front but turning them into lower-level types when it passes the request on. Such a wire protocol standard would likely evolve a lot faster than the stick-in-the-mud that is the SQL standard, because the "shimability" of such proxies would allow app developers to start confidently using new DBMS features much earlier, the same way frontend developers use new Javascript APIs rather fearlessly given the existence of a rich system of polyfills in NPM. And then DBMS vendors—like browser vendors—would be driven to add said features to their own systems much more quickly, since now their developer-base would use the polyfills—but be annoyed by them as they did so—and the vendor could ameliorate that annoyance by shipping the feature. (Rather than the current world, where the best-practice is "just don't use anybody's custom extensions", so developers don't tend to know what they're missing to get annoyed.)
    - kbenson 10y ago
      You know, I wonder if this could be done with ODBC. It would admittedly not be as spiffy as what you're suggesting, but event with the absence of discoverability, I bet an ODBC proxy with specifically loaded filters for DB-A to DB-B could achieve a lot. It would be a project ot make sure the conversion filters worked well and were up to date, but something like that might be the best we can expect for a while.
- cpburns2009 10y ago
  Actually, that search yields all questions and answers. Adding "is:question" to the search query http://stackoverflow.com/search?q=nullif+%5Bsql-server%5D+is%3Aquestion http://stackoverflow.com/search?q=nullif+%5Bsql-server%5D+is... results in 337 answers. This is still a large number though because NULLIF is a ridiculously simple function to use. Base upon a small sample, these questions appear to be due to either misordered expressions or poor grasp of order of operations that happen to involve NULLIF.
  - YCode 10y ago
    Without something to compare it to isn't it hard to say if 337 is a large or small number of questions?
- Pxtl 10y ago
  By that logic we should avoid ISNULL. Because i've lost count of conversations where we've had confusion between ISNULL(x) and x IS NULL.
- mamcx 10y ago
  That is non-sense. Is like use Java, and think is wrong to use any code apart of IF, WHILE, ARRAY and ASSIGNMENT because the extra is non-standard across languages. You have a powerfull RDBMS. NOT USE THAT POWER IS IDIOTIC.
  - mamcx 10y ago
    I know some have a strong-belief to not use the DB-features as fully as possible. Instead of down-vote, think hard why do you have that belief and if truly the reason apply TO EVERYONE ELSE. --- In the other hand, why is instead "ok" to use a NoSql database? EVERYTHING is non-standard, in contrast with sql. Who, thinking rationally, will deny to use the full redis features just because is "non-standard"?
infogulch 10y ago
Cool I didn't know about PARSENAME. I've always written udfs for that. I suppose it wouldn't work in all cases since it can only split on '.', but with REPLACE it gets you a long ways. Edit: Looks like PARSENAME is more exciting than that. It actually unescapes escaped identifiers: select n, parsename('[srv with newline].[db [ with ]] brackets].[schema with . dot]."quoted . dot"', n) from (values (1),(2),(3),(4),(5)) n(n) /* results: 1 quoted . dot 2 schema with . dot 3 db [ with ] brackets 4 srv with newline 5 NULL */ Another fun fact about SQL Server: Object identifiers support newlines, among other crazy characters. Don't believe me? create table #t ([newline here] int) select * from #t Stay safe, and always use QUOTENAME [1]. :D [1]: https://docs.microsoft.com/en-us/sql/t-sql/functions/quotename-transact-sql https://docs.microsoft.com/en-us/sql/t-sql/functions/quotena...
metamicah 10y ago
Some of these functions (like SIGN, STUFF, and PARSENAME) set off little alarms in my head that sound vaguely like a full-stack developer furiously yelling "DON'T HANDLE THIS IN YOUR DATABASE LAYER." Of course we don't always have that option, but I feel like I have to acknowledge those alarms if I'm going to use them.
- kbenson 10y ago
  STUFF is actually needed in the database layer to not kill performance of your app. SQL server has no built in convenience function the equivalent of mysql's GROUP_CONCAT. Instead, you do a subquery in the selected field, interpret it as XML, pull out the specific XML elements you are looking for as a list, and then use STUFF to join them. As much as you might recoil in horror at this (and I still do), it's actually fairly performant because the optimizer recognizes the subquery is dependent on the main query, and does the equivalent of a join under the covers or something. E.g. # MSSQL SELECT movie.id, movie.name, STUFF( (SELECT ','+producer.last_name FROM actor WHERE producer.movie_id = movie.id FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(32)'), FROM movie; # MySQL SELECT movie.id, movie.name, GROUP_CONCAT(producer.last_name), FROM movie LEFT JOIN producer ON movie.id = producer.movie_id
  - Pxtl 10y ago
    Every time I've used STUFF that way I've had hopelessly atrocious performance. Simply dire. In the end I've found that re-implementing GROUP_CONCAT using SQLCLR is far more performant. But ultimatley, the fact that queries are so tied to the flat resultset is infuriating, and that sucks for every SQL engine. I want a graph of results, not a goddamned glorified spreadsheet. There are ORMs that mitigate that limitation, but ultimately they're getting compiled down to SQL so in the middle there's a big flat resultset coming back or a bunch of redundant queries.
    - kbenson 10y ago
      > There are ORMs that mitigate that limitation, but ultimately they're getting compiled down to SQL so in the middle there's a big flat resultset coming back or a bunch of redundant queries. Yes. It's important to be very aware of this. Letting the ORM do this for you automatically for a few chained joins can result in a massive resultset that is literally 90%+ redundant information depending on the strategy the ORM uses.

50 points by RmDen 10y ago

Here's a counter argument. Continue ignoring functions which are non-standard or are confusing. http://stackoverflow.com/search?q=nullif+%5Bsql-server%5D http://stackoverflow.com/search?q=nullif+%5Bsql-server%5D 1500+ questions on NULLIF on SO...

kbenson 10y ago
The same argument can be made for not using non-standard data types in your schema. I'm actually torn about that, because as time goes on I prefer to put more and more constraints in the schema to ensure the data is valid, and remove errors in how it's accessed. Should IP, JSON or geolocation data types (to name a few from Postgres) be ignored for portability if you are targeting Postgres during development and have no plans to port your app to another database? I suspect the answer relies on the specifics of your situation, and how likely it is you actually will need to target another DB. In some ways, non-standard functions are easier, because you could probably recreate most of those from scratch if you had to.
- derefr 10y ago
  I constantly wish we had had some sort of standardized DBMS wire protocol. Then the answer to "should you use higher-level datatypes" would be obvious: sure, use them, and then if you switch to a DBMS that doesn't support them, just stand up a proxy that has polyfills for those types, exposing them on its front but turning them into lower-level types when it passes the request on. Such a wire protocol standard would likely evolve a lot faster than the stick-in-the-mud that is the SQL standard, because the "shimability" of such proxies would allow app developers to start confidently using new DBMS features much earlier, the same way frontend developers use new Javascript APIs rather fearlessly given the existence of a rich system of polyfills in NPM. And then DBMS vendors—like browser vendors—would be driven to add said features to their own systems much more quickly, since now their developer-base would use the polyfills—but be annoyed by them as they did so—and the vendor could ameliorate that annoyance by shipping the feature. (Rather than the current world, where the best-practice is "just don't use anybody's custom extensions", so developers don't tend to know what they're missing to get annoyed.)
  - kbenson 10y ago
    You know, I wonder if this could be done with ODBC. It would admittedly not be as spiffy as what you're suggesting, but event with the absence of discoverability, I bet an ODBC proxy with specifically loaded filters for DB-A to DB-B could achieve a lot. It would be a project ot make sure the conversion filters worked well and were up to date, but something like that might be the best we can expect for a while.
cpburns2009 10y ago
Actually, that search yields all questions and answers. Adding "is:question" to the search query http://stackoverflow.com/search?q=nullif+%5Bsql-server%5D+is%3Aquestion http://stackoverflow.com/search?q=nullif+%5Bsql-server%5D+is... results in 337 answers. This is still a large number though because NULLIF is a ridiculously simple function to use. Base upon a small sample, these questions appear to be due to either misordered expressions or poor grasp of order of operations that happen to involve NULLIF.
- YCode 10y ago
  Without something to compare it to isn't it hard to say if 337 is a large or small number of questions?
Pxtl 10y ago
By that logic we should avoid ISNULL. Because i've lost count of conversations where we've had confusion between ISNULL(x) and x IS NULL.
mamcx 10y ago
That is non-sense. Is like use Java, and think is wrong to use any code apart of IF, WHILE, ARRAY and ASSIGNMENT because the extra is non-standard across languages. You have a powerfull RDBMS. NOT USE THAT POWER IS IDIOTIC.
- mamcx 10y ago
  I know some have a strong-belief to not use the DB-features as fully as possible. Instead of down-vote, think hard why do you have that belief and if truly the reason apply TO EVERYONE ELSE. --- In the other hand, why is instead "ok" to use a NoSql database? EVERYTHING is non-standard, in contrast with sql. Who, thinking rationally, will deny to use the full redis features just because is "non-standard"?

infogulch 10y ago

Cool I didn't know about PARSENAME. I've always written udfs for that. I suppose it wouldn't work in all cases since it can only split on '.', but with REPLACE it gets you a long ways. Edit: Looks like PARSENAME is more exciting than that. It actually unescapes escaped identifiers: select n, parsename('[srv with newline].[db [ with ]] brackets].[schema with . dot]."quoted . dot"', n) from (values (1),(2),(3),(4),(5)) n(n) /* results: 1 quoted . dot 2 schema with . dot 3 db [ with ] brackets 4 srv with newline 5 NULL */ Another fun fact about SQL Server: Object identifiers support newlines, among other crazy characters. Don't believe me? create table #t ([newline here] int) select * from #t Stay safe, and always use QUOTENAME [1]. :D [1]: https://docs.microsoft.com/en-us/sql/t-sql/functions/quotename-transact-sql https://docs.microsoft.com/en-us/sql/t-sql/functions/quotena...

metamicah 10y ago

Some of these functions (like SIGN, STUFF, and PARSENAME) set off little alarms in my head that sound vaguely like a full-stack developer furiously yelling "DON'T HANDLE THIS IN YOUR DATABASE LAYER." Of course we don't always have that option, but I feel like I have to acknowledge those alarms if I'm going to use them.

kbenson 10y ago
STUFF is actually needed in the database layer to not kill performance of your app. SQL server has no built in convenience function the equivalent of mysql's GROUP_CONCAT. Instead, you do a subquery in the selected field, interpret it as XML, pull out the specific XML elements you are looking for as a list, and then use STUFF to join them. As much as you might recoil in horror at this (and I still do), it's actually fairly performant because the optimizer recognizes the subquery is dependent on the main query, and does the equivalent of a join under the covers or something. E.g. # MSSQL SELECT movie.id, movie.name, STUFF( (SELECT ','+producer.last_name FROM actor WHERE producer.movie_id = movie.id FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(32)'), FROM movie; # MySQL SELECT movie.id, movie.name, GROUP_CONCAT(producer.last_name), FROM movie LEFT JOIN producer ON movie.id = producer.movie_id
- Pxtl 10y ago
  Every time I've used STUFF that way I've had hopelessly atrocious performance. Simply dire. In the end I've found that re-implementing GROUP_CONCAT using SQLCLR is far more performant. But ultimatley, the fact that queries are so tied to the flat resultset is infuriating, and that sucks for every SQL engine. I want a graph of results, not a goddamned glorified spreadsheet. There are ORMs that mitigate that limitation, but ultimately they're getting compiled down to SQL so in the middle there's a big flat resultset coming back or a bunch of redundant queries.
  - kbenson 10y ago
    > There are ORMs that mitigate that limitation, but ultimately they're getting compiled down to SQL so in the middle there's a big flat resultset coming back or a bunch of redundant queries. Yes. It's important to be very aware of this. Letting the ORM do this for you automatically for a few chained joins can result in a massive resultset that is literally 90%+ redundant information depending on the strategy the ORM uses.