MASTERING SQL SERVER 2000- P23

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

0
41
lượt xem
4
download

MASTERING SQL SERVER 2000- P23

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'mastering sql server 2000- p23', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:
Lưu

Nội dung Text: MASTERING SQL SERVER 2000- P23

  1. 1120 APPENDIX A • TRANSACT-SQL REFERENCE T hroughout the book, you’ve seen examples of Transact-SQL (T-SQL) state- ments. Nearly every SQL Server operation can be performed using Transact- SQL from a graphical interface such as Query Analyzer or even from the command line using OSQL. This includes operations such as setting up jobs and alerts for which we demonstrated only the Enterprise Manager steps. In this appendix, we’ve presented all of the SQL statements that we discussed explicitly in this book. For each statement, we include the entire syntax and a cross- reference to the chapter where that statement is discussed in more depth. Creating a Database CREATE DATABASE statement (Chapter 10): CREATE DATABASE database_name ON [PRIMARY] ( NAME=logical_file_name, FILENAME=’os_file_name’, SIZE=size (in MB or KB), MAXSIZE=maximum_size (in MB or KB) or UNLIMITED (fill all available space), FILEGROWTH=growth_increment (in MB or KB) ) LOG ON ( NAME=logical_file_name, FILENAME=’os_file_name’, SIZE=size (in MB or KB), MAXSIZE=maximum_size (in MB or KB) or UNLIMITED, FILEGROWTH=growth_increment (in MB or KB) ) [ FOR LOAD | FOR ATTACH ] Cursor Statements DECLARE CURSOR statement (Chapter 8): DECLARE cursor_name [INSENSITIVE][SCROLL] CURSOR FOR select_statement [FOR {READ ONLY | UPDATE [OF column_name [,…n]]}]
  2. DATABASE OPTIONS 1121 DECLARE cursor_name CURSOR APP [LOCAL | GLOBAL] A [FORWARD_ONLY | SCROLL] [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] [TYPE_WARNING] FOR select_statement Transact-SQL [FOR UPDATE [OF column_name [,…n]]] Reference OPEN statement (Chapter 8): OPEN {{[GLOBAL] cursor_name} | cursor_variable_name} FETCH statement (Chapter 8): FETCH [[ NEXT | PRIOR | FIRST | LAST | ABSOLUTE {n | @n_variable} | RELATIVE {n | @n_variable} ] FROM ] {{[GLOBAL] cursor_name} | @cursor_variable_name} [INTO @variable_name [,…n]] CLOSE statement (Chapter 8): CLOSE {{[GLOBAL] cursor_name} | cursor_variable_name} DEALLOCATE statement (Chapter 8): DEALLOCATE {{[GLOBAL] cursor_name} | cursor_variable_name} Database Options ALTER DATABASE statement (Chapter 5): ALTER DATABASE database_name SET {SINGLE_USER | RESTRICTED_USER | MULTI_USER} | {OFFLINE | ONLINE} | {READ_ONLY | READ_WRITE} | CURSOR_CLOSE_ON_COMMIT {ON | OFF} | CURSOR_DEFAULT {LOCAL | GLOBAL} | AUTO_CLOSE ON | OFF } |
  3. 1122 APPENDIX A • TRANSACT-SQL REFERENCE AUTO_CREATE_STATISTICS ON | OFF } | AUTO_SHRINK ON | OFF } | AUTO_UPDATE_STATISTICS ON | OFF } | ANSI_NULL_DEFAULT { ON | OFF } | ANSI_NULLS { ON | OFF } | ANSI_PADDING { ON | OFF } | ANSI_WARNINGS { ON | OFF } | ARITHABORT { ON | OFF } | CONCAT_NULL_YIELDS_NULL { ON | OFF } | NUMERIC_ROUNDABORT { ON | OFF } | QUOTED_IDENTIFIERS { ON | OFF } | RECURSIVE_TRIGGERS { ON | OFF } | RECOVERY { FULL | BULK_LOGGED | SIMPLE } | TORN_PAGE_DETECTION { ON | OFF } [,…n] sp_dbcmptlevel statement (Chapter 5): sp_dbcmptlevel [[@dbname=] ‘database_name’] [,[@new_cmptlevel=] version] sp_dboption statement (Chapter 5): sp_dboption [[@dbname=] ‘database_name’] [, [@optname=] ‘option_name’] [, [@optvalue=] ‘option_value’] Deleting Records DELETE statement (Chapter 7): DELETE [FROM] { table_name [WITH (table_hint […n]]) | view_name | OPENQUERY | OPENROWSET | OPENDATASOURCE } [FROM table_source] [WHERE search_conditions] [OPTION query_hints] TRUNCATE TABLE statement (Chapter 7): TRUNCATE TABLE table_name
  4. RETRIEVING RECORDS 1123 Inserting Records APP A INSERT statement (Chapter 7): INSERT [INTO] { table_name [WITH (table_hint […n])] Transact-SQL | view_name Reference | OPENQUERY | OPENROWSET | OPENDATASOURCE } { [(column_list)] { VALUES ( { DEFAULT | NULL | expression }[,…n] ) | derived_table | execute_statement } } | DEFAULT VALUES SELECT INTO statement (Chapter 7): SELECT select_list INTO new_table_name FROM table_source [WHERE condition] [GROUP BY expression] HAVING condition] [ORDER BY expression] Retrieving Records SELECT statement (Chapter 6): SELECT [ALL | DISTINCT] [{TOP integer | TOP integer PERCENT} [WITH TIES]] < select_list > [INTO new_table] [FROM {< table_source >} [,…n]] [WHERE search_condition ]
  5. 1124 APPENDIX A • TRANSACT-SQL REFERENCE [GROUP BY [ ALL ] group_by_expression [,…n] [WITH { CUBE | ROLLUP }] [HAVING search_condition] [ORDER BY { order_by_expression | column_position [ASC | DESC]] [OPTION ( < query_hint > [ ,…n ])] Rowsets CONTAINSTABLE statement (Chapter 8): CONTAINSTABLE (table_name, {column_name | *}, ‘’ [,top_n]) ::= { | | | | } | {() {AND | AND NOT | OR} […n] } ::= FORMSOF(INFLECTIONAL, [,…n]) ::= {“word*” | “phrase*”} ::= { | } {{NEAR | ~} { | }} […n] ::= word | “phrase” ::=
  6. TRANSACTIONS 1125 ISABOUT ( APP {{ A | | | } Transact-SQL [WEIGHT (weight_value)] Reference } [,…n]) Transactions BEGIN TRANSACTION statement (Chapter 8): BEGIN TRANS[ACTION] [transaction_name | @name_variable] [WITH MARK [‘description’]] COMMIT TRANSACTION statement (Chapter 8): COMMIT TRANS[ACTION] [transaction_name | @name_variable] COMMIT [WORK] ROLLBACK TRANSACTION statement (Chapter 8): ROLLBACK TRANS[ACTION] [transaction_name | @name_variable | savepoint_name | @savepoint_variable] ROLLBACK [WORK] SAVE TRANSACTION statement (Chapter 8): SAVE TRANS[ACTION] {savepoint_name | @savepoint_variable} FREETEXTTABLE statement (Chapter 8): FREETEXTTABLE (table_name, {column_name | *}, ‘freetext’ [,top_n]) OPENQUERY statement (Chapter 8): OPENQUERY(linked_server, ‘query’) OPENROWSET statement (Chapter 8): OPENROWSET (‘provider_name’,
  7. 1126 APPENDIX A • TRANSACT-SQL REFERENCE ‘datasource’;’user_id’;’password’, ‘query’) OPENDATASOURCE statement (Chapter 8): OPENDATASOURCE(provider_name, connection_string) Updating Records UPDATE statement (Chapter 7): UPDATE { table_name [WITH (table_hint […n])] | view_name | OPENQUERY | OPENROWSET | OPENDATASOURCE } SET { column_name = {expression | DEFAULT | NULL} | @variable = expression | @variable = column = expression } [,…n] { [FROM {table_source} [,…n]] [WHERE search_condition] } [OPTION (query_hint [,…n])] UPDATETEXT statement (Chapter 7): UPDATETEXT {table_name.dest_column_name dest_text_ptr} { NULL | insert_offset } { NULL | delete_length } [WITH LOG] [ inserted_data | {table_name.source_column_pointer source_text_ptr} ] WRITETEXT statement (Chapter 7): WRITETEXT {table.column text_ptr} [WITH LOG] {data}
  8. USER-DEFINED FUNCTIONS 1127 User-Defined Functions APP A CREATE FUNCTION statement (Chapter 5): CREATE FUNCTION [owner_name].function_name ( [{@parameter_name data_type [=default_value]} [,…n]] Transact-SQL ) Reference RETURNS data_type [AS] {BEGIN function_body END}
  9. This page intentionally left blank Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  10. APPENDIX B Installing Microsoft SQL Server 2000 F E AT U R I N G : The Prerequisites 1130 The Setup Wizard 1131 The Client Software 1142 Unattended Setup 1142 Upgrading from a Previous Version 1143 Installing SQL Server Yourself 1150 Installing a Second Instance 1151 The Desktop Database Engine 1153 Troubleshooting Installation 1153 Service Packs 1154
  11. 1130 APPENDIX B • INSTALLING MICROSOFT SQL SERVER 2000 H ave you ever bought something whose instructions read, “Some assembly required”? What was the first thing you did with it when you got home? If you’re like most people, you opened the box, scattered the parts on the floor, and tried to find pieces that looked like they fit together. In the end, you probably had a huge mess on your hands and a burning desire to read the instructions that came with the product. SQL Server 2000 should have a sign right on the front of the box that reads, “Some assembly required” just to remind you that you need to read the instructions first, not last. Just like with the product you bought, with SQL Server, if you read the instruc- tions after the install, you will end up with a mess. This mess is not easy to clean up, though; in some instances, you may even need to reinstall SQL Server. In this chapter, we are going to present the instructions for installing SQL Server 2000 so that you need to do it only once. We’ll start by looking at the prerequisites—what sort of hardware and software need to be in place before you can even think about installing SQL Server. Then we’ll move into installing SQL Server itself, examining each step of the Setup Wizard and pointing out things to which you need to pay spe- cial attention. Once SQL Server is installed, we’ll look at an alternative method of installation: the unattended install. Because there are so many of you out there who will be upgrading from a previous version of SQL Server, we are also going to look into the upgrade process. Finally, because not all installs go perfectly, we’ll look into some troubleshooting techniques to ensure that SQL Server gets up and running. The Prerequisites There are a few things that you will need in place on your machine before you will be able to install SQL Server 2000, the first of which is Internet Explorer 4 SP1 or higher. Many people have cowered in terror at this thought, believing that SQL Server requires this program to serve up data. That is not the case. The only parts of SQL Server 2000 that require IE4 are the Microsoft Management Console (discussed later) and HTML help (Books Online). The minimum hardware requirements and the recommendations (what you should actually use) are listed in Table B.1.
  12. THE SETUP WIZARD 1131 TABLE B.1: THE REQUIREMENTS Component Required Recommended Computer DEC Alpha AXP; Intel Pentium 133 Mhz The fastest possible proces- or higher; or compatible systems sor; multiple if your budget allows Memory RAM (32MB minimum) More is better but at least 128 Disk drive CD-ROM drive Hard-disk space 190MB (Full); 163MB (Typical); 74MB Don’t forget that you need (Minimum); 73MB (management enough space to store data- tools only) bases Operating system Microsoft Windows 2000 Be sure to get the latest family; Microsoft Windows NT 4 service pack family with SP5; Microsoft Windows 95/98; Windows 95 OSR2 APP Network software Windows 2000, NT, or 95/98 built-in network software; additional network B software is not required unless you are using Banyan Vines or AppleTalk ADSP; Novell NetWare client support is pro- Installing Microsoft vided by NWLink SQL Server 2000 Now that you have the hardware and software in place, you can start the Setup Wizard. You’ll do that by inserting the CD and selecting Install SQL Server Compo- nents, then selecting the appropriate version. The Standard version is for use on Win- dows NT Server; the Desktop version is a scaled-down version that will run on Windows NT Workstation and Windows 95/98. Once you have selected your version, the Setup Wizard takes over and guides you through installation. The Setup Wizard Before you run the Setup Wizard, which we’ll do later, there are a few things you’ll need to understand, so in this section, we’ll look at each of the steps in the Setup Wiz- ard; then, later on, you’ll get to put this knowledge to use by installing SQL Server on your own machine.
  13. 1132 APPENDIX B • INSTALLING MICROSOFT SQL SERVER 2000 NOTE There are two editions of SQL Server 2000: Standard and Enterprise. Enterprise is tuned for much larger databases and runs on Windows NT Enterprise Edition or 2000 Advanced Server. Although the following steps apply to either edition, we will use Standard. After the welcome screen, you are asked whether this installation is to be a local, remote, or virtual installation (as seen in Figure B.1): Local Computer: This option will install SQL Server on the machine where you are running the setup program. Remote Computer: Selecting this option will allow you to install SQL Server on any machine on the network on which you have administrative authority. You could, for example, sit at a machine in San Francisco and install SQL Server on a machine in Chicago. Virtual Server: SQL Server can be set up in a cluster, which means that mul- tiple machines act as a single machine—this can be very useful for load balanc- ing and fault tolerance. The option to set up a virtual server is for clustering support. To use this option, you must have clustering support already installed. FIGURE B.1 The Computer Name screen
  14. THE SETUP WIZARD 1133 As shown in Figure B.2, the next screen you run into will present four choices: • The first option will allow you to create a brand-new instance of SQL Server. We’ll discuss instances shortly. • The second choice presented will allow you to upgrade or add components to an existing installation. • The third choice will allow you to maintain a virtual server in a cluster. • The final option will allow you to record a file for unattended installations (discussed later in this chapter). FIGURE B.2 You have four installation types from which to choose. APP B Installing Microsoft SQL Server 2000 The next two screens that pop up will ask you for a user and company name, and agreement to the license terms. When these steps are complete, the screen shown in Figure B.3 appears, where you are asked what you would like to install. There are three choices available: Client Tools Only: This option will install only the client tools necessary for connecting to SQL Server, such as Query Analyzer and Enterprise Manager. Server and Client Tools: This will install the client tools as well as the SQL Server services. This option is what actually turns your machine into a SQL Server.
  15. 1134 APPENDIX B • INSTALLING MICROSOFT SQL SERVER 2000 Connectivity Only: This option will install just the network libraries needed to connect to SQL Server. If you use this option, you will need a custom program, such as Access or Visual Basic, to access the data on the server. FIGURE B.3 There are three defini- tions from which to choose. Once you have selected what you would like to install, you are asked which instance to install. This is a new concept in SQL Server 2000, which is essentially like running multiple SQL Servers on one machine. Previous versions of SQL Server could not run more than one character set or sort order on a single machine. This meant that if you needed to run more than one character set or sort order, you needed multiple physical machines. In SQL Server 2000, if you need to run more than one character set or sort order (now referred to as collation settings), you can run multiple instances of SQL Server on one machine. Your clients will see each instance as a completely sep- arate installation of SQL Server. The Default instance is selected by default (no pun intended) and should be left that way for the first installation of SQL Server. Subsequent installations on the same machine can be given installation names of up to 32 characters. Clients will then use this new name to refer to the new instance. On the next screen, you will be asked where you would like to install your program files, which are the SQL Server executable files (called binaries), and where you would like to store your data files, which are your system and user databases. Before you pick a directory, there is an important decision to be made. If you are upgrading and you
  16. THE SETUP WIZARD 1135 install over the top of the old version (the default), you will not be able to switch back and forth between the two versions; if you place the new version in a new directory, you can switch between the new and old versions of SQL Server. This capability can prove invaluable for troubleshooting applications that may not be compatible with SQL Server 2000 just yet. You also have the choice of performing a Typical, Minimum, or Custom install (as shown in Figure B.4). With Custom, you can install whatever features you like; the differences between Typical/Custom and Minimum are listed in Table B.2. FIGURE B.4 The Setup Type screen APP B Installing Microsoft SQL Server 2000 TABLE B.2: THE INSTALL TYPES Installation Option Typical/Custom Minimum Install database server Yes Yes Install upgrade tools Yes No Install replication support Yes Yes Install full-text search No No Install client management tools All None Install client connectivity Yes Yes Install online documentation Yes No
  17. 1136 APPENDIX B • INSTALLING MICROSOFT SQL SERVER 2000 TABLE B.2: THE INSTALL TYPES (CONTINUED) Installation Option Typical/Custom Minimum Install development tools None None Install code samples None None Install collation Yes Yes Configure network protocols Named Pipes, TCP/IP Named Pipes, TCP/IP (Microsoft Windows NT) Sockets, and Sockets, and Multiprotocol Multiprotocol Configure network protocols TCP/IP Sockets and TCP/IP Sockets and (Windows 95 and Windows 98) Multiprotocol Multiprotocol If you opt for the Custom install type, you will see the screen that shows up in Fig- ure B.5, asking you which components should be installed. We’ll select the defaults and take you to the next screen, where you must pick a service account. FIGURE B.5 Setup allows you to choose the components you want to install. Choosing Service Accounts When you first turn on your Windows NT/2000 machine and try to use it, you are pre- sented with a dialog box that asks you for a username and password. That username
  18. THE SETUP WIZARD 1137 and password allow you access to the machine (and the network) with whatever privi- leges your administrator has seen fit to assign. Many services, programs running in the background, require a user account just like you do. This special user account, called a service account, allows the service access to the machine and network with the privi- leges the service requires to get its work done. The dialog box used to assign such an account to SQL Server is shown in Figure B.6. FIGURE B.6 The Services Accounts screen APP B Installing Microsoft SQL Server 2000 The service account assigned to the SQL Server services can be one of three types (see Table B.3). TABLE B.3: USER ACCOUNTS Type Limitations Advantages Local System You will not be able to communicate Easy to set up, because you with other SQL Servers over the need not create a user network. account Local User You will not be able to communicate Allows you to control the with other SQL Servers over the service permissions without network. allowing network access Global User None; slightly more difficult to configure Allows you to communicate than the other two. fully with other network machines, including SQL Servers and e-mail servers
  19. 1138 APPENDIX B • INSTALLING MICROSOFT SQL SERVER 2000 If you opt to use a user account (local or global), you must first create it using the appropriate tool for your operating system. If you create only one account to be used by both MSSQLServer and SQLServerAgent services (discussed earlier in this book), you must add the user account to the Administrators local group; otherwise, replica- tion (also discussed earlier) will not function properly. If you decide you would like greater control over the security on your network, you can add two separate accounts, one for the MSSQLServer service and one for the SQLServerAgent service. A very good reason to do this is that only the SQLServerAgent service really requires administra- tive authority; the other service can get by just fine as a user. Once you have selected a service account, you are asked to set the authentica- tion mode. Authentication modes are discussed in detail in Chapter 18, but it is good to know a little about them for setup purposes. To access SQL Server, your users need to log in to the server. To log in to the server, they need an account. The type of account they use depends upon the authentication mode that is set. If you select Windows Authen- tication mode, only clients that have a Windows NT/2000 account will be able to access the system. If you have other clients (such as Novell or UNIX), you should select Mixed mode. After selecting a mode, you can click Next. You will then be asked for a collation setting. Choosing a Collation Setting In previous versions of SQL Server, it was necessary to choose a character set, a sort order, and a Unicode collation setting. In SQL Server 2000, these three entities have been combined to form the collation setting. There are two types of collation settings to choose from: Windows Collation and SQL Collation. SQL Collation is for backward compatibility with older versions of SQL Server and does not control Unicode character storage. If you need to replicate with older ver- sions of SQL Server or will be switching between SQL Server 2000 and older versions, you should use SQL Collation. If you are installing SQL Server 2000 on a machine with an older version of SQL Server installed, the setup program will detect the neces- sary SQL Collation for you; otherwise you will need to select the proper collation. Windows Collation uses the collation (code page, sort order, etc.) of the underlying operating system and controls Unicode and non-Unicode sorting and storage. If you choose Windows Collation, you have two more things to worry about: the collation designator and the sort order.
  20. THE SETUP WIZARD 1139 Selecting a Collation Designator As you read this book, you see the characters as lines, curves, and various shapes. If you read Cyrillic, you see different shapes for the characters than does someone read- ing German or English. Computers need to read and interpret characters just like you do—the only problem is that computers don’t see them as various shapes; they see them as different combinations of ones and zeros. It makes sense then that if your computer is storing German data, your computer must store different characters, or combinations of ones and zeros, than an English server would. How these characters are stored is controlled by the collation designator. If you decide to use Windows Collation, it is best to use the collation of the under- lying operating system; for example, if you are running a German server, you would most likely choose a German collation designator. The easiest way to find your colla- tion designator is to look in Control Panel under Regional Options; the locale dis- played there should be used as your collation designator. APP Selecting a Sort Order B All of the data that you are storing on your server must be sorted from time to time, usually during queries or indexing. You sort data because looking at a mass of jumbled Installing Microsoft data is hard on the brain, whereas looking at a nicely ordered report of data is easy and SQL Server 2000 pleasing to the eye. The sort order defines how SQL Server sorts and compares your data during queries. This sort order is the second part of the collation setting. There are four sort options available, even though you see only three. The first is the default sort order—case-insensitive and accent-insensitive. This means that SQL Server will not pay attention to case or accent marks when sorting, indexing, or per- forming queries. The remaining three options can change this behavior, and if you are familiar with previous versions of SQL Server, you will want to pay attention because the options have changed: Binary: Using the default sort order, SQL Server will view characters as char- acters; by using binary sort order, SQL Server will view characters as binary rep- resentations. This is the fastest sort order available, but it is case-sensitive and accent-sensitive. Case Sensitive: This simply tells SQL Server to use dictionary sort order and pay attention to case. Accent Sensitive: This tells SQL Server to use dictionary order and pay attention to accent marks. Here’s the catch: Once you have installed SQL Server, you cannot change the colla- tion setting. To change it, you must reinstall SQL Server and rebuild all of your data- bases. So choose wisely; it is usually best to use the default sort setting of insensitivity
Đồng bộ tài khoản