--fred
limits
last updated: 6/14/2000
applies to: oracle 7.3+, ms sql server 6.5+
the following table shows differences in limits of oracle and ms sql server:
| description | oracle | ms sql server |
|---|---|---|
| columns per table | 1000 | 250 (6.5) 1024 (7.0) |
| row size | unlimited | 1962 bytes (6.5) 8060 bytes (7.0) - includes pointers, but not data, for text and image columns |
| long and long raw columns per row | 1 (must be last column) | unlimited (16-byte pointer per) |
| lob, text, and image columns per row | unlimited (16-byte pointer per) | unlimited (16-byte pointer per) |
| clustered indexes per table | 1 | 1 |
| non-clustered indexes per table | unlimited | 249 |
| columns per index | 16 | 16 |
| index row size | 2k bytes | 900 bytes |
| identifier length | 30 chars | 30 chars (6.5) 128 chars (7.0) |
| tables per select | unlimited | 16 (6.5) 256 (7.0) |
| source code per stored procedure | 64kb (6.5) 250mb (7.0) | |
| data type limits | (see data types) | |
--fred
operators
last updated: 6/7/1999
applies to: oracle 7.3+, ms sql server 6.5+
most operators are the same in oracle and ms sql server. here are some that differ:
| description | oracle | ms sql server |
|---|---|---|
| string concatenation | string1 || string2 | string1 + string2 |
--fred
built-in functions
last updated: 6/7/1999
applies to: oracle 7.3+, ms sql server 6.5+
oracle and ms sql server offer many of the same built-in functions. for example, they both offer abs, exp, round, upper, lower, avg, count, sum, ascii, etc. the following table shows some of the corresponding functions that don't have the same name. for a more complete list, see "migrating oracle applications to sql server"
| description | oracle | ms sql server |
|---|---|---|
| smallest integer >= n | ceil | ceiling |
| modulus | mod | % |
| truncate number | trunc | <none> |
| max or min number or string in list | greatest, least |
<none> |
| translate null to n | nvl | isnull |
| return null if two values are equal | decode | nullif |
| string concatenation | concat(str1,str2) | str1 + str2 |
| convert ascii to char | chr | char |
| capitalize first letters of words | initcap | <none> |
| find string in string | instr | charindex |
| find pattern in string | instr | patindex |
| string length | length | datalength |
| pad string with blanks | lpad, rpad |
<none> |
| trim leading or trailing chars other than blanks | ltrim(str,chars), rtrim(str,chars) |
<none> |
| replace chars in string | replace | stuff |
| convert number to string | to_char | str, cast |
| convert string to number | to_number | cast |
| get substring from string | substr | substring |
| char for char translation in string | translate | <none> |
| date addition | add_month or + | dateadd |
| date subtraction | months_between or - | datediff |
| last day of month | last_day | <none> |
| time zone conversion | new_time | <none> |
| next specified weekday after date | next_day | <none> |
| convert date to string | to_char | datename, convert |
| convert string to date | to_date | cast |
| convert date to number | to_number(to_char(d)) | datepart |
| date round | round | convert |
| date truncate | trunc | convert |
| current date | sysdate | getdate |
| convert hex to binary | hextoraw | cast |
| convert binary to hex | rawtohex | convert |
| if statement in an expression | decode | case ... when or coalesce |
| user's login id number or name | uid, user | suser_id, suser_name |
| user's database id number or name | uid, user | user_id, user_name |
| current user | user | user |
