Query Tools and Programming Interfaces

Accessing and Changing Relational Data

Accessing and Changing Relational Data

Query Tools and Programming Interfaces

Users who access and change data in instances of Microsoft® SQL Server™ require different levels of Transact-SQL knowledge, depending on the way users access the database:

  • Users of graphical report generators and general business applications need little or no knowledge of Transact-SQL. The applications present either easy-to-use charts and graphs, which require little database knowledge, or dialog boxes based on the user's business functions.

  • Users of general purpose SQL applications, such as SQL Query Analyzer and the osql utility, must understand how to use Transact-SQL from the utilities or in scripts of Transact-SQL statements.

  • Application programmers must have a complete understanding of the Transact-SQL functionality in SQL applications, as well as how to use a database application programming interface (API) to provide data values for Transact-SQL statements and to retrieve data in a relational (tabular) result set.

  • Internet application programmers must understand the fundamentals of how Transact-SQL statements work with relational rowsets, but they must also understand how SQL Server works with XML documents. They must understand:
    • How merged schemas present XML-based views of the data in SQL Server tables, and how to use XPath queries to retrieve that data in the form of XML documents.

    • How to use the FOR XML clause to direct SELECT statements to return results as XML documents instead of tabular result sets.

    • How to add XML documents to a database and then use the OPENXML clause to present the data from the document as a relational result set.

    • How to execute Transact-SQL statements, query templates, and XPath queries using Universal Resource Locators (URLs), ADO, or OLE DB.

Many users may, at different times, work in all categories of applications. Application programmers may use a SQL Server tool, such as SQL Query Analyzer, to test their Transact-SQL statements before coding them into an application. Database administrators work at all levels as they work with users and programmers to design new features and resolve database problems.

The topics in Accessing and Changing Relational Data pertain primarily to users of generic SQL tools and application programmers. Where relevant, information about additional features available to application programmers is provided. Internet programmers must know many of the concepts in Accessing and Changing Relational Data to understand the effects of the many Transact-SQL statements they execute. For information specific to working with data in the form of XML documents, see XML and Internet Support Overview.