looping
Brian S.
looping through a select statement, I want put a numeric field into a temp
value ..
declare My_cursor cursor
for
select [end_dia] - [start dia] as removed from table1
open My_cursor
declare @myremoved
declare @myremoved1
fetch next from My_cursor into @myremoved
while @@fetch_status=0
begin
select @myremoved1 = @myremoved1 + @myremoved
fetch next from My_cursor into @myremoved
close My_cursor
deallocate My_cusor
select @myrremoved
I get a syntax near the "select @myremoved1 = @myremoved1 + @myremoved"
any help?
Simon Whale
select @myremoved1 = @myremoved1 + @myremoved
to
set @myremoved1 = @myremoved1 + @myremoved
Simon
"Brian S." <bsgal...@community.nospam> wrote in message
news:uN8E13h...@TK2MSFTNGP05.phx.gbl...
sloan
with TSQL.
That code is horrible. Whatever you're trying to accomplish, you need post
it.
99.9% of the time, there is a non cursor solution for what you need.
I've written one cursor in 8 years. And that is because the db designer
violated 1NF.
...............
"Brian S." <bsgal...@community.nospam> wrote in message
news:uN8E13h...@TK2MSFTNGP05.phx.gbl...
Brian S.
was thinking about in my head... I've not done cusors in MS SQL before,
but have in Oracle. You seem to have a problem with using cusors, would
you like to explain more?
"sloan" <sl...@ipass.net> wrote in message
news:eKTsuUi...@TK2MSFTNGP03.phx.gbl...
sloan
They perform horribly 99% of the time.
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!276.entry
Take the 10 minutes to step through the example.
Change this value
select @nameCharCounter = 997
to something like
select @nameCharCounter = 1000997
(which will insert alot of records to test against)
and see the performance difference between a cursor based solution and a non
cursor based solution.
.......................
You can fish for some more comments (in the set-based thinking topic) by
looking at old posts:
http://groups.google.com/groups/search?hl=en&ie=UTF-8&q=%22set+based%22+cursor&btnG=Search&sitesearch=
"Brian S." <bsgal...@community.nospam> wrote in message
news:eBOqBfiY...@TK2MSFTNGP02.phx.gbl...
--CELKO--
The goal of SQL is to be a declarative language. All the optimizers
and SQL engines are designed for that goal. Cursors were meant to be
a way to get set-oriented data into a sequential file structure that
would work with a 3GL (Cobol, Fortran, C, etc.) and not for the
database side of the enterprise.
Get a copy of my THINKING IN SETS book for details. Thanks to
parallelism, a set-oriented approach to problem solving will run order
of magnitude faster, give shorter code, be easier to maintain, etc.
Your mindset is still working with punch cards.
>> [sloan] I've written one cursor in 8 years. And that is because the db designer violated 1NF. <<
I am at five cursors, but I am old and we did not have CASE
expressions and other things in the old days. I know I could have
avoided three of them easily.
Tony Rogerson
> of magnitude faster, give shorter code, be easier to maintain, etc.
> Your mindset is still working with punch cards.
I'm suprised you've never had to do rolling totals on any realistically
production volume of data.
CTE approach....
with Trans ( row_no, id, salesperson_id, tran_date, clear_date, amount,
transaction_types_id, account_id )
as (
select row_no = row_number() over( order by account_id, tran_date, id ),
id, salesperson_id, tran_date, clear_date, amount, transaction_types_id,
account_id
from Transactions
where account_id between 1000 and 5000
)
select t1.*,
running_amount = coalesce(
( select sum( amount )
from Trans t2
where t2.row_no < t1.row_no
and t2.account_id = t1.account_id ), 0 )
+ amount
from Trans t1
order by account_id, row_no
Ran for 3.5 hours after which I cancelled it - in that time it had taken
15447110 cpu's and 80600104 logical reads (equates to 614GB of data having
to be processed).
Now, the cursor on the other hand (below) ran in 84 seconds took 15926
cycles, 726413 logical reads (equates to 5.5GB of data having to be
processed).
Before you answer - think for a minute - I'm not interested in what
theoretically is right and I'm not interested in what works on DB2 or
Oracle - this is a problem requiring an answer that works on SQL Server.
If you want the test data yourself I'll be happy to oblige - it will be on
the blog post that I will write once you've have completed your retort/rant.
-- Cursor - INSERT method
set nocount on
create table #Trans(
account_id int not null,
id int not null,
amount decimal(28,2) not null,
rolling_total decimal(28,2) not null,
salesperson_id int not null,
tran_date smalldatetime not null,
clear_date smalldatetime not null,
transaction_types_id int not null
)
declare trans_cur cursor fast_forward for
select account_id,
row_no = row_number() over( partition by account_id order by
tran_date, id ),
id,
amount,
salesperson_id,
tran_date,
clear_date,
transaction_types_id
from Transactions
where account_id between 1000 and 5000
order by account_id, row_no
declare @account_id int,
@row_no int,
@id int,
@amount decimal(28,2),
@salesperson_id int,
@tran_date smalldatetime,
@clear_date smalldatetime,
@transaction_types_id int,
@rolling_total decimal(28,2)
open trans_cur
fetch next from trans_cur
into @account_id, @row_no, @id, @amount,
@salesperson_id, @tran_date, @clear_date, @transaction_types_id
begin tran
while @@fetch_status = 0
begin
if @row_no = 1 -- initialise on each partition
set @rolling_total = @amount
else
set @rolling_total = @rolling_total + @amount
insert #Trans ( account_id, id, amount, rolling_total, salesperson_id,
tran_date, clear_date, transaction_types_id )
values( @account_id, @id, @amount, @rolling_total, @salesperson_id,
@tran_date, @clear_date, @transaction_types_id )
fetch next from trans_cur
into @account_id, @row_no, @id, @amount,
@salesperson_id, @tran_date, @clear_date, @transaction_types_id
end
deallocate trans_cur
commit tran
select id, salesperson_id, tran_date, clear_date, amount,
transaction_types_id, amount, rolling_total
from #Trans
order by account_id, tran_date, id
drop table #Trans
go
"--CELKO--" <jcel...@earthlink.net> wrote in message
news:0ecfffa8-b66e-44e4...@l2g2000yqd.googlegroups.com...
Uri Dimant
> Before you answer - think for a minute - I'm not interested in what
> theoretically is right and I'm not interested in what works on DB2 or
> Oracle - this is a problem requiring an answer that works on SQL Server.
He is going to ignore your question as well as he has been ingonerd Aaron's
"Tony Rogerson" <tonyro...@torver.net> wrote in message
news:OgkP8bsY...@TK2MSFTNGP04.phx.gbl...
Tony Rogerson
away from the facts in the case in point also - it won't come anywhere near
addressing the point I'm making that for running totals cursors rock and set
based utterly sucks.
Its a real world problem, the OVER clause on SUM gives us no benefit here
either.
Rebuffing --celko-- posts these days are like shooting fish in a barrel - it
was a lot more difficult 10 years ago, but it looks like the guy is tired
and even more out of touch than he was 10 years ago.
I'll be blogging this thing tomorrow or Friday - got some time for some
research.
Tony.
"Uri Dimant" <ur...@iscar.co.il> wrote in message
news:#Gt0OqsY...@TK2MSFTNGP02.phx.gbl...
Simon Whale
can you please post the link to your blog here as i would like to read it!
it would be good to see where a cursor is better at a task than a set based
approach
Thanks
Simon
"Tony Rogerson" <tonyro...@torver.net> wrote in message
news:E45A970B-1570-45AE...@microsoft.com...
Tony Rogerson
the recording and presentation is here:
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/03/21/sqlbits-20080301-birmingham-making-the-leap-into-advanced-tsql.aspx
But, I will focus specifically on this comparison cursor v set for rolling
totals.
Tony.
"Simon Whale" <si...@nospam.com> wrote in message
news:uMpk$qtYKH...@TK2MSFTNGP02.phx.gbl...
Uri Dimant
Have you looked into Hugo's solution for running totals ( his chapter in
MVP books). It was definitely fast than my another solution , but
unfortunately I have not compared it with a cursor.
"Tony Rogerson" <tonyro...@torver.net> wrote in message
news:E45A970B-1570-45AE...@microsoft.com...
Dave Ballantyne
Quirky update.
http://www.sqlservercentral.com/articles/T-SQL/68467/
Dave Ballantyne
http://sqlblogcasts.com/blogs/sqlandthelike/
Tony Rogerson
That's been in the product for a long time and its best avoided unless all
the criteria are met like uniqueness on the keys and stuff but I don't think
using it in this way is good practice.
Tony
"Dave Ballantyne" <symlink@no5p323mmer> wrote in message
news:erhW0LuY...@TK2MSFTNGP05.phx.gbl...
TheSQLGuru
complete implementation of windowing functions to at LEAST include the
ability to do efficient running totals. It is SUCH a common application
need!
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Tony Rogerson" <tonyro...@torver.net> wrote in message
news:E45A970B-1570-45AE...@microsoft.com...
--CELKO--
Ditto at SQL Connections. The programmers want it, but more
importantly the MS SQL language and engine people were interested.
>> It is SUCH a common application need! <<
and such a common feature in other products
Tony Rogerson
Looks like you are right; anyway - I'll post a new thread on the ng shortly.
He's not getting away from this one.
At least its kept him quiet for a couple of days - that's a couple of days
of him not trolling - that must be a bonus!
Tony
"Uri Dimant" <ur...@iscar.co.il> wrote in message
news:#Gt0OqsY...@TK2MSFTNGP02.phx.gbl...
--CELKO--
What has kept me quiet for the last two weeks has been speaking at
PASS followed by SQL Connection, a book review for Amazon, material
for SQL FOR SMARTIES 4-th, servicing some clients and looking for more
gigs.
Tony, your off-topic personal insults and outbursts without any
supporting material are just not the important to me. I am doing this
while two weeks of laundry is running, so let me try to stay on topic,
here is what I found out on the road:
1) Microsoft SQL people want to move to more and more standard SQL.
This is why the DATE data type defaults to "yyyy-mm-dd" and not
"yyyymmdd" among other things. Look for more of this later. Oh,
this has been one of your rants against me, why have you not commented
that decision?
2) The [ROWS | RANGE] sub-clause in OVER () is considered important.
That means your cursor to do running totals might become less
desirable in a few releases.
Frankly, I have avoided database side running totals because I
consider them reporting and not data retrieval. Front end report
tools do a better job than cursors. The few times I did them was with
a scalar subquery in the SELECT clause; small data sets and a quick
solution that would not have to port or to scale.
3) There are other easy, but less requested, standards that could be
added. While it is good to listen to the customers, they do not know
that they want something until they see it -- Post-It notes comes to
mind.
I am going to propose that TRANSLATE() be considered. Then usual
nested REPLACE() is pretty effective, but hard to non-LISP programmers
to read :)
While it is easy to maintain with a recursive text editor, it is bitch
to read.
Tony Rogerson
Kettle - pot - black.
Whilst I have a one to one relationship in terms of --ROGGIE-- to {poster}
where {poster} = '--CELKO', you on the other hand insult the majority of
posters - granted you have been better in the last WEEK - but 7 days
compared to 10 years is a drop in the ocean in terms of YOUR OWN attitude.
Now - stop insulting people on this forum.
Keep your posts relevant and accurate so the advice you give is actually
relevant and actually works on the product this forum has a context for ie
MICROSOFT SQL SERVER.
> supporting material are just not the important to me. I am doing this
> while two weeks of laundry is running, so let me try to stay on topic,
I always stay on topic; you know that, people on here know that - and
believe me it will continue until you get a grasp of the real world or stap
strap lining your posts with something like "this post is only relevant to
the ANSI SQL standard, it may not work on this product".
Now, on the other hand it is you that drifts off topic, especially when
challenged on something (usually real world) that you have no defense for,
for instance but not limited to - consistency of date formats within SQL
SERVER, ie you must use YYYYMMDD, the use of surrogate keys within the
product and our necessity in using the IDENTITY property, your rants about
temporary tables being some sort of scratch tape - enough typing, but I
could go on.
Anyway, out of interest - why have you parked yourself on this forum, why do
we have to put up with your canned crap and out of date answers? I never see
you on the Oracle nor DB2 forums, the products you are so familiar with!??!
> 1) Microsoft SQL people want to move to more and more standard SQL.
> This is why the DATE data type defaults to "yyyy-mm-dd" and not
> "yyyymmdd" among other things. Look for more of this later. Oh,
> this has been one of your rants against me, why have you not commented
> that decision?
It's a good move forward and I look forward to when EVERYBODY is on SQL
Server 2008.
Now, what about the majority of customers who aren't on SQL Server 2008?
What about the "legacy code museum" as you put it? What about the "real
world"??
Now, lets throw this around the other way - why aren't you using infix
notation when the great majority, including ALL examples in books online,
system stored procedures and KB articles are all infix whereas you insist on
using the old style join in the WHERE - a very dangerous concept.
> 2) The [ROWS | RANGE] sub-clause in OVER () is considered important.
> That means your cursor to do running totals might become less
> desirable in a few releases.
How exactly does that help the developer TODAY in writing a solution that
actually scales? Answer: it doesn't.
>
> Frankly, I have avoided database side running totals because I
> consider them reporting and not data retrieval. Front end report
> tools do a better job than cursors. The few times I did them was with
> a scalar subquery in the SELECT clause; small data sets and a quick
> solution that would not have to port or to scale.
No doubt because you realise its a real world and common requirement that a
set based solution just cannot deliver.
I still wait for some examples in comparison to cursors.
And yet again you rely on yet another product, yet another developer to do
what can already be done in the engine - one skill set, on set of code to
develop, support and maintain.
> 3) There are other easy, but less requested, standards that could be
> added. While it is good to listen to the customers, they do not know
> that they want something until they see it -- Post-It notes comes to
> mind.
I'm in a perfect position to answer this question - I'm out there in the
field actually listening to customers and understanding the challenges
myself.
The question is - are you? I think you are just relying on and listening to
hearsay when you come up with these conclusions - a perfect example of that
is your opinions (though wrong) about CLR.
This is where you could do us all a favour - if you want to be taken
seriously - get out in the real world back into industry and see what is
actually going on, what business needs - stop guessing! You cannot draw an
industrial opinion on what is posted in this forum which seems to be what
you are doing now you have stagnated for the past decade.
>
> I am going to propose that TRANSLATE() be considered. Then usual
> nested REPLACE() is pretty effective, but hard to non-LISP programmers
> to read :)
>
> While it is easy to maintain with a recursive text editor, it is bitch
> to read.
1000 parameters, 52 nested REPLACE's, code anything that is not set
achievable in C or COBOL.
Hardly practical in the real world.
Anyway - I'm here for good --celko--; given your attitude on here for the
past decade its time you are brought to task and people shown just how out
of touch you really are.
--ROGGIE--
--CELKO--
My feeling is that the old UNIX/Sybase time model is deprecated. I
have no authority to speak for MS, <<insert NDA text here>>. I said
this before, I want to see CAST (<string exp> AS <temporal data type>)
accept a wide range of ISO strings --NOT LOCAL DIALECT -- for values.
I want no spaces, "T", timezone offsets, etc,. But we must have one
and only one output string. The CONVERT codes were from decades ago
in Unix and 16bit machines when there was little if any tiered
architecture.
>> Anyway, out of interest - why have you parked yourself on this forum, why do we have to put up with your canned crap and out of date answers? I never see you on the Oracle nor DB2 forums, the products you are so familiar with !??! <<
I go to Oracle forums when I have an Oracle problem. I go to DB2
forums when I have a general problem. DB2 is better about Standards
and then people are sharp. For the record, I am not a product expert
for any of the 17+ SQLs that I have worked for/used/evaluated. I am
the SQL guy, not the product guy.
>> It's a good move forward and I look forward to when EVERYBODY is on SQL Server 2008. <<
Me, too! But I also expect them to move to other platforms (Ticket
Masters from SQL Server to MySQL, etc). And I want them to be ready
for SQL Server 20xx because I gave them portable code instead of
current version stuff.
>> Now, what about the majority of customers who aren't on SQL Server 2008? What about the "legacy code museum" as you put it? What about the "real world"? <<
They are now yet my clients. I am usually doing upgrades,
ports,initial designs, etc
>> How exactly does that [ROW subclause] help the developer TODAY in writing a solution that actually scales? Answer: it doesn't. <<
Yes. And that is one of many reasons he needs to upgrade. But he
needs to know about "the world outside the village" so he can make an
informed decision.
Granted that I get consultation work at a higher level. Many of my
favorite moments have been people who thanked me for my books or code
with comments.
>> No doubt because you realize its a real world and common requirement that a set based solution just cannot deliver. <<
Actually, the WINDOW clause on aggregates works pretty good in DB2 and
Oracle, but I would still rather do it in the reporting tools. I
really try to keep a tiered architecture as pure s possible. Based on
decades of software engineering research, high cohesion and loose
coupling make better systems in the long run.
We have had this discussion before. You want to treat SQL Server as a
monolithic un-tiered solution, much like Cobol was, do stuff in any
tier if you have a fast proprietary solution, etc. This is classic
Cowboy Coding.
>> I still wait for some examples in comparison to cursors.<<
Sure thing! Right after you post that Sodoku solution you wrote in 5
minutes sooo many months ago :) If you wish, I can post the Emails
between you and Richard.
>> And yet again you rely on yet another product, yet another developer to do what can already be done in the engine - one skill set, on set of code to develop, support and maintain. <<
"A child with a hammer thinks everything is a nail" -- old proverb
A professional uses the right tool for the job. In the tiered
architecture model that replaced the monolithic model you advocate, I
do the database side of things. Period. I worry about metadata,
changes in Industry Standards. external data sources, constraints, and
all that "data stuff" so that I can throw it over the wall to the
front end guys.
When I am on the other side of the wall, I wear a different hat and
have different rules and goals at that tier.
>> I'm in a perfect position to answer this question - I'm out there in the field actually listening to customers and understanding the challenges myself. The question is - are you? <<
Tony, I was one of the few (sometimes ONLY) non-vendor ANSI X3H2
Committee members. When I did my columns in the early days, I was the
accessibility to the Standards for the for ALL the guys in the field.
>> I think you are just relying on and listening to hearsay when you come up with these conclusions - a perfect example of that is your opinions (though wrong) about CLR. <<
Tens of thousands of readers is not exactly hearsay; it is feedback.
You might want to look at numbers for CLR performance at the
presentations at PASS and SQL Connections. It is still too new to get
the REAL measure of value - maintainability. But if the last 30 years
of software engineering were right about mixed languages, then I am
correct.
If you think that CLR inside the schema is a good thing, can you list
the various ways that MOD() is defined in all 43 (last count I saw,
might be more) CLR languages? Remember xBase versus Cobol versus SQL
string comparison rules? The Pascal Standards debate on MOD()?
DUIH.
Now try to do data integrity in such a "Wiley Coyote/ Loony Tunes"
data model universe.
>> You cannot draw an industrial opinion on what is posted in this forum which seems to be what you are doing now you have stagnated for the past decade. <<
One posting you fault me for being "too academic, living with
technology nobody has, etc." and now you fault me for being a decade
behind. Wow! I am versatile!
I might also point out that you cannot get an industrial opinion from
a few companies in one industry in one country. Remember a thread
where I advocated VIN numbers and you said that since they are not as
important in the UK, I was wrong using them as a key? Someone did
"Google Diligence", hit that thread, emailed me and it might lead to
some work next year.
The truth is that the Standards in IT used to be behind the products
and were a clean up afterward. But SQL is unique in being ahead of
the products. Of the "Big Three" SQL Server is the weakest and lags
behind DB2, Oracle and some other products. But, Microsoft wants to
catch up and has the money/smarts to do it.
>> 1000 parameters, 52 nested REPLACE's, code anything that is not set achievable in C or COBOL. Hardly practical in the real world. <<
LOL! When is the last time you talked to an optimizer internals
person about how they do thing? The parameter list length in SQL
Server 2008 is set at 2100 for a reason. I need to find time to look
over the long-parm-list, XML, string parser, etc documents from SQL
Connections, but the basic idea was that long-parm-list was used
internally when you needed speed and validation.
Talking to DB2 and Oracle internals people, the TRANSLATE () stuff is
done as a stack (aka recursion) rather than a C or Cobol style loop.
Modern hardware makes that easier and faster than loops.
>> Anyway - I'm here for good --CELKO--; given your attitude on here for the past decade its time you are brought to task and people shown just how out of touch you really are. <<
Gee, you might want to tell that to the advanced technology venture
capital people here in Austin :) I have fooled them without any
effort on my part!
Oh, thanks for sticking to topic and not making personal remarks.
Tony Rogerson
>>> challenged on something (usually real world) that you have no defense
>>> for, for instance but not limited to - consistency of date formats
>>> within SQL SERVER, ie you must use YYYYMMDD, the use of surrogate keys
>>> within the product and our necessity in using the IDENTITY property,
>>> your rants about temporary tables being some sort of scratch tape -
>>> enough typing, but I could go on. <<
>
> My feeling is that the old UNIX/Sybase time model is deprecated. I
> have no authority to speak for MS, <<insert NDA text here>>. I said
> this before, I want to see CAST (<string exp> AS <temporal data type>)
> accept a wide range of ISO strings --NOT LOCAL DIALECT -- for values.
> I want no spaces, "T", timezone offsets, etc,. But we must have one
> and only one output string. The CONVERT codes were from decades ago
> in Unix and 16bit machines when there was little if any tiered
> architecture.
"You're" feeling; so you are saying that yourself as somebody who only does
the odd training gig and the odd consultancy gig and is so anal about the
SQL standard that anything else you stick your head in the sand has a more
current real world opinion as somebody who works day in day out with the
product, is a SQL MVP so gets the thread of where the product is going from
private groups where other leading SQL Server experts and dev team post.
Come on - get real.
What you want as a purist is totally incompatible with what people in
business - the folk who pay the bills and buy the product need to sort real
world business problems.
>
>>> Anyway, out of interest - why have you parked yourself on this forum,
>>> why do we have to put up with your canned crap and out of date answers?
>>> I never see you on the Oracle nor DB2 forums, the products you are so
>>> familiar with !??! <<
>
> I go to Oracle forums when I have an Oracle problem. I go to DB2
> forums when I have a general problem. DB2 is better about Standards
> and then people are sharp. For the record, I am not a product expert
> for any of the 17+ SQLs that I have worked for/used/evaluated. I am
> the SQL guy, not the product guy.
So you must have one hell of a lot of problems with SQL Server.
But that is not what I asked - why do you troll this news group? You never
post on Oracle nor DB2 groups, checking those forums its clear to see why -
not much standard SQL going on there.
>
>>> It's a good move forward and I look forward to when EVERYBODY is on SQL
>>> Server 2008. <<
>
> Me, too! But I also expect them to move to other platforms (Ticket
> Masters from SQL Server to MySQL, etc). And I want them to be ready
> for SQL Server 20xx because I gave them portable code instead of
> current version stuff.
I can sit here and list a number of companies that have migrated to SQL
Server as well.
But lets again look at this "canned" piece of knowledge - it is from 2001
which at the time was SQL Server 2000 and that version didn't have the HA
stuff we now have in SQL Server 2005 and SQL Server 2008.
This is your problem - you have stagnated, you are so opinionated you don't
keep up with the industry - this is one of my biggest points and objections
on your posts.
So, why are you basing your entire opinion that folk migrate away from SQL
Server on something that happened 2 versions (3 if you could R2) and over 8
years ago?
>
>>> Now, what about the majority of customers who aren't on SQL Server
>>> 2008? What about the "legacy code museum" as you put it? What about the
>>> "real world"? <<
>
> They are now yet my clients. I am usually doing upgrades,
> ports,initial designs, etc
>
In your dreams, the reality as you well know is somewhat different.
>>> How exactly does that [ROW subclause] help the developer TODAY in
>>> writing a solution that actually scales? Answer: it doesn't. <<
>
> Yes. And that is one of many reasons he needs to upgrade. But he
> needs to know about "the world outside the village" so he can make an
> informed decision.
>
> Granted that I get consultation work at a higher level. Many of my
> favorite moments have been people who thanked me for my books or code
> with comments.
>
Lol - you rarely get anything other than a training gig - you know that and
it really bites you in the arse.
Face it - your opinionated attitude on this forum has become a preventer for
you to be employed with any frequency.
PS... I don't have enough time in the week to keep up the work I'm getting!
>>> No doubt because you realize its a real world and common requirement
>>> that a set based solution just cannot deliver. <<
>
> Actually, the WINDOW clause on aggregates works pretty good in DB2 and
> Oracle, but I would still rather do it in the reporting tools. I
> really try to keep a tiered architecture as pure s possible. Based on
> decades of software engineering research, high cohesion and loose
> coupling make better systems in the long run.
>
> We have had this discussion before. You want to treat SQL Server as a
> monolithic un-tiered solution, much like Cobol was, do stuff in any
> tier if you have a fast proprietary solution, etc. This is classic
> Cowboy Coding.
>
Again, stuck 15 years ago where what you state is true.
We've (the industry) has moved on.
>>> I still wait for some examples in comparison to cursors.<<
>
> Sure thing! Right after you post that Sodoku solution you wrote in 5
> minutes sooo many months ago :) If you wish, I can post the Emails
> between you and Richard.
>
Its on my list, but not very high - and yes, I too have the emails of what I
said - I don't delete emails and can't find this reference to "5 minutes" -
can you give me date and time and my quote so I can validate you aren't just
spouting BS as usual.
>>> And yet again you rely on yet another product, yet another developer to
>>> do what can already be done in the engine - one skill set, on set of
>>> code to develop, support and maintain. <<
>
> "A child with a hammer thinks everything is a nail" -- old proverb
>
> A professional uses the right tool for the job. In the tiered
> architecture model that replaced the monolithic model you advocate, I
> do the database side of things. Period. I worry about metadata,
> changes in Industry Standards. external data sources, constraints, and
> all that "data stuff" so that I can throw it over the wall to the
> front end guys.
>
> When I am on the other side of the wall, I wear a different hat and
> have different rules and goals at that tier.
>
Yep - out of touch, stagnated.
There has been greater integration between "the front end guys" and "the sql
guys"; basically the wall came down a long time ago and with stuff like LINQ
its getting better.
>>> I'm in a perfect position to answer this question - I'm out there in the
>>> field actually listening to customers and understanding the challenges
>>> myself. The question is - are you? <<
>
> Tony, I was one of the few (sometimes ONLY) non-vendor ANSI X3H2
> Committee members. When I did my columns in the early days, I was the
> accessibility to the Standards for the for ALL the guys in the field.
>
Yep - no industrial experience.
Where did you serve your apprenticeship in industry? I think its a case you
did your degree and then started writing books.
When was the last time you spent a few weeks on a client project writing SQL
that had to produce something - frankly - have you ever done it?
>>> I think you are just relying on and listening to hearsay when you come
>>> up with these conclusions - a perfect example of that is your opinions
>>> (though wrong) about CLR. <<
>
> Tens of thousands of readers is not exactly hearsay; it is feedback.
>
> You might want to look at numbers for CLR performance at the
> presentations at PASS and SQL Connections. It is still too new to get
> the REAL measure of value - maintainability. But if the last 30 years
> of software engineering were right about mixed languages, then I am
> correct.
>
More rubbish. Instead of introducing myths and rumours - post a link.
> If you think that CLR inside the schema is a good thing, can you list
> the various ways that MOD() is defined in all 43 (last count I saw,
> might be more) CLR languages? Remember xBase versus Cobol versus SQL
> string comparison rules? The Pascal Standards debate on MOD()?
> DUIH.
>
> Now try to do data integrity in such a "Wiley Coyote/ Loony Tunes"
> data model universe.
>
So - you can successfully and efficiently validate an email address in a
CHECK constraint can you? I'd like to see that code please.
Most folk would use CLR and regex.
>>> You cannot draw an industrial opinion on what is posted in this forum
>>> which seems to be what you are doing now you have stagnated for the past
>>> decade. <<
>
> One posting you fault me for being "too academic, living with
> technology nobody has, etc." and now you fault me for being a decade
> behind. Wow! I am versatile!
>
> I might also point out that you cannot get an industrial opinion from
> a few companies in one industry in one country. Remember a thread
> where I advocated VIN numbers and you said that since they are not as
> important in the UK, I was wrong using them as a key? Someone did
> "Google Diligence", hit that thread, emailed me and it might lead to
> some work next year.
>
Really, here is my resume:
http://www.sql-server.co.uk/tonyrogersonresume.pdf; I had to bring one
up-to-date for a masters in business intelligence I want to do next year.
In a nutshell I've worked for nearly 100 companies in the past decade
covering all industry sectors.
I have not stagnated, I'm not opinionated - I listen to people and learn, I
keep current in the space and within business - I have my own specialist
hosting company which hosts research websites for investment banks.
> The truth is that the Standards in IT used to be behind the products
> and were a clean up afterward. But SQL is unique in being ahead of
> the products. Of the "Big Three" SQL Server is the weakest and lags
> behind DB2, Oracle and some other products. But, Microsoft wants to
> catch up and has the money/smarts to do it.
>
It's so far behind what business wants its laughable which is why vendors
have so many extensions away from the standard.
>>> 1000 parameters, 52 nested REPLACE's, code anything that is not set
>>> achievable in C or COBOL. Hardly practical in the real world. <<
>
> LOL! When is the last time you talked to an optimizer internals
> person about how they do thing? The parameter list length in SQL
> Server 2008 is set at 2100 for a reason. I need to find time to look
> over the long-parm-list, XML, string parser, etc documents from SQL
> Connections, but the basic idea was that long-parm-list was used
> internally when you needed speed and validation.
>
How its serialised internally in the 3gl is no concern to me when I use the
abstracted T-SQL code which I need to worry about in terms of support,
maintainability etc...
But yes - I've Ken Hendersons internals book on my shelf - I technical
edited it - look in the preface where he thanks me.
> Talking to DB2 and Oracle internals people, the TRANSLATE () stuff is
> done as a stack (aka recursion) rather than a C or Cobol style loop.
> Modern hardware makes that easier and faster than loops.
>
>>> Anyway - I'm here for good --CELKO--; given your attitude on here for
>>> the past decade its time you are brought to task and people shown just
>>> how out of touch you really are. <<
>
> Gee, you might want to tell that to the advanced technology venture
> capital people here in Austin :) I have fooled them without any
> effort on my part!
>
> Oh, thanks for sticking to topic and not making personal remarks.
Ah - so you are working frequently day to day - my apologies.
--ROGGIE--
Erland Sommarskog
> Me, too! But I also expect them to move to other platforms (Ticket
> Masters from SQL Server to MySQL, etc). And I want them to be ready
> for SQL Server 20xx because I gave them portable code instead of
> current version stuff.
No matter how many times you say it, YYYY-MM-DD *isn't* portable. It does
not work safe only SQL 2005 and previous version. You are only doing
people a disservice with using this format.
Well, of course, there is no reason to assume that you are interested in
helping people anyway. Actually, you have more than once proven the
opposite.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
--CELKO--
in the SQL Standards. And that the DATE data type defaults to it for
display in SQL Server 2008. You can confirm that easily. It looks
like Microsoft is slowly moving toward Standards.
Tony Rogerson
Look at the standard - its there in black and white.
It requires the DATE prefix to signify that the string literal is actually a
SQL standard date; otherwise it is a string literal which can be any ISO
(note - not ISO SQL) date, so unless Oracle, DB2 and SQL Server implement
the DATE prefix then it ain't a true ISO SQL DATE.
See http://savage.net.au/SQL/sql-92.bnf.html#date literal
<date literal> is defined as:
DATE 'YYYY-MM-DD'
The above is correct and ISO SQL standard.
We don't have that syntax and its not in SQL Server 2008 either and there
aren't any plans for that that I've seen or heard talked about.
We have a string literal 'YYYYMMDD' which is converted to a date type using
regional settings; YYYYMMDD is an ISO date.
I'm stunned you don't know this and even more stunned that although you've
been told the above countless times you don't accept you are wrong and just
keep perpetuating the same incorrect answer around dates.
--ROGGIE--
"--CELKO--" <jcel...@earthlink.net> wrote in message
news:f60dc9db-5fb2-416e...@e23g2000yqd.googlegroups.com...
Erland Sommarskog
><date literal> is defined as:
>
> DATE 'YYYY-MM-DD'
>
> The above is correct and ISO SQL standard.
>
> We don't have that syntax and its not in SQL Server 2008 either and there
> aren't any plans for that that I've seen or heard talked about.
vote here:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=356470
Tony Rogerson
Tony.
"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9CC4EDE07...@127.0.0.1...