Upload File

sql server 2005 – dynamic management views & functions

  • Home
  • Documents
  • SQL Server 2005 – Dynamic Management Views & Functions
16
SQL Server 2005 – Dynamic Management Views & Functions Whitney Weaver Senior Consultant [email protected] om

Upload: stasia

Post on 25-Feb-2016

19 views

Category:

Documents


2 download

Report

Tags:

DESCRIPTION

SQL Server 2005 – Dynamic Management Views & Functions. Whitney Weaver Senior Consultant [email protected]. Discussion Overview. History Architecture Scope Permissions Organization Demo Questions. History. - PowerPoint PPT Presentation

TRANSCRIPT

Page 1: SQL Server 2005 – Dynamic Management Views & Functions

SQL Server 2005 – Dynamic Management Views & FunctionsWhitney WeaverSenior [email protected]

Page 2: SQL Server 2005 – Dynamic Management Views & Functions

Discussion Overview

• History• Architecture• Scope • Permissions• Organization• Demo• Questions

Page 3: SQL Server 2005 – Dynamic Management Views & Functions

History

• Added to SQL Server 2005 based on customer demand for greater visibility into their instance

• A few dozen people in virtual team put DMVs together

• Will continue to be driven by customer need– Roughly 30 new DMVs in SQL 2008

Page 4: SQL Server 2005 – Dynamic Management Views & Functions

Discussion Overview

• History• Architecture• Scope • Permissions• Organization• Demo• Questions

Page 5: SQL Server 2005 – Dynamic Management Views & Functions

Architecture

• Dynamic metadata made available by SQL 2005 SQLOS

• Resource database (mssqlsystemresource)

• Views and Functions

• Copy of DMV objects in each user database

Page 6: SQL Server 2005 – Dynamic Management Views & Functions

Discussion Overview

• History• Architecture• Scope • Permissions• Organization• Demo• Questions

Page 7: SQL Server 2005 – Dynamic Management Views & Functions

Scope

• Two scopes for DMVs– Server– Database

• Sys schema (sys.dm_*)

• Data cleared on server restart

• Some DMVs can be reset without restart

Page 8: SQL Server 2005 – Dynamic Management Views & Functions

Discussion Overview

• History• Architecture• Scope • Permissions• Organization• Demo• Questions

Page 9: SQL Server 2005 – Dynamic Management Views & Functions

Permissions

• SELECT permission on objects

• VIEW SERVER STATE

• VIEW DATABASE STATE

Page 10: SQL Server 2005 – Dynamic Management Views & Functions

Discussion Overview

• History• Architecture• Scope • Permissions• Organization• Demo• Questions

Page 11: SQL Server 2005 – Dynamic Management Views & Functions

OrganizationCommon Language Runtime I/O

Database Mirroring Query Notifications

Database Replication

Execution Service Broker

Full-Text Search SQL Server OS

Index Transaction

Page 12: SQL Server 2005 – Dynamic Management Views & Functions

Discussion Overview

• History• Architecture• Scope • Permissions• Organization• Demo• Questions

Page 13: SQL Server 2005 – Dynamic Management Views & Functions

Demo

• Show me the code!

Page 14: SQL Server 2005 – Dynamic Management Views & Functions

Resources

• Blogshttp://www.sqlskills.com/blogs/conor/http://sqlblog.com/blogs/louis_davidson/

• BOLhttp://technet.microsoft.com/en-us/library/ms188754.aspx

• Paul MestemakerProgram Manager, Microsoft SQL Server

Page 15: SQL Server 2005 – Dynamic Management Views & Functions

Questions

• ??

Page 16: SQL Server 2005 – Dynamic Management Views & Functions

Contact Information

• My [email protected]

• My bloghttp://blog.magenic.com/blogs/whitneyw/default.aspx


Sql server ___________session_16(views)

SQL Views for Dummies - LISUGlisug.org/presentations/SQL_Views_for_Dummies.pdf · SQL Views for Dummies Scott Forstie ... QSYS2.NETSTAT_ROUTE_INFO

