Overview of SQL Server I

1
Overview of SQL Server I

• Advantages
• Easy to install and maintain
• Lowest price among main frame databases
• Tightly integrated with Windows
• Editions
• Desktop Limit to 2 CPUs and 4GB size
• Standard Limit to 4 CPUs, not limit on size
• Enterprise Support 4 CPUs and 2GB memory, no
  size limit

2
Overview of SQL Server II

• SQL Server Facilities
• Enterprise Manager a GUI for normal database
  management using Microsoft Management Console
  (MMC)
• Query Analyzer Edit and Execute SQL statements
  or stored procedures.
• Data Transformation Services (DTS) Transfer data
  from one place to another SQL Server, Oracle,
  Excel, Text

3
Overview of SQL Server III

• SQL Server Facilities
• OLAP (Online Analytical Processing) A tool for
  data warehouse. Allows to reorganize the data
  from a regular relational DB into a
  multidimensional data store.
• English Query A series of COM components to
  translate an English language question into a SQL
  statement

4
Database Architecture I

• True Client/Server architecture
• System Databases
• Master Information (name, physical file,
  security info) about databases that the server
  manages
• TempDB Holds temporary tables and stored
  procedures, Provides temporary storage for
  intermediate results
• msDB Maintain information about scheduled
  activities (backups, DTS jobs, etc.)
• model Is used as a template whenever SQL Server
  creates a new database
• User Databases
• Data File (.MDF) Actual data
• Log File (.LDF) A series of log records that
  contains information to undo a change to the
  database

5
Database Architecture II

• Data File
• Consists of 8 KB pages 96 bytes for header, 32
  bytes for additional row info, 8060 bytes for
  data
• Page Type
• Data row information
• Index Index values
• Text/Image Large data for Text and Image
• Global Allocation Map Info about allocated
  extents
• Index Allocation Map Info about the extents used
  by a table or index
• Page Free Space Info about free space on pages
• Extent The primary unit of allocation for table
  or index, in 8 page format.

6
Database Architecture III

• Main Database Capacities
• Bytes per record 8060
• Fields per record 1024
• Database size 4GB for Desktop Edition, no limit
  for other two editions
• Records per table limited by the DB size
• Number of Indexes 249

7
Database Architecture IV

• Field Data Type (String)
• Char String value with fixed length, 8000
  characters
• Varchar String value with variable length, 8000
• Nchar Unicode string with fixed length, 4000
  char
• Nvarchar Unicode string with variable length,
  4000
• Text Store large blocks of text data, 231-1
  char
• Ntext Store large blocks of unicode text data,
• 230-1 characters

8
Database Architecture V

• Field Data Type (Numerical)
• Decimal Decimal number with 38 digits of
  accuracy
• Numeric Same as Decimal
• Float 64 bit float point number
• Real 64 bit float point number
• Int 32 bit integer
• SmallInt 16 bit integer (-32,00032,000)
• Tinyint 8 bit integer (0255)
• Money 8 byte integer with 4 digits of accuracy
• SmallMoney 4 byte integer with 4 digits of
  accuracy

9
Database Architecture VI

• Field Data Type (Other)
• Binary Fixed length binary string up to 8000
  bytes
• Image Variable length binary data up to 231 -1
  bytes
• Bit Boolean value
• Datetime Date and time
• SmallDatetime 1 Jan 1900 to 6 Jun 2079, accurate
  to minute
• Sysname Hold the database object name
  (Nchar(128))
• Timestamp Unique identifier for the sequence of
  events
• UniqueIdentifier A GUID string

10
Objects at Server Level

• Databases A collection of databases available on
  the server
• Data Transformation Info about data
  transformation packages and repository
• Management Info about operational activities
  performed by the server
• Security Info about login and sever roles
• Support services Distributed Transaction
  Coordinator (DTC), Full Text Search and SQL Mail

11
Objects at Database Level I

• Tables The tables in current database
• Diagram Table relationship
• Views A collection of views (Recordset created
  by SQL statement)
• Stored Procedures Defined stored procedures
• Users Info about all the users may access the DB

12
Objects at Database Level II

• Roles Description of the security roles
• Rules For backward compatibility with older
  versions. Replaced by CHECK constraints
• Defaults A collection of default values that may
  be referenced.
• User Defined Data Types
• Full Text Catalogs

13
Database Operation

• Get Database Information (Click on the DB)
• Get Database Property (Right click on the DB and
  then select Property)
• New Table, View, Stored Procedure
• All Tasks Import, Export
• Create a new database
• Specify the database name
• Specify the data file(s)

14
Table Operations

• New table
• Design table
• Open table All/Top n records
• Delete table
• Import from external source

15
Index

• Three ways to create index
• Running Create Index Wizard Available at Menu
  ToolsWizardsDatabaseCreate Index Wizard
• Run the Index Tuning Wizard Available at Menu
  ToolsWizardsManagementIndex Tuning Wizard
• Right click the tableDesign TableProperties
  Indexes/Keys
• View Create Indexes
• Right click a tableAll TasksManage Indexes
• Indexes have no Ascending or Descending order.
  They are only used to improve search performance

16
Diagram

• Have an overview of the database
• Build up relationship and apply the integrity
  rules
• Create diagram Right click on Diagram and then
  select New Database Diagram from the popup menu
• Modify a diagram Right click on an existing
  diagram and then select Design Diagram from the
  menu
• Modify a relation Right click on a relation and
  then select Properties from the popup menu

17
T-SQL

• Database languages
• DML (Data Manipulation Language) SELECT, DELETE,
  INSERT, UPDATE
• DDL (Data Definition Language) CREATEALTERDROP
  TABLEVIEWINDEX
• T-SQL (Transit-SQL) is an extended ANSI SQL with
  enhancements on
• DDL (Data Definition Language)
• Variable support (_at_VariableName)
• SQL statement block (Begin End)
• Flow control (If Else , While )

18
T-SQL DDL I

• For T-SQL Help, use Transact-SQL Help in Query
  Analyzer
• CREATE TABLE Table (ClomunDefinition1, )
• ClomunDefinition ColumnName ColumnProperties
• ColumnProperties DataType IDENTITY NOT NULL
  NULL etc
• ALTER TABLE Table
• ALTER COLUMN Column ColumnProperties
• ADD ClomunDefinition
• ADD Column AS computed_column_expression
• DROP Column

19
T-SQL DDL II

• Examples
• create table test (f1 char(10), f2 int)
• alter table test alter column f2 money
• alter table test add f3 int
• alter table test add f4 as f3f2
• alter table test drop column f4
• See more example in c\VB6DB\SampleDB\SQLServer.sq
  l

20
T-SQL Use Variables

• Variables must be declared before use
• Declare _at_Variable Type
• Assign to variables
• SET _at_VariableExpression
• SELECT VariableFieldExpression FROM
• Use variables (as parameters)
• SELECT FROM WHERE fieldX _at_Variable
• System variables Begin with _at__at_

21
T-SQL Use Variables - Example

• declare _at_mS char(2)
• set _at_mS"MN"
• Select From Customers Where State _at_mS
• print _at__at_servername

22
T-SQL Flow Control I

• Block Encloses a series of SQL statements into
  one unit
• BEGIN
• SQL Statements
• END
• Decision Imposes conditions on the execution of
  statements
• IF ltBooleanExpressiongt
• SQL Statement Block 1
• ELSE
• SQL Statement Block 2
• See example in HelpBegin

23
T-SQL Flow Control II

• Loop For the repeated execution of a statement
  block
• WHILE Boolean_expression
• sql_statement statement_block
• BREAK
• sql_statement statement_block
• CONTINUE
• See example in HelpWhile

24
T-SQL Functions

• String functions
• Mathematical functions
• Date functions
• System functions
• print Getdate()
• Note the difference with Jet SQL
• (See Page. 562 and the Excel table)

25
T-SQL Other Statements I

• Comment
• Single line comment --
• Multiple lines comment / /
• RETURN n Used in stored procedure.
• N0 Successful execution
• n-1-99 Errors
• GOTO Go to a line with defined label. Same as in
  VB
• WAITFOR DELAY 'time' TIME 'time
• DELAY Wait until the specified amount of time
  has passed
• TIME Wait until the specified time
• See example in HelpWaitfor

26
T-SQL Other Statements II

• EXECUTE Expression
• Expression is a executable statement
• Normally it is used to run customer stored
  procedure with necessary arguments
• If the batch has more than two stored procedures,
  EXEC must be used
• EXEC("select from sysobjects")

27
T-SQL Other Statements III

• GO Signals the end of a batch of Transact-SQL
  statements. It can let the server to execute the
  statements by batches.
• See example in HelpGo
• USE Database Specifies the default database
• PRINT Expression Print the value in the
  expression, the expression must be in string type

28
T-SQL Other Statements IV

• RAISERROR (msg_id msg_str, severity, state ,
  argumentlist ) Return an user defined error
  message
• msg_id Is a user-defined error message stored in
  the sysmessages table
• msg_str a error message with IDs represented by
  d
• severity 018 for user defined errors
• argumentlist The values for IDs in msg_str
• See example in T_SQL.sql

29
T-SQL Transaction I

• Transaction statements
• BEGIN TRANSACTION TransName
• COMMIT TRANSACTION TransName
• ROLLBACK TRANSACTION TransName SavePoint
• SAVE TRANSACTION SavePoint
• Transaction nest Use _at__at_Trancount to trace the
  nest layer
• BEGIN TRAN _at__at_Trancount increase by 1
• COMMIT TRAN _at__at_Trancount decrease by 1

30
T-SQL Transaction II

• COMMIT is executed within the layer, but changes
  are not posted to the DB until outmost
  transaction is committed.
• ROLLBACK always roll back to the outmost
  transaction, or to a save point inside a
  transaction
• If partial rollback is required, then you need to
  use SAVE TRAN SavePoint
• TransName is for readability. It as little effect
  on the transaction

31
T-SQL Transaction Example I

• BEGIN TRAN --_at__at_Trancount1
• IF EXISTS(SELECT FROM table1 WHERE F1111)
• BEGIN
• RAISERROR(The value already exist,16,-1)
• ROLLBACK TRAN
• END
• ELSE
• BEGIN
• INSERT INTO table1 (f1) VALUES (111)
• COMMIT TRAN
• END

32
T-SQL Transaction Example II

• BEGIN TRAN --_at__at_Trancount1
• DELETE FROM table1
• SAVE TRAN SavePoint1
• BEGIN TRAN --_at__at_Trancount2
• INSERT INTO table2 ...
• COMMIT --_at__at_Trancount1
• COMMIT --_at__at_Trancount0
• ROLLBACK SavePoint1
• Example of using TranName see T-SQL helpCOMMIT

33
T-SQL Cursor I

• Cursor A temporary storage for the record set
  created by SELECT statement. Same as Recordset in
  VB
• Declare
• DECLARE cursor_name CURSOR
• LOCAL GLOBAL
• FORWARD_ONLY SCROLL
• STATIC KEYSET DYNAMIC FAST_FORWARD
• READ_ONLY SCROLL_LOCKS OPTIMISTIC
• TYPE_WARNING
• FOR select_statement
• FOR UPDATE OF column_name ,...n

34
T-SQL Cursor II

• LOCAL GLOBAL Specify the scope of cursor
• FORWARD_ONLY SCROLL Whether the cursor can
  be manipulated backwards
• STATIC KEYSET DYNAMIC FAST_FORWARD
  Visibility to the changes made by other users
• STATIC Not visible to the changes
• KEYSET Only the changes to current selected
  records are visible
• DYNAMIC Visible to all changes, includes new
  inserted records
• FAST_FORWARD Specifies a FORWARD_ONLY, READ_ONLY
  cursor

35
T-SQL Cursor III

• READ_ONLY SCROLL_LOCKS OPTIMISTIC
  Permission to other users
• READ_ONLY Prevents updates to this cursor
• SCROLL_LOCKS The rows are locked when they are
  fetched. To guarantee the success of changes made
  to the records
• OPTIMISTIC Rows are not locked until the changes
  are committed

36
T-SQL Cursor IV

• Open cursor Execute the SELECT statement and put
  records into the cursor
• FETCH NEXT PRIOR FIRST LAST ABSOLUTE n
  _at_nvar
• RELATIVE n _at_nvar
• FROM cursor INTO variablelist
• Retrieve information from a row
• CLOSE cursor Release the results and locks in
  the cursor
• DEALLOCATE cursor Free all resources occupied by
  the cursor.

37
Stored Procedure I

• Advantage
• Performance
• Security
• Convenience
• Create
• CREATE PROCEDURE procedure_name parameterlist
  datatype AS T-SQL statements
• Stored Procedures can be created/modified either
  in Enterprise Manager or Query Analyzer

38
Stored Procedure II

• Modify
• Method 1 Use ALTER PROC command to replace whole
  the statements
• ALTER PROCEDURE procedure_name parameterlist
  datatype AS T-SQL statements
• Method 2 In Enterprise Manager, right click on
  the procedure and then choose Properties
• Delete
• DROP PROCEDURE procedure_name
• Execute
• EXECUTE procedure_name