1 / 50

SQL Server

SQL Server. Glossary. Peter Gfader. Specializes in C# and .NET (Java not anymore) Testing Automated tests Agile, Scrum Certified Scrum Trainer Technology aficionado Silverlight ASP.NET Windows Forms. SQL Server 2008. DBMS ( Database Management System). Manages databases

orien
Download Presentation

SQL Server

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. SQL Server Glossary

  2. Peter Gfader • Specializes in • C# and .NET (Java not anymore) • TestingAutomated tests • Agile, ScrumCertified Scrum Trainer • Technology aficionado • Silverlight • ASP.NET • Windows Forms

  3. SQL Server 2008

  4. DBMS (Database Management System) • Manages databases • Controlling data access • Enforcing data integrity • Managing concurrency

  5. DBMS (Database Management System) • Backup / Restore • Backup • Full • Differential • Transaction log • Recovery model • Simple • Full • Bulk logged

  6. Database • Container for • See picture • Holds tables with rows 

  7. Tables • In relational databases a table is a set of elements with vertical columns and horizontal rows • Central element in a database

  8. Tables

  9. Rows • Also called a record or tuple, represents a single, implicitly structured data item in a table

  10. Columns

  11. Schemas • Logical Container for tables • Good for security and permissions • Logical grouping

  12. Index • Like an index in the back of an encyclopedia • Lookup for whole rows

  13. Non-Clustered Index • A non-clustered index contains the index key values and row locators that point to the storage location of the table data.

  14. Clustered Index • Clustered indexes sort and store the data rows in the table based on their key values. There can only be one clustered index per table.

  15. View • A view is a virtual table based on the result-set of an SQL statement. Example: CREATE VIEW [Current Product List] ASSELECT ProductID,ProductNameFROM ProductsWHERE Discontinued=No SELECT * FROM [Current Product List]

  16. Primary key • Unique key or primary key is a candidate key to uniquely identify each row in a table • Single column or set of columns • No two distinct rows in a table can have the same value(or combination of values) in those columns

  17. Foreign Key • Referential constraint between two tables • Column or a set of columns in one (referencing table) that refers to a column or set of columns in another (referenced) table • Column in referencing must be a primary key or other candidate key in the referenced table

  18. Relationships • There three types of relationships: • One-to-one • One-to-many • Many-to-many

  19. Relationships – One to Many

  20. Transactions • ACID properties • Atomicity (all or none) • Consistency (before and after) • Isolation (others) • Durability (after) • Comprises of unit of work in DBMS, treated in a coherent and reliable way independent of other transactions • Allow for correct recovery from failures and keep a database consistent even in cases of system failure • Provides isolation between programs accessing database concurrently

  21. Language statements • Data Manipulation Language (DML)Examples: SELECT, UPDATE, INSERT statements • Data Definition Language (DDL)Examples: CREATE, ALTER, DROP statements • Data Control Language (DCL)Examples: GRANT, REVOKE statements • Transactional Control Language (TCL)Examples: COMMIT, ROLLBACK statements

  22. SQL Server Compact • Relational Database for applications that run on mobile devices and desktops

  23. SQL Server Filegroups • Provide an opportunity for fine-tuning performance by allowing the movement of specific tables and indexes from one physical drive array to another

  24. Different types of backups • Types of backups • Full or complete • Differential • Transaction log

  25. Different types of backups • Full or Complete • Create stand-alone image of entire database • Restore to same database or create new • Data lost = time of disaster and time of last backup • Storage requirements large • backup size = original database size (if not compressed)

  26. Different types of backups • Differential • Backs up modified data since the last full backup • Data lost = same as with Full backups • Space efficient • Only changes from last full backup

  27. Different types of backups • Transaction Log • Backs up changes since last transaction log backup or last full backup (If no other transaction log backup exists) • The inactive part of the transaction log will also be truncated

  28. Different types of recovery models • Type of recovery models • Simple • Full • Bulk Logged • Backup and restore depend on recovery models

  29. Different types of recovery models • Simple • No transaction log • No transaction log backup available • Restore = full database or differential backup • Recover data = most recent full database or differential backup. • Use when data is not critical and losing all transaction since the last backup is not an issue

  30. Different types of recovery models • Full • Full, differential, or transaction log backups available • Recover data = point of failure or specific point in time • Use when data is critical, point of time recovery is a requirement, and bulk operations are performed throughout the day

  31. Different types of recovery models • Bulk-logged • Bulk-logged = Full, but • The following operations are minimally logged and not fully recoverable: SELECT INTO, bulk-load operations, CREATE INDEX, text and image operations. • Use when data is critical, point of time recovery is a requirement, but backing up bulk operations would bog down the system

  32. Different types of backups and recovery models • Set Recovery Model from database’ properties

  33. Different types of backups and recovery models • Set backup type during backup process • based on the recovery model

  34. SQL Server Services

  35. SSIS (SQL Server Integration Services) • Is a platform for data integration (combining data residing in different sources and providing users with a unified view of these data) and workflow applications(automates, at least to some degree, a process or processes) • Fast and flexible data warehouse tool

  36. SSIS (SQL Server Integration Services) • Extract, Transform, Load (ETL) and integration capabilities, enabling organizations to more easily manage data from a wide array of data sources. • New features SQL 2008: • Creating scripts with C# and VB • New SSIS connectors for SAP BW, Oracle and Teradata

  37. SSAS(SQL Server Integration Services) • Collection of Online Analytical Processing (OLAP) and Data Mining Services supplied in Microsoft SQL Server

  38. SSAS - Cube • Data structure that allows fast analysis of data • Extensions to the two-dimensional array of a spreadsheet • Company might wish to analyse some financial data by product, by time-period, by city, by type of revenue and cost, and by comparing actual data with a budget

  39. SSAS – Cube (Diagram)

  40. Dimension • Used to slice main data • Dimensions organize data with relation to an area of interest • Attribute based on column from table or view • Think of categorization of data • (Customer, Productcategory, Date,...)

  41. Measure • Property in data warehouse to perform calculations (aggregations) • Numbers • Used for aggregation on measure column of Fact table :-)

  42. Fact table • Contains two basic types of columns: • Attribute columns • Foreign key relationships to dimension tables • Measure columns • Define the granularity of a fact table and its measure group

  43. SSAS - KPI(Key Performance Indicator) • Measures are commonly used to help an organization define and evaluate how successful it is, typically in terms of making progress towards its long-term organizational goals

  44. SSAS - OLAP(Online analytical processing) • Approach to quickly answer multi-dimensional analytical queries

  45. SSAS - ROLAP(Relational Online Analytical Processing) • Differs significantly in that it does not require the pre-computation and storage of information • Access the data in a relational database and generate SQL queries to calculate information at the appropriate level when an end user requests it • Creating summary tables or aggregations which summarize the data at any desired combination of dimensions

  46. SSAS - MOLAP (Multidimensional Online Analytical Processing) • It requires the pre-computation and storage of information in the cube - the operation known as processing • This data in an optimized multi-dimensional array storage rather then relational database

  47. SSAS - HOLAP(Hybrid Online Analytical Processing) • Combination of ROLAP and MOLAP which are other possible implementation of OLAP • Allows to store part of the data in the MOLAP store and another part of the data in ROLAP store.

  48. SSAS - Data Mining • Sometimes called data or knowledge discovery • It is the process of analysing data from different perspectives and summarizing it into useful information • This information is useful to increase revenue, cuts costs, or both • Technically, data mining is the process of finding correlations or patterns among dozens of fields in large relational databases

  49. SSRS(SQL Server Reporting Services) • Server-based report generation software system • Prepare and deliver a variety of interactive and printed reports

  50. Thank You! Gateway Court Suite 10 81 - 91 Military Road Neutral Bay, Sydney NSW 2089 AUSTRALIA ABN: 21 069 371 900 Phone: + 61 2 9953 3000 Fax: + 61 2 9953 3105 info@ssw.com.auwww.ssw.com.au

More Related