The primary purpose of a Microsoft® SQL Server™ 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 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 Server Query Analyzer, or the osql utility and begin working with the data in SQL Server.
Applications and utilities use two components to access SQL Server:
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:
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 Server Query Analyzer and the osql utility, are tools for executing Transact-SQL statements. The tools execute Transact-SQL statements 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 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.
Accessing and Changing 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:
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 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 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 are nonexecuting remarks in program code.
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.
Words that SQL Server reserves for its own functionality. It is recommended that you avoid using these reserved keywords as identifiers.
Null values are those 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 contact is unknown, you could indicate the unknown contact name with a value of NULL.
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 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 allows program code to take action, depending on whether a condition is met. For example, IF the amount of product ordered is equal to or less than the amount of product currently on hand, THEN we must order more product.
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.
| Data Types | CREATE TABLE |
| Expressions | ALTER TABLE |
| Using Identifiers | Operators |
| Functions |