The subject of this article is called (by others) database constants. So lets be sure we're on the same page with what a constant is. It is just a value. X = Substring(Y,1,5); X and Y are variables, 1 and 5 are values. X = Y * 5; X and Y are variables, 5 is a constant. It is a number whose value is 5. A table in D4 is just as much a variable as X and Y. A table in sql is like the value 5, a constant. The difference between a constant and a variable is also the difference between D4, a relational database, and sql! The following is part of an exchange I had with someone on the issue of database constants. The idea of database constants was raised in the article: A New (and Hopefully Better) Approach to Constants http://www.sqlservercentral.com/articles/T-SQL/61244/ Here I go ☺ : Sql Server lets users define a 'table variable'. Now you really don't believe it's a table 'variable' do you?:) Of course not. They're just playing with words. There is no such thing in sql server as a table variable. But because they call it that most users really believe it. It's like believing in the tooth fairy:) Now we're going to play the same game with database 'constants':) There is no such thing as a database constant. There are only values (which can't be updated or assigned other values) and variables (which can be updated and assigned other values). But in sql there is no real talk of these basic things. And that's why I talk about Dataphor ☺. There is no difference between what this guy is calling a 'constant' and a table or 'resultset' in sql. They are all (constant) values and not variables. I'm trying to show what sql would be like if there really were table 'variables'. One way was with the CTE article, 'Do you know what the Sql CTE really is?' But people seem to be having a hard time wrapping their head around it:) This sql stuff just gets crazier. Now people are going to believe that you can pass a table as a parameter to a stored procedure. That is nonsense. One reason people believe it is they have nothing to compare/constrast it with. So I show what it really means to pass a table to a sp: http://beyondsql.blogspot.com/2007/10/sql-whats-really-with-with.html http://beyondsql.blogspot.com/2007/06/dataphor-13-passing-table-as-parameter.html http://beyondsql.blogspot.com/2007/08/dataphor-creating-super-function.html http://beyondsql.blogspot.com/2007/09/dataphor-all-tables-are-typed-variables.html etc.etc. But to do these kind of things requires a foundation different than sql. Hence the name of my site ☺ . The so called 'constants' are basically a table without a key, ie a row. So I do: create table Y { A:Integer, B:String, key{} }; insert row{1 A,'Micheal' B} into Y; select Y[].B 'Michael' select Y[].A 1 where the '[]' is called a row extracter. Or I can define a 'list': var LList:=list(String){'Michael','Steve','Jimmy'}; select LList[0]; Michael Or I can define a table (ie a row) with lists. create table Z { Strings:list(String), //The column holds a list of strings. Integers:list(Integer),//The column holds a list of integers. key{} }; insert row{{'Micheal','Steve','Jimmy'} Strings,{2,10,22,40} Integers} into Z; select Z[].Strings[2]; Jimmy select Z[].Integers[1]; 10 where the [2] and [1] refer to the ordinal position of the item (value) in the list. But sql doesn't have a table 'type' or a list type so it couldn't understand these things. The only thing sql can do is try to simulate these types. And it winds up in a mess of gooblygook that few understand and robs the user of the integrity that the database should provide by supporting these things directly. All this stuff is based on values, variables and types. The only types that exist is sql are numbers and strings. Again there is no type for tables, row or list. And therefore no variables for these things because without a type you can't define a variable. Make sense ☺ . var X:= Orders //X is a variable of type table, specifically the type //of the Orders table (the column names and there data types). This is the huge step forward from sql. In sql the result of a query is no different than the 'Constant' that guy was trying to talk about ☺ . It is also the difference between a CTE and a variable that holds the result of the 'CTE'. You can't reuse a CTE in sql because it's a 'constant', a value. You can only reuse a variable.
Dataphor SQL RAC (Relational Application Companion)
A site of hope for those looking for a true relational database system
- a one-one requirement constraint with dataphor (1)
- anatomy of sql server part I - what is a stored procedure (1)
- anatomy of sql server part II - the unit test as part of the database (1)
- anatomy of sql server part III - what does deferred name resolution really mean (1)
- censoring sql posts (1)
- creating an opposite constraint in dataphor (1)
- dataphor (2)
- Dataphor (7)
- dataphor # 13 a table as a parameter (1)
- dataphor - download and start working with it (1)
- dataphor - fixed sized word segments (1)
- dataphor # 10 sql mythology (1)
- dataphor # 11 string differences (1)
- dataphor # 12 trimming a string (1)
- dataphor # 14 sql the meaning of Update..From (1)
- dataphor # 15 views with substance (1)
- dataphor # 16 inclusive vs exclusive solutions (1)
- dataphor # 17 a visual look at ranking queries (1)
- dataphor # 18 data scrubbing using lists (1)
- dataphor # 19 create intervals over strings (1)
- dataphor # 20 browsing an sql window (1)
- dataphor # 21 an example of relational division (1)
- dataphor # 22 reusable procedures (1)
- dataphor # 23 repley to Michel (1)
- dataphor # 24 basics of the table type (1)
- dataphor # 25 extending the dense rank function (1)
- dataphor # 26 query a hierarchy with explode (1)
- dataphor # 27 combine strings with Split and Concat (1)
- dataphor # 28 constants and variables or sql and D4 (1)
- dataphor # 29 another example of relational division (1)
- dataphor #1 introduction (1)
- dataphor #2 splitting strings (1)
- dataphor #3 string concatenation (1)
- dataphor #4 comment (1)
- dataphor #5 comment (1)
- dataphor #6 formal definition (1)
- dataphor #7 sql: table this (1)
- dataphor #8 list to table (1)
- dataphor #9 table constraints (1)
- dataphor creating lists in a query (1)
- extracting numbers from a string with dataphor (1)
- jeff modens dynamic crosstabs for sql server (1)
- linq to sql the what and why (1)
- linq to sql as a window of opportunity to sql users (1)
- linq to sql should be important to sql users (1)
- linq to sql vs. older 4GL attempts (1)
- listing missing table item (1)
- Multiple cascade paths to the same table (1)
- RAC (4)
- RAC #1 comment (1)
- RAC #2 example (1)
- RAC #3 finding the Nth number in a string (1)
- RAC #4 Sql Server 2005 ranking functions vs. Rac ranking (1)
- sorting a delimited string by its numerical string parts (1)
- sql an example of extreme implicit conversions (1)
- sql can't handle complicated cascading updates (1)
- sql CTE should be a variable not a value (1)
- sql dense rank for identifying consecutive runs (1)
- sql is there really a table variable (1)
- sql ranking functions explained by relational types (1)
- sql server triggers are best set based (1)
- sql the idea of using substring to simulate lists (1)
- sql the undefined trigger in Sql Server (1)
- sql vs relational on tables (1)
- sql what the sql CTE covers up (1)
- types and procedures (1)
Wednesday, October 17, 2007
Sql - Constantly struggling
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment