Home arrow SQL Server arrow Page 4 - Building Your First SQL Server 2000 Database
SQL SERVER

Building Your First SQL Server 2000 Database


SQL Server 2000 is one of the most popular RDBMS's for the Windows platform. Never worked with SQL Server 2000 before but dying to know how? In this article Mitchell looks at how to create your first database, and also shows you how to manipulate this database through both Enterprise Manager and Query Analyzer.

Author Info:
By: Mitchell Harper
Rating: 5 stars5 stars5 stars5 stars5 stars / 86
April 02, 2002
TABLE OF CONTENTS:
  1. · Building Your First SQL Server 2000 Database
  2. · Creating a database with Enterprise Manager
  3. · Creating a database with Query Analyzer
  4. · Manipulating our database
  5. · Conclusion

print this article
SEARCH DEVARTICLES

Building Your First SQL Server 2000 Database - Manipulating our database
(Page 4 of 5 )

On this page we're going to play around with a couple of TSQL commands and modify our database. The details of the commands you're about to see are available in the SQL Server 2000 help file, which you can access by pressing F1 in Query Analyzer.

Adding a column to our widgets table

Let's pretend that the boss of our widgets corporation wants to be able to set whether or not a particular widget is in stock. Most widgets are available, however a select few have to be imported from Widgetville first. We could add a new column called widgetAvailable with this TSQL code:

USE MYDATABASE1

GO

ALTER TABLE widgets

ADD widgetAvailable BIT NOT NULL DEFAULT 1

GO


The code above creates the new widgetAvailable column and also sets the value of that column to 1 for every record that currently resides in the widgets table.

Add a trigger to our widgets table

On of the most powerful features of SQL Server is the trigger. A trigger is a piece of code that is fired whenever an INSERT, UPDATE or DELETE command is executed against a particular table. Let's create a trigger on our widgets table so that whenever we add a new widget to the table, SQL Server will tell us how many widgets exist in the table:

USE MYDATABASE1

GO

CREATE TRIGGER trigShowTotalWidgets

ON widgets

FOR INSERT

AS

DECLARE @numWidgets INT

SELECT @numWidgets = (SELECT COUNT(*) FROM Widgets)

PRINT 'There are '+CAST(@numWidgets AS VARCHAR)+' in the widgets table'

GO


Run the code above, clear the Query Analyzer window and then enter and run the following code:

USE MYDATABASE1

GO

SET NOCOUNT ON

INSERT INTO widgets(widgetName, widgetPrice, widgetAvailable)

VALUES('Maroon Widget', 21.45, 1)

GO


We've setup a trigger that will execute whenever an INSERT statement is executed against our widgets database. The trigger prints how many records exist in the widgets table. When you run the code above, the bottom pane of Query Analyzer should read something like "There are 7 in the widgets table". If you'd like to read more about triggers then I've written a complete article about them. You can read that article here.

Backing up our database

SQL Server 2000 is one seriously robust and flexible RDBMS. It includes several stored procedures, which are simply blocks of TSQL statements grouped together. The master database contains dozens of stored procedures, and you can see them using Enterprise Manager by expanding the master database and clicking on the store procedure node. Double click on any stored procedure in the right pane to view its code.

To backup our MyDatabase1 database, we first need to create a backup device, which is a place where the backup will reside. We can use the sp_adddumpdevice stored procedure to do this:

USE MASTER

GO

sp_addumpdevice 'disk', 'MyDatabase1_device', 'c:\MyDatabase1.dat'

GO


In the code above, we've just created a new backup device with the alias of MyDatabase1_device. We can then backup our database to this device using TSQL's BACKUP DATABASE command, like this:

BACKUP DATABASE MyDatabase1 TO MyDatabase1_device

GO


Query Analyzer will respond with something similar to this:

Processed 96 pages for database 'MyDatabase1', file 'MyDatabase1' on file 1.

Processed 1 pages for database 'MyDatabase1', file 'MyDatabase1_log' on file 1.

BACKUP DATABASE successfully processed 97 pages in 0.217 seconds (3.635 MB/sec).


Retrieve data as XML

SQL Server 2000 supports the FOR XML clause, which we can use to easily return records as XML data. Use the following code in Query Analyzer to return all rows from our widgets table as XML elements:

USE MyDatabase1

GO

SELECT 1 AS Tag,

NULL AS Parent,

widgetName AS [Widget!1!Name],

widgetPrice AS [Widget!1!Price],

widgetAvailable AS [Widget!1!Available]

FROM widgets

FOR XML EXPLICIT

GO


The XML will be returned as one record and will something look like this:

<Widget Name="Red Widget" Price="9.9500" Available="1"/>

<Widget Name="Blue Widget" Price="14.5000" Available="1"/>

<Widget Name="Green Widget" Price="24.9500" Available="1"/>

blog comments powered by Disqus
SQL SERVER ARTICLES

- 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 
Support 

Developer Shed Affiliates

 




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