This book was written about SQL Server 7 and its set of features. However, at press time, Microsoft had just finalized SQL Server 2000, with a scheduled launch date of late September 2000. Obviously, you might have questions about how the new version affectsyour Visual FoxPro client/server applications and, in particular, if any of SQL Server 2000’s new features are worth exploring. This appendix serves as a short comparison of the two products, describing some of what Microsoft did to make SQL Server 2000 superior toSQL Server 7.
For a greater level of detail on any features of SQL Server 2000, you can check Microsoft’s Web site at http://www.microsoft.com/sql/, or install and read SQL Server 2000 Books Online, particularly the topic “What’s New in Microsoft SQL Server 2000.”
Feature list
Table 1 shows an abridged high-level feature list for SQL Server 2000. The complete version of this table is available from Microsoft at www.microsoft.com/sql/productinfo/sql2ktec.htm. In the Editions column, “E” stands for the Enterprise edition, “S” for Standard and “P” for Personal. Each edition has its own hardware and operating system requirements, which are detailed on Microsoft’s Web site as well as in Books Online.
Table 1. Some of the new features of SQL Server 2000.
Feature
Benefits
Editions
URL and
HTTP Access
From a browser, use SQL, XML templates or XPath in the URL
line for querying.
E,S,P
OpenXML
Access, manipulate and update XML documents as if they were
tables using Transaction SQL (T-SQL) and stored procedures.
E,S,P
Full-Text Search
Enable Full-Text Search across the Web and intranets for
formatted documents, such as Microsoft Word, MicrosoftExcel and HTML. Track changes automatically.
E,S,P
English Query
Allow all users to access data through natural language queries.
Graphically author queries with wizards in the includedMicrosoft Visual Studio environment. Generate Multi-Dimensional Expressions (MDX) to query cubes.
E,S,P
Multi-Instance
Support
Run reliably in hosted scenarios with separate database
instances per customer or application.
E,S,P
Security
Protect data with higher default security on installation. Includes
support for Secure Sockets Layer (SSL) connections and
Kerberos. C2-level certification underway.
E,S,P
Installation Disk
Imaging
Create standard or default databases for server farms from any
machine on the network with built-in cloning technology.
E,S,P
Table 1, continued
Feature
Benefits
Editions
Distributed Partition
Views
Achieve software scale-out on the data tier by partitioning
workload across servers. Add additional servers forgreater scalability.
E
Log Shipping
Automatically keep databases synchronized for warm standby on
multiple backup servers to share load—no matter howphysically far apart.
E
Parallel Index
Creation
Take full advantage of symmetric multiprocessing (SMP)
hardware to speed up index creation, easing the loadon frequently updated systems.
E
Failover Clustering
Install failover-ready databases directly from setup. Use
active/passive failover with standby hardware oractive/active failover in hardware-constrained environments.Databases can failover to any surviving node with four-nodefailover.
E
32 CPU SMP
System Support3
Scale up SQL Server 2000 databases to SMP systems with as
many as 32 processors.
E
64GB RAM
Support3
Handle the largest data sets and transactional loads with up to
64GB of RAM for SQL Server 2000.
E
Indexed Views
Create indexes on views to improve performance of existing
queries without recoding. Speed up analysis and reportingthat rely on complex views.
E
Online Index
Reorganization
Keep the server up and running while reorganizing indexes to
improve performance.
E,S,P
Microsoft Active
Directory™ Integration5
Manage databases centrally alongside other enterprise
resources. View and search for servers, replicationpublications, cubes and more.
E,S,P
SQL Query
Analyzer
Debug stored procedures. Set breakpoints, define watches, view
variables and step through code. Trace executing code onthe server or the client. Easily write T-SQL based ontemplates.
E,S,P
User-Defined
Functions
Achieve code reuse by creating T-SQL functions. Incorporate
routinely used logic to simplify development.
E,S,P
Cascading
Referential Integrity
Constraints
Control how changes propagate through tables when keys
are updated.
E,S,P
Instead of and After
Triggers
Execute code flexibly by specifying what happens in lieu of an
operation or after it.
E,S,P
Indexes on
Computed Columns
Define indexes on column types even when the data in the
column is computed from other columns.
E,S,P
New Data Types
Store and reference data flexibly with bigint, sql_variant, and table
data types.
E,S,P
Column Level
Collations
Store objects that have different collations in the same database.
Collations can be specified at the database level or atthe column level.
E,S,P
As you can see from Table 1, there are plenty of new features—and this table is not a complete list! To save space, the features dealing specifically with data warehousing and XML were left out of the table; however, if you are working with either of these technologies, be sure to check out SQL Server 2000. It contains numerous features over those provided by SQL Server 7 for data warehousing, while providing support for XML—support that did not exist at all in SQL Server 7.
Since there are so many new features, this appendix will only cover those that are related to the topics covered in this book.
Installation issues
Before you attempt to install SQL Server 2000, you should take the time to read the setup/upgrade Help provided on the installation CD. It provides valuable information for upgrading an existing SQL Server 7 to 2000, as well as the new installation options available to you.
One installation issue with SQL Server 2000 is that it now allows multiple instances of the server on the same computer. This is useful if you need to run databases for different clients or applications, but cannot afford the additional expense of multiple servers. Under SQL Server7, if you needed a different sort order or code page for two different databases, you were forced to install the product on two different machines. This is because these features can only be set at the server level.
Once you have installed SQL Server 2000, you will find that you have the same tools that were available under SQL Server 7, such as Enterprise Manager, Service Manager, Books Online, and the Query Analyzer. However, all of these tools have been enhanced for use in SQLServer 2000.
Query Analyzer
The SQL Server 2000 Query Analyzer has significant enhancements over the version included with SQL Server 7. One very nice feature is the Object Browser, displayed on the left side of the Query Analyzer window (shown in Figure 1). It contains a list of all the available objects, as well as a hierarchical list of available functions and variables. Any member of the object browser can be dragged to the query window, where text is automatically entered based upon the dragged object.
For example, you can grab a table from the Object Browser and right-mouse-drag it to the
query window. When you release the right mouse button, a shortcut menu appears, allowing you to insert code for any of the following commands:
CREATE TABLE table … ALTER TABLE table … DROP TABLE table … SELECT…FROM table INSERT INTO table … UPDATE table SET… DELETE FROM table…
This is extremely powerful, as it frees the developer from having to remember all of a table’s column names or the syntax of these commands. The CREATE TABLE command even includes any constraints on the selected table.
The Object Browser also contains templates for many T-SQL commands. These can also be dragged and dropped into the query window, allowing you to quickly build scripts. The templates insert any necessary parameters enclosed in less than/greater than symbols (i.e.,
<parameter>) so that you can easily find and replace the parameters for your particular needs.
Figure 1. The SQL Server 2000 Query Analyzer showing the new Object Browser.
Debugging stored procedures
The SQL Server 2000 Query Analyzer also contains a highly anticipated feature: a source- level debugger for T-SQL stored procedures. The debugger, shown in Figure 2, can only run within the Query Analyzer and can only debug stored procedures that have been saved in a database. This means that you cannot debug a script that you have saved to a SQL file or that only exists in the query window.
To use the debugger, simply right-click the desired stored procedure in the Object
Browser and choose Debug. This will load the debugger into Query Analyzer, allowing you to set breakpoints, step through the T-SQL source, view and change the value of any local variables, view global variables, and check the procedure nesting level with a call stackwindow.
Figure 2. The SQL Server 2000 T-SQL debugger in break mode.
User-defined functions
Another feature that Visual FoxPro developers greatly missed in SQL Server 7 was the ability to create and incorporate user-defined functions nearly anywhere in code. SQL Server 2000 changes things by allowing you to write user-defined functions, store them in a database, and use them inside commands or even as column definitions.
The following example demonstrates how to create a simple user-defined function, and then shows its use within a CREATE TABLE statement:
CREATE FUNCTION MyFraction (
@Val1 Decimal(4,1),
@Val2 Decimal(4,1) )
RETURNS Decimal(9,7)
AS
BEGIN
RETURN (@val1/@val2)
END
GO
CREATE TABLE sqltest (
numerator Decimal(4,1),
denominator Decimal(4,1),
result AS (
MyFraction(numerator,denominator) ) ) GO
To test this functionality, insert some values into the first two fields of the table. When you query the table afterwards, you can see that SQL Server 2000 has automatically populated the third column in each inserted record with the result of the user-defined function:
INSERT INTO sqltest (numerator,denominator) VALUES (5,4) INSERT INTO sqltest (numerator,denominator) VALUES (3,2) INSERT INTO sqltest (numerator,denominator) VALUES (1,7) INSERT INTO sqltest(numerator,denominator) VALUES (2,3)
SELECT * FROM sqltest numerator denominator result
--------- ----------- ----------
5.0
4.0
1.2500000
3.0
2.0
1.5000000
1.0
7.0
.1428570
2.0
3.0
.6666660
(4 row(s) affected)
Other than this special feature, user-defined functions in SQL Server 2000 can be used in the same fashion as in Visual FoxPro. For example, you can use this same function within a query:
SELECT job_desc, min_lvl, max_lvl, MyFraction(max_lvl,min_lvl) as ratio
FROM jobs
job_desc min_lvl max_lvl ratio
----------------------------- ------- ------- -----------
New Hire - Job notspecified
10
10
1.0000000
Chief Executive Officer
200
250
.8000000
Business OperationsManager
175
225
.7777770
Chief Financial Officer
175
250
.7000000
Publisher
150
250
.6000000
Managing Editor
140
225
.6222220
Marketing Manager
120
200
.6000000
Public Relations Manager
100
175
.5714280
Acquisitions Manager
75
175
.4285710
Productions Manager
75
165
.4545450
Operations Manager
75
150
.5000000
Editor
25
100
.2500000
Sales Representative
25
100
.2500000
Designer
25
100
.2500000
(14 row(s) affected)
SQL Server 2000 user-defined functions can return any data type except text, ntext, image, cursor or timestamp. This means that you can use the new data types (detailed in the “New Data Types” section later in this appendix) as return values from user-defined functions,providing plenty of flexibility for your database implementation needs.
Referential integrity
One of the biggest “gotchas” in SQL Server 7 is that declarative referential integrity only supports restrictive relationships. This is because of the way the FOREIGN KEY and REFERENCES constraints were designed in SQL Server 7—they can only handle the restrict rules. If youneed to cascade a Delete or Update, you must work with stored procedures, or remove the constraints and implement the cascade with T-SQL code in the appropriate trigger.
SQL Server 2000 now allows cascading referential integrity constraints. This means that you no longer have to write code to implement a cascading Delete or Update. Furthermore, since constraints are defined at the table level without code, this new feature will perform more efficiently over the trigger- or stored-procedure-based techniques necessary under version 7.
To implement cascading RI, you can use the Table Properties dialog (shown in Figure 3), which is part of the table designer in Enterprise Manager. By clicking the Cascade check boxes, you will set the appropriate cascading constraint in the table.
Figure 3. The Relationships page of the Table Properties dialog.
Alternatively, you can use SQL Server 2000’s CREATE TABLE or ALTER TABLE T-SQL commands, which now support the ON DELETE, ON UPDATE and CASCADE keywords. The following example creates a relationship between an existing State code table and a newCustomer table, basing the relationship upon the state code field in both tables:
CREATE TABLE dbo.customer (
cu_id int NOT NULL IDENTITY (1, 1),
cu_last char(30) NULL,
cu_first char(30) NULL,
cu_company char(30) NULL,
cu_addr char(30) NULL,
cu_city char(30) NULL,
cu_stcode char(2) NULL,
cu_zip char(10) NULL )
GO
ALTER TABLE dbo.customer ADD CONSTRAINT FK_customer_state FOREIGN KEY (cu_stcode)
REFERENCES dbo.state (st_code) ON UPDATE CASCADE
ON DELETE CASCADE
GO
Trigger enhancements
In SQL Server 7, constraints are fired before the data is modified in a table. Therefore, if a constraint fails, SQL Server fails the modification, leaving the data untouched and preventing the firing of triggers. Triggers can only fire after a data modification has taken place, which can only happen after all constraints have passed successfully. However, since all triggers finish with an implied COMMIT TRANSACTION, triggers that need to discard changes must do so by issuing a ROLLBACK TRANSACTION statement. This reverts changes that were alreadymade to the data.
SQL Server 2000 still supports these types of triggers—they are now called AFTER triggers. In addition, a new type of trigger exists in SQL Server 2000 called an INSTEAD OF trigger. These triggers fire instead of the triggering action (i.e., INSERT, UPDATE or
DELETE), execute before any constraints, and can be used on tables or views. Therefore, when a data modification is made in SQL Server 2000, any INSTEAD OF triggers fire first, then the constraints and, finally, any AFTER triggers.
The best place to use INSTEAD OF triggers is on views, particularly when the view contains more than one base table. This allows any insertion of records into a view to work properly and permits the view to be fully updatable. Without INSTEAD OF triggers, views can onlymodify data in one table at a time.
Another trigger feature that was new for SQL Server 7 has been enhanced in SQL Server
2000. In version 7, it became possible to define multiple triggers for a single operation. For example, you can create multiple UPDATE triggers, where each trigger essentially “watches” for changes in a particular column. The only problem with multiple triggers is that SQL Server
7 did not provide any mechanism for specifying the order in which these multiple triggers would fire.
This shortcoming of SQL Server 7 forced developers to write a single trigger that encapsulated the functionality of the desired multiple triggers. With a single trigger, calls could be made in the desired sequence.
In SQL Server 2000, you can now specify which trigger fires first and which fires last with the sp_SetTriggerOrder system stored procedure. For example, if you have three update
triggers named Upd_Trig1, Upd_Trig2 and Upd_Trig3, you can force them to fire in numerical order with the following T-SQL code:
EXECUTE sp_SetTriggerOrder
@TriggerName='Upd_Trig1',
@Order='first',
@stmttype='UPDATE'
EXECUTE sp_SetTriggerOrder
@TriggerName='Upd_Trig3',
@Order='last',
@stmttype='UPDATE'
All triggers have an order of ‘None’ by default, which means that their order has not been specified. If you need to determine whether a trigger is first or last, you must use the OBJECTPROPERTY() function with one of the following properties: ExecIsFirstInsertTrigger, ExecIsFirstUpdateTrigger, ExecIsFirstDeleteTrigger, ExecIsLastInsertTrigger, ExecIsLastUpdateTrigger or ExecIsLastDeleteTrigger.
DECLARE @objID int, @IsFirst tinyint
SET @objID = OBJECT_ID('Upd_Trig1')
SET @IsFirst = OBJECTPROPERTY(id,'ExecIsFirstUpdateTrigger')
PRINT @IsFirst
In the preceding code, if the @IsFirst variable contains zero, the trigger is not the first update trigger. If the variable contains one, then the trigger has been specified as the first update trigger for the table.
Indexing computed columns
SQL Server 7 only allowed you to build indexes on the columns of a table. While not a limitation in transactional systems, this does prevent the best performance possible in analytical systems, because data marts and warehouses typically require lots of aggregation and calculationagainst table data. Wouldn’t it be great if you could index columns that already contained these calculations? SQL Server 2000 now permits you to index computed columns, and even allows indexes on views.
Transactional systems will not benefit from indexed computed columns or views, as they will tend to slow data entry speed. This is simply because SQL Server will need to update the indexes as new data is added or existing data is modified. The more indexes and the more complex those indexes are, the slower inserts and updates will become. The same issue exists in Visual FoxPro and should be familiar.
However, for analytical (i.e., OLAP or data warehousing) systems, this feature can provide a huge performance benefit. By indexing the appropriate computed columns, the data can be retrieved more quickly, as it will already contain the necessary calculations and be accessible
through an index instead of a table scan. The restriction here is that the calculated column function must be deterministic. This means that the function must always return the same result set when provided with the same set of input values.
Another restriction can be demonstrated with the table definition created in the preceding “User-defined functions” section, where a function was used as a column definition. While this column could be indexed, SQL Server 2000 will not yet allow it. To enable the indexing ofthis column, you must use the SCHEMABINDING function. A schema-bound function will prevent the associated object from being altered or dropped, ensuring that any dependencies on the function do not accidentally “disappear” and break the function.
New data types
SQL Server 2000 introduces three new data types called bigint, sql_variant and table. These new data types were added to provide greater flexibility when writing scripts or when the “old” data types of SQL Server 7 do not meet storage needs.
Big integers
The bigint data type is an eight-byte (64-bit) integer value with a range of
±9,223,372,036,854,775,808. This can be used in IDENTITY columns where the number of records will exceed the “limited” range of the int data type (a four-byte integer with a range of ±2,147,483,648). However, as this new data type is incompatible with the current integerfunctions, SQL Server 2000 also added a COUNT_BIG() function and the ROWCOUNT_BIG() function. These are functionally equivalent to the COUNT() function and @@ROWCOUNT variable, but the returned data type is a bigint instead of an int.
Variants
The sql_variant data type is very similar to what we’re used to in Visual FoxPro—it’s a variant data type, and it can hold any data type at any time. It cannot store BLOB data (text, ntext or image data) or timestamp data, but it can be used as the data type for any column! Therefore,it is possible now in SQL Server 2000 to have a column that stores different types of data in each row. For example, here is a test SQL script to verify that this really works:
CREATE TABLE s2ktest ( Field1 Sql_variant, Field2 Int,
Field3 Char(30) ) GO
INSERT INTO s2ktest VALUES ('test',1,'Char') INSERT INTO s2ktest VALUES (getdate(),2,'Date') INSERT INTO s2ktest VALUES (3.1453,3,'Real') INSERT INTO s2ktest VALUES (49,4,'Int')
SELECT * FROM s2ktest
Note how the INSERT INTO statements put a different data type into each row of the table. None of these statements fail because the first field is defined with the sql_variant data type. Of course, once the data has been stored, you will want to retrieve the data, and the
SELECT statement handles this with no problems. However, if you desire to know the data
type of the actual data in the column, you can use the SQL_VARIANT_PROPERTY() function to get the data type, similar to how the TYPE() function works in Visual FoxPro.
For example, if you wanted to add a column that displays the data type of the first field, you could use this SELECT statement to produce the following output:
SELECT s2kTest.*,SQL_VARIANT_PROPERTY(Field1,'BaseType') FROM s2kTest
Field1
------------------------
test
Fiel
----
1
d2Field
-- -----
char
3 Field4
- --------
varchar
2000-08-2421:29:53.520
2
date
datetime
3.1453
3
real
numeric
49
4
int
int
(4 row(s) affected)
Tables as variables
The new table data type permits result sets from queries to be stored in a variable on the server. This means that you cannot use the table data type for a column definition, but it can be used within server-side code. This data type is a clear advantage over using temporary tables,since these tables always consume some amount of space in the tempdb database. On the other hand, data stored in a table data type exists entirely in memory, eliminating the performance problems and storage requirements of temporary tables.
Defining a table data type requires use of the DECLARE command in T-SQL, with additional text that specifies the structure of the table. Once the table has been declared, you
can work with it as any other table: Insert data, delete records or modify data that you’ve placed into it. The following is an example of how to do this:
--Create table in memory
DECLARE @MyTable Table (Field1 int, Field2 Varchar(10), Field3 DateTime)
--Switch to northwind database
USE Northwind
--Throw some records into the table
INSERT INTO @MyTable
SELECT OrderID,CustomerID,OrderDate
FROM Orders
WHERE orderDate < '1996-07-10'
--Select data from the table
SELECT * FROM @MyTable
While this is not a tremendously useful example, it at least shows how a table variable works like a temporary cursor in Visual FoxPro. It is important to note here that the @MyTable variable goes out of scope when the procedure ends, so any data stored in the @MyTable
table will be released at that point. This is true for any variable declared within a SQL Server stored procedure.
Summary
With only the features covered in this appendix, it’s easy to see how SQL Server 2000 offers a tremendous amount of benefit over SQL Server 7. However, this is only a small part of what has changed for the newest version of SQL Server. The features mentioned here relate to databases that are built for use in transactional, non-Internet-based applications. For Internet applications, SQL Server 2000 provides numerous XML features to sweeten the pot over SQL Server 7. Additionally, for OLAP applications, there are plenty of enhancements to further improve on the performance of retrieving data from your data warehouse.
In any case, upgrading from SQL Server 7 to SQL Server 2000 seems like a win-win situation, no matter what type of application you are planning to build with it.
Posts
No comments:
Post a Comment