1 / 152

Programming Microsoft SQL Server

Programming Microsoft SQL Server. David Henson dhenson@certifiednetworks.com www.certifiednetworks.com. Logistics. Hours: 9:00am to 5:00pm Lunch Phones Parking. Course Material. Handouts of SQL scripts “SQL Server 2008 for Developers”, Murach press. Recommended Reading.

edda
Download Presentation

Programming Microsoft 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. Programming Microsoft SQL Server David Henson dhenson@certifiednetworks.com www.certifiednetworks.com

  2. Logistics • Hours: 9:00am to 5:00pm • Lunch • Phones • Parking

  3. Course Material • Handouts of SQL scripts • “SQL Server 2008 for Developers”, Murach press

  4. Recommended Reading • “Professional SQL Server 2000 Programming”, Wrox Press • One of the best technical resources: “Inside SQL Server 2000”, • Microsoft Press

  5. Course Outline • Chapter 1: Configuration/Tools • Chapter 2: T-SQL Language Elements • Chapter 3: Foundation T-SQL Statements • Chapter 4: Joins • Chapter 5: Subqueries and Summary Queries • Chapter 6: Changing Data • Chapter 7: Scripts, Batches and TSQL Extensions

  6. Course Outline • Chapter 8: Views • Chapter 9: Stored Procedures • Chapter 10: User Defined Functions • Chapter 11: Triggers • Chapter 12: Cursors • Chapter 13: Transactions and Locks • Chapter 14: CLR Integration

  7. Chapter 1: Configuration/Tools

  8. Machine Setup • Windows 2003, Standard Installation • SQL Server 2008, Enterprise Edition • Trial at www.microsoft.com/sql • SQLClass Database • Will be built during class • \\instructor\public

  9. Tools - Overview • SQL Management Studio • Query Editor Window • SQLCMD.exe • Books Online • Misc. Tools: • Visual Studio .Net 2008 Older Tools: • Query Analyzer (isqlw.exe)

  10. Microsoft SQL Server Management Studio

  11. Query Editor Window

  12. SQLCMD • C:\SQLCMD –Sserver -E • Osql /? For help, or see books online

  13. Books Online • Best help possible for syntax • www.google.com best help possible for strangeness

  14. Miscellaneous Tools • Profiler • Server Config Tool • Surface Area Configuration Tool • 2005 • Client Config Tool • Visual Studio 2005/ 2008

  15. Chapter 2: T-SQL Language Elements

  16. Statement Types • DDL – Data Definition Language • CREATE TABLE Orders(….) • DCL • GRANT SELECT ON Orders To Public • DML • SELECT Max(OrderDate) From Orders • INSERT Orders VALUES(….) • DELETE Orders WHERE OrderID = 10 • UPDATE Orders SET OrderAmount = 0 WHERE OrderID = 10

  17. T-SQL Elements • Comments • Identifiers • Variables • Datatypes • System Functions • Operators • Expressions • Control-of-flow

  18. Comments • Block Comment /*Multi-line comments here*/ • Double Dash SELECT * FROM Orders -- This is a comment • Edit/Advanced/Comment Out

  19. Identifiers • Objects are identified internally by number • Object Interaction is by name • Standard Identifiers • Orders • Customers • Delimited Identifiers • [Spaces are evil] • [1Person]

  20. Variables • Must Be Declared • Must Start with @ symbol DECLARE @SaleDate datetime SELECT @SaleDate = getdate() PRINT @SaleDate

  21. Datatypes • Numbers Bigint - +/- 2 to the 63rd power 8 bytes Int - +/- 2,147,483,648 4 bytes Smallint - 32,768 to 32,767 2 bytes Tinyint 0 to 255 1 byte Bit 0,1 or null 2 bytes Decimal(precision,scale) Default max precision 38 decimals

  22. Datatypes • Money, Smallmoney • Datetime, Smalldatetime • Getdate() • Char, Varchar • Nchar, nvarchar • Text • Timestamp/rowversion • Uniqueidentifier • Newid()

  23. System Functions • Getdate() • Cast() • Convert() • “Global Variables” • SELECT @@version

  24. Operators • + - * / % • = > < >= <= <> • Concatination + • AND, OR, NOT

  25. Expressions • Symbols and operators that evaluate to a single value • Must be used in the context of another query

  26. Control of flow • Somewhat limited compared to other languages • WHILE • IF ELSE • BEGIN END block • CASE • WAITFOR • CONTINUE/BREAK

  27. Lab 2A: T-SQL Language Elements • In this instructor led lab, students will practice using various T-SQL elements

  28. Chapter 3: Foundation T-SQL Statements

  29. ANSI SQL • History • Purpose • Four Main DML Statements: • SELECT • INSERT • UPDATE • DELETE

  30. SELECT • Retrieve/Filter Rows SELECT <selectlist> FROM <table1, n…> WHERE <conditional expression> ORDER BY <fieldlist> • Over 30 pages of online help!

  31. INSERT • Adds new rows to tables INSERT Customers VALUES( ‘Henson’, ‘Dave’, ‘dhenson@certifiednetworks.com’ )

  32. Update • Modifies existing records

  33. Delete • Wipes out rows forever • Truncate Table Customers

  34. Temp tables • Preceed object name with a # during creation • New in SQL 2000 – table variables

  35. Lab 3A: Foundation T-SQL • In this instructor led lab, we will establish business requirements and build our class database from the ground up.

  36. Chapter 4: Joins

  37. Data Environments • OLTP • Structured/Optimized for transactions • Data is organized in many tables • OLAP • Structured/Optimized for reporting • Large space requirements • Redundant Data

  38. Putting Things Back Together Again • Joins pull together data from two or more tables • Basic Syntax SELECT * FROM Orders o INNER JOIN OrderDetails od ON o.OrderID = od.OrderID

  39. Join Types • INNER JOIN • LEFT OUTER JOIN • RIGHT OUTER JOIN • FULL JOIN • CROSS JOIN

  40. Inner Join • Exclusive nature of inner join • Only rows that match in both input tables are returned

  41. Outer Join • All rows from “left” or “right are returned • Any matching rows from the other side are returned • Business questions: • “Show me all customers, and their purchases for the month” • “Show me all products, and their profit last week”

  42. Full Join • All data from both inputs are returned • Null is returned in cells where there is no intersection

  43. Cross Join • Cartesian product of two inputs • Produces all intersections • Can produce mass amounts of data…good for populating databases

  44. Joining multiple tables • Every join is between two “input” tables • One table could be an “intermediate result” of another join • Many errors(and hours of frustration) come from improper mixing of inner and outer joins

  45. Self Joins • Join a table to itself for recursive lookups • Must use table aliases • Example: • Employees table contains ManagerID field, which must be a valid EmployeeID

  46. Chapter 5:Subqueries and Summary Queries

  47. Grouping Summarizing • GROUP BY clause is often used for reporting, in conjunction with joins • Basic Structure: SELECT <item>, <aggregate> FROM <tablelist> <join sets> GROUP BY <item>

  48. Having Clause • Having is the where clause applied to the aggregated data SELECT ProductID, Sum(SaleAmount) FROM Sales GROUP BY ProductID HAVING Sum(SaleAmount) > 1000

  49. Aggregate functions • Count(), Count(*) • Sum() • Min() • Max() • Avg() • Ceiling() • Floor() • Other statistical aggregates…

  50. Unions • Joins two complete result sets together • Field number and types must match • Good for adding summary lines to reports and pulling data together from multiple servers

More Related