March 17, 2009 at 9:48 pm
And also, foxjazz, you are missing one of your tables in that hugh script that is needed in the code that follows. You know, you are really making it hard to prove you wrong when you constantly leave things out.
March 17, 2009 at 10:25 pm
Even though you are missing code from your setup script, missing an important table from your setup script, didn't provide ANY test data or expected results based on that data, here is a set based replacement to your cursor based loop process.
delete PrdAttribute
DBCC CHECKIDENT ('dbo.prdattribute', RESEED, 1)
INSERT INTO dbo.PrdAttribute (
ProductAttribute,
ProductAttributeNameID,
ProductID,
changeddate,
changedtpe,
changedmachinename,
changedusername)
SELECT DISTINCT
pad.[Product Specification], -- @attribute from your cursor
pan.ProductAttributeNameID, -- from dbo.PrdAttributeName
p.ProductID, -- from dbo.PrdInfo
getdate(),
'I',
'Refresh',
'Refresh'
FROM
APAPartSource.dbo.ProductAttributeData pad
INNER JOIN dbo.PrdCategory pc
on (pad.[Product Type] = pc.ProductCategoryDesc) -- pad.[Product Type] is @cattype in your cursor
INNER JOIN dbo.PrdAttributeName pan
on (pad.[Product Attribute] = pan.ProductAttributeName -- pad.[Product Attribute] is @attributename in your cursor
and pc.ProductCategoryID = pan.ProductCategoryID
and pan.ProductAttributeTypeID = 1)
INNER JOIN dbo.PrdInfo p
on (pad.[Part Number] = p.PartNumber -- pad.[Part Number] is @PartNumber from your cursor
and pad.[Product Line] = p.ProductLine) -- pad.[Product Line] is @productline from your cursor
If this isn't correct it is due to poor requirements (your error), poor test setup (your error), no test data and expected results (your error).
Edit: As I look at this code, I may have another version shortly.
March 17, 2009 at 10:31 pm
foxjazz (3/17/2009)
I agree. If I can do more stuff without the use of cursors, I am happier.Some things just have to be looped. Like for instance, sequence numbers.
but we will get to that jazz later.
No they don't. You just need to know how to do it without a loop.
March 17, 2009 at 10:46 pm
As I mentioned above, here is a second version of the code. It is quite possible that both of these may return different results. I have no idea as I couldn't test the code because you didn't provide everything necessary to test and validate the code. So, as I also said above, any failure with this code is your problem, not mine.
delete PrdAttribute
DBCC CHECKIDENT ('dbo.prdattribute', RESEED, 1)
INSERT INTO dbo.PrdAttribute (
ProductAttribute,
ProductAttributeNameID,
ProductID,
changeddate,
changedtpe,
changedmachinename,
changedusername)
SELECT
pad.attribute,
pan.ProductAttributeNameID,
p.ProductID,
getdate(),
'I',
'Refresh',
'Refresh'
FROM
(SELECT DISTINCT
[Product Attribute] as attributename, -- @attributename in cursor
[Product Line] as productline, -- @productline in cursor
[Part Number] as partnumber, -- @PartNumber in cursor
[Product Type] as cattype, -- @cattype in cursor
[Product Specification] as attribute -- @attribute in cursor
FROM
APAPartSource.dbo.ProductAttributeData ) pad
INNER JOIN dbo.PrdCategory pc
on (pad.cattype = pc.ProductCategoryDesc)
INNER JOIN dbo.PrdAttributeName pan
on (pad.attributename = pan.ProductAttributeName
and pc.ProductCategoryID = pan.ProductCategoryID
and pan.ProductAttributeTypeID = 1)
INNER JOIN dbo.PrdInfo p
on (pad.partnumber = p.PartNumber
and pad.productline = p.ProductLine)
March 17, 2009 at 11:06 pm
March 17, 2009 at 11:08 pm
This can easily be done with a setbase solution????????????
Yes it can, Fox. 🙂 Don't be amazed. We do this for a living. It's not magic.
Lynn already posted while I was typing this up, but I think you might profit from a little step-by-step, so I'll post anyway.
Here is the heart of the code in your last post.
set @sel = cursor for
SELECT DISTINCT [Product Attribute],[Product Line],[Part Number],[Product Type],[Product Specification]
FROM APAPartSource.dbo.ProductAttributeData
open @sel
fetch next from @sel into @attributename,@productline,@PartNumber, @cattype,@attribute
while (@@FETCH_STATUS = 0)
begin
select @catid = ProductCategoryID from prdcategory where productcategorydesc = @cattype
select @attributeNameid = ProductAttributeNameID from prdattributename where ProductAttributeName = @attributeName and ProductCategoryID = @catid and productattributetypeid = 1
select @productid = ProductID from PrdInfo where partnumber = @partnumber and productline = @productline
insert into PrdAttribute ( ProductAttribute, ProductAttributeNameID, ProductID, changeddate,changedtype,changedmachinename, changedusername)
values (@attribute,@attributeNameid,@productid,getdate(),'I','Refresh','Refresh')
fetch next from @sel into @attributename,@productline,@PartNumber, @cattype,@attribute
end
close @sel
Time to break it down. Here's what your code is doing:
1. Define a set of rows to be retrieved from the table ProductAttributeDate, which I'll call "P1",
BUT use a cursor so you handle it a row at a time.
2. For each row:
Assign values to variables from the columns in ProductAttributeDate ("P1")
a. set @attributeName = P1.[product attribute]
b. set @productLine = P1.[product line]
c. set @partnumber = P1.[part number]
d. set @catType = P1.[product type]
e. set @attribute = P1.[product specification]
Then, assign a value to a variable from a column in the prdcategory table ("P2")
f. set @catID = P2.productCategoryID
Then, assign a value to a variable from a column in the prdAttributeName table ("P3")
g. set @attributeNameID = P3.productAttributeNameID
Then, assign a value to a variable from a column in the prdInfo table ("P4")
h. set @productID = P4.ProductID
Then, insert the following variables, and a few constants into prdAttribute
insert into PrdAttribute ( ProductAttribute, ProductAttributeNameID, ProductID, changeddate,changedtype,changedmachinename, changedusername)
values (@attribute,@attributeNameid,@productid,getdate(),'I','Refresh','Refresh')
Finally, get the next row, loop back and see if you really did, and repeat.
The handling of variables for each row is procedural coding. Now think back to your first algebra course.
A = B, B = C :. (therefore) A = C
Your variables are B. We can cut them out entirely.
Set based processing is transferring values from column to column for 1 to N rows without going through intermediate variables.
We can get the source columns from a SELECT query immediately following an INSERT INTO
So, all we really need is:
insert into PrdAttribute ( ProductAttribute, ProductAttributeNameID, ProductID, changeddate,changedtype,changedmachinename, changedusername)
-- values (@attribute, @attributeNameid, @productid, getdate(),'I','Refresh','Refresh')
select P1.[Product Specification], P1.[Product Attribute], P4.productID, getdate(),'I','Refresh','Refresh'
Of course the Select needs to join in the other tables, so we can reach P4. The full query would look like this:
insert into PrdAttribute ( ProductAttribute, ProductAttributeNameID, ProductID, changeddate,changedtype,changedmachinename, changedusername)
select DISTINCT P1.[Product Specification], P1.[Product Attribute], P4.productID, getdate(),'I','Refresh','Refresh'
FROM APAPartSource.dbo.ProductAttributeData P1
JOIN prdCategory P2 on productCategoryDesc = [product type]
JOIN prdAttributeName p3 on ProductAttributeName = [product attribute] and p3.productCategoryID = p2.productCategoryID and productAttributeTypeID = 1
JOIN prdInfo p4 on p4.partNumber = [Part Number] and productLine = [Product Line]
That's it. Those six lines are a set based solution and they will run much faster than your cursor.
The database engine was designed for this and contains highly refined routines to get the job done.
Now, it's almost midnight and I'm typing most of this freehand, so it hasn't been debugged nor tested.
Read it and think about it. They key is that you don't need to do inserts from variables, which means
you don't need to assign values to variables one row at a time. You just take entire columns and go.
I think that's what is hanging you up right there. You are trained to focus on one value at a time.
SQL was designed to do something entirely different, which is to perform identical operations to entire
columns at a time.
Update SomeTable
Set SomeColumn = 'A'
We don't assign values to cells like in a spreadsheet. We deal with entire columns.
Joins and where clauses can limit which rows get affected, but values are assigned to entire columns
The light bulb over your head is going to go on any minute now. Hang in there.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 17, 2009 at 11:13 pm
I'm sure it is a lesson in futility. I seriously doubt that foxjazz will ever admit defeat and apologize to us all for his lack of true understanding of the power of T-SQL when applied correctly.
I know he will find some fault with the code, not admittig that he did not provide everything needed to properly test and validate the code.
He'll propably even throw us another "test" and say try this one now without using a cursor.
But, it is keeping my skills fresh and the mind working which aids me in the end. 😉
March 17, 2009 at 11:16 pm
Bob Hovious (3/17/2009)
This can easily be done with a setbase solution????????????
Yes it can, Fox. 🙂 Don't be amazed. We do this for a living. It's not magic.
Lynn already posted while I was typing this up, but I think you might profit from a little step-by-step, so I'll post anyway.
Here is the heart of the code in your last post.
set @sel = cursor for
SELECT DISTINCT [Product Attribute],[Product Line],[Part Number],[Product Type],[Product Specification]
FROM APAPartSource.dbo.ProductAttributeData
open @sel
fetch next from @sel into @attributename,@productline,@PartNumber, @cattype,@attribute
while (@@FETCH_STATUS = 0)
begin
select @catid = ProductCategoryID from prdcategory where productcategorydesc = @cattype
select @attributeNameid = ProductAttributeNameID from prdattributename where ProductAttributeName = @attributeName and ProductCategoryID = @catid and productattributetypeid = 1
select @productid = ProductID from PrdInfo where partnumber = @partnumber and productline = @productline
insert into PrdAttribute ( ProductAttribute, ProductAttributeNameID, ProductID, changeddate,changedtype,changedmachinename, changedusername)
values (@attribute,@attributeNameid,@productid,getdate(),'I','Refresh','Refresh')
fetch next from @sel into @attributename,@productline,@PartNumber, @cattype,@attribute
end
close @sel
Time to break it down. Here's what your code is doing:
1. Define a set of rows to be retrieved from the table ProductAttributeDate, which I'll call "P1",
BUT use a cursor so you handle it a row at a time.
2. For each row:
Assign values to variables from the columns in ProductAttributeDate ("P1")
a. set @attributeName = P1.[product attribute]
b. set @productLine = P1.[product line]
c. set @partnumber = P1.[part number]
d. set @catType = P1.[product type]
e. set @attribute = P1.[product specification]
Then, assign a value to a variable from a column in the prdcategory table ("P2")
f. set @catID = P2.productCategoryID
Then, assign a value to a variable from a column in the prdAttributeName table ("P3")
g. set @attributeNameID = P3.productAttributeNameID
Then, assign a value to a variable from a column in the prdInfo table ("P4")
h. set @productID = P4.ProductID
Then, insert the following variables, and a few constants into prdAttribute
insert into PrdAttribute ( ProductAttribute, ProductAttributeNameID, ProductID, changeddate,changedtype,changedmachinename, changedusername)
values (@attribute,@attributeNameid,@productid,getdate(),'I','Refresh','Refresh')
Finally, get the next row, loop back and see if you really did, and repeat.
The handling of variables for each row is procedural coding. Now think back to your first algebra course.
A = B, B = C :. (therefore) A = C
Your variables are B. We can cut them out entirely.
Set based processing is transferring values from column to column for 1 to N rows without going through intermediate variables.
We can get the source columns from a SELECT query immediately following an INSERT INTO
So, all we really need is:
insert into PrdAttribute ( ProductAttribute, ProductAttributeNameID, ProductID, changeddate,changedtype,changedmachinename, changedusername)
-- values (@attribute, @attributeNameid, @productid, getdate(),'I','Refresh','Refresh')
select P1.[Product Specification], P1.[Product Attribute], P4.productID, getdate(),'I','Refresh','Refresh'
Of course the Select needs to join in the other tables, so we can reach P4. The full query would look like this:
insert into PrdAttribute ( ProductAttribute, ProductAttributeNameID, ProductID, changeddate,changedtype,changedmachinename, changedusername)
select DISTINCT P1.[Product Specification], P1.[Product Attribute], P4.productID, getdate(),'I','Refresh','Refresh'
FROM APAPartSource.dbo.ProductAttributeData P1
JOIN prdCategory P2 on productCategoryDesc = [product type]
JOIN prdAttributeName p3 on ProductAttributeName = [product attribute] and p3.productCategoryID = p2.productCategoryID and productAttributeTypeID = 1
JOIN prdInfo p4 on p4.partNumber = [Part Number] and productLine = [Product Line]
That's it. Those six lines are a set based solution and they will run much faster than your cursor.
The database engine was designed for this and contains highly refined routines to get the job done.
Now, it's almost midnight and I'm typing most of this freehand, so it hasn't been debugged nor tested.
Read it and think about it. They key is that you don't need to do inserts from variables, which means
you don't need to assign values to variables one row at a time. You just take entire columns and go.
I think that's what is hanging you up right there. You are trained to focus on one value at a time.
SQL was designed to do something entirely different, which is to perform identical operations to entire
columns at a time.
Update SomeTable
Set SomeColumn = 'A'
We don't assign values to cells like in a spreadsheet. We deal with entire columns.
Joins and where clauses can limit which rows get affected, but values are assigned to entire columns
The light bulb over your head is going to go on any minute now. Hang in there.
Bob, I thought you and the wife had guests and were watching a movie?? 😉
March 18, 2009 at 5:42 am
foxjazz (3/17/2009)
I agree. If I can do more stuff without the use of cursors, I am happier.Some things just have to be looped. Like for instance, sequence numbers.
but we will get to that jazz later.
When I saw this ...
--delete from category where categorytypeid = 1
declare @sel cursor
declare @make varchar(100)
declare @parentid int
declare @mx int
set @sel = cursor for
SELECT DISTINCT EquipmentMake.EquipmentMake, Category.GenCategoryID
FROM EquipmentMake INNER JOIN
EquipmentModel ON EquipmentMake.EquipmentMakeID = EquipmentModel.EquipmentMakeID INNER JOIN
EquipmentType ON EquipmentModel.EquipmentTypeID = EquipmentType.EquipmentTypeID INNER JOIN
EquipmentClass ON EquipmentType.EquipmentClassID = EquipmentClass.EquipmentClassID INNER JOIN
Category ON EquipmentClass.EquipmentClass = Category.Description
WHERE (Category.CategoryTypeID = 0)
open @sel
fetch next from @sel into @make,@parentid
while (@@fetch_status = 0)
begin
--select @mx = max(gencategoryid) + 1 from category
insert into category ( categorytypeid,parentid,description,hasrelation)
values(1,@parentid,@make,0)
fetch next from @sel into @make,@parentid
end
... I thought "this guy cannot be serious if he thinks that NEEDS a cursor"!
Since I the solution is so obvious - just reorder the columns and put the SELECT straight into the INSERT!
--delete from category where categorytypeid = 1
insert into category ( categorytypeid,parentid,description,hasrelation)
SELECT DISTINCT
1,
Category.GenCategoryID,
EquipmentMake.EquipmentMake,
0
FROM EquipmentMake INNER JOIN
EquipmentModel ON EquipmentMake.EquipmentMakeID = EquipmentModel.EquipmentMakeID INNER JOIN
EquipmentType ON EquipmentModel.EquipmentTypeID = EquipmentType.EquipmentTypeID INNER JOIN
EquipmentClass ON EquipmentType.EquipmentClassID = EquipmentClass.EquipmentClassID INNER JOIN
Category ON EquipmentClass.EquipmentClass = Category.Description
WHERE (Category.CategoryTypeID = 0)
Jeff Williams already did that. 🙂
You then changed it to this...
open @sel
fetch next from @sel into @make,@parentid
while (@@fetch_status = 0)
begin
--
insert into category (gencategoryid, categorytypeid,parentid,description,hasrelation)
values(@mx,1,@parentid,@make,0)
set @mx = @mx + 1
fetch next from @sel into @make,@parentid
end
...and specified that gencategoryid was no longer an IDENTITY column. The SS2K5 answer is, of course, to use row_number(), but this is an SS2K forum so we'll fake it.
You said not to use IDENTITY, but I assume that was merely on the permanent tables; since it's a feature of T-SQL, you must allow it to used on temporary tables.
create table #MAKE_PARENTID
(i int identity(1,1),
[Description] [varchar](150) NULL,
[ParentID] [int] NULL)
insert #MAKE_PARENTID
SELECT DISTINCT EquipmentMake.EquipmentMake, Category.GenCategoryID
FROM EquipmentMake INNER JOIN
EquipmentModel ON EquipmentMake.EquipmentMakeID = EquipmentModel.EquipmentMakeID INNER JOIN
EquipmentType ON EquipmentModel.EquipmentTypeID = EquipmentType.EquipmentTypeID INNER JOIN
EquipmentClass ON EquipmentType.EquipmentClassID = EquipmentClass.EquipmentClassID INNER JOIN
Category ON EquipmentClass.EquipmentClass = Category.Description
WHERE (Category.CategoryTypeID = 0)
insert into category (gencategoryid, categorytypeid,parentid,description,hasrelation)
select
i+(select isnull(max(gencategoryid),0) from category),
1,
parentid,
make,
0
from
#MAKE_PARENTID
drop table #MAKE_PARENTID
And before you object to the use of a temporary table, take a moment to think what declaring a cursor does!
In order to get some idea of perfromance difference, I wrote a test query based on your example, but using my data (since you didn't provide any).
perf.dbo.tbljournal contains a about 87000 records; column documentnumber is not unique (get reused for different companies and years). Unfortunately, it doesn't have a convenient text field so I've had to convert the amount to get something to test with.
Normally, I'd just use set statistics time on
set statistics io onto check perfromance, but this doesn't work well with cursors, so I've just looked at the elapsed and cpu times.
Since this machine is actually SS2K5, I've added a row_number test as well.
use scratch
set nocount on
-- for timing ...
declare @cpu0 int, @d0 datetime
declare @cpu1 int, @d1 datetime
declare @cpu2 int, @d2 datetime
declare @cpu3 int, @d3 datetime
-- tables
drop table #q
create table #q (gen int,cat int, par bigint,descr varchar(20), rel int)
drop table #r
create table #r (gen int,cat int, par bigint,descr varchar(20), rel int)
drop table #s
create table #s (gen int,cat int, par bigint,descr varchar(20), rel int)
drop table #t
create table #t (i int identity(1,1),make varchar(20), parentid bigint)
--start timing..
set @cpu0=@@cpu_busy
set @d0=getdate()
-- cursor...
declare c cursor for
select
distinct
convert(varchar(20),amount),documentnumber
from
perf.dbo.tbljournal
declare @make varchar(20), @parentid bigint, @mx int
select @mx=isnull(max(gen),0) from #q
open c
fetch next from c into @make, @parentid
while (@@fetch_status=0)
begin
insert into #q (gen,cat,par,descr,rel)
values(@mx,1,@parentid,@make,0)
set @mx=@mx+1
fetch next from c into @make, @parentid
end
close c
deallocate c
set @cpu1=@@cpu_busy
set @d1=getdate()
-- temp_table...
insert #t (make,parentid)
select
distinct
convert(varchar(20),amount),documentnumber
from
perf.dbo.tbljournal
insert #r (gen,cat,par,descr,rel)
select
i+(select isnull(max(gen),0) from #r),
1,
parentid,
make,
0
from
#t
set @cpu2=@@cpu_busy
set @d2=getdate()
-- row_number...
insert #s (gen,cat,par,descr,rel)
select
(row_number() over(order by parentid))+(select isnull(max(gen),0) from #s),
1,
parentid,
make,
0
from (
select
distinct
convert(varchar(20),amount) as 'make',
documentnumber as 'parentid'
from
perf.dbo.tbljournal
) x
set @cpu3=@@cpu_busy
set @d3=getdate()
-- output timing
select
'Cursor' as 'Method',
datediff(ms,@d0,@d1) as 'Elapsed ms',
(@cpu1-@cpu0)*cast(@@timeticks as float)/1000.0 as 'CPU ms'
union all
select
'Temp table' as 'Method',
datediff(ms,@d1,@d2) as 'Elapsed ms',
(@cpu2-@cpu1)*cast(@@timeticks as float)/1000.0 as 'CPU ms'
union all
select
'row_number' as 'Method',
datediff(ms,@d2,@d3) as 'Elapsed ms',
(@cpu3-@cpu2)*cast(@@timeticks as float)/1000.0 as 'CPU ms'
Typical results were...
Method Elapsed ms CPU ms
---------- ----------- ----------------------
Cursor 44070 3906.25
Temp table 1626 906.25
row_number 686 625
As you can see, either a temp table or row_number leaves using a cursor in the dust. I know which one I'd use and I haven't started looking at tuning the tables!
So the question is "why do you think sequence numbers need a cursor?"
P.S. Lynn's already looked at your next problem and Bob's given a good explanation of converting cursors to set-based code, so I'm not going to go through it again.
[Edited to fix a typo in untested code]
Derek
March 18, 2009 at 6:24 am
foxjazz (3/17/2009)
--------------------------------------------------------------------------------
I agree. If I can do more stuff without the use of cursors, I am happier.
Some things just have to be looped. Like for instance, sequence numbers.
but we will get to that jazz later.
NYET ! Absolutely WRONG
Before pontificating on your fixation with cursors, LOOK at the superior solutions mentioned in this marathon thread.
Again, take a look at the use of a tally table as explained in detail by Jeff Moden. And the other article I mention. Both explain clearly why a cursor is NOT needed. All you have to do is to follow the links in the signature block of Lynn Pettis.
Instead of persisting in rejecting the advice of people who have demonstrated a keen knowledge of T-SQL, READ these articles. This obsession about cursors may look to you as thinking outside the box, it appears everyone concluded that you are stuck in your own box of C-like design. To the point where you reject the use of IDENTITY even though it was already present in your table. This attitude has nothing to do with solving a real-life problem, this is only an academic exercise.
Your statement about sequence is yet another instance where you show you do not know what you are talking about.
March 18, 2009 at 7:47 am
I feel Im running the risk of being lynched, but I agree in general with foxjaaz.
Im a database developer but I too am frustrated with the snail pace at which t-sql range and syntax has been ... well not really evolving.
Why cant t-sql be made to be more like a "modern" language, rather than some COBOL esque language that it is now. Why cant we declare constants, define packages, finer grained exception handling, autonomous transactions, basically most of whats already been in PL/SQL for years? Until 2008 you couldnt even declare and initaliase variables at the same time! You cant really blame people for looking at t-sql and thinking "how backward is this".
Why not get rid of some of the redundant syntax, C# did it by getting rid of the SET keyword, why not lose it in t-sql. Curly braces instead of BEGIN...END, sure why not?
At least give us a choice, so if you want to write COBOL, sorry T-SQL, or use perhaps a more familiar c# type syntax then let the writer choose. And expand t-sqls language constructs so I can do some "proper" development, without having to constantly bend the good coding rules because of limitations in t-sql.
March 18, 2009 at 7:59 am
kevin.reynolds (3/18/2009)
I feel Im running the risk of being lynched, but I agree in general with foxjaaz.Im a database developer but I too am frustrated with the snail pace at which t-sql range and syntax has been ... well not really evolving.
Why cant t-sql be made to be more like a "modern" language, rather than some COBOL esque language that it is now. Why cant we declare constants, define packages, finer grained exception handling, autonomous transactions, basically most of whats already been in PL/SQL for years? Until 2008 you couldnt even declare and initaliase variables at the same time! You cant really blame people for looking at t-sql and thinking "how backward is this".
Why not get rid of some of the redundant syntax, C# did it by getting rid of the SET keyword, why not lose it in t-sql. Curly braces instead of BEGIN...END, sure why not?
At least give us a choice, so if you want to write COBOL, sorry T-SQL, or use perhaps a more familiar c# type syntax then let the writer choose. And expand t-sqls language constructs so I can do some "proper" development, without having to constantly bend the good coding rules because of limitations in t-sql.
I consider those legitimate complaints and suggestions, at least to one extent or another.
As for declaring constants, I use tables for that. After all, persisting data is something databases seem to manage pretty well.
Exception handling has been greatly improved in 2005 with Try...Catch, but it still does need some work. There needs to be a better way to insert error data into a table, even if you issue a rollback command.
Not sure what you mean by "autonomous transactions". Just not familiar with the term.
Like 2008 added declare and assign in one statement, perhaps some of these are just things that need to be requested often enough to make them worth building. MS does listen to such.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 18, 2009 at 8:08 am
kevin.reynolds (3/18/2009)
--------------------------------------------------------------------------------
I feel Im running the risk of being lynched, but I agree in general with foxjaaz.
Im a database developer but I too am frustrated with the snail pace at which t-sql range and syntax has been ... well not really evolving.
Why cant t-sql be made to be more like a "modern" language, rather than some COBOL esque language that it is now. Why cant we declare constants, define packages, finer grained exception handling, autonomous transactions, basically most of whats already been in PL/SQL for years? Until 2008 you couldnt even declare and initaliase variables at the same time! You cant really blame people for looking at t-sql and thinking "how backward is this".
Why not get rid of some of the redundant syntax, C# did it by getting rid of the SET keyword, why not lose it in t-sql. Curly braces instead of BEGIN...END, sure why not?
At least give us a choice, so if you want to write COBOL, sorry T-SQL, or use perhaps a more familiar c# type syntax then let the writer choose. And expand t-sqls language constructs so I can do some "proper" development, without having to constantly bend the good coding rules because of limitations in t-sql.
Because T-SQL is NOT C#, COBOL, or any other language of that type. SQL is a data access and manipulation language use to access data. It doesn't do the same things as C# or COBOL. Instead of complaining about the language, learn how to use it. You don't program in T-SQL the same way you program in C# or COBOL. You need to use a different mind set to write highly performant code in T-SQL.
March 18, 2009 at 8:29 am
I get the whole declare then assign bit with variables, yes it's a pain, great that they finally did something about it in 2008. As for the SET Keyword, other than changing database and connection properties SET ANSI NULL on etc I never use it. I do all of my variable assignments using a SELECT Statement cause you can assign all of your varibales in one shot instead of bunch of separate SET Statements...
Don't even get me started on curly braces. I hate trying to read sets of nested {{{}{}}{{}{}}} cause some idiot thought they'd be slick and do it all on one line. They just all blend together when I look at them.
As was already stated in this thread if you make accommodations for C# developers in TSQL and make it more C# esque, who decides to use that convention and not VB, or Java or something else entirely? Do they include them all? What about ANSI SQL? Should we just throw that out cause it's not convenient? I'm sure all those fine folks working on the standard would just love that! I can hear Celko's rant already.
-Luke.
March 18, 2009 at 8:29 am
kevin.reynolds (3/18/2009)
I feel Im running the risk of being lynched, but I agree in general with foxjaaz.Im a database developer but I too am frustrated with the snail pace at which t-sql range and syntax has been ... well not really evolving.
Why cant t-sql be made to be more like a "modern" language, rather than some COBOL esque language that it is now. Why cant we declare constants, define packages, finer grained exception handling, autonomous transactions, basically most of whats already been in PL/SQL for years? Until 2008 you couldnt even declare and initaliase variables at the same time! You cant really blame people for looking at t-sql and thinking "how backward is this".
Why not get rid of some of the redundant syntax, C# did it by getting rid of the SET keyword, why not lose it in t-sql. Curly braces instead of BEGIN...END, sure why not?
At least give us a choice, so if you want to write COBOL, sorry T-SQL, or use perhaps a more familiar c# type syntax then let the writer choose. And expand t-sqls language constructs so I can do some "proper" development, without having to constantly bend the good coding rules because of limitations in t-sql.
One of the reasons why T-SQL may seem COBOL-esque is that the unbderlying core, SQL, is a standardized language, hence implementations should try to follow the standard. So the basic statements, SELECT, INSERT, UPDATE, DELETE, CREATE & DROP, have a clearly defined syntax and it make sense for extensions to follow a logical path from this. Hence, compared to a modern C-like language, it looks a bit COBOL-esque.
C# is simply a development by Microsoft, so whatever is done is the standard and they are free to add or drop keywords as they wish.
I don't see any reason to move towards C# and use {...} instead of BEGIN...END, since, in my view, there's little need for minor syntax changes that are mere eye-candy. I'd much rather see the effort going into features that actually change things, for example, the inclusion of the declare-with-assign in 2008.
Personally I'd like to see work done on making the optimizer better at, for example, handling User Defined Functions and recognizing where they might be expanded inline rather than actually called.
Derek
Viewing 15 posts - 211 through 225 (of 465 total)
You must be logged in to reply to this topic. Login to reply