380 likes | 576 Views
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.
E N D
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
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
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
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
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.
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
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, 2^31-1 char • Ntext: Store large blocks of unicode text data, • 2^30-1 characters
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,000~32,000) • Tinyint: 8 bit integer (0~255) • Money: 8 byte integer with 4 digits of accuracy • SmallMoney: 4 byte integer with 4 digits of accuracy
Database Architecture VI • Field Data Type (Other) • Binary: Fixed length binary string up to 8000 bytes • Image: Variable length binary data up to 2^31 -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
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
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
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
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)
Table Operations • New table • Design table • Open table: All/Top n records • Delete table • Import from external source
Index • Three ways to create index • Running Create Index Wizard: Available at Menu Tools|Wizards|Database|Create Index Wizard • Run the Index Tuning Wizard: Available at Menu Tools|Wizards|Management|Index Tuning Wizard • Right click the table|Design Table|Properties| |Indexes/Keys • View Create Indexes • Right click a table|All Tasks|Manage Indexes • Indexes have no Ascending or Descending order. They are only used to improve search performance
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
T-SQL • Database languages: • DML (Data Manipulation Language): SELECT, DELETE, INSERT, UPDATE • DDL (Data Definition Language): CREATE|ALTER|DROP TABLE|VIEW|INDEX • T-SQL (Transit-SQL) is an extended ANSI SQL with enhancements on • DDL (Data Definition Language) • Variable support (@VariableName) • SQL statement block (Begin … End) • Flow control (If … Else …, While …)
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
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 f3*f2 alter table test drop column f4 • See more example in c:\VB6DB\SampleDB\SQLServer.sql
T-SQL: Use Variables • Variables must be declared before use • Declare @Variable Type • Assign to variables • SET @Variable=Expression • SELECT Variable=FieldExpression FROM … • Use variables (as parameters) • SELECT … FROM … WHERE fieldX= @Variable • System variables: Begin with @@
T-SQL: Use Variables - Example • declare @mS char(2) • set @mS="MN" • Select * From Customers Where State = @mS • print @@servername
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 <BooleanExpression> SQL Statement Block 1 ELSE SQL Statement Block 2 • See example in Help|Begin
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 Help|While
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)
T-SQL: Other Statements I • Comment • Single line comment: -- • Multiple lines comment: /* … */ • RETURN n: Used in stored procedure. • N=0: 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 Help|Waitfor
T-SQL: Other Statements II • EXEC[UTE] 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")
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 Help|Go • USE Database: Specifies the default database • PRINT Expression: Print the value in the expression, the expression must be in string type
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: 0~18 for user defined errors • argumentlist: The values for IDs in msg_str • See example in T_SQL.sql
T-SQL: Transaction I • Transaction statements • BEGIN TRAN[SACTION] [TransName] • COMMIT [TRAN[SACTION]] [TransName] • ROLLBACK TRAN[SACTION] [TransName| SavePoint] • SAVE TRAN[SACTION] [SavePoint] • Transaction nest: Use @@Trancount to trace the nest layer • BEGIN TRAN: @@Trancount increase by 1 • COMMIT TRAN: @@Trancount decrease by 1
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
T-SQL: Transaction Example I BEGIN TRAN --@@Trancount=1 IF EXISTS(SELECT * FROM table1 WHERE F1=‘111’) BEGIN RAISERROR(‘The value already exist’,16,-1) ROLLBACK TRAN END ELSE BEGIN INSERT INTO table1 (f1) VALUES (‘111’) COMMIT TRAN END
T-SQL: Transaction Example II BEGIN TRAN --@@Trancount=1 DELETE FROM table1 … SAVE TRAN SavePoint1 BEGIN TRAN --@@Trancount=2 INSERT INTO table2 ... COMMIT --@@Trancount=1 COMMIT --@@Trancount=0 ROLLBACK SavePoint1 Example of using TranName: see T-SQL help|COMMIT
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]]]
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
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
T-SQL: Cursor IV • Open cursor: Execute the SELECT statement and put records into the cursor • FETCH [NEXT | PRIOR | FIRST | LAST |ABSOLUTE n | @nvar | RELATIVE n | @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.
Stored Procedure I • Advantage • Performance • Security • Convenience • Create • CREATE PROC[EDURE] procedure_name parameterlist datatype AS T-SQL statements • Stored Procedures can be created/modified either in Enterprise Manager or Query Analyzer
Stored Procedure II • Modify • Method 1: Use ALTER PROC command to replace whole the statements • ALTER PROC[EDURE] 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