June 1, 2012 at 10:46 pm
I'm going to take my normal "Luddite-like" stance on all of this. 😉
We're talking about databases here. More specfically, we're talking about SQL Server databases. The language of SQL Server databases is T-SQL... not SSIS and a bunch of scripts.
With that in mind, what I've found is that too many people use tools, such as SSIS and a myriad of scripting languages, to make up for a lack of knowledge of T-SQL. While I freely admit that it's probably easier to build spreadsheets using the likes of SSIS (not really... I've found ways around even that 😀 ), most of the "problems" that people try to solve using some form of scripting language are usually just as easy and frequently easier to solve using some form of well written T-SQL. Sure, some "black arts" techniques can be helpful but they're usually not needed and can usually be "borrowed" when needed. You just need to know the language well enough to do the job.
I know, I know. "Put your money where your mouth is, Jeff. Let's see an example!"
Ok... no problem. Here's a link to just such a problem that an OP was trying to solve using SSIS.
http://qa.sqlservercentral.com/Forums/Topic1309031-147-1.aspx
Even with the marvels you think you can pull off with a decent scripting language, the OP was still having problems and the code was getting just as long as some equivalent T-SQL. Add in the fact that you've introduced a different language and that you have to "go external" to the database engine and into the world of SSIS, I'm not really seeing an advantage. Worse yet, even with the wiles of a scripting language, the OP couldn't come up with the desired solution. The OP also couldn't come up with a truly "self realizing" solution that would accommodate not actually knowing how many columns there were in the "unknown" table. The scripted code ended up being hard coded for "prior knowledge" of how many columns there were going to be both in the imported file and the final output.
I'll be the first to agree that you normally need to know how many columns there are but I have run into instances where you don't and you still need to handle it. Downloads from "DoubleClick.Net" to capture "spotlight" reports for ads falls into that category. You need to match up an unknown number of pairs of columns with the first 9 columns to produce a normalized table that can be intelligently queried.
What about performance? The reason why I got involved in the DoubleClick.net data was because someone had written a package to do the job and because the native tools of SSIS are woefully deficient for such a thing, someone wrote a script to do what I stated in the previous paragraph. It took 45 minutes just to get a lousy 800 column wide, 10,000 row file ready for import. I rewrote it using only the T-SQL methods I used in the link I previously provided and got it so that I could actually import the data from a file like that in less than a minute whereas the script method just created another file in 45 minutes and still hadn't actually imported the data.
If you look at the T-SQL code I wrote, it's not rocket science. In fact, it's very similar to the script code on that same thread. I just didn't need to know 2 languages well enough to do the job and I didn't need to go outside the database engine.
Soooooo... I told you that story to tell you this one. YES! It's good to know a lot of different ways to do things. But if you're going to work with databases a lot, perhaps you should spend a whole lot less time learning how to climb small foothills and learn to climb Mt. Everest! Spend more time learning T-SQL and you won't need to know VB for one thing, C# for another, PERL for yet another, etc, etc, ad infinitum.
Can someone do what I did in SSIS? Absolutely! But [font="Arial Black"]WHY??? [/font]It was easy to do in T-SQL.
Hmmm... perhaps I'm not the Luddite people think I am. Maybe I'm just using the right tool for the right job! 😉
BWAAA-HAAAA!!!! Shifting gears and speaking of Luddites, how many of you still think that calling PowerShell from a T-SQL stored procedure using xp_CmdShell is a security risk? You might be a Luddite yourself! 😛
"Before you can think outside the box, you must first realize... you're in a box!".
--Jeff Moden
June 1, 2012 at 11:01 pm
rajiv.varma (6/1/2012)
Thank you all for your valuable suggestions. I think I will just try and learn new things as I encounter new problems. I will try to learn both the languages depending on the situation. If VB.NET is better at solving a given problem, I will use it; or otherwise I will use C# 🙂
See my previous post just above this one, Rajiv. Perhaps the best thing to do is to learn T-SQL well enough so that you don't actually need those other 2 languages to do things in the database. 🙂
--Jeff Moden
June 1, 2012 at 11:07 pm
Jeff Moden (6/1/2012)
I'm going to take my normal "Luddite-like" stance on all of this. 😉We're talking about databases here. More specfically, we're talking about SQL Server databases. The language of SQL Server databases is T-SQL... not SSIS and a bunch of scripts.
With that in mind, what I've found is that too many people use tools, such as SSIS, to make up for a lack of knowledge of T-SQL.
I fully agree with you, on the other hand SSIS is a tool and one of its features is CLR scripting, mastering it is good, knowing when to use it depends on the skills level of the developer and his/her professional background.
The disadvantage of CLR scripting, in my opinion, is that people with limited SQL knowledge and given tasks better suited for real Sql developers, if you add tight deadlines that is a recipe for disaster. T-Sql or Pl/SQL are set driven and the developers need to know how to think in sets, while CRL scripting is not really a set focused environment, it adds an extra level of complexity not required by business critical processes.
Cheers,
Hope this helps,
Rock from VbCity
June 1, 2012 at 11:19 pm
Rock from VbCity (6/1/2012)
... if you add tight deadlines that is a recipe for disaster
Thanks for the thoughtful feedback.
A bit off subject, for sure, but you've touched on another pet peeve of mine. I've found that tight deadlines are a recipe for disaster even if you have the perfect mix of the required talent. Just like I'd like to see people that use databases a lot learn much more about T-SQL, I'd also like to see people that create schedules and make promises to customers learn much more about how bad a tight deadline can make you look in the eyes of your customers.
--Jeff Moden
June 1, 2012 at 11:31 pm
Jeff Moden (6/1/2012)
A bit off subject, for sure, but you've touched on another pet peeve of mine. I've found that tight deadlines are a recipe for disaster even if you have the perfect mix of the required talent. Just like I'd like to see people that use databases a lot learn much more about T-SQL, I'd also like to see people that create schedules and make promises to customers learn much more about how bad a tight deadline can make you look in the eyes of your customers.
I agree with you, I once heard the expression "starfleet command", I call them "yes people" myself, they don't care, they want to look good with their superiors, and if something goes wrong the blame goes down to the developers and IT in general, when things get really tough, they find a job elsewhere problem solved.
Hope this helps,
Rock from VbCity
June 2, 2012 at 6:31 am
SQLCLR is an excellent tool, and there are just some things that can't be accomplished with pure T-SQL. Mostly the Windows environment, and aspects outside of the instance. SQLCLR is also a wise choice for complex string manipulation. There are some things that managed code can just do a little better (and is designed for) than SQL. (Not to mention, SQLCLR will be replacing extended stored procedures eventually altogether)
As for the C# vs. VB.NET debate, it's an age-old one. They both compile to the same IL. Use your preference. There are subtle differences, but the novice programmer probably won't run into them. If you have a VB background, go VB.NET. If you have a C/C++ background, go C#.
June 2, 2012 at 6:51 am
Thomas Stringer (6/2/2012)
SQLCLR is an excellent tool, and there are just some things that can't be accomplished with pure T-SQL. Mostly the Windows environment, and aspects outside of the instance. SQLCLR is also a wise choice for complex string manipulation. There are some things that managed code can just do a little better (and is designed for) than SQL. (Not to mention, SQLCLR will be replacing extended stored procedures eventually altogether)
I couldn't agree more. Sometimes its just the right tool for the job.
As for the C# vs. VB.NET debate, it's an age-old one. They both compile to the same IL. Use your preference. There are subtle differences, but the novice programmer probably won't run into them. If you have a VB background, go VB.NET. If you have a C/C++ background, go C#.
From personal experience learning VB.net and C# were equally difficult to learn. More important in the transition than syntax for me was the programming paradigm you're coming from. I was a VB6 dev in the late 90's and early 2000's and had no structured OO training or experience outside of my college C++ classes. I was given the same advice you just gave and jumped into learning VB.net. VB.net was nonsense to me and it was beyond frustrating trying to absorb a paradigm shift. In other words, syntax was the least of my worries and the fact that VB.net looked similar to VB6 was really no comfort at all when I could not figure out where CInt went 😛 When I figured out I had to unlearn a bunch of stuff anyway and start with a completely new paradigm I looked around at the winds of change and switched to learning C# to ensure that if putting in the same amount of work to learn a .NET language that I would gain the most benefit from a marketability and job attainment standpoint as possible. No one could point out a significant difference between either to warrant a decision based solely on technical merit. This is likely why the argument continues on forever, because there is no right or wrong, just lots of competing opinions.
All things being equal I would recommend learning C# since by the numbers it gives one the best chance of being able to employ their skills on an open market. I am not telling anyone to do anything, nor am I saying either is better, I am only sharing my experience and giving my opinion. Please feel free to disagree.
Edit: spelling
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 2, 2012 at 7:13 am
opc.three (6/2/2012)
Thomas Stringer (6/2/2012)
SQLCLR is an excellent tool, and there are just some things that can't be accomplished with pure T-SQL. Mostly the Windows environment, and aspects outside of the instance. SQLCLR is also a wise choice for complex string manipulation. There are some things that managed code can just do a little better (and is designed for) than SQL. (Not to mention, SQLCLR will be replacing extended stored procedures eventually altogether)I couldn't agree more. Sometimes its just the right tool for the job.
As for the C# vs. VB.NET debate, it's an age-old one. They both compile to the same IL. Use your preference. There are subtle differences, but the novice programmer probably won't run into them. If you have a VB background, go VB.NET. If you have a C/C++ background, go C#.
From personal experience learning VB.net and C# were equally difficult to learn. More important in the transition than syntax for me was the programming paradigm you're coming from. I was a VB6 dev in the late 90's and early 2000's and had no structured OO training or experience outside of my college C++ classes. I was given the same advice you just gave and jumped into learning VB.net. VB.net was nonsense to me and it was beyond frustrating trying to absorb a paradigm shift. In other words, syntax was the least of my worries and the fact that VB.net looked similar to VB6 was really no comfort at all when I could not figure out where CInt went 😛 When I figured out I had to unlearn a bunch of stuff anyway and start with a completely new paradigm I looked around at the winds of change and switched to learning C# to ensure that if putting in the same amount of work to learn a .NET language that I would gain the most benefit from a marketability and job attainment standpoint as possible. No one could point out a significant difference between either to warrant a decision based solely on technical merit. This is likely why the argument continues on forever, because there is no right or wrong, just lots of competing opinions.
All things being equal I would recommend learning C# since by the numbers it gives one the best chance of being able to employ their skills on an open market. I am not telling anyone to do anything, nor am I saying either is better, I am only sharing my experience and giving my opinion. Please feel free to disagree.
Edit: spelling
Absolutely, I agree with you. And our personal preferences definitely align. I'll choose C# over VB 11 times out of 10. Just feels more natural for me.
And you hit the nail on the head. Good, bad, or indifferent the fact of the matter is simply that there is more demand for C# in the current development workplace.
June 2, 2012 at 10:59 am
Thomas Stringer (6/2/2012)
SQLCLR is also a wise choice for complex string manipulation
I'll say "IT DEPENDS". Let's have a race. You write a CLR function to solve the INITIAL CAPS problem to force all letters to be lower case and force any letter that doesn't follow a letter to be upper case. Of course the first character should be forced to upper case if it's a letter.
I'll do the same in T-SQL. I'll even give myself a handicap. Two, actually. I'll create a Scalar Function AND use a While Loop.
Once done, I'll be happy to provide a large test table and we'll see just how good CLRs actually are for "complex string manipulation". 😉
--Jeff Moden
June 2, 2012 at 11:24 am
Jeff Moden (6/2/2012)
Thomas Stringer (6/2/2012)
SQLCLR is also a wise choice for complex string manipulationI'll say "IT DEPENDS". Let's have a race. You write a CLR function to solve the INITIAL CAPS problem to force all letters to be lower case and force any letter that doesn't follow a letter to be upper case. Of course the first character should be forced to upper case if it's a letter.
I'll do the same in T-SQL. I'll even give myself a handicap. Two, actually. I'll create a Scalar Function AND use a While Loop.
Once done, I'll be happy to provide a large test table and we'll see just how good CLRs actually are for "complex string manipulation". 😉
Umm, wasn't this already done in another thread Jeff?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 2, 2012 at 11:29 am
RBarryYoung (6/2/2012)
Jeff Moden (6/2/2012)
Thomas Stringer (6/2/2012)
SQLCLR is also a wise choice for complex string manipulationI'll say "IT DEPENDS". Let's have a race. You write a CLR function to solve the INITIAL CAPS problem to force all letters to be lower case and force any letter that doesn't follow a letter to be upper case. Of course the first character should be forced to upper case if it's a letter.
I'll do the same in T-SQL. I'll even give myself a handicap. Two, actually. I'll create a Scalar Function AND use a While Loop.
Once done, I'll be happy to provide a large test table and we'll see just how good CLRs actually are for "complex string manipulation". 😉
Umm, wasn't this already done in another thread Jeff?
It certainly was. 😉
--Jeff Moden
June 2, 2012 at 11:34 am
Ok, I'm not saying it is going to outperform T-SQL 100% of the time. Making a broad generalization like that would be foolhardy.
But sometimes it may be more efficient to use SQLCLR. Your scenario you gave was not all that complex. It is ignorant to say SQLCLR is never the right choice. Likewise, it is ignorant to say SQLCLR is always the right choice.
June 2, 2012 at 12:25 pm
Thomas Stringer (6/2/2012)
Ok, I'm not saying it is going to outperform T-SQL 100% of the time. Making a broad generalization like that would be foolhardy.But sometimes it may be more efficient to use SQLCLR. Your scenario you gave was not all that complex. It is ignorant to say SQLCLR is never the right choice. Likewise, it is ignorant to say SQLCLR is always the right choice.
Now we're talking. That's exactly the point I was trying to make. "It Depends". When given a choice, try both. 🙂
--Jeff Moden
June 2, 2012 at 12:26 pm
Jeff Moden (6/2/2012)
Thomas Stringer (6/2/2012)
Ok, I'm not saying it is going to outperform T-SQL 100% of the time. Making a broad generalization like that would be foolhardy.But sometimes it may be more efficient to use SQLCLR. Your scenario you gave was not all that complex. It is ignorant to say SQLCLR is never the right choice. Likewise, it is ignorant to say SQLCLR is always the right choice.
Now we're talking. That's exactly the point I was trying to make. "It Depends". When given a choice, try both. 🙂
I absolutely couldn't agree more. +1000. Glad we're on the same page. 😀
June 3, 2012 at 10:26 am
Jeff Moden (6/2/2012)
RBarryYoung (6/2/2012)
Jeff Moden (6/2/2012)
Thomas Stringer (6/2/2012)
SQLCLR is also a wise choice for complex string manipulationI'll say "IT DEPENDS". Let's have a race. You write a CLR function to solve the INITIAL CAPS problem to force all letters to be lower case and force any letter that doesn't follow a letter to be upper case. Of course the first character should be forced to upper case if it's a letter.
I'll do the same in T-SQL. I'll even give myself a handicap. Two, actually. I'll create a Scalar Function AND use a While Loop.
Once done, I'll be happy to provide a large test table and we'll see just how good CLRs actually are for "complex string manipulation". 😉
Umm, wasn't this already done in another thread Jeff?
It certainly was. 😉
Do you remember where that was? I'd like to take stab at it. 😀
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply