Accessing and Changing Data Fundamentals

The primary purpose of a Microsoft® SQL Server™ 2000 database is to store data and then make that data available to authorized applications and users. While database administrators create and maintain the database, users work with the contents of the database:

• Accessing, or retrieving, existing data
  
  
• Changing, or updating, existing data
  
  
• Adding, or inserting, new data
  
  
• Deleting existing data

Accessing and changing data in Microsoft SQL Server is accomplished by using an application or utility to send data retrieval and modification requests to SQL Server. For example, you can connect to SQL Server using SQL Server Enterprise Manager, SQL Query Analyzer, or the osql utility to begin working with the data in SQL Server.

Applications and utilities use two components to access SQL Server:

• Database application programming interfaces (APIs) send commands to SQL Server and retrieve the results of these commands. The APIs can be general-purpose database APIs such as ADO, OLE DB, ODBC, or DB-Library. They can also be APIs designed specifically to use special features in SQL Server, such as SQL-DMO, SQL-DTS, or the SQL Server replication components.
  
  
• Commands sent to SQL Server are Transact-SQL statements.

  Transact-SQL statements are built using the SQL language defined in the Transact-SQL Reference. Most of these operations are implemented using one of four Transact-SQL statements:

  • The SELECT statement is used to retrieve existing data.
    
    
  • The UPDATE statement is used to change existing data.
    
    
  • The INSERT statement is used to add new data rows.
    
    
  • The DELETE statement is used to remove rows that are no longer needed.

These four statements form the core of the SQL language. Understanding how these four statements work is a large part of understanding how SQL works.

Graphical or forms-based query tools require no knowledge of SQL. They present the user with a graphical representation of the table. The user can graphically select the columns to be retrieved and easily specify how to qualify the rows to be retrieved.

Some applications, such as SQL Query Analyzer and the osql utility, are tools for executing Transact-SQL statements. These statements are entered interactively or read from a file. To use these tools, you must be able to build Transact-SQL statements.

Applications written to the general-purpose database APIs, such as ADO, OLE DB, ODBC, or DB-Library, also send Transact-SQL statements to SQL Server. These applications present the user with an interface reflecting the business function they support. When the user has indicated what business function should be performed, the application uses one of the database APIs to pass SQL statements to SQL Server. You must be able to build Transact-SQL statements to code these types of applications.

Other applications, such as SQL Server Enterprise Manager, use an object model that increases efficiency in using SQL Server. SQL Server Enterprise Manager uses an object model that eases the task of administering SQL Servers. APIs such as SQL-DMO, SQL-DTS, and the replication components also use similar object models. The objects themselves, however, communicate with SQL Server using Transact-SQL. Knowing the Transact-SQL language can help you understand these objects.

Building Transact-SQL Statements

Accessing and Changing Data Fundamentals contains information about the basic elements used to build Transact-SQL statements. It also provides information about the functions Transact-SQL can perform, as well as similar functionality offered by the database APIs.

A SELECT statement contains the common elements used in Transact-SQL statements. For example, to select the names, contact names, and telephone numbers of customers who live in the USA from the Customers table in the Northwind database, these elements are used:

• The name of the database containing the table (Northwind)
  
  
• The name of the table containing the data (Customers)
  
  
• A list of the columns for which data is to be returned (CompanyName, ContactName, Phone)
  
  
• Selection criteria (only for customers living in the USA)

This is the Transact-SQL syntax to retrieve this information:

SELECT CompanyName, ContactName, Phone
FROM Northwind.dbo.Customers
WHERE Country = 'USA'

Additional elements used in Transact-SQL statements include:

• Functions.

  Functions are used in SQL Server queries, reports, and many Transact-SQL statements to return information, similar to functions in other programming languages. They take input parameters and return a value that can be used in expressions. For example, the DATEDIFF function takes two dates and a datepart (weeks, days, months, and so on) as arguments, and returns the number of datepart units there are between the two dates.

• Identifiers.

  Identifiers are the names given to objects such as tables, views, databases, and indexes. An identifier can be specified without delimiters (for example, TEST), with quoted delimiters ("TEST"), or in brackets ([TEST]).

• Comments.

  Comments are nonexecuting remarks in program code.

• Expressions.

  Expressions include constants or literal values (for example, 5 is a numeric literal), functions, column names, arithmetic, bitwise operations, scalar subqueries, CASE functions, COALESCE functions, or NULLIF functions.

• Reserved keywords.

  Words that SQL Server reserves for its own functionality. It is recommended that you avoid using these reserved keywords as identifiers.

• Null values.

  Null values are values that are unknown. You can use values of NULL to indicate that this information will come later. For example, if the contact at the Leka Trading company changes and the new contact is unknown, you could indicate the unknown contact name with a value of NULL.

• Data types.

  Data types define the format in which data is stored. For example, you can use any of the character or Unicode data types (char, varchar, nchar, or nvarchar) to store character data such as customer names.

• Batches.

  Batches are groups of statements transmitted and executed as a unit. Some Transact-SQL statements cannot be grouped in a batch. For example, to create five new tables in the pubs database, each CREATE TABLE statement must be in its own batch or unit. This is an example of a Transact-SQL batch:

  USE Northwind
  SELECT *
  FROM Customers
  WHERE Region = 'WA'
    AND Country = 'USA'
  ORDER BY PostalCode ASC, CustomerID ASC
  UPDATE Employees
  SET City = 'Missoula'
  WHERE CustomerID = 'THECR'
  GO
  

• Control-of-flow language.

  Control-of-flow language allows program code to take action, depending on whether a condition is met. For example, IF the amount of products ordered are equal to or less than the amount of products currently on hand, THEN we must order more products.

• Operators.

  SQL Server includes operators, which allow certain actions to be performed on data. For example, using arithmetic operators, you can perform mathematical operations such as addition and subtraction on your data.

See Also

Data Types

CREATE TABLE

Expressions

ALTER TABLE

Using Identifiers

Operators

Functions