How to translate T-SQL Datetime2 to Float or Real

The new Datetime2 data type introduced in Microsoft SQL Server 2008 has several advantages over the old Datetime type. One of them is precision in 100 nanoseconds rather than coarse milliseconds, another is that is has a larger date range. It has disadvantages, too, like a difficulty in translating it into numerical values. It was a classic hack to CONVERT/CAST a Datetime into a Float in order to get a numerical value that you could manipulate (like convert it to an integer to get the date without time, which is now accomplished by converting it to Date, another type introduced in SQL Server 2008). There are many reasons why one needs to translate a datetime into a numerical value, I don't get into that here. So here is how to convert a Datetime2 value into a Float.

First solution:

DATEDIFF(SECOND,{d '1970-01-01'}, @Time)+DATEPART(nanosecond,@Time)/1.0E+9

- returns a value in seconds with nanosecond precision from the beginning of the year 1970. Advantage: simple to use and understand. Disadvantage: not similar to the conversion from Datetime to Float.

Second solution:

DATEDIFF(DAY,{d '1900-01-01'}, @Time)+DATEPART(HOUR,@Time)/24.0+DATEPART(MINUTE,@Time)/(24.0*60)+DATEPART(SECOND,@Time)/(24.0*60*60)+DATEPART(nanosecond,@Time)/(24.0*60*60*1.0E+9)

- returns a value that is similar to the float conversion of a datetime. Advantage: doesn't lose precision like converting to a Datetime and then to Float. Disadvantage: look at the length of that!

Final solution:

25567+(DATEDIFF(SECOND,{d '1970-01-01'}, @Time)+DATEPART(nanosecond,@Time)/1.0E+9)/86400.0

- combines the two solutions above. It easy to read and understand. It computes the number of seconds with nanoseconds from 1970, divides by 86400 to get the number of days and adds 25567, which is the number of days between 1900 and 1970.

Read More

The story of the LEFT JOIN in T-SQL (and why I learned to love the NOT EXISTS)

As a software developer - and by that I mean someone writing programs in C#, Javascript and so on, and occasionally using databases when something needs to be stored somewhere - I have an instinctual fear of the Arrow Anti-pattern. Therefore I really dislike stuff like NOT EXISTS(SELECT 1 FROM Something). However, recent tests have convinced me that this is the best solution for testing for existing records. I am not going to reinvent the wheel; here are some wonderful links regarding this, after which I will summarize:

• Left outer join vs NOT EXISTS
• SQL performance on LEFT OUTER JOIN vs NOT EXISTS
• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server

Let's say you want to insert in a table all records from another source that do not already exist in the table. You have several options, but the most commonly used are:

SELECT *
FROM SourceTable
LEFT JOIN DestinationTable
ON SomeCondition

WHERE DestinationTable.Id IS NULL
and

SELECT *
FROM SourceTable
WHERE NOT EXIST(SELECT 1 FROM DestinationTable WHERE SomeCondition)

Personally I prefer the first version, for readability reasons as well as having listened to the mantra "Never do selects in selects" for all my life. However, it becomes apparent that the second version is a lot more efficient. The simple reason is that for the first example Microsoft SQL Server will first join the two tables in memory, then filter. If you have multiple combinations of records that satisfy the condition this will result in some huge memory and CPU usage, especially if you have no indexes defined and, sometimes, because you have some indexes defined. The second option uses one of the few methods guaranteed to exit, NOT EXISTS, which immediately invalidates a record at the first match.

Other options involve using the EXCEPT or INTERSECT operations in SQL, but they are not really helping. Intersecting ids, for example, then inner joining with SourceTable works, but it is somewhere in between the two solutions above and it looks like crap as well. Join hints don't help either.

Read More

How to OUTPUT columns that you did not INSERT in T-SQL

The OUTPUT clause is a very useful tool in Microsoft SQL, allowing for getting automatically inserted columns in the same command as the INSERT. Imagine you have a table with an identity column and you need the generated ids as you insert new records. It would look like this:

CREATE TABLE MyTable 
  ( 
     Id    INT PRIMARY KEY IDENTITY(1, 1), 
     Value NVARCHAR(100) 
  ) 

CREATE TABLE AnotherTable 
  ( 
     Value               NVARCHAR(100), 
     AnotherValue        NVARCHAR(100), 
     SomeConditionIsTrue BIT 
  ) 

go 

CREATE TABLE #ids 
  ( 
     Id INT ,
     AnotherValue        NVARCHAR(100)
  ) 

INSERT INTO MyTable (Value) 
    OUTPUT inserted.Id INTO #ids (id) 
SELECT Value 
    FROM   AnotherTable 
WHERE  SomeConditionIsTrue = 1 

-- Do something with the inserted Ids 

However, what do you do if you want to also insert the column AnotherValue to the #ids table? Something like this does not work:

INSERT INTO MyTable (Value) 
    OUTPUT inserted.Id,AnotherTable.AnotherValue INTO #ids (id,AnotherValue) 
SELECT Value 
    FROM   AnotherTable 
WHERE  SomeConditionIsTrue = 1 

Enter the often ignored MERGE, which can help us translate the query above into:

MERGE INTO MyTable USING (
    SELECT Value , AnotherValue
        FROM   AnotherTable
    WHERE  SomeConditionIsTrue = 1 
) t ON 1=0 --FALSE
WHEN NOT MATCHED THEN
  INSERT (Value) VALUES (t.Value)
  OUTPUT Inserted.Id, t.AnotherValue INTO #ids (Id, AnotherValue);

