T-Sql rant

Question

T-Sql rant

Viewing 15 posts - 46 through 60 (of 465 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 1

foxjazz (3/13/2009)
Why couldn't they write t-sql more like C# or have a flavored compiler for t-sql so that we could work it like c# or vb depending on how you like it. (or the t-sql language which really is the worst language MS has ever put on the block). I really don't understand why the sql server group didn't do a better job of it, or is it because they were taking thier queue from ORACLE.
Really stupid paradigm if you ask me, and I think it needs to be improved upon.

Heh... because the people that wrote it understood that most procedural programers shouldn't go anywhere near a database. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

foxjazz (3/13/2009)
Why couldn't they write t-sql more like C# or have a flavored compiler for t-sql so that we could work it like c# or vb depending on how you like it. (or the t-sql language which really is the worst language MS has ever put on the block). I really don't understand why the sql server group didn't do a better job of it, or is it because they were taking thier queue from ORACLE.
Really stupid paradigm if you ask me, and I think it needs to be improved upon.

We have a saying here in the UK: "A poor workman always moans about his tools".

If you don't like the tools, don't use them.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

foxjazzG SSCrazy Points: 2221 More actions · Answer 3

Ok set this baby... Real world.

I have a table catagory fields are description, catID, typeID. parentID

another tables queryed with (make, model, yr, engine).

in the category table makes are typeid = 1, models typeid = 2, years , typeid = 3, engines, typeid = 4.

for each model, the parentid points to the make of the model. Yes it's hiearchical.

This is real world folks, do this in T-Sql without using a cursor.

And it doesn't matter whether identity insert is set to on or off, can be either way on the primary key (CATID).

If there is anything I left out, let me know.

foxjazzG SSCrazy Points: 2221 More actions · Answer 4

Actually we are forced to use t-sql when doing such things as storedprocedures. Unless of course we write clr functions.

Not having the ability to use non-cursored rowbased loops is a big downfall. And I think it should be different in the future.

And without someone speaking up about the issues, nothing will get done. You all that use t-sql and don't code probably never know any difference. I know my dba wife doesn't.

And of course she is glad not to be a coder, and I don't blame her a bit.

I think one of the reasons they built SSIS, was so that we could do stuff a different way, and not worry about cursors.

So why don't they just build datatables into t-sql, it would do away with the cusor paradigm all together.

It's mainly because MS is built on separate camps, and they don't talk to each other in ways that could have made sql server easier to use and merging some of the languages.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

foxjazz (3/14/2009)
Ok set this baby... Real world.
I have a table catagory fields are description, catID, typeID. parentID
another tables queryed with (make, model, yr, engine).
in the category table makes are typeid = 1, models typeid = 2, years , typeid = 3, engines, typeid = 4.
for each model, the parentid points to the make of the model. Yes it's hiearchical.
This is real world folks, do this in T-Sql without using a cursor.
And it doesn't matter whether identity insert is set to on or off, can be either way on the primary key (CATID).
If there is anything I left out, let me know.

You didn't actually ask us to do anything. What do you want us to try and do with this?

[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]

Gift Peddie SSC Guru Points: 73570 More actions · Answer 6

You all that use t-sql and don't code probably never know any difference. I know my dba wife doesn't.
And of course she is glad not to be a coder, and I don't blame her a bit

I am a woman who wrote JAVA and now C# and also write SQL(T-SQL and PL/SQL) so your wife being a DBA that does not write code is based on her ability and not related to her sex or T-SQL or the DBA profession. I also moderate C# and most of the .NET forums on MSDN.

MCPD Web C#, MCITP BI and DBA and MCTS TFS

:Whistling:

I think one of the reasons they built SSIS, was so that we could do stuff a different way, and not worry about cursors. So why don't they just build datatables into t-sql, it would do away with the cusor paradigm all together.

SSIS was created to compete with Informatica ETL just as SSRS was created to compete with Crystal Report though Crystal Decision the original creator of Crystal Report was Microsoft partner.

It's mainly because MS is built on separate camps, and they don't talk to each other in ways that could have made sql server easier to use and merging some of the languages.

That is not relevant I write both C# and SQL at advanced level I never take any of the two for granted I buy books from experts as needed and spend time with both products. The teams work together that is the reason there is Visual Studio for Database projects only today. What you know about a language depends on your effort and resources. What is in SQL Server relational engine is not Microsoft's to change as needed because Microsoft is just another vendor. Take look at this connect request about MERGE statement the user did not know it is not Microsoft's to change, just as Microsoft cannot persist XML CDATA sections in XML column which in some cases contain six tables shopping cart.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=291266

Kind regards,
Gift Peddie

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 7

Also be prepared for the possibility that the database design will be critiqued. If it turns out that the table structure is a poor design, the fault doesn't lie with T-SQL.

By the way, nobody here is your "baby." Language like that makes you sound like a whiny punk, even to those of us who tried to give you the benefit of the doubt. So far you've managed to impress no one with anything substantive. Whatever impression you've made is nothing to be proud of. Nobody really cares whether you like SQL or not. The only thing that's keeping this going is the professionalism of the volunteers who are still quite willing to show you how to do it right, despite your lame attitude.

Now, assuming you would actually like an answer to the question you neglected to type in, here is what I was able to put together from your sketchy description. Having worked on bill of materials processors in the past, I don't think what I'm seeing is correct. But I'm going to post it here to make it easy for you to correct and post back. Once we have a clear picture, then rational discussion can take place. Your choice.

/*

I have a table catagory fields are description, catID, typeID. parentID

another tables queryed with (make, model, yr, engine).

in the category table makes are typeid = 1, models typeid = 2, years , typeid = 3, engines, typeid = 4.

for each model, the parentid points to the make of the model. Yes it's hiearchical.

This is real world folks, do this in T-Sql without using a cursor.

And it doesn't matter whether identity insert is set to on or off, can be either way on the primary key (CATID).

*/

declare @types table (typeID int, typeDesc varchar(20))

insert into @types

select 1,'Makes' union all

select 2,'Models' union all

select 3,'Years' union all

select 4,'Engines'

declare @categories table (catDescription varchar(50), catID varchar(10) primary Key, typeID int, parentID int)

insert into @categories

select 'Pontiac', 1001, 1, 1 union all

select 'GTO', 1002, 2, 1 union all

select '1969', 1003, 3, 1 union all

select '400 V8 RamAir',1004,4,1

select * from @types

select * from @categories

produces:

[font="Courier New"]

typeID typeDesc

1Makes

2Models

3Years

4Engines

desc catID typeId parentID

Pontiac 100111

GTO 100221

1969 100331

400 V8 RamAir100441

[/font]

[edited to include sample setup and additional comments]

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 8

foxjazz (3/14/2009)
Ok set this baby... Real world.
I have a table catagory fields are description, catID, typeID. parentID
another tables queryed with (make, model, yr, engine).
in the category table makes are typeid = 1, models typeid = 2, years , typeid = 3, engines, typeid = 4.
for each model, the parentid points to the make of the model. Yes it's hiearchical.
This is real world folks, do this in T-Sql without using a cursor.
And it doesn't matter whether identity insert is set to on or off, can be either way on the primary key (CATID).
If there is anything I left out, let me know.

See the link in my signature below and post the table creation and data in that fashion, and we'll give it a try... without a cursor and without recursion.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 9

foxjazz (3/14/2009)
Not having the ability to use non-cursored rowbased loops is a big downfall.

There is a way to do non-cursored row-based loops in T-SQL... the technique is called "pseudo-cursors" and it's nasty fast. It doesn't help with hierarchies, but it'll blow the doors off just about everything else for the likes of running totals, special ranking, Median calculations, and the creation of things that require numbered rows to function.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 10

foxjazz (3/13/2009)
select name from mynametable where changedate > @yesterday
fetch from @sel into @name
while (@@fetch_status = 0)
begin
insert into nameother (name,changeddate) values (@name,getdate())
end
Do this without a cursor!

Heh... rant complete... regard all further alarms. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

Jeff Moden (3/14/2009)
foxjazz (3/13/2009)
select name from mynametable where changedate > @yesterday
fetch from @sel into @name
while (@@fetch_status = 0)
begin
insert into nameother (name,changeddate) values (@name,getdate())
end
Do this without a cursor!
Heh... rant complete... regard all further alarms. 😉

You know, the irony here is that I did a presentation about this last month and just submitted the first in a series of articles on it. In fact, the first example in in my presentation is logically identical to this "impossible" problem. The solution is there too. 🙂

[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]

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 12

BWAA-HAA!!! The real irony you speak of is that it obviously wasn't read by those that needed it the most! 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

foxjazzG SSCrazy Points: 2221 More actions · Answer 13

Ok, yes I did it that way before. Except you have to do it in 4 distinct query sets, and link the first results to the other set, and so on.

Ok, another problem:

Lets say you have a "product table with a descriptionID that links to a description table.

In another database, there is a product and description table.

This product table has lots of field in it,ProductID, productlineID, partnumber, searchnumber, upc etc... most of which we don't care about.

Partnumber and productlineid are unique. productid is the uniqueKey.

The only productlineid we are worried about in this exercise is where it is 1 all the others can just be there for other reasons.

in the first table we say select partnumber, description and get a set of rows.

38101, belt

7404, 'drive align tensioner'

These descriptoins may change over time and lets say they do change but te changes are small (like instead of 'drive align tensionser' its now just 'tensioner'.

so the first goal is to find all the changed items from the first table, which is simple using a outer join, where product is null.

So we have a few partnumbers that are listed (which are already in the product table, so no inserts are necessary).

select partnumber, description from FIRSTTABLE would be the first query

now for each partnumber you receive, you have to update the descriptionID using the productID so the structure looks like this:

table = productdescription

descriptionID int,

description nvarchar(50)

table = product

productid int, primarykey

partnumber nvarchar(50),

productlineid int,

descriptionid int,

other fields of other uses...

so this would be a valid query.

select @productid = productid, description from product inner join productdescription on product.descriptionid = productdescription.descriptioinid

where partnumber = '38101'

and this would be legal.

Update product set descriptionid = @descriptionid where productid = @productid

however you have to check the descriptiontable to see if there is a valid description, if not you have to insert one.

select @cnt = count(*), @descriptionid = descriptionid from productdescription where description = 'tensioner'

if @cnt = 0

begin

insert productdescription(description) values ('tensioner')

update product set descriptionid = @@identity where productid = @productid

end

else

begin

update product set descriptionid = @descriptionid where productid = @productid

end

This is a real world problem that can't be done with set statements, you have to run a cursor because you can't make more than one primarykey update to the table to make it have the correct description with one update.

foxjazzG SSCrazy Points: 2221 More actions · Answer 14

wrong...

typeID typeDesc

1 Makes

2 Models

3 Years

4 Engines

desc catID typeId parentID

Pontiac 1001 1 1

GTO 1002 2 1

1969 1003 3 1

400 V8 RamAir 1004 4 1

the results should look like this:

Pontiac catid 1001, typeid 1, parentid 1

GTO 1002, 2, 1001

1969 1003, 3, 1002

400 V RamAir 1004,4,1003

The parentID has to be the parent, not 1.

and yes you can do inserts to accommodate this table by linking the results of the first insert in your second query, the results of the second to the third and third to fourth.

Which is really an easy problem.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 15

I am still waiting for you to actually ask a question. These incoherent ramblings do not constitute a question. You don't even bother to indicate which previous post you are replying to.

If you find that you are having too much difficulty organizing your thoughts for a straigh-forward question, then please do as Jeff asks and read his article and submit a question in that form.

[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]