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