A Fine Slice Of SQL Server
David Fowler shows how to use bitwise logic in SQL Server:
Bitwise operators are operators that work on the individual bits of a value. Let’s take a little look at the three bitwise operators that we’ve got in SQL server (there is actually a fourth but I’ll talk about that one another time) and hopefully things will start making a bit of sense.
Now let us never speak of this again…
Comments closedShane O’Neill ponders an existential problem:
So, drinking my first (of many) coffee of the day, I asked him what was wrong with it.
I have two tables. 1 with values 1,2,3 & the other with values 1,2,3,4,5. When I use delete exists, it should just delete 1,2,3 but table1 is always empty.
Hmmm, not an unreasonable assumption I suppose so I asked him for his code.
Read on for Shane’s explanation, though he doesn’t like the verbosity. My version is, what happens in EXISTS stays in EXISTS. It just returns a signal to the outer query saying yea or nay and the outer query does its thing accordingly. In this case, if you want to tie results back to the delete operation, use IN (the ANSI standard way) or JOIN (typically my preferred way, given that IN can get dicey with more complex criteria).
Comments closedDennes Torres shows off four T-SQL functions releasing in SQL Server 2017:
Translate
Translate does the work of several replace functions, simplifying some queries.
The function is called ‘Translate’ because its main objective: transform one kind of information in another by doing a bunch of replaces.
For example: GeoJson and WKT are two different formats for coordinates. In GeoJson a coordinate is represented using the format ‘[137.4, 72.3]’ while in WKT a point is represented using the format ‘(137.4 72.3)’.
We would need several ‘Replace’s to transform GeoJson format in WKT format and the reverse. The ‘Translate’ function can do this easily.
I knew the first three, but Translate sneaked right past me.
Comments closedPhil Factor on the IsNumeric() function:
IsNumeric() is actually answering the question ‘Can this string be converted or ‘cast’ to a numeric datatype by SQL Server?’ It is pretty good at getting this answer right but it doesn’t tell you which datatype. It also isn’t interested in such things as overflow.
This problem of IsNumeric() was solved in SQL Server 2012. There was no way of morphing IsNumeric() intro something more valuable, so Try_Cast(), Try_Parse(), and Try_Convert() were introduced. The introduction of these system functions really solve the problem, unless you are still on earlier versions than SQL Server 2012. I’ll show how to do the same thing in previous versions of SQL Server, and demonstrates one or two tricks you can do with these functions.
I’ll stick with the post-2012 version, please.
Comments closedJohn Morehouse explains what the REVERSE function is and why it can be useful:
Recently I had a conversation with a colleague that wasn’t aware of the REVERSE function in SQL Server. I had sent them a code snippet and they inquired as to what the purpose of the function was. Essentially, this function that reverses any string value.
For example, the phrase:
The brown dog jumped over the lazy fox
reversed looks like this
xof yzal eht revo depmuj god nworb ehT
Read on to see a good example of when you might use REVERSE.
Comments closedWe’ve seen that we can easily solve the original problem with SQL only: Select some data from a table using predicate A, and if we don’t find any data for predicate A, then try finding data using predicate B from the same table.
Oracle and PostgreSQL can both optimise away the unnecessary query 2 by inserting a “probe” in their execution plans that knows whether the query 2 needs to be executed or not. In Oracle, we’ve even seen a situation where the combined query outperforms two individual queries. SQL Server 2014 surprisingly does not have such an optimisation.
Interesting totally-not-a-comparison between the three database products. There are some things I’d ideally like the SQL Server optimizer to do with common table expressions, but as Lukas notes, it doesn’t, so user beware.
Comments closedShane O’Neill engages in wacky newline misadventures:
GOis a special little guy. It’s not exactly T-SQL. It’s a way of telling the SQL Server Management Studio (SSMS) to send everything before it, from the beginning of the script or the precedingGO, to the SQL Server instance.If you read the documents, the main point to take away is…
A Transact-SQL statement cannot occupy the same line as a GO command. However, the line can contain comments.
The problem turns out to be a little trickier than you’d first imagine. Also, after reading this post, I think it’s lunchtime…
Comments closedTywan Terrell has a listing of various functions built into SQL Server:
SQL Server starting with 2012 ship with a robust set of functions that can be used to make code perform faster, with fewer lines of code. The functions can be used in ETL Process to provide better error handling. A example of this would be the Try_Parse function that allows you to check if a value can be converted.
Another example would be using the FIRST_VALUE() and LAST_VALUE() functions which work against a set of data. These functions are very useful when looking for things like month over month averages and when doing calculations. The table below contain a list of function that are supported starting with 2012 along with some examples of how to use them.
He breaks them down into four categories and provides examples. Functions can bring their own set of problems with query performance, but most of them can be very useful.
Comments closedCommas are all the rage nowadays:
There are a fair number of questions nowadays about returning data from a database in a comma separated string. Sure the application should probably do that but hey, database servers are expensive, why not get some bang for your bucks!
Protip: I use the colon as a separator because sometimes commas will get stuck in the Ethernet cable.
Comments closedAndy Levy has a T-SQL programming breakthrough:
Finally, this week I had a breakthrough. I was working on updating a bunch of data but it was breaking on a small subset of that data. In this case, I was attempting to
JOINtwo tables on fields that should have beenINTs, but in a very small number of cases one side was using a comma-delimited string. The user told me that someone else had done these updates in the past and didn’t encounter the problem I was having (so I knew that it was something i was doing “wrong”), but given that it was only a handful of broken updates she was OK with manually doing the updates (we were scripting it because we were updating potentially tens of thousands of records).I am not OK with manually fixing this in the future. I wanted to know how the other DBA had done it before. I dug into some history and found
CROSS APPLY. My nemesis. I was determined to figure out how to use it this time.
The APPLY operator is extremely powerful in the right set of circumstances. Andy shows the “classic” use case, but there are a number of other uses for the operator.
Comments closed