SQL Coding: Microsoft T-SQL vs. Oracle PL/SQL

Here is a comparison of SQL usage and coding for the three most popular database products: Microsoft's T-SQL, Oracle's PL/SQL, and mySQL.

Related Topics:

Topics this page:

Site Map  
About this site  

Sound: Match found

SQL Limitation Factoids - Numbers to Remember

Maximum...	MS-SQL	Oracle
Rows per Table	-	unlimited
Database size	(2005) 32 terabytes (2008) 524,272 TB	-
Log file size	4 TB	-
BLOB, CLOB, NCLOB, BFILE bytes	-	4GB
Page extent contiguous size	64 KB (8 pages)	-
size of SQL Procedure	-	64KB
Page block size	8 KB (8,192) = header: 96 bytes body: 8096 bytes	-
bytes per row	8,060	(9i) 2000 (10g) 4000
char type bytes	8,000	255
Pages tracked by each PFS (Page Free Space) page	8,000	-
UDT size	(2005) 8,000 (2008) unlimited	-
VARCHAR2 / NVARCHAR2 & ROWID / UROWID bytes	n/a	4,000
int type value	2.147 billion (2^31)	-
Tables per Database	2 billion	-
Objects open per Database	2,147,483,647	-
Users and roles in database	-	2,147,483,638
LONG and Long Raw type bytes	-	2 billion
smallmoney type value	$214,748.0000	-
Money decimal precision	4 (.4444)	-
Files per Database	-	65,533
smallint type value User Connections databases per server instance	32,767	-
Files per database	32,767	-
Filegroups per database	256	-
Parameters per stored procedure or UDF (user defined function)	2,100	-
Files per physical Oracle Tablespace/MS-SQL Filegroup	-	1,022 (OS dep.)
Columns per SELECT statement	4,096	-
Columns per INSERT statement	1,024	-
Columns per non-wide base table Columns per (2008) wide table	1,024 30,000	1,000
Columns in composite index. primary/foreign key	16	32
Tables per SELECT statement	256	-
Non-clustered indexes per table	(2005) 249 (2008) 999	-
Characters in Table / Column Name	30	30
Index column key size bytes	900	-
Levels for nesting in stored procedures, subqueries, trigger levels	32	-
Clustered indexes per table	1	-

This page compares the capacity limitations of Oracle vs. Microsft SQL Server databases.

More on Data Types

SQL is pronounced either as a sequence of letters "Es-Queue-Ell" or as acronym "Sequel".

Microsoft: Maximum Capacity Specifications for SQL Server

Yahoo Computers and Internet > Programming and Development > Languages > SQL

MS-SQL Blogroll:

• Paul Nielson
• Kimberly Tripp
• SQL Server Performance
• sqlservercentral.com
• Plamen Ratchev
• Narayana Vyas Kondreddi
• Nigel Rivett

MySQL AB was created by Monty Widenius.

MySQL Enterprise Solutions (Indianapolis, Ind. : John Wiley & Sons, Inc. (US), 2003) by Pachev, Alexander "Sasha"

ANSI Standard Conformance

Microsoft's T-SQL is compliant with just the Entry Level of the SQL-92 ANSI (American National Standards Institute) standard, and not all features of the Intermediate and Full Levels of SQL-92.

Stored procedures are not part of the ANSI SQL standard.

SQL Commands

SQL commands are classified in these groups:
• DDL (Data Definition Language) commands change metadata of tables containing data. These commands (CREATE, ALTER, TRUNCATE, DROP) require a COMMIT. PL/SQL does not support ANSI-SQL's data definition commands.
• DML (Data Manipulation Language) commands (SELECT, INSERT, UPDATE, DELETE, MERGE) change data values in tables under transaction control. These commands can be undone.
• DCL (Data Control Language) commands (GRANT, ALTER, REVOKE) control permissions to access data.

Other Oracle SQL commands:
ANALYZE
AUDIT
CONNECT
LOCK
NOAUDIT
RENAME
SET ROLE
SET TRANSACTION

Interactive Execution Environments - Oracle vs. Microsoft

This table cross-references the jargon for the same concept from both products.

Action	Oracle SQL	Microsoft SQL
Language Name	PL/SQL - Procedural Language	Transact-SQL
Tool for interactive input, store, and running of stored procedures	SQLPLUSW userid/password@what.world	C:\Windows\system32\SQLServerManager.msc for 2005/2008 SQL Server Management Studio's SQL Editor SQLW isqlw for SQL Query Analyzer
Batch invocation	-	To invoke MS-SQL automatically, the SQL service Manager is placed in the Startup folder with a Target of: E:\MSSQL7\Binn\sqlmangr.exe /n
Read product documentation	-	For Books online: Within SQLW, Help toolbar -> Building SQL Server Applications -> Transact-SQL Reference -> System Stored Procedures (T-SQL)
Return to the OS	exit	-
To control output	set serveroutput on set serverout off	SET NOCOUNT ON
Send output to a file	spool myoutput.out	-
Stop sending output	spool off	-
Send output file to printer (for lpr)	spool out	-
To invoke SQL statements stored in file named my.sql	start my.sql	-
View properties about an object	select * from all_views	sp_help 'object_name' or sp_helptext 'object_name' for unencrypted comments in the syscomments system catalog table.
Text source of all stored objects belonging to the user	user_source	-
Tool to trap activity between client app and SQL to a flat file	-	SQL Server Profiler
Comment operator	-- double hyphen or between /* and */	-- double hyphen or IF 1 = 0 -- never true BEGIN PRINT 'Line 1'; PRINT 'Line 2'; END
To execute a procedure in the buffer	start or @ (ampersand)	?
To execute SQL script in the buffer	. dot run; or / (slash)	GO or F5 key or Ctrl-E
To concatenate and print	DBMS_OUTPUT.PUT_LINE ( 'hello' || v_name );	-
Ending a block	period ends each PL/SQL block	; semicolon ends each SQL block.

Every time an Oracle user invokes SQL*Plus, two scripts are also automatically executed:

• the Site Profile glogin.sql for all users defines column formats. By default, environment variable SQLPATH points to this file at $ORACLE_HOME/sqlplus/admin
• the login.sql for the user.

Batch Execution

SQLCMD Win32 utility from Microsoft enables SQL to be executed in batch mode such that it can be applied across several SQL servers in a single execution. The version available for SQL Server 2005 is in C:\Program Files\Microsoft SQL\90\Tools\Binn, so this should be in your PATH Environment Variable.

The GO command is needed at the end of each statement.

Output Space Used to a CSV file: MS-SQL

This procedure from Nigel creates a stored procedure s_SpaceUsed which outputs a file containing space used.

if exists (select * from sysobjects 
	where id = object_id(N'[dbo].[s_SpaceUsed]') 
	and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_SpaceUsed]
GO

Create procedure s_SpaceUsed
@SourceDB	varchar(128)
as
/*
exec s_SpaceUsed 'AdventureWorks2008'
*/

set nocount on

declare @sql varchar(128)
	create table #tables(name varchar(128))
	select @sql = 'insert #tables select TABLE_NAME from ' 
		+ @SourceDB + '.INFORMATION_SCHEMA.TABLES 
	where TABLE_TYPE = ''BASE TABLE'''
	exec (@sql)
	
	create table #SpaceUsed (name varchar(128), rows varchar(11), 
		reserved varchar(18), data varchar(18), index_size varchar(18), 
		unused varchar(18))
	declare @name varchar(128)
	select @name = ''
	while exists (select * from #tables where name > @name)
	begin
		select @name = min(name) from #tables where name > @name
		select @sql = 'exec ' + @SourceDB 
			+ '..sp_executesql N''insert #SpaceUsed exec sp_spaceused ' 
			+ @name + ''''
		exec (@sql)
	end
	select * from #SpaceUsed
	drop table #tables
	drop table #SpaceUsed
go

SQL Transactions

With T-SQL, each command is treated as a transaction unless specified.

SELECT @@TRANCOUNT returns 0 if you're not in an open transaction, and a value greater than 0 if you are.

Oracle SQL commands:

MS-SQL specific commands:

Raise errors of different severity levels:
RAISERRROR('Print',13,1)
RAISERRROR('Info',14,1)
RAISERRROR('Warning',15,1)
RAISERRROR('Critical',16,1)


MS T-SQL processes SELECT statements are logically processed in this order (regardless of the sequence they are coded):

1. FROM table
2. WHERE filter
3. GROUP BY
4. HAVING group values
5. SELECT (return) columns or variables
6. ORDER BY (sort by) columns

Clauses

Column clause	Table clause
SELECT *	FROM SchemaOwner.TableName   tuple ;

For a list of current users and processes within MS-SQL, use sp_who system stored procedure.
For more detailed information, use this undocumented stored procedure:
sp_who2 [[@login_name =] 'login']

sp_who3

To mark a stored procedure (myproc1) in the master db to start automatically when MS-SQL Server starts:

USE master
EXEC sp_procoption 'myproc1', 'startup', 'true'

Read-Only Global Environment Variables

Value	Oracle SQL	Microsoft SQL
-	-	select @@Connections; select @@Max_Connections;
Session Id of each connection in the current user process	-	select @@SPID; select @@ProcID;
% CPU Busy & Idle	-	select @@CPU_Busy; select @@Idle;
% IO Busy	-	select @@IO_Busy;
Number of Rows in most current statement.	-	select @@Cursor_Rows;
The first day of the the week (1 to 7) specified by SET DATEFIRST.	-	select @@DateFirst;
timestamp (datatype varbinary) provides the last-used rowversion value of the current database. A new rowversion value is generated when a row with a rowversion column is inserted or updated.	-	select @@DBTS;
The error number of previous SQL command (used instead of TRY/CATCH)	-	select @@Error; select @@Fetch_Status;
-	-	select @@Total_Errors;
Count of reads and writes	-	select @@Total_Read; select @@Total_Write;
Unlike SCOPE_IDENTITY(), not scope-specific way to get a unique id to the last auto-incremented record added among all tables during a session.	-	select @@Identity;
Language Identifier	-	select @@LangID; select @@Language;
-	-	select @@Lock_TimeOut;
-	-	select @@Max_Precision; select @@TimeTicks;
-	-	select @@Pack_Received; select @@Pack_Sent;
-	-	select @@RemServer; select @@ServerName; select @@ServiceName;
-	-	select @@TextSize;

Versions

select @@VERSION returns a value such as

9.00.1399.06 for v2005.

SELECT @@microsoftversion / 0x01000000 yields:

6 for sql v6.0
7 for sql 7
8 for sql 2000 [code named Shiloh (32-bit), Liberty (64-bit)]
9 for sql 2005 [code named Yukon]
10 for sql 2008 [code named Katmai (mountain in Alaska) / Akadia]
11 for sql 2010 [code named Killimanjaro (mountain in Keyna, Afica)]

SELECT @@microsoftversion & 0xffff gives the build number integer.

Within a .NET VB program, to use SQL Management Objects (SMO) DLLs introduced with Microsoft SQL Server 2005 folder C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies directory:

Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SmoEnum.dll

Use this code:

using Microsoft.SqlServer.Management.Common; 
using Microsoft.SqlServer.Management.Smo;
...
Server theServer = new Server("myServerName"); 
Console.WriteLine(theServer.Information.VersionString);

To get Oracle's version (such as "8.1.7.4.0" or "11.1.0.6.0") :

SQL> select instance, version, hostname, status, database_status,
to_char(startup_time,'DD-MON-YYYY HH:MI:SS') strtd AS 'STARTED'
from v$instance;

At a Unix command prompt:

% $ORACLE_HOME/bin/svrmgrl command=exit | grep "Release"

Oracle 9i introduced a package:

SET SERVEROUT ON
EXEC DBMS_OUTPUT.PUT_LINE (DBMS_DB_VERSION.VERSION DBMS_DB_VERSION.RELEASE);;

Bind Variables (temporary holding areas)

To define a bind variable in Oracle's SQL*Plus:

PROMPT
ACCEPT
VARIABLE x NUMBER

To print a bind variable in SQL*Plus:

PRINT :x; --Note the colon to designate an Oracle bind variable

Oracle PL/SQL prompts entry of values for variables preceded with an ampersand and ending with a semi-colon, such as WHERE empid = &empid;

To override Oracle's default prompt text, precede the SELECT statement with

ACCEPT var PROMPT 'Enter ...'

SQL*Plus preserves values for variables preceded with a double ampersand.

Type a slash and press Enter to rerun statements. SQL*Plus stores and reuses the most recently executed SQL statement in file afiedt.buf.

Loading Data Into Databases

There are several ways to get data into a SQL Server system.

To restore a .bak (backup) file, first do a FILELISTONLY to get LogicalName of files in the MOVE statement:

RESTORE FILELISTONLY 
FROM DISK = 'C:\projects\Ashim\db01.bak'
GO
RESTORE DATABASE db01
FROM DISK = 'C:\projects\Ashim\db01.bak'
WITH 
	MOVE 'sample_Data' 	TO 'C:\projects\Ashim\db01.mdf',
	MOVE 'sample_Log' 	TO 'C:\projects\Ashim\db01.LDF'
GO

To attach a trusted .mdf and .ldf file, instead of the less secure sp_attach_db procedure using this T-SQL:

CREATE DATABASE
ON 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PetShop.mdf'
FOR ATTACH

The "MSSQL10.MSSQLSERVER" is the instance name.

To import tab-delimited csv files, the AdventureWorks sample database for Microsoft SQL Server 2005/2008 use this BULK INSERT code which runs within the SQL Server process (instead of BCP, which creates its own process):

PRINT 'Loading [Production].[WorkOrder]';
BULK INSERT [Production].[WorkOrder]
FROM '$(SqlSamplesSourceDataPath)AdventureWorks 2008 OLTP\WorkOrder.csv'
WITH (

CHECK_CONSTRAINTS,
CODEPAGE='ACP',
DATAFILETYPE='char',
FIELDTERMINATOR='\t',
ROWTERMINATOR='\n',
KEEPIDENTITY,
TABLOCK
);
• TABLOCK avoids row-at-a-time locking, which eats up time.
• ROWS PER BATCH = 2500 imports multiple streams into one table while avoiding lock escalation.

SQL 2008 enhanced the VALUES clause for multiple rows:

INSERT INTO dbo.Orders (orderid, orderdate, empid, custid)
VALUES
	  (10003, '20090213', 4, 'B'),
	  (10004, '20090214', 1, 'A'),
	  (10005, '20090213', 1, 'C'),
	  (10006, '20090215', 3, 'C');

$99 DBLoad shareware provides a GUI to load data from text delimited files and to transfer data from among databases of different vendors (MySQL to Oracle, etc.), The package can be setup to transfer on a set schedule.

