Welcome Home !!

I realize I have been relatively quite for a while, but summer is actually a fairly busy time for us at work.  Finishing things up for the school year and getting ready for the next year.  However, today I am going to talk about something non-technical.

Friday night my youngest daughter and I went out to the Special Events Center on Fort Carson.  Four hundred soldiers of 4th Infantry Division 2nd Brigade Combat Team came home from Iraq.  We were there meet and bring home with us a soldier my oldest daughter had met before going into the Army herself.  Now we hadn't met this soldier yet, but my daughter had, and had been talking to him online and on the phone when he had the opportunity to call from Iraq.

As I stood inside the Special Event Center, I saw family and friends of returning soldiers.  Some were dressed to the nines, others in shirts and jeans.  Some kids wore t-shirts with the name of their mom or dad who was returning.  There was much excitement and anticipation, and you could literally feel it in the air.  To help pass the time for the young kids there was even a jumper that the kids could play in while waiting.  Music was playing, some people (particularly young kids in elementary and middle school, but some older) dancing.  Everyone had signs to welcome home their loved ones.  Some of the songs were patriotic country songs that I'll admit brought tears to my eyes as I thought about the sacrifices made by both the soldiers and their families.

Then they finally arrived.  They marched in while Toby Keith's "Courtesy of the Red, White and Blue" played.  The cheering that accompanied both was loud and raucous.  All became quite once they were all in the Special Events Center and they then played our National Anthem, "The Star Spangled Banner," which end with much more applause.  Following a prayer and a speech by the Commanding General of Fort Carson, the soldiers were dismissed.  That is when the real reunions started.  Wives and husbands and children hugging each other, some soldiers looking for friends or family until they too were found.  My daughter and I stood there with a small sign with just a soldiers name.  He found us and we left the Center to our car and he gathered his things.

As we left, we passed a soldier who looked as if he had been crying, don't know why.  Perhaps no one was there to welcome him home, or maybe he was just happy to be home.  I don't know.

What I do know is this, we need to do more for this men and women in the military and their families.  We say thanks to them when we see them in the stores or restaurants, or airports.  Some people have even picked up their tab for dinners.  We need to more if we can.  What more can I do right now, I don't know but I will try to find something more to honor them.  It may even be nothing more at the moment than pray for them and their safety.  But more I will try to do, and not just because my daughter now too serves her country.

I don't want those who live in other countries to think I am only thinking of our military at this time, I'm not.  The we I am talking about is all of us and the Soldiers, Sailors, Marines, and Airmen I am truly talking about are those that serve in the military of any country, regardless of ideology or politics.

The next time you see one, even if you don't say Thank You to them, remember that they have made a commitment to serve and protect your country, and honor them.

To all the Soldiers, Sailors, Marines, and Airmen; Thank you for your selflessness and sacrifice by both you and your families.  Some have given the ultimate sacrifice and they too must be honored.  Freedom isn't free and their must be those who stand ready when the call is made.  Thank you.

Learning a New Development Platform

This past week at work we have been having some in-depth training.  The interesting thing is it hasn't been SQL Server, although the Microsoft product involved uses it extensively, it has been Windows SharePoint Services and Microsoft Office SharePoint Server.

The reason I thought I'd talk about it briefly is what I have learned about it during the last 4 days.  My previous employer used it, and based on one f my co-workers who also worked there with me, its use there exploded after I left.  Everyone in the company used it extensively.  We started using Windows SharePoint Services about three years ago a little bit.  I implemented a small team site when we first started our Data Warehouse project, but it didn't go anywhere beyond that for a quite awhile.  We developed a small POC site last school year hat went over pretty good at the school where it was used, but it wasn't really a ready for prime-time site.

We have put together a site just recently that went into production for our Professional Development department.  We'll see how this site goes.  The interesting thing here is that the developer that put it together wishes we had had this class first.  Found some things he would have done quite differently had he known more about the product.

I have learned quite a bit myself.  WSS and MOSS are both extremely powerful development platforms.  WSS for department level applications, and MOSS for Enterprise applications.  If you get the opportunity to work with either product, take the time to really learn it in depth, I think you'll be quite surprised.  For me, there are a couple of minor issues, the most glaring is the lack of scheduled backups for Farms, Site Collections, and Sites.  You either have to use Task Scheduler (I haven't figured out yet how to get a job in SQL Server Agent to run the STSADM tool to run a backup) or purchase a third party tool to accomplish the task.

I'm actually looking forward to learning a lot more about WSS and MOSS over the next few months.

My Latest Articles

A few weeks ago I mentioned my blog that I would have two articles published on sswug.org.  Well, they have been published and are available to members of sswug.org.

The first article, published on May 26th, is titled Deleting Large Number of Records.

The second article, published on June 1st, is titled The Dynamic Tally or Numbers Table.

I am a somewhat disappointed that only paid members can read these articles at this time, as I am sure they would have been read by many more people by now than have read them on sswug.org.

Once I am able to republish these two articles, I will submit them to Steve Jones on SSC where I am certain they will get a much wider reading then they have so far to date.

Although disappointed by the low number of views, I do plan on writing additional articles for sswug.org, as it is one way to get free monthly memberships.  But I am also going to try and write more articles so that I can also have some published on SSC as well.  Hopefully as I get more proficient and prolific, I'll have multiple articles be published on multiple sites at the same time, and be able to republish the more restricted ones later.

Comparing Hardcoded functions, In-Line TVF's, and Scalar Functions

While perusing some of the threads on SQLServerCentral.com one of the questions that hit me was regarding the use of scalar functions in queries.  In this particular case, in the column list of a SELECT statement.  Did it make sense to encapsulate a routine to strip of the time portion of a datetime value or should you put the actual function call(s) in the SELECT list.

I decided to run some tests so I created to user-defined functions that would strip off the time portion of a datetime value, one was created as a scalar function and the second as an in-line table valued function.  In my SandBox database I have a table, dbo.LAPTest, that has 17,996,362 records.  One of the columns in this table is a datetime column with random datetime values.

Here is the code for the two functions I wrote for the test:

CREATE FUNCTION [dbo].[ufnDayOnly](@DateValue DateTime)
RETURNS table
AS
RETURN (select dateadd(dd, datediff(dd,0, @DateValue),0) as DateOnly );
GO

CREATE FUNCTION [dbo].[fn_DayOnly] (@DateValue DateTime)
RETURNS DateTime
AS
BEGIN
    RETURN dateadd(dd, datediff(dd,0, @DateValue),0) 
END
GO

I then wrote three separate T-SQL scripts, shown below, that will run 8 time each selecting 1 to 10,000,000 rows.  Each script strips the time portion of datetime values using three methods; hardcoded date functions, the table-valued function, and the scalar function.  Each time through the loop we will clear the caches.

Set NoCount On;
Declare @cpu_ int,
        @lreads_ int,
        @eMsec_ int;
declare @AcctID int,
        @Amount money,
        @TDate datetime,
        @ADate datetime;
declare @LoopCnt int;
set @LoopCnt = 0;

while @LoopCnt < 8
begin
    dbcc freeproccache with no_infomsgs;
    dbcc freesystemcache('ALL') with no_infomsgs;

    Select
        @cpu_ = cpu_time 
        , @lreads_ = logical_reads 
        , @eMsec_ = total_elapsed_time 
    From
        sys.dm_exec_requests 
    Where
        session_id = @@spid;

    select top (power(10, @LoopCnt))
        @AcctID = AccountID,
        @Amount = Amount,
        @TDate  = TDate,
        @ADate  = dateadd(dd,datediff(dd,0,TDate),0)
    from
        dbo.LAPTest;
     
    Select
        power(10, @LoopCnt) as RowsProcessed,
        cpu_time-@cpu_ as CpuMs 
        , logical_reads- @lreads_ as LogRds 
        , total_elapsed_time - @eMsec_ as Elapsed 
    From
        sys.dm_exec_requests 
    Where
        session_id = @@spid;
    set @LoopCnt = @LoopCnt + 1;
end

 

Set NoCount On;
Declare @cpu_ int,
        @lreads_ int,
        @eMsec_ int;
declare @AcctID int,
        @Amount money,
        @TDate datetime,
        @ADate datetime;
declare @LoopCnt int;
set @LoopCnt = 0;

while @LoopCnt < 8
begin
    dbcc freeproccache with no_infomsgs;
    dbcc freesystemcache('ALL') with no_infomsgs;

    Select
        @cpu_ = cpu_time 
        , @lreads_ = logical_reads 
        , @eMsec_ = total_elapsed_time 
    From
        sys.dm_exec_requests 
    Where
        session_id = @@spid;

    select top (power(10, @LoopCnt))
        @AcctID  = AccountID,
        @Amount = Amount,
        @TDate   = TDate,
        @ADate   = DateOnly
    from
        dbo.LAPTest
        cross apply dbo.ufnDayOnly(TDate);
     
    Select
        power(10, @LoopCnt) as RecordsProcessed,
        cpu_time-@cpu_ as CpuMs 
        , logical_reads- @lreads_ as LogRds 
        , total_elapsed_time - @eMsec_ as Elapsed 
    From
        sys.dm_exec_requests 
    Where
        session_id = @@spid;
    set @LoopCnt = @LoopCnt + 1;
end

 

Set NoCount On;
Declare @cpu_ int,
        @lreads_ int,
        @eMsec_ int;
declare @AcctID int,
        @Amount money,
        @TDate datetime,
        @ADate datetime;
declare @LoopCnt int;
set @LoopCnt = 0;

while @LoopCnt < 8
begin
    dbcc freeproccache with no_infomsgs;
    dbcc freesystemcache('ALL') with no_infomsgs;

    Select
        @cpu_ = cpu_time 
        , @lreads_ = logical_reads 
        , @eMsec_ = total_elapsed_time 
    From
        sys.dm_exec_requests 
    Where
        session_id = @@spid;

    select top (power(10,@LoopCnt))
        @AcctID = AccountID,
        @Amount = Amount,
        @TDate  = TDate,
        @ADate   = dbo.fn_DayOnly(TDate)
    from
        dbo.LAPTest;
     
    Select
        power(10,@LoopCnt) as RecordsProcessed,
        cpu_time-@cpu_ as CpuMs 
        , logical_reads- @lreads_ as LogRds 
        , total_elapsed_time - @eMsec_ as Elapsed 
    From
        sys.dm_exec_requests 
    Where
        session_id = @@spid;
    set @LoopCnt = @LoopCnt + 1;
end

The results of running these scripts are as follows:

	Hardcoded Functions			In-Line TVF/Cross Apply			Scalar Function
RowsProcessed	CpuMs	LogRds	Elapsed	CpuMs	LogRds	Elapsed	CpuMs	LogRds	Elapsed
1	0	26	1	0	52	1	0	40	2
10	0	4	0	0	4	1	0	18	1
100	0	4	1	0	4	0	0	18	2
1000	0	14	1	0	14	2	16	28	36
10000	16	58	12	16	58	13	94	72	102
100000	125	523	143	125	523	115	703	537	699
1000000	703	5130	730	703	5130	715	7687	5144	7958
10000000	6984	51171	7072	7063	51171	7157	67016	51185	67414

As you can see there isn't much difference between the hardcoded functions and the In-Line TVF/Cross Apply, however, as the number of rows increase there is a definite disadvantage to using the scalar function.

Does this mean you should convert all your scalar functions to In-Line TVF's?  Not necessarily, as you may need those functions else where, such as a WHERE clause where it may only be called once.

Does this mean you should always use the CROSS APPLY with In-Line TVF's? Again, not necessarily as it may not be performant either in all cases.

What can I suggest?  Test, test, and test again is the best advice.  The testing I ran above is limited and not necessarily what you may do on a normal, day to day basis.  But it does give you some food for thought.

 

See you on the forums.  I look forward to seeing what you all have to say.

Published, Again!

I have written two new articles and they are going to be published, not on SSC but on SSWUG.  I thought I'd help out Chris Shaw a bit and write a few articles for him as well.  Plus it broadens my horizons some as well.

The first article, based on numerous posts here on SSC is titled Deleting Numerous Rows from a Table, and will be published on May 25th.  It shows how to delete records in batches and control the size of the transaction log files, if the database is using the bulk logged or full recovery model.  This article originally started as my first blog entry, but when I finished it, it seemed more like an article.

The second article, to be published on June 1st, is titled The Dynamic Tally or Numbers Table.  I wrote this one after reading an article in SQL Server Magazine about creating a tally table.  I think you will find the methodology behind my dynamic tally table much more performant than the one in the SQL Server Magazine article.

When a Community Rallies!

I have been a bit lax these past few days, but I have been having a bit of a writers block not knowing what to write about.  Well, I realized this evening there is something worth writing about, and it is happening right now on SQLServerCentral.com.  An amazing thing is happening on this thread Performance issue with tally solution.

The SSC community has come together in a effort to develop a variety of methods to parse strings.  People have put together routines that use cursors, while loops, tally tables, double barreled tally tables, double barreled double carbed tally tables, CLR procedures, and (if I remember correctly, which I might not) even a XML version.  Florian Reischl started this particular thread and has been spearheading the testing of the procedures.  Many others have joined in the project that has definitely shown what can happen when a community like that on SSC gets together.

This has been one of the most interesting threads I have had the opportunity to be just a small participant in as it has grown.  Many of us are eagerly awaiting the testing of the procedures, looking to see which one is "the best".  Early testing, however, has shown that the choice of procedures is really dependant on many factors.  I'm not going to spoil anything here, so I'm not even going to say anything more on that particular subject.

We are encouraging Flo to fully document everything that is occurring and to submit an article (or two or three) when he finally completes the testing.  I really think it will be a significant article and one that many people will reference.

If you are interested to see what is happening click on the hyperlink above and start reading!

Career Development and Training

I know I discussed this in an early post, but it seems to have reared its ugly head again.  This time during the discussion of Barry Young's first article in his series on removing cursors from your code.

It seems interesting that there are individuals and companies out there the subscribe to the theory of developing to the lowest common denominator on their team.  If this results in the writing of code that is inefficient, not scalable, but works in the test and development environment and meets the customers requirements, that's good enough.  The excuse being that they have deadlines to meet, and customers to satisfy; they don't have time to train their developers in better, more efficient development techniques in a database environment like MS SQL Server.  So, if you know and understand cursors, go ahead and use them even though they are not the best tool in the toolbox.

What is scary about this, is I could be in that category.  When I first started working with SQL Server 6.5 back in 1997, I didn't have the benefit of working with anyone who had experience with SQL Server.  Everything I learned, I learned from reading books or just experimenting.  There was no SQLServerCentral.com at the time, in fact not much on the Internet at all back then really.  I'm sure code I had written back then, except maybe the trivial code, is something now I would never write with the knowledge and experience I have now.  Even some of the things I learned from books back then I will try to avoid today, like triangular joins for one.

I have learned new ways to look at things in SQL, and I am now willing to challenge the code I currently develop.  Yes, it may work and provide me the answers I require, but is it really the best code I can develop?  Is there something I am missing that could improve the performance or scalability of the code?  I have even started search sites like SSC for similar problems to see if I could find a better solution that I could use.

I have also found myself sharing techniques I have learned from others on SSC with my coworkers, wether it has been simple things like determining the first and last days of the month, calculating work days, or parsing strings using a Tally table.  These techniques have allowed us to implement new processes in our environment that might otherwise not have been considered viable projects.

As professionals, we must take personal responsibility learning the tools we are working with and how best to use them for the benefit of both ourselves, our employers, and our customers.  If the company we work for is willing to pay for that training, great, make the best of those training dollars.  If, however, they can't, or won't, then we have to step up to the plate and do what is necessary to help ourselves and our employer.  The better we are able to use the tools we have, the more valuable we are to the people we work for, and in today's economy, that is very important.

