SQL Server Q & A - 26 Jan 2000 | ITPro Today: IT News, How-Tos, Trends, Case Studies, Career Tips, More

I'm working with different programming languages and sort orders in databases. Do I need to reinstall SQL Server 7.0 with the new sort order every time I import and export data?

Yes, you need to reinstall SQL Server to rebuild user databases in SQL Server 7.0. You need to rebuild the Master database after you export data to ASCII text files with bulk copy program (bcp) or Data Transformation Services (DTS). Then you import the databases with the new sort order to the server with bcp, BULK INSERT, or DTS.

To increase performance, when should I use the EXISTS clause? For example, Table1 has a unique nonclustered index on col1. Which statement will perform better?

IF EXISTS (SELECT * FROM table1 WHERE col1
  = or
IF EXISTS (SELECT 1 FROM table1 WHERE col1
  = Is the execution strategy different in SQL Server 6.5 and 7.0?

The query optimizer in both SQL Server 6.5 and 7.0 will use the same execution plan, so it 

doesn't matter which way you write an EXISTS clause. In general, SQL Server 7.0 performs better than SQL Server 6.5, but if the query is small, you might not be able to distinguish the performance difference. 

 In SQL Server 7.0, 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 and SQL Server treats the null value as an empty string. For example, 

SELECT abc + NULL 

yields abc. If you don't override the CONCAT setting specifically, the null database option applies. This method is good for string concatenation, but does SQL Server 7.0 have an option for numeric or integer concatenation? For example, 

SELECT 1234 + NULL

will result in null, whereas the result should be 1234. 


SQL Server 7.0 doesn't control numeric null additions. According to ANSI standards, null means unknown, so any number plus an unknown by definition results in an unknown value. The option for character strings is only to support backward compatibility. Also, according to the definition, the correct results won't be 1234. If you want 1234 from the above query, you should add 0 or use the ISNULL() function to get a numeric value before you try a math operation. Use CONVERT if you need to treat a numeric value as a string. 

In SQL Server 7.0, what's the best way to transfer a database to a new server? 


The easiest way to transfer a current database to a new server is to use the sp_detach_db and sp_attach_db stored procedures. This option won't move your settings from the Master and msdb databases, so you might want to back them up on your current server and restore them on your new server. You can then put the files in the same drive letter locations as before, and the server won't know the difference. However, you need to change the name of the server in the sysservers table.

Comments

Plain text