February 16, 2008 at 8:57 am
So OK let's continue. Back to lengths. I'm not so concerned about limit of 30 symbols per identifier however, I'm quite amazed by following math in SQL Server:
select len(a), len(b), len(c), len(d) from a;select len(a+b+c+d) from a;30483048304830488000
3048+3048+3048+3048 = 8000???
Lordy, what system are you actually testing on? You'll need to post the rest of the code for that example because when you try to create a table (A) with 4 columns of 3048 characters each, SQL Server warns ya...
CREATE TABLE A
(
A VARCHAR(3048),
B VARCHAR(3048),
C VARCHAR(3048),
D VARCHAR(3048)
)
Warning: The table 'A' has been created but its maximum row size (12221) exceeds
the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this
table will fail if the resulting row length exceeds 8060 bytes.
Server: Msg 511, Level 16, State 1, Line 9
Cannot create a row of size 12209 which is greater than the allowable maximum of
8060.
Warning: The table 'A' has been created but its maximum row size (12221) exceeds
the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this
table will fail if the resulting row length exceeds 8060 bytes.
The statement has been terminated.
Even if you choose to ignore such a warning during table creation, if you actually try to populate the table with that much information, it won't actually let you... and, it does provide a warning...
INSERT INTO A
(A,B,C,D)
SELECT REPLICATE('A',3048) AS A,
REPLICATE('B',3048) AS B,
REPLICATE('C',3048) AS C,
REPLICATE('D',3048) AS D
Server: Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 12209 which is greater than the allowable maximum of 8060.
The statement has been terminated.
Since you didn't mention what the data type of the columns were, here's what happens when you use VARCHAR(MAX) in 2k5...
CREATE TABLE A
(
A VARCHAR(MAX),
B VARCHAR(MAX),
C VARCHAR(MAX),
D VARCHAR(MAX)
)
INSERT INTO A
(A,B,C,D)
SELECT REPLICATE('A',3048) AS A,
REPLICATE('B',3048) AS B,
REPLICATE('C',3048) AS C,
REPLICATE('D',3048) AS D
select len(a), len(b), len(c), len(d) from a;
select len(a+b+c+d) from a;
-------------------- -------------------- -------------------- --------------------
3048 3048 3048 3048
(1 row(s) affected)
--------------------
12192
(1 row(s) affected)
Like I said, you need to show your setup code so I can see what you're trying to do because it looks to me like SQL Server won't produce the error by ommision that you claim it will.
Heh... I absolutely agree on the following...
Yeahh that's paryially true. But some basics still applies:
1. You can do everything in the fastest way if you don't do that at all. Many times business requirements can be fullfilled just throwing out half of the crap code.
2. If you can do that in pure SQL, then do that.
3. If you need more than SQL, then use pl/sql or t-sql.
4. If you need more than pl/sql then use Java. Not sure what in SQL server though. Probably .NET. And believe me - there are very few things one cannot do in pl/sql.
And, Java plays pretty with with SQL Server without .NET.
So far as recurrsion goes for solving problems, I normally avoid it... People tend to use it because they can't think of a setbased solution. I do agree, however, that Oracle's "Connect By" is an outstanding implementation of recurrsion especially for the resolution of hierarchies. I wouldn't mind seeing SQL pick up on that little bit of heaven.
I probably came across the wrong way on Auto-commit... because of the way Oracle's engine works, there's actually a whole lot less chance of a deadlock actually occuring even on the longest of transactions. It was meant to point out the paradigm shift... as was most of the rest of my post.
Anyway, Gint, thanks for taking the time to post back it detail... I think it gives folks a great starter comparison between the two RDBMS's... one thing that you said that I'd left out and it's probably the most important thing that either of us could have said... whether you're make the shift from SQL Server to Oracle, or vice versa, "be open to change your mind" because change will absolutely be required.
Thanks again, Gint.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing post 31 (of 30 total)
You must be logged in to reply to this topic. Login to reply