Largest FREE Microsoft eBook Giveaway

sqlwayne news

(according to Eric Ligman, director of Business & Sales Operations)

I just downloaded 78 books and PDFs on SQL Server, Power Shell, Windows Server, Azure, development, and a lot of other topics that caught my eye.  There’s no indication as to how long the giveaway will be going on, so get ’em while the getting is good.

Words of warning and advice: many of the offerings are only as PDF, some are just DOC, others are also as MOBI or EPUB.  So don’t expect to be able to load everything to your iPad or Kindle and be able to have lovely reflowable and resizable text.  Obviously it’s easy to run the DOCs through Calibre or other programs and convert them to your favorite ebook format, not so easy with the PDFs.  One major problem for me is that some of the links just give you a file with an ISBN#.epub, or a really bad file name, so personally I’d recommend doing a copy of the book title as it appears in the giveaway blog post, then a right click Save As on the link, and paste in the nicely formatted title.

https://blogs.msdn.microsoft.com/mssmallbiz/2017/07/11/largest-free-microsoft-ebook-giveaway-im-giving-away-millions-of-free-microsoft-ebooks-again-including-windows-10-office-365-office-2016-power-bi-azure-windows-8-1-office-2013-sharepo/

Interested in designing computer games? Here’s some books!

sqlwayne Uncategorized

Humble Bundle is selling 17 ebooks worth almost $500 on programming and game design on multiple platforms for the stunning price of $15.  Technologies include: Android, Swift, Unity3D, RPGMaker, Java in LibGDX, and HTML5!  As usual, they’re available in epub, Kindle, and PDF formats and are not encumbered with DRM, something that cannot be said for most computer games. 🙂

Unfortunately, the bundle goes away in about 35 hours from this post.  Sorry for the lateness of this notification, December has been a pretty rough month.

I hope you all had a wonderful holiday and here’s to a better ’17!  Let’s make gaming great again! 🙂

https://www.humblebundle.com/books/code-your-own-games-book-bundle

Dennis Ritchie, RIP

sqlwayne Uncategorized

Well.  It turns out that Dennis Ritchie passed away five years ago.  In an article on Slashdot, they describe the anniversary of the death of famous people sometimes launches a ‘second death’ response, making it look like it just happened.  I somehow noticed that the reported date of his passing was different between the article and his Wikipedia entry but failed to look at the year.  *facepalm*

The Slashdot summary:

Dennis Ritchie invented the “C” programming language, so a second round of honors comes as no surprise. Although five years ago he passed away, some confusion over a tweet started the social media avalanche known as “second death syndrome”. The problem, especially if you look at it from Ritchie’s perspective, is that he’s been dead for five years — exactly five years. That time gap seems to have escaped some of the biggest names in tech, including Google CEO Sundar Pichai, who late Wednesday tweeted out Wired’s five-year-old obituary on Ritchie, thanking him for his “immense contributions.” Om Malik, a partner at True Ventures and the founder of tech site GigaOm, retweeted Pichai’s tribute before soon recognizing his mistake and tweeting an apology for “adding to the confusion and noise.” Craig Newmark, founder of the popular online bulletin board Craigslist, also paid his respects, saying, “this guy made a huge contribution to the world.”

https://developers.slashdot.org/story/16/10/13/199235/inventor-of-c-dennis-ritchie-honored-with-second-death

I learned today that Dennis Ritchie passed away on October 8 at the age of 70 (Wikipedia says he died 10/12, I don’t know which date is correct, it doesn’t matter much).  Dennis was one of the gods of the early days of modern computing, being a major part of the team that created Unix and C.  The most respected C system used to be known as K&R C, the initials being for Brian Kernighan and Dennis Ritchie.

In many ways he was more fundamental to modern computing than Gates or Jobs as C and Unix are core to so many things today.  While I am not a fan of C as I prefer higher level languages, I definitely respect Unix and Linux.

http://arstechnica.com/business/2011/10/dennis-ritchie-the-giant-whose-shoulders-we-stand-on/

Is what is new actually old, just relabeled?

sqlwayne Uncategorized

