Sql query 101
This presentation is the property of its rightful owner.
Sponsored Links
1 / 20

SQL Query 101 PowerPoint PPT Presentation


  • 60 Views
  • Uploaded on
  • Presentation posted in: General

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

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Sql query 101

SQL Query 101

Nick Haman


Agenda

Agenda

  • sysobjects

  • SQL Table naming structures

  • Index information

  • Table selects and joins

  • Quick table backups/restores

  • Views

  • Indexed Views


Agenda1

Agenda

  • Performance Tuning query

  • Helpful queries


Sysobjects

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


Sysobjects1

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


Sql table naming structures

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


Index information

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


Index information1

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


Index information2

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


Index information3

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


Sql query 101

Demo

Table Selects and Joins


Quick table backups

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


Views

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


Sql query 101

Demo

Views


Indexed views vsift

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


Stored procedures

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


Sql query 101

Demo

Stored Procedures


Query tuning

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


Helpful queries

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)


Thank you

Thank You


  • Login