
Hardly-used SQL Server functions that should be used more often - RmDen
http://sqlservercode.blogspot.com/2007/02/ten-sql-server-functions-that-you.html
======
rodionos
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
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
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
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.

------
metamicah
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
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

~~~
MarkusWinand
Although SQL Server vNext (14.x) will get string_agg:
[https://msdn.microsoft.com/en-
us/library/mt790580.aspx](https://msdn.microsoft.com/en-
us/library/mt790580.aspx)

In the latest version of the SQL standard (SQL:2016) the LISTAGG function was
added for this purpose. I find it "disturbing" that the next SQL Server
release gets a function for this, but the new function doesn't follow the new
standard.

Even worse: the name "string_agg" was apperently borrowed from PostgreSQL. But
if you think that SQL Server will stick to the syntax of PostgreSQL's
string_agg — nope. They use the same function name, but a different syntax.

Microsoft double fail, I'd say.

I've just written an article about LISTAGG, btw: [http://modern-
sql.com/feature/listagg](http://modern-sql.com/feature/listagg)

~~~
kbenson
That doesn't exactly surprise me. MSSQL feels like MySQL's old MyISAM table
type technology that's been hacked and extended for decades rather than a
shift to something more technologically competent. I still get errors on a
regular basis about the deadlock manager killing queries because they became
deadlocked. Because of course the way to deal with your system easily running
into deadlocks is just to implement a checker that runs once a second and
kills those queries that deadlock, and not to actually make the system work
better....

This is even more ludicrous when you consider all the extremely cool stuff the
team responsible for porting MSSQL to other platforms accomplished in order to
get it functioning on Linux[1].

1: [https://arstechnica.com/information-
technology/2016/12/how-a...](https://arstechnica.com/information-
technology/2016/12/how-an-old-drawbridge-helped-microsoft-bring-sql-server-to-
linux/)

~~~
treebeard901
> Because of course the way to deal with your system easily running into
> deadlocks is just to implement a checker that runs once a second and kills
> those queries that deadlock, and not to actually make the system work
> better....

If your database design and code frequently deadlocks, I'd argue it is not a
sign of the database engine being incompetent. After all, it's doing what you
asked it to do. The comparison to MySQL is simply not true and it ignores the
years of new features and enhancements to the product.

~~~
kbenson
I admit it's entirely possible most of my bad experiences with SQL Server is
due to a horrible schema. I don't control the schema in this case, nor the
majority of different types of queries that run against that schema.

What I do know is that I _routinely_ get single, non-transactional select
queried deadlocked and killed. That _seems_ like something that shouldn't
happen. If I had to guess, it's likely because of the subselects to
approximate group_concat, as outlined above, and they have different lock
times than the main query, allowing for this single query to attampt to get
locks at different times. Unfortunately, this is an ORM generated query, so
sticking NOLOCK all over the place is easier said than done, and since I don't
control the DB, I can't change default transactional levels. Perhaps that
would solve it too.

It just seems odd that what appears to be (from my admittedly non SQL server
expert) eyes to be a fairly standard database schema is so easily susceptible
to this problem.

------
infogulch
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/quotena...](https://docs.microsoft.com/en-
us/sql/t-sql/functions/quotename-transact-sql)

