create table (record capacity, or Size)

pts.
Tags:
SQL
When using CRTPF we also use SIZE. Example: CRTPF FILE(MYLIB/NEWFILE) SIZE(10000 1000 9) I searched for the same possibility via SQL but can?t find it. Is there a way with a SQL statement 'CREATE TABLE' to do the same?

Answer Wiki

Thanks. We'll let you know when a new response is added.

The CREATE TABLE statement does not allow you to specify the size of a table. The only way that you can limit the size of a table is to create a separate file to contain the table and then use the CREATE TABLE statement to put the table into that file.

From the SQL Server 2005 books on-line:
USE master;
GO
– Create the database with the default data
– filegroup and a log file. Specify the
– growth increment and the max size for the
– primary data file.
CREATE DATABASE MyDB
ON PRIMARY
( NAME=’MyDB_Primary’,
FILENAME=
‘c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLdataMyDB_Prm.mdf’,
SIZE=4MB,
MAXSIZE=10MB,
FILEGROWTH=1MB),
FILEGROUP MyDB_FG1
( NAME = ‘MyDB_FG1_Dat1′,
FILENAME =
‘c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLdataMyDB_FG1_1.ndf’,
SIZE = 1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB),
( NAME = ‘MyDB_FG1_Dat2′,
FILENAME =
‘c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLdataMyDB_FG1_2.ndf’,
SIZE = 1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB)
LOG ON
( NAME=’MyDB_log’,
FILENAME =
‘c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLdataMyDB.ldf’,
SIZE=1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB);
GO
ALTER DATABASE MyDB
MODIFY FILEGROUP MyDB_FG1 DEFAULT;
GO

– Create a table in the user-defined filegroup.
USE MyDB;
CREATE TABLE MyTable
( cola int PRIMARY KEY,
colb char(8) )
ON MyDB_FG1;
GO

Notice that each file or file group has a minimum size, maximum size, and amount to grow each file when it fills up. The CREATE TABLE statement creates the table on a particular file/file group so you could put each table on a separate file if you wanted to control each one independently. Of course this introduces a lot more management overhead.

For more information you can look up “Using Files and File Groups” and “Physical Database Files and File Groups” in the Books On-Line.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Cenens
    what I did forgot to mansion is that it is on a Iseries V5R3 DB2 UDB database. IBM is talking to switch from DDS to DDL. With DDS we use CRTPF and there you can give the size of the files(tables).
    0 pointsBadges:
    report
  • Mortree
    I probably should not speak to this question having long lost any expertise. But I hope I can clarify the first fellow's answer that the two database systems manage physical implementation very differently. This is because the physical implementation of storage aspects doesn't change the logical entity relationships and conceptual solution much normally -- however code translation could be a bear. Scott is right most modern SQL servers choose to let tables and databases grow dynamically. Normally you would tune at the database level initial size, and increments to grow. SQL tends not to worry so much about size of individual tables unless they get so huge as to warrant handling as separate databases. IBM on the other hand tends to create individual tables as their largest normal object and weaves a database together as a more conceptual entity through relationship tables than a single physical unit...at least on older AS/400s.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following