SQL Server: Database types

Two types of databases:

• (a) System databases: Used by SQL Server and SQL Server services.
• Four system databases: master, MSDB, model and tempdb
  contain configuration information about the SQL Server services and user databases.
• (b) User databases: Used to store user data.

System databases

Master database	MSDB database
Primary configuration database. Usually small. Data in: master.mdf Logs in: masterlog.ldf Used by SQL Server service. Systems table in the master database: keep information about users and databases within the instance. Needs to be available (not corrupted) for database startup.	Used by the SQL Server Agent service. Data here include: scheduled jobs, job parameters, alerts, operators. Data in: MSDBData.mdf Logs in: MSDBlog.ldf

USE MASTER;
EXEC sp_helpfile
name    fileid  filename                                               filegroup size    maxsize   growth usage
------  ------  -----------------------------------------------------  --------- ------- --------- ------ --------- 
master  1       C:\Program Files\...\MSSQL10...\MSSQL\DATA\master.mdf  PRIMARY   4096 KB Unlimited 10%    data only
mastlog 2       C:\Program Files\...\MSSQL10...\MSSQL\DATA\mastlog.ldf NULL      1024 KB Unlimited 10%    log only

USE MSDB;
EXEC sp_helpfile
name     fileid  filename                                                filegroup size     maxsize      growth  usage
------   ------  ------------------------------------------------------  --------- -------  ---------    ------- --------- 
MSDBData 1       C:\Program Files\...\MSSQL10...\MSSQL\DATA\MSDBData.mdf PRIMARY   15104 KB Unlimited   10%    data only
MSDBLog  2       C:\Program Files\...\MSSQL10...\MSSQL\DATA\MSDBLog.ldf NULL      5184 KB  2147483648 KB 10%    log only

Model database

Tempdb database

Model for new databases. Properties set in the model database will be automatically configured on new databases.

Used by SQL Server database engine tempdb is recreated every time SQL Server service starts. Therefore, any data in tempdb is LOST when the database shutdown. If you need an object to always exist in tempdb, it HAS to be recreated after instance startup. For this you can use a startup stored procedure, which are automatically executed when the SQL Server service starts up. Users may also create temporary objects in the tempd: i.e. USE tempdb; SELECT * INTO #tmp_orders   FROM InsideTSQL2008.Sales.Orders   WHERE custid = 10; -- copy all data from sales table into a temporary table #temp_table. The # sign directs the server to create a LOCAL temporary table in the tempdbdatabase. Local temporary tables: visible only in the current session. The ## sign directs the server to create a GLOBAL temporary table. Global temporary tables: visible to all sessions.

USE model;
EXEC sp_helpfile

USE tempdb;
EXEC sp_helpfile

name       fileid  filename                                                filegroup size     maxsize    growth  usage
--------   ------  ------------------------------------------------------  --------- -------  ---------  ------- --------- 
modeldev   1  C:\Program Files\...\MSSQL10...\MSSQL\DATA\model.mdf      PRIMARY   1280 KB  Unlimited  1024 KB data only
modellog   2  C:\Program Files\...\MSSQL10...\MSSQL\DATA\modellog.ldf  NULL      512  KB  Unlimited  10%     log only

name       fileid  filename                                                filegroup size     maxsize    growth  usage
--------   ------  ------------------------------------------------------  --------- -------  ---------  ------- --------- 
tempdev    1  C:\Program Files\...\MSSQL10...\MSSQL\DATA\tempdb.mdf     PRIMARY   8192 KB  Unlimited  10%     data only
templog    2  C:\Program Files\...\MSSQL10...\MSSQL\DATA\templog.ldf   NULL      512  KB  Unlimited  10%     log only

The full name of a temporary table as stored in the sysobjects table in tempdb. The full name is: table name specified in the CREATE TABLE statement + a system-generated numeric suffix.

USE tempdb;
  SELECT * FROM sysobjects
  WHERE name LIKE '#%';

If you need to move tempdb (to address performance issues due to disk contention), you can:

USE MASTER;
GO

ALTER DATABASE tempdb modify FILE
  (name = tempdev,
   filename = 'F:\tempdata\tempdb.mdf');
GO
ALTER DATABASE tempdb modify FILE
  (name = templog,
   filename = 'F:\tempdata\templog.ldf');
GO

Exception Handling: Nested Blocks

Question: Can you use the same name for User-Defined Exceptions in nested blocks? A: Yes. But, oracle treat them as DIFFERENT exceptions. If exception e1 is raised and NOT CATCHED in the inner block, it WILL NOT be caught in the outer block EVEN if there is an exception handler with that name.

[ Oracle PL/SQL ]

Case 1: Exception caught in the internal block

set serveroutput on
DECLARE                -- outer block
 e1 exception;
 a integer;
BEGIN
  a :=2; 
  declare              -- inner block
    e1 exception;
    b integer;
  begin
    b := 2;
    raise e1;           -- exception e1 raised.
  exception
    when others then 
       dbms_output.put_line('error1 caught: inner block');
       dbms_output.put_line(sqlerrm);
  end;
  a :=3;
  raise e1;              -- exception e1 raised.
EXCEPTION
 when e1 then 
   dbms_output.put_line('error1 caught: outer block');
END;
/
anonymous block completed
error1 caught: inner block
User-Defined Exception
error1 caught: outer block

Case 2: Exception unhadled in the internal block is NOT caught in the external one

set serveroutput on
DECLARE                         -- outer block
 e1 exception;
 a integer;
BEGIN
  a :=2; 
  declare                -- inner block
    e1 exception;
    b integer;
  begin
    b := 2;
    raise e1;                   -- exception e1 raised
  exception
    when no_data_found then 
       dbms_output.put_line('error1 caught: inner block');
       dbms_output.put_line(sqlerrm);
  end;
  a :=3;
  raise e1;
EXCEPTION
 when e1 then 
   dbms_output.put_line('error1 caught: outer block');
END;
/

Error report:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 11
06510. 00000 -  "PL/SQL: unhandled user-defined exception"
*Cause:    A user-defined exception was raised by PL/SQL code, but
           not handled.
*Action:   Fix the problem causing the exception or write an exception
           handler for this condition. Or you may need to contact your
           application administrator or DBA.

Native Dynamic SQL x DBMS_SQL

[ Oracle PL/SQL ]

With Oracle 11g:

• Native dynamic SQL and DBMS_SQL package now support statments larger than 32Kb.
• EXECUTE IMMEDIATE, OPEN-FOR and DBMS_SQL.PARSE accept SQL statements in the form of CLOBs.

• DBMS_SQL.TO_REFCURSOR converts a DBMS_SQL cursor ID into a REF CURSOR.
• DBMS_SQL.to_cursor_number converts a REF CURSOR into a DBMS_SQL cursor ID.

DBMS_SQL.TO_REFCURSOR: From Cursor ID => to REF CURSOR.

set serveroutput on 
declare 
 lcursor number;                       -- for DBMS_SQL Cursor ID
 lreturn number;                       -- for DBMS_SQL Cursor ID
 
 lref_cursor sys_refcursor;            -- for REF CURSOR
 type        t_emptab is table of employees%rowtype;
 lemp_tab    t_emptab;

begin
  lcursor := dbms_sql.open_cursor;
  dbms_sql.parse(lcursor, 
                'Select * from employees',
                 DBMS_SQL.NATIVE);
  lreturn := dbms_sql.execute(lcursor);
  
                                        -- convert from dbms_sql Cursor ID to a REF CURSOR
  lref_cursor := dbms_sql.to_refcursor(lcursor);
  fetch lref_cursor bulk collect into lemp_tab;
  dbms_output.put_line('Employee count: '||lemp_tab.count);
  close lref_cursor;
end;
/
anonymous block completed
Elapsed: 00:00:00.050
Employee count: 107

DBMS_SQL.to_cursor_number: from REF CURSOR => to DBMS_SQL cursor ID.

declare 
  lref_cursor sys_refcursor;
  lcursor number;
  lcount  number := 0;
begin 
  OPEN lref_cursor FOR 'select * from employees';
                                          -- convert from REF CURSOR to a dbms_sql Cursor ID
  lcursor := dbms_sql.to_cursor_number(lref_cursor);

  while dbms_sql.fetch_rows(lcursor) > 0 LOOP
    lcount := lcount + 1;
  end loop;
  dbms_output.put_line('Employee count: '|| lcount);
  dbms_sql.close_cursor(lcursor);
end;
/
anonymous block completed
Elapsed: 00:00:00.024
Employee count: 107