First steps to SQL Server

Question

I've working with Oracle for years (a like it so much and will a lot of years more) and I want to know about SQL Server. I have installed SQL Server Express.

Can anyone give some clues for working with It? For instance:

• There are any command like like sql*plus on Oracle or the mysqladmin?
• Which are the superusers names? And which are the default passwords for them?
• Which are the most important metadata tables? For query existing tables, indexs, etc.
• Any reference to query language?

Answer 1

Sql*plus - you get the Management Studio, which is basically the equivalent of Toad. A Graphical query editor/viewer (plus more).

Your default user is sa. You decide the password on install.

You're best off using the GUI to get to know the metadata tables - there's a tree with all the contraints, tables, procesures, triggers etc. Just browse, and use the 'script to' command (off the right-click menu) to generate the sql you would have typed to create them had you actually typed them, I find that powerful to learn what do do after using the wizards the GUI provides to do these tasks.

The language is a lot like Oracle, the biggest difference I found is the join syntax - if you use the (+) syntax, lose it and get with the ansi 'right outer join' style instead, its the only one SQLServer understands.

Answer 2

The equivalent to sqlplus is osql.

Usually there is one user called sa but the password can be given during installation. If SQL Server Authentication mode is enabled.

You can see the available views with meta-data using the following:

SELECT
  TABLE_NAME
FROM
  INFORMATION_SCHEMA.Views
WHERE
  TABLE_SCHEMA = 'INFORMATION_SCHEMA'
ORDER BY
  TABLE_NAME;

Look for T-SQL to learn about build in functions that can be used in queries.

Answer 3

Some things I found useful doing the same switch ...

ORA:  select table_name from dba_tables 
MSSS: select name from sysobjects where type = 'U'
      (select distinct type from sysobjects -- I found it very important to know what
 each magic value represents)

ORA:   select * from v$session
MSSS:  select * from master..sysprocesses

ORA:   select * from dba_users
MSSS:  Does not directly translate.  Google the terms DB user and DB logon.  Take a 
look at sp_change_users_login and understand why this Stored Proc exists (for me this
 was the path to understanding the difference)
       select * from sysusers
       select * from master..syslogins


MSSS: Select * from systypes  -- major help in resolving the 'magic numbers' in MSSS 

data dictionary.

There are 3 system databases Master, Model and MSDB. (more in 2005, 2008 but 3 v. important to know) 
  Master is similar to the SYS schema.  
  Model is a template; set values in here you want any new DB to inherit.  
  MSDB holds detail of scheduled jobs, etc.

1 thing I wish I'd known earlier :) Prefixing a Stored Proc with sp_ and placing it in the Master DB allows it to bve called from any other DB.

Answer 4

Books online (should be installed with SQL, else see here), is the best documentation of any Microsoft product that I know (or at least was until SQL2000, I haven't done much SQL since).

Answer 5

Disclaimer: I work for Quest Software, the makers of Toad.

If you're coming from an Oracle environment, you're probably used to Toad. Why not use Toad for SQL Server? There's a free beta right now of v4.5:

http://www.toadsoft.com/toadsqlserver/toad_sqlserver.htm

You can also use our free SQL Server community portal with tutorials and wiki articles:

http://sqlserverpedia.com/