Home arrow SQL Server arrow Page 3 - Sample Chapter: Wrox Beginning SQL Server 2000 Programming

Sample Chapter: Wrox Beginning SQL Server 2000 Programming

Wrox are well known for their "How-To" series of books, which teach beginning-intermediate developers how to use Microsoft technologies properly and efficiently. Today, Mitchell takes a look at chapter three from "Beginning SQL Server 2000 Programming", which is published by Wrox Press.

Author Info:
By: Mitchell Harper
Rating: 4 stars4 stars4 stars4 stars4 stars / 23
January 04, 2002
  1. · Sample Chapter: Wrox Beginning SQL Server 2000 Programming
  2. · Chapter 3: Query Analyzer (contd.)
  3. · Chapter 3: Query Analyzer (contd.)
  4. · Chapter 3: Query Analyzer (contd.)
  5. · Chapter 3: Query Analyzer (contd.)
  6. · Chapter 3: Query Analyzer (contd.)
  7. · Chapter 3: Query Analyzer (contd.)
  8. · Chapter 3: Query Analyzer (contd.)
  9. · Chapter 3: Query Analyzer (contd.)
  10. · Summary

print this article

Sample Chapter: Wrox Beginning SQL Server 2000 Programming - Chapter 3: Query Analyzer (contd.)
(Page 3 of 10 )

This is where using Windows Authentication comes in to play, especially where SQL Server is on an NT or 2000 machine. By logging in to SQL Server using Query Analyzer with Windows Authentication, you are logging in to the server as yourself, with the correct credentials. Even when more than one person is logged in to SQL Server, with Windows Authentication, it is possible to track the users and the processes that they are running. Of course, people can still log on using SQL Server Authentication they just shouldn't do it with the sa login. The best method, as was described in Chapter 1, is to place a secure but memorable password on the sa account, and tell nobody what it is.

A major point to note in both screens is the top SQL Server combo box. Notice the period (.) where the name of the server should be. This is what will be seen for a local SQL Server. You can also type in: (local). If, however, the SQL Server were on a remote computer, then you would enter the name of this remote computer in this box, or click the ellipsis (...) button on the right. This would bring up a list of SQL Servers on remote computers that you had access to, and you could select which installation to connect to:

As you can see, in this local installation there are no active servers on the network.

The final point to note is the Start SQL Server if stopped check box. It could be that SQL Server is not running when you start up Query Analyzer from the Start button. This box is checked by default, which means that Query Analyzer will start SQL Server for you. Really you would only check this if you wanted to use Query Analyzer without the need to find out any information from any SQL Server database, which would be pretty rare.

Now that you know how to start Query Analyzer and have it started on your desktop, we can take a look at what makes up Query Analyzer, starting with the Object Browser.

The Object Browser

As you can see in the screenshot below, the Object Browser is located on the left-hand side of Query Analyzer. If it is not immediately visible, you will need to go to Tools | Object Browser | Show/Hide. Note the two Objects and Templates tabs at the bottom of the screen:

The Object Browser shows a list of objects within the SQL Server you have connected to. It also provides a list of functions that can be used for constructing queries and stored procedures, and help in using these functions. Finally, all the system data types that are valid within SQL Server are also available for inspection through this pane.

This all helps you, as a developer, to know what functions are available for use within your queries, and to demonstrate the syntax and parameters to be passed in to them. The next section will look at these objects in a bit more detail. First of all, let's take a look at the Objects tab and the objects within SQL Server.

System Objects

SQL Server comes installed with a number of built-in databases four are required to make SQL Server work and two others created as example databases. These were discussed in Chapter 1 when the installation process of SQL Server was demonstrated. However, no matter whether the database is a system database, an example database, or even your own database, there will be specific objects within every database that make that database function in the correct manner. These are all known as system objects, and includes user and system tables, views, and even the databases themselves.

When you first open Query Analyzer, the system objects tree nodes will all be collapsed such that no individual item within any node can be seen: The root object nodes within the tree are the databases of the SQL Server installation, and when you expand these nodes, you will see other system objects, which are grouped together.

You may have noticed the Common Objects node situated below these system objects. This is something quite separate, which we will discuss shortly.

These nodes can then be expanded, by clicking on the + signs, so that you can see whatever information you require at any given time. Imagine, for example, that you want to explore what information is within a Northwind table. You can't remember whether a column called OrderDate is within a table called Orders or OrderDetails. By expanding the nodes in the Object Browser, you can see exactly where that piece of information is. The screenshot below shows the columns in the Orders and OrderDetails tables expanded:

As you can see, the OrderDate column is in the Orders table and not in OrderDetails.{mospagebreak title=Chapter 3: Query Analyzer (contd.)&toc=1} Common Objects

Common objects under the Common Objects folder, are those objects that are not specific to a particular database. The items within this part of the Object Browser are not tables, etc., but are the functions that you can perform within SQL Server, or the system data types that are available to you. These are aids to help you with SQL Server commands within Query Analyzer, a sort of quick reference guide if you like.

Query Analyzer has grouped all of these functions into areas of similar functionality, so that specific functions are easy to locate. For example, you can expand the String Functions node to find a list of all the string manipulation functions that you can use.

What is great about this part of the Object Browser is that, if you place the cursor over a specific function, a tool tip is displayed giving a short description of what the function does.

Let's now take a look at the other tab Templates.


The Templates tab is used for holding SQL Server and user-built templates. A template is a file which holds the basics of a function used in Query Analyzer, which can either manipulate databases, manipulate database objects, or server objects, or work with the data within a table in a server. When I say manipulate here, I also mean that there are templates that exist for the creation of databases, objects, and so on. Notice that I said "the basics". This is the whole essence of what a template is it provides the basic template for a function. Some templates contain more information, and more built-in functionality than others. However, with all templates, there are parameters that must be set to allow the template to become a fully functional piece of code.

If you move on to the Templates tab you will see many template groups, which give us a quick overview of the areas that we may apply templates to.

This may seem a little confusing and, although templates will be covered in more detail in Chapter 6 when we look at the creation of tables, it would be useful to see the contents of a template right now.

Click on the Create Table node to expand it and double-click on Create Table Basic Template. The following code will appear in the main pane of Query Analyzer. Don't worry if you don't understand it all yet, as this specific code is covered in Chapter 6:

-- =============================================

-- Create table basic template -- =============================================


FROM sysobjects

WHERE name = N'<table_name, sysname, test_table>'

AND type = 'U')

DROP TABLE <table_name, sysname, test_table>


CREATE TABLE <table_name, sysname, test_table> (

<column_1, sysname, c1> <datatype_for_column_1, , int> NULL,

<column_2, sysname, c2> <datatype_for_column_2, , int> NOT NULL)


If you look closely, you will see a number of lines of code with greater-than and less-than signs surrounding three comma separated names. These are the parameters that were referred to above. By using the template replacement facility, Ctrl+Shift+M, or Edit | Replace Template Parameters from the menu, you can very quickly alter these parameters to make the template complete. The dialog for setting these parameters is shown overleaf:
blog comments powered by Disqus

- Executing SQL Server Stored Procedure from P...
- How to Search for Date and Time Values Using...
- Replication: SQL Server 2000 - Part 2
- Replication: SQL Server 2000 - Part 1
- SQL Sever: Storing Code in Binary or Text Fi...
- Execute SQL on Multiple Tables/Columns - New...
- How to Connect to a SQL Server from Visual F...
- SQL Server Hardware Tuning and Performance M...
- Primary Key on Multiple Tables New RDBMS C...
- Migrating from Sybase to SQL Server
- What's Best for DBAs? GUI or T-SQL Comma...
- How to Perform a SQL Server Performance Audit
- An Introduction To The Bulk Copy Utility
- SQL Server Stored Procedures 101
- Building Your First SQL Server 2000 Database

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2018 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials