Select

, , ...

From ...

Where ...

SELECT

SupplierID, CompanyName, ContactName, *

FROM Suppliers

WHERE Country <> 'USA'

ORDER BY Country

SELECT

Country, COUNT(*) as 'COUNT'

FROM Suppliers

GROUP BY Country

ORDER BY 'COUNT' DESC

SELECT

DISTINCT Country

FROM Suppliers

SELECT

TOP 10 ProductID, Sum(UnitPrice * Quantity) as Sales

FROM [Order Details]

GROUP BY ProductID

ORDER BY Sales

SELECT

SupplierID, CompanyName FROM Suppliers S

WHERE NOT EXISTS

(SELECT

'x' FROM [Order Details] D, Products P

WHERE D.ProductID = P.ProductID

AND S.SupplierID = P.SupplierID)

SELECT

FirstName + ' ' + LastName FROM Employees

SELECT

'SELECT

''' + name + ''', count(*) from [' + name + ']'

FROM sysobjects

WHERE type = 'U'

ORDER BY name

SELECT

GetDate() -- This is a comment from here on, I can type whatever I want

/* This is a multi-line comment, none of the statements are ever processed

DECLARE @myDate DATETIME

SET @myDate = GetDate()

PRINT 'The current date is ' & convert(varchar(18), @MyDate, 110)

*/

Select

convert(varchar(12), mydate, 113)

Select

convert(varchar(35), RTRIM(FNAME) + ' ' + RTRIM LNAME))

select

* from codes where convert(varchar(2), CODE_TAG) = ‘BO’

Select

case datepart(month, GetDate())

When 1 then 'January is nice'

When 2 then 'February is cold'

When 3 then 'Spring is coming'

Else 'Its not winter anymore'

End

Select

case

when datepart(month, GetDate()) > 9

then 'Winter is here'

when datepart(hour, GetDate()) > 22

then 'Its getting late'

when convert(varchar(12), getdate(), 113) = '28 NOV 2003'

then 'Everythings on SALE!'

when datepart(weekday, GetDate()) = 6

then 'TGIF!'

Else 'Who knows???'

CREATE VIEW PriceLookUp AS

Select

ProductID, ProductName, CategoryID, UnitPrice,

Case when getdate() > '28-NOV-2003 08:00' and

getdate() < '28-NOV-2003 18:00'

then UnitPrice * .85

else UnitPrice

end as SalePrice

from Northwind..Products

go

select

GetDate(), UnitPrice, SalePrice, * from PriceLookUp

Coalesce (expression1, expression2, ...)

-- this is essentially the same as:

case when expression1 is not null expression1

when expression2 is not null expression2

etc...

select

coalesce (OrderDate, RequiredDate, ShippedDate) as LastUpdate

from orders

NullIf(expression1, expression2)

-- NullIf is evaluated as

case when expression1 = expression2 then Null

else expression1

IsNull(expression1, expression2)

-- This is evaluated as

case when expression1 is null then expression 2

else expression1

-- NullIf is evaluated as

case when expression1 = expression2 then Null

else expression1

-- SQL:89 Inner Join Syntax

SELECT

*

FROM Suppliers S, Products P

WHERE S.SupplierID = P.SupplierID

AND P.Units > 2

-- SQL:92 Inner Join Syntax

SELECT

*

FROM Suppliers as S JOIN Products as P ON S.SupplierID = P.SupplierID

WHERE P.Units > 2

-- SQL:89 Left Outer Join Syntax

SELECT

*

FROM Suppliers S, Products P

WHERE S.SupplierID *= P.ProductID

AND P.Units > 2

-- SQL:92 Left Outer Join Syntax

SELECT

*

FROM Suppliers as S LEFT JOIN Products as P ON S.SupplierID = P.SupplierID

WHERE P.Units > 2

Select

* from Suppliers, Products

select

'Categories' as TAB, count(*) as CNT from Categories UNION

select

'CustomerCustomerDemo', count(*) from CustomerCustomerDemo UNION

select

'CustomerDemographics', count(*) from CustomerDemographics UNION

select

'Customers', count(*) from Customers UNION

select

'Employees', count(*) from Employees UNION

select

'EmployeeTerritories', count(*) from EmployeeTerritories UNION

select

'Order Details', count(*) from Order Details UNION

select

'Orders', count(*) from Orders UNION

select

'Products', count(*) from Products UNION

select

'Region', count(*) from Region UNION

select

'ReplenishWork', count(*) from ReplenishWork UNION

select

'Shippers', count(*) from Shippers UNION

select

'Suppliers', count(*) from Suppliers UNION

select

'Territories', count(*) from Territories

INSERT INTO (, , ...) values (, , ...)

 

You can omit the column list only if you have a value for every column in the row. Adding a new row to the Supplier table would look like this:

INSERT INTO Suppliers (SupplierID, CmpyName, Country)

Values (3, 'Isa Cmpy', 'CA')

SQL Server provides us with another form of the INSERT statement in which we can use an existing table as the source of the new records. In this case we use the syntax:

INSERT INTO (, , ...)

SELECT

, , ... FROM WHERE ...

For example, supposing we were implementing a reporting database (also called a data warehouse) in which we wanted to perform aggregation and summarization on our vendors. We could define a new table to contain our calculations and provide some level of isolation from the transactional system. Our new table might look like this:

	ReplenishWork
	SupplierID
	CmpyName
	ProductsOutOfStock

INSERT INTO ReplenishWork (SupplierID, CmpyName, ProductsOutOfStock)

SELECT

P.SupplierID, S.CmpyName, Count(*)

FROM Suppliers as S INNER JOIN Products as P ON S.SupplierID = P.SupplierID

WHERE P.Units < 2

GROUP BY P.SupplierID, S.CmpyName

UPDATE SET = , =
WHERE ...

 

But it also includes a variation in which you can update one table using another table as your source. This syntax is an extension Microsoft includes to the ANSI standard that won't necessarily run in other environments.

UPDATE ReplenishWork SET ProductsOutOfStock = P.UnitsInStock

FROM Products as P

WHERE P.SupplierID = ReplenishWork.SupplierID

Delete

Purging the database of unnecessary information, or to enforce relational integrity, we use the DELETE function. The basic syntax is:

DELETE FROM

 

WHERE ...

 

The WHERE clause determines which rows will be affected. A word of caution when you are deleting data, it takes a long time to type it back in so you want to be sure you check what you are getting rid of. You can do this by running a count of the rows that will be affected (select

count(*) from

WHERE…) before executing the delete. You can further protect yourself from unwanted aggravation by using Transactions.

 

Variables

Variables are used to temporarily hold values that may be useful in the processing of statements. We declare them using the DECLARE keyword, specifying the name and the type, and we assign a value to them with the SET statement. You can also assign a value as part of a SELECT

statement, but using SET is listed as the preferred method.

We reference them as we would column data or constants in the processing logic of our code using the @ character in front of the name to denote a variable. Global variables reference the status of the system and the database engine and other options of the server and are referenced with two @@ characters.

All user declared variables are local in scope, meaning they remain accessible until the end of the current batch of statements is complete. For example executing the following script would result in an error because the variable @MyVar is not declared within the batch in which it is referenced.

DECLARE @MyVar varchar(15)

SET @MyVar = 'New Value'

Go

PRINT 'The value of MyVar is ' + @MyVar

Batches

Batches are a grouping of one or more SQL statements that together form a logical unit of work. They are sent together to the SQL Server engine, which creates an execution plan. If errors are generated in creating the plan, none of the statements are actually executed. Errors that occur after the execution plan has been compiled will cause succeeding statements to fail, but will not affect previous logic in the batch. This is true unless the batch is part of a transaction in which all of the statements are rolled back after the error.

For example, the following Transact SQL Code is comprised of 3 statements, which are all part of the same batch:

DECLARE @Discount float

SET @Discount = 0.05

SELECT

ProductID, UnitPrice, UnitPrice - @Discount * UnitPrice as SalePrice

FROM Products

ORDER BY SalePrice Desc

GO

Because the @Discount variable is used in the successive commands, if you try to run either of the last two statements separately you will raise an error condition.

Creating objects such as views, defaults, procedures, rules and triggers cannot be combined with other statements in a batch. A batch is completed by the inclusion of the command GO. An important point to remember about GO is that it is not a Transact SQL statement, rather it is recognized by Query Analyzer and ISQL as a marker to send all statements from the previous GO to the engine and await the result set.

You can see the effect of this when the results of statements before the GO command are visible in the results pane of Query Analyzer before the next batch of statements executes. If you include GO in an ODBC call or from one of the database libraries such as ADO or OLE DB, the SQL Server engine will flag a syntax error. Another point is that local variables declared in a batch go out of scope when the batch end, which is one reason why you cannot include GO in stored procedure declarations.

Transactions

A transaction is a grouping of statements that must be either succeed or fail together as a logical unit of work. You may have heard of the ACID (Atomicity, Consistency, Isolation and Durability) test for a transaction. These are the four properties which a valid transaction must meet.

• Atomicity means that all the statements part of the group must either succeed or fail together. For example, if you were developing a banking application to add new accounts, the transaction of adding rows to the customer table and the account table might for a logical unit of work in which our data model integrity constraints require that records must exist in both tables to be valid.
• Consistency means that the database is left in a stable state at the end of the transaction, and that all data integrity constraints are met.
• Isolation defines that the data is modified either before or after other transactions against the same rowset. Basically we cannot alter the data that is at an intermediate state. The locking rules of the database prevent this from happening.
• Durability means that the effects of the transaction are persisted after the end of the transaction, even in the event of a system failure. A system failure in the middle of a transaction would roll back the state of the database to where it was before the transaction began.

We manage transactions with the command BEGIN TRANSACTION, and end it with the command COMMIT or ROLLBACK. When beginning a transaction, locks on the database prevent other user sessions from altering the data.

For example, suppose we want to implement a sale price on our Products at Northwind.

BEGIN TRAN

DECLARE @Discount float

SET @Discount = 0.05

UPDATE Products

SET UnitPrice = UnitPrice - (@Discount * UnitPrice)

Go

-- Check the results...

Select

ProductID, UnitPrice

FROM Products

ORDER BY UnitPrice Desc

If we don't like what we see, we can roll back the transaction and leave the database in its previous state

ROLLBACK

GO

SELECT

ProductID, UnitPrice -- See the original prices!

FROM Products

ORDER BY UnitPrice Desc

GO

Conversely to make the changes permanent we use the COMMIT operator:

COMMIT

Go

Data Definition Language (DDL)

Data Definition Language (DDL) is used to define tables, views, stored procedures, and other objects that physically and logically express the data model you are working with. Data modeling and the Data Definition Language is a big topic that takes up more space than we've been allotted here. There are some very good books that focus just on this topic including Louis Davidson's Data Modeling Book ISBN: ???

The core of relational databases are the tables that store information. We can create tables using the Graphical tools provided with SQL Server, but this practice lends itself better to development of unique, custom objects that are still in the process of evolving. Deploying a schema out to multiple servers in a consistent basis typically relies on scripts to complete the tasks involved. The Enterprise manager allows us to generate these scripts, but we still should understand what the scripts are doing in case we need to customize or change the script.

Managing Tables

Our first task in making our data model usable is to create base set of tables. We do this using the CREATE TABLE statement which specifies how information is stored in the table. Each column definition includes a name, data type, whether the column allows null values to be stored or not, and other optional attributes. The basic syntax diagram for the CREATE TABLE is below:

CREATE TABLE

( )

 

A basic simple example of the ReplenishWork table can be created with the following statement:

CREATE TABLE ReplenishWork

(SupplierID int NOT NULL,

CmpyName varchar(40) NOT NULL,

ProductsOutOfStock int NULL)

Business changes drive database changes and we don't always have the luxury of recreating our schema in a production environment. Transact SQL allows us to expand column sizes and add new columns to existing tables using the ALTER statement:

ALTER TABLE ReplenishWork

ADD ContactName varchar(20)

We can also change the size and type of columns, drop columns, manage constraints and default values using the ALTER statement. If we didn't need to preserve existing data and were making changes to the schema that prohibited the use of the ALTER command we can DROP the table before recreating it. If we needed to reconfigure our ReplenishWork table to support integration of legacy data that used a character based supplier id we could do so with the following batch:

DROP TABLE ReplenishWork

GO

CREATE TABLE ReplenishWork

(SupplierID varchar(16) NOT NULL,

CmpyName varchar(40) NOT NULL,

ProductsOutOfStock int NULL,

ContactName varchar(20) NULL)

Indexes and Keys

Databases manage large amounts of information. In order to be efficient, they use data structures to map the contents called Indexes. In SQL Server the indexes come in two flavors: Clustered and Non-Clustered. The difference is that there can be only one Clustered index on a table, and this index will determine the ordering of how the data is physically stored. SQL Server supports up to 249 non-clustered indexes for any given table. To add an index to our ReplenishWork table we could use the following command

CREATE TABLE ReplenishWork

(SupplierID int PRIMARY KEY CLUSTERED,

CmpyName varchar(40) NOT NULL,

ProductsOutOfStock int NULL)

By default SQL Server will use clustering on the primary key defined for a table. This is important because without at least one clustered index on a large table, all the data would be scattered throughout the physical storage, and performance on retrieval of data may suffer. You can also create indexes on existing tables using the CREATE command:

CREATE INDEX IDX_ReplenishWork

ON ReplenishWork (CmpyName)

On the other hand unnecessary indexes can impact performance as well. Think of an index as a physical data structure that has a cost associated with maintaining it when data changes. If the nature of your system involves capturing transactional information as quickly as possible, you want to run with the least number of indexes. Each insert, update and delete must be reflected in each of the indexes on the table, so the fewer the indexes the less the overhead of those operations.

Systems which are primarily decision support and reporting based can afford to have more indexes because once the data has been written it isn't updated as much as a transactional system. You will tend to see more generous use of indexes in Data Warehousing systems that support a lot of Ad-Hoc querying.

Create Views

Views allow us to provide an abstraction layer between the physical implementation of the data and how users see the information. They are in essence virtual tables that we can use to limit or expand how we are able to use the base set of tables in our database. In SQL Server we are limited to a single SQL Select

statement to define the result set. The security architecture of SQL Server allows us to grant users access to a view without giving access to tables (and views) on which the view is based. For example, if we needed to make an employee phone list available to users but needed to protect other employee information (such as salary, social security number, birth date, etc) we could create a view of just the name & phone.

CREATE VIEW PhoneList AS

SELECT

FirstName + ' ' + LastName as EmpName, HomePhone

FROM Employees

go

select

* from PhoneList

The ALTER and DROP commands operate in the same way they do with tables. One interesting feature of SQL Server is that it supports indexed and updateable views. This means that you can define a view on some base objects, create indexes to optimize access and even alter data stored in the underlying tables, although you need to be aware that there is more overhead in updating a view than if you managed the data in the underlying tables directly.

Schema Binding

In order to create an index on a view, the view must be created with the SCHEMABINDING option. This option saves information about the underlying schema is stored in the system tables and SQL Server will prevent changes that would invalidate our View.

CREATE VIEW PhoneList

WITH SCHEMABINDING
AS

SELECT

FirstName + ' ' + LastName as EmpName, HomePhone

FROM Employees

go

Declarative Referential Integrity (DRI)

The business rules, which define the how our application behaves, such as that we will allow users of our application to setup a customer before they make an order, are implemented using foreign keys and constraints.

Constraints

ALTER TABLE dbo.Table1 ADD CONSTRAINT

PK_Customer PRIMARY KEY CLUSTERED

(

CustID

) ON [PRIMARY]

Default values and extended properties

We can further define default values and extended properties for the tables we create using the:

ALTER TABLE dbo.Table1 ADD CONSTRAINT

DF_Table1_CreditLimit DEFAULT 0 FOR CreditLimit

Data Control Language (DCL)

Data Control Language (DCL) allows the owner and administrator of the database objects to define what rights users have to the data and objects in the database. The DBA can manage access to the database by granting and denying users rights to objects. The term CRUD Matrix refers to the rights to Create, Read, Update, and/or Delete information in a given table.

• GRANT
  Grant is used to give access to someone who needs to use the information stored in the objects. SQL Server allows us to specify user based or group based rights, and to apply them to objects or to groups of objects within a database.
• REVOKE
  We use the REVOKE statement to reverse the effect of the GRANT statement.
• DENY
  The DENY statement will prevent a user from accessing objects in the database.

Improvements over ANSI SQL

With the release of SQL Server 2000 Microsoft takes the SQL language a step further. Among the many additions in this release, we find new data types, user defined functions, and extensive support for XML, as well as indexing, trigger handling, and an expanded suite of system stored procedures. The result is one of the most flexible, scalable, and complete offerings in the database management system product field.

Data Types

While all relational databases implement the basic numeric, string, and date data-types for storing data, Microsoft has added some which you may not have heard of. Some of these were added in support of the Replication functions built into the database engine, others fit more neatly with the new feature of User Defined Functions. The new data types include:

• BIGINT:
  True to its name, this is a very big whole number. You can use it to store values in the range of -9223372036854775808 to 9223372036854775807. This is 19 digits of precision that can hold some extremely large values. If you are porting data from platforms that include intelligent keys (like the first 4 digits is the location, the next 6 is customer, the next 3 is the weight, etc.) it would probably be better to normalize your schema and transform the data into multiple columns.
• UniqueIdentifier:
  Columns of this type store system generated identifiers known as GUID's or Globally Unique Identifier. The GUID is a 38 character value generated by windows that is guaranteed to be unique across the world. It is takes the same form as the CLSID in the COM world. The NEWID function is used to generate it, and we can store them in the column type UniqueIdentifier.

DECLARE @guid uniqueidentifier

select

@guid = NEWID()

PRINT 'Value of @guid is: '+ CONVERT(varchar(50), @guid)

Value of @guid is: F4840C7B-F44C-4A49-A12E-097B45AE3CE0

While you may not want to use a GUID as a primary key because of its cumbersome nature for ad-hoc queries (try correctly typing in the 36 character value), it does allow us to implement n-way replication in which data is created on multiple servers. For example, if we were going to deploy the Northwind solution across the country and setup a merge replication with a corporate site to contain all orders, using a sequentially generated OrderID could create problems in that the same ID could be created on more than one server. By redefining it as:

CREATE TABLE Orders (

OrderID uniqueidentifier NOT NULL default NEWID(),

CustomerID nchar (5) NULL ,

EmployeeID int NULL ,

OrderDate datetime NULL ,

RequiredDate datetime NULL ,

ShippedDate datetime NULL ,

ShipVia int NULL ,

Freight money NULL DEFAULT (0),

ShipName nvarchar (40) NULL ,

ShipAddress nvarchar (60) NULL ,

ShipCity nvarchar (15) NULL ,

ShipRegion nvarchar (15) NULL ,

ShipPostalCode nvarchar (10) NULL ,

ShipCountry nvarchar (15) NULL

)

While this makes the replication scheme possible, you should define a clustered primary key on some other columnset. Ordering the data according to a randomly generated value will result in poor performance when processing the data because the data is scattered all over the disk in no order means there and there is increased seek time.

SQL_Variant:
Similar to the variant data type in Visual Basic, SQL_Variant is used to store data values of all the supported data types not including text, ntext, image, and timestamp. We can use it for column definitions, parameters, variables, and for the return value of user defined functions. Microsoft provides the function SQL_VARIANT_PROPERTY to return the base data type and other information about data stored.

One use for variants is in defining schema to hold attribute data. Suppose you need to keep a variable number of optional attributes about a person, such as hair color, weight, age, number of children, birthdate, favorite pet, etc. In our example we are collecting what information we can, but because there are a lot of optional attributes, we end up with a sparsely populated table.

PersonID (int)	HairColor (varchar 8)	Weight (int)	Age (int)	Sex (char 1)	Birthdate (datetime)	Pet (varchar6)
1	Brown					Cat
2		250	32	M		Dog
3					1/2/62	Bird

Using SQL Variant we could more efficiently store the same set of information using a schema that looks like this:

PersonID (int)	AttributeName (varchar 12)	AttributeValue SQL_Variant
1	HairColor	Brown
1	Pet	Cat
2	Weight	250
2	Age	32
2	Sex	M
2	Pet	Dog
3	Birthdate	1/2/62
3	Pet	Bird

Table:

True to its name, the table data type is a variable that temporarily stores a rowset in memory for later processing that can be used like an ordinary table. It accomplishes this using space in the TEMPDB, but because it has a well defined scope, it efficiently cleans up after itself. It also requires less locking and logging than tables created in the TEMPDB, which results in fewer recompilations of the execution plan. The Table datatype was added in support of a powerful new feature of SQL Server, the User Defined Function.

You declare a table variable using the DECLARE statement. You can then manage the content of that table variable, inserting, updating and deleting as needed. Since the scope of variables is local to the current batch, other processes cannot access your table variable.

User Defined Functions

Structured programming languages have long provided the ability to create procedures and function calls to breakdown complex routines into manageable units. The introduction of the User Defined Function (UDF) with SQL Server 2000 provides us with a new way of organizing and optimizing our use of the database engine. A User Defined Function takes zero or more input parameters and returns either a scalar or a table based value. We could use a UDF to calculate the distance between two geographic locations, return a list of projects for a given client, or return an amortization table for a given set of loan parameters.