My first job doing actual database programming was around 30 years ago, writing a dBase III+ system that sucked information from a commercial time charge billing system and produced much nicer and richer reports.

That’s ETL. The E and L were easy, the T took a bit of interesting work (setting the third bit of the last byte of a number to 1 to represent the field being a negative value?!). But when did the term start being bandied about in SQL Server as common nomenclature? At a guess, I’d say when Data Transformation Services became a part of the package, whenever that was.

Then a decade or so later, I wrote a system in Microsoft Access (probably 2.0) that allowed multiple users to coordinate individual databases so that each user could see other people’s information, but couldn’t change it.

That was sneaker net replication. Everything was copied on to floppies (some data sets were so minuscule back then!) and it worked reliably. I have no idea when replication entered the database lexicon, but I certainly didn’t use the term back then. We had SQL Server, but this group was isolated from our network because of reasons, so I had to provide the service through some unconventional means. And it worked.

So what’s next? What are we doing today, that we think is a one-off process, which will become a TLA or known term in a couple of decades? I have no idea, I hope to be fully retired and enjoying the German countryside then.

PowerShell on OS-X (or MacOS, whatever) and Linux, and edX Training

sqlwayne news

First up, Ars Technica announced (along with many others, I’m sure) that Microsoft is releasing PowerShell to the Linux and Mac OS-X operating systems.  It’s an interesting development, especially when you think about running SQL Server on Linux at some point in the future.  I wonder if this heralds a future release of SQL Server for MacOS?  Since it’s available for some flavors of Linux, and MacOS is based on Unix, it seems to me a natural fit.

Next, edX.  LOTS of people know of edX and many have taken courses on it.  A somewhat smaller number have actually completed courses on it, but that’s another story.  This morning I completed Querying With Transact-SQL, DAT201x. It’s an eleven-part course with videos, lab problems, quizzes, and a final exam.  I wasn’t able to work on it the first two or three weeks after it launched, so I crammed the entire course in to about two weeks.  And I learned a bunch of new things, very interesting stuff.  I’ve been working with SQL Server for 25 years, since the initial 4.21a release on Lan Manager.  But we tend to specialize and don’t really use all of the language.  If this describes you, then you might benefit from this course.  It uses syntax that I knew of but that I had never used as it was not needed at the places that I had worked.  And through learning about those and doing the lab exercises, I picked up a lot of information.

The final exam was 21 questions.  Your 11 quizzes make up 50% of the grade, the final the remainder.  You don’t need to do the labs, but if you’re wanting to learn new things, then why not do them?!  You don’t need SQL Server installed on your system as they use AdventureWorksLT(v12) on Azure (a VERY small version of the full-blown AW database), which you can link up to SSMS on any Windows PC.  Obviously you will need an Azure account, and while there are free accounts available, you’ll still need to provide a credit card to register for your free account.  There are almost 90(?!) free courses offered by Microsoft available right now, you’ll probably find something to your taste.

I have a couple of complaints about DAT201X, and they’re probably true of any course like these.  First, I blew a question on the final because it required two responses and I only clicked one (the quizzes and final were all multiple-guess or select something to fill in the blank).  If the software requires X > 1 responses, it shouldn’t let you click the Check button unless that many options are selected.  Next, some of the problems were, in my humble opinion, nonsensical.  In several cases you were processing information that should have been handled by a reporting system.  And a complaint about Azure that caused me some major headaches and was responsible for delaying me working on the class: I followed the steps and I could not connect to my server.  Eventually I decided to strip everything out and start again from scratch, and I stumbled upon a log that showed one step failed.  Of course it’s a step that wasn’t obvious: had the obvious steps failed, I would have easily seen the problem.  Azure provided no alert or email to tell me that the step failed.  Fortunately the second time that I created everything went just fine.

I expect my next edX courses will be Implementing ETL with SQL Server Integration Services (SSIS) (DAT217x) and Delivering a Relational Data Warehouse (DAT216x), but not at the same time.  My SSIS skills vary between pathetic and non-existent, so I really need to learn that system.  I was pretty good at ETL with DTS, but that system no longer exists and you have to do it all in SSIS.  And while I have little interest in data warehousing, it is a skill in demand and it can’t hurt to learn it.  There are some Sharepoint classes that might be useful, and I’m hoping that some Reporting Services classes will be appearing soon.

