There are several basic ways of interfacing with SQL Server.

  • Manager
  • Embedded SQL
  • CLI (Call Level Interfaces)
  • Object Interfaces
  • Shortcuts

Manager

Microsoft provides some sort of visual app to manage many of its server products. This Windows Explorer style interface is quite intuitive and provides multiple utilities and wizards.

Here are the rough structures of the "folder list" in two older versions of SQL Server.

SQL Server 7.0 SQL Server 2000 (8.0) with Enterprise Manager SQL Server 2005 (9.0) with Management Studio
  • SQL Server Groups
    • Servers
      • Databases
        • Diagrams
        • Tables
        • Views
        • Stored Procedures
        • Users
        • Roles
        • Rules
        • Defaults
        • User Defined Data Types
      • Data Transformation Services
        • Local Packages
        • Repository Packages
        • Metadata
      • Management
        • SQL Server Agent
        • Backup
        • Current Activity
        • Database Maintenance Plans
        • SQL Server Logs
        • Web Publishing
      • Security
        • Logins
        • Server Roles
        • Linked Servers
        • Remote Servers
      • Support Services
        • Distributed Transaction Coordinator
        • SQL Mail
  • ServerType
    • SQLServerGroup
      • Server
        • Databases
          • Database
            • Diagrams
            • Tables
            • Views
            • Stored Procedures
            • Users
            • Roles
            • Rules
            • Defaults
            • User Defined Data Types
            • User Defined Functions
            • Full-Text Catalogs
        • Data Transformation Services
          • Local Packages
          • Meta Data Services Packages
          • Meta Data
        • Management
          • SQL Server Agent
            • Alerts
            • Operators
            • Jobs
          • Backup
          • Current Activity
            • Process Info
            • Locks / Process ID
            • Locks / Object
          • Database Maintenance Plans
          • SQL Server Logs
        • Replication
          • Publications
          • Subscriptions
        • Replication Monitor
          • Publishers
          • Agents
            • Snapshot Agents
            • Log Reader Agents
            • Queue Reader Agents
            • Distribution Agents
            • Merge Agents
            • Miscellaneous Agents
          • Replication Alerts
        • Security
          • Logins
          • Server Roles
          • Linked Servers
          • Remote Servers
        • Support Services
          • Distributed Transaction Coordinator
          • Full-Text Search
          • SQL Mail
        • Meta Data Services
          • Contents
            • Microsoft Data Warehousing Framework
              • Microsoft Analysis Services Schema
  • Server
    • Databases
      • System Databases
        • master
        • model
        • msdb
        • tempdb
      • Database Snapshots
      • Database
        • Database Diagrams
        • Tables
          • System Tables
          • Table
            • Columns
            • Keys
            • Constraints
            • Triggers
            • Indexes
            • Statistics
        • Views
          • System Views
          • View
        • Synonyms
        • Programmability
          • Stored Procedures
            • System Stored Procedures
            • Stored Procedures
              • Parameters
          • Functions
            • Table-valued Functions
              • Function
                • Parameters
            • Scalar-valued Functions
            • Aggregate Functions
            • System Functions
          • Database Triggers
          • Assemblies
          • Types
            • System Data Types
              • Exact Numerics
              • Approximate Numerics
              • Date and Time
              • Character Strings
              • Unicode Character Strings
              • Binary Strings
              • Other Data Types
            • User-defined Data Types
            • User-defined Types
            • XML Schema Collections
          • Rules
          • Defaults
        • Service Broker
          • Message Types
          • Contracts
          • Queues
          • Services
          • Routs
          • Remote Service Binding
        • Storage
          • Full Text Catalogs
          • Partition Schemes
          • Partition Functions
        • Security
          • Users
          • Roles
            • Database Roles
            • Application Roles
          • Schemas
          • Asymmetric Keys
          • Certificates
          • Symmetric Keys
    • Security
      • Logins
      • Server Roles
      • Credentials
    • Server Objects
      • Backup Devices
      • Endpoints
        • Database Mirroring
        • Service Broker
        • SOAP
        • TSQL
      • Linked Servers
        • Providers
      • Triggers
    • Replication
      • Local Publications
      • Local Subsriptions
    • Management
      • Maintenance Plans
      • SQL SErver Logs
      • Activity Monitor
      • Database Mail
      • Distributed Transaction Coordinator
      • Full-Text Search
      • Legacy
        • Database Maintenance Plans
        • Data Transformation Services
        • SQL Mail
    • Notification Services
    • SQL Server Agent
      • Jobs
      • Job Activity Monitor
      • Alerts
      • Operators
      • Proxies
        • ActiveX Script
        • Operating System (CmdExec)
        • Replication Distributor
        • Replication Queue Reader
        • Replication Snapshot
        • Replication Transaction-Log Reader
        • Analysis Services Command
        • Analysis Services Query
        • SSIS Package Execution
        • UnassignedProxies
      • Error Logs

There are additional ways to manage and interface with SQL Server.

  • The most common and most powerful way to interact with SQL Server is by using plain old SQL (or T-SQL)
    • In SQL Server 2000, the SQL Server Query Analyzer is a great for entering SQL directly.
    • In SQL Server 2005, queries can be done right in Management Studio.
  • Here are other interfaces for SQL Server 2000
    • From time to time use SQL Server's SQL Server Profiler to check data usage.
    • From time to time use NT's Performance Monitor to check SQL Server performance via traces, etc. Performance Monitor is  conveniently accessed through SQL Server Profiler as well. You may have to use SQL Server Client Configuration Utility first.
  • Here are other interfaces for SQL Server 2005
    • Configuration Tools
      • Notification Services Command Prompt
      • Reporting Services Configuration
      • SQL Server Configuration Manager
      • SQL Server Error and Usage Reporting
      • SQL Server Surface Area Configuration. SQL Server starts out secure and you have to poke holes into the "surface" area, install stuff, activate stuff, open stuff, etc. as needed.
    • Performance Tools
      • Database Engine Tuning Advisor
      • SQL Server Profiler
    • Not quite an interface but something new in SQL Server 2005 is that nearly every resource has extended properties, custom name & value pairs that are very flexible.

Embedded SQL

An ANSI standard interface by which SQL statements are incorporated into source code. The code is framed by EXEC SQL tags. Here are some of the languages that can use Embedded SQL:

  • Oracle
  • DB2
  • COBOL
  • c

CLI (Call Level Interfaces)

There are two CLIs that SQL Server supports:

  • ODBC (Open DataBase Connectivity)
  • DB Library

Both are very powerful but the former is easier to learn, more applicable industry-wide, and utilizes some of SQL Server's features more automatically. CLI is only practical for situations where a company already has a large body of legacy DB Library applications.

Object Interfaces

This is the modern way to go. Object interfaces use a model of objects, complete with methods and properties, to access SQL Server. This includes:

  • SQL-DMO (Distributed Management Objects). This is an object interface specifically for performing SQL Server administrative tasks. The SQL Enterprise Manager itself is layered on top of SQL-DMO. This can be used to programmatically administer SQL Server.
  • DAO (Data Access Objects) over ODBC. An older object interface closely tied to JET (Joint Engine Technology).
  • RDO (Remote Data Objects) over ODBC. An older object interface closely tied to ODBC.
  • ADO (ActiveX Data Objects) over OLE DB (Object Linking and Embedding DataBase). An object interface closely tied to OLE DB. ADO is the recommended SQL Server interface method, especially for Web applications.

Shortcuts

CTRL+:
Enters today's date.
CTRL+0
Enters NULL.


GeorgeHernandez.comSome rights reserved