UDF's are similar to stored procedures in that they both use the control statements and data manipulation code to define the logic of the module. But they differ in that UDF's don't allow modification of the global state of the calling process or the database environment. In other words User Defined Functions are stateless in that there can be no side affects. You can't alter data in tables that are not locally defined within the UDF, and you can't use non-deterministic functions within your code. Deterministic functions always return the same value given the same input parameters, such as the DATEADD function. The GETDATE function is non-deterministic in that the value depends on when you call it.

SQL Server 2000 supports 3 types of User Defined Functions: Scalar Functions, Inline Table Valued Functions, and Multiple Statement Table Valued Functions. Scalar Functions are UDF's that return a single discrete value and contain logic that is embedded between begin and end statements. This flavor of UDF must return a single value each time it is called. We create a UDF using the DECLARE function:

Table Valued Functions return a table variable that can be comprised from one or more statements. If it is a single table and doesn't specify the column set in the declaration, then the statement doesn't require the begin/end wrapper, and it is considered to be Inline.

Multi-statement UDF's allow us to do more complex processing in order to derive the result set. Like scalar UDF's we can declare local variables, use flow control statements, manage data in locally defined table variables and call stateless extended stored procedures.

Using our Northwind database for this example, suppose we wanted to create a function that returned sales ranking information.

Stored Procedures

While we can run ad-hoc SQL Statements within Query Analyzer, and we have the ability to use the execute() function in ADO, frequently performed logic can be encapsulated into batches and saved as Stored Procedures on the server. The benefits from this approach not only include the reduction of work of testing multiple programs and applications to ensure that the logic is correct, but also from the execution plan generated on the server when the stored procedure is compiled and saved.

Stored Procedures are a collection of Transact SQL statements which perform a defined set of work. This can be as simple as updating a single row or returning a configuration setting, to being as complex as implementing a business rule that requires sending an e-mail alert containing crucial information specific to the problem. They can take both input and output parameters, and always return an integer value. There are several types of stored procedures including those that come with SQL Server, user defined and extended stored procedures.

System Stored Procedures.

Microsoft includes a broad suite of system stored procedures for administering the server engine. There are over 900 system stored procedures that SQL Books breaks into 17 categories. They include procedures for managing system security, to setup and manage distributed queries and linked servers (making tables and data on different machines visible to each other), data replication, retrieve information about schema, view current performance and usage information, manage SQL Agent (the scheduler that comes with SQL Server), and to interface with XML data.

For example, to return the schema of a table we can use the sp_help stored procedure:

If the stored procedure call is the first command in the batch we can omit the EXEC(UTE) command. To see the definition of a view or a stored procedure or other system objects we use sp_helptext:

User defined Stored Procedures.

We can define our own stored procedures to provide users of the database with a consistent implementation of work logic across applications. This simplifies the administration of business rules by acting as a single point of control on the database server itself. If the rule is changed, we only need to update the associated stored procedures instead of the various applications making use of the data.

Stored procedures are declared with the CREATE PROCEDURE statement.

For more information on developing stored procedures see chapter XX

Extended stored procedures (XP's).

Extended stored procedures allow us to extend Transact SQL develop logic outside of the SQL Server using tools such as C or Visual Basic. They follow the same calling standards as a normal stored procedure, complete with parameters and a return code. XP's are implemented using a call to dynamic link libraries (dll) that conform to the Extended Stored Procedure API. you need to be careful that they are thoroughly tested and checked for aberrant behavior before using them in a production situation because these calls are external to SQL Server.

If we had written a standard event management system to log information to the NT Event Log and wanted to include it in our SQL environment we call the stored procedure sp_addextendedproc:

Where we use T-SQL

The Transact SQL Language is the primary interface to the SQL Server database engine. Any time we interact with SQL Server, whether it is through one of the provided administrative tools, a custom application, or through the command line interface ISQL, we are executing SQL statements. The vehicle for this might be ODBC or one of the high level drivers, or it might be with DB-Library, the original interface provided for talking to the database. In any case, requests in the form of SQL statements are made to the engine, which then processes them and returns the result set.

Query Analyzer

Query Analyzer is the current ad-hoc user interface that comes with SQL Server. It is installed as part of the client tools and is typically one of the first places a developer will begin working with SQL. By default it opens to an edit screen in which we can type our code. It provides context based coloring which is a nice enhancement from the previous version.

To run a batch of statements, you can click on the Execute button and results pane will be display the output of the selected batch. By default the entire script will be run, but you can limit it to portions of the script by selecting what you want to execute and then only the selected statements will be run.