Note the 1=0 condition so that the merge never "matches" and how the select from the first query now contains all the columns needed to be output, even if only some of them are inserted in the insert table.

This post was prompted by a StackOverflow answer that, as great as it was, didn't make it clear what to do when you get your values from a more complicated select. The answer is simple: put it all in the 'using' table.

Read More

No Longer Human (Ningen Shikkaku), by Osamu Dazai

I read this short novel from start to end in under a day. Osamu Dazai writes from the point of view of a sociopathic young man who cannot seem to understand the human condition and fears all people around him, mostly because he expects to be found out at every moment. The title of the book can be translated in several ways, the English one relates to the protagonist's feelings of losing one's humanity, while the literal translation reads as "disqualified from being human", implying a societal judgement. Imagine a Japanese version of The Stranger, by Albert Camus, and you get a good picture of the plot and feel of the book. Both books were written in the same period, more or less, but while Camus probably imagined the character, many believe Dazai was talking about himself - he committed suicide soon after.

No Longer Human is the second best rated Japanese book and was adapted in movie and manga. It is difficult to imagine those being better than the dry accounting of the inner turmoil of the character, starting as a little boy who devises "clowning" as a method of passing the test of humanity, outwardly fun and good natured and inwardly terrified of being discovered as a fraud and punished by the society of strange human beings that he cannot understand or empathize with. I highly recommend it.

Read More

Daemon, by Daniel Suarez

I have been watching horror movies since I was six and read books of all sort through the years, but rarely have I seen something so truly scary as Daemon. Daniel Suarez manages to convey terror not by upgrading the villain, but by making it mundane. The daemon is not an all knowing Artificial Intelligence that takes over the world, but a stupid game engine run by a logic tree. The ease with which something like this could be created makes the book truly terrifying, particularly for me, who has actually thought of the weakness of humans when faced with decisions and pondered a world where machines make the decisions not because they want to rule us, but because we don't want to choose.

But there is more to this book than its subject. It is actually very well written and that is remarkable considering it is Suarez' first book. I will read the sequel to Daemon, Freedom™ as soon as I can. I loved the attention to detail, not a descriptive boring series of useless trivia, but a close focus on what makes people tick and how technology falls into place to fill the gaps that our failings leave. On the cover of the new book that Daniel Suarez wrote there is a quote that I feel is totally true: he is a true heir to Michael Crichton.

Read More

Bastard!! - a funny six episode anime

Bastard!! is an adaptation of the manga with the same name. The manga itself is ongoing, but very slowly. At the moment of the writing it had 138 chapters. The genre of it is magical fights in an action comedy kind of style. Bob Samurai has a video review of it.

For myself I have to say that I had fun watching it, in a mindless "I come from work and I don't feel like doing anything" kind of way, but it wasn't that special in plot, animation or feeling. The "anti-hero" is actually the typical hero that does incredible good deeds for the love of women and the biggest source of humor are the few lines peppered throughout the episodes that break the fourth wall. Stuff like "What would have been the purpose of defeating that guy when we were off screen" or "a handsome hero like myself couldn't possible lose to one as ugly as you". The manga is a little bit more about the scoundrel nature of the main character - as it should be, there are 70 chapters (the Host of Shadows) covered by mere 6 episodes of the OVA - but it is also rather different from the anime: more story detail, more types of magic, etc. Probably the OVA, as quick dirty fun as it was, is not a very good one, since it relays only bits and pieces of the manga.

One can watch the anime at AnimeDreaming, read the manga at MangaHere and watch BobSamurai's video review on YouTube.

Read More

Creatures of the Abyss, by Murray Leinster

Another book that can easily be found in audio format on Librivox and YouTube, Creatures of the Abyss (also known as The Listeners), by Murray Leinster, is a slow mid 20th century sci-fi that reads as a cross between Jules Verne, H. P. Lovecraft and one of those books about people drinking and falling in love on boats in South America. More Verne, though.

The thing that made me continue listening to it was its way of depicting the mentality from back then. Written in 1961, it tells a story of people who, faced with extraordinary circumstances, first evade formulating a theory in their own head, for fear of contravening their own set view of the world, then - forced by events - they do allow themselves to formulate a theory, but keep it to themselves for fear of ridicule, even when they see other people considering the same things, then they proceed to test those theories by themselves and only then share them with others. Compared with the modern culture of sharing half formed thoughts before they can constitute complete phrases, it is quite different. It is also fun to read about people that think Venus is a large ocean planet, as is Jupiter, with a gravity four times that of Earth.

However, while it was interesting in a sociological way and good as a background for other activities, its slow pace might feel excruciating for the casual reader. More than half of it is more about boats and sailing and catching fish. The science fiction part is slowly creeping into the story and the climax is in the last chapter alone. Maybe my association of the book with Lovecraft is strained, as the only commonality is touching on tentacled abyssal creatures that might appear disturbing to human sensibilities and certainly the elements of horror are very rare in Creatures of the Abyss. The book does feel more real, though, as it goes through this slow process of examination of evidence and formulating hypotheses and testing them before jumping to conclusions. It depicts the beginning of the modern era of scientific thought, back when it was respectable and desirable to be thinking like that.

Bottom line: Slow paced, but very well written, you should at least try it, since it is so readily available. You can even listen to it right here, on this post.

Read More