April 6, 2009 at 4:46 am
steve dassin (4/6/2009)
An sql table is certainly not a variable.
Who says?
Any ground for this statement?
I thought I was being kind and considerate calling it a constant (it has a value but no type.
What???
What is something with no type? Certainly not something that can support assignment).
You want to say a it's a constant?
Do you really mean a constant does not have a type?
How computer allocates memory for it then?
If that offends you perhaps you prefer a computer science type of lox 🙂
Would it offend you if I say you're missing some essential computer education from school?
_____________
Code for TallyGenerator
April 6, 2009 at 6:22 am
Mike C (4/5/2009)
Oracle is good about implementing new features and functionality really quickly... sometimes they even get their features out to market before the ISO standards updates are approved.
That's not been my observation... hasn't DATEDIFF, DATEADD, and DATEPART part of the standard for quite a while? And wasn't CASE part of the standard for a very long time like way before 9i2?
And, I absolutely agree with you and Bruce. Set-Based beats the tar out of RBAR even in Oracle. The problem is that there are some places where you can't actually avoid it. For example, triggers don't allow manipulation of DELETES or INSERTS except by one row at a time using the FOR EACH ROW clause.
I haven't had to touch Oracle in about 9 months... it's been a great 9 months. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
April 6, 2009 at 6:41 am
steve dassin (4/5/2009)
I'm not trying to sell you anything. I'm trying to get you to go in for some trippy stuff 🙂
Heh... it's some trippy stuff, alright. I've never understood why they can't just use "=" instead of ":=", why they need ";" to identify the end of a "thought" that the compiler should easily recognize like the interpreter in T-SQL does. The code you have there kinda looks like the same thing that Enterprise Manager does... unnecessary brackets and parenthesis all over the place.
And then there's just plain old bad design...
select table{MyView['Poland' by {ShipCountry}]};[font="Arial Black"]//A row converted to a table.[/font]
That pretty much tells me that someone just doesn't understand the structure of a table.
Man, I'm [font="Arial Black"]SO[/font] happy to have gotten away from all that. You do what you want in that world... stop complaining about mine. That's exactly what you would say if the tables were turned. Adapt or don't use it.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 6, 2009 at 6:48 am
steve dassin (4/6/2009)
Sergiy (4/6/2009)
steve dassin (4/5/2009)
getting sql folks to get their head out of what is essentially a 'constant' (an sql table) and into something that is variable.Such a pile of c..p!
What makes sql table a 'constant'???
Well I have a tacit agreement with the moderator to play nice and not raise the ire of any more participants here so I'll play nice 🙂 An sql table is certainly not a variable. If it's not then what then is it? I thought I was being kind and considerate calling it a constant (it has a value but no type. What is something with no type? Certainly not something that can support assignment). If that offends you perhaps you prefer a computer science type of lox 🙂 Of course the rectangular idea of rows and columns doesn't quite fit the idea of a bagel. It seems that trying to morph a lox into a variable offends you. Perhaps you can't envision programming without a lot of salt:-) I'm not sure how many here think variables are overrated. I'm just trying to reaquaint them to a long forgotten concept 🙂
best behavior steve,
And THAT is why you can't get anyone to even start to listen to you! You always bring it down to ad hominem attacks.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 6, 2009 at 9:09 am
steve dassin (4/6/2009)
Sergiy (4/6/2009)
steve dassin (4/5/2009)
getting sql folks to get their head out of what is essentially a 'constant' (an sql table) and into something that is variable.Such a pile of c..p!
What makes sql table a 'constant'???
An sql table is certainly not a variable. If it's not then what then is it?
Back in my CS classes we used to call it a... you know... "data structure".
April 6, 2009 at 9:22 am
Jeff Moden (4/6/2009)
Mike C (4/5/2009)
Oracle is good about implementing new features and functionality really quickly... sometimes they even get their features out to market before the ISO standards updates are approved.That's not been my observation... hasn't DATEDIFF, DATEADD, and DATEPART part of the standard for quite a while? And wasn't CASE part of the standard for a very long time like way before 9i2?
And, I absolutely agree with you and Bruce. Set-Based beats the tar out of RBAR even in Oracle. The problem is that there are some places where you can't actually avoid it. For example, triggers don't allow manipulation of DELETES or INSERTS except by one row at a time using the FOR EACH ROW clause.
I haven't had to touch Oracle in about 9 months... it's been a great 9 months. 😀
I can't really speak for older versions of Oracle, but analytic functions (windowing/ranking functions), the MERGE statement, CTEs, XML support and a lot of other SQL:2003 standard items have been in Oracle since before even the SQL Server 2005 was released. I was discussing the new features in Oracle with a Java/Oracle developer back in 2002. Analytic functions, for instance, were available in Oracle 9i back in 2001 or 2002 (I can't remember exactly when it came out...) We got a partial implementation in SQL 2005. There are quite a few Connect items requesting new functionality in SQL Server that already exists in one form or another in Oracle -- like full support for windowing/ranking functions per the ISO SQL standard that's already 6 years old.
April 6, 2009 at 9:33 am
steve - sounds to me that you need to get yourself nominated to sit on the ANSI SQL standards board. It is clear to me that you have a. some strong opinions and b. some ideas as to how to "fix SQL". I'm not going to go into whether they're right or not, whether I agreee or not - there are hundreds of posts in this thread already belaboring those points.
What I will say is - show me any of those solutions that maintain close ties to Codd's laws, normalization, the ACID principles, AND maintain best practices as to keeping your development and applications layers in order and separate.
A LOT of smart people have been spending a LOT of time trying to balance all of these various priorities, so I'm not willing to just say they're wrong and chuck what's been done. Not given the processing and performance standards that are being established by BOTH SQL Server and Oracle. I have yet to see anything that can process data as fast as either, and for that I am willing to give a few things up. Do I have some rules about how the data needs to be organized? Fine - I can live with that. Etc...
It comes down to pain vs. gain. You clearly think things are broken somewhere, and good for you. But ask yourself what you give up when you introduce all of these new ideas into the mix.
It's ultimately the same challenge I put to Mr. Date and those of the 3rd Manifesto: your theory sounds good, but the commercial application of those ideas are worse than what they intend to replace. 6NF might look nice on the simplest of models, but it's a nightmare in HealthCare/Insurance/Telecom; No server hardware currently available oculd handle the load required to back those madels up. If you can fix those issues, THEN I may listen.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 6, 2009 at 10:30 am
foxjazz (3/14/2009)
Ok set this baby... Real world.I have a table category 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 hierarchical.
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.
REAL WORLD:
I will ignore this hypothetical example and focus instead on an actual example in my company.
I have a table categorize adjustments. Each adjustment goes to one and only one category, but each category can belong to a parent group. So I have a table tAdjustmentCategory as defined in the construct SQL excerpt below.
CREATE TABLE [dbo].[tAdjustmentCategory](
[Key] [int] IDENTITY(1,1) NOT NULL,
[Label] [varchar](75) NOT NULL,
[Parent] [int] NULL,
CONSTRAINT [PK_tAdjustmentCategory] PRIMARY KEY CLUSTERED
([Key] ASC)
Now with this table I can move categories around and define as in depth a hierarchy as possible. But how to use it?
Well, to get a look at the tree in a human readable format, I use this view:
ALTER VIEW [dbo].[vAdjustmentCategoryTree] WITH SCHEMABINDING
AS
WITH AdjCategoryTree
AS
(
SELECT A.[Key], A.Label, CONVERT(VARCHAR(MAX), Label) AS LabelPath, A.Parent, 0 AS Depth
FROM dbo.tAdjustmentCategory A WITH (NOLOCK)
WHERE Parent IS NULL
UNION ALL
SELECT A.[Key], A.Label, CONVERT(VARCHAR(MAX), B.LabelPath + '|' + A.Label) AS LabelPath, A.Parent, B.Depth + 1
FROM dbo.tAdjustmentCategory A WITH (NOLOCK)
JOIN AdjCategoryTree B
ON A.Parent = B.[Key]
)
SELECT TOP 100 PERCENT [Key], REPLICATE(' ', Depth) + Label AS DisplayLabel, Label
, ROW_NUMBER() OVER (ORDER BY LabelPath) AS [Order], Depth
FROM AdjCategoryTree
ORDER BY LabelPath
And to actually query all members of a specific node I use this view:
ALTER VIEW [dbo].[vAdjustmentCategoryAncestry] WITH SCHEMABINDING
AS
WITH AdjCategoryTree
AS
(
SELECT A.[Key], A.Label, A.Parent, A.[Key] AS Origin
FROM dbo.tAdjustmentCategory A WITH (NOLOCK)
UNION ALL
SELECT A.[Key], A.Label, A.Parent, B.Origin
FROM dbo.tAdjustmentCategory A WITH (NOLOCK)
JOIN AdjCategoryTree B
ON A.Parent = B.[Key]
)
SELECT Origin AS Parent, [Key] AS Child
FROM AdjCategoryTree
Now these views may not be the most optimized solutions to using this set, but I am not using the 2008 Hierarchy structure. And this is real world. Oh, and I bet it performs better than C#.
The key to gaining knowledge is to first admit that you do not know. A wise man never says "this is what I know", but instead says "this is as I understand it" or "this is what I believe".
April 6, 2009 at 2:17 pm
[font="Verdana"]:blink:
Last time I looked, the definition of a "constant" was something that was invariant.
Tables vary.
So, not constants.
I agree with many of your (earlier) points about SQL as a language. Yet the points I made about enterprise adoption of new languages still stand. Using SQL Server is not all about just T-SQL.
And please keep in mind that SQL Server is not just one language. T-SQL, MDX, whatever you call the hodge-podge of SSIS, integration with .Net CLR, adoption of PowerShell... from a language purist point of view, it's a nightmare!
And yet... it works. 😀
[/font]
April 6, 2009 at 2:29 pm
Jeff Moden (4/6/2009)
I've never understood why they can't just use "=" instead of ":=", why they need ";" to identify the end of a "thought" that the compiler should easily recognize like the interpreter in T-SQL does.
[font="Verdana"]The := is a hang over from crappy keyboards unable to do the true mathematical assignment operator, so when the language designers for Algol came along, they decided on that instead. They didn't use = as that is used for comparison. Pascal (an Algol derivative) made := popular (well, not that Algol wasn't popular in it's own right), and Ada followed suit. There are a lot of languages that use the := notation.
C (C++, Java, C#, Objective C, etc) and BASIC (including Visual Basic) on the other hand have different roots. BASIC quite happily uses = for both assignment and comparison, making statements like LET A=B=C quite legitimate. Personally, I find C the most confusing, with == for comparison.
Statement separators is more essential for the classic 3-GL type of language because otherwise a compiler cannot tell easily when a statement ends, and the execution times for compiling go through the ceiling. Note that even T-SQL is getting this now (the ; before a WITH?) So it's actually not easy to recognise breaks in statements.
There have been a couple of languages (anyone remember BASIC?) that used lines as statement separators. But that has issues as well.
Oracle PL/SQL (as you well know) uses ; also.
Nature of the beast on that one.
[/font]
April 6, 2009 at 2:38 pm
steve dassin (4/6/2009)
Sergiy (4/6/2009)
steve dassin (4/5/2009)
getting sql folks to get their head out of what is essentially a 'constant' (an sql table) and into something that is variable.Such a pile of c..p!
What makes sql table a 'constant'???
Well I have a tacit agreement with the moderator to play nice and not raise the ire of any more participants here so I'll play nice 🙂 An sql table is certainly not a variable. If it's not then what then is it? I thought I was being kind and considerate calling it a constant (it has a value but no type. What is something with no type? Certainly not something that can support assignment). If that offends you perhaps you prefer a computer science type of lox 🙂 Of course the rectangular idea of rows and columns doesn't quite fit the idea of a bagel. It seems that trying to morph a lox into a variable offends you. Perhaps you can't envision programming without a lot of salt:-) I'm not sure how many here think variables are overrated. I'm just trying to reaquaint them to a long forgotten concept 🙂
best behavior steve,
I have to admit I don't follow you in the slightest, but maybe that's because I prefer donuts. (And yes, that makes just about as much sense as your post.)
Tables are neither variables nor constants. You don't assign values to tables.
- 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
April 6, 2009 at 2:50 pm
Jeff Moden (4/6/2009)
steve dassin (4/6/2009)
Sergiy (4/6/2009)
steve dassin (4/5/2009)
getting sql folks to get their head out of what is essentially a 'constant' (an sql table) and into something that is variable.Such a pile of c..p!
What makes sql table a 'constant'???
Well I have a tacit agreement with the moderator to play nice and not raise the ire of any more participants here so I'll play nice 🙂 An sql table is certainly not a variable. If it's not then what then is it? I thought I was being kind and considerate calling it a constant (it has a value but no type. What is something with no type? Certainly not something that can support assignment). If that offends you perhaps you prefer a computer science type of lox 🙂 Of course the rectangular idea of rows and columns doesn't quite fit the idea of a bagel. It seems that trying to morph a lox into a variable offends you. Perhaps you can't envision programming without a lot of salt:-) I'm not sure how many here think variables are overrated. I'm just trying to reaquaint them to a long forgotten concept 🙂
best behavior steve,
And THAT is why you can't get anyone to even start to listen to you! You always bring it down to ad hominem attacks.
That's an attack!? You trying to get me in trouble? I sure hope the moderator has his R bar set higher than you do 🙂 I'm just poking a little humor around an important idea. Do you have something against a little levity too 🙂 I can understand your feeling of being protective of your peers but lets not tilt the playing field too much. Hey I haven't explicitly skewered sql nor anyone. I'm really not the sql scrooge. I bring good tidings and cheer:-)
best (not worst),
steve
April 6, 2009 at 4:28 pm
steve dassin (4/6/2009)
Jeff Moden (4/6/2009)
steve dassin (4/6/2009)
Sergiy (4/6/2009)
steve dassin (4/5/2009)
getting sql folks to get their head out of what is essentially a 'constant' (an sql table) and into something that is variable.Such a pile of c..p!
What makes sql table a 'constant'???
Well I have a tacit agreement with the moderator to play nice and not raise the ire of any more participants here so I'll play nice 🙂 An sql table is certainly not a variable. If it's not then what then is it? I thought I was being kind and considerate calling it a constant (it has a value but no type. What is something with no type? Certainly not something that can support assignment). If that offends you perhaps you prefer a computer science type of lox 🙂 Of course the rectangular idea of rows and columns doesn't quite fit the idea of a bagel. It seems that trying to morph a lox into a variable offends you. Perhaps you can't envision programming without a lot of salt:-) I'm not sure how many here think variables are overrated. I'm just trying to reaquaint them to a long forgotten concept 🙂
best behavior steve,
And THAT is why you can't get anyone to even start to listen to you! You always bring it down to ad hominem attacks.
That's an attack!? You trying to get me in trouble? I sure hope the moderator has his R bar set higher than you do 🙂 I'm just poking a little humor around an important idea. Do you have something against a little levity too 🙂 I can understand your feeling of being protective of your peers but lets not tilt the playing field too much. Hey I haven't explicitly skewered sql nor anyone. I'm really not the sql scrooge. I bring good tidings and cheer:-)
best (not worst),
steve
Not to mention, Jeff, that you missed your mark by one post. Or is it OK for Sergiy to call Steve's post (or was it Steve?) "a pile of c--p"? Or perhaps you were bemoaning Steve's ability to draw such a response, and therefore blamed him for the remark?
To get back to the point, though. If a T-SQL table is a variable, then why did MS introduce T-SQL "table variables" in SS2K5? Are they the same thing? If not, was it simply an unfortunate choice on MS's part to name the new construct such? If so, why introduce the new construct?
What are some things we would expect to be able to do with proper programming variables that we are not able with T-SQL tables (or table variables)? Assignment? Passing as arguments? Comparison? Are T-SQL variables or table variables available to us for declaration and manipulation in the .NET language of your choice? What about other programming languages?
Just a few things to think about.
TroyK
April 6, 2009 at 4:38 pm
[font="Verdana"]Hmmm.
This is like saying "I only know about two colours: blue and green. Now this thing called "red" comes along... is it blue or green?
A table is, believe it or not, a table. Shocking concept! It is neither a variable or a constant. Nor is it an object, although it could be considered a sub-class of a generic object. It is closer to the concept of a file in many 3-gls, but it's not that either.
You can instantiate a table as a variable. It'd be nice to be able to instantiate one as a constant too! But it is a first class citizen in it's own right.
[/font]
April 6, 2009 at 4:46 pm
Well, I'm lost in this thread.
I'd hope it would be a good debate, but it looks like it's gotten confused with bad feelings between participants.
At this point, I'm going to close this thread down. If you'd like to debate dataphor, there's a thread for that here: http://www.sqlservercentral.com/Forums/Topic685234-8-1.aspx
Viewing 15 posts - 451 through 465 (of 465 total)
You must be logged in to reply to this topic. Login to reply