Ike Ellis' Blog

Woot Makes Me Think

2011-12-12T06:20:00.000-08:00

I got an email from Woot today. At the bottom they had this silly graphic:

I've been minimalizing a lot of stuff in my life lately: Possessions, bills, work, Facebook friends (yeah, I got rid of people I didn't really know and it simplified my Facebook experience.) I'm enjoying freedom from life's unnecessary burdens, and it's freeing up my time so I can focus on the things that really matter to me.

I never thought about freedom from knowledge before, but it really makes sense. Freedom from knowing what other people expect me to know. I don't need to know the meaning of every inside joke. What happens if I never watch the most popular TV shows and I don't get references? Will my life be less meaningful? Will I be less happy? What happens if I stop watching the news? Will my vote count less? Will I be less of an American?

I really don't have the answers to these questions, but the woot monkeys made me think about it. I'll let you know if I land on anything brilliant.

Planning Into Minimalism

2011-12-12T06:07:00.000-08:00

I'm on day 2 of my journey into Minimalism. I'm planning. I didn't buy a calendar like they recommend, but instead used Microsoft Outlook. I'm thinking Outlook might be the problem. That software is expensive. I'm planning my digital minimalist life. I have a lot of monthly, quarterly, and yearly fees for digital services, when free ones are just as good or nearly just as good. I think I'll spend my free time during Christmas transitioning to the free services, like using GMail instead of Outlook/Exchange.

I pay for source control, when I could just host it myself for free.

I pay for web hosting from several different companies, but I should consolidate and make my expenses cheaper.

I canceled GOTOMeeting (which I use primarily to remote control computers or show someone my screen), because I can just use mikogo for free.

I'm going to comb over my cell phone bill and cancel unneeded phones and services. My phone bill is typically $330 a month. YES, YOU READ THAT RIGHT! I know, I know...it's embarrassing. But I have three sons. Little buggers are expensive.

My Must List for the Next 90 Days

2011-12-11T15:59:00.000-08:00

I read Day 1 of the journey from The Minimalist Blog. They mention that I need to turn shoulds into musts and I totally agree.

This is who I am for the next 90 days.

I must do P90X first thing in the morning.

I must write 1000 words a day on my book.

I must complete projects for work.

I must listen to podcasts to and from work.

I must be available for my sons for surfing, golf, and bonding time.

I must eat healthy for every meal.

I must not watch TV, HULU, etc.

I must not go on the Internet unless it's to research or write.

I must not hold on to my fucking phone like I'm Gollum and it's the goddamn One Ring.

I must watch a technology video every day.

I must make time for making good friends.

New BunkSpeed Site is Amazing!

2011-11-16T12:24:00.001-08:00

Brad Cunningham just showed me the new website for his company, Bunkspeed. This site was created with Javascript and CSS. I'm flabbergasted how seamless the scroll in and scroll out works without losing any graphical detail. Brad and his team did some breathtaking work here and they should be proud.

www.bunkspeed.com

How To Reconcile Belief in God with Morals

2011-11-16T11:28:00.002-08:00

Slides for SQL in the City Presentation on SQL Azure

2011-11-02T11:09:00.000-07:00

Here's the promised slide deck:

Developing on SQL Azure

View more presentations from Ike Ellis

My Interview with SSWUG

2011-10-20T15:05:00.001-07:00

SSWUG just released an interview I did with them back in August. You can see it here:

Thanks, SSWUG! I totally enjoyed the experience and look forward to doing it again

clemens vasters discusses lack of dtc on the azure platform

2011-10-11T11:40:00.000-07:00

clemens vasters responded to complaints on the azure nda list this week. he addresses the lack of a distributed transaction coordinator on the azure platform. i thought his response was interesting, told him that, and he turned it into a public blog post.

he still got skewered by the mvps, but he gets an "a" for effort.

http://blogs.msdn.com/b/clemensv/archive/2011/10/06/achieving-transactional-behavior-with-messaging.aspx

below is my favorite part:

"The grand canonical example for 2PC transactions is a bank account transfer. You debit one account and credit another. These two operations need to succeed or fail together because otherwise you are either creating or destroying money (which is illegal, by the way). So that’s the example that’s very commonly used to illustrate 2PC transactions. The catch is – that’s not how it really works, at all. Getting money from one bank account to another bank account is a fairly complicated affair that touches a ton of other accounts. More importantly, it’s not a synchronous fail-together/success-together scenario. Instead, principles of accounting apply (surprise!). When a transfer is initiated, let’s say in online banking, the transfer is recorded in form of a message for submission into the accounting system and the debit is recorded in the account as a ‘pending’ transaction that affects the displayed balance. From the user’s perspective, the transaction is ’done’, but factually nothing has happened, yet. Eventually, the accounting system will get the message and start performing the transfer, which often causes a cascade of operations, many of them yielding further messages, including booking into clearing accounts and notifying the other bank of the transfer. The principle here is that all progress is forward. If an operation doesn’t work for some technical reason it can be retried once the technical reason is resolved. If operation fails for a business reason, the operation can be aborted – but not by annihilating previous work, but by doing the inverse of previous work. If an account was credited, that credit is annulled with a debit of the same amount. For some types of failed transactions, the ‘inverse’ operation may not be fully symmetric but may result in extra actions like imposing penalty fees. In fact, in accounting, annihilating any work is illegal – ‘delete’ and ‘update’ are a great way to end up in prison."

SQL Quick and Easy Tips - SQL Saturday San Diego

2011-09-19T05:58:00.001-07:00

Here is the slide deck for two of my SQL Saturday presentations. Enjoy!

Quick & Easy SQL Tips

View more presentations from Ike Ellis

Create a Numbers Table with Buckets Using a Recursive CTE

2011-09-06T16:35:00.001-07:00

I needed a numbers table from –400 to 5000. I needed every number to be in a specific bucket for a report. For instance number 55 would be in the bucket "31 – 60".

This code elegantly creates the data for the number ranges (without listing each of them) and for those buckets.

with myNumberCounts as
(
select CAST(-400 as int) NumValue
union all
select NumValue + 1
from mynumberCounts
where numvalue + 1 <= 5000
)
select NumValue
, case
when NumValue <= 30 then '0 - 30'
when NumValue >30 and NumValue <= 60 then '31 - 60'
when NumValue > 60 and NumValue <= 90 then '61 - 90'
when NumValue > 90 and NumValue <= 120 then '91 - 120'
when NumValue > 120 and NumValue <= 150 then '121 - 150'
when NumValue > 150 and NumValue <= 180 then '151 - 180'
when NumValue > 180 and NumValue <= 365 then '181 - 365'
else '365+'
end AS DayBucket
from myNumberCounts
option (maxRecursion 0)

San Diego Code Camp Slides

2011-06-28T13:15:00.001-07:00

Here is the link for the slides for the T-SQL Tips and Tricks presentation at code camp:

http://www.slideshare.net/ellisike/quick-easy-sql-tips

A Bug Using INTERSECT and ORDER BY

2011-05-26T04:46:00.000-07:00

I was teaching a TSQL course to Total Jobs Group in London, UK, and we stumbled on the following bug. This script uses the Northwind sample database on SQL Server 2008 R2.

use northwind

select contactName, Phone
from customers
intersect
select lastname + ', ' + firstname
, homephone
from employees
order by contactName

If you execute this statement with INTERSECT and ORDER BY, you get the following error:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

If you remove the ORDER BY, it works just fine. This is definitely a bug and I found that it was reported on Connect. I'll test this out to see if they've fixed this in Denali.

Find All The Columns in Indexes with SQL Azure

2011-04-21T08:34:00.000-07:00

In SQL Server on-premise, this query is often used to find all the columns that are currently participating in an index:

SELECT OBJECT_NAME(sysindexkeys.id) Table_Name,
            syscolumns.name Column_Name,
            sysindexes.name Index_Name,
    CASE WHEN
                INDEXKEY_PROPERTY(sysindexes.id, sysindexes.indid, sysindexkeys.keyno, 'IsDescending')=0
                THEN 'Ascending'
    ELSE    'Descending'
    END SORT_ORDER
    FROM sysindexkeys
            INNER JOIN syscolumns
            ON sysindexkeys.colid=syscolumns.colid 
        AND sysindexkeys.id=syscolumns.id
            INNER JOIN sysindexes
            ON sysindexkeys.indid=sysindexes.indid 
        AND sysindexkeys.id=sysindexes.id

In SQL Azure, we don't have sysindexes or sysindexkeys. Instead, we use sys.indexes and sys.index_columms. It looks like this:

SELECT OBJECT_NAME(sysindexkeys.OBJECT_ID) Table_Name,
            syscolumns.name Column_Name,
            sysindexes.name Index_Name,
    CASE WHEN is_descending_key = '0' THEN 'Ascending'
    ELSE 'Descending'
    END AS SORT_ORDER,
            type_desc AS indexType
    FROM sys.index_columns sysindexkeys
            INNER JOIN syscolumns
            ON sysindexkeys.index_column_id=syscolumns.colid
        AND sysindexkeys.OBJECT_ID=syscolumns.id
            INNER JOIN sys.indexes sysindexes
            ON sysindexkeys.index_id=sysindexes.index_id
        AND sysindexkeys.OBJECT_ID=sysindexes.OBJECT_ID



Father of the Year Candidate at Desert Code Camp 2011
2011-04-17T18:39:00.000-07:00

As I was walking around the campus at Desert Code Camp last month, a man stopped me.  This guy had tattoos riding up his arm, a flat-brim hat, baggy jeans, and looked like he just walked off the construction site...not your typical code camp attendee.  Trailing him was a skinny 12-year-old boy wearing a pacman t-shirt.

"Excuse me, I have no idea what my son is saying.  Can you please translate?"

His son wanted to go to a specific class.  I found one close enough on the schedule and pointed them on their way.  Later, as I was running around after my own sons, I saw the man walk out of the class his son was in.  He found a chair by the door, sat down, and waited.  As I walked around code camp that day, I saw the same sight over and over again.  This guy was either walking his son to the next class, or waiting outside the door.

I wonder what he was thinking about with all that free time that day.  Perhaps, "why doesn't my boy like to throw the football?"  Or, "why can't we make furniture together?"  I wonder how many super smart kids are born to parents that are not as generous and thoughtful as that man.  I wonder how many brilliant minds the world has lost out on because of bad luck or circumstance.  

On the flip side, maybe one of my sons was meant to be an NFL Quarterback, but we'll never know.  They're stuck in code camp classes.


Outlook 2011 for Mac (OsX) Won't Open New Email Window and Reply All Doesn't Work
2011-03-25T16:47:00.000-07:00
Today, none of my email buttons would work in Outlook.  After some digging, I  found this solution:

http://jimmyblake.com/2010/11/09/microsoft-outlook-2011-not-opening-e-mail-compose-window/

I reinstalled Safari and it finally fixed it.


Database Design Fundamentals - Louis Davidson's SQL Pass 2010 Talk
2011-03-17T19:48:00.000-07:00
I've been watching old SQL Pass talks from last year's conference.  Today I watched Louis Davidson's talk, found here:

http://sqlpass.eventpoint.com/topic/details/AD270S


He is an interesting speaker, who talks funny because he's from Tennessee.  My favorite is the pronounciation of varchar(max) as vare care (max).  That rhymes, by-the-way.

His presentation covers database normalization from first normal form through Boyce/Codd normal form.  He also talks about de-normalization and offers tips and techniques for good design.  Forty-nine minutes into his presentation, he has a brilliant slide:



He says that if you find a lot of parsing and function calls in queries, particularly on the same column over and over again, you have strong evidence of a design that needs to be revisited.  I totally agree.  As soon as he said it, it hit me like a ton of bricks.  If you see parsing functions over and over again, you have opened yourself up to bad data and inconsistant results.  You are overly-relying on report authors and developers to get the parsing formulas right every time they touch that data.  That won't happen.  The database needs to be refactored so that data is easy to retrieve and store.

I've thought about this specifically, but I've never considered it as a good general rule.

Here are links for Louis Davidson:

His Website
His Blog
His Latest Book on Database Design for SQL Server 2008
His Twitter: @SqlDr


The SQL MCM Might Not Be For Me
2011-02-25T10:44:00.000-08:00


I've been thinking a lot about the  Microsoft Certified Master Certification  recently, trying to decide if it's worth it.

On one hand, I really like being identified as a smart guy, and perhaps MCM will help that effort.  I can picture it now - I walk into SQL Pass, standing proud and tall in my scottish kilt, legs hairy, smiling brightly...and I hear hushed whispers of "There's Ike...the Microsoft Certified Master."  Reverence on their faces as I stride into the room...right up to the point where I trip, legs sprawling and they realize I'm wearing that kilt as a true scotsman.

Also, I seem to learn better when I have a goal.  It gives the learning purpose, context, and urgency, which I (and many people) require in order to learn.

On the other hand, my clients do not care about this certification.  I doubt I would ever even mention it.  My clients are not SQL DBAs.  They are corporations and institutions that don't have great SQL knowledge in-house.  Some of them are large software companies that just haven't found the right SQL resource.  They've never heard of MCM and therefore, wouldn't add to my bill rate by a single dollar.  MCM = time-consuming = expensive and <> cost-justified.  Basically, I'd be going against the advice I constantly give my customers.  If it doesn't make you more money, it's not worth it.

My clients employ me for one basic reason.  I'm always concerned about their profitability, efficiency, and productiveness.  Getting better at making them money will make me more money.  I'm unconvinced that MCM helps me achieve that.

Is ego enough of a reason to pursue this?

EDIT - 3/31/2011:  Joe Sack, acting PM for MCM:SQL, has been talking to me about this post for quite some time.  He gave me the details of a study done to prove the value of MCM...the non-ego-driven value.  Here is a link to the study:

http://download.microsoft.com/download/7/9/5/795B3672-1B65-49DE-9180-7B7BEB0E1F52/MCM_Research_and_Evidence.pdf

Looks like my SQL MCM journey is going to begin today.


Webcast for DevelopMentor - Top 5 Things for Developers to Know about SQL Azure
2011-02-15T12:44:00.000-08:00
Thanks for coming, today.  Here's the link for the slide deck:

Top 5 things to know about sql azure for developersView more presentations from Ike Ellis.


One thing I wanted to cover, and didn't is that you can analyze query plans while using SQL Azure.  Here's a photo to jog your memory:



Also, here are the links to the audio portions of the webcast:

Streaming Video (WMV):
http://developmentor.s3.amazonaws.com/webcasts/Top-5-Things-to-Know-About-SQL-Azure-for-Developers-by-Ike-Ellis-Feb-15-2011.wvx

Video Download for WMV:
http://developmentor.s3.amazonaws.com/webcasts/Top-5-Things-to-Know-About-SQL-Azure-for-Developers-by-Ike-Ellis-Feb-15-2011.zip


SSRS 2008 Execution Log - Report Auditing
2011-02-10T12:29:00.001-08:00
I wrote this query to tell me how many times a report gets excecuted, who ran it last, and when it was ran last.  It's a simple, but effective query:


USE reportserver;

WITH LastExecution
AS
(
   SELECT e.ReportID
       ,  MAX(e.timestart) AS LastExecution
       FROM ExecutionLog  e
       GROUP BY e.ReportID
)
, 
ExecutionCount
AS
(
   SELECT e.ReportID
       ,  COUNT(*) AS ExecutionCount
       FROM ExecutionLog  e
       GROUP BY e.ReportID
)
SELECT c.Path
   , c.Name 
   , le.LastExecution 
   , ec.ExecutionCount 
   , e.UserName AS LastExecutingUser
   FROM ExecutionLog e 
   JOIN Catalog c 
       ON e.ReportID = c.ItemID 
   JOIN LastExecution le 
       ON e.ReportID = le.ReportID AND e.TimeStart = le.LastExecution 
   JOIN ExecutionCount ec 
       ON e.ReportID = ec.ReportID; 


 


T-SQL Script: Finding the First Monday of the Month
2011-01-27T16:28:00.000-08:00
I recently had a reporting requirement to find the first Monday of the month.  I used the dateadd/datediff method introduced to me by Itzik Ben-Gan.  This is the single line of code, but it might help to break it down into multiple lines of code.

SELECT DATEADD(wk, DATEDIFF(wk, '20101206', dbo.FormatDateTime( GETDATE(), '[MM]/07/[YYYY]')), '20101206')

1) Working from the inside out, we have this line:

dbo.FormatDateTime(GETDATE(), '[MM]/07/[YYYY]')

This line uses a function that I wrote (or stole off the Internet) called FormatDateTime.  Here's a blog post of mine about it: FormatDateTime Script

This line assumes that the first Monday of the month needs to be in the first seven days.  It chooses the seventh day of the month of the current month.

2)  Next comes this line:

DATEDIFF(wk, '20101206', dbo.FormatDateTime(GETDATE(), '[MM]/07/[YYYY]'))

This line chooses an anchor date that is the first Monday of a chosen month.  12/6/2010 was the first Monday of December 2010.  It then takes that seventh day of the current month and finds out how many weeks (wk) are between 12/6/2010 and the seventh of the current month.  If today is January 26th, 2011, then the answer will be 5 weeks.

Finally, when you put the whole statement together, it adds five weeks only to 12/6/2010, which will be the first Monday of January 2011 which is 1/3/2011.

If you want to find out the first Monday of next month, or any month, then replace the getdate() call with a dateadd call like this:

DATEADD(m, 1, GETDATE())

which will create this:

SELECT DATEADD(wk, DATEDIFF(wk, '20101206', dbo.FormatDateTime(DATEADD(m, 1, GETDATE()), '[MM]/07/[YYYY]')), '20101206')


SQL AZURE - A Warning to Those Using sys.dm_exec_query_stats
2010-12-13T09:18:00.000-08:00
Today, I read Dinakar Nethi's blog post on Troubleshooting and Optimizing Queries with SQL Azure.  In that article, he discusses how we can use SQL Azure DMVs to troubleshoot poor performing queries.  He relies heavily on sys.dm_exec_query_stats DMV, which I use all the time with on-premise SQL Servers.

While reading it, I remembered reading a blog post from Adam Mechanic about how if there is an alter statement in a batch, sys.dm_exec_query_stats will not pick up any of the statements in the batch.  This might be because some DDL statements do not generate query plans, but I'm not sure.

I wanted to see if SQL Azure had the same issue.  I was hopeful it wouldn't because they change the implementation of many of their views.  Nope...the issue is still there in SQL Azure.  I re-wrote Adam's script, because SQL Azure does not support SELECT..INTO.  Also, tables in SQL Azure require a primary key.

Here's the script:


--BATCH #1
--In this batch, the select does not appear in sys.dm_exec_query_stats

create table #x
(a int primary key
, b varchar(100))

--This is the alter statement that causes the issue.
alter table #x
add unique (a)

insert into #x
values
  (1, 'getstuff')
, (2, 'getMoreStuff')
, (3, 'getEvenMoreStuff')

select *
from #x

select *
from sys.dm_exec_query_stats
where    plan_handle IN    
(        
select            plan_handle        
from sys.dm_exec_requests        
where            session_id = @@spid    )

drop table #x

GO

--BATCH #2
--In this batch, the select does appear in sys.dm_exec_query_stats, 
--because I change the alter table to a create index statement

create table #x
(a int primary key
, b varchar(100))

--I change the alter statement to a create unique index statement.
CREATE UNIQUE INDEX whatever
ON #x (b)

insert into #x
values
  (1, 'getstuff')
, (2, 'getMoreStuff')
, (3, 'getEvenMoreStuff')

select *
from #x

select *
from sys.dm_exec_query_stats
where    plan_handle IN    
(        
select            plan_handle        
from sys.dm_exec_requests        
where            session_id = @@spid    )

drop table #x




Day Of Azure II Talk - SQL Azure Survey
2010-12-05T18:37:00.000-08:00
Lynn and I had a blast speaking at the Day of Azure.  We covered SQL Azure as thoroughly as possible in 2 hours.  You could probably tell that we could have spent two days on our topic....maybe next time.  Here are the slides:

Sql azure dec_2010 Lynn & IkeView more presentations from Ike Ellis.



A Lap Around SQL Azure Data Sync - Step By Step Example
2010-12-02T00:00:00.000-08:00
SQL Azure Data Sync is a cloud-based service, hosted on Microsoft SQL Azure, which allows many SQL Azure databases to stay consistent with one another.  This capability is ideal for a project I’m currently working on where the users need low latency data access across an entire company but they are geographically separated by great distances with one office in USA and one in Europe. SQL Azure Data Sync allows them to have consistently synchronized data physically close to both locations.

SQL Azure Data Sync is super simple to setup (try saying that five times fast).  In this article, I’ll show you how to implement a data synchronization between two databases on the same logical SQL Azure server.  We’ll be implementing the design in the diagram below.




STEP 1 – Signup for SQL Azure
If you need some guidance on how to do that, follow this link:
SQL Azure Signup Tutorial

As of this writing (AOTW), Microsoft is literally giving SQL Azure away (Web Edition – 1GB), so you really have no excuse on why you haven’t signed up yet.

STEP 2 – Create Two Databases
OK, here’s where you might get charged.  For this example, we’ll create two databases that will sync with each other, and you might get charged when you add the second database.  When we're finished, you may want to delete one or both databases to keep costs down.  Database usage is metered per day, so your bill should be less than $1, but I haven’t confirmed that.

You create a database by following these steps:

a) Go to http://sql.azure.com.  Click on the project you created in Step 1.

 


b) First you’ll see just the Master database.  That doesn’t do us much good, since SQL Azure doesn’t allow us to add tables to the Master Database.  



 
We need two more databases.    Click on “Create Database.”  The dialog below will pop up.  Type in library1, and click “Create.”  Do the same for the library2 database.
 



When you are finished, your console should look like this:




 
Now you have two blank databases.  Let’s add some schema and data to library1, then we'll configure SQL Azure Data Sync to copy them over to library2.

STEP 3 – Add Schema and Data to Library1.

We’ll use SQL Server Management Studio (SSMS) to add the schema and data, though we could have easily used Visual Studio, SQLCMD, or even BCP.

Connect to SQL Azure using your server name, user name, and password.  Don’t worry, it’s all encrypted when connecting to SQL Azure.  Make sure your internal firewall allows port 1433 to pass through to SQL Azure.  AOTW, SQL Azure’s TCP port cannot be adjusted, so don’t bother trying to look up how to change it.  It’s a tough break that network admins at many organizations unilaterally block that port.

Your SSMS connection dialog will look something like this:
 



Once you’re connected, run this script to create two tables.  You’ll notice that the TITLES table references the AUTHORS table.  That will be important later.


CREATE TABLE authors
(id INT PRIMARY KEY
, name VARCHAR(100) DEFAULT('')
)

CREATE TABLE titles
(id INT PRIMARY KEY
, name VARCHAR(100) DEFAULT(11)
, authorId INT REFERENCES authors(id))

INSERT INTO authors
VALUES
  ('1', 'Stephen King')
, ('2', 'George RR Martin')
, ('3', 'Neal Stephenson')
, ('4', 'Steig Larsson')


INSERT INTO titles
VALUES
  ('1', 'A Game of Thrones', '2')
, ('2', 'A Clash of Kings', '2')
, ('3', 'The Song of Susannah', '1')
, ('4', 'The Gunslinger', '1')



There are a couple of things to notice about this script.  One, each table in SQL Azure needs a clustered index.  I’m creating one by specifying a primary key in the create table statement.  AOTW, SQL Azure will allow you to create a table without a clustered index, but it won’t allow you to insert data into it, so there’s no reason to bother to even create the heap table.  Two, I like the insert syntax where we can insert multiple rows after the VALUES keyword.  That’s not SQL Azure specific, I just think it’s cool.

We have two databases, one filled with tables and data and one empty.  Let’s fix that and get to the meat of this demo.

STEP 4 – Setup SQL Azure Data Sync.
a) Goto http://www.sqlazurelabs.com/ and click on “SQL Azure Data Sync”

b) You’ll need to sign in to Windows Live ID.

c) The first time you go to this site, you’ll have to agree to a license agreement.
 




d) Click “Add New”.




e) Name the Sync Group "LibrarySync".  I don’t know the limit of how long this name can be, but I’ve thrown in a lot of text in there and it took it.  I wonder if it’s varchar(max).   Then click “Next”.

f) Register your server by typing in your server name, user name, and password.  Notice how in red it says “Your credentials will be stored in an encrypted format.”  This is good news because it saves your credentials when registering other databases on the same server.




g) Then click “Add Hub”.  The Hub database is similar to a publishing database in replication.  For instance, it will win if there are any update conflicts.

h) Then choose the library2 database and click “Add Member”.  Your screen should look something like this:




i) Then click “Next”.

j) You’ll get to a screen that looks like this:



 
The order you select the tables in this screen is the same order that the tables will be replicated.  Remember that we have a foreign key constraint, so it’s real important that we add the authors table before we add the titles table.  Click “Finish".

k) OK, now it seems like you’re done, but you’re not.  Click on “LibrarySync” and then click “Schedule Sync”.  Notice the options you have for scheduling synchronization.  You can sync hourly, daily, etc.  If you choose hourly, the “Minute” dropdown does not let you schedule at a minute interval, rather it allows you to choose the minute after the hour that the sync will begin.  Click “OK”.
 



l) Technically, your sync is ready to go, but click “Sync Now” and wait a minute or two so we can examine the changes.

Step 5 – Examine the Changes
a) Notice the library2 database has all the schema and data from library1.  It also has some other things that SQL Azure Data Sync added for us.  BAM!  We did it!

b) Look at the 3 tables that SQL Azure Data Sync added in both databases.  These tables seem to track sync and schema information.
       a.  Schema_info 
       b.  Scope_config
       c.  Scope_info

c) Each user table gets its own tracking table.  For instance, we have authors_tracking and titles_tracking.  This tells SQL Azure Data Sync which records need to be updated on the other members of the sync group.  Notice how this is not an auditing tool like Change Data Capture.  It works more like Change Tracking in SQL Server 2008.  You won’t get all the changes that led up to the final state of the data.  

d) Each user table gets three triggers that are used to keep the databases consistent.

e) There are many stored procedures added to both databases for the same reason.  Feel free to poke around and examine the triggers and stored procedures.  I found them to be cleanly written.  I like how they’re using the MERGE statement, introduced in SQL Server 2008.

f) Feel free to add a record to Library2, and click “Sync Now”.  You’ll see it in Library1 in no time, thus proving that the synchronization is indeed bi-directional.

g) On the SQL Azure Data Sync Page, check out the synchronization log by clicking on the Dashboard button.

Final Thoughts
At PDC 2010, Microsoft announced that this service will extend to on-premise SQL Servers.  They demo’d how it’s done through an agent that’s installed on the on-premise SQL Server.  It should be available to use in CTP 2, which is not publicly available AOTW.

Also, although the initial snapshot pushed the schema down to the library2 database, it will not keep schema in sync without tearing down the sync group and rebuilding it.  I recommend you finalize your schema before setting up the sync group.

This is built on the Microsoft Sync Framework.  I believe we’ll be seeing this used to synchronize all sorts of data from all sorts of data sources.  I think it’s worth learning and I hope this gives you a fair introduction to the technology.   Remember to delete at least one of your library databases to keep from being charged.  Also, delete your data sync.  Good luck!


TIG
2010-12-01T23:07:00.000-08:00
San Diego Technology Immersion Group (TIG) info


WHERE:
Robert Half Technology
4365 Executive Drive
Suite 450
San Diego, CA 92121

WHEN:
4th Wednesday of every month at 5:45pm, doors lock at 6pm, so don't be late.

WHAT:
We meet once a month to discuss the same book we're all reading.
Those books are chosen off from a range of topics centered on Microsoft Development Technology. 
We usually get about 50 members each month. 
Right now, we are studying C# and .NET for our beginner track.  We'll do that through January
Starting in February, we will study WPF, XAML, and Silverlight. 
Members help each other overcome stumbling blocks while learning new technology.
In addition, we have mentors (experts) helping us understand all this new stuff.


MENTORS:
Ike Ellis
Twitter: http://twitter.com/EllisTeam1
Blog: http://ellisteam.blogspot.com
Website: http://www.ellisteam.net

Scott Reed
Twitter:  http://twitter.com/mscottreed 
Blog:  http://brainhzsoftware.com/blog/

Brad Cunningham
Twitter: http://twitter.com/Foovanadil
Blog: http://www.bradcunningham.net/

Jim Bears

OTHER STUFF:
Email Ike to get on the email list so you know about future meetings.  Also, join google groups: http://groups.google.com/group/sdtig

SPECIAL THANKS:
To Robert Half Technologies for donating our meeting space.
To O'Reilly for donating our first book to all of us, Head First C#




SQL Azure Connection Error with SSIS, SSAS
2010-11-21T12:32:00.000-08:00
Today, I was playing around with making a small cube out of the data in the sys.bandwidth_usage table in SQL Azure, and I got an error message which reads:

"Test Connection failed because of an error in initializing provider.  Server Name cannot be deteremined.  It must appear as the first segment of the server's dns name (servername.database.windows.net.)  Some libraries do not send the server name, in which case the server name must be included as part of the user name (username@servername).  In addition, if both formats are used, the server names must match."



I solved this the way the error says to solve it, by adding what was in the server name field after the user name in the user name field.  I also noticed that it can be solved by switching to the native .NET provider, and not using the server name in the user name.  I played with the data source in SSIS, too, and it works the same way.  Interestingly, SSRS chooses a native .NET SQL provider by default, and therefore doesn't have this problem.


