Building Your First SQL Server 2000 Database

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