1 / 38

Overview of SQL Server I

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.

raina
Télécharger la présentation

Overview of SQL Server I

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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, 2^31-1 char • Ntext: Store large blocks of unicode text data, • 2^30-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,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

  9. 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

  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 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

  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): 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 …)

  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 f3*f2 alter table test drop column f4 • See more example in c:\VB6DB\SampleDB\SQLServer.sql

  20. 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 @@

  21. T-SQL: Use Variables - Example • declare @mS char(2) • set @mS="MN" • Select * From Customers Where State = @mS • print @@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 <BooleanExpression> SQL Statement Block 1 ELSE SQL Statement Block 2 • See example in Help|Begin

  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 Help|While

  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. • 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

  26. 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")

  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 Help|Go • 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: 0~18 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 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

  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 --@@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

  32. 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

  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 | @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.

  37. 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

  38. 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

More Related