SQL Azure: AOTW = As Of This Writing
2010-11-20T08:43:00.000-08:00
I'm using a new acronym now.  Because SQL Azure is changing so much, and I find myself saying and thinking "As Of This Writing, I'm just shortening it to AOTW.


SQL Azure Deprecating sysLogins
2010-11-20T08:37:00.000-08:00
There is no sysLogins in SQL Azure.  To find out a list of your SQL login accounts use:

select * from sys.sql_logins

The sys.sql_logins dmv doesn't work either.  I assume both of these have been taken out because there are no active directory users AOTW.


Best Agile Tools - By Blog Favorite - Woody Zuill
2010-11-18T12:00:00.000-08:00
Here we go again.  I asked Woody what his favorite agile tools/blogs are.  This was his very well thought out answer (the bolding is my own):

"I don't pay much attention to the Agile blogs - there is a LOT OF NOISE out there with people giving advice about stuff they have insufficient (or no) experience to give advice about, and probably very few people available for taking that advice anyway.  Many of the "blogs" are merely lame marketing efforts.  The most solid of the bloggers are worth reading - but I don't typically follow them, but I'm more likely to click a link to an article if it is from a blogger who is someone I respect.  
 
Tools for managing an Agile effort are another matter.  
 
My one general rule about Agile Tools is DON'T (if the tool is in the form of a computer application).  There are better alternatives. (I am not talking about refactoring tools, testing tools, etc. here - but the management tools, the communication tools, things like that).
 
Agile is best done manually, and the computerized management tools will in most cases be counter-productive IMHO. (Or IMNSHO - which is In My Not So Humble Opinion).  
 
In a nutshell: You learn Agile by study and practice and doing and talking about the doing and so on. Tools often block learning by forcing you to follow what someone else decided is useful.  Tools needlessly complicate the simplicity of Agile.  Simplify, simplify.
 
Like the Agile Manifesto says: We value Individuals and Interactions over Processes and Tools.   

I take that to at least partly mean that we get a lot more value out of focusing on empowering Individuals and improving our ability to interact well as individuals than on following some Process or using some Tool. 
 
I have only one rock solid, immutable rule, however (that I reserve the right to change anytime): Continuously Inspect and Adapt - continuous improvement of our thinking, our understanding, how we learn, how we get along, our principles, our practices, our processes, our tools, etc.   
 
So, if I found a tool that looks like it would bring me value, I would certainly investigate it, try it, and use it if it proves to be userful.  And I would continually evaluate its usefulness.  But typically - I follow the rule of DON'T when it comes to computerized Agile management tools.
 
Right now, my favorite tools continue to be physical ones - white boards and erasable markers, sticky notes (small) and sticky notes (huge), 3 x 5 index cards and Sharpies, face-to-face interactions and information radiators, blank flip charts and  a digital camera, eye of newt and toe of frog (to paraphrase Shakespeare)."


TechEd Berlin 2010 - Link to Videos on SQL Azure and SQL Server
2010-11-17T09:54:00.001-08:00
I didn't get a chance to go to TechEd Berlin, but I've been watching the videos online.  The user interface to the website is not super great.  I shouldn't complain too much.  I'm super grateful that they posted all the videos from that week online so quickly.  By comparison, it will take two weeks to get the SQL Pass 2010 videos online.

Here's where the SQL Azure videos start (page 19 of the video list):
http://www.msteched.com/2010/Europe/Page19/

SQL Azure Reporting videos are on page 20:
http://www.msteched.com/2010/Europe/Page20/

Here is where the SQL videos start (page 18):
http://www.msteched.com/2010/Europe/Page18/

SQL Videos for BI/PowerPivot etc (page 21):
http://www.msteched.com/2010/Europe/Page21/

There were a few other presentations that I thought would be interesting to the SQL developer/DBA:
Introduction to PowerShell
http://www.msteched.com/2010/Europe/WSV301

SSRS 2008R2 Integration with SharePoint 2010
http://www.msteched.com/2010/Europe/OFS330

Creating Self-Service Analytic BI Applications with SharePoint 2010
http://www.msteched.com/2010/Europe/OFS206

Code First Development with Entity Framework
http://www.msteched.com/2010/Europe/DEV212

Building Business Applications with Visual Studio LightSwitch (which seems to be the MS Access front end for SQL Server....it's super cool.)
http://www.msteched.com/2010/Europe/DEV206

The Future of C#
http://www.msteched.com/2010/Europe/DEV301

Phew, that ought to keep us busy for a long time.  Have fun and happy watching!


Finding The Difference Between Two Dates that are in Seperate Rows in T-SQL
2010-11-04T10:58:00.000-07:00
This problem is often solved with a cursor, but is easily re-written using a CTE.  In the below sample, we are using data from a university who's trying to figure out the time between when a course ended and when the next course began.  So the data looks something like this:
learnerID       StartDate                  CompletedDate
0000131801 2010-07-01 00:00:00.000    2010-09-23 00:00:00.000
0000131801 2010-10-01 00:00:00.000    2010-12-24 00:00:00.000
0000212970 2009-11-01 00:00:00.000    2010-01-24 00:00:00.000
0000212970 2010-02-01 00:00:00.000    2010-04-26 00:00:00.000
0000212970 2010-03-01 00:00:00.000    2010-05-24 00:00:00.000
0000212970 2010-06-01 00:00:00.000    2010-08-24 00:00:00.000
0000212970 2010-07-01 00:00:00.000    2010-09-23 00:00:00.000
0000212970 2010-10-01 00:00:00.000    2010-12-24 00:00:00.000

and the query that will give them the average days between a course ending and the next course starting looks like this:

with lc
as
(
 select row_number() over (order by learner_id, start_date, learner_course_id) as rownum
  , learner_course_id
  , learner_id
  , start_date
  , coalesce(completed_date, end_date) as CompletedDate
 from learner_courses
)
select datediff(d, lc1.completeddate, lc2.start_date) as daysbetweenstarts, *
from lc lc1
join lc lc2 
on lc1.learner_id = lc2.learner_id 
 and lc2.rownum = (lc1.rownum + 1)
where datediff(d, lc1.completeddate, lc2.start_date) > -1


Best Books on TDD, Agile, and SCRUM by Woody Zuill
2010-11-03T13:26:00.000-07:00
My friend, Woody Zuill, is a developer and manager that I respect an awful lot.  Recently he was asked to recommend great books on agile software development.  Since Woody is always reading, he seemed like a great guy to ask.  His response was thoughtful and interesting, so I thought I'd post it below:

"The books I've found useful all have holes in them.  You have to get a fair amount of bad to get a small amount of good.
 
- For me, the Lean books by the Poppendiecks were probably the most good with the least bad. 
 
- Kent Beck's XP book - both editions.  I think you need both to get it all.  These books have been very useful to me.  A lot more real stuff and real thinking than most of the "How To Agile" books.
 
- Kent Beck's TDD book
 
- The second edition of Alistair Cockburn's book - Agile Software Development: The Cooperative Game.  I think the first edition missed the boat in a lot of things.  I think he must have learned a lot by the time the second edition came out. I don't know how experienced he actually is, but I like the nature of his book.  Note: His book on How to Survive an OOP project was one of the worst books I have seen.
 
- Of course, Michael Feather's Legacy Code book is great.  I recommend that for all developers.
 
- And Bob Martin's Clean Code and Agile Software Development books are must reads.  The Bob Martin Agile book is overall probably the most useful book I've read (on software development) - but it is a hard read in some ways, and very difficult to sort through.
 
- I found Diana Larsen and Esther Derby's Agile Retrospectives book very useful to adjust my thinking about making teams work well.
 
I used to like Mike Cohn's books on estimating and so on - but only barely.  Not so much anymore.  Same with Ken Schwaber's Scrum books, in some ways I wish they were never written.
 
I'ver read about 20 or 30 books on the topic, and flipped through a lot of others.  Most are misleading at best.  Knowing what I know about things in general, I suspect a lot of this garbage is written by people who don't really know what they are talking about and aren't able to admit it to themselves.  I want to believe that - otherwise the authors are being purposely misleading or deceptive and I am not ready to accept that.
 
There is one book that I really loved, and felt the guy was actually right on - but I searched all over the Internet tonight looking for it and couldn't find it.  When I get home I'll look around and see if I can find it. [ I found it: "Sustainable Software Development: An Agile Perspective" by Kevin Tate.  I must have "loaned" it to someone because I couldn't find it anywhere at home.  I remember it as being very good and humble - but I read it about 5 years ago and haven't seen it since so a re-read is in order, and I reserve the right to change my mind about its usefulness. ]
 
Well... there have been a lot of good in a lot of those (and other) books.  And for me, the main use was to help me think through things.  There is a lot of garbage to sift through, though."

Great stuff, huh?  Below are links to all the books he references in the email:

Kent Beck's Books

Agile Software Development: The Cooperative Game

Working Effectively with Legacy Code

Bob Martin's Books

Clean Code

Esther Derby's Agile Retrospectives

Mike Cohn's Books

And the gem:

Sustainable Software Development


T-SQL - Split Long String into Several Small Strings
2010-11-02T17:25:00.000-07:00
This is a fantastic little function:

CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(8000)) 
RETURNS table 
AS 
RETURN ( 
    WITH Pieces(pn, start, stop) AS ( 
      SELECT 1, 1, CHARINDEX(@sep, @s) 
      UNION ALL 
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1) 
      FROM Pieces 
      WHERE stop > 0 
    ) 
    SELECT pn, 
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 8000 END) AS s 
    FROM Pieces 
  ) 

I found it here:

Stack Overflow

and here:

SQLTeam


TimeMatters Master Audit File Error
2010-11-01T13:48:00.000-07:00


If you are running TimeMatters in a replicated environment and you get the error:

Unable to access Master Audit File  
Record Audit will not be saved.

ERROR:  The insert failed.  It conflicted with an identity range check constraint in database 'TimeMatters10', replicated table 'lntmuser.Audit.Current', column 'Ident'.  If the identity column is automatically managed by replication, update the range as follows: f

The run the following command on the publisher and it should fix it:

exec sp_adjustpublisheridentityrange @table_name = 'audit current', @table_owner =  'lntmuser'


LA Code Camp Files
2010-10-25T18:56:00.000-07:00

Hi There!

Thanks for coming to my LA Code Camp session on SQL Server Performance Optimization.  I hope you had a great time, learned a lot, and left with action items that can immediately help you at work.

Here's a link to the download files:

LA Code Camp.zip

See you soon!

Ike


ID Columns With No Indexes
2010-10-25T18:36:00.001-07:00
This is a really simple script that will tell you what columns with "ID" in the name don't have indexes.  Often, foreign key constraints are not added to the database.  I'm sure the referrential integrity is being enforced in the business logic of the application.  At least I hope so.  In any case, the optimizer still needs those columns to be indexed.




select * from sys.columns c 
    where c.name like '%id%'
        and c.object_id not in 
            (
            select object_id from sys.index_columns
            )


Then I created this script to just go ahead and create a basic nonclustered index on those columns.  Just copy & paste the results into a SQL Server Management Studio query window and run it.



select 'create nonclustered index  IX_' + t.name + '_' + c.name + '_001'
    + ' ON ' + schema_name(t.schema_id) + '.' + t.name + '(' + c.Name + ');'
    from sys.columns c 
    join sys.tables t 
        on c.object_id = t.object_id
    where c.name like '%id%'
        and c.object_id not in 
            (
            select object_id from sys.index_columns
            ) 



You'll want to include other columns in these indexes to make sure your indexes are covering the queries, but this should get you started.  Make sure the results are return in text, not grid.


T-SQL Random Date
2010-10-16T19:42:00.000-07:00

I wrote this code to generate a random date between two dates. I wanted a random date for each row in a table. Use @beginDate and @endDate to specify the lower and upper bounds of the random date. The only tricky part is the checksum(newid()). You can try using the rand function without it, but you'll just get the same random date for every row in the table.

declare @beginDate datetime, @endDate datetime
set @beginDate = '1/1/2000'
set @endDate = getdate()

select dateadd(d, cast(-1 * datediff(d, @beginDate, @endDate)
* rand(checksum(newid())) as int), @endDate)
from sys.objects 


If you want to update a date column with a random date, you can just do this:

update Orders 
set orderdate = dateadd(d, cast(-1 * datediff(d, @beginDate, @endDate)
* rand(checksum(newid())) as int), @endDate)


Blind Spot - T-SQL  $Identity
2010-09-29T15:59:00.000-07:00
I've been working with SQL Server for 12 years, but this short cut escaped me.

In Transact-SQL, the $IDENTITY keyword allows us to refer to the identity column without knowing its name. Since identity columns are often primary keys, we could use this as a short cut for always referring to the PK. This would be awesome to use in a code generator or an ORM.

This is not to be confused with @@IDENTITY or SCOPE_IDENTITY(), which tells us the actual number auto generated by the IDENTITY column (though each behaves in different ways.)

Test it out with this script:

use tempdb
go

create table test 
(
id int identity(1,1)
, descr varchar(100)
)
go

insert into test
(descr)
values
('first record')
, ('second record')
, ('third record')
go

select $identity, descr from test


TIG Notes
2010-09-23T13:02:00.000-07:00
Yesterday at TIG I mentioned that the Visual Studio tips from Sara Ford had helped me to be a faster developer and worker.  Many of you asked where you could find her blog, so here it is:

Sara Ford's BLOG


San Diego TIG - 2nd Meeting
2010-09-23T12:51:00.001-07:00

The Tech Immersion Group met last night, and we had a great time.  Next month, we'll be reading Head First C#, Chapters 6 - 8, and Lab 2.  We'll be meeting on October 27th.  

We also setup a new Google Group.  DM me for an invitation.


San Diego Tech Immersion Group
2010-09-13T18:12:00.000-07:00


TIG - Tech Immersion Group 

WHAT

The Tech Immersion Group is founded on the belief that learning new technology can be fun.  Our first topic will be C# and .NET.  Future topics include Silverlight, SharePoint, WCF, Entity Framework, LINQ, and Azure.  The idea is as follows:

1) We will meet monthly to discuss about 200 – 300 pages of a book we’ve chosen.

2) We will stay with the same topic for several months (probably until we’re finished with the book.)

3) We will study for certification exams together.

4) We will write sample code and software projects.

5) This will be a safe place where any question can be asked  (Ugh, I hope you get a good answer).

 

WHO

All are welcome to join the group.  We do need to know who they are ahead of time so we can ensure a book is provided.  We’ve been able to get our first book donated by O’Reilly Press.  Hopefully, we can keep this sweet deal going.

I’m Ike Ellis, and I’ve been writing software for 12 years.  Software I have written is currently in use by several thousand people.  I love to teach and I love to learn.  I can’t wait to meet you and get started on this together!

THE COMMITMENT

You will get a free book, free time from a mentor, and really valuable knowledge.  In return, we ask:

1) That you show up on time and ready to learn.

2) That you commit to being there every month.

3) That you consider becoming a member of the San Diego .NET User Group ($50 annually.)

