Cool way to get row counts for all tables in SQL Server DB

...Or other repetitive actions on all tables

CREATE TABLE #counts
(
    table_name varchar(255),
    row_count int
)

EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC
DROP TABLE #counts

--Al-

Some of my Favorite Tech Words

May sound weird, but I've always enjoyed collecting words that sound a bit funny but have real meaning. And I love tossing them into talk or paper. These are some of my favorite ones related to tech and programming.

The classic for beginning programmers is Concatenate, many never having heard the word before going into programming. But others are much more fun.

Canonicalization: Conversion of data that has more than one possible representation into a standard form, know as the canonical form.

Sargability: Comes from "Search ARGument ABLE" meaning the quality of a SQL query which allows the database engined to use indexes. The easiest way to understand this is to see what makes a query un-Sargable such as saying "Upper(ColName) = 'value'" or "ColName Like '%value'".

Orthogonality: Designing such that a small set of component can be combine to get results. It speaks of simplicity, and few exceptions. Orthogonal components are designed with few exceptions. They are symmetrical, consistent, and simple.

Idempotence: In mathematics and computer science mean an operation that can be applied multiple times without changing the result beyond the initial application. In programming means a function or service that when called with the same parameters always returns with the same values.

Memoization: To optimize by storing the results of expensive calculations to be able to return the cached results when called with the same parameters.

Enjoy!

--Al-

Talk: Social Media for Technologists 2014-02-20

Gave a talk for DotNetMiami group on Social Media for Technologists. Here's the followup docs along with a larger list of suggested Twitter follows.

==> Hey, at this Saturday's Code Camp Sherlock Tech is going to be be Social Media Tune-Ups. Stop by and check yourself out for free! http://www.sherlocktech.com/free-social-media-tune-up-at-codecamp/

Me:

About.me: http://about.me/asantaballa

Twitter: @asantaballa http://twitter.com/asantaballa
LinkedIn: http://www.linkedin.com/in/asantaballa
Github: http://github.com/asantaballa
StackOverflow: http://stackoverflow.com/users/995429/asantaballa
Google+: http://plus.google.com/u/0/113077764814491199428/posts

Slides: 

http://issuu.com/asantaballa/docs/social_media_for_technologists

To Follow:

@shanselman - Scott Hanselman
@BrentO - Brent Ozar
@John_Papa
@scottgu - Scott Guthrie
@Jorriss - Richie Rump
@cecilphillip
@wildturtle21 - Sam Abraham
@dvee81 - Dave Nicolas
@janierdavila
@jstawski - Jonas Stawski
@dotNetMiami


Hope to get stuff from y'all on Twitter soon!

--Al-

Disorderly Conduct

A friend recently got burned when a report he had written “suddenly” started coming out wrong in production. The data was good, but the report was no longer in the original sequence. He came to ask me if I had any idea what it could be and without looking at a line of his code I told him he did not have an Order By clause. He started explaining why it should still come out in order and I stopped him and said, “Whenever you are expecting an order from SQL, specify it explicitly.” He started explaining that the report had been working and he thought it was related to a SQL upgrade, not his code. I said again “Whenever you are expecting an order from SQL, specify it explicitly.” I see many SQL developers who find it hard to really, really swallow this. And I see many SQL developers burned when “something changes”.

Think of the SQL statement as a contract between you and the database engine. If the engine accepts your statement the contract specifies what is expected of the result. Everything specified in the statement must be adhered to. And everything that is not specified is up for grabs. Put another way for this situation, if you don't have an Order By in your statement the database is engine is completely free to deliver the results to you in any arbitrary order it chooses. The reason it will choose for ordering are its own and it does not care about what you might think would be an obvious order.

Where I think developers get screwed up is that sometimes it does seem as though you can in fact predict the order of a result set without having an Order By. For example, if you do a Group By on FieldA it “makes sense” that the grouped results come back in ascending FieldA order. If you select from the database with an Order By into a temporary table, then select from that temporary table without an Order By, it “makes sense” that the second result set comes back in the same order as the original result set. If you select from a small table with no Where clause it “makes sense” that the results come back in primary key sequence.

In all the examples above the outcome probably matches the expected and this leads you to believe that it works. But in all those cases there is no “contract” that you've made with SQL for that. It can change it's mind at any time and it's not “wrong” in doing so, it's not “acting weird”.

Think of it from the viewpoint of the database engine. You've made a request. In that request, you've specified certain requirements. Those requirements it has to honor. But now anything you have not specified, it is free to do as it wishes. “As it wishes” generally translates to however it can do it as fast as possible and with as few resources as possible.

Some of the things I've seen change the order of a result set unexpectedly over the years.

1. Changing indexes on a table.
2. Updating statistics in SQL Server.
3. DB tuning changes.
4. Changes in table partition structure.
5. DB updates.

Why? Well for instance, if a result set was sequenced in the order of an index used for access and a new index was introduced, the engine may decide that new index is more advantageous and now your results come in the native order of the new index. If the engine needs to store results in a temporary table as part of its execution plan, it may find it advantageous to store the entries in a certain structure and a DB update may change that algorithm.

The why's are fascinating and worth reviewing with a DBA. But consider that an exercise in learning. You don't want to do this study in production with angry users. The rule is simple. If you’re expecting a specific sequence to your result set, always specify the Order By.

Genesis in JCL

Back in the Dark Ages of computing I came across a great piece of geek humor. This was the real Dark Ages and the real geekiness. The creations of the world described in IBM MVS (mainframe) JCL. Back in those days we didn't have the Internet. Sharing meant xeroxing paper copies and passing on to friends.

I lost mine.

So every once in a while I looked for it on the Internet but could never find it. Mentioned to a new friend and in a few minutes he found it. I was convinced it was called Genesis JCL, but he looked for creation instead.

So here it is for your enjoyment and for posterity.

http://www.textfiles.com/humor/COMPUTER/creation.jcl

Upload by Kevin Green.... [LUCIFER] on Apple/Boston BBS 617-275-9422
Re-created from Boston Univ. computer network
I take no credit for creation 
I take no responsibilty for what does or doesn't offend
I only provide this file for your perusal

//CREATION JOB (0000,EARTH),'GOD',PRTY=13,RESTART=EDEN,TIME=1440
//*
/*SETUP           DISK=PRIMAL
//*
//JOBLIB   DD     DDN=UNIVERSE,DISP=(OLD,KEEP)
//*        
//* FOR EXTENDED DOCUMENTATION ON THIS JOB REFER TO MEMBER
//* BOOK.ONE,CHAPTER ONE OF SYSDOC FILE WORD.OF.GOD
//* TAMPER WITH THIS JOB AT YOUR OWN EXTREME RISK!
//*
//DAYONE   EXEC   PGM=IEBGENER
//VOID     DD     DSN=CHAOS
//DAY      DD     DSN=LIGHT
//NIGHT    DD     DSN=DARKNESS
//SYSIN    DD     *
   LET THERE BE LIGHT, AND LET THERE DARKNESS BE A SEPERATE DATASET!
/*
//DAYTWO   EXEC   PGM=SORT
//FIRM     DD     DSN=HEAVEN,DCB=DSORG=PO
//WATERS1  DD     DSN=HEAVEN(ABOVE)
//WATERS2  DD     DSN=HEAVEN(BELOW)
//SYSIN    DD     *
   LET THE FIRMAMENT, CALLED HEAVEN, PARTITION THE WATERS!
/*
//DAYTHREE EXEC   PGM=MERGE
//MERGEIN  DD     DSN=BELOW
//MERGEOUT DD     DSN=DRY.LAND
//EARTH    DD     DSN=DRY.LANE
//BELOW    DD     DSN=SEAS
//FLORA    DD     DSN=GRASSES.HERBES
//         DD     DSN=FRUIT.TREES
//SYSIN    DD     *
   SET THE EART CONCATENATE GRASS AND TREES
/*
//DAYFOUR  EXEC   PGM=IEBUPDTE
//SUN      DD     DSN=LIGHT
//MOON     DD     DSN=LIGHT
//STARS    DD     DSN=LIGHT
//SYSIN    SS     *
    LET THERE BE PANEL LIGHTS TO INDICATE THE STATUS OF THE UNIVERSE!
/*
//DAYFIVE  EXEC   PGM=IEHMOVE
//WHALES   DD     DSN=MOVING.CREATURE
//FOWL     DD     DSN=MOVING.CREATURE
//SYSIN    DD     *
    BE FRUITFUL AND MULTIPLY UNTIL OVERFLOW!
/* 
//DAYSIX   EXEC   PGM=IEBCOPY
//MAN      DD     DSN=GOD.IMAGE
//MALE     DD     DSN=MAN(ADAM)
//FEMALE   DD     DSN=MAN(EVE)
//SYSABEND DD     DSN=ETERNAL.HELL
//SYSIN    DD     *
    ALL THE DATASETS NOW EXIST. LET MAN TEND THE CONSOLE AND REPLENISH
    THE LINE PRINTER AND KEEP HIS MITTS OUT OF THE MICROCODE!
/*
//DAYSEVEN EXEC   PGM=ENTROPY,COND=((IT IS GOOD,DAYSIX),ONLY)
//TIME     DD     DSN=ETERNITY
//SYSIND   DD     *
    NOW LET THE SYSTEM RUN, THE PANEL LIGHTS TWINKLE, AND THE 
    DISKS FILL WITH DATA!
/*
//

--Al-

LibreOffice Presentation 3/7/2013

Follow up materials for LibreOffice presentation at Planet Linux Caffe to the Coral Gables Open Source User Group.

Thanks to all who attended!

To download: http://www.libreoffice.org/

The presentation slides: http://www.slideshare.net/asantaballa/libre-office-intro

Doing Twilio demo

Ever have trouble figuring out how to approach an application that needs very custom SMS? I'm going to be talking Thursday, January 17, 2013 at 6:30 PM for the dotNet Miami Group  at Planet Linux Caffe on how to use Twilio to set up a small SMS voting applciation. 

See how to use Twilio to implement an SMS application with ease, low cost, and almost not setup. Twilio is completely cloud based and uses web and REST interfaces to support programmatic access to SMS and telephony. The front end administration is easy to set up and use, but allows growth to large scale applications.

But wait, that's not all! Janier Davila will also give an introduction to Git and GitHub and show us why you should be using it.

Come by and get your geek on!

--Al-