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.
Building Your First SQL Server 2000 Database - Creating a database with Enterprise Manager (Page 2 of 5 )
Before we create our first SQL Server 2000 database, let me fill you in on a couple of things. Firstly, SQL Server 2000 is a RDBMS (Relational Database Management System), meaning that it allows you to create, update and administer relational databases. A relational database is a collection of data items that can be accessed and manipulated in many ways without actually changing the organization of the tables within that database.
Secondly, SQL Server 2000 supports its own dialect of SQL (Structured Query Language), which is known as Transact-SQL, or TSQL for short. TSQL is an extremely powerful and advanced set of queries and commands that can be used to create, modify and delete databases, tables, triggers, constraints, etc. It's based on the ANSI SQL 92 standard but takes this standard into its own hands if you like, adding that extra touch of flexibility and robustness to the commands that it supports.
Thirdly, as with many RDBMS's, there's always more than one-way to accomplish a particular task. For example, Enterprise Manager lets you create tables using a point and click interface, which is similar to the way you create applications in Visual Basic. Query Analyzer on the other hand acts more like C++, allowing you to get right down to the bare metal and hand code everything yourself. Throughout this article we will be accomplishing many things firstly with Enterprise Manager, and then with plain TSQL code in Query Analyzer.
Now that we've got a bit of jargon out of the way, let's create our first database. Jump onto the PC where you installed SQL Server and click Start -> Programs -> Microsoft SQL Server. You should see the following menu:
The two programs that we're interested in working with are Enterprise Manager and Query Analyzer, so click on the Enterprise Manager item to load it up. When it loads, you'll notice two panels: the first is a tree view and the second is a list of items contained within the selected node of that tree.
Expand the "Microsoft SQL Servers" branch in the left panel and also expand the "SQL Server Group" branch. Enterprise Manager should look something like this:
My computers Net BIOS name is SERVER, and as you can see in the screenshot above, SQL Server represents my computer as a node called SERVER. Obviously this node will appear with your computers Net BIO name next to it. Expand this node and you'll see a number of folders appear in the right pane: databases, data transformation services, management, replication, security, support services and meta data services. In this article we're only concerned with databases, so expand the databases tab.
Each installation of SQL Server 2000 also includes a master database. One of the most important tables in the master database is the sysdatabases table, which contains a list of database that reside on your SQL Server. You can view the contents of the sysdatabases table in your master database by expanding the master node under your databases node in the left pane, clicking on its tables node, and then finding a table called sysdatabases in the right pane.
If you can't find the sysdatabases table then click on the right pane and start to type the first few letters of sysdatabases. Enterprise Manager will highlight the table for you. Once you've found it, right click on it and choose the Open Table -> Return all rows option.Take a look at the various fields and values in the table and then close it by pressing Ctrl+F4.
If you take a look at the list of databases under the databases node in the left pane, then you'll also notice the Northwind and pubs database, which come bundled with the SQL Server 2000 installation. These databases are not tied to the workings of SQL Server 2000 in any way, and you're free to do whatever you want with them.
Instead of playing around with the Northwind or pubs databases however, let's instead create our own. Right click on the databases node in the left pane and choose the New Database option. The database properties dialog will appear:
The dialog contains three tabs, however all we really need to do to create a new database is give it a name, so enter MyDatabase1 in the name field and click OK. If you're feeling confident then you can set the location of the database file and its growth settings by clicking on the Data Files tab. By default our new database will be created as C:\Program Files\Microsoft SQL Server\MSSQL\data\MyDatabase_Data.MDF, but you can change this to any location that you like. You can also change the location and growth settings of the transaction log for our database by clicking on the Transactions Log tab, but we won't worry about that for now.
If you take a look at the databases node in the left pane then you'll see that our MyDatabase1 database now exists in the list. Expand the MyDatabase1 node in the left panel. We're now going to create a table within it. Right click on the tables node and choose the New Table option. This will load the table designer, which allows us to add, edit and delete fields both to and from new and existing tables.
Each row in the designer represents one column in the table. Let's create a table that will hold the details of a set of widgets. Click on the first Column Name field and enter the value widgetId. The column name field is the name by which we refer to each particular column in the table, which we will see shortly. Click on the Data Type field for widgetId and choose the int option, which represents an integer, or whole number. Notice that by selecting a data type, the length and allow null fields are automatically completed for us. We want the widgetId to always contain a value however, so uncheck the Allow Nulls checkbox.
To make sure that each widget in our table is unique, we'll make the widgetId field a unique primary key. We can do this by modifying the columns details at the bottom of the table designer window, like this:
Notice how I've set the identity value to Yes and also set the seed (the number that the column will have initially) and increment (the number of values by which the field will increase when a new record is added) values to 1.
Now that we've created a unique primary key column for our widgets, click on the next Column Name field and enter widgetName. Make this column a varchar (variable length character) and set its length to 20. Again, this column must contain a value, so uncheck the Allow Nulls table.
To keep our table simple, we will create just one last field. Click on the next Column Name field and enter the value widgetPrice. Make this column a money value that can't be null. Using the column properties tab at the bottom of the table designer window, change the Default Value of the column to 0.
Press Ctrl+F4 to close the table designer and choose Yes when asked if you'd like to save the changes to Table1. When prompted for the tables name, enter Widgets and click OK. If you take a look in the right pane of Enterprise Manager, you'll see our new table, Widgets.
It's all good to have a new table, but what use is this table if it doesn't contain any data? Right click on our Widgets table in the right pane and choose the Open Table -> Return all rows option. Normally you'd be presented with a complete list of all records in the table at this point, but because our table is empty, all we see are the column names and one empty record.
This empty record is the key to populating our database with records from within Enterprise Manager, so click on the empty widgetName column and enter the value "Red Widget". Click on the widgetPrice column and enter 9.95. Press enter when you’re done and you've just added a new record to our Widgets table!
Repeat this process with the following values to add more widgets to our table:
Notice how I've left some widgetPrice values blank? When you don't enter a value for the widgetPrice field, SQL Server sees that you've set a Default Value of 0 for that column and puts this value in automatically. Close the table by pressing Ctrl+F4.
Believe it or not, we're done creating our first database with Enterprise Manager... see how easy it was? We didn't have to use one bit of TSQL because Enterprise Manager handled everything behind the scenes for us.
I don't know about you, but I'm more of a old-style programmer and I like to create my code by hand. In the next section we're going to do exactly that with the use of Query Analyzer.