Frequently asked questions - SQL Server 2000 - Server

Questions and Answers

1. Q: What is the difference between DBCC INDEXDEFRAG and DBCC DBREINDEX?
   
   A: Unlike DBCC DBREINDEX or any general index build, DBCC INDEXDEFRAG is an online operation, so it does not hold long-term locks that can block running queries or updates. Depending on the amount of fragmentation, DBCC INDEXDEFRAG can be considerably faster than running DBCC DBREINDEX due to the fact that a relatively unfragmented index can be defragmented much faster than a new index can be built. Also, another advantage is that with DBCC INDEXDEFRAG the index is always available, unlike DBREINDEX. Note however, that a large amount of fragmentation can cause DBCC INDEXDEFRAG to run considerably longer than DBCC DBREINDEX, which may or may not outweigh the benefit of the command's online capabilities. Also, DBCC INDEXDEFRAG will not help if two indexes are interleaved on the disk because INDEXDEFRAG shuffles the pages in place. To improve the clustering of pages, rebuild the index.
2. Q: When I create a table, I get the following 2714 error message:
   
   
   Warning: The table '%.*ls' has been created but its maximum row size (%d) exceeds the maximum numberof bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
   
   The table creation succeeds and data can be inserted without problems. What did this error message mean?
   
   A: This error message indicates that you have variable length columns in your table (such as nvarchar or varbinary) and that the total maximum length of all the columns adds up to more than 8060 bytes. You can still insert rows into the table provided that the total length of the data in each row does not exceed 8060 bytes. However, if the data does exceed 8060 bytes, the insertion fails with the following error message:
   Server: Msg 511, Level 16, State 1, Line 5
   Cannot create a row of size <rowlength> which is greater than the allowable maximum of 8060.
   The statement has been terminated.
   
3. Q: A user-defined function returns a table that is schemabound to two tables in my database. According to the documentation, the referenced tables cannot be altered until the schemabound option is removed. However, I am able to add or delete columns from these referenced tables. Shouldn't this give me an error?
   
   A: Not if you alter columns that are not referenced by the function. You cannot drop or alter the columns of a table that are involved in the schemabinding, but you can alter other columns that are not involved in the schemabinding. For example, let's say we have a schemabound table, table1, defined with 5 columns: c1, c2, c3, c4, and c5. If the function only references c1 and c3, only columns c1 and c3 cannot be altered. Columns c2, c4, and c5 can be altered as needed.
4. Q: How can I set the database to single user mode and restrict the access to dbo use only?
   
   A: In SQL Server 2000, a database cannot be in single-user mode with dbo use only. Instead, the following alternative options are available by using the ALTER DATABASE command:
   • ALTER DATABASE database SET SINGLE_USER.
     
     This command restricts access to the database to only one user at a time.
     
   • ALTER DATABASE database SET RESTRICTED_USER.
     
     This command restricts access to the database to only members of the db_owner, dbcreator, or sysadmin roles.
     
   • ALTER DATABASE database SET MULTI_USER.
     
     This command returns access to the database to its normal operating state.
5. Q: Can I run multiple instances of SQL Server 2000 at the same time on one computer?
   
   A. Yes, please refer to the "Multiple Instances of SQL Server" topic in SQL Server Books Online for more information.
6. Q: Are DB-Library applications still supported in SQL Server 2000?
   
   A: Yes. However, DB-Library has not been enhanced for SQL Server 2000. DB-Library ships with the same features that the Microsoft SQL Server 7.0 DB-Library contained. This means that a DB-Library application can only connect to a default instance of SQL Server 2000, it cannot connect to a named instance. In addition, it won't understand any of the new features available in SQL Server 7.0 and later.
7. Q: Do I need to use the multi-protocol network library to enable encryption?
   
   A: No, Microsoft SQL Server 2000 can use the Secure Sockets Layer (SSL) to encrypt all data transmitted between an application computer and a SQL Server instance on a database computer. The SSL encryption is performed within the Super Socket Net-Library (Dbnetlib.dll and Ssnetlib.dll) and applies to all inter-computer protocols supported by SQL Server 2000. For more information, see the "Net-Library Encryption" topic in SQL Server Books Online.
8. Q: Why does my SQL statement work correctly outside of a user-defined function, but incorrectly inside it?
   
   A: You have probably included a statement in the BEGIN-END block that has side effects, which is not allowed in user-defined functions. Function side effects are any permanent changes to the state of a resource that has a scope outside the function. Changes can only be made to local objects such as local cursors or variables. Examples of actions that cannot be performed in a function include modifications to database tables, operations on cursors that are not local to the function, sending e-mail, attempting a catalog modification, and generating a result set that is returned to the user.
9. Q: How can I qualify a named instance in a linked server query?
   
   A: You must use square brackets around the multi-instance linked server name in the four part query. For example:
   
   

   SELECT * FROM [myServer\sql80].northwind.dbo.customers