The courses are free, you can buy a certificate upon successful completion for $49.  And you don’t have to commit to the certificate until after you finish the final, as long as you do it before the class end date, so if something happens to your real-life availability, you haven’t committed to the $50.

Updating Statistics

sqlwayne Uncategorized

I read a post recently saying that updated statistics could prove more important to performance than optimizing indexes.  Cool stuff, and possibly much lighter on your server load.  If I can find the article that I read, I’ll post a link and/or an excerpt.

Here’s a couple of scripts using the undocumented sp_msforeachtable stored procedure, provided by MS since time began or shortly thereafter.

First, view the date that statistics were updated for every table:

sp_msforeachtable @command1="SELECT name, STATS_DATE(object_id, stats_id) as StatsDate FROM sys.stats 
WHERE object_id = OBJECT_ID('?')"

The results are interesting, you have one result pane for every table with one row for every index.  If your table name is part of the index name, identifying what is what shouldn’t be difficult.  But what was really interesting was the groups of indexes, including PKs, that the StatsDate field was null even after running an Update Statistics.  It took a minute for me to realize that those tables currently had no records.  *facepalm*

That ‘?’ functions as a variable substitution for the table name within the SP.  Its companion undocumented SP, sp_msforeachdatabase, has a similar wildcard.  One problem with sp_msforeachtable is that @command1 only accepts 128 characters, this is probably a carryover of the days that the maximum length of a char/varchar data type was 256 characters, with nchar/nvarchar taking half that.  Now in more modern SQL, char/varchar has a max length of 4,000 characters, so it’s not quite the problem that it used to be.  And you can find numerous rewrites of these stored procedures that do all sorts of spiffy things, probably also including bypassing this 128 character limit.

Next, update EVERY table’s statistics.  This is fine and pretty fast if your system is pretty small — if your system is pretty big, I hope you know what you’re doing before doing something like this!

sp_msforeachtable @command1="UPDATE STATISTICS ?"

Should you update statistics for every table every night, or multiple times every day?  The answer to that is left to the reader to research and decide what is appropriate to their installation(s).

Using LEN() Wrong

sqlwayne SQL Server, Uncategorized

Kenneth Fisher had a recent blog post on a question that was posted to Stack Overflow.  The interesting bit was someone using the LEN() function to determine the length of a float with odd results.

Personally, it had never occurred to me to use the LEN() function against a number.  It’s a string manipulation function, and that’s it.

Here’s a little test harness:

BEGIN TRANSACTION;

-- https://sqlstudies.com/2016/06/02/using-len-wrong/
-- Kenneth Fisher
CREATE TABLE #temp (MyFloat FLOAT, MyStr VARCHAR(50));

INSERT INTO #temp VALUES (12345,'12345'),(123456789,'123456789');

SELECT MyFloat, 
    LEN(MyFloat) AS MyFloatLen, 
    DATALENGTH(MyFloat) AS MyFloatDataLen,
    MyStr, 
    LEN(MyStr) AS MyStrLen, DATALENGTH(MyStr) AS MyStrDataLen,
    LEN(LTRIM(RTRIM(CAST(MyFloat AS VARCHAR)))) AS MyFloatTrimmedLen,
    LEN(LTRIM(RTRIM(CAST(MyStr AS VARCHAR)))) AS MyStrTrimmedLen
FROM #temp;

ROLLBACK;

The results:

MyFloat    MyFloatLen  MyFloatDataLen MyStr      MyStrLen  MyStrDataLen
---------- ----------- -------------- ---------- --------- ------------
12345      5           8              12345      5         5 
123456789  12          8              123456789  9         9

(2 row(s) affected)

If we look at the execution plan, we see a hidden implicit conversion of applying the LEN() function to the float.  These implicit conversions can really hurt code execution: take a look at Grant Fritchey’s SQL Server Execution Plans, published by Red Gate.

Curious stuff.