Creating And Altering Tables In Microsoft SQL SERVER 2000
(Page 1 of 3 )
We have already covered some hardcore details about RDBMS, and how it evolved in the previous tutorial. We also dealt briefly about designing logical databases and how to build database connected systems. We concentrated more on SQL Server 2000 and .NET technology; it's new features and extended support. In addition enumerated in detail about the various Database Objects in SQL Server 2000, and discussed at length about the first database object-the database itself.
At this point, you have a solid foundation on how to create our own database using the complex CREATE DATABASE syntax, which will come in handy during real time application development, or for verifying and editing codes. In today’s tutorial we will fine-tune our skills on creating a database and discuss on the concepts of adding tables to our database. The easiest way to create a database is to use SQL Server Enterprise Manager, which provides a graphical front end to Transact-SQL commands and stored procedures that actually create the database and set its properties. Open Enterprise Manager’s, Database Properties dialog box, which represents the Transact-SQL CREATE DATABASE command for creating a new user database. Only someone with the
sysadmin role or a user who's been granted CREATE DATABASE permission by a DBA can issue the CREATE DATABASE command.
When you create a new user database, SQL Server copies the
model database, which—as you learned earlier—is simply a template database. A new user database must be 1 MB or greater in size, and the primary data file size must be at least as large as the primary data file of the
model database. Kindly remember, if Enterprise Manager is used to create a database called
newdb, the default logical and physical names will be different than if you use the CREATE DATABASE command. Enterprise Manager will give the data file the logical name of
newdb_Data (instead of just
newdb), and the physical file will have the name newdb_data.mdf.
In this section, we will be studying the syntax to create our own tables. We will also take a look at how to make use of the Enterprise Manager to help us with this, only after we know how to do it ourselves. For it is always better to understand the basics of how it works. {mospagebreak title=Create Table Made Easy} The first part of creating a table is pretty much the same as creating any object. Remember the syntax we already learned, well here it is again
Create <object type><object name>
Since a table is what we want we can be more specific:
Create table customer
With Create Database, we could have stopped with just
these first three key words, and SQL Server would have built a database based on the guidelines established in the
model database. With CREATE TABLE syntax however there is no “model” table (unlike “model” database) set up in the Server to mimic it, we have to provide all the specifications in the form of columns, data types and special operator.
Create Table [database_name.[owner].]table_name
(<Column name> <data type>
[ [DEFAULT <constant expression>]
|[IDENTITY [ (seed, increment) [ NOT FOR REPLICATION]]]
[ROWGUIDCOL]
[COLLATE <collation name>]
[Null| NOT NULL]
[<Column constraints>]
[column_name as computed_column_expression]
[<Table constarints>]
[,…n]
)
[ON {<filegroup>|DEFAULT}]
[TEXTIMAGE_ON {<filegroup >|DEFAULT]
Now that’s a handful, believe me it still has sections
taken out of it for simplicity sake! Don’t worry; let’s look at it part by part starting with the second line.
Next: Bit By Bit >>
More ASP Articles
More By Gayathri Gokul
/ 50 
