 | Mike Verhagen on geerlingguy/my-backup-plan: Ho… |
 | Attila Kovacs on docs.embarcadero.com unreachab… |
 | David Blue on MacOS: converting a man page t… |
 | jpluimers on How do I pretty-print JSON in… |
| jpluimers on Delphi 10.2 Tokyo Godzilla ISO… |
Posted by jpluimers on 2014/04/08
Some great venn diagrams of SQL JOINs via Data Visualization – Google+ of which I wish they were PNG:
Thanks to that link, I found the original article and images by C.L. Moffat!
Click on the image to view a larger version.
–jeroen Read the rest of this entry »
Posted in Access, Database Development, DB2, Development, Firebird, InterBase, MySQL, OracleDB, PostgreSQL, SQL, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 7 | Leave a Comment »
Posted by jpluimers on 2014/03/19
Funny way to learn something new:
I hardly use CAST and CONVERT (Transact-SQL), and when I do, it is the standard SQL-92 way.
But a while ago, I came across some code like this:
CONVERT(DATETIME, "31/12/2013", 105);
and wondered what the 105 was.
And it appeared to convert from the Italian date format to DateTime. And that it has been there since at least SQL Server 2000, probably earlier.
Not sure why the passed slashes (/) in stead of dashes (-) as separators though.
There are styles for these groups of conversions:
Never to old to learn something new (:
–jeroen
Posted in Database Development, Development, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 | Leave a Comment »
Posted by jpluimers on 2014/02/04
I’m sure there are many organizations that only upgrade things until they absolutely have to (i.e. long after mainstream support has ended, often even after extended support has ended). This was from last year: upgrading away from SQL Server 2000 just before extended support ended. While migrating a bunch of applications we inherited from SQL Server 2000 to SQL Server 2008 R2, I came across an ORDER BY style that failed. The queries are generated by an kind of SQL generation layer, so not easy to change. the main questions were:
First two possible fixes, then the full stack overflow question I posted about the migration.
Aaron Bertrand very quickly posted two fixes, which I paraphrased and extended. Read the rest of this entry »
Posted in Database Development, Development, SQL Server, SQL Server 2000, SQL Server 2008 R2 | Leave a Comment »
Posted by jpluimers on 2014/01/14
Last year, I had a very odd project at a client.
Their monitoring software was quite odd, and there was no time to create/test/implement a module for it doing SQL query performance measurement any better.
The odd software had two ways of looking at queries:
Don’t ask why it was that way, the monitor was hysterically grown.
So below is a small query script that does what the odd monitoring software can do: provide a select with rows indicating the query response time.
What is does is insert into the #sma temporary table a number of records depending on the query duration.
The partition here is 1 record per 125 milliseconds, aiming for four partitions (green, yellow, orange, red) in half a second.
Note the maximum accuracy is about 3.3 milliseconds.
The script is based on these SQL server features:
I might add a try/catch to fake a finally in case the #sma insert fails. Read the rest of this entry »
Posted in Database Development, Development, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 | Leave a Comment »
Posted by jpluimers on 2013/12/24
This has bitten me so many times, so I’m glad I found the below question/answers on StackOverflow.
When you perform calculations in SQL Server involving numeric literals, you have to take into account which precision you want your result to be, and CAST/CONVERT the literals accordingly.
The reason is condensed to this statement by Lieven Keersmaekers:
SQL Server uses the smallest possible datatype.
He follows with examples to view the actual representation of a literal/expression using SQL_VARIANT_PROPERTY (which has been there since at least SQL Server 2000).
SELECT SQL_VARIANT_PROPERTY(1.0, 'BaseType') SELECT SQL_VARIANT_PROPERTY(1.0, 'Precision') SELECT SQL_VARIANT_PROPERTY(1.0, 'Scale') SELECT SQL_VARIANT_PROPERTY(1.0, 'TotalBytes')
Posted in Algorithms, Database Development, Development, Floating point handling, Software Development, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 7 | Leave a Comment »
The Wiert Corner - irregular stream of stuff 