applies to: ms sql server 6.5+
a typical tradeoff for a database application is dynamic sql (sql commands embedded in the application -- for flexibility) vs. stored procedures (pre-compiled sql procedures stored in the database and invoked by name from the application -- for speed and control over what sql statements get executed). however, you can have the best of both worlds by using dynamic sql inside your stored procedures. in a stored procedure, you can use the exec statement to execute a string of sql statements that you built dynamically in the stored procedure or read from the database or any other data source.
thanks to steve rhoads for this tip.
--fred
sql enterprise manager tips
this section contains tips on the sql enterprise manager tool. it is a graphical front end to the database, allowing you to create, delete, view, and modify all ms sql server objects: tables, views, stored procedures, etc.
keyboard shortcuts
last updated: 6/20/1999
applies to: ms sql server 7.0
here is a list of some of the more useful shortcut keys in sql enterprise manager.
| key | function |
|---|---|
| f1 | help on sql enterprise manager |
| shift-f1 | help on syntax of current sql statement |
| ctrl-e | execute selected text in query analyzer |
| ctrl-r | hide/show results pane in query analyzer |
obviously, this list is far from complete. please feel free to mail me your favorite shortcuts. i'll add to this list as time permits.
see also: windows shortcut keys
--fred
sql generating sql
last updated: 2/7/1999
applies to: ms sql server 6.5+
to automate tedious database maintenance chores, you can use sql statements to generate sql statements that do your maintenance for you. for example, to change the permissions on all stored procedures in a database, you can use a select statement like:
select 'grant execute on ' + name + ' to public
go'
from sysobjects
where type = 'p'
the output of this select statement is a series of alternating grant and go statements, one pair per stored procedures, for all stored procedures in the database. then you copy that output as your next set of commands and execute it.
note: be sure to leave the line break before the word go. it is required to start on a new line, after the grant statement.
thanks to steve rhoads for this tip.
--fred
see also
last updated: 6/6/1999
applies to: ms sql server 6.5+
the following are good sources of info about ms sql server:
- ms sql server books on the msdn library cd.
--fred
differences between oracle and ms sql server
concepts and terminology
last updated: 4/24/2001
applies to: oracle 7.3+, ms sql server 6.5+
the following table shows some differences in concepts and terminology between oracle and ms sql server:
| concept/term | oracle | ms sql server |
|---|---|---|
| database engine | database | database server |
| database (collection of tables) | schema | database |
| roles/groups | roles | groups |
| database adminstrator account, database owner | dba | sa, dbo |
| data about the database | data dictionary - one per server |
database catalog - one per database "master" database - one per server |
| blocks and extents | blocks and extents | pages and extents |
| network software | sql*net | net-library |
| data stream protocol | transparent network substrate (tns) | tabular data stream (tds) |
| case sensitivity of names of tables, columns, etc. | case-insensitive | depends on character sort order, default is case-insensitive |
| synonyms | supported | not supported |
| readonly transaction | supported | not supported |
--fred
data types
last updated: 6/6/1999
applies to: oracle 7.3+, ms sql server 6.5+
the following table shows the corresponding data types in oracle and ms sql server:
| data type | oracle | ms sql server |
|---|---|---|
| fixed length string | char(n) - limit 2kb |
char(n), character(n) - limit 255 (6.5) - limit 8kb (7.0) |
| variable length string | varchar2(n), varchar(n) - limit 4kb in a column - limit 32kb in a variable - varchar is obsolete |
varchar(n), char varying(n), character varying(n) - limit 255 (6.5) - limit 8kb (7.0) |
| integer | integer, integer(n), smallint | integer (4 bytes), int (4 bytes), smallint (2 bytes), tinyint (1 byte), bit (1 bit) |
| fixed point | number, number(n), number(n,d), float, float(n), float(n,d) |
numeric, numeric(n), numeric(n,d), decimal, decimal(n), decimal(n,d), dec, dec(n), dec(n,d), money, smallmoney |
| floating point | decimal | float, float(n), double precision, real, |
| date | date | datetime, smalldatetime, timestamp - timestamp auto-updated |
| binary | raw(n) - limit 255 bytes |
binary(n), varbinary(n), binary varying(n) - limit 255 (6.5) - limit 8kb (7.0) |
| large string | long, long varchar - limit 2gb - limit one per table row clob - limit 4gb |
text - limit 2gb |
| large binary | long raw - limit 2gb - limit one per table row blob - limit 4gb |
image - limit 2gb |
| multi-byte chars | nchar(n) nvarchar(n) nclob - same limits as char, varchar, clob |
nchar(n), national char(n), national character(n) nvarchar(n), national char varying(n), national character varying(n) ntext, national text - same limits as char, varchar, text |
| os file | bfile | <not supported> |
| row identifier | implicit rowid column | (use an identity column) |
| secure os label | mlslabel, raw mlslabel | <not supported> |
| 128-bit unique number (uuid, guid) |
<not supported> | uniqueidentifier (version 7.0 only) |