But beyond that, we also need to be willing to share our knowledge with others we work with as well.  Some may say that in today's economy that may not be the best thing to do.  I have to disagree with that as well.  Your willingness to share knowledge shows that you are a team player, working for the best interest of not only yourself, but the company as well.  This is, in fact, an area I need to improve myself.  As a lead for our database team, I should be doing more to improve the knowledge of all my team members.  Perhaps by developing some simple brown-bag lunch seminars or half day classes.

So, I guess the best way to end this, is with a question.  What are your thoughts regarding this subject?  You can leave your comments here, or drop a short comment and link back to your own blog and post an answer there,  You can be sure that I'll come and read it.

Dealing with Difficult Forum Users - The Flip Side

Earlier I talked about dealing with the difficult forum user from the perspective of providing help.  Now let us look at from the flip side of the coin.  You are asking for help and you aren't getting what you want or need.

The first thing you have to realize is that the people who are out there willing to help do it voluntarily.  They are giving of themselves to the community for free.  There are many other things that they could be doing instead, like spending time with family and friends, or learning and developing skills to keep current with changing technologies and career development.  With this simple knowledge, you can combat the difficult forum user.

The first thing, when asking for help with a problem, post the question or problem in the appropriate forum and state the problem you are having explicitly.  Be concise yet detailed enough to be sure that anyone from the novice to the guru can understand the problem.  Doing this gives those who may respond to your question or request for help with a clear understanding of the problem at hand.  A big no-no is posting your question/problem in multiple forums on a single site.  This fragments the possible responses, and you may get multiple responses that say the same thing from different people.

Second, you need to provide as much background information as possible as well.  This means DDL for your tables (CREATE TABLE statements), sample data for the tables (Usually in the form of a INSERT ... SELECT ... UNION ALL SELECT ... statements), expected results based on the sample data, and most importantly your code that you have developed so far.  The sample data should be sufficient to show the issues and provide a decent sample for testing.  Also, the people who are willing to help are more willing if all they have to do is cut, paste, and execute the DDL and INSERT statements to create a test environment.  Remember, the more work you do up front, the more likely you will get well tested results in return.  Although some people will take the time to format poorly provided data, some people will just skip your post entirely.

Third, if you are pointed to Books Online, articles, other threads on the Forum; please take the time to read and research.  If you have problems finding the information in Books Online, come back and say so but be sure to provide information on what you were able to find, or ask what you should look under in Books Online.  With the articles and other threads on the Forum, read them and if you still have questions, ask for clarification.  The worst thing you can do is to ignore the advice.  If it was suggested, there is usually a reason.  I can tell you from experience, it is not to mean, but to help you learn where and how to look for information.  It may still not make sense, but you should hopefully be able to ask more detailed questions that help you clarify your knowledge and understanding.

Fourth, provide feedback to the community.  Wether you solve the issue yourself or use the information or code provided by someone, tell us.  If the solution is of your own devising, show us.  It may help others with a similar problem.  Also, it gives us a chance to see what you did, and perhaps help you improve it as well.

Finally, maintain your cool.  You may get some snide or obnoxious responses.  Let them go.  By maintaining a professional manner, you will demonstrate strength of character.  In addition, you may also earn additional good will from others.

Hopefully this helps when asking for questions or for help.  See you all on Forums!

A Variable Length Random String

I was looking through some of the active threads on SQLServerCentral.com tonight, and I ran across one with some code that looked familiar.  While reading this thread, I noticed that the function returned a fixed length random character string.  This told me that if you needed a random character string of a different length, you'd need another function.

How can you get around this?  You can't use the function NEWID() in a function, SQL Server 2005 doesn't like it.  There is a way to do this.  Create a view that returns the result of the function NEWID(), and use that in your function to return a random string of different lengths.

 

Here is the code I put together:

create view dbo.MyNewID as
select newid() as NewIDValue;
go
create function dbo.ufn_RandomString(
    @pStringLength int = 20
) returns varchar(max)
as begin
    declare  @RandomString varchar(max);
    with
    a1 as (select 1 as N union all
           select 1 union all
           select 1 union all
           select 1 union all
           select 1 union all
           select 1 union all
           select 1 union all
           select 1 union all
           select 1 union all
           select 1),
    a2 as (select
                1 as N
           from
                a1 as a
                cross join a1 as b),
    a3 as (select
                1 as N
           from
                a2 as a
                cross join a2 as b),
    a4 as (select
                1 as N
           from
                a3 as a
                cross join a2 as b),
    Tally as (select
                row_number() over (order by N) as N
              from
                a4)
    , cteRandomString (
        RandomString
    ) as (
    select top (@pStringLength)
        substring(x,(abs(checksum((select NewIDValue from MyNewID)))%36)+1,1)
    from
        Tally cross join (select x='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') a
    )
     select @RandomString = 
    replace((select
        ',' + RandomString
    from
        cteRandomString
    for xml path ('')),',','');
    return (@RandomString);
end
go

Keeping Relevant in the Face of Changing Technologies

I was recently asked by a co-worker on my team at work what he should do to keep up with changing technologies in our career field.  I was slightly amazed at the question, as I know I have made my thoughts on the matter fairly clear for several years.  My answer was the same as it has always been for the last few years.

We, as IT professionals, must keep ourselves relevant in the face of changing technologies.  We must be willing to expend some of our own resources, money and time, to keep up technology changes.  We can not depend on our employers to provide us with the training, and with tightening budgets, the first thing to go is usually money for training.  And if those training dollars are still there, employers are more likely to spend those dollars on individuals that have shown the initiative to seek out training opportunities on there own and are learning and advancing their skills than on those sitting around waiting for the employer to provide the training.

I will admit that I am behind the curve today as I haven't yet purchased a copy of SQL Server 2008 Developer Edition.  This is something I will be hopefully correcting in the near future.

How to Deal with the Difficult Forum User

It has happened to us all at one time or another.  We are trying to help someone on an online forum and they won't cooperate when asked for more details regarding the problem they are experiencing.  You may have written some code and they come back and say it doesn't work, but they won't tell you what was wrong.  The may question your advice regarding an approach to solve a problem.  What do you do?

Well, you could throw in the towel and walk away.  If this is a user that you have had frequent issues with over several problems or questions, you may even add them to your personal black list, those forum posters that you will simple ignore in the future.  You may even let your frustration get the better of you and say things in the heat of the moment that you may regret later.

Before you do any of those things, let's stop and think a minute.  I'd like to thank RBarryYoung on sqlservercentral.com for articulating two of the principles that can make dealing with difficult individuals easier.

The first is to treat the individual with respect.  Do this even if they are not showing you that same respect in return.  It isn't easy, but it shows strength of character, and eventually the individual may see that and begin to show more respect in return.

The second, the individual has come looking for help, do your best to help.  This doesn't mean we should do their work for them, but help them figure out what is going on or how to do something better or easier.  Sometimes, this does mean providing code, but hopefully with some explanation as to how and why it works.  Other times it may just be asking them to read an article or certain sections in Books Online.  When doing that, let them know that you are still available if they still have questions after doing the reading.  It may help them, but they may still be confused or have new questions about what they are doing or the problem they are trying to solve.

The third principle is that you, yourself, need to have someone one you can go to and vent your frustrations with when you feel like exploding.  This may be a coworker, or another member of the forum that you can contact separately.  By allowing yourself an avenue to release your own stress, you won't take it out on the individual you are trying to help.

And as I write this, a fourth principle comes to mind, and that is to ask others to help as well.  You may be struggling with an individuals problem, and can't seem to figure out what they want or need.  Ask for help from other members of the forum.  It could be as simple as misunderstanding the individuals requirements, to a language barrier.  Most of the SQL Server user sites are English language sites, but by nature of the Internet the people asking for help, English may not be their first or even second language.

Hopefully this makes some sense, and will help when you find yourself dealing that difficult forum user that is asking for help.  Remember, you were once on the other side of the coin.