SlideServe
Télécharger Obtenir Pro
Menu
Accueil Pro
Préférences
ApparenceLight Aide et commentaires
© SlideServe · v2026

SQL Query 101

bairn avatar
bairn
20 diapositives · 384 Views · Jul 29, 2014

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

SQL Query 101 - slide 1
01 / 20SQL Query 101
1 / 17

Télécharger la présentation

SQL Query 101
Une image / un lien ci-dessous est fourni (tel quel) pour télécharger la présentation

Politique de téléchargement : le contenu du site vous est fourni TEL QUEL pour votre information et votre usage personnel ; il ne peut être vendu, concédé sous licence ni partagé sur d’autres sites sans l’accord de son auteur.Le contenu vous est fourni TEL QUEL, pour votre information et votre usage personnel uniquement. Téléchargez la présentation en cliquant sur ce lien. Lors du téléchargement, si vous ne parvenez pas à télécharger une présentation, l’éditeur a peut-être supprimé le fichier de son serveur.Pendant le téléchargement, si vous ne parvenez pas à obtenir une présentation, le fichier a peut-être été supprimé par l’éditeur.

F I N

Plus de contenu associé

Transcription de la présentation

  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