A. Yes, Transact SQL is still the language engine for SQL Server. There are a number of extensions to support the new features of SQL Server 7, but routines written for earlier versions should still run as before.

SQL Server 7 does automate a number of administrative chores however, so you should check to see if it’s still necessary to run all of your TSQL scripts.

Q. Does SQL Server support the ANSI standard?

A. Yes, SQL Server 7 is fully ANSI-compliant (ANSI SQL-92). There are, of course a number of proprietary extensions which provide extra functionality – for example the multi-dimensional RollUp and Cube extensions.

Q. Can I still run SQL Server from DOS and Windows 3.x?

A. Yes. Both can still act as client operating systems. Of course there are a number of features that won’t be available (for example, the Enterprise Manager, which requires a Windows 9.x or NT client operating system).

Remember that you can run SQL Server 7’s server components on a Windows 9.x machine, but security is less strictly applied on Windows 9.x systems.

Q. What’s happened to the SQL Executive service?

A. SQL Agent now replaces the SQL Executive service which controlled scheduling operations under earlier versions of SQL Server. SQL Agent is far more flexible, allowing Jobs (formerly Tasks) to be run as a series of steps.

MSDB is remains the scheduling database storing all of SQL Agent’s scheduling information.

Q. ISQL seems to have been replaced by OSQL. What’s the difference, if any?

A. ISQL is still there, and it continues to use ODBC to connect to SQL Server. However SQL Server 7 has been designed to use OLE DB as its data access interface. OLE DB is designed to be faster and more flexible than ODBC.

While ODBC allows access to SQL data only, OLE DB can work with both structured and unstructured data. OLE DB is a C-like interface, but developers using Visual Studio have access to a code wrapper known as Active Data Objects.

Q. I’m presently using the RDO (Remote Data Object) library to work with SQL Server data. Should I migrate to ADO or wait and see?

A. RDO is a tried and tested data access technology, that’s been tweaked and tuned over two versions. If you’re developing a new system from scratch, consider using ADO, as its object model is flatter and more streamlined than RDO, but if you’re supporting a large, complex suite of applications using RDO to interface with SQL Server, adopt a more cautious, incremental approach. Consider migrating portions of your application suite to ADO, perhaps encapsulating particular functions in classes.

Q. How has the Distributed Management Framework changed in SQL Server 7?

A. The Distributed Management Framework uses SQL Server’s own object library to make property and method calls to SQL Server components. SQL Server 6.5 used the SQLOLE object library (SqlOle65.dll); SQL Server 7 uses a new version, the SQLDMO library (SqlDmo.Enu).

Certain collections are no longer supported (notably the Devices collection) and others have changed their name. You can still choose to manipulate SQL Server using the new, snap-in Enterprise Manager (which makes the SQLDMO calls for you) or make the calls yourself from an ActiveX-compliant client.

Q. How can I test the Authentication mode of a particular SQL Server? I don’t want to pass a user Id and password across the network if I don’t have to.

A. If you can call SQLDMO functions directly, you’ll find an Enumeration (a group of SQL Server constants) called SQLDMO_LOGIN_TYPE. This Enumeration supports three login modes: (1) SQLDMOLogin_Standard, (2) SQLDMOLogin_NTUser, and (3) SQLDMOLogin_NTGroup.

You need to create an instance of the enumeration, test the login type and pass the appropriate values.

Q. I have a group of TSQL scripts that query SQL Server’s system tables. Can I continue to use them with SQL Server 7?

A. Yes, but be aware this isn’t good practice. If Microsoft change the structure of system tables in a later release your scripts may fail or provide inaccurate information. Consider instead using the new information schema views (for example information_schema.tables). These are system table independent, but still give access to SQL Server metadata.

Q. I’ve tried to load SQL Server 7, but the setup insists on loading Internet explorer 4.01 "or later". I’m perfectly happy with IE3; do I have to upgrade to IE4?

A. Unfortunately, yes. All operating systems require IE4.01 or later to be loaded before SQL Server 7 can be installed.

Q. Can I speed up SQL Server sort operations by choosing a non-default Sort Order?

A. In short, yes. The default remains Dictionary Order, Case Insensitive. A binary sort will be faster, but consider that you may not get result sets back in the order you expect. It’s up to check the sequence in which character data is returned.

Note also that changing the sort order after installation requires that you rebuild all your databases, and you can’t carry out backups and restores between SQL Servers with differing Sort Orders.

Q. Do I need to set the same Sort Order for both Unicode and non-Unicode data?

A. It’s recommended that you adopt the same Sort Order for both Unicode and non-Unicode data as you may experience problems migrating non-Unicode data to Unicode. In addition Unicode and non-Unicode data may sort differently.

Q. I’m having problems sending Email notifications to other SQL Servers. What could be the problem?

A. It could be a host of problems, but the most common is running under a LocalSystem account rather that a Domain User account. Under the LocalSystem account you can’t communicate with another server expecting a Trusted Connection.

Q. Can I still use the Net Start command to start SQL Server in single-user mode or with a minimal configuration?

A. Yes. To start SQL Server in single-user mode the syntax is net start mssqlserver –m typed at the Command Prompt. To start SQL Server with the minimum set of drivers loaded, type net start mssqlserver –f at the Command Prompt.

Q. After registering a SQL Server 7 I noticed the system databases weren’t visible in the Enterprise Manager. Have they been removed from the Enterprise Manager interface?

A. No, they’re still available to manage via the Enterprise Manager, but by default they’re not displayed.

Q. Under SQL Server 6.5 I managed Permissions using Grant and Revoke statements. How does the new Deny statement work?

A. A Revoke used to be the opposite of a Grant – removing a Permission to do something. Under SQL Server 7 a permission is removed by a Deny statement and the Revoke statement has become ‘neutral’. In other works you can Revoke a Grant or Revoke a Deny.

SQL Server, SQL Agent or the Distributed Transaction Co-ordinator can all be started, paused, and stopped using a right mouse-click on the System Tray icon.

The SQL Server Distributed Management Object (SQLDMO) library can be directly programmed to carry out almost any SQL Server, SQL Agent, or DTC task by manipulating properties and methods.

To give a visual display of the Execution Plan adopted by SQL Server, type the query into the Microsoft SQL Server Query Analyser and select the toolbar icon 'Display SQL Execution Plan'.

To output statistics from particular parts of the Execution Plan generated by the SQL Server Query Analyser, pause the pointer over the appropriate icon. For instance Index statistics, including the individual and total subtree costs, can be displayed by selecting the icon captioned with the index name.

The output from the SQL Server Query Analyser can be run into a grid, or output in raw format to the results pane. To generate the output in a grid, compose the query and click the toolbar icon 'Execute Query into Grid'.

When creating a new SQL Server database you have the option to increase the database size by fixed increments, or by a percentage of the current size of the database.

SQL Server automatically performs query parallelism with multi-processor computers. Operators provide process management, data redistribution, and flow control for queries which would benefit from query parallelism.

To determine who's currently logged on use the system procedure sp_who
SQL Server 7 automatically adjusts its memory requirements according to the current load on the operating system.

SQL Server 7 removes the administrative chore of periodically updating statistics by a combination of table, row and index sampling.

Connections to SQL Server should now be routed through the Microsoft Active Data Objects. SQL Server 7 provides the ADO 2.0 library which supersedes ODBC. Connection is made by calling an ADO Provider.

SQL Server 7 now provides full, cost-based locking: automatically de-escalating to a single-row lock as well as escalating to a Table lock.

Server Roles allow extensive privileges in one area of SQL Server Administration to be assigned, e.g. Process Administration, while implicitly denying all other Administration rights. The task of System Administrator can now be compartmentalised into a number of discrete, mutually-exclusive roles.

Single queries can now reference 32 tables, and the number of internal work tables used by a query is no longer limited to 16 as in earlier versions.

SQL Server tables now support multiple triggers of the same type - e.g. several Insert triggers on the same table. This allows greater flexibility of Business Rule processing.

The SQL Server Page size has increased to 8K removing the upper limit of 1960 bytes per data row.

SQL Server Extents are now 64K in size (increasing by a factor of eight). Multiple objects can now share the same Extent until they grow large enough to be allocated their own Extent. 'Uniform' extents are owned by a single object, all eight pages in the extent can only be used by the owning object. 'Mixed' extents are shared by up to eight objects. A new table or index is allocated pages from mixed extents. When the table or index grows to the point that it has eight pages, it is switched to a uniform extent.

SQL Server Tables now support up to 1024 columns.

The SQL Server ODBC Driver now supports the ODBC 3.5 API. The driver has also been enhanced to support the bulk copy functions originally introduced in DB-Library, and now possesses the ability to get metadata for linked tables used in distributed queries.

The SqlServer Distributed Management Object (SQLDMO) library can now be called using either of the two major scripting languages: VBScript or Jscript.

SQL Server 7 now supports Unicode data types: nchar, nvarchar, ntext. These DataTypes are exactly the same as char, varchar, and text, except for the wider range of characters supported and the increased storage space used. Data can be stored in multiple languages within one database, getting rid of the problem of converting characters and installing multiple code pages.

The maximum length of the char, varchar, and varbinary data types has been increased to 8000 bytes, substantially greater than the previous limit of 255 bytes in SQL Server 6.0/6.5. This growth in size allows Text and Image datatypes to be reserved for very large data values.

SQL Server 7 allows the SubString function to be used in the processing of both text and image data values.

GUID's (128 bit, Globally unique Identifiers) are now automatically generated via the UniqueIdentifier DataType.

Most of SQL Server's functionality is now supported on Windows 95/98. Exceptions are processes like Symmetric Multiprocessing, Asynchronous I/O, and integrated security, supported on NT platforms.

SQL Server's new Replication Wizard automates the task of setting up a distributed solution. Replication is now a simple task, and is significantly easier to set up, administer, deploy, monitor, and troubleshoot.

SQL Server 7 has introduced a new Replication model 'Merge Replication' allowing 'update anywhere' capability. Use Merge Replication with care however, as it does not guarantee transactional consistency, as does the traditional transactional replication.

SQL Server Tasks have now become multistep Jobs, allowing the Administrator to schedule the job, manage job step flow, and store job success or failure information in one central location.

Indexes are substantially more efficient in SQL Server 7. In earlier versions of SQL Server, nonclustered indexes used physical record identifiers (page number, row number) as row locators, even if a Clustered index had been built. Now, if a table has a clustered index (and thus a clustering key), the leaf nodes of all nonclustered indexes use the clustering key as the row locator rather than the physical record identifier. Of course, if a table does not have a clustered index, nonclustered indexes continue to use the physical record identifiers to point to the data pages.

Setting up SQL Server to use its LocalSystem account restricts SQL Server to local processes only. The LocalSystem account has no network access rights at all.

When setting up replication, it's sensible to set up the Publisher and all its Subscribers to share the same account.

Ensure you develop the appropriate standard for SQL Server's character set. If you need to change it later, you must rebuild the databases and reload the data. Server-to-server activities may fail if the character set is not consistent across servers within your organisation.

The SQL Server Upgrade Wizard can be run either following SetUp or later, at your convenience.

If appropriate, you can choose to Autostart any or all of the three SQL Server processes: the MSSQLServer itself, SQLAgent, or the Distributed Transaction Co-ordinator.

Rather than backing up data to a device such as disk or tape, SQL Server backs up data through shared memory to a virtual device. The data can then be picked up from the virtual device and backed up by a custom application.

Ensure you select the correct sort order when you first install SQL Server. If you need to change sort orders after installation, you must rebuild your databases and reload your data.

The simplest and fastest sort order is 'Binary'. The collating sequence for this sort order is based on the numeric value of the characters in the installed character set. Using the binary sort order, upper-case Z sorts before lower-case a because the character Z precedes the character a in all character sets.

Remember that SQL Server's options for dictionary sort orders (Case-sensitive, Case-Insensitive) carry with them a trade-off in performance.

Since SQL Server 6.0 login passwords have been encrypted. To check this, look in Master's syslogins Table What appears to be garbled text is actually a textual representation of the binary, encrypted password.

When using Windows 95/98 clients, client application and server-to-server connections must be over TCP/IP Sockets instead of Named Pipes. Named Pipes is not an available protocol in Windows 95/98.

To help trap the cause of SQL Server errors, while the error dialog still showing, look at Sqlstp.log in the \Windows or \WinNT directory. Check the last few events recorded in the log to see if any problems occurred before the error message was generated.

When upgrading from an earlier version of SQL Server, The Upgrade Wizard estimates only the disk space required. It cannot give an exact requirement.

When upgrading from SQL Server 6.x, remember to set TempDb to at least 25 MB in your SQL Server 6.x installation.

Forget memory tuning! Unlike SQL Server 6.x, SQL Server 7.0 can dynamically size memory based on available system resources.

SQL Server's Upgrade Wizard allows the selection of databases for upgrade to SQL Server 7 databases. If you run the SQL Server Upgrade Wizard again after databases have been upgraded, previously updated databases will default to the excluded list. If you want to upgrade a database again, move it to the included list.

All tasks scheduled by SQL Executive, for a SQL Server 6.x environment are transferred and upgraded so that SQL Server 7.0 can schedule and run the tasks in SQL Server Agent.

SQL Server's Quoted_Identifier setting determines what meaning SQL Server gives to double quotation marks. With 'Set Quoted_Identifier = Off, double quotation marks delimit a character string, in the same way that single quotation marks do. With 'Set Quoted_Identifier = On, double quotation marks delimit an identifier, such as a column name.

SQL Server 7 can be installed side-by-side with SQL Server 6.x on the same computer, however only one version can be run at one time. When the SQL Server Upgrade Wizard is complete, SQL Server 7 is marked as the active version of SQL Server. If you have enough disk space, it is a good idea to leave SQL Server 6.x on your computer until you are sure the version upgrade to SQL Server 7.0 was successful.

Failover Support for SQL Server 7 is designed to work in conjunction with Microsoft Cluster Server (MSCS). Managing Failover Support provides the ability to install a virtual SQL Server that is managed through the MSCS Cluster Administrator. MSCS monitors the health of the primary (active) and secondary (idle) nodes, the SQL Server application, and shared disk resources. Upon failure of the primary node, services will automatically 'fail over' to the secondary node, and uncommitted transactions will be rolled back prior to reconnection of clients to the database.

You can launch any Windows NT-based application from SQL Server's Enterprise Manager. External applications can be added and run from the Tools menu.

With SQL Server 7 exactly the same database engine can be used across platforms ranging from laptop computers running Microsoft Windows 95/98 to very large, multiprocessor servers running Microsoft Windows NT, Enterprise Edition.

SQL Server Performance Monitor allows the SQL Server Administrator to set up SQL Server-specific counters in the NT Performance Monitor, allowing monitoring and graphing of the performance of SQL Server with the same tool used to monitor Windows NT Servers.

In addition to a programmable library of SQLDMO SQL Server Distributed Management Objects), SQL Server 7 also offers access to the Object Library for DTS (Data Transformation Services).

The Windows 95/98 operating systems do not support the server side of the trusted connection API. When SQL Server is running on Windows 95 or 98, it does not support an Integrated Security model.

When running an application on the same computer as SQL Server, you can use refer to the SQL Server using the machine name or '(local)' or '.'.

SQL Server 7 uses a new algorithm for comparing fresh Transact-SQL statements with any Transact-SQL statements which have created an existing execution plans. If SQL Server 7 finds that a new Transact-SQL statement matches an existing execution plan, it reuses the plan. This reduces the performance benefit of pre-compiling stored procedures into execution plans.

To check Server Role membership, use sp_helpsrvrole; to extract the specific permissions for each role execute sp_srvrolepermission.

Every user in a SQL Server database belongs to the Public role. If you want everyone in a database to be able to have a specific permission, assign the permission to the public role. If a user has not been specifically granted permissions on an object, they use the permissions assigned to the Public role.

At the top of every SQL Server 8K Page is a 96 byte header used to store system information such as the type of page, the amount of free space on the page, and the object ID of the object owning the page.

Rows still can't span pages in SQL Server. In SQL Server 7, the maximum amount of data contained in a single row is 8060 bytes, excluding text, ntext, and image data, which are held in separate pages.

SQL Server 7 will automatically shrink databases that have a large amount of free space. Only those databases where the AutoShrink option has been set to true will be shrunk. The server checks the space usage in each database periodically. If a database is found with a lot of empty space and it has the AutoShrink option set to true, SQL Server will reduce the size of the files in the database.

SQL Server uses a Global Allocation Map (GAM) to record what extents have been allocated. Each GAM covers 64,000 extents, or nearly 4 GB of data. The GAM has one bit for each extent in the interval covered. If the bit is 1, the extent is free; if the bit is 0, the extent is allocated.

SQL Server Index Statistics are stored as a long string of bits across multiple pages in the same way that Image data is stored. The column sysindexes.statblob points to this distribution data. You can use the DBCC SHOW_STATISTICS statement to get a report on the distribution statistics for an index.

Because Non-Clustered indexes store Clustered index keys as their pointers to data rows, it is important to keep Clustered index keys as small as possible. Do not choose large columns as the keys to clustered indexes if a table also has Non-Clustered indexes.

In SQL Server 7, individual text, ntext, and image pages are not limited to holding data for only one occurrence of a text, ntext, or image column. A text, ntext, or image page can hold data from multiple rows; the page can even have a mix of text, ntext, and image data.

In SQL Server 7, Text and Image data pages are logically organized in a b-tree structure, while in earlier versions of SQL Server they were linked together in a page chain.

Because SQL Server 7 can store small amounts of text, ntext, or image data in the same Page, you can insert 20 rows that each have 200 bytes of data in a text column, with the data and all the root structures fitting onto the same 8K page.

User Connections are cheaper in SQL Server 7. Under SQL Server 6.5 each connection 'cost' 44K of memory; each connection under SQL Server 7 costs only 24K of memory.

If you need to maintain existing processes Pause rather than Stop your SQL Server. Pausing SQL Server prevents new users from logging in and gives you time to send a message to current users asking them to complete their work and log out before you Stop the server. If you stop SQL Server without Pausing it, all server processes are terminated immediately. Stopping SQL Server prevents new connections and disconnects current users. Note that you can't pause SQL Server if it was started by running sqlservr. Only SQL Server services started as NT services can be paused.

If you need to start SQL Server in minimal configuration to correct configuration problems, stop the SQLServerAgent service before connecting to SQL Server. Otherwise, the SQLServerAgent service uses the connection and blocks your connection to SQL Server.

When specifying a trace flag with the /T option, make sure you use an uppercase "T" to pass the trace flag number. A lowercase "t" is accepted by SQL Server, but this sets other internal trace flags that are required only by SQL Server support engineers.

Be careful when stopping your SQL Server. If you stop SQL Server using Ctrl+C at the command prompt, it does not perform a CHECKPOINT in every database. Therefore, the recovery time is increased the next time the server is started.

To shutdown your SQL Server immediately, issue 'SHUTDOWN WITH NOWAIT'. This stops the server immediately, but it requires more recovery time the next time the server is started because no CHECKPOINT is issued against any databases.

If you prefer to avoid the command line, data can be transferred into a SQL Server table from a data file using the 'Bulk Insert' statement. The Bulk Insert statement allows you to bulk copy data into SQL Server using the bcp utility with a Transact-SQL statement: e.g. Bulk Insert pubs..publishers FROM 'c:\publishers.txt' With (DataFileType = 'char').

When running the Bulk Copy Program (BCP), use the -n parameter where possible. Storing information in native format is useful when information is to be copied from one computer running SQL Server to another. Using native format saves time and space, preventing unnecessary conversion of data types to and from character format. However, a data file in native format cannot be read by any program other than BCP.

Native format BCP can now be used to bulk copy data from one computer running SQL Server to another running with a different processor architecture. This was impossible with earlier versions of SQL Server.

When running the Bulk Copy Program (BCP), the new -6 parameter, when used in conjunction with either native format (-n) or character format (-c), uses SQL Server 6/6.5 data types. Use this parameter when using data files generated by BCP in native or character format from SQL Server 6/6.5, or when generating data files to be loaded into SQL Server 6/6.5. Note that the -6 parameter is not applicable to the Bulk Insert statement.

With BCP, the SQL Server Char DataType is always stored in the data file as the full length of the defined column. For example, a column defined as Char(10) always occupies 10 characters in the data file regardless of the length of the data stored in the column; spaces are appended to the data as padding. Note that any pre-existing space characters are indistinguishable from the padding characters added by BCP.

When running the Bulk Copy Program (BCP), choose terminators with care to ensure that their pattern does not appear in any of the data. For example, when using tab terminators with a field that contains tabs as part of the data, bcp does not know which tab represents the end of the field. The bcp utility always looks for the first possible character(s) that matches the terminator it expects. Using a character sequence with characters that do not occur in the data avoids this conflict.

When running the Bulk Copy Program (BCP), you may decide to drop the indexes on the table prior to loading a large amount of. Conversely, if you are loading a small amount of data relative to the amount of data already in the table, dropping the indexes may not be necessary because the time taken to rebuild the indexes can be longer than performing the bulk copy operation.

If, for any reason, a BCP operation aborts before completion, the entire transaction is rolled back, and no new rows are added to the destination table.

Following a BCP operation, it's necessary to identify any rows that violate constraints or triggers. To do this run queries or stored procedures that test the constraint or trigger conditions, such as: UPDATE pubs..authors SET au_fname = au_fname. Although this query does not change data to a different value, it causes SQL Server to update each value in the au_fname column to itself. This causes any constraints or triggers to fire, testing the validity of the inserted rows.

During BCP, users often try to load an ASCII file in native format. This leads to misinterpretation of the hexadecimal values in the ASCII file and the generation of an "unexpected end of file" error message. The correct method of loading the ASCII file is to represent all fields in the data file as a character string (i.e character format BCP), and let SQL Server do the data conversion to internal data types as rows are inserted into the table.

During BCP In, a hidden character in an ASCII data file can cause problems, generating an "unexpected null found" error message. Many utilities and text editors display hidden characters which can usually be found at the bottom of the data file. Finding and removing these characters should resolve the problem.

With BCP, it's possible to specify the number of rows to load from the data file rather than loading the entire data file. For example, to load only the first 150 rows from a 10,000 row data file, specify the -L last_row parameter when loading the data.