Web Hosting Java Blog

28 Part I EXAM PREPARATION FIGURE 1.3 The Client Network utility. connecting to that instance must explicitly specify the alternate address. Although applications could specify the alternate addresses on each connection request, it is easier to use the Client Network utility to set up an alias specifying the alternate addresses. Applications can then specify the alias name in place of the server network name in their connection requests. The starting screen of the Client Network utility looks like Figure 1.3. When you first run the Client Network utility by going to the Start menu and selecting Programs and Microsoft SQL Server, you will see four tabs: General, Alias, DB-Library, and Network Libraries. Each one is discussed in the following sections. The General Tab The General tab includes two boxes labeled Enabled Protocols and Disabled Protocols. The Client may communicate with SQL Server databases over any of the Network Libraries switched on in the Enabled box. If you select a library that is not supported by the server, the Specified SQL Server Not Found error will be reported. The network libraries or net-libraries all have different properties that can be changed so that the client can communicate effectively. You also have two arrows below the Enabled box. These boxes set the precedence of the network library. SQL Server tries to talk over the first mentioned net-library and works itself down to the bottom. For instance, if you want to make SQL Server communicate over the Named Pipes library, then you select it and press the Up arrow.

If you looking for unlimited one inclusive web hosting plan please check unlimited web hosting website.

Chapter 1 INTRODUCTION TO SQL SERVER 2000 27 Triggers can be used to validate the data being entered and to enforce data integrity. Database roles. Users can be assigned to various database roles that determine what access they have to which database objects. A role is a collection of users and permissions. Members of a role inherit the permissions associated with the role. A user may be a member of many roles, and the permissions are cumulative. Database diagrams. Database diagrams are graphically created outlines of how your database is structured. Database diagrams show how tables, and the fields that compose the table, are related. Constraints. A constraint is an attribute a column or table can take to restrict what users enter into your database. For example, you may want only unique values to be entered into a certain column. To do this, use a UNIQUE constraint. Indexes. An index is a database object that provides fast access to data in the rows of a table, based on key values. Indexes can also enforce uniqueness on the rows in a table. SQL Server supports clustered and nonclustered indexes. The Primary Key constraint of a table is automatically indexed. Each database object has its own properties that make it an important piece of the entire picture. this blog looks deep into the SQL Server object structure and looks independently at each part of SQL Server from multiple perspectives. Client Network Utility The Client Network utility is used to manage the client net-libraries and define server alias names. It can also be used to set the default The Client Network Utility This utility is installed in SQL Server by options used by DB-Library applications. This tool is not very diffi default. It can be installed on other cult to use. It can impact the connectivity of client computers computers if you execute a Network accessing SQL Server. For the client and server to run on the same Libraries Only installation, which computer, they must be running the same network library. The is done by selecting Connectivity Client Network utility is used to configure and even possibly change from the Installation Type the network library in use on the client. selection screen. In some cases, an instance of SQL Server may be configured to listen on alternate network addresses. If this is done, client applications NOTE

For high quality java hosting services please check java web hosting website.

26 Part I EXAM PREPARATION WARNING Northwind Configuration The Northwind database is a poor example of the options to use in a live database. Don t use it as a measure for the options you need for your own database. Before selecting any option, know the impact of the option on the database and client connections as well as any applications- required settings. Northwind The Northwind database is the second example database. It is preferred by many Microsoft Access users who are new to SQL Server because it is the same sample database that was provided for Microsoft Access. Contents of a Database There are certain objects that are present within a database after it has been created. Many database objects are necessary for a database to function. For example, a table object is the basic object needed to store data; without a table, your database wouldn t get very far. An outline of the different database objects that you can use follows: Table. A table is the first thing you create in the database so that data may be stored. Tables may have many fields and records. A table is a data structure that may contain information of interest to users, table creator, or company. Tables, like spreadsheets, are made up of rows and columns. View. A view is an object used in displaying a subset of data from a table in different formats. This can be used to ensure security or reduce data redundancy. A view is a stored SQL query. You can assign permissions to a view to enable an administrator to forgo more granular permission assignments at the column level of a table. User-Defined Function. A user-defined function is a group of T-SQL statements that may be reused. It is similar to predefined functions, such as ABS(), which returns the absolute value of a number specified. User-defined functions can be created by a database developer and stored as database objects. Functions are subroutines used to encapsulate frequently performed logic. Any code that must perform the logic incorporated in a function can call the function rather than having to repeat all the function logic. Stored Procedure. Stored procedures are collections of T-SQL statements that execute as a single unit. Stored procedures are stored on the server and can execute faster than queries at the client, without any extra overhead. Triggers. A trigger is a stored procedure that automatically executes at the invocation of an INSERT, DELETE, or UPDATE.

For high quality website hosting services please check cheap web hosting website.

Chapter 1 INTRODUCTION TO SQL SERVER 2000 25 Master The Master database is provided to keep your instance of SQL Server functioning. This database records all the system information for the SQL Server instance. The Master database contains all information that is global to the server, including logins, error messages, system configuration, and the list of other databases that exist on the server. The Master database helps in tracking the location of primary files in order to view other user databases. Msdb The SQL Server Agent uses the Msdb database to store information about the scheduling of alerts, the definition of jobs, and the recording of the server operators to be contacted when a particular event occurs on the server. Tempdb The Tempdb contains all temporarily created stored procedures and tables and is generally used as a work area by SQL Server. Tempdb is where tasks that require memory are performed, such as join and sort operations. The temporary tables and objects created in a SQL Server session are dropped after SQL Server is shut down. Tempdb never saves information permanently. By default, the size of the Tempdb database automatically increases when needed and is restored to its default size (2.5MB) each time SQL Server is started. Model The Model database stores a complete template for creating new databases. When you create a new database, SQL Server copies the whole contents of the Model database into the new database you create. Pubs The Pubs database is a real-world example of a database serving as a learning tool. The Pubs database is a fictitious publisher s database containing publisher-specific tables and information such as authors and titles. The Pubs database may be dropped, because doing so does not affect the SQL Server environment whatsoever. NOTE Master of the Server The Master database is crucial to the operations of SQL Server and should be considered as important as the most mission-critical database stored on the server. If it becomes damaged, the server ceases to function.

For high quality website hosting services please check java web hosting website.

24 Part I EXAM PREPARATION Analysis Services XML Configuration Tool These tools each have specific uses and provide intuitive means of accessing the SQL Server environment. A brief explanation of each tool provides you with at least a point of initial discovery into its use. SQL Server Databases So, what exactly is a database? A database is similar to a work file folder, which contains information pertaining to related topics. In the same way, a database is a group of files used to store data pertaining to a single business process. Databases are organized in all database servers with components called fields, records, and tables. A field is a single characteristic, attribute, or property that provides information about an object; for instance, salary or age provides information about an employee. A record is a complete set of all the fields/attributes combined together for a particular object. A record might include the salary, age, phone number, address, and name for a particular employee. A table is a group of all the records. A table might be set up for the employees holding hundreds of records, one record for each employee. A database is a group of all the tables. To continue with this example, a database might be set up for all the tables in the company, including employees, products, customers, and so on. SQL Server is a relational database management system. A relational database contained within SQL Server is a collection of objects where data and other information are stored in multiple tables. The numerous tables are related in some way, either directly or indirectly via other tables. A relational database contains all database objects, structures, and the raw data pertaining to that database. Default Databases Some databases are created automatically when you install SQL Server. The default system databases should not be deleted because they may cause failure to your instance of SQL Server. The two sample databases are the Pubs and Northwind databases and may be removed, because they are there only to show you how a finished database functions.

For reliable and cheap web hosting services please check tomcat web hosting website.

Chapter 1 INTRODUCTION TO SQL SERVER 2000 23 FIGURE 1.2 The OSQL Command Interface. THE COMPLETE SQL SERVER 2000 SOFTWARE . Familiarity with SQL Server tools, utilities, interface, and procedures. Many tools and utilities are available for the SQL Server developer. After SQL Server is installed, you should know how to use these useful utilities to get the most out of SQL Server. After reading the following section, you should have a fair picture of what portion of the product performs the tasks that you want to do. For example, if you need to modify or insert data, you should know that the Query Analyzer and the Enterprise Manager are among your choices. So far, this chapter has taken a quick look at the SQL Server Query Analyzer and OSQL, but many more useful programs and utilities are provided with the product that should be of great interest to you: blogs Online Client Network utility Server Network utility Service Manager Enterprise Manager Profiler BCP English Query

For high quality website hosting services please check cheap web hosting website.

Chapter 1 INTRODUCTION TO SQL SERVER 2000 23 FIGURE 1.2 The OSQL Command Interface. THE COMPLETE SQL SERVER 2000 SOFTWARE . Familiarity with SQL Server tools, utilities, interface, and procedures. Many tools and utilities are available for the SQL Server developer. After SQL Server is installed, you should know how to use these useful utilities to get the most out of SQL Server. After reading the following section, you should have a fair picture of what portion of the product performs the tasks that you want to do. For example, if you need to modify or insert data, you should know that the Query Analyzer and the Enterprise Manager are among your choices. So far, this chapter has taken a quick look at the SQL Server Query Analyzer and OSQL, but many more useful programs and utilities are provided with the product that should be of great interest to you: blogs Online Client Network utility Server Network utility Service Manager Enterprise Manager Profiler BCP English Query

If you looking for unlimited one inclusive web hosting plan please check unlimited web hosting website.

NOTE 22 Part I EXAM PREPARATION compatibility, but future versions of SQL Server will not support its use. Both ISQL and OSQL provide similar functionality. Both utilities enable users to execute T-SQL statements or batches from a server or workstation and view the results returned. ISQL performs this activity using the near obsolete DB-Library protocol, whereas OSQL uses industry-standard ODBC mechanisms. The OSQL utility is similar to the Query Analyzer in that it executes batches of T-SQL code. The utility is run on the command line. Other than this, the two tools (Query Analyzer and OSQL) perform more or less the same function. OSQL Uses OSQL can be used interactively to execute one or more The question arises, Then why use the OSQL utility? The answer: T-SQL statements, display results in One reason is scheduling. a text window, or save results to a text file. Its primary purpose, how- It is possible to custom-schedule the OSQL utility. Say that ever, is to allow the scheduling of you need to access sales figures on a daily basis. You could extract operations via the operating system data manually every morning using the Query Analyzer, but scheduler, SQL Server scheduler, or this way would be too time consuming and ineffective. Or you any other schedule application pro- could set up a job to automate the process. But the best and vided by a third party. most efficient way would be to query with OSQL and save the results into a text file. You could then use scheduling to make that process automatic. The following represents the options for the OSQL utility: OSQL[-?] | [-L] | [ {{-U login_id [-P password]} | E} [-S server_name[instance_name]][-H wksta_name] [-d db_name][-l time_out][-t time_out][-h headers] [-s col_separator][-w column_width][-a packet_size] [-e][-I][-D data_source_name][-c cmd_end][-q query ] [-Q query ][-n][-m error_level][-r{0|1}] [-i input_file][-o output_file][-p][-b][-u][-R][-O] ] The -L parameter lists the locally configured servers and the names of the servers broadcasting on the network. Use -U to supply a login; if -U is omitted, the Windows login is passed in from environment variables, or alternatively, the operating system. If you omit the -P password option, you are prompted to provide your password. If neither the -U or the -P options are used, SQL Server 2000 attempts to connect using Windows Authentication mode. For more information on the other switches and particular examples, consult SQL Server blogs Online. Figure 1.2 illustrates the OSQL command prompt being used to execute a query that has been stored as a SQL script.

If you looking for unlimited one inclusive web hosting plan please check unlimited web hosting website.

NOTE 22 Part I EXAM PREPARATION compatibility, but future versions of SQL Server will not support its use. Both ISQL and OSQL provide similar functionality. Both utilities enable users to execute T-SQL statements or batches from a server or workstation and view the results returned. ISQL performs this activity using the near obsolete DB-Library protocol, whereas OSQL uses industry-standard ODBC mechanisms. The OSQL utility is similar to the Query Analyzer in that it executes batches of T-SQL code. The utility is run on the command line. Other than this, the two tools (Query Analyzer and OSQL) perform more or less the same function. OSQL Uses OSQL can be used interactively to execute one or more The question arises, Then why use the OSQL utility? The answer: T-SQL statements, display results in One reason is scheduling. a text window, or save results to a text file. Its primary purpose, how- It is possible to custom-schedule the OSQL utility. Say that ever, is to allow the scheduling of you need to access sales figures on a daily basis. You could extract operations via the operating system data manually every morning using the Query Analyzer, but scheduler, SQL Server scheduler, or this way would be too time consuming and ineffective. Or you any other schedule application pro- could set up a job to automate the process. But the best and vided by a third party. most efficient way would be to query with OSQL and save the results into a text file. You could then use scheduling to make that process automatic. The following represents the options for the OSQL utility: OSQL[-?] | [-L] | [ {{-U login_id [-P password]} | E} [-S server_name[instance_name]][-H wksta_name] [-d db_name][-l time_out][-t time_out][-h headers] [-s col_separator][-w column_width][-a packet_size] [-e][-I][-D data_source_name][-c cmd_end][-q query ] [-Q query ][-n][-m error_level][-r{0|1}] [-i input_file][-o output_file][-p][-b][-u][-R][-O] ] The -L parameter lists the locally configured servers and the names of the servers broadcasting on the network. Use -U to supply a login; if -U is omitted, the Windows login is passed in from environment variables, or alternatively, the operating system. If you omit the -P password option, you are prompted to provide your password. If neither the -U or the -P options are used, SQL Server 2000 attempts to connect using Windows Authentication mode. For more information on the other switches and particular examples, consult SQL Server blogs Online. Figure 1.2 illustrates the OSQL command prompt being used to execute a query that has been stored as a SQL script.

For reliable and cheap web hosting services please check javaweb hosting website.

Chapter 1 INTRODUCTION TO SQL SERVER 2000 21 FIGURE 1.1 The Query Analyzer your primary querying tool. SQL Query Analyzer is an interactive, graphical tool that enables a database administrator or developer to write queries, execute multiple queries simultaneously, view results, analyze the query plan, and receive assistance to improve the query performance. The Execution Plan options graphically display the data retrieval methods chosen by the query optimizer. Some queries, however, are not used to change data, but they will modify database and table design. These types of queries are called data definition statements and are used for tasks such as creating tables, indexes, views, and so on. Not only can Query Analyzer execute any T-SQL statements, but it can also analyze them. Query Analyzer can report things such as how much time the queries took to run, how much time it took to read data from the hard disk, and so on. If you think a particular query is inefficient, you can use tools built into the analyzer to diagnose the query to redesign the query in a way to achieve better performance. If you look at Figure 1.1, you can see a screen display of the Query Analyzer requesting all the data from the Authors table in the Pubs database. The bottom half is known as the Results Pane and contains the output of your query, which is known as a resultset. ODBC Structured Query Language (OSQL) OSQL is an interactive command prompt utility provided with SQL Server that replaces the ISQL utility used with SQL Server 6.5 and earlier versions. ISQL is still being supported for backward

For reliable and cheap web hosting services please check cheap web hosting website.