2. 计算机
  3. You are the administrator of a SQL Server 2000 computer. The server contains a database named Inventory. The Inventory database

You are the administrator of a SQL Server 2000 computer. The server contains a database named Inventory. The Inventory database

admin2009-05-19  7
问题 You are the administrator of a SQL Server 2000 computer. The server contains a database named Inventory. The Inventory database has a table named StorageLocations that stores the location of parts contained in your company’s warehouses. The StorageLocations table is configured as shown in the exhibit.


The LocationDescription field is usually described with a name 10 to 25 characters in length. The locations never store more than 100,000 units of any given part.

You want to modify the table’s schema to save space. You cannot lose any existing data. You want to do this by using the minimum amount of administrative time and server resources.
Which Transact-SQL statement should you execute?
选项 A、ALTER TABLE [dbo].[StorageLocations]
ALTER COLUMN [UnitsStored] [int] NOT NULL
B、ALTER TABLE [dbo].[StorageLocations]
[LocationDescription] [char] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
C、ALTER TABLE [dbo].[StorageLocations]
ALTER COLUMN [UnitsStored] [smallint] NOT NULL
D、ALTER TABLE [dbo].[StorageLocations]
[LocationDescription] [nvarchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
答案A
解析 Explanation: In this scenario there are two considerations that must go into the table schema modfication. The space allocated to the LocationDescription and the use of data types. The scenario states that the LocationDescription field is usually described with a name, 10 to 25 characters in length.

The locations never store more than 100,000 units of any given part. In the table the parts is represented by the PartID column; the amount of units by UnitsStored Storage column; the locations by the LocationID column and the LocationDescription field by the LocationDescription column.

Because the LocationDescription field is usually described with a name, 10 to 25 characters in length, does not mean that the character length of this field is never longer than 25 characters. We thus cannot reduce the character length of this column. We must thus turn our attention to the UnitsStored column. SQL Server 2000 has four integer data types. These are bigint, which has a storage size of 8 bytes and is whole numbers that range from -9223372036854775808 through 9223372036854775807; int, which has a storage size of 4 bytes and ranges from -2,147,483,648 through 2,147,483,647; smallint, which has a storage size of 2 bytes and ranges from -32,768 through 32,767; and tinyint, which has a storage size of 1 byte and ranges from 0 through 255.

As no more than 100,000 units of any given part will be held in storage and int ranges from -2,147,483,648 through 2,147,483,647, this data field can be represented by the int data type. ALTER COLUMN is used in conjunction with ALTER TABLE. The latter modifies a table definition by altering, adding, or dropping columns. Through ALTER COLUMN a specified column’s data type length can be altered.

Incorrect Answers:
B: The scenario states that the LocationDescription field is usually described with a name, 10 to 25 characters in length. This does not mean that the character length of this field is never longer than 25 characters. If we ALTER COLUMN to a char data type field with a field length of only 25, truncation of the LocationDescription will occur if the description exceeds 25 characters. We thus cannot reduce the character length of this column.

C: As no more than 100,000 units of any given part will be held in storage and int ranges from -2,147,483,648 through 2,147,483,647, this data field can be represented by the int data type. smallint, however, does not provide a sufficient range as it only ranges up to 32,767 while we require a range of up to at least 100,000.

Note: SQL Server 2000 uses four integer data types, namely, bigint, which has a storage size of 8 bytes and is whole numbers that range from -9223372036854775808 through 9223372036854775807; int, which has a storage size of 4 bytes and ranges from -2,147,483,648 through 2,147,483,647; smallint, which has a storage size of 2 bytes and ranges from -32,768 through 32,767; and tinyint, which has a storage size of 1 byte and ranges from 0 through 255

D: While the char data type is a fixed-length character data with a specified length has a storage size in bytes that is equal to the specified data length, varchar is a variable-length character data with a specified length but has a storage size in bytes that is equal to the actual length of the entered data and not the specified length of the data.

The varchar data type should be used when it is expected that the data values in a column will vary considerably in size. The use of this data type can reduce database size but this solution restricts the data length to 25 characters. The scenario states that the LocationDescription field is usually described with a name, 10 to 25 characters in length. This does not mean that the character length of this field is never longer than 25 characters.

If we ALTER COLUMN to a varchar data type field with a field length of only 25, truncation of the LocationDescription will occur if the description exceeds 25 characters. We thus cannot reduce the character length of this column.
转载请注明原文地址:https://kaotiyun.com/show/euhZ777K
本试题收录于： 微软70228题库微软认证分类
0

微软70228

微软认证

相关试题推荐
随机试题
最新回复(0)