Questions, Answers, and Tips About SQL Server - 29 Oct 1999

How will Windows 2000 (Win2K) affect SQL Server users?

We recently installed Win2K beta 3 and SQL Server 7.0 Service Pack 1 (SP1), but we haven't had time to discover the new functionality Win2K will provide SQL Server users or track down potential gotchas. However, we have a few predictions about how Win2K will affect SQL Server users.

Win2K migration planners consistently point to Active Directory (AD) as a major concern because it's so different from current domain structures. We figure that AD will be smart enough to incorporate SQL Server's login and permissions information, but not without tweaking. (In particular, consider using security account delegation for distributed queries.) To prepare for AD, we're performing internal security audits to make sure we know the security status of all our servers and databases before AD discovers their status.

Win2K will offer very large memory (VLM) support, which will be useful for large SQL Server data warehouses. SQL Server will be able to address 32GB of memory on 64-bit systems running Win2K.

We think SQL Server 7.0 is making inroads into high-end corporate accounts. Many Oracle customers say they would consider SQL Server 7.0 if not for Windows NT 4.0. In other words, they're waiting to see how Win2K Datacenter Server (Datacenter) performs.

Today, most people snicker when the subject of NT 4.0's clustering support comes up. Microsoft Cluster Server (MSCS) currently supports only two-node failover, and you can't build a true 24 x 7 SQL Server environment that provides only 99.999 percent of guaranteed uptime. Win2K will offer much better clustering support. Initially, SQL Server will support four-node failover clusters.

Finally, we think Microsoft will roll out Knowledge Management (KM) and data mining products at the same time the company rolls out Win2K. So we're starting to think of business problems we can solve using forthcoming SQL Server-based solution frameworks.

In SQL Server Books Online (BOL) under sp_addlinkedsrvlogin, I read that SQL Server 7.0 supports security account delegation. That support lets me establish a trusted connection to SQLServer1 and use the same Windows NT account credentials to run a distributed query on SQLServer2. However, I can't make this feature work. Do you have any suggestions?

Unfortunately, in BOL, Microsoft doesn't clarify one very important fact: NT 4.0 and Windows 9x don't support security account delegation. In theory, SQL Server 7.0 supports this capability, but you have to wait until Windows 2000 (Win2K) to use it.

After I upgraded from SQL Server 6.5 to SQL Server 7.0, some of my Transact SQL (T-SQL) string-manipulation code doesn't work. For example, when I concatenate NULL values to an existing string, SQL Server sets the entire string equal to NULL. How can I change this erroneous behavior?

You discovered one of the biggest gotchas that can result from upgrading to SQL Server 7.0. To make SQL Server compliant with ANSI-92 standards, SQL Server 7.0's default NULL settings are different from earlier versions of SQL Server. For evidence of this problem, run the following statement under SQL Server 6.5:

SELECT "Start" + NULL + "Finish"

SQL Server 6.5 outputs the following result set:

------------Start Finish

When you run the same command under a new SQL Server 7.0 installation, you receive the following output:

-------------NULL

The ANSI-92 standard states that if you concatenate a NULL value onto an existing string, the result set is NULL. This statement makes sense because 5 + NULL = NULL. SQL Server 6.5 is technically wrong because it treats the concatenation of NULL as an empty string, and NULL isn't the same as an empty string.

Fortunately, Microsoft provides a way to toggle between the old and new ANSI-compliant behavior using the SET CONCAT_NULL_YIELDS_NULL command. SQL Server Books Online (BOL) states, "When SET CONCAT_NULL _YIELDS_NULL is ON, concatenating a NULL value with a string yields a NULL result. For example, SELECT 'abc' + NULL yields NULL. When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a NULL value with a string yields the string itself (i.e., SQL Server treats the NULL value as an empty string). For example, SELECT 'abc' + NULL yields 'abc'."

To reestablish the SQL Server 6.5 behavior that you expect, enable the SET option, as in the following statement:

SET CONCAT_NULL_YIELDS_NULL OFFgo

However, if you use this method, you won't be ANSI-92 compliant.

Can you store data on compressed volumes?

In the Microsoft article "INF: SQL Server Databases Not Supported on Compressed Volumes" (http://support.microsoft.com/ support/kb/articles/q231/3/47.asp), Microsoft explains that storing data on compressed volumes is physically possible but extremely dangerous. Compressed volumes don't guarantee sector-aligned writes, which you need to ensure transactional recovery in some circumstances. The article also describes other complications, including performance degradation and internal recovery space management concerns. Storing data on compressed volumes is playing with fire.

Using SQL Server 7.0, I tried putting a TRUNCATE TABLE statement inside a transaction, and I discovered that I was able to roll back the operation. In SQL Server 6.5, TRUCATE TABLE is a nonlogged operation, so you can't include it in a transaction and you can't issue a DUMP TRANSACTION LOG statement until you perform a full DATABASE DUMP. Is SQL Server 7.0's behavior a bug?

Good catch! In SQL Server 7.0, Microsoft enhanced TRUNCATE TABLE to function as a regular logged SQL statement. Using TRUNCATE TABLE is equivalent to issuing a DELETE TABLE statement without supplying a WHERE clause. However, TRUNCATE TABLE is faster because the DELETE statement removes each row and the TRUNCATE TABLE statement deallocates pages and doesn't cause triggers to fire. Unfortunately, in SQL Server 7.0, permission still defaults to the table owner, which is usually Database Owner (DBO), and you can't assign permission to individual users. Therefore, TRUNCATE TABLE is valuable only for maintenance.