Table 10-1 Product_Info Table
Product_ID Product_name Description Price Brand_id1 蓬 人用 8用 使用 环 6 一 6 环 一 一 一 一 一 一 一 环 一 环 一 一 一 一 一 一. 一. 一. 环. 环. 一 一 环. 环. For example, the data line data type (Data type) is defined to define the scope of the data (such as the character length, etc.), and SQL Server provides several data types for users to choose, or the user's custom data type (You can only add data type, you can't change the data of the system already existing). The data type using the system data-type other data sheet can be set to the following properties: the data can be included in the data, such as the character of the character, integer, integer or image (image) The size or length of the effective digits (settings for numerical data type), that is, the number of digits decimal (settings other than the numerical data type) can be included in the data line. Number range data type also affects the visual information line, the pre-depreciation parameters, variables, and T-SQL functions transmitted back to one or more data values. Table 10-2 lists the built-in data provided by SQL Server. SQL Server 2000 introduces three new data types - Bigint, SQL_VARIANT and TABLE. (In addition to the special cases indicated in the information sheet, all objects use the same basic data type).
Table 10-2 Server 2000 system data type
Data type indicates that the integer (full number) of the size of the Bigint8 unit group (full number) 8 bit tuple binary [(n)] n b positioning information is fixed. n must be between 1 and 8000. When the data project size is consistent in the data line, binary should be used. N 4 bit tuple Bit integer data, its value is 1,0 or NULL. Index cannot be established on the Bit data line. For 1 bitmap space in the data sheet in the data sheet; the data line for 9 to 16-bit yuan uses 2 position tuning spaces, and pushes the non-Unicode of the fixed length in this class. The character data, the value of the n bitmage group N must be the most 1-8000. The reference of the CURSOR data indicator is only used for variables and pre-preserved program parameters. DateTime Date and Time Information, from January 1, 1999 to December 31, 9999, accuracy is 300 mums of 8 positions of the group decimal [(p, [s])] or numeric [(P , [S])] The number of fixed effective bits and decimal digits (NuMERIC is functionally equal to Decimal). Accuracy (P) Specifies the maximum value of the storage bits (including the left and right sides of the decimal point). The decimal number (s) specifies the maximum value of the number of stored bits on the right side. The decimal number should be less than or equal to the effective number of bits. The minimum value of the effective bits is 15 to 17 positioning tuples, depending on the effective bit, the maximum value is 28. If SQL Server is started with the -p parameter, the maximum value is up to 38. Float [(N)] Digital information of floating point digits, from -1.79e 308 to 1.79e 308. n is a decimal number of floating-point numbers, from 1-53.4 to 8 position tunages, depending on the accuracy image variable length, the maximum length is 2 ^ 31 - 1 (2, 147, 483, 647) A bit group. Image Data Line The project is an indicator of the image data value storage location. This information is stored separately from the actual data sheet. Indicators can allow 16 positions of INTEGER OR INT integer (full numbers) data from -2 ^ 31 (-2, 147, 483, 648) to 2 ^ 31 - 1 (2, 147, 483, 647). The 4-bit tuple Money currency data values range from -2 ^ 63 (-922, 337, 203, 685, 477.5808) to 2 ^ 63 - 1 ( 922, 337, 203, 685, 477.5807), accuracy to a million currency unit. 8 position tunnel nchar [(n)] fixed length Unicode characteristics, N value ranges from 1 to 4000. Each Unicode is stored in 2 position tuples and supports international character. The 2 position tunnel multiplied by NTEXT variable length of NText variable length, the maximum length is 2 ^ 30 - 1 (1, 073, 741, 823) a word element. NTEXT data line project is an indicator of the data value location, which is stored separately from the actual data sheet. Indicators can allow 16 positions; and the number of the input data is multiplied by the Unicode characteristics of the 2 position tuple nvarchar variable length, the maximum length is 4000 words. Each Unicode is stored in 2 position tuples and supports international character. The 2 position tunnel multiplied by the digital data of the number of the number of the number of characters, from -3.40e 38 to 3.40e 38. The synonym of Real is Float (24).
4 positions of the small group SmallDateTime Date and time information, from January 1, 2079, June 6, 2079 (Datetime data type is more accurate) 4 position tunages Smallint integer data, from 2 ^ 15 (-32, 768) To 2 ^ 15 - 1 (32, 767). The 2-bit tuple Smallmoney currency data value is from -214.748, 3648 to 214, 748.3647, accuracy to a thousand-toteen currency unit. 4 position tuples SQL_VARIANT This data is not stored in TEXT, NTEXT, TIMESTAMP and SQL Server supported by SQL_VARIANT. SQL_VARIANT can be used in the data line, parameters, variables, and back to the value of the user's custom function, and SQL_VARIANT allows these database objects to support other data types. SMSNAME is a user-reserve data sheet for a system support, and SysName is defined as NVARCHAR (128) for SQL Server to refer to the name of the store. 256 unit group table is just like using a temporary storage table - the data line list must be included in the data line. Can be used to store region variables or the return value of the user's custom function. Depending on the non-Unicode data of the TEXT variable length, the maximum length is 2 ^ 31 - 1 (2, 147, 483, 647) a word element. It is an indicator of the data value position, which is stored separately from the actual data sheet. The indicator requires 16 positions of the TimeStamp full number, each time the number is updated whenever a data is updated, and this number is updated. A data sheet can only have a TimeStMap field Tinyint integer data, from 0 to 255.1 unit Unique Identifies (GUID). The 16-position group Varbinary variable length binary data, the actual length of the maximum length input data is 8,000 bit groups. When the size of the data line is inconsistent, Varbinary can be used. VARCHAR [(N)] variable length non-Unicode data, the maximum length of the actual input data is 8,000 characters.
Choose the correct data type to choose the correct data type to build a library important part. The information values stored in the data line must be within the allowable range and refuse to store information not inform (for example, when the data line is only allowed to enter the digital value, the data input cannot be allowed when entering the word value). Once the data type is specified, the above is avoided. The following two questions can help you decide what information of the specified: Do you agree with this data? Is the data set to a fixed length or variable length? Choosing the appropriate data type is very intuitive, that is, the information entered in the information line can be in accordance with the data specified by the data line. Therefore, you want to predict the scope of the information, this range can cover the range of future data values, but no waste is ware. Waste space refers to our scope of the data value to a data line, but the range is set too loosely and wasts the reserved space. For example, assuming that you need a data line to store integer data from 1 to 100, of course, you can set the data line to an Integer data type, but each integer takes up 4 positions of space groups. If the data is used as the Tinyint data type (the value between 0 and 255), only the space of 1 bit group is occupied. Therefore, this information is recommended to Tinyint. Another thing to consider is that the data must use a fixed length or a variable length data type. If all the values in the data line are almost the same size, the fixed length is recommended. As for the variable length data type, it is applicable when the size of the data value is not fixed, or when the data itself is not changed frequently. Variable length data types include VARCHAR, NVARCHAR, VARBINARY, TEXT, NTEXT, and IMAGE. Elastic use variable length data type can not significantly save storage space. If you define the data line to a fixed length, the shorter data value in the data line also occupies the same size storage space, which is not as variable to save space. Then why don't we set all the information to a variable length? In fact, it is because the system has a long time to handle variable length information line. Therefore, if it is determined that the length of the data value is fixed, it is still a fixed length. Use the system data type to establish a product_info data sheet before starting, first look at the T-SQL CREAT TABLE command (to establish a Product_INFO Data Sheet). In this example, you will only use the data type provided by the system information and the fixed length data column. When using the T-SQL command to establish a data sheet, the data table is built on the database. To use a repository, use the USE DATABASE_NAME command according to the following syntax. In this example, our database is named mydb. Keyword Go indicates that all commands in front of the column should now be executed. (See Chapter 13 Using T-SQL Detailed Content.) Uses MYDB
Go
Create Table Product_Info
(
Product_id Smallint,
Product_name char (20),
Description Char (30),
Price smallmoney,
BRAND_ID Smallint
)
Go
After entering the CREATE TABLE command, specify the data table name as Product_Info. In the middle of parentheses, the data type of each data line is defined after the data line name. Two CHAR data types are set to 20 and 30 (depending on the product_name and the description, it is within this value range). Since we expect more than 255 products and purchasers, less than 32,767 products and purchasers, in order to avoid waste space or space, set Product_ID and BRAND_ID to Smallint data (Tinyint range from 0 to 255 And the scope of Smallint is to 32, 767), not Tinyint or int. The user's customized data type is self-reserved, or the alias data type is self-reserved system data type. When several data sheets are different from the same information line, the self-subscribing type may be utilized if the corresponding data is the same type, length, and NULL value attribute. You can use the descriptive name to define the data type, the process is simplified, and the consistency is maintained. For example, now there are two data sheets in the same database, one for the Brands data sheet; one is a product_info data sheet. The BRAND_ID data column in the Brands Data table has the same data type, and the NULL value is not allowed. At this point, you can build the user's customized data type and assigned to these two data. Now suppose there are several data sheets, and several of them need the same properties. You may not remember that the information in one of the data sheets is using smallint or tinyint, or whether NULL value is used. However, if you have previously used the descriptive name to create a user-reserve data, you can use the custom data type, but don't worry about the actual data type. Note To create a user-reserve data file in a specific database, it can only be used in the database. However, if you establish a data type in the Model Database (Database Template), you can use it in all newly created databases.
When establishing the user-reserve data type, the Enterprise Manager is established, and three information must be provided: the data type is different. New data type is not based on which system data type. Data-type NULL attributes, that is, whether the NULL value is allowed (in this chapter ) will be discussed in detail). When the above information is determined, Enterprise Manger can be used to establish a user-reservation data sheet:
In Enterprise Manager, expand the SQL Server group and then expand the server.
Expand the database folder, expand a database, as shown in Figure 10-1.
Figure 10-1 Using Enterprise Manager Defining Data Division
Press the mouse button on the user's custom data type, select the new user custom data from the quick function table. A user-based information window appears. Enter a new data type in the name column. Temporarily named BRAND_TYPE, as shown in Figure 10-2.
Figure 10-2 "Data-type Self-Customized Property" Window
Then you must specify the SQL Server system data type and length refer to the user's customization. Take this example as an example, first define the information of the supplier code data column (the preset length value is 5). If you create a text data, you can define the length. If the data type is allowed to allow null values, select Allow NULL. (For detailed information of null values, please refer to this chapter Sections.) If the data type uses a predefined rule or preset value, select them from the clear order. (Rules and preset values will be discussed in detail in Chapter 16.) Press OK to store new data type. Use Enterprise Manager to delete users Customs Data You can use the following steps to remove unwanted users Customs Data: In Enterprise Manager, find the user's customs Data type to be deleted (expand SQL Server group) / Server / Database Data Clip, and then unfold to delete a data type, as shown in Figure 10-3.
Select the user's self-reservation data clip. The user's custom data is displayed in the right pane, as shown in Figure 10-3.
Figure 10-3 "Users Custom Data Division" data clip
The user's self-reservation data is pressed on the user's self-reservation, and the right button is selected in the quick-changing function table, and enter the removal of the object, as shown in Figure 10-4.
Figure 10-4 "Removing the object" dialogue
Before you really delete the data type, press the case where the dependencies enter the dependence dialogue, as shown in Figure 10-5.
Figure 10-5 "Dependency" dialogue
The clear unparallelfes on the left side of the dependent dialogue are displayed in the user's customized data type, and the right-signed unparalleled blocks show the objects depending on the user's self-reservation. If any information list or object is used, the system will not be deleted. Otherwise, an error message will appear, as shown in Figure 10-6.
Figure 10-6 Deleting the error message that appears in use in use
If you want to delete the data type problem, you can close the dependency dialogue, press to remove all the discharge in the object window, that is, delete the data type. Don't worry, remove all refers to deleting the data file displayed in the object window instead of all user custom data type. Establishing and deleting User Customizing Self-Customization Self-Customization Self - SP_AddType is a T-SQL command for defining user data type. When using the Model library, do this command, you will make all the newly created user subscription libraries use this new data type (because the built-in user custom data is used by the MODEL database. The property is the same). When you perform this command when you use the user's self-reservation, you will only let the database use the new data type. (Remember, use a certain database, you must perform the use database_name command.) The following T-SQL command has established a user-reservation data in the Model database: BRAND_TYPE.
Use model
Go
sp_addtype brand_type, 'smallint', 'not null'
Go
The three parameters of sp_addtype are: user-reserve data type, which is based on what system data type, and the new data type NULL value attribute. The new data type BRAND_TYPE will appear in all user-reserved databases. If you have created the user's self-reservation data in the user database, you must see this type in Enterprise Manager, select Reasonably on the ENTERPRISE Manager's execution function table. To delete an unused user's custom information, execute the sp_droptype command in defining its database. The following demonstration removes Brand_Type from the Model database: use model: USE MODEL
Go
SP_DROPTYPE BRAND_TYPE
Go
Use the user's custom data type Also build the product_info and the BRAND data table first returns to the quotation table. Re-establish the Product_info Data Sheet using the custom data type, re-establish the Product_info data sheet, and then build a BRAND data table. There will be a Brand_ID in the Brand Data Sheet and Product_info, we will use the same user's custom. First, first delete the old product_info data sheet, then re-establish, the steps are as follows:
Use mydb
Go
DROP TABLE PRODUCT_INFO
Go
Create Table Product_Info
(
Product_id Smallint,
Product_name char (20),
Description Char (30),
Price smallmoney,
BRAND_ID BRAND_TYPE
)
Go
Create Table Brand
(
BRAND_ID BRAND_TYPE,
BRAND_NAME CHAR (30),
Supplier_id smallint
)
Go
Specify Brand_Type's data type to the BRAND_ID data line in the two data sheets, you can determine the same properties of the two data, without remembering the base data type. It is usually necessary to remember that the user's customized data is the DBA and application programming designers. DBA is not said to be aware of these, and the program designer may need to know the data type when the writer code is required. As for the user, it is not necessary to know these details. Establish a data sheet in the Archives Group If you have established a user-defined archive group, SQL Server allows you to specify which archive groups stored in. In the preset, the data sheet will be stored in the main archive group unless another archive group is designated as the preset archive group. The archive group can across multiple disks or disk arrays. To learn more about how information is stored in the archive and archive group, see Chapter 9. Create a product group in the archive group assumes that we create a profile group named Product_Group in the MYDB database. This archive group is placed in the E disk where the secondary archive group is located, and the main archive groups In the C disk. This trick allows us to separate the information and information sheets and SQL Server system data sheets. We will also establish a record file in disk F to separate record files I / O (see Character 9 of this book Using the archive group to establish a database).
Use master
Go
Creat Database MYDB
On Primary - Clear Definition Main Archive Group (Selective)
- Main information archives
(Name = mydbroot,
Filename = 'c: /msq12k/mssql/data/mydbroot.mdf'
Size = 8MB,
MaxSize = 10MB, FileGrowth = 1MB),
FileGroup product_group - Archive group in the next file
(Name = mydbdata1, - Subject profile
FileName = 'E: /Mssq12k/mssql/data/mydbdata1.ndf',
Size = 100MB,
MaxSize = 1500MB,
FileGrowth = 100MB)
LOG ON
(Name = logData1, - Recording file
FileName = 'f: /long_files/logdata1.ldf',
SIZE = 100MB
MaxSize = 1500MB,
FileGrowth = 100MB)
Go
Now you can use the create table command, build the Product_info data table in the Product_Group archive group, the method is as follows:
Use mydb
Go
Create Table Product_Info
(
Product_id Smallint,
Product_name char (20),
Description Char (30),
Price smallmoney,
BRAND_ID BRAND_TYPE
)
On Product_Group
Go
The new information list and all inserted data sheets will be placed in the defined product_group's E disk, so as long as there is no other information table in the same archive group, I / O in the Product_info data sheet is available. A specialized disk. Using NULL Value Null Value means an unknown value, we will be named NULL. The NULL value attribute of the data line refers to whether the data line accepts or rejects the NULL value. NULL values in a data line usually indicate that there is no value input in the data line, possibly because this value is unknown, not applicable, or will be added later. NULL values are neither this value, nor 0; their actual values are unknown. Therefore, any two NULL values will never wait. So when will NULL values? For example, when the customer does not provide information on all information columns, a null value is required. For example, the customer's URL, perhaps the customer does not have an URL. This information is not entered. At this time, the NULL value in a data column will make sense, that is, the information listed now has not decided. For general rules, avoid using NULL values as much as possible because this increases complexity when queries and updates, and some options (such as main index keys and Identity properties) cannot be used in data containing NULL values.
Related Information In SQL Server "Online Book", enter "Null Value" keyword, select Comparison Search Conditions in the list to find relevant information. Or in this chapter section mentioned more information.
You can specify a preset value for the data line to avoid using NULL, so if a data line does not enter a value, the data is automatically filled in the preset value. (For more information on preset, please refer to Chapter 16.) If null value is allowed in a line of data, NULL will enter this information in two ways: If you insert a data column in the data sheet, but not Empty information column specifies the data value, SQL Server assigns a NULL value for the data line (unless the data has specified a preset value). Users can manually enter NULL this word. Please note that do not input quotation marks (") to avoid confusion as a string NULL. Use null to establish a product_info data sheet back to the sample_info data sheet, now add a null value option for each data line. If you want some The data is allowed to allow NULL, please add NULL after the data type. If you do not want the null value, add NOT NULL after the data is not allowed, unless you use the user's custom data type, and the data type There is a custom NULL or NOT NULL that specifies whether the NULL value is a good habit for the data line. This helps you take into account the NULL value attribute of the data row each time you set a data line. Related Information in SQL Server "Online Book", enter "CREATE TABLE", look down on the null value attribute rules theme in the data sheet, you can find the rule of the NULL value attribute when NULL or NOT NULL is not clearly specified.
Back to the sample of the Product_info data sheet, the product list is defined for the product list to accept the NULL value. Since the BRAND_TYPE data is not previously defined as NOT NULL, there is no need to specify NULL value attributes for the BRAND_TYPE data type. The new Create Table command is as follows:
Use mydb
Go
DROP TABLE PRODUCT_INFO
Go
Create Table Product_Info
(
Product_ID Smallint Not Null,
Product_name char (20) Not null,
Description Char (30) NULL,
Price SmallMoney Not Null,
BRAND_ID BRAND_TYPE
)
Go
Now, if you do not specify the value described in the product, the values of the other four data lines have specified (in the product line of Product_ID, Product_name, Price, Brand_ID do not accept NULL values), when entering a product In the data sheet, the Description data is inserted into NULL in the new information column. If you do not enter information in the data line that does not accept NULL, the information column will not succeed. When adding the Identity property to establish a data sheet, you can add the Identity property in the data line definition to specify a piece of data as the identification item. If the data is created, it allows the newly inserted data to automatically prepare the data column value by seed and increment value. Seed is the identification number specified by the first data column inserted into the data sheet. Each time a data column is inserted, the next recognition value is generated by adding the increasing value to the initial value, this identification value guarantees the uniqueness of its data sheet. If each of the information rows require a unique identification number to determine the uniqueness of its information column (such as Product_ID Data Line), identifying the numbering attributes is very applicable, there is no need to consider the number to get every time you add a number of information columns. In a data sheet, the identification data line is usually used as a main index key condition to use its uniqueness to identify a data column. More information about the main index key conditional constraints can be found in Chapter 16 of this book. For example, if IDENTITY (0, 10) is specified, the value of the identification data line inserted into the first column is 0, and the second list is 10, and the third list is 20, and so on. If not specified, the preset seed value and the increment value are (1, 1). You must specify that two parameters or simply do not specify. There is no preset value in the recognition data line, nor the NULL value is allowed. A data sheet can only have one identification information. In the case of the preset, the identification data cannot be directly inserted into the information, and cannot be updated. If you want to re-insert the already deleted column, and you want to keep the original identification value, you can use the following description to modify the preset settings: Set Indetity_Insert Tablename ON
Use this statement to insert the data column and specify the value of the identification line. After completion, you should use the following command to refuse the identification information line to insert information:
Set Identity_INSERT TABLENAME OFF
After the above command is performed, the SQL Server will be incremented by the maximum value of the identification value next time. Add Identity attributes in the Product_INFO data sheet. Now we will increase the Identity property in the Product_info tab. We will use the Product_ID Data to the identification line, this data is not manually input, and the SQL Server automatically generates the identification value to ensure the uniqueness of the identification value. Below is a T-SQL syntax for establishing a table:
Use mydb
Go
DROP TABLE PRODUCT_INFO
Go
Create Table Product_Info
(
Product_id Smallint Identity (1, 1) Not Null,
Product_name char (20) Not null,
Description Char (30) NULL,
Price SmallMoney Not Null,
BRAND_ID BRAND_TYPE
)
Go
The value of the Product_ID Data will start from 1 and in 1 as a subsequent insertion into the data column of the data column in the data sheet to ensure that each product is uniquely identified, without relying on the user to enter the value. The increment value can be arbitrarily selected, but no matter what the increment you use, the identification value will be unique. Using Enterprise Manager Establishing Information Table Now let's use Enterprise Manager to build a document. Remember, before actually establishing the database, you should first clear all the database data sheets and inter-inter-inter-inter-inter-inter-inter-interoperability. To build a database data sheet using Enterprise Manager, follow these steps: Expand the SQL Server group in Enterprise Manager to expand the database. Expand the database folder and review the existing database. Expand the database you want to operate, here we use myDB. In the data sheet data clip, press the right button to display the fast display function table, select the new information table. A new information table window appears, as shown in Figure 10-7 (this is the maximized illustration).
Figure 10-7 "New Data Sheet" Window
The new information table window contains a checkered in the same way. Each column in the square represents each of the information sheets; each of the data lines in the square represents attributes - such as data type, length, and null value properties.
Note When you are named after the information list, you should use a certain standard. What naming standard is not important, but the logic of naming must have its consistency. This consistency can avoid confusion when performing queries.
Define each of the data lines in the database (defined by one column), in the name of the data line name, select the drop-down list in the data type data line, define the data type. Select the length of the data type and the data column, whether to press the SHIFT key in the NULL value data column or in the box in the data column to allow NULL values (not ticking, no NULL value) . The Product_Info data sheet is shown in Figure 10-8. Please note that in Brand_ID's data line, we set the data type to the user's self-reserve data type BRAND_TYPE. It should be noted that although we have defined the BRAND_TYPE to define the NULL value when self-reserve, it will still be set to a preset (tick) in the NULL value of the NULL value. So here you must allow the NULL value to be canceled to maintain the consistency of the data type NULL value attribute. The storage order of the data will be stored in the order of your defined data line. If you want to insert a new data column between the input data, click the right button to click the right button to call the fast display, select the insert information, if you want to delete, choose the information you want to delete. After pressing the right button, select the delete data line in the quick function table. Now we will set the Product_ID data line into a primary index key in the Product_INFO data table. After selecting the Product_ID data column, press the right button to select the set the main index key in the quick function table, or select the product_id data column, press the tool list directly on the key. It can be seen next to the data line name next to the key shown in Figures 10-9. Chapter 16 of this book discusses main index and other conditional constraints.
Figure 10-8 New Data Word Window Defining Data Line
In this window, there is a data line tab, which allows you to change the properties of some of the above data lines. For example, select the Brand_ID data line, then enter some information in the Data Row Tab, enter 0 in the preset value, as shown in Figure 10-10.
Figure 10-9 Key illustration of "main index key"
Figure 10-10 "Data Line" tab
Other conditional constraints and indexes can be set in the data sheet, press the right button on the data line name, select the index / index key, association, check condition constraint, or attributes from the quick function table. Or schedule a data sheet and an index property image next to the archive diagram on the tool column. Any method allows you to enter the properties window, as shown in Figure 10-11. The information sheet you have created may appear in the selected data sheet options such as Table1, Table2. We choose Table2 here, and you can change the name in the process of the next step. This window has 4 tabs. The detailed setting method of each tab will be described in this book. Figure 10-11 "Properties" window with information sheets and indexes
To name the new information table, press the archive icon, then select the name dialog box, then you can re-enter the data table name. After the name is input, press OK to save the data table and all settings. When you turn this window, you can see the new information table just now in the right window of Enterprise Manager. This chapter summarizes in this chapter, we have learned the establishment of a data sheet, including the use and custom data type, store the information table in the archive group, use the null value and add the Identity property. In fact, there are still many other properties to increase to the data sheet. You can add or delete the field, add condition constraints and indexes, and modify the information sheet. Of course, in the most ideal case, the established information is the most applicable data sheet, but this is almost impossible, because in many cases you have not considered before the information sheet is established Indexes or conditions constraints. Chapter 15 demonstrates a number of methods using T-SQL to modify the information sheet. In the following chapters, you learn to install and set up a network, and learn Microsoft Cluster Server.