# Manually load a text delimited file named "ImportFile.csv" on a PC into MySQL table1:
LOAD DATA LOCAL INFILE "./ImportFile.csv"
     INTO TABLE table1
     FIELDS TERMINATED BY ","
     OPTIONALLY ENCLOSED BY """"
     LINES TERMINATED BY "\r\n"
     (field1, filed2, field3);

The order of fields in the flat file should match fields in the database.
If your file is delimited by tabs, use instead: FIELDS TERMINATED BY "\t"

This assumes that there is a valid .my.cnf file with entries such as:

[client]
user = DBUSERNAME
password = DBPASSWORD
host = DBSERVER
[mysql]
database = DBNAME

This also assumes that permissions have been changed on the file to make it writable with a shell command such as:

chmod 600 /.my.cnf

Scriptella ETL (Extract-Transform-Load) tool is a Apache Open sourced Java program that uses XML syntax to load CSV data into databases Javadoca on it. Notes on this

robocopy (a contraction of Robust File Copy), is a command-line directory replication command to mirror directory trees inclusive of all NTFS attributes and properties. It is more reliable because it includes auto-restart in case of network connection disruption. It was once in the Windows Resource Kit but since has become a standard feature of Windows Vista and Windows Server 2008.

Formatting and Transforms

The lack of a value is called "NULL" -- the value of columns before being populated with actual values. Null values are not returned by SQL queries. Comparing a variable to the reserved word NULL will return NULL rather than TRUE. So to transform nulls to recognizable value, use a special built-in (datatype sensitive) function:

Oracle SQL	Microsoft SQL
NVL( column_name, value_if_null ) NVL( num_field, 0 ) NVL( txt_field, "NULL" )	ISNULL( column_name, value_if_null ) ISNULL( price, $0.00 ) ISNULL( SSN, 'NNN-NN-NNNN' )

In T-SQL, the COALESCE function below is often used to set the current database to work with if a database name is not specified in the input parameter:

SET @dbname = COALESCE(@dbnameParam, DB_NAME())
SELECT	@dbname AS [Database Name], ...

In Oracle, to calculate numbers or display text not from any table, use the special dummy table owned by Oracle user sys:

select 1+1 FROM dual

Data Definitions

Action	Oracle SQL	Microsoft SQL
Format US Social Security (9 digit) Number	-	SELECT STUFF(STUFF('123456789',4,0,'-'),7,0,'-')
Convert a small number of fixed values	decode( weekday, 1,'Sun', 2,'Mon', 3,'Tue', 4,'Wed', 5,'Thu', 6,'Fri', 7,'Sun', '???')	-
Datatypes and Value Assignment	skalar char, number, long, date, and varchar2 as in price := qty*(cost*2.5); plus v_valid_order boolean not null := true; and tables	-
Set date format just for current session	alter session set nls_date_format = 'YYYY-MON-DD-HH24:MI:ss'	SET DATEFORMAT
Identify date of table	-	SELECT crdate FROM sysobjects WHERE name = 'mytable'
Create Custom Datatype	-	EXEC sp_addtype @typename=typeSSN, @phystype='CHAR(11)', @nulltype='NOT NULL'
Create a Table with a Check Constraint using a custom data type typeSSN	-	CREATE TABLE SSNTable ( EmployeeID INT PRIMARY KEY, EmployeeSSN typeSSN, CONSTRAINT SSNCheck CHECK ( EmployeeSSN LIKE '[0-9][0-9][0-9]—[0-9][0-9]—[0-9][0-9][0-9][0-9]' ) )
Encryption	-	SET ENCRYPTION ON -- Introduced with SQL 2008, enables cell-level Transparent Data Encryption (TDE) using Data Encryption Key (DEK).

Date Time User Defined Functions

T-SQL 2008 allows SELECT $identity FROM ... to display values of the identity (ID) column for the table.

Data Integrity Constraints

The preferred method for maintaining data integrity is to add constraints associated with tables. Rules and default objects are, as my tween daughter says, “so last season”!

Type of Constraint	Oracle	MS-SQL
list constraints	select constraint_name from user_constraints;	?
Domain integrity by limiting acceptable values for a column entry.	-	CHECK
PRIMARY KEY A table can only have one column to uniquely identify rows. However, a composite index can consist of up to 16 columns.	-	CONSTRAINT tab1_pk PRIMARY KEY (col1, col2) If this is not specified, the MS-SQL default is to create a clustered index which stores data in physical order within leaf nodes containing data pages which are linked in a doubly-linked list implemented as a B-tree index structure that supports fast retrieval of the rows based on their clustered index key values. Tables created with a non-clustered index are called heaps with leaf nodes containing index rows. A covering index includes all columns referenced in the query such that the index contains the data sought in order to avoid looking up actual data in the table -- good If applications perform the same query over and over on the same table.
UNIQUE Entity integrity A non-primary key column is unique among all rows.	-	By default, a nonclustered index is created for each such column. Each combination of columns that could uniquely identify rows in a table is called a candidate key.
NOT NULL	-	Special processing for null values degrade performance.
FOREIGN KEY A foreign key in one table points to a primary key in another table.	-	ALTER TABLE table1 col_a INT NULL CONSTRAINT tab1_fk1 REFERENCE table2(tab1_col_pk)
IDENTITY property (starting IdentitySeed, IdentityIncrement)	-	ALTER TABLE table1 ADD Identity_column INT IDENTITY(1,1) GO Whether a table has an identity column can be determined using the OBJECTPROPERTY function. This can be selected using the IDENTITYCOL keyword.
To turn off Identify checking temporarily (for ETL)	-	SET IDENTITY_INSERT mytable On; INSERT mytable (IdentityColumnID, etc. ) ... SET IDENTITY_INSERT mytable Off;

Default Objects

In Microsoft SQL Case statements:

SELECT CustomerTypeName, [Default],
CASE [Default]
WHEN 1 THEN 'default type'
WHEN 0 THEN 'alternate'
ELSE '-'
End as AssignStatus -- end case
FROM dbo.CustomerType

This replaces sp_bindefault and sp_bindrule objects in SQL7 now recognizes only for backward compatibility.

USE table1
GO
CREATE DEFAULT default_par AS 72
GO
sp_bindefault default_par, 'scores.par'

By default, table names specified in the FROM clause have an implicit schema associated with the user's login. A schema is a logical grouping of database objects based on the user/owner. Tables in another schema can be specified if the other schema is prepended to the table:

FROM other_schema.other_table

Oracle supplied built-in package UTL_FILE can be used to read or write text operating system files from within PL/SQL.

Indexes

Action	Oracle SQL	Microsoft SQL
Create Index	-	Syntax: CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name ON table (column [,...n]) [WITH [PAD_INDEX] [[,] FILLFACTOR = fillfactor] [[,] IGNORE_DUP_KEY] [[,] DROP_EXISTING] [[,] STATISTICS_NONRECOMPUTE] [ON filegroup]

• MS-SQL stores as many data rows as can fit in 8KB pages.
• To support sequential retrieval of data, each data page contains a header record containing a row locator which points to the previous and next data page.
• To arrange for fast sequential access, data in a table may be arranged physically to a key (such as a sequentially assigned employee number or the date/timestamp of entry). Such a table is usually physically pre-sorted according to its clustered index key. This takes 120% more space than the space of data alone. If a new row is inserted in the middle, data rows are physically moved to make room for it. So it's not a good idea to cluster index a table by a column which is not sequentially input. This allows queries to be answered without the time-consuming task of sorting.
• Since MS-SQL7, key values from several fields (such as last name, first name, middle initial, etc.) can be stored together in composite index spanning up to 16 columns.
• To speed up the random retrieval of data pages, clustered indexes are created and stored on index pages apart from the data pages so that sorting is performed on pointers to the data instead of the entire data pages.
• Clustered indexes are organized using a technique called B-trees, which are depicted visually as a triangular upside-down tree (like a pyramid).
• The top node of the B-tree pyramid is the sysindexes.root node.
• A lookup traverses the Intermediate nodes in the middle.
• Pages containing data are called leaf nodes at the bottom layer.
• To avoid the need to shuffle index pages, indexes are built with only a percentage full. FILLFACTOR=100 specifies full index leaf, which limits reads to resolve queries. This reduces space usage at the expense of time for page splits during inserts and updates.
• The PAD_INDEX option specifies intermediate page fullness.
• Index fields should be created as 'NOT NULL'.
• When IGNORE_DUP_KEY is specified, SQL ignores individual duplicates but not the entire request.
• The DMV sys.dm_db_index_usage_stats lists indexes used by select, update or delete operations. Conversely, unused indexes do not appear on the list should be removed to improve OLTP performance.

Index Tuning

Which tables in a database may need indexes?
Which tables are being scanned by queries instead of using an index?

Microsoft's Index Tuning Wizard uses a workload file generated by the SQL Server Profiler Create Trace Wizard "Identify Scans of Large Tables" trace run.

Its results are presented in a Index Usage Report of the percentage of queries and recommended indexes.

For applications that perform the same query over and over on the same table, consider creating a covering index on the table.

However, each additional index operating on a table increases the time to do CREATE, UPDATE, etc.

Indexes on integer columns are significantly faster than columns with character values.

MS-SQL Configuration

For faster MS-SQL Server restart, set the tempdb to the size needed while running a normal load. The larger size avoids the time to autogrow the tempdb.

Also set Tempdb to autogrow at increments appropriate to the maximum anticipated load.

Index Work

Action	Oracle	MS-SQL
Create statistics on every column supporting statistics in the current database		sp_createstats
Create statistics data from the table.		CREATE STATISTICS
When were statistics last updated?		STATS_DATE
Determines whether a particular statistic will be automatically updated.		sp_createstats
The density of an existing statistic set.		DBCC_SHOW_STATISTICS
Update statistics data from the table.		UPDATE STATISTICS
Remove statistics data from the table.		DROP STATISTICS

To rebuild all indexes in a MS-SQL database, schedule an undocumented procedure to execute during CPU idle time and slow production periods:

sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"

DBCC SHOWCONTIG Fragmentation Statistics

SQL Server's DBCC (Database Console Command) interface is rather dated.

An out-of-order page is one for which the next page indicated in an IAM is a different page than the page pointed to by the next page pointer in the leaf page. Huh?

Statistic	Description
Pages Scanned	Number of pages in the table or index found during scanning.
Extents Scanned	Number of physical extents among pages scanned.
Extent Switches	Number of times the DBCC statement left an extent while it traversed the pages of the extent.
Avg. Pages per Extent	Number of pages per extent in the page chain.
Scan Density [Best Count: Actual Count]	100 percent if everything is contiguous. The smaller this value, the more fragmentation exists. The best count is the ideal number of extent changes that would be necessary if everything were contiguously linked. The actual count is the actual number of extent changes.
Logical Scan Fragmentation	The percentage of out-of-order pages returned from scanning the leaf pages of an index.
Extent Scan Fragmentation	Percentage from a count of out-of-order pages divided by the count of all leaf pages of an index scanned.
Avg. Bytes free per page	The absolute number of free bytes averaged over all pages scanned. Lower values are better because they make fuller use of each page. Higher row sizes make this number worse.
Avg. Page density (full)	Percentage of each page's bytes of useful data divided by the total bytes of all pages scanned. Higher values are better because they make fuller use of each page. make fuller use of each page. er worse.

Query Joins

• inner joins
• left outer joins
• cross joins to connect primary key or to create test data or pidgeon-hole positions for future data values.
• nested loops
• merge joins
• hash joins
• Star Joins (MS-SQL 2008)

Inner Joins

Inner joins are the most common for connecting a category look-up table to a value.

Microsoft has deprecated "legacy" joins which use the WHERE clause to link tables. This approach is also used in Oracle.

USE ...
SELECT
WHERE ...
ORDER BY ...

Microsoft instead uses the JOIN keyword.

USE ...
SELECT
WHERE ...
ORDER BY ...
JOIN ...

Views

Views simplify ad-hoc queries by saving SQL statements consisting of multiple joins.

Action	Oracle SQL	Microsoft SQL
Create/Alter View	-	CREATE VIEW testview AS SELECT firstname, lastname FROM employees ... or ALTER VIEW testview AS NOTE: T-SQL has no ALTER VIEW IF EXISTS
List Views defined	select view_name from user_views;	-
Display read I/O	-	SET STATISTICS_IO ON
Display Clustered indexes used in optimized query Execution Plans (algorithms)	-	SET SHOWPLAN_TEXT ON or SET SHOWPLAN_ALL ON for resource costing estimates or graphical SHOWPLAN in SQL Server Query Analyzer
Update Statistics Used for Auto Indexing	-	UPDATE STATISTICS or UPDATE STATISTICS NORECOMPUTE to stop updating
Display database linkages and sizes	-	DBCC CHECKTABLE
Get Fragmentation Statistics	-	SELECT OBJECT_ID('PUBS..TITLES') GO DBCC SHOWCONTIG(123456789) number from Data Base Consistency Checker response DBCC CHECKTABLE displays linkages and sizes
Sync view metadata with view	-	sp_refreshview 'viewname'

Avoid ORDER BY within views unless TOP is used.

Views cannot access temporary tables.

View Information

Information Stored	Oracle Data Dictionary View Name	Microsoft MS-SQL
A list of current errors on all objects accessible to the user	all_errors	.
Text source of all stored objects accessible to the user	all_source	.
Current errors on all stored objects in the database	dba_errors	.
All PL/SQL objects in the database	dba_object_size	.
Text source of all stored objects in the database	dba_source	.
Current errors on all a user's stored objects	user_errors	.
Text source of all stored objects belonging to the user	user_source	.
User's PL/SQL objects	user_object_size	.
Top 50 CPU bound statements (Queries Expensive for CPU)	-	SELECT TOP 50 (a.total_worker_time/a.execution_count) as [Avg_CPU_Time], Convert(Varchar,Last_Execution_Time) as 'Last_execution_Time', Total_Physical_Reads, SUBSTRING(b.text,a.statement_start_offset/2, (case when a.statement_end_offset = -1 then len(convert(nvarchar(max), b.text)) * 2 else a.statement_end_offset end - a.statement_start_offset)/2) as Query_Text, dbname=Upper(db_name(b.dbid)), b.objectid as 'Object_ID' FROM sys.dm_exec_query_stats a cross apply sys.dm_exec_sql_text(a.sql_handle) as b ORDER BY [Avg_CPU_Time] DESC

Logic Control

Action	Oracle SQL	Microsoft SQL
Control structures	PL/SQL extensions to SQL: conditional if-then-else	-
Control structures	PL/SQL extensions to SQL: iterative for-loop sequential while-loop & end loop; exit-when unconditional flow-control goto	-
Labels to go to	<< SampleLabel >>.	-
Sequential processing using a cursor	declare   cursor each_emp is     select ename, sal     from emp     where deptno = 30;   emp_name varchar2(20);   emp_sal number(7,2); begin   open each_emp;   loop     fetch each_emp       into emp_name, emp_sal;     if each_emp%NOTFOUND then       exit;     end if;     emp_sal := emp_sal + (emp_sal * .10);   end loop;   close each_emp; end;	use payroll declare @ename as varchar(20) declare @emp_sal as decimal(7,2) DECLARE @each_emp CURSOR FOR SELECT ename, pay_rate FROM emp inner join pay on emp.emp_id = pay.emp_id where deptno = 30 OPEN each_emp FETCH NEXT FROM emp_cursor into @emp_name, @emp_sal WHILE @@FETCH_STATUS = 0 BEGIN begin set @emp_sal = (@emp_sal + (@emp_sal * .10)) FETCH NEXT FROM each_emp into @emp_name, @emp_sal end CLOSE each_emp DEALLOCATE each_emp END
Related functions and cursors stored together	Packages	-
Table support	declare   type emp_table_type is     table of varchar2(35)     index by binary_integer;   emp_table emp_table_type;   i binary_integer := 0;   cursor each_emp is     select ename     from emp; begin   open each_emp;   loop     i := i + 1;     fetch each_emp       into emp_table(i);     if each_emp%NOTFOUND then       exit;     end if;   end loop;   statement(s);   -- work with emp_table(1), emp_table(2), etc.   close each_emp; end;	EXEC CREATE TABLE emp_table (column1 INT NOT NULL, column2 CHAR(10) NOT NULL) GO EXEC sp_help emp_table GO SELECT emp_table FROM INFORMATION_SCHEMA.TABLES GO
Debugging	Public package dbms_output creates a buffer to accept dbms_output.put or put_line commands. set serverout displays the buffer.	exec sp_addmessage 50500, 14, 'msg' creates error code 50500 with severity 14 at state 1. raiseerror (50500,14,1) issues the message

PL/SQL does not support SQL's data definition commands, session control commands, or the system control commands.

SQL_RoboPERF procedure reports

1) HIGHCPU - Top 50 statements that are CPU intensive
2) LOCKS - Detect real time locking information (like system stored procedure sp_lock2 corrected for 2000)
3) TempDBData - View current SQL Statements from TempDB
4) SQLJobs - View SQL Server Job details
5) IndexUsage - View which indexes are being used

Locks for Transaction Isolation

Dynamic management view (DMV) sys.dm_tran_locks lists locks GRANT or in WAIT at a moment in time.

T-SQL by default imposes a shared (S) lock on the data resource requested by each transaction request. This lock is released as soon as the read statement is done. Multiple transactions can hold shared locks on the same data resource simultaneously. This is done for lock compatibility -- so that a grant request for exclusive locking can be rejected on a resource already having any kind of lock.

To get an exclusive lock (X), a transaction must first acquire an intent exclusive (IX) lock both on the page where the row resides plus an intent exclusive lock on the object that owns the page.

update mode lock converts to exclusive.

Use TRY/CATCH to handle deadlocks (a fatal embrace) when two transactions are waiting on each other.
SQL Server Profiler, New Trace, Event Deadlock Graph and Lock:Deadlock -- schema locks?

Coarse-grain locks (such as on a whole database or table during schema changes), block locks of smaller granularity on RID (row key), 8KB page, 64KB extents, allocation units, or object (for example, table), and heap or B-tree.

SQL Server 2008 adds a way to set table LOCK_ESCALATION using the ALTER TABLE statement to control automatic lock escalation.

By default, T-SQL implements the isolation level to READ COMMITTED to prevent "dirty reads" when a transaction sees uncommitted changes by other transactions. This causes locks to wait until changes are committed.

To enable dirty reads, set the least restrictive isolation level -- READ UNCOMMITTED.

To prevent "non-repeatable" reads when a transaction sees changes committed by other transactions, set isolation level to REPEATABLE READ.

To prevent "phantom rows" when repeating the same SELECT reflect changes committed by other transactions. set isolation level to SERIALIZABLE.

T-SQL Snapshot Isolation is an alternative, but it puts a heavy load on tempdb.

To set session lock expiration time-out ...

Conventions

Item/Object	add/drop/help Stored Procedure EXEC ...	To get a list: SELECT createdate, updatedate, accdate, ... FROM server.database.owner.object	Executive GUI
NT Network Accounts		Domain SAM
SQL Login		dbname, loginname, isntgroup FROM master..syslogins (view of master table sysxlogins)
Databases	sp_helpdb database_name	name, filename, crdate FROM master..sysdatabases
Columns		TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
Privileges		* FROM INFORMATION_SCHEMA.TABLES_PRIVELEGES
Database User Accounts	sp_addlogin login, name_in_db	uid, name from northwind..sysusers where islogin = 1
	sp_grantdbaccess login, name_in_db
	sp_grantlogin login, name_in_db or GRANT CREATE VIEW, CREATE PROCEDURE, CREATE TABLE TO cathy
	sp_revokelogin, sp_denylogin login, name_in_db or REVOKE CREATE VIEW, CREATE PROCEDURE TO cathy
	sp_denylogin login, name_in_db or DENY CREATE VIEW, CREATE PROCEDURE TO cathy
Permissions for Uid's		uid, action, protecttype from northwind..sysprotects
Fixed Server Role	sp_addsrvrolemember @loginame, @rolename	name from master..syslogins system table	for Logins
Fixed Database Role	sp_addrolemember role, security_account	name from northwind..sysusers where issqlrole = 1
Database Standard User Role	sp_addrole @rolename, @ownername	name from northwind..sysusers where issqluser = 1
Application Role	sp_addapprole @rolename, @password GO sp_setapprole @rolename, @password	name from northwind..sysusers where isapprole = 1
Primary Data Files	CREATE DATABASE library ON PRIMARY ( name=library_data, FILEname='c:\mssql7\data\library.mdf', size=10MB, MAXsize=15MB, FILEGROWTH=20%) DROP DATABASE library	...\*.mdf containing 8KB data pages (128 pages per MB)
Secondary Data Files		...\*.ndf containing 8KB data pages (128 pages per MB)
Log Files	... LOG ON ( name=library_log, FILEname='c:\mssql7\data\library.ldf', size=3MB, MAXsize=5MB, FILEGROWTH=1MB)	C:\Mssql7\Log\*.ldf (sized 25% of data file by default)
File Groups	ALTER DATABASE northwind ADD FILEGROUP orderhistorygroup GO

• The NT Registry is used to store SQL start-up parameters.
• Unlike User-defined roles, Fixed means you can't create new roles.
• Max row size = 8060 bytes

Aggregate Functions

Sum, Avg, Min, Max, StDev, StDevP, Var, VarP

Max is less efficient than top(n).

count(*) returns the number of rows in the table.
count(column name) returns the number of non-null values.

Output table to a flat text file:

DECLARE @sql VARCHAR(8000)
SELECT @sql = 'bcp master..sysobjects out c:\sysobjects.txt -c -t, -T -S ' + @@servername
EXEC master..xp_cmdshell @sql

User Defined Functions

To avoid the full scan that MS-SQL 6.5 & 2000 performs for SELECT COUNT(*), the ROW_COUNT() scalar UDF from Alexander Chigrik returns from sysindexes the total row count for a given table:

CREATE FUNCTION ROW_COUNT ( @table sysname )
RETURNS int
AS
BEGIN
  DECLARE @row_count int
  SELECT @row_count = rows FROM sysindexes
    WHERE id = OBJECT_ID(@table) AND indid < 2
  RETURN @row_count
END
GO
USE pubs
GO
SELECT dbo.ROW_COUNT('authors')
GO

Cursors

Unlike a GUI cursor, a databse cursor is an area of memory which holds data fetched from the database. Global cursors are deallocated when the connection ends. Local cursors are limited to a single procedure.

A database cursor is a variable that runs through the tuples of some relation. This relation can be a stored table or the answer to a query. Fetching into the cursor each tuple of the relation allows processing the value of each such tuple. If the relation is stored, we can also update or delete the tuple at the current cursor position.

Cursors must die!

Cursors are derided by some because cursors process one row at a time, so they scale less well than SET transactions (correlated subqueries) which operate on entire sets of rows at a time. It is also a hassle to allocate and de-allocate cursor memory, such as these MS-SQL statements from Paul Nielson:
USE AdventureWorks
SET NoCount ON
go
-- Add column to hold the CumulativeTotal:
ALTER TABLE Sales.SalesOrderHeader
ADD CumulativeTotal MONEY NOT NULL
CONSTRAINT dfSalesOrderHeader DEFAULT(0)
ALTER INDEX ALL ON Sales.SalesOrderHeader
REBUILD WITH (FILL FACTOR = 99, SORT_IN_TEMPDB = ON );
go
DECLARE
@SalesOrderID INT,
@TotalDue MONEY,
@CumulativeTotal MONEY
SET @CumulativeTotal = 0
DECLARE cRun CURSOR FAST_FORWARD -- not READ_ONLY

FOR SELECT SalesOrderId, Total Due
FROM Sales.SalesOrderHeader
ORDER BY OrderDate, SalesOrderID
OPEN cRun
FETCH cRun INTO @SalesOrderID, @TotalDue -- to prime the cursor

WHILE @@Fetch_Status = 0 --[0=FOUND]

BEGIN --loop to accumulate values:
SET @CululativeTotal = @CumulativeTotal + @TotalDue
UPDATE Sales.SalesOrderHeader
SET CumulativeTotal = @CumulativeTotal
WHERE SalesOrderID = @DalesOrderID
FETCH cRun INTO @SalesOrderID, @TotalDue -- next
END
CLOSE cRun
DEALLOCATE cRun

This example is shown because cursors are more efficient than SET transactions at calculating running sums (cumulative totals) and at dynamic DDL Code iteration.

PL/SQL example:

DECLARE
v_cnt NUMBER;
c1rec NUMBER;
CURSOR c1 IS 
    SELECT * FROM customers;
BEGIN OPEN c1;
FETCH c1 INTO c1rec;

Declaring a SCROLL CURSOR allows you to fetch backward with these commands:

FETCH PRIOR
FETCH RELATIVE -3

Declaring a CURSOR INSENSITIVE makes it not sensitive to changes in the table and won't refresh.

FETCH FIRST
FETCH NEXT
FETCH RELATIVE 3   (3 rows prior)
FETCH ABSOLUTE 4
FETCH ABSOLUTE -1
FETCH LAST   (bottom row)


To determine how many rows have been affected by the executable statement:

MS-SQL updates @@ROWCOUNT and displays even when SET NOCOUNT ON which controls display of "nn rows affected" at the end Transact-SQL statements such as SELECT, INSERT, UPDATE, and DELETE. The default is OFF to reduce network traffic to send DONE_IN_PROC messages to the client for each statement in a stored procedure.

Oracle provides built-in cursor ATTRIBUTE SQL%ROWCOUNT, one of Oracle's four built-in cursor ATTRIBUTES to determine the status of cursors:

• SQL%FOUND tells if one or more rows have been found that fit the select criteria.
• SQL%NOTFOUND tells if no rows are affected by the executable statement.
• SQL%ISOPEN will be FALSE in SQL and is used only after an EXPLICIT cursor has been defined.

To link a row updated to the latest row fetched inside a cursor called row_cur:

UPDATE x
   SET due_dt = sysdate + 2 
WHERE CURRENT OF row_cur;

Cursor variables allow different SQL SELECT statements.

To define a Oracle PL/SQL procedural block using an explicit cursor:

DECLARE --optional PL/SQL user variables, cursors, local subprograms, exceptions:
v1 NUMBER(3);
v_empno employee.empno%TYPE;
CURSOR emptyp_cur IS
SELECT emptyp.type_desc
FROM employee_type
WHERE type_code = :emp.type_code
BEGIN --mandatory data manipulation statement(s):
v1 := 3;
DBMS_OUTPUT.PUT_LINE('v1= ' || v1);
IF NOT emptyp_cur%ISOPEN THEN
OPEN emptyp_cur; END IF;
FETCH emptyp_cur INTO :emp.type_desc;
CLOSE emptyp_cur;

INSERT INTO employee VALUES (6, 'TOM LEE', 10000);
UPDATE employee SET sal = sal + 5000 WHERE empno = 6;
SELECT sal INTO v_sal FROM employee WHERE empno = 6;
COMMIT;
EXECUTE procedure EXCEPTION --optional error handling code

WHEN exception
THEN   null; passes control to the next statement. END; --mandatory .

Set-Base Query Optimization

Using a SET command in MS-SQL :

SET NOCOUNT ON
GO
DECLARE @ComulativeTotal MONEY
SET @ComulativeTotal=0
UPDATE Sales.SalesOrderHeader
SET @ComulativeTotal=ComulativeTotal=@ComulativeTotal+ISNULL(TotalDue, 0)

The SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column.

The problem with this is there is no assurance of data ordering assuming a clustered index.

• Use a data-driven database schema design
• Use UDF to embed logic within a set-based query
• Use CASE and PIVOT expressions for flexible logic in crosstab queries
• Use multiple queries and multiple assignment variables to break down complex tasks

Common Table Expressions vs. Derived Tables MS-SQL 2005 introduced CTEs which (as Nigel Rivett explains) is used instead of functions, temp tables, cursors, etc. because it's a "temporary result set" that exists only within the scope of a single SQL statement.

But unlike a derived table, a CTE can be referenced multiple times in the same query. which allows CTEs to be great at recursion. used to calculate percentages by performing arithmetic using aggregates without repeating queries or using a temp table.

Triggers on Tables

Action	Oracle	MS-SQL
Get a list of triggers	.	SELECT trigger_name 'INSERT'=object_name(instrig), 'UPDATE'=object_name(updtrig), 'DELETE'=object_name(deltrig), FROM sysobjects WHERE type = 'U' AND (instrig <> 0 OR updtrig <> 0 OR deltrig <> 0)
Create a new trigger	.	CREATE OR REPLACE TRIGGER trigger_name after logon -- after logoff -- after servererror -- after startup -- after shutdown -- after create -- after drop -- after alter ON table FOR ( [DELETE] [,] [INSERT] [,] [UPDATE] } [WITH ENCRYPTION] AS sql_statement
Drop a trigger	.	DROP TRIGGER trigger_name


Sequence of Trigger processing:


Step	Action
1	Execute before statement triggers.
2	Loop for each row affected by the SQL statement.      a.    Execute before row triggers.      b.    Lock and change rows, check for integrity constraints.
3	Complete deferred integrity constraint checking.
4	Execute after statement triggers.

Alerts

MS-SQL Alerts are stored in the Msdb physical database and managed by the SQL Server Agent

Debugging

• First, look for the most common syntax errors:
  • missing semi-colon ending SQL blocks
  • missing parens,
  • unmatched variable names
  • referencing tables or column names that have not been defined (spelling)
• Look for these mistakes at the line Oracle reports in error.
• Use the sho err (show error) command to list code errors.

  The MS-SQL Server Profiler Create Trace Wizard run with "Identify Scans of Large Tables" (during non-peak times from a workstation machine) determines (into a trace text file) which tables are being scanned by queries instead of using an index.

  Ignite™ for SQL Server Performance Finds, analyzes, and resolves bottlenecks impacting SQL Server response time with Ignite™ for SQL Server.

Oracle Resources

Oracle Underground FAQ list provides links, message boards, articles, a glossary and other resources submitted by Oracle affciados.

Moderator Frank Naude's Oracle PL/SQL FAQ

Oracle View 1996-8 is an online magazine by Intelligent Enterprise, Inc. for architects and managers of information technology on the implementation and management of Oracle database products, development strategies, and application tools.

Andrew Odewahn's Oracle Web Applications: Developer's Introduction online book

The Virginia Tech reference tool describes functions, procedures, data types in the PL/SQL packages that make up the Web Application Server PL/SQL Web Toolkit in addition to an Overview, Glossary, FAQ, Installation, and Cartridges.

Reveal Net: PL/SQL Pipeline maintained by the International Oracle Users Group (IOUG-A) of more than 7,500 Oracle professionals,

Want to access Oracle and mySQL databases from .NET apps?
CoreLab's OraDirect.NET can do it for less than $200.

MS-SQL Resources

Amazon EC2 runs Windows SQL Server for about $1 per instance hour.

Microsoft's Best Practices Analyzer for SQL Server 2000 (bpasetup.msi) published 5/25/2004 reports on the implementation of common Best Practices such as the usage and administration aspects of SQL Server databases.

Microsoft SQL Analysis Services:

• Installing
• Monitoring Analysis Services Performance
• How to Install SQL Server 7.0, Enterprise Edition on Microsoft Cluster Server: Step by Step Instructions To cluster MSDTC run comclust.exe on both nodes before you set up SQL Server.

  network bandwidth is likely to become a problem first, then RAM within the server, then maybe CPU capacity.

  To upgrade from 6.5 to 7, first uncluster, remove replication, migrate, then re-cluster. SQL Server 2000 - there is no such thing as "unclustering" anymore.

  How to Upgrade SQL Server 6.5 and 7.0 to SQL Server 2000 (September 2000)

  fibre interconnects for the drives and a private network for the heartbeat (most of the time I simply use a crossover cable).

  between 5 & 15% reduction in performance impact on the host during an online backup.

  Replication wizard

  6 roundtrips are needed for a 2-node 2-phase commit - the prepare, the actual transaction, and the commit.

  http://www.microsoft.com/sql/productinfo/tpc.htm benchmarks

  To make a database accomodate VLDB, You shouldn't need to partition the database logically, but I would explore adding filegroups and files to make it more manageable. Do this my creating clustered indexes on tables with the ON FILEGROUP option.

  October 1998 Microsoft SQL Server 7.0 Performance Tuning Guide

  Microsoft SQL Server 7.0 Query Processor

  Microsoft SQL Server Administrator's Companion: Chapter 8 - Monitoring Server Performance and Activity

  Microsoft SQL Server Diagnostics: Chapter 9 - SQL Server Tools Troubleshooting

  SQL Server 7.0 Evaluation Exercises from Microsoft TechNet Server Utilities CD file AllExer.exe in the \Sql\Evalex directory.

Certifications for Database Administrators

• Microsoft Certified Database Administrator (MSCDBA) preparation:

  Take the Brainbench certification test on MS SQL Server 2000, 2003, 2008 Administration & Programming and MS SQL Server 7 Administration & Programming

  • ITForum
  • Braindump Heaven
  • Exam 70-228: Installing, Configuring, and Administering Microsoft SQL Server and 2000 Enterprise Edition or Exam 70-028: MS SQL 7.0

    AND

  • Exam 70-029: Designing and Implementing Databases with Microsoft SQL Server 7.0 or Exam 70-229: 2000 Enterprise Edition
  • Obsolete:
    • Exam 70-026: Administering MS SQL 6.5 Braindump
    • Exam 70-027: MS SQL Server 6.5 Implementation Braindump

    AND

  • Exam 70-215: Installing, Configuring, and Administering Microsoft Windows 2000 Server or
  • Exam 70-275: .NET Server (Available 2002)

    AND one Elective:

  • Exam 70-216: Implementing and Administering a Microsoft Windows 2000 Network Infrastructure

• Oracle CPD (Certified Professional Developer)

  Take the Brainbench certification test on Database Development and Administration

• Seagate offers a free download (update?) of Crystal Reports ActiveX control at: http://www.seagatesoftware.com/scrvbasic/download/. This download includes samples and help file.
• About ER (Entity-Relationship) Diagramming: Federal Information Processing Standard (FIPS) Publication 184 "Integration Definition for Information Modeling (IDEF1X)" is the standard for data modeling implemented in major CASE tools.
• Bill Vaughn's Building Great SQL7 Applications with Visual Basic
• Migrating to SQL7 from Access
• Upsizing from Access to SQL
• Professional Association for SQL Server holds a chat the 4th Wednesday of every month 10-11 AM Pacific Time.
• List of consultants with the SQL Server Worldwide Users Group (sswug.org)
• Swynk.com's discussion forum on SQL7 and SQL2000
• Anil Desai
• Microsoft's LINQ (Language Integrated Query)

SQL Server 2008 Express Edition ebook from MS

Administrating SQL 7.0 #70-028 Braindump

Implementing SQL 7.0 #70-029 Braindump

$35 Transact-SQL Programming by Kline, Kevin E.; Gould, Lee.; Zanevsky, Andrew. Sebastopol, Calif. O'Reilly and Associates, 1999 compares Microsoft SQL Server 6.5 and Sybase SQL Server 11.5.

MSDN on Securing SQL 7

This User's Security Guide details how to harden SQL Server 2000 for a C2-evaluated configuration.

2005 Exams are retiring March 2009, so MS has reduced their price 40% from $125 to $75.

MCITP: Database Administrator certification
ISBN 0071546693 SQL Server 2005 Database Administration All-in-One Exam Guide for MCDBA Exams

• 70-431
• 70-443
• 70-444

70-447 Upgrade from 2000 to 2005

Inside SQL Server 2005 books "T-SQL Querying", "T-SQL Programming", and "Query Tuning and Optimization".

Questions

Question: what is the point when the small size of a table makes it more efficient than an indexed search?

Question: You are collecting SQL server Cache Hit Ratio data. What should the value be greater than for a well-tuned SQL server?

a. 10%

b. 25%

c. 50%

d. 65%

e. 80%

f. 95% Correct answer: e