SQL Routines – Procedures, Functions (and Triggers) · PDF fileSQL Routines – Procedures, Functions (and Triggers) ... (PL/SQL, T-SQL, etc) • First ... NO SQL – No SQL allowed

introdution to SQL and SQL functions

3.1 Chapter 3: SQL. 3.2 Chapter 3: SQL Basic Query Structure Set Operations Aggregate Functions Null Values Nested Subqueries Complex Queries Views Modification

SQL Cockpit - Cadaxo GmbH€¦ · SQL Cockpit – Releasenotes 3.0 New functions Support of SQL Expressions and CDS Views Support of Code Completion in the SQL Cockpit Editor Generic

SQL FUNCTIONS - Junagadhngivbt.edu.in/wp-content/uploads/2016/08/SQL-Functions.pdf · SQL Function Sub program of SQL Lang. Used to do Operation on the SQL Expression. SQL Functions

Introduction to SQL Programming Techniques & Constraints and Views in SQL

SQL Views Chapter 3A. Appendix Objectives Learn basic SQL statements for creating views Learn basic SQL statements for using views Understand the reasons

SQL Server 2005 – Dynamic Management Views & Functions Whitney Weaver Senior Consultant [email protected]

SQL Grouping Functions

SQL STORED ROUTINESusers.cms.caltech.edu/~donnie/cs121/CS121Lec09.pdf · SQL Functions ¨SQL queries can use sophisticated math operations and functions ¤Can compute simple functions,

Advanced SQL functions

Views Para SQL Server

Unit 10. Database Testing...triggers and logical views, which are going to support database refactoring. It performs module testing of database functions, triggers, views, SQL queries

Chapter 4: SQL - UvAChapter 4: SQL Basic Structure Set Operations Aggregate Functions Null Values Nested Subqueries Derived Relations Views Modification of the Database Joined Relations

8 SQL Functions

SQL: Triggers, Views, Indexes

Views (and SQL) - Rutgers Universityszhou/336/views-triggers.pdf · Views (and SQL) ©Borgida/Ramakrishnan & Gehrke 2003 2 Levels of Abstraction • Many views, single conceptual

Analytical SQL Functions 1

My sql udf,views

8.4 SQL Functions

SQL Updates Views

1 Intro to JOINs SQL INNER JOIN SQL OUTER JOIN SQL FULL JOIN SQL CROSS JOIN Intro to VIEWs Simple VIEWs Considerations about VIEWs VIEWs as filters ALTER

Advanced SQL and Functions - joeconway.com. SQL - Basics Adv. SQL ... JSONB and SP-GIST Functions - Overview Function Basics Functions - By Example Advanced SQL and Functions ... Simple

8. More SQL features: Views, PL/SQL, Functions,Triggers … · More SQL features: Views, PL/SQL, Functions,Triggers 8.1 Views and view updates 8.2 Application architectures ... •

SQL Functions. SQL functions are built into Oracle Database and are available for use in various appropriate SQL statements. These functions are use full

Advanced SQL and Functions - joeconway.com · Adv. SQL - Basics Adv. SQL - Window Functions, CTEs, LATERAL JSONB and SP-GIST Functions - Overview Function Basics Functions - By Example

TSQL Functions (SQL Server)

Keys, SQL, and Views - UMass Amherstavid.cs.umass.edu/courses/645/s2010/lectures/03-KeysSQL.pdfKeys, SQL, and Views CMPSCI 645. SQL Overview • SQL Preliminaries • Integrity constraints

ORACLE SQL Materialized Views

Advance SQL: SQL Performance Tuning SQL Views · 12/16/1996 · Advance SQL: SQL Performance Tuning SQL Views ... If you have the Northwind database you can see that it has several

SQL Functions With Eg

Lecture 5: SQL Schema & Views

Advanced SQL and Functions - joeconway.com · Adv. SQL - Basics Adv. SQL ... JSONB and SP-GIST Functions - Overview Function Basics Functions - By Example Advanced SQL and Functions