Altering and Dropping Databases And Tables In Microsoft SQL Server 2000, Part 3

It is trivial that we master some of the basics T-SQL, which you will be using quite often as programmers in either building new applications or modifying and supporting existing ones. Today we will see how to alter or modify existing databases and tables and also examine some drop statements.

The Alter Statement

Sometimes, in fact far more often than we would like, we get requests to change a table rather than recreate it. Similarly, need may arise to change the file size, its location or add some new features to our database. This is where our ALTER statement comes in.

 ALTER
<OBJECT type="">
<OBJECT>

The above syntax is pretty much the bare bone structure on which we build our syntax to various complex ALTER issues, which we will encounter in real time coding.

ALTER DATABASE provides the ability to change the database name, filegroup names, and the logical names of data files and log files. Let’s get right into our job by taking a look at how to change our database. In our example below we actually make a couple of changes just so that we can see the effect in our result and how the syntax works. The trickiest part about ALTER statement is remembering what already exists. Having said this let us take a look at the Customers database which we had created in the very first part of this tutorial. Let us look at it again to examine what we had created by using the command below.

EXCEC sp_helpdb Customers

Name	db_size	Owner	dbid	Created	Status	Compatibility
Customers	20MB	sa	10	May 24, 2002	Status=online, Updateability=Read_write, UserAccess=MULTI_USER, Recovery =FULL Version=538, Collation=SQL_LATIN1_ General_CP1_C1_AS, SQLSortOrder =52, IsTornPageDetectionEnable, IsAutoCreateStatistics, IsAutoUpdateStatistics	80

And the following table: (Please note just giving you approximate file sizes, not exact figure).

Home	Field	Filename	Filegroup	Size	MaxSize	Growth	Usage
Customers	1	‘C:\Program Files\Microsoft SQL Server \MSSQL\data\Customerdata.mdf’,	PRIMARY	10240 KB	51200 KB	5120 KB	Data Only.
Customer Log	2	‘C:\Program Files\Microsoft SQL Server \MSSQL\data\Customelog.ldf’,	NULL	5120 KB	25600 KB	5210 KB	LOG ONLY.

Now let us say we want to change things a bit. A current database is only 20MB in, say we want to import large amount of data from another database. Since we have Autogrowth turned on; SQL Server would automatically grow as we start importing data at a rate of 5 MB each time. But if you were inserting 100MB of data, then the SQL server would have to deal with that relocation at least 16 times (at 25, 30, 35 MB etc.) this is really a lot of work on the server. Since we know we are going to add some 100MB data, we would do so easily using ALTER DATABASE command. blog comments powered by Disqus