Your First SQL Server 2000 Database

Try surfing around the Internet and find a popular site that doesn't use some sort of database to store its content. I bet you'll have a tough time finding just one popular site (i.e. receiving over 5,000 unique visitors a day) that doesn't use some sort of DBMS, such as SQL Server 2000, MySQL, or Oracle.

As I'm sure you're aware, it's extremely difficult to build a dynamic Website without a way to access and manipulate your content dynamically. Back in 1995 when the majority of Websites were still serving static content (i.e. plain HTML files), database driven sites were nowhere near as popular as they are today. Why do you think this is? Are databases easier to setup today? Is Web server software more usable? Or was it just a natural progression that we as Web developers went through to "evolve" from the way we developed "back then"?

I like to think it's all of these things. Today we have databases such as MySQL and SQL Server that can be set up on a development machine with very little expertise: you simply activate the setup program, change a couple of options and you're up and running in 20 minutes or so. It also seems that databases are "the way to go" these days, which makes sense when you think about it: they're readily available (some such as MySQL are free), some are supported cross-platform, and others are supported with native providers or pure source code libraries.

In this article we're going to take a beginner's look at Microsoft SQL Server 2000. We'll look at how to create our first database, and also how to manipulate this database through both Enterprise Manager and Query Analyzer. If you've never worked with SQL Server 2000 before but have always been curious, then this article's guaranteed to get you up and running in no time.

Before we proceed, however, you should have SQL Server 2000 installed on your machine. I'm running Windows 2000 Advanced Server, but SQL Server 2000 can also be installed on Windows 2000 Server, Windows 2000 Datacenter Server, and Windows NT 4.0 with service pack 5. If you'd just like to fiddle around with SQL Server 2000 for the span of this article, then you'll be glad to know that you can download a free 120-day trial version of it by clicking here.

If you have any trouble with your installation then check MSDN or consult the reference material that was included with your copy of SQL Server 2000.

Creating a Database with Enterprise Manager

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), which means 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 commands that can be used to create, modify, query and delete databases, tables, triggers, constraints, etc. It's based on the ANSI SQL 92 standard but takes this standard into its own hands so to speak, adding that extra touch of flexibility and robustness to the commands that it supports.

Thirdly, as with many RDBMSs, there's always more than one way to accomplish a particular task. For example, Enterprise Manager lets you create tables using a familiar point and click interface similar to basic desktop databases like MS Access. Query Analyzer, on the other hand, acts more like command-driven databases such as MySQL, allowing you to get right down to the bare metal and code everything yourself. Throughout this article we will accomplish 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.