Learning SQL 2000 from Access Roots

Results 1 to 2 of 2

Thread: Learning SQL 2000 from Access Roots

  1. #1
    Join Date
    Dec 1969

    Default Learning SQL 2000 from Access Roots

    What basics should I be aware of when creating a data base and create queries using Microsoft SQL 2000?<BR><BR>I have a decent understanding of data bases in general, experience in using MS Access files, writing simple SQL statements, but haven&#039;t dealt with a real SQL 2000 data base.<BR><BR>Any big differences I need to be aware of? Should I treat the creation of my database and queries like Access? Any good tutorials for going from Access to real SQL?<BR><BR>FYI, I need access because the data base/site might have 50 - 200 projected concurrent users.

  2. #2
    Join Date
    Dec 1969

    Default Functions and dates?

    The biggest differences, *I* think, are in the "basics". The things you got used to in Access and now have to figure out again in SS.<BR><BR>For instance, look at all the handy functions you had in Access: IIF, CSTR, TRIM, etc. Yeah, you can do the equivalents in SS, but sometimes the differences are greater than the similarities.<BR><BR>IIF is a good example. Instead of something like:<BR> SELECT SUM( IIF( field1 &#062; field2, field1, field2 ) ) AS greater<BR>you have do something like this in SS:<BR> SELECT SUM( CASE WHEN field1 &#062; field2 THEN field1 ELSE field2 END ) AS greater<BR><BR>Or take minor junk like string concatenation: Access is nearly as "sloppy" about that as is VBScript:<BR> SELECT (field1 & ": " & field2) AS both<BR>and that would work even if field2 was a number or date, for example. With SS, you have to do:<BR> SELECT (field1 + ": " + CONVERT(VARCHAR, field2)) AS both<BR>[and that assumes that field1 was already a VARCHAR or CHAR field]. See? Not *BIG* things, but just stuff to relearn.<BR><BR>Then, of course, there are dates. You use &#039;...&#039; instead of #...#, to indicate literal dates. That&#039;s for starters.<BR><BR>Both Access and SS have *only* Date/Time fields, meaning that any Date/Time field can hold *both* a date and a time (you might choose to store only a date or only a time, but the field doesn&#039;t know that).<BR><BR>In Access, it&#039;s easy to extract just the date or just the time:<BR> DateValue( someDateTimeField )<BR> TimeValue( someDateTimeField )<BR>In SS it&#039;s a real pain. (Ask, if you really need it.)<BR><BR>In other ways, there are a lot of the same functions:<BR> DateAdd( )<BR> Year( )<BR> Month( )<BR> Day( )<BR> etc.<BR><BR>So one piece of advice: Book mark this page:<BR> http://msdn.microsoft.com/library/en-us/tsqlref/ts_syntaxc_9kvn.asp<BR><BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts