Altering and Dropping Databases And Tables In Microsoft SQL Server 2000, Part 3
(Page 1 of 3 )
Transact-SQL is central to the use of Microsoft SQL Server. All applications that communicate with SQL Server do so by sending Transact-SQL statements to the server, regardless of an application's user interface.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.
Next: The General Syntax >>
More MS SQL Server Articles
More By Gayathri Gokul
/ 31 