4) That you write a review on Amazon regarding the book you got for free (This will keep the free books coming.)

That’s it!  Let’s get ready to learn a ton!

WHERE

Robert Half Technology
4365 Executive Drive
Suite 450
San Diego, CA 92121

WHEN

The 4th Wednesday of every month.  Please be there at 5:45pm, as the doors to our building will lock by 6pm.  Don’t be late!

Ike Ellis Contact Info
Twitter: @EllisTeam1
FaceBook: http://www.facebook.com/ellisike
Linked In
http://ellisteam.blogspot.com
http://www.ellisteam.net

 

A BIG THANK YOU TO O'RIELLY FOR DONATING OUR BOOKS!!!  
Visit them at http://www.oreilly.com.



T-SQL Session, Promised Script
2010-09-08T09:19:00.001-07:00
Several people asked that I post this script on T-SQL fundementals:

USE Northwind

SELECT * 
 FROM Customers;

SELECT CompanyName
 , ContactName
 , ContactTitle
 --, Address
 --, City
 --, Region
 --, PostalCode 
 FROM Customers;
 

SELECT CompanyName
 , ContactName
 , ContactTitle
 , Address
 , City
 , Region
 , PostalCode 
 FROM Customers
 ORDER BY ContactName;




SELECT CompanyName
 , ContactName
 , ContactTitle
 , Address
 , City
 , Region
 , PostalCode 
 FROM Customers
 ORDER BY CompanyName, ContactName DESC; --ASC is the default.
 
SELECT CompanyName
 , ContactName
 , ContactTitle
 , Address
 , City
 , Region
 , PostalCode 
 FROM Customers
 WHERE CompanyName = 'Alfreds Futterkiste'
 ORDER BY ContactName DESC; --ASC is the default.
 
--Comparison Operators
--=
--<>
--!=
--<
--<=
--!<
-->
-->=
--!>
--BETWEEN
--IS NULL

SELECT CustomerID
 , OrderID
 , Freight 
 , ShippedDate 
 FROM Orders
 WHERE Freight > 100;

SELECT CustomerID
 , OrderID
 , Freight 
 , ShippedDate 
 FROM Orders
 WHERE Freight !> '100';

SELECT CustomerID
 , OrderID
 , Freight 
 , ShippedDate 
 FROM Orders
 WHERE Freight <= '100' ;
 
SELECT CustomerID
 , Region  
 FROM Customers
 WHERE Region <> NULL;
 
SELECT CustomerID
 , Region  
 FROM Customers
 WHERE Region <> 'CA';
 
SELECT CustomerID
 , Region 
 FROM Customers
 WHERE Region <> 'CA' OR Region IS NOT NULL;

SELECT CustomerID
 , OrderID
 , Freight 
 , ShippedDate  
 FROM Orders
 WHERE Freight BETWEEN '10.14' AND '43.90'
 ORDER BY Freight ;
 
--Logical Operators
--AND
--OR
--NOT

SELECT CustomerID
 , OrderID
 , Freight 
 , ShippedDate  
 FROM Orders
 WHERE Freight BETWEEN '10.14' AND '43.90'
  OR ShippedDate BETWEEN '1/1/1997' AND '12/1/1997'
 ORDER BY ShippedDate, Freight  ;
  
SELECT CustomerID
 , OrderID
 , Freight 
 , ShippedDate 
 FROM Orders
 WHERE Freight BETWEEN '10.14' AND '43.90'
  OR ShippedDate BETWEEN '1/1/1997' AND '12/1/1997'
 ORDER BY ShippedDate, Freight  ;


--I want all orders from CustomerID = VINET with a freight over $10 and all orders from CustomerID TOMSP with a freight over $10.
SELECT CustomerID
 , OrderID
 , Freight  
 FROM Orders
 WHERE Freight > '10'
  AND CustomerID = 'VINET'
  OR CustomerID = 'TOMSP' 
  ORDER BY CustomerID;

--SQL is reading this from left to right.  So technically we can do this:
SELECT CustomerID
 , OrderID
 , Freight  
 FROM Orders
 WHERE CustomerID = 'VINET'
  AND Freight > '10'
  OR CustomerID = 'TOMSP'
  AND Freight > '10'
  ORDER BY CustomerID;
  
SELECT CustomerID
 , OrderID
 , Freight 
 FROM Orders
 WHERE (CustomerID = 'VINET'
  OR CustomerID = 'TOMSP')
  AND Freight > '10'
  ORDER BY CustomerID;

--IN Operator
SELECT CustomerID
 , OrderID 
 FROM Orders
 WHERE (CustomerID = 'VINET'
  OR CustomerID = 'TOMSP'
  OR CustomerID = 'HANAR'
  OR CustomerID = 'SUPRD');

  
SELECT CustomerID
 , OrderID 
 FROM Orders
 WHERE CustomerID IN ('VINET', 'TOMSP', 'HANAR', 'SUPRD');

SELECT CustomerID
 , OrderID 
 FROM Orders
 WHERE CustomerID NOT IN ('VINET', 'TOMSP', 'HANAR', 'SUPRD');
 

--WILDCARDS
SELECT CompanyName
 FROM Customers 
 WHERE CompanyName LIKE 'A%';


SELECT CompanyName
 FROM Customers 
 WHERE CompanyName LIKE '%A';
 

SELECT CompanyName
 FROM Customers 
 WHERE CompanyName LIKE '%A%';
 

SELECT CompanyName
 FROM Customers 
 WHERE CompanyName LIKE 'W%A';


SELECT CompanyName
 FROM Customers 
 WHERE CompanyName LIKE 'F%A%';

SELECT CompanyName
 FROM Customers 
 WHERE CompanyName LIKE '[FM]%';
 
 
SELECT CompanyName + ' - ' + ContactName 
 + ' - ' + ContactTitle AS HeaderLine1
 FROM Customers
 WHERE CompanyName + ' - ' + ContactName 
 + ' - ' + ContactTitle LIKE 'A%'
 ORDER BY HeaderLine1;

--Order of Operation
--1.FROM clause
--2.WHERE clause
--3.GROUP BY clause
--4.HAVING clause
--5.SELECT clause
--6.ORDER BY clause 

--Calculated fields - Math
SELECT
 OrderID
 , ProductID
 , UnitPrice
 , Quantity
 , UnitPrice * Quantity AS TotalPrice 
 FROM [Order Details];
 
--Math operators
-- +, -, *, /


--FUN WITH FUNCTIONS!

--Figuring out envelope size for a mailing
SELECT CompanyName
 , LEN(CompanyName) AS NumberOfChars 
 FROM Customers;

--Uppercase CompanyName
SELECT UPPER(CompanyName) AS UpperCompany
 FROM Customers;
 
--STRING FUNCTIONS
--LTRIM, RTRIM
--SUBSTRING
--RIGHT
--LEFT
--SOUNDEX

SELECT ContactName
 FROM Customers
 WHERE SOUNDEX(ContactName) = SOUNDEX('paul');
 
--Date Functions
--DATEDIFF
--DATEADD
--MONTH
--YEAR
--DATEPART

--The difference between order date and ship date in days - T-SQL help is great with Functions - SHIFT-F1
SELECT 
 OrderDate
 , ShippedDate
 , DATEDIFF(d, OrderDate, ShippedDate) AS DaysToShip
 FROM Orders;
 
--The orders that took longer than 15 days to ship
SELECT 
 OrderDate
 , ShippedDate
 , DATEDIFF(d, OrderDate, ShippedDate) AS DaysToShip
 FROM Orders
 WHERE DATEDIFF(d, OrderDate, ShippedDate) > 15;


--Aggregates
--MAX
--MIN
--SUM
--AVG
--COUNT
--COUNT DISTINCT

--Most recent Order Date
SELECT MAX(OrderDate) AS MaxOrderDate
 FROM Orders;
 
--First Order Date
SELECT MIN(OrderDate) AS MaxOrderDate
 FROM Orders;

--Let's combine this into a subquery
SELECT OrderID 
 FROM Orders 
 WHERE OrderDate = 
  (
   SELECT MAX(OrderDate) AS MaxOrderDate
    FROM Orders
  );

--Max Days to Ship --Null values out of aggregation
SELECT MAX(DATEDIFF(d, OrderDate, ShippedDate)) AS MaxDaysToShip
 FROM Orders;

--Average Days to Ship
SELECT AVG(DATEDIFF(d, OrderDate, ShippedDate)) AS MaxDaysToShip
 FROM Orders;
 
--Orders above my average days to ship
SELECT OrderID 
 , CustomerID 
 , OrderDate
 , ShippedDate
 , DATEDIFF(d, OrderDate, ShippedDate) AS DaysToShip
 FROM Orders
 WHERE DATEDIFF(d, OrderDate, ShippedDate) > 
  (
   SELECT AVG(DATEDIFF(d, OrderDate, ShippedDate)) AS MaxDaysToShip
    FROM Orders
  ) ;
  

SELECT CustomerID
 , AVG(DATEDIFF(d, OrderDate, ShippedDate)) AS MaxDaysToShip
 FROM Orders
 GROUP BY CustomerID;
 
--Customers with an average greater than the average
SELECT CustomerID
 , AVG(DATEDIFF(d, OrderDate, ShippedDate)) AS MaxDaysToShip
 FROM Orders
 GROUP BY CustomerID
 HAVING AVG(DATEDIFF(d, OrderDate, ShippedDate)) >= 15;

--JOINS
--INNER
--RIGHT OUTER
--LEFT OUTER

--Inner Join
SELECT c.CompanyName
 , c.CustomerID 
 , o.OrderDate
 , o.ShippedDate
 FROM Customers c 
 JOIN Orders o 
  ON c.CustomerID = o.CustomerID 
 ORDER BY c.CompanyName
 
--LEFT OUTER JOIN
SELECT c.CompanyName
 , c.CustomerID 
 , o.OrderDate
 , o.ShippedDate
 FROM Customers c 
 LEFT OUTER JOIN Orders o 
  ON c.CustomerID = o.CustomerID 
 WHERE o.CustomerID IS NULL
 ORDER BY c.CompanyName
 
 
SELECT * 
 FROM Customers
 
--CRUD
INSERT INTO Customers 
(CustomerID, CompanyName)
VALUES
('ELLIS', 'EllisTeam')

DELETE FROM Customers
WHERE CustomerID = 'ELLIS'

UPDATE Customers
 SET ContactName = 'Ike Ellis'
 
SELECT * FROM Customers 
 WHERE CustomerID = 'Ellis'

DELETE FROM Customers 
WHERE CustomerID = 'ELLIS'


UPDATE o
SET Freight = Freight + 1
FROM Customers AS c 
 JOIN Orders AS o 
  ON c.CustomerID = o.CustomerID 
 WHERE Region = 'CA'
 

--Tips
--================
--Sometimes T-SQL doesn't read as intuitively as we like.  Use white space and comments.

--Limit record results to as little as possible. (Good for network, disk, memory, users, and performance.)

--Nulls

--Connection Tips - Coloring and highlighting

--Always use Linenumbers

--Copy and paste results into EXCEL for adhoc stuff

--Show an error message (RAISEERROR) with severity
RAISERROR (N'This is message.', 18, 1);

--Learn about Query Plans
SELECT * 
 FROM Customers c 
 JOIN Orders o 
  ON c.CustomerID = o.CustomerID 
 JOIN [Order Details] od 
  ON o.OrderID = od.OrderID
 JOIN Products p 
  ON od.ProductID = p.ProductID
ORDER BY c.CompanyName

--Object Explorer Drag & Drop

--Avoid Cursors

--Be careful updating and deleting (WHERE clauses, backup, SELECT using the WHERE clause, then Delete with the same one.)

--Never change production without changing dev and beta first

--Don't bother coding for portability or language independence.

--Consider using synonyms for long table names
CREATE SYNONYM Cust FOR dbo.Customers
SELECT * FROM Cust
DROP SYNONYM Cust

--TRUNCATE VS DELETE
--Logged and WHERE
DELETE FROM Temp1

--Non-logged and no WHERE, but fast
TRUNCATE TABLE Temp1


--Common question - How to delete duplicates
CREATE TABLE dbo.duplicateTest 
( 
[ID] [int] , 
[FirstName] [varchar](25), 
[LastName] [varchar](25)  
) ON [PRIMARY] 

INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith') 
INSERT INTO dbo.duplicateTest VALUES(2, 'Dave','Jones') 
INSERT INTO dbo.duplicateTest VALUES(3, 'Karen','White') 
INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith')

SELECT * 
 FROM dbo.duplicateTest
 
SELECT * 
 FROM dbo.duplicateTest 
 WHERE ID = 1 AND FirstName = 'Bob' AND LastName = 'Smith'
 
DELETE TOP(1) FROM dbo.duplicateTest WHERE ID = 1

DROP TABLE duplicateTest

UPDATE Customers 
SET ContactName = 'Ike Ellis'


WHERE CustomerID = 'ELLIS'


DELETE FROM Customers 
WHERE CustomerID = 'ELLIS'


Microsoft Lightswitch = Enterprise Microsoft Access
2010-09-08T08:17:00.000-07:00
Lynn Langit blogged on Microsoft Lightswitch and that made me go check it out.  It's a beta tool for Visual Studio that allows screens and database tables to be created with minimal software development or training.  

This reminds me of Microsoft Access in the 1990s.  Access made it so easy to create a database, data entry screens, and reports, that tons of lay people were writing custom apps for their own purposes.  Because Access would collapse after it had 12 concurrent users, professional developers would need to be hired to port the app to SQL Server.

Lightswitch finally provides all of those great Access features for SQL Server, SQL Azure, and cloud development.  I'll post more about it after I've played with it awhile.

Here are some links:

Lynn's Original Post

MSDN Lightswitch Site

Lightswich:  15 Reasons Non-Programmers Should Try It Out

UPDATE:  Carl Franklin on .NET Rocks released a podcast on LightSwitch last month.
.NET Rocks Podcast on LightSwitch


TIG - Head First C# - 1st Assignment
2010-08-26T20:57:00.000-07:00



Our first reading assignment:

     -Read the part under your commitment, and decide if you can do those things.
     -Make and O’Reilly account.
     -Forward that account logon name to me.
     -Read Chapters 1 – 5 of our book.
     -See you on September 22nd!
     -Optionally, show me a mortgage calculator.



T-SQL: Use A Variable to Create a ColumnList for a PIVOT Query of Dates
2010-07-15T09:00:00.001-07:00
DECLARE @BeginDate datetime
DECLARE @EndDate datetime

SET @BeginDate = '7/1/2010';
SET @EndDate = '7/31/2010';

--Create a dates table with every date between 7/1/2010 and 7/31/2010
WITH mycte AS
 (
     SELECT cast(@BeginDate as datetime) DateValue
     UNION ALL
     SELECT DateValue + 1
  FROM  mycte    
  WHERE   DateValue + 1 < @EndDate

 )
SELECT CONVERT(varchar,DateValue,101) as StDate INTO #dates from mycte
OPTION (MAXRECURSION 0);

--Get each date in the #dates table as a set
select ',[' + StDate + ']' from #dates;

--Get it as a long string.  We use the XML path for that, which is awesome.
select ',[' + StDate + ']' from #dates for XML path('');

--Use the STUFF function to remove the leading comma.  This is a pretty cool way to do that, rather than messing around with LEN and stuff.
SELECT STUFF((select ',[' + StDate + ']' from #dates for XML path('')),1,1,'') AS NoLeadingCommaOneLongString;

--Use the same query to set a variable value.
DECLARE @ColumnList varchar(max);
SET @ColumnList = STUFF((select ',[' + StDate + ']' from #dates for XML path('')),1,1,'');

--Here's a sample table with dates and names.
CREATE TABLE #SampleWorkDates
(
 Workeddate datetime 
 , [name] varchar(100)
 , workdone int
);
INSERT INTO #SampleWorkDates 
SELECT '7/12/2010','john', 100 UNION 
SELECT '7/12/2010','john', 110 UNION 
SELECT '7/12/2010','debby', 102 UNION 
SELECT '7/18/2010','john', 60 UNION 
SELECT '8/10/2010','alex', 89 UNION 
SELECT '9/11/2010','john', 120;

--We need to pivot that data, but the column list is really long.

--Rather than writing out all the columns by hand, like this:
select * from 
 (select [Name],count([Name]) over (partition by [Name]) as Total,CONVERT(varchar,[WorkedDate],101) as Workeddate,COUNT(*) as cnt from #SampleWorkDates 
where CAST(WorkedDate AS datetime) >= '07/01/2010' and CAST(WorkedDate AS datetime) <= '07/31/2010' group by [Name],CONVERT(varchar,[WorkedDate],101))p 
pivot (MAX([name]) for WorkedDate in ([07/01/2010],[07/02/2010],[07/03/2010],[07/04/2010],[07/05/2010],[07/06/2010],[07/07/2010],[07/08/2010],[07/09/2010],[07/10/2010],[07/11/2010],[07/12/2010],[07/13/2010],[07/14/2010],[07/15/2010],[07/16/2010],[07/17/2010],[07/18/2010],[07/19/2010],[07/20/2010],[07/21/2010],[07/22/2010],[07/23/2010],[07/24/2010],[07/25/2010],[07/26/2010],[07/27/2010],[07/28/2010],[07/29/2010],[07/30/2010]))pvt


--We can just use the @ColumnList variable we used before.  It looks cleaner and is reusable.
declare @sql nvarchar(max);
set @sql = 'select * from (select [Name],count([Name]) over (partition by [Name]) as Total,CONVERT(varchar,[WorkedDate],101) as Workeddate,COUNT(*) as cnt from #SampleWorkDates 
where CAST(WorkedDate AS datetime) >= ''' + CONVERT(varchar, @BeginDate, 101) + ''' and CAST(WorkedDate AS datetime) <= ''' + CONVERT(varchar, @EndDate, 101) + 
''' group by [Name],CONVERT(varchar,[WorkedDate],101))p 
pivot (sum(cnt) for WorkedDate in (' + @ColumnList + '))pvt';

PRINT @sql;

exec(@sql);


DROP TABLE #SampleWorkDates;
DROP TABLE #dates;


T-SQL: Script to Populate a Dates Table With Recursion and a CTE
2010-07-15T08:10:00.000-07:00
I used to create dates tables like this:

--********************************
DECLARE @BeginDate datetime
DECLARE @EndDate datetime

SET @BeginDate  = '1/1/1900'
SET @EndDate = '12/31/2050'

CREATE TABLE #Dates ([date] datetime)

WHILE @BeginDate <= @EndDate
BEGIN
 INSERT #Dates
 VALUES
 (@BeginDate)
 
 SET @BeginDate = @BeginDate + 1
END
SELECT * FROM #Dates
DROP TABLE #Dates 
--********************************

That works fine, but it has a lot to clean up, executes slowly, and has a lot of code.  Plus it uses looping.

This is a better way:
--********************************
WITH mycte AS
 (
     SELECT cast('1900-01-01' as datetime) DateValue
     UNION ALL
     SELECT DateValue + 1
  FROM  mycte    
  WHERE   DateValue + 1 < '2050-12-31'

 )
SELECT DateValue
    FROM    mycte
    OPTION (MAXRECURSION 0)
--********************************

By default, the MAXRECURSION option is set to 100.  I set it to zero, which means we could go into an infinite loop.  I use the WHERE clause to make sure the loop bails out at 12/31/2050.  Without the WHERE clause, we'd be hosed.


SSRS 2008 - Adding SSL After Installation
2010-07-01T16:09:00.000-07:00
If you can't get it to work, try doing everything in this thread:

http://prologika.com/CS/forums/t/946.aspx

And everything here:

http://technet.microsoft.com/en-us/library/ms345223(SQL.100).aspx


Also, SSRS 2008 does not support wildcard certificates, ie *.yourdomain.com. Obtain a special dedicated SSL cert YourServerName.yourdomain.com, then apply it on the SSRS 2008 server. This is what worked for us at a client, after calling PSS.  This is a known issue at Microsoft and they are working on a better solution.


Slides and Code from Code Camp Presentation
2010-06-27T09:35:00.000-07:00
T-SQL A Primer

T-SQL Slides and Scripts


Cool Control Panel View
2010-05-19T11:23:00.000-07:00
This went around a few months ago, but in case you missed it, create a folder on your desktop and rename it to this: GodMode.{ED7BA470-8E54-465E-825C-99712043E01C}

This will create a new control panel icon on your desktop with a ton of cool options.

Looks like this:




Data Explosion - Great Story
2010-04-23T13:19:00.000-07:00

I got this story from Scott Cameron of Hitachi Consulting, although I've heard it before:

The story of the man who invented chess can help illustrate the problem of data explosion.

The man lived in India, and according to legend his name was Sessa. The king of India was very impressed with the game of chess and asked Sessa to name his reward. Sessa's request was so modest that it offended the king: He asked simply for one grain of rice for the first square of his chess board, two grains for the second square, four grains for the third, and so forth, doubling the number of grains of rice for each of the 64 squares of the board. Of course, by the time the king's magicians calculated the total amount of rice needed to pay the reward, they realized that it would require a warehouse 3 meters by 5 meters by twice the distance to the sun to pay the reward.

The king gave Sessa a sack, pointed him to the warehouse and told him to go count out his reward - no rush.

When numbers increase geometrically (like they do in a data warehouse), they get large very quickly.


Business Intelligence Quote
2010-04-12T09:56:00.000-07:00
"You can spend millions building the data warehouse, but if you don't have the back office under control, you are wasting your money."
Mike Costa
Corporate Director of Quality Process & Architecture
The Dow Chemical Company


Feedback Loops - Donella Meadows
2010-04-10T17:38:00.000-07:00


"Examples of compelling feedback is not hard to find. Suppose taxpayers got to specify on their return forms what government services their tax payments must be spent on....Suppose any town or company that puts a water intake pipe in a river had to put it immediately downstream from its own wastewater outflow pipe. Suppose any public or private official who made the decision to invest in a nuclear power plant got the waste from that facility stored on his or her lawn...Suppose the politicians who declare war were required to spend that war in the front lines.

There is a systematic tendency on the part of human beings to avoid accountability for their own decisions. That's why there are so many missing feedback loops."

She goes on to relate a story about Amsterdam:



"Near Amsterdam, there is a suburb of single-family houses all built at the same time, all alike. For some unknown reasons it happened that some of the houses were built with the electric meter down in the basement. In other houses, the electric meter was installed in the front hall.

It was discovered that the houses with the electric meter in the hall used 1/3 less energy."

She explains that this is because the feedback loop was available for all to see.

This is the main purpose of business intelligence. To provide a feedback loop that provides accountability at all levels of an organization.


Donella H. Meadows Quote
2010-04-09T17:25:00.000-07:00
"Purposes are deduced from behavior, not from rhetoric or stated goals."
Donella H. Meadows
Thinking in Systems

"An important function of almost every system is to ensure its own perpetuation."
Same as above

Donella is not speaking of software systems, but rather behavior systems. She's speaking about the war on drugs, welfare, health care, national security, the environment, or any other societal or natural system.

"Keeping sub-purposes and overall system purposes in harmony is an essential function of successful systems."
Same as above

"We misperceive risk, assuming that some things are much more dangerous than they really are and others much less. We live in an exaggerated present - we pay too much attention to recent experience and too little attention to the past, focusing on current events rather than long-term behavior"
Donella H. Meadows
Thinking in Systems

"Usually the greater the discrepancy between the goal and the actual situation, the more emphatic the action will be."
Same as above

"The most damaging example of the systems archetype called "drift to low performance" is the process by which modern industrial culture has eroded the goal of morality.  The workings of the trap have been classic, and awful to behold."
Same as above

"We know what to do about drift to low performance.  Don't weigh the bad news more heavily than the good.  And keep standards absolute."
Same as above


Microsoft OneNote, an Answer to Prayer
2010-04-09T15:18:00.000-07:00


Since my business is growing, I find that I'm forgetting more and more things. If it doesn't fit neatly in a todo or a calendar event, I have trouble keeping track of it.

I've been asking people all over what they did to keep track of non-actionable data. Most of them had no answers for me. Beau Merseraeu said that he's using OneNote. I'm well-acquainted with the other Office products, but OneNote always seemed like tablet software to me. I was wrong.

If you read a quote that you want to remember, throw it in OneNote. If you see statistics to help you with a sales presentation....OneNote. I'm using it more and more. It's like having an online journal, but it is organizable, which allows for fast data retrieval. I love it and wish I hadn't wasted so much time not using it.


DigiGirlz - Blowing Away the Girl/Programmer Myths
2010-04-05T09:57:00.000-07:00

I spent last Friday volunteering with DigiGirlz. This awesome event, sponsored by Microsoft, exposes High School age girls to technology. In one day, they learn programming, database querying, robotics, and video game programming. In between classes, they meet IT professionals who explain what life is like when you choose a career in technology. All of the girls had a great time and learned a lot.

Here are some myths that DigiGirlz helps debunk:

Myth #1: Girls don't like programming, math, or science.

False. While teaching some of these classes, I never saw a student waver in her interest. I never caught one texting on her cell phone. I never saw one not complete an assignment. When the programming class was over, many of them were disappointed that it ended. Many asked how they could program at home on their own time. Most of them thirsted for more.

Myth #2: Girls aren't good at programming.

Totally false. During a one hour SmallBasic class, I saw many students immediately understand the language constructs. Some girls only needed about 10 minutes of direction and they were free styling. By the end of the class, the girls were shocking me with the images and vectors they were programmatically drawing. They fluently understood the math, and could quickly grasp the creation of geometric shapes. I've seen adult programmers who struggled with concepts that the girls immediately learned.

Myth #3: Girls who do like technology and programming are basically boys (or aren't very cute.)

Lynn Langitt (SoCalDevGal) and Linda Routson (Microsoft Account Manager Extraordinaire)  did a great job finding female instructors for these courses. They were all beautiful women who were professional, intelligent, and feminine. One was a programmer for a fashion company. These examples taught the girls that you can be good at traditional male professions without surrendering what you like about being a girl.

I think any girl would enjoy this event.  Find more information here:

DigiGirlz




ASP.NET AJAX Still Flickering and Posting Back
2010-03-30T15:53:00.000-07:00
We looked everywhere to solve this problem, and finally found this blog post.  Neil clearly outlines the solution to our issue and it worked for us.

neil kilbride's blog


Mind Map Software - Free Mind
2010-03-18T11:53:00.000-07:00

A friend of mine, Chris York, turned me on to mind mapping as a way to flush out projects and requirements.  I love it.  

Mind mapping is a way to document and diagram meeting notes, while the meeting is taking place.  It allows the meeting to be a freeform expression of thoughts, while maintaining the general topic.

During meetings, conversations tend to move from one topic to another, seemingly directionless.  This is an excellent place to capture salient points about a software or technology project, without overcontrolling the meeting.  It can also help to avoid repetition, because you can go to a branch on the map and add or remove detail as needed.

You can also use mindmapping outside meetings to organize your own thoughts and projects.  It will help you take a general idea and create concrete action items and ToDos.

Here's some info on mindmapping:

Mind Map Wikipedia Entry

Here's FreeMind, an open source, java software package that will help you implement it:

FreeMind


Quote from David Kekich and others.
2010-03-04T17:15:00.001-08:00
Anxiety is caused by a lack of control, organization, preperation, and action.
-David Kekich

Think like a man of action, act like a man of thought.
-Henry Bergson

It is not enough to stare up the steps; we must step up the stairs.
-Vaclav Havel


Thomas Edison and Bill Maher
2010-03-04T10:19:00.000-08:00
Here are a couple more quotes:

"People would rather be nice than right, rather be sensitive than be true.  Well, being nice and sensitive are important, but they're not more important than being right; they're not more important than the truth."
Bill Maher

"Opportunity is missed by most people because it is dressed in overalls and looks like work"
Thomas Edison


Favorite Quotes
2010-03-04T09:09:00.001-08:00
I was out to dinner with some friends and was going through a few of my favortie quotes.  I realized that I don't have any posted, so here they are:

*All comments are written in first person by Ike.  
"Reduce everything to it's essence, so that form harmonizes with function"
Cris Kobryn 
Chair, U2 Partners' UML 2.0 Submission Team 

"If we pursue the parsimony koan to its full intent, we should find UML modeling projects to be as enjoyable as we found finger-painting and drawing classes in grammer school." 
Cris Kobryn
Chair, U2 Partners' UML 2.0 Submission Team 

"Do not rely on Rigid, Inefficient Processes (RIP)." 
Steve McConnell 
Software Project Survival Guide

"Early in the project you can have firm cost and schedule targets or a firm feature set, but not both." 
Steve McConnell 
Software Project Survival Guide 

"There is a word for software that cannot be changed after delivery. That word is hardware." 
Ward Cunningham 
co-Inventor of Extreme Programming
Preface to Extreme Programming Pocket Guide 

"The problem with quick and dirty is that dirty remains long after quick is forgotten." 
Unknown 

"As a programmer writing computer programs, you must develop a very special relationship with a shadowy being who lurks just beyond the periphery of your vision. At the same time needy, confused, angry, and malicious, that person is known to you only as The User." 
Charles Petzold
Programming in the Key of C# 

"Some people hold the view that the UML should be used to create a design that is independent of the programming language that's used for implementation. Others believe that language-independent design is an oxymoron, with a strong emphasis on the moron." 
Martin Fowler
UML Distilled 
I agree with this on most topics, not just on software design, ie database design, T-SQL authoring, multi-platform development. -Ike

"Question: What is the difference between a methodologist and a terrorist?
Answer: You can negotiate with a terrorist." 
Martin Fowler
UML Distilled 

"You should use iterative development only on projects that you want to succeed.
Perhaps that's a bit glib, but as I get older, I get more aggressive about using iterative development." 
Martin Fowler, espousing Extreme Programming
UML Distilled 

"Often, you'll find that the more stable packages tend to have a higher proportion of interfaces and abstract classes." 
Martin Fowler, espousing Polymorphism
UML Distilled


"The real moments of discovery aren't when someone shouts "Eureka", but when someone mumbles, "Hmmm....that's wierd." " 
Carl Sagan 

"Too often, however, the metaphor is used to assert that the child class inherits the internals of the parent class, an allusion to the fact that biological organisms inherit the DNA structures of their parents. This is a poor and potentially misleading use of the metaphor....
The desire for a child class to inherit internals of its parent classes can be better accommodated if we change the notion of inheritance from DNA to assets. It has been noted that an object has access to whatever resources it needs to fulfill its behavioural expectations. " 
David West
Author of Object Thinking 

"The best programmers are up to 28 times better than the worst programmers, according to "individual differences" research. Given that their pay is never commensurate, they are the biggest bargains in the software field." 
Robert L. Glass
Author of Facts and Fallacies of Software Engineering


Agile Development - Not A License to Skip Steps
2010-02-12T06:02:00.001-08:00

We've all been part of software development projects that didn't have a very good project plan.  I believe that it is still possible for a project to be successful without a project plan, but it introduces unnecessary risk.  A properly agreed upon project plan will not eliminate that risk, but it will greatly reduce it.  Proejects without planning documents are about 50% successful.  When they have a plan, they are about 90% successful.  That is independent of developer talent.  

Often as agile developers, we skip some of the SDLC steps.  Agile does not mean that SDLC steps are skipped.  It just means that they are compressed into shorter, more frequent release cycles.

Here are the SDLC steps that are mandatory:

Planning/Design
Development
Review
Testing
Rollout
Support Plan

The two steps that are most frequently skipped or inappropriately shortened are arguably the most important: planning and testing.  This is just a friendly reminder to review how those steps are implemented and delivered.


Programmers Are Like Boy Scouts On A Hike
2010-01-06T14:17:00.000-08:00

I was reading "The Goal", by Eliyahu Goldratt, when I came across an analogy that was familiar.  I'm a scout leader, and so get plenty of opportunities to watch boy scouts hike on trails. I've noticed that when each one walks at their own pace, some boys will get to the end of the trail far faster than the slowest boy. If it's a two hour hike, one boy will finish in 90 minutes, while the slowest boy will finish in 2 1/2 hours.

If this were a software development department, that would mean that some developers would finish far ahead of others. The entire project would be delivered according the pace of the slowest developer.

When Boy Scouts march as a troop, the most amazing thing happens. They work together as a team. Two hour hikes are often completed between 1 3/4 hours and 2 hours. That means that the team environment compensates for the slowest guy far more than it slows down the fastest guy.

For a software development department, this increase of productivity is immediate. As the team gets used to working together, they get faster and faster, often sprinting and running towards finish lines. Each software project is delivered on or before the deadline, as long as we are implementing the principles of team development.

Now if I could just get the developers to "Be Prepared", we'd be all set.


Book Review - Marketing Metrics, by Paul Farris
2009-10-01T14:06:00.000-07:00

I have seen some very poorly run marketing departments.  Because of my past experience, I’ve been skeptical every time I deal with marketing.  This book made me reevaluate those feelings and grow to respect the new found science of marketing.   Marketing can be effective when metrics are measured and qualified.  While reading this book, I met Ron Hendricks, CMO for Northcentral University.  He runs his department like a lab, and he is very, very effective.  Thanks to this book and Ron, I admire a well-run marketing department and look forward to dealing with more of them.

The metrics in this book are well thought-out, with clear formulas, good examples, and thoughtful insights.  I think this should be on every executive’s desk.  These measures will make you rethink your business.   Hopefully, you’ll have new ideas on how it can grow.

Also, our business intelligence practice will be better equipped to interact with marketing executives, salesmen, and creatives.

Here's the Amazon link:

Marketing Metrics


Book Review - The McGraw-Hill 36-Hour Course In Finance for Non-Financial Managers, by Robert Cooke
2009-09-25T13:33:00.000-07:00

This book is perfect if you are not an accountant and one of the following things are true.

-You need to read financial reports
-You interact with executives
-Deal with CFOs or Controllers
-Prepare budgets
-Invest in companies

As a technology consultant, I’m often asked to help with difficult business problems, and more often than not, those issues deal with accounting principles.   I’ve tried reading Accounting 101 books, but I don’t need to know how to keep a "T" account.  I needed a summary book for Accounting 400, and this book fit the bill.

There is also an online test that will grant you a certificate of completion.  I didn't do that, but you might like to.

Here's the Amazon link:

The McGraw-Hill 36-Hour Course In Finance for Non-Financial Managers


Book Review, Hiring Smart by Pierre Mornell
2009-09-15T13:07:00.000-07:00

Before reading this book, I thought my philosophy of hiring fast and firing fast was a sound management principle.  I was wrong.  After reading the horror stories in this book, I am recommitted to interviewing thoroughly, completely a full background check, checking all references, and then proceeding cautiously.  I still believe that we should terminate employees very soon after discovering that they weren’t who we thought they were.

I also have a habit of being captivated and intrigued by everyone I meet.  I need time to let that fade away.  I need reality to set in before making an objective hiring decision. 

This book is filled with 45 great tips on hiring good people, but all of them don't need to be applied all at once.  In fact, I think that would be impossible. Instead, I intend to continuously evolve the hiring process by selectively applying these tips.   I’ll just keep experimenting until the perfect team is assembled.

I liked the idea of putting prospects on projects immediately.  For instance, I could show a prospect a user interface design and tell them to improve it.  I could show them a database and ask them to write a query.  

Mornell mentions that we should walk the prospect to his or her car.  He says you can tell a lot about a person by how they organize their car.  Mornell is a psychiatrist, and brings a unique insight to the hiring process.

Mornell mentions a 49er's Football scout that would not recommend a player until he observed 200 plays of the player.  He did this so he could feel comfortable with how they react in different situations. It's impossible to observer 200 plays when hiring people, but we should watch the person in a variety of situations before making a hiring decision.

This book was very easy to read, had very big type, lots of pretty pictures, and can be completed in about 2 hours.

Software development is increasingly becoming a team sport.  This book reinforced my belief that the team needs to be part of the entire hiring process.

Here's the Amazon link:

Hiring Smart


Book Review - Lean Thinking, by Womack & Jones
2009-08-07T12:20:00.000-07:00

Lean Principles are a hot topic in today's economy.  It isn't just about cost reduction; it's about observing every aspect of our lives and our businesses and making them more efficient.

I learned: 

1)  Muda, the Japanese word for waste, is everywhere and should be shunned and eliminated in every circumstance.

2)  Eliminating muda will make workers happier, reduce costs, and will simplify complicated processes.

3)  My new hero is Taiichi Ohno, who was the original anti-muda engineer. 

4) This book is full of examples of real world companies who applied these principles and did great things with them. 

5)  After reading this book, I now observe waste wherever I am.  I see it in the airport security line, at the DMV, when filing my taxes, at the hospital waiting room, and at the butcher counter.

Software Developers see waste every day, but often do nothing about it.  They are too worried about breaking code that is working.  Proper testing methods would give them the confidence they need to eliminate wasteful code.  I would remind them of their responsibility to remove code that does nothing, simplify complicated code, and to never repeat themselves.

Here's the Amazon link:

Lean Thinking


Book Review - The Copywriter's Handbook, by Bob Bly
2009-07-25T12:05:00.000-07:00


Before reading this book, I wasn’t clear on what a copywriter actually did.   A copywriter creates print and web content for marketing purposes.  They are wordsmiths who choose their words to capture their audience’s attention.

Here’s what I learned:

1)  We see the same words repeated in a variety of ads because those words are proven to sell, time and time again.

2)  Copywriter's sell things with their words, so every copywriter should look at the end product and ask, “is this going to sell something?”  Everything else is irrelevant.

3)  Copy (the text that copywriters produce) should tell a compelling story that keeps people reading.

Here's Bob Bly's website:

Bob Bly

Here’s the Amazon link:

The Copywriter's Handbook


Book Review - Getting Things Done, by David Allen
2009-07-10T11:50:00.000-07:00

Getting Things Done
 This book is a very quick read and worth the couple of hours it will take to get through it.

Here’s what I learned:

 1)  Manage your TODOs using an electronic medium.  Set a start date, a complete date, and a priority.  Don’t use your email as your to-do list.  A clear to-do list will clear your mind and lift a burden.  I tried this and it worked.

 2)  A good day means that we've completed 3 important things that day.  At the beginning of the day, choose which 3 things you will complete.

 3)  If your organizational system is simple, you are more likely to follow through with it and complete it.

Here's the Amazon link:
Getting Things Done


Differences Between SSRS Report Builder 1.0 and Report Builder 2.0
2009-06-09T09:19:00.001-07:00
I was going to draft a blog post on this topic when I found this one:

 Andrew Fryer's Blog

Andrew wrote this very clearly and I don't feel the need to add to it.


Book Review - The Power of Full Engagement By Jim Loehr
2009-06-05T11:30:00.000-07:00

This is a great book that taught me a lot about energy management.  I learned that mental energy should be conserved and used much like physical energy.  Here are my takeaways:

1) Manage your energy, instead of managing time.  This is contrary to the mountain of books written on time management.  This is the first book I've read on energy management.  When you workout, you have intervals of high energy, followed by a rest interval. The work day should follow the same pattern.  Now I'm trying to work in 90 minute sprints, followed by a 5 - 15 minute break.  I'm also devising a way to apply this when managing software developers on projects.

2) Stress is not always a bad thing, but can be very good if it results in positive results.  Stress also pushes us out of our comfort zone and enhances our abilities.  Constant stress is a bad thing.

3) Working out, eating well, and getting sleep will provide more energy to accomplish your goals and should not be ignored.  Bad eating and low activity creates negative stress and a loss of concentration. 

Here is the Amazon link:
The Power of Full Engagement


Internet Explorer Timer
2009-06-04T11:43:00.000-07:00
One of the chapters in "The Power of Less" focuses on Internet usage.  That led me to wonder how much time I spend on the Internet.  I looked for different applications, but couldn't find one that would simply tell me the total amount of time I use Internet Explorer.  I decided to write one myself.

This application simply times how long the iexplore.exe process is in memory.  I know that doesn't answer how long I'm on the Internet, but it should give me a pretty good idea.

You can download it here:

IE Timer

FYI, it requires the Microsoft DotNet Framework 2.0.  You probably already have it installed.

Here's a screenshot of my IE time as of this writing:




The Power of Less
2009-06-02T06:00:00.000-07:00

Overall, I enjoyed the book I learned the following things:

1) Get to the essentials in everything in life and ignore the rest. Eliminate things that do not add value towards a primary goal.

2) Only work on one primary goal at a time. Either lose weight or finish the degree or write the great American novel. Avoid spreading yourself too thin.

3) Focus on a single task until that task is accomplished. Trying to complete a project while keeping the cell phone on and watching for new email notifications is not very efficient. It also leads to a hectic and harried life. Turn off all outside extractions and immerse into a project. Time will go by faster and happiness will follow.

This book should have been 50 pages, rather than a 160. It is a tad repetitive and is easily skimmed.  You can also learn everything you need to know by looking at this guy’s blog:

Zen Habits

Here's the Amazon link:

The Power of Less


SQL Server 2008 Shrink Log File
2009-05-21T15:56:00.000-07:00
When DBAs wanted to shrink a log file in SQL 2000 and 2005, they would often backup the log with TRUNCATE_ONLY.  When they attempt to do the same thing in SQL Server 2008, they will be greeted with this message:

'TRUNCATE_ONLY' is not a recognized BACKUP option.

Here is a replacement script for SQL Server 2008:

USE Test
GO
ALTER DATABASE Test
SET RECOVERY SIMPLE
GO

DBCC SHRINKFILE(Test_Log, 10)

GO
ALTER DATABASE Test
SET RECOVERY FULL

This script puts the database in Simple Recovery mode.  It then shrinks the log file.  Finally, it puts the database back in Full Recovery mode.


New Index grayed out in SQL Server Management Studio
2009-05-18T10:51:00.000-07:00
In SQL Server Management Studio, if you right-click on the indexes and you see new index grayed out, it is because the table is schema-locked in another window.  Close the design view of the table and new index should now be black and usable.


Putting for Analytics
2009-05-14T15:01:00.000-07:00
I often do my best thinking on a putting green that is conveniently located about half mile from my desk.  

Recently, I was all lined up, taking a practice swing, and it was perfect, very smooth.  My practice swings are usually pretty good, I remember and focus on all the technique I have learned from coaches, lectures, books, experience.  I swing freely and effortlessly.  

Things only seem to go awry when I put a ball in front of me.  I stop swinging freely, I tense up, tighten my grip, and both my swing and the ball become erratic.  I have spent years attempting to recreate my practice swing with a ball present, I am sometimes successful, sometimes less so, but I’m always a scratch golfer when I golf without a ball. 

Why is there a difference between my practice swing and my real swing?  I think it's because when the ball is there, I become so focused on performance that I no longer think about the process that gets me the putt I am after.  

I have found the same to be true about a performance focused mindset in business intelligence.  Companies spend much less time examining the fundamentals of what got them to where they are in the first place.  While examining the reports of a standard company, all focus is directed on dissecting "What happened?"  When companies are only performance-oriented, much time is spent analyzing sales by product, by territory, by sales person, by location, or by time of the year.  Retail organizations look sharply at sales during "the season", while time and billing firms examine their "top performers" with a keen interest.  But how much do these things impact the future?  Regardless of what the answer might have been in the past, our current recession has made it very clear that the companies that are solid are those who focused on sound fundamentals, on the process.  How effective is it now to examine December 2007 sales?  Will those figures have any impact or accurate prediction on what sales will be like in December 2009?  What about December 2010?  It is likely that if companies continue to be overly performance focused, reactions will be tense and erratic. 

I see examples of this behavior in the news, all the time.  The latest government stimulus packages are very reactive, with little thought put into goals, impact, future.  Wall Street is too results oriented.  Every time a firm posts numbers that are shorter than expectations, there is a direct impact on stock price, which affects the longevity of that organization.  As companies focus on current numbers, avoid creating a vision, and the process to implement it, they too compromise the future.  

So I loosen up, I remember what I have been taught and what I have learned from experience.  I know how to do this.  I remind myself that keeping an eye on the ball is important...but I will never make it to the hole/goal if I lose my grip on the club.


SSRS 2008 List All Reports, Users, Roles, and Security
2009-05-12T13:41:00.000-07:00
Someone on the newsgroups today asked the following question:

Is there any report that can be run in Reporting Services that lists
all reports and the users that have access?


I assumed he meant that he wanted a list of all the reports he has, and a list or roles and the users that are in them.  I quickly wrote the following query for him.  I hope some of you find it useful:

SELECT u.UserName, c.[Path], c.[Name], r.RoleName,  * 
 FROM Users u 
   JOIN PolicyUserRole pur
 ON u.UserID = pur.UserID 
   JOIN Policies p 
 ON pur.policyID = p.PolicyID
   JOIN Catalog c 
 ON p.policyID = c.policyID 
   JOIN Roles r 
 ON pur.RoleID = r.RoleID 
        ORDER BY u.UserName, c.[Name], r.RoleName


Book Review - 10 Days to Faster Reading
2009-05-05T09:39:00.000-07:00

I loved this book, and I think all software developers and business stakeholders should read it. We all have a lot to read, and getting through some of it quickly will give us back the gift of time.

Here is what I learned:

1) In general, authors are repetitive, so once you get the point, and you don't need to see the supporting evidence used to reinforce it, just skip the rest and find the next point.

2) Read with an index card covering the line and you'll stay focused and read faster. I don't find that particularly enjoyable. I only do this when there's a book I have to read for work, like a standards specification, or something really boring.

3) If I read 150 words per minute or 400 words per minute, my comprehension stays the same, around 80%. So if my comprehension is the same, I might as well read fast.

If you want to buy this book, here's the Amazon link:

10 Days to Faster Reading


Book Review:  Getting Started with Consulting
2009-04-12T13:26:00.000-07:00

I just finished reading "Getting Started with Consulting" and I loved it.  I've been a consultant for nine years.  This book did three things for me:

1)  It confirmed the things I was doing right, but felt like I was the only one doing them.
2)  It told me to stop doing things that weren't working, but I kept trying anyway.  I suppose I did this because I felt like everyone else was doing them, so why shouldn't I?
3)  It gave me important insight into consulting as a business.  It taught me how to market, how to arrive at good pricing, and how to network more efficiently.

In this job market that many of you are looking for new positions.  If you are considering a consulting practice, this book will be an excellent beginning.

Amazon Link


Excel at Excel (Or My Quick Tips for MS Excel)
2008-11-06T08:31:00.001-08:00
In this space, sometimes I'll offer strategic insight into the Business Intelligence space, and sometimes I'll offer helpful tips to the grunts* in the field, actually doing the heavy lifting of reporting, analyzing, and planning.  These tips will cover mostly Microsoft products, since that’s what I specialize in.  I have tips for SQL Reporting Services, SQL Integration Services, SQL Analysis Services, MDX, T-SQL, and most importantly, Microsoft Excel.

The major arrow of every analyst's quiver is Microsoft Excel.  Without exception, it is heavily used at every single one of my customers.  I won't give away all of my tips now.  What would keep you coming back?  Certainly not my charming personality...
Here are a few overlooked gems that Microsoft Excel offers you:

-By default, when you look at a spreadsheet, you see the results of the formulas, not the formulas themselves.  You can manually set "Show Formulas", or you can just click CTRL-' (that a single quote.)  Usually you can find that key just to the left of the 1 key on the top row of your keyboard.

-You can look at the same workbook in two different windows.  You can keep "Show Formula" on one view of the workbook and the default results of the formulas on the other view of the workbook.  This works great if you have multiple monitors, displaying each view on its own monitor.

-In Excel 2007, the status bar is better than ever.  Highlight some cells and look at your status bar at the bottom of the screen.  You'll see a count, sum, and average right in front of you.  In addition, right-click on the status bar and check out all of your options.  You'll be surprised at what you can view down there.

-Most people know how to save a workbook, but did you know you can save an entire workspace?  When you work on those month-end reports, and you find yourself looking at the same four worksheets over and over again, save it as a workspace.  The next time you work on month-end, just open up the workspace and those four worksheets will open right in front of you.  It's hidden on the View tab of the ribbon.  It's called "Save Workspace."

-If you want the same report to open each time you start your computer, just follow these steps.  First, put a shortcut to MS Excel in the Startup folder of your Start menu.  Then find the startup folder of MS Excel.  Put your workbook in there.  You can usually find it at "C:\Users\Ike(unless your name is not Ike, then just use your name)\AppData\Roaming\Microsoft\Excel\XLStart".  

That's it for now.  Keep checking back for more quick tips and hints.

*I use the word “grunts” with nothing but respect and fondness.


QlikView vs Gemini
2008-10-15T13:41:00.000-07:00
Last week, at Microsoft's Business Intelligence Conference, I was excited to see that QlikView had a booth.  There's been a lot of buzz about this little company.  Gartner recently named them as a challenger in the BI space.  They are positioning themselves as a BI solution that the business users control with very little IT involvement.  As far as I can tell, here are their main selling points:

-No IT-built cubes.  I can see why this is so attractive.  Most contemporary business users see IT as their main stumbling block.  Business users hate waiting so long to see business requirements finally make their way into the cubes.  QlikView does this by creating something called "in-memory" cubes.  Supposedly, you create the cubes using the interface and it stores them internally for you.  All you need is access to the data warehouse.

-Lightening-fast queries.  Because the data is stored in memory, response time is minimized and performance is increased.

-Intuitive user interface design.  I didn't find it all that intuitive.  It was OK, but I thought it was a little clunky.

Anyway, I think this company has some really neat technology, but it probably doesn't have much of a future on the Microsoft platform.  Microsoft headlined Project Gemini at the conference.  It seemed to me to be the exact same product, including using "in-memory cubes."  They also used the phrase "empowering business users."  That is the same phrase the QlikView booth guys said about their product.  Microsoft demo'd Gemini and it looked exactly like MS Excel.  They showed how they can build in-memory cubes, upload it into SharePoint, and share them with other business users.  Microsoft said that Gemini was just another cube storage option.  You’ll be able to choose between MOLAP, HOLAP, and in-memory cube storage.  It was pre-beta (probably pre-alpha) and I think it spells bad news for QlikView.  We’ll see how quickly Microsoft can get this shipped to us.

Here are some links for you:

http://www.qlikview.com/home.aspx
http://www.microsoft.com/Presspass/press/2008/oct08/10-06BI08PR.mspx


The Ideal Customer: One Person Who Calls The Shots
2008-08-11T20:56:00.001-07:00
Further reading from Mr. Parmenter’s “Key Performance Indicators”, unveiled this nugget; “Any layer between the CEO and the [KPI] team indicates that Step 1 has not been successfully achieved.  This point is so important that the project should not proceed if the CEO does wish to be involved in this way.”

Mr. Parmenter’s point is that the KPI project team should report directly to the CEO and no one else.

I was just talking to my oft-quotable, former business partner, Roy Allen.  He said, “I only want to work with customers who have a single person who is responsible for the entire organization, and that person mandates what the other employees of that organization do.”

In other words, Roy likes decision makers who actually make decisions and he wants to report directly to that decision maker.  He feels like that’s the only way to ensure success of his projects at that organization.

I don’t know about Roy’s projects, but Business Intelligence projects need a top-down mandate in order to succeed.  I don’t always get to report to the CEO, but I love it when I do.


I'm Not Pete Rose
2008-08-05T20:50:00.000-07:00
I went to Lake Havasu with my old business partner, Roy Allen, this weekend.
While we were out boating with our families, hanging out in the nice cool lake, avoiding the 120 degree heat, he said something pretty interesting.
He said, "You know your problem, Ike? You suffer what most of us suffer from. You are a terrible player-manager."

After I got over my shock, I asked him what he meant by that. He explained that there hasn't been a really good player-manager in baseball since Pete Rose, and even he wasn't all that great. He said that in a services firm, there are players (billers) and there are managers, and you can't be really good at both. You can stink at both, or stink at one, but being good at both is not an option. He explained that someone has to play the game and someone has to watch the players objectively, because only the manager will have good insight on how the game is progressing.

After some thought, I totally agree. And since I find consulting, teaching, and creating solutions so much fun, it looks like I'm going to need a full-time manager for the rest of the staff.


Don't Rush to Buy Software
2008-07-15T20:55:00.000-07:00
I was reading “Key Performance Indicators”, by David Parmenter today, and came across an interesting passage.   Mr. Parmenter says, “Much can be done with existing application such as Excel, PowerPoint, SharePoint Team Services, and Access.  There is often no need to purchase specialized software at this stage.  Any such purchases can be done more efficiently and effectively 12 months down the track.”

Wow, finally someone is speaking my language.   Managing a business through effective KPIs is such a drastic culture change, that most organizations fail at it.   Instead of introducing new tools and applications, organizations should really take a hard look at the software they already own and use.  Once the KPI project is in place and a seamless part of the atmosphere of the company, then new and more effective tools can be researched.

Though Mr. Parmenter is a technology agnostic consultant, I find it interesting that he lists all Microsoft applications.  I suppose he does this because it is safe to assume that most organizations already own these products.


Down the Rabbit Hole
2008-06-30T20:57:00.000-07:00
At the beginning of Brian Larson's book, "Delivering Business Intelligence with Microsoft SQL Server 2005", the author quotes Alice's Adventure in Wonderland.

"Would you tell me please, which way I ought to go from here?" asked Alice.
"That depends a good deal on where you want to get to," said the Cat.
"I don't much care where," said Alice.
"Then, it doesn't matter which way you go," said the Cat.

The Cheshire Cat reminds us how important goals and direction are to leadership. All the immaculate data in the world won't help an organization if it doesn't have a clear idea on where it wants to go. Unfortunately, it is sometimes a chicken and the egg situation. Leadership has a hard time mapping direction when it doesn't have a clear idea on the current state of the company.

This analogy provides an excellent case on why monitoring projects should be implemented before planning projects.


SQL Server Support with VMWare
2007-12-06T13:42:00.000-08:00
I have a client who is running SQL Server 2005 Standard Edition on a VMWare ESX operating system.  I wondered if Microsoft supported this environment and did some digging.  I had some difficulty finding current information, so once I found it, I thought I'd share it with you.

As of November 2007, Microsoft says they do not support third party software virtualization.  They require that you reproduce the issue in a non-virtualized environment before they will help you.  Here's the link confirming that:

http://support.microsoft.com/kb/897615


Tracing Events Appropriately
2007-11-25T07:10:00.000-08:00
When using SQL Server Profiler, it is generally a good idea to only trace COMPLETED events, instead of starting events.

Completed events have all necessary performance information. The starting events do not have important performance statistics because they haven't been collected yet. This will reduce the load of tracing and give you less data to filter out later.


Performance Tuning and the Heisenberg Uncertainty Principle
2007-11-11T06:48:00.000-08:00
Often when I visit a new client, they say, "We need you to start SQL Server Profiler and tell us why our server is so slow."

I'm reluctant to immediately resort to the SQL Server tracing utilities because of the performance load that they cause. Sometimes, I explain to the Heisenberg Uncertainty Principle:

http://en.wikipedia.org/wiki/Uncertainty_principle

and similarly the Observer Effect:

http://en.wikipedia.org/wiki/Observer_effect

Developed by Werner Heisenberg in 1927, this principal states that when you measure something, there is uncertainty caused by the measuring process. For instance, if we write our trace logs to a file and we're looking for disk contention, the tracing itself might be contributing to it. The uncertainty might also be caused by the measuring logic. We might be looking at excessive disk contention and determine that we need faster disks. But what if memory paging is causing the pages to be written to disk? In that case, our measuring logic was flawed, and we would need to add more RAM to resolve the performance bottlenecks.

So before I start SQL tracing, I like to use other tools first. Task Manager, System Monitor, Activity Monitor, and DMVs will tell us an awful lot about a server's performance before tracing is even needed. This allows me to observe the server's performance with an open mind, before using invasive tracing utilities.


Max Column Length
2007-10-21T11:01:00.000-07:00
This script will return all the columns in a table and the maximum length of all data in them.

DECLARE ColumnList CURSOR
FOR
SELECT c.[name] AS ColumnName
FROM sys.Columns c
JOIN sys.Tables t
ON c.Object_ID = t.Object_ID
WHERE t.Name = 'reyAASerWIP'
DECLARE @ColumnName varchar(30)
OPEN ColumnList
FETCH NEXT FROM ColumnList INTO @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @ColumnName
EXECUTE('SELECT MAX(LEN(RTRIM(' + @ColumnName+ '))) AS ' + @ColumnName + ' FROM reyAASerWIP ')
FETCH NEXT FROM ColumnList INTO @ColumnName
END
CLOSE ColumnList
DEALLOCATE ColumnList


Importing CSVs with SSIS
2007-10-14T19:56:00.000-07:00
The CSV import process is much more difficult with SSIS than it was for DTS. Scanning the MS newsgroups it looks like many of the problems will be corrected in SQL Server 2008. That's a long ways off, so let's look at some of the issues and how we can solve them.

Our CSV looks like this:

1- Name, Address, PhoneNumber, InvoiceAmount
2- "Fred Flintstone", "365 Stone Ave, BedRock, StoneAge", "928-555-1212", "25.00"
3- "Barney Rubble, "152 Granite Street, BedRock, StoneAge", "928.555.6326"
4- "Mister Slate", "52 Diamond Road", BedRock, StoneAge", "928.555.5332", "14.00"

The first problem is that there are commas enclosed by quotation marks in the Address field. SSIS will put BedRock as the PhoneNumber and StoneAge as the InvoiceAmount. This is very easy to solve. In the properties window of the Flat File Connection Manager, you will see a textbox labeled "Text qualifier." Just put a quotation mark in that field and it will see that the address column contains commas.







The next issue is that record #3 does not have the appropriate number of columns. It is one column short. DTS would handle this by inserting NULLs in the missing fields and then moving on to the next record. SSIS chokes on this. It will attempt to put "Mister Slate" in the InvoiceAmount field of record #3.

To solve this issue, I wrote the following script. I had to write it in VB.NET, since SSIS does not allow C# quite yet.


Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports System.Text
Public Class ScriptMain
Public Sub Main()
Dim myLineCount As Int32 = 0
Dim myFilePath As String = Dts.Connections("FlintstoneCSV").ConnectionString
Dim myNewFilePath As String = Dts.Connections("NewFlintstoneCSV").ConnectionString
Dim myNewFile As FileInfo = New FileInfo(myNewFilePath)
Dim myNewFileStream As FileStream = myNewFile.Create()
Dim myFileStream As FileStream = New FileStream(myFilePath, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite)
Dim myStreamReader As New StreamReader(myFileStream)
Dim myStreamWriter As StreamWriter = New StreamWriter(myNewFileStream, Encoding.ASCII)
Dim myLine As String
Try
myLine = myStreamReader.ReadLine
While Not myStreamReader.EndOfStream
Dim myOffSet As Int32 = 1
Dim myBeginIndex As Int32
Dim myEndIndex As Int32
Dim myCount As Int32 = 0
Dim myColumnCount As Int32 = 41
Dim myCommaCount As Int32 = 0
Dim myChar As Char
'myLineCount += 1
'Dts.Events.FireInformation(0, "Cleaning CSV", "Processing Line " + myLineCount.ToString(), "", 0, True)
Dim myLineChars As Char() = myLine.ToCharArray()
While myCount <= myLineChars.Length - 1 If myLineChars(myCount) = """" Then myCount = InStr(myCount + 2, myLine, """") Else If myLineChars(myCount) = Convert.ToChar(",") Then myCommaCount += 1 End If myCount += 1 End If End While Dim myCommas As String = "" If myColumnCount - myCommaCount > 0 Then
myCommas = New String(Convert.ToChar(","), myColumnCount - myCommaCount)
End If
myStreamWriter.WriteLine(myLine.Replace(vbCrLf, "") + myCommas)
myLine = myStreamReader.ReadLine
End While

Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.Events.FireError(0, "Cleaning CSV", "Encountered Error " + ex.Message _
+ " While Processing Line " + myLine, "", 0)
Dts.TaskResult = Dts.Results.Failure
Finally
myStreamReader.Close()
myStreamReader.Dispose()
myStreamWriter.Flush()
myStreamWriter.Close()
myFileStream.Close()
End Try
End Sub
End Class


Originally, I put a call to DTS.Events.FireInformational to tell us how many records have completed processing. It turns out that calls to DTS.Events are very, very slow. I removed those lines and processing was much, much faster.


I recently imported a CSV file with very dirty data. It included fields that will stretch across multiple rows and fields with quotes in the data. After struggling with SSIS, I finally decided to write my own CSV importer. As I was beginning my project and doing CSV research, I stumbled upon this project, called "A Fast CSV Reader."

http://www.codeproject.com/cs/database/CsvReader.asp


I used this code, along with a SQLBulkCopy object from DotNet 2.0. It imported 9 million large rows of dirty data in just a few minutes. I was amazed. What cleaning it didn't do, I will do in a SPROC.

Here is the extent of the code I wrote myself:


using (LumenWorks.Framework.IO.Csv.CsvReader
myCSVReader = new LumenWorks.Framework.IO.Csv.CsvReader(
new StreamReader("c:\\Data\\Flintstone.csv", Encoding.ASCII), true,
Convert.ToChar(",")
, Convert.ToChar("\""), Convert.ToChar("\\"), Convert.ToChar("#"), true))
{
myCSVReader.DefaultParseErrorAction = ParseErrorAction.ThrowException;
myCSVReader.MissingFieldAction = MissingFieldAction.ReplaceByEmpty;
myCSVReader.SkipEmptyLines = true;
myCSVReader.SupportsMultiline = true;
using (SqlBulkCopy myBulkCopy = new SqlBulkCopy(SqlHelper.ConnectionString))
{
myBulkCopy.DestinationTableName = "Destination";
myBulkCopy.BulkCopyTimeout = 30;
myBulkCopy.BatchSize = 10000;
myBulkCopy.WriteToServer(myCSVReader);
}
}
MessageBox.Show("Import Success");



Format dates and times in T-SQL
2007-09-22T13:08:00.000-07:00
I stole this script off of the internet a few years ago.  I wish I could give the author credit for it, but I can't remember who he is.  If anyone knows, let me know and I'll add him to this post.

This function allows you to do date/time formatting just like you do it in C# or VB.NET.

I find it enormously useful.  My code is littered with calls to it.


USE [ncu]
GO
/****** Object:  UserDefinedFunction [dbo].[FormatDateTime]    Script Date: 01/27/2011 17:31:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[FormatDateTime]
                      (@f_datetime DATETIME
                     , @f_format   VARCHAR (60))
RETURNS VARCHAR (60)
AS
/*
||======================================================================
|| Purpose:  Provide a more intuitive method of converting DATETIME
||           values to character.  Only the parts specified within
||           the input format request are sent back, and any other
||           characters are returned in their respective places.
||
||           Here are the special-request symbols:
||
||           [YY] or [YYYY]   -> year
||           [MM] or [M]      -> month (numeric; 'M' means one digit
||                                      where possible)
||           [DD] or [D]      -> day
||           [DWEEK]          -> day of week
||           [DWK]            -> 3-characters day of week abbreviation
||           [MON]            -> 3-character month abbreviation
||           [MONTH]          -> entire month name
||           [HH] or [H]      -> hour
||           [24HH] or [24H]  -> 24-hour clock
||           [mm] or [m]      -> minute
||           [NN] or [N]      -> minute
||           [SS] or [S]      -> second
||           [AA] or [PP]     -> 'AM' or 'PM'
||           [AM] or [PM]     -> 'AM' or 'PM'
||
||           Every other character is considered part of the output.
||======================================================================
*/
BEGIN
  /*
  ||====================================================================
  || Data declarations...
  ||====================================================================
  */
  DECLARE @datetime    VARCHAR (60)
        , @symbol      VARCHAR (7)
        , @translation VARCHAR (9)
        , @hhmmss      CHAR (8)
        , @year        INT
        , @month       INT
        , @day         INT
        , @hour        INT
        , @minute      INT
        , @second      INT
        , @pos         INT
        , @pos2        INT
        , @len         INT

  /*
  ||====================================================================
  || Validations...
  ||====================================================================
  */
  /*
  ||====================================================================
  || A NULL input earns a NULL output...
  ||====================================================================
  */
  IF @f_datetime IS NULL
  OR @f_format   IS NULL
--THEN
    GOTO FNC_EXIT
--END IF

  /*
  ||====================================================================
  || End of validations...
  ||====================================================================
  */
  /*
  ||====================================================================
  || Main logic...
  ||====================================================================
  */
  /*
  ||====================================================================
  || Acquire the value of each of the date-time components in integer
  || form...
  ||====================================================================
  */
  SELECT @hhmmss   = CONVERT (VARCHAR, @f_datetime, 8)
       , @datetime = @f_format
  SELECT @year     = YEAR  (@f_datetime)
       , @month    = MONTH (@f_datetime)
       , @day      = DAY   (@f_datetime)
       , @hour     = CONVERT (INT, LEFT (@hhmmss, 2))
       , @minute   = CONVERT (INT, SUBSTRING (@hhmmss, 4, 2))
       , @second   = CONVERT (INT, RIGHT (@hhmmss, 2))

  /*
  ||====================================================================
  || For users' convenience, we allow small-case m to denote minutes
  || large-case M to denote month; or they can use the unintuitive
  || 'N' to denote minutes.
  ||
  || However, in order to support case-seensitivity even in a case-
  || insensitive environment, the code below locates each special symbol
  || within the specified format and replaces small-case '[m]' and
  || '[mm]' with '[N]' and '[NN]', or else all other small-case letters
  || with their large-case equivalents...
  ||====================================================================
  */
  SELECT @pos = CHARINDEX ('[', @datetime)

  WHILE @pos > 0
  BEGIN
    SELECT @pos2 = CHARINDEX (']', @datetime, @pos)

    IF @pos2 = 0
  --THEN
      SELECT @pos = 0
    ELSE
      BEGIN
        SELECT @len    = @pos2 - @pos + 1
             , @symbol = SUBSTRING (@datetime, @pos, @len)

        IF CONVERT (VARBINARY (120), @symbol)
         = CONVERT (VARBINARY (120), '[m]')
      --THEN
          SELECT @symbol = '[N]'

        ELSE IF CONVERT (VARBINARY (120), @symbol)
              = CONVERT (VARBINARY (120), '[mm]')
      --THEN
          SELECT @symbol = '[NN]'
        ELSE
          SELECT @symbol = UPPER (@symbol)
      --END IF

        SELECT @translation =
          CASE @symbol
            WHEN '[YYYY]' -- year
            THEN CONVERT (CHAR (4), @year)
            WHEN '[YY]'   -- year
            THEN RIGHT (CONVERT (CHAR (4), @year), 2)
            WHEN '[MONTH]'
            THEN UPPER (DATENAME (MM, @f_datetime))
            WHEN '[MON]'
            THEN LEFT (UPPER (DATENAME (MM, @f_datetime)), 3)
            WHEN '[M]'
            THEN CONVERT (VARCHAR, @month)
            WHEN '[MM]'
            THEN RIGHT ('0' + CONVERT (VARCHAR, @month), 2)
            WHEN '[D]'
            THEN CONVERT (VARCHAR, @day)
            WHEN '[DD]'
            THEN RIGHT ('0' + CONVERT (VARCHAR, @day), 2)
            WHEN '[DWEEK]'
            THEN UPPER (DATENAME (DW, @f_datetime))
            WHEN '[DWK]'
            THEN UPPER (LEFT (DATENAME (DW, @f_datetime), 3))
            WHEN '[H]'
            THEN
              CASE
                WHEN @hour BETWEEN 1 AND 12
                THEN CONVERT (VARCHAR, @hour)
                ELSE CONVERT (VARCHAR, ABS (@hour - 12))
              END
            WHEN '[HH]'
            THEN
              CASE
                WHEN @hour BETWEEN 1 AND 12
                THEN RIGHT ('0' + CONVERT (VARCHAR, @hour), 2)
                ELSE RIGHT ('0' + CONVERT (VARCHAR, ABS (@hour - 12)), 2)
              END
            WHEN '[24H]'
            THEN CONVERT (VARCHAR, @hour)
            WHEN '[24HH]'
            THEN RIGHT ('0' + CONVERT (VARCHAR, @hour), 2)
            WHEN '[N]'
            THEN CONVERT (VARCHAR, @minute)
            WHEN '[NN]'
            THEN RIGHT ('0' + CONVERT (VARCHAR, @minute), 2)
            WHEN '[S]'
            THEN CONVERT (VARCHAR, @second)
            WHEN '[SS]'
            THEN RIGHT ('0' + CONVERT (VARCHAR, @second), 2)  
            ELSE
              CASE
                WHEN @symbol IN ('[AM]', '[PM]', '[AA]', '[PP]')
                THEN
                  CASE
                    WHEN @hour < 12
                    THEN 'AM'
                    ELSE 'PM'
                  END
                ELSE @symbol
              END
          END

        SELECT @datetime = STUFF (@datetime, @pos, @len, @translation)
             , @pos      = CHARINDEX ('[', @datetime, @pos + LEN (@translation))
      END
  --END IF
  END

FNC_EXIT:
  RETURN @datetime
END



Do Your Foreign Key Columns Have Indices?
2007-09-20T07:03:00.000-07:00
When I do a database audit, I always check for the existence of foreign keys that enforce referential integrity. That is often a great sign on whether or not a data-centric developer has been involved in the project. Once foreign keys have been created, indices must manually be configured, for SQL Server does not create them automatically.

I wrote this script to tell you if you have indices on all of your foreign key columns:

SELECT o.[Name] AS TableName, c.[Name] AS ColumnName
    FROM sysForeignKeys f
    JOIN sysObjects o
        ON f.fkeyID = o.[id]
    JOIN sysColumns c
        ON c.[ID] = f.fkeyID AND c.[colid] = f.fkey
    LEFT OUTER JOIN sysIndexKeys i
        ON f.[fkeyid] = i.[id] AND f.[fkey] = i.[colid] AND f.[keyno] = i.[keyno]
    WHERE i.[id] IS NULL

Even if referential integrity is being enforced at the application layer (GASP!), you should still have indices created on those virtual foreign key columns. Unfortunately, that will have to be audited manually by the application developer, which pretty much means it will never happen.


Hard Disk Configuration
2007-09-16T16:06:00.000-07:00
When asked where hardware dollars are best spent, I always recommend a great disk subsystem.

Hard drives are often the source of performance bottlenecks. Any dime spent on a high-performing hard drives will not go to waste.

Disks are also the component that will most likely fail. Matt, the Microsoft blogger, claims that disks have a failure rate of 2% per year per disk. If that's the case, then an investment in RAID arrays would be very wise.

As far as specific drive components go, it's difficult to make a recommendation without knowing the purpose of the SQL Server.

Here's Matt's post:

http://blogs.msdn.com/matt_hollingsworth/archive/2007/04/02/mtbf-is-useless-how-to-decide-when-to-use-raid-for-database-high-availability.aspx


No More DBCC SHOWCONTIG
2007-09-07T09:29:00.000-07:00
In SQL Server 2005, DBCC SHOWCONTIG has been replaced with the function sys.dm_db_index_physical_stats.  Use this function if you need to diagnose fragmentation and page usage issues. 

Pay attention to the fragmentation levels in the output.  This is reporting more accurately than in SQL Server 2000.

Here's a Books Online link explaining the new function:
http://msdn2.microsoft.com/en-us/library/ms188917.aspx

And a link on index fragmentation:
http://msdn2.microsoft.com/en-us/library/ms189858.aspx


Dedicated Administrator Connection
2007-09-05T09:23:00.000-07:00
In previous version of SQL, it was possible for the server to tie up so many resources, that one more connection was no longer possible.  For instance, you could have one process that was blocking all the others, and have no way to open the SQL Server tools to kill it.

That all changed with the DAC.  Here's the Microsoft article on how to use it:

http://msdn2.microsoft.com/en-us/library/ms189595.aspx

Kalen Delaney (Inside SQL Server 2005) has this great query for determining the SPID of the DAC:

SELECT t2.session_id
FROM sys.tcp_endpoints as t1
JOIN sys.dm_exec_sessions as t2   
    ON t1.endpoint_id = t2.endpoint_id
WHERE t1.name='Dedicated Admin Connection';  GO


Question of the Week
2007-09-04T06:00:00.000-07:00
What is the maximum number of SQL Server instances that can be loaded on one physical server?

Ready for the answer?

There is no real maximum, only a supported maximum.  On the Enterprise Edition, Microsoft supports 50 instances in a non-clustered environment.  Microsoft supports 25 instances on a cluster.  Other instances of SQL Server have a supported maximum of 16.

Here a link:

http://www.microsoft.com/technet/technetmag/issues/2006/03/SQLQA/


32-bit vs 64-bit SQL
2007-09-03T07:22:00.000-07:00
When it comes to hardware, most SQL Servers I have seen are over-powered.  Memory and CPU are most often underuntilized.  So when I'm asked whether a customer needs the 64-bit version of SQL server, I think it comes down to one question.  Do you need more than 3GB of memory addressed?

It's important to note that the 32-bit version of Windows can only address 4GB of memory.  By default, it reserves 2GB for itself, however this can be reduced to 1GB by using an operating system switch.  Memory beyond the 4GB addressed by the OS can be accessed by SQL Server indirectly using the PAE switch.

http://msdn2.microsoft.com/en-us/library/ms190673.aspx

The 64-bit version of SQL Server can address an incredible amount of memory directly, with no need of these work-arounds.


Free SQL Server 2005
2007-09-01T09:11:00.000-07:00
Just a friendly reminder that you can evaluate SQL Server 2005 for free for 120 days.  This is an excellent way to learn the features of the product before purchasing it.  It is the full Enterprise edition.  You can download it here:

http://www.microsoft.com/technet/prodtechnol/eval/sqlsrv05/default.mspx