1 / 40

School of Software SUN YAT-SEN UNIVERSITY Mar, 27, 2011

Overview of Sql server. School of Software SUN YAT-SEN UNIVERSITY Mar, 27, 2011. aGENDA. The Procedure of Installing SQL Server 2005 Microsoft SQL Server 2005 Express Microsoft SQL Server Management Studio Express Introduction of TPC-H and Generate lineitem.tbl

quincy-joseph
Download Presentation

School of Software SUN YAT-SEN UNIVERSITY Mar, 27, 2011

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 School of Software SUN YAT-SEN UNIVERSITY Mar, 27, 2011

  2. aGENDA • The Procedure of Installing SQL Server 2005 • Microsoft SQL Server 2005 Express • Microsoft SQL Server Management Studio Express • Introduction of TPC-H and Generate lineitem.tbl • Import Lineitem.tbl into SQL Server • Experiment about the Efficiency between Croup By and Group By With Cube

  3. Install sql server • Configuration demands

  4. Install sql server • Install procedure for SQL server 2005 Express

  5. Install sql server • Install procedure for SQL server 2005 Express

  6. Install sql server • Install procedure for SQL server 2005 Express

  7. Install sql server • Install procedure for SQL server 2005 Express

  8. Install sql server • Install procedure for SQL server 2005 Express • This situation only for installing VS2005 already

  9. Install sql server • Install procedure for SQL server 2005 Express

  10. Install sql server • Install procedure for SQL server 2005 Express

  11. sql server • Connect to SQL Server

  12. sql server • Connect to SQL Server

  13. sql server • The interface of SQL Server

  14. sql server • The interface of SQL Server

  15. Introduction to TPC-h • The TPC Benchmark™H (TPC-H) is a decision support benchmark. • The components of the TPC-H database are defined to consist of eight separate and individual tables.

  16. Introduction to TPC-h

  17. Introduction to TPC-h • Get the tpch_2_14_0 • The DBGEN program can be downloaded at the • following URL: • http://www.tpc.org/tpch/spec/tpch_2_14_0.zip • The schema of LINEITEM can be found at page 12 • in the tpch2.14.0.doc, which can be downloaded at the • following URL: • http://www.tpc.org/tpch/spec/tpch2.14.0_cb.doc

  18. Introduction to TPC-h • Create lineitem.tbl (Linux)

  19. Import lineitem into db • Create a new query

  20. Import lineitem into db • Create database dbTPC

  21. Import lineitme into db • Use graphical interfaces

  22. Import lineitme into db • Use graphical interfaces

  23. Import lineitme into db • Create the table use SQL use dbTPC create table lineitem ( orderkey int, partkey int, suppkey int, linenumber int, quantity int, extendedprice decimal, discount decimal, tax decimal, returnflag nchar(1), linestatus nchar(1), shipdate datetime, commitdate datetime, receiptdate datetime, shipinstruct nchar(25), shipmode nchar(10), comment varchar(44) )

  24. Import lineitme into db • Create the table use interface

  25. Import lineitme into db • Step 1 Import file into SQL Server Using Bulk Insert. • BULKINSERT Tablename • FROM 'D: \lineitem.tbl' • WITH • ( • FIELDTERMINATOR = '|', • ROWTERMINATOR = '|\r' • )

  26. experiment • When GROUP BY and Aggregate Functions are used together, • the practical meaning is significant. • The Aggregate Functions generate a value for each group when • used together with GROUP BY, other than for the whole table.

  27. experiment • Example: Display the how many lineitems are at each • returning status. • SQL: • SELECT returnflag, COUNT(*) • FROM lineitem • GROUP BY returnflag

  28. experiment • Example: Display the quantity of lineitems which come from • the same order and at the same returning status. • order and they. • SQL: • SELECT returnflag, orderkey, SUM(quantity) • FROM lineitem • GROUP BY returnflag, orderkey

  29. Experiment • The CUBE operator generates a result set that is a • multidimensional cube. • A multidimensional cube is an expansion of fact data, • The expansion is based on columns that the user wants to analyze • The cube is a result set that contains all the possible combinations • of the dimensions.

  30. Experiment SELECT Item, Color, SUM(Quantity) AS QtySum FROM Inventory GROUP BY Item, Color WITH CUBE

  31. Experiment SELECT Item, Color, SUM(Quantity) AS QtySum FROM Inventory GROUP BY Item, Color WITH CUBE

  32. Experiment • These four rows report the the original sum, in another words • this time we get four groups with their sum value. SELECT Item, Color, SUM(Quantity) AS QtySum FROM Inventory GROUP BY Item,Color

  33. Experiment • These two rows report the subtotals for the Item dimension. both have null in the Color dimension to show that aggregate date came from rows having any value for the Color dimension. SELECT Item, SUM(Quantity) AS QtySum FROM Inventory GROUP BY Item

  34. Experiment • These two rows report the subtotals for the Color dimension. both have null in the Item dimension to show that aggregate date came from rows having any value for the item dimension. SELECT Color, SUM(Quantity) AS QtySum FROM Inventory GROUP BY Color

  35. Experiment • This row reports the grand total for the cube. All values of both • dimensions are summarized in the row. SELECT SUM(Quantity) AS QtySum FROM Inventory • Then we can extend this situation to n dimensions. • 2ndifferent combinations of the dimensions should be considered.

  36. experiment • Analysis the column orderkey, partkey, suppkey, linenumber of Table LineItem using WITH CUBE.

  37. experiment • Using 16 GROUP BY clauses simulate the result set of GROUP BY WITH CUBE.

  38. experiment

  39. homework Use the DBGEN program of the TPC-H Benchmark to generate all the eight tables of the TPC-H schema, with the Scale Factor set to 1. B. Create a database with eight tables including possible constrains(You can refer to tpch2.14.0.doc), and then import the generated data. Submit all the nine queries and the time cost for importing data.

  40. THANK YOU!

More Related