1 / 20

SQL Query 101

SQL Query 101. Nick Haman. Agenda. sysobjects SQL Table naming structures Index information Table selects and joins Quick table backups/restores Views Indexed Views. Agenda. Performance Tuning query Helpful queries. sysobjects. Stores all objects in database name

bairn
Download Presentation

SQL Query 101

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 Query 101 Nick Haman

  2. Agenda • sysobjects • SQL Table naming structures • Index information • Table selects and joins • Quick table backups/restores • Views • Indexed Views

  3. Agenda • Performance Tuning query • Helpful queries

  4. sysobjects • Stores all objects in database • name • sys... are all system related objects • sp_. or xp_. are extended stored procedures • sp_ - Internal SQL defined procedures • xp_ - created and loaded as DLL’s • Ex: sp_helptext; sp_helpdb

  5. sysobjects • xtype • S = system; U = User Table; PK = Primary Key; V = View; P = Procedure • Following url shows all sysobject columns • http://msdn.microsoft.com/en-us/library/ms177596.aspx

  6. SQL Table naming structures • Naming Convention • CompanyName + $ + Table Name • . (periods) Replaced with _ (underscores) • EX: CRONUS USA, Inc_$Customer • Due to spaces in names of tables, use [ ] around table names in SQL Queries

  7. Index Information • Clustered Index • 1 allowed per table • Rearranges data in table • Frequently searched columns by ranges • Use on columns not updated frequently • Always the first defined index in NAV

  8. Index Information • Non-Clustered Index • 249 allowed per table • Creates separate list of key values with pointers to the location of data • Used for columns searched for individual values • All indexes defined in NAV after the first one • All Non-Clustered add Clustered to end

  9. Index Information • Index Types • Composite • Index contains more then 1 column not exceeding 16 • 900-byte limit • Unique (can be Composite) • Value must be unique across the column(s) included for each record in table

  10. Index Information • Index Types (Continued) • Filtered Index (New on SQL 2008) • Indexes a portion of a table • Reduces storage • Uses WHERE clause to create • Not used by default on NAV

  11. Demo Table Selects and Joins

  12. Quick Table Backups • Syntax to backup a table quickly • select * into [CRONUS USA, Inc_$CustomerBackup] from [CRONUS USA, Inc_$Customer] • Syntax to restore the data back • Insert [CRONUS USA, Inc_$CustomerBackup] ( <column names>) select <column names> from [CRONUS USA, Inc_$CustomerBackup] double click the following text box and copy to SQL Query Query builds the script to restore from a table

  13. Views • View • Virtual table that consists of columns from one or more tables • No data stored, data is derived from base tables • Serves as security mechanism • Simplifies query execution • *None created by default for NAV

  14. Demo Views

  15. Indexed Views (VSIFT) • Introduced in SQL 2005 • Introduced with NAV 5.0 SP1 • Replaced SIFT Tables used previous versions • Aggregates are precomputed and stored in index

  16. Stored Procedures • Grouping of SQL statements • Precompiled execution – reuses execution plan • Security controls independent of tables • Accepts parameters • Can return data • None created by default for NAV

  17. Demo Stored Procedures

  18. Query Tuning • Tools • Query Analyzer • Display Estimated Execution Plan • Include Actual Execution Plan • Include Client Statistics • SQL Server Profiler • Trace performance information (see help file for settings) • URL is article on performance tuning http://msdn.microsoft.com/en-us/library/aa178417(v=SQL.80).aspx

  19. Helpful Queries • sp_helpindex tablename (shows index information for table) • sp_helpdb(shows all databases and size/compatibility information) • select @@version (shows SQL Server version) • sp_who2(shows login/commands/status/blocking information) • select * from sysobjects (shows all objects in database) • sp_helpsort (shows collation settings) • sp_spaceused (shows database size and breakdown of size usage)

  20. Thank You

More Related