Tips from the SQL Server MVPs - 03 Jan 2001

Editor’s Note: This is the first installment in a monthly, Web-only series of SQL Server tips brought to you by the Microsoft SQL Server Most Valuable Professionals (MVPs). Microsoft introduced the MVP program in the 1990s to recognize members of the general public who donate their time and considerable computing skills to help users in various Microsoft-hosted newsgroups. MVPs are nominated by Microsoft support engineers, team managers, and other MVPs who notice a participant's consistent and accurate technical answers in various electronic forums and other peer-to-peer venues. For more information about the MVP program, go to http://support.microsoft.com/support/mvp. The MVPs donate their SQL Server Magazine author fees for these tips to the World Food Programme (http://www.wfp.org).

SQL Server Resources

Where can I find answers to my SQL Server questions?

The best place to start is with the documentation Microsoft supplies with SQL Server—Books Online (BOL). Although most SQL Server developers are familiar with BOL, a surprising number of them have never consulted this resource.

BOL automatically loads when you install SQL Server. You can access BOL by selecting Start, Microsoft SQL Server, Books Online. The interface to BOL is fairly intuitive, so we’ll just add one additional hint: When you’re searching for a specific phrase, enclose it in double quotes. For example, in SQL Server 2000 BOL, a search for the phrase CREATE SCHEMA (without double quotes) yields 421 topics.However, a search for the phrase "CREATE SCHEMA"(with double quotes) reduces the number of returned topics to just four. Note that you must use double quotes, not single quotes, to ensure that your search returns only topics containing the entire phrase instead of topics that contain each word separately.

Case-Sensitive Comparisons

How can I program a case-sensitive comparison of a user-entered password on a case-insensitive SQL Server 7.0 instance?

If you upgrade your system to SQL Server 2000, you’ll be able to specify data collation down to the column level. (The SQL Server 2000 BOL glossary defines collation as "A set of rules that determines how data is compared, ordered, and presented. Character data is sorted using collation information, including locale, sort order, and case-sensitivity.")

However, until you upgrade to SQL Server 2000, you can use the following technique. Let’s assume that the value of the password stored in your table is BamBi2000. Notice that the "Bs" are uppercase, whereas all other letters are lowercase.

DECLARE @user_password varchar(12)IF CAST (@user_password AS varbinary(12)) =CAST ('BamBi2000' AS varbinary(12))PRINT 'Password match'ELSEPRINT 'Password mis-match'

SQL Server 2000 Performance Feature

Does SQL Server 2000 include features that will help me gain performance over SQL Server 7.0?

Yes. If you’re using temporary tables, consider replacing them with table variables, the new table data type. Temporary tables—real, physical tables—reside in the tempdb database. Because they’re physical tables, they require I/O both to the tempdb database and to the transaction log. Table variables, by contrast, reside in memory. A decrease in I/O correlates to an increase in performance.

Of course, if you use temporary tables extensively and decide to switch to table variables, you might need to increase the amount of memory that you allocate to SQL Server.

Date Errors

I'm migrating a Microsoft Access application to SQL Server, and I'm experiencing a problem moving the data over successfully. Any clues to the errors I should be looking for?

One glitch is that Access supports dates beginning from January 1, 100, and Microsoft Visual FoxPro permits dates from January 1, 1, whereas SQL Server supports dates from January 1, 1753.

You might not think that dates prior to January 1, 1753, exist in your application, but data-entry people can make mistakes, creating dates that are valid in Access and Visual FoxPro but invalid in SQL Server. A typing error can easily turn May 12, 1990, into May 12, 199, or May 12, 190.

Leveraging String-Manipulation Functions

I have a string containing Yes/No answers to a series of questions. How can I quickly count the number of Yes answers and the number of No answers in the string?

For SQL Server 7.0 and later, you can submit:

DECLARE @TestString varchar (12)SET @TestString = 'YNYYNNYYYNNN'SELECT NumY = LEN (REPLACE (@TestString, 'N', '')),NumN = LEN (REPLACE (@TestString, 'Y', ''))

Although SQL Server doesn’t support many built-in string manipulation functions, with a little bit of ingenuity, you can often use the existing functions to great advantage.

Who Needs Stored Procedures?

If you’re programming in a client/server environment, you can make quick application changes by encapsulating the SQL script in stored procedures. For example, stored procedures let you make either business logic adjustments or program fixes in a single place and instantly distribute the new logic without touching the client side.

Of course, most applications these days have moved from two-tier, client/server architecture to a multitier intranet- or Internet-aware architecture with business logic in a middle tier. But we can still make a compelling argument for stored procedures. You can consider stored procedures a precursor to OOP. One of OOP’s strengths is that the data structures and logic you use to implement an object are in a "black box"—only the object's methods and properties are exposed. The mechanism used to implement an object is hidden from the database developer.

By using stored procedures, the developer maintains the freedom to alter the underlying database schema without changing the user interface. If you have to alter your tables’ design, you might be able to implement your schema changes without affecting the front-end or middle-tier levels.

Granting Permissions on Database Objects

Why do I have to give permissions on the underlying database objects when I'm using dynamic SQL?

Consider the following simple stored procedure:

USE pubsGOCREATE PROCEDURE GeneralSelect  @TableName SYSNAMEASEXEC ('SELECT * FROM ' + @Tablename)GO

You’re probably expecting that your stored procedure will make a call that looks something like

USE pubsEXEC GeneralSelect 'authors'

However, consider the consequences of someone passing to your stored procedure the following:

USE pubsEXEC GeneralSelect 'authors  DROP TABLE authors'

If you, the creator of the stored procedure, were a member of the db_owner role in the Pubs database, and your users needed only the EXECUTE privilege on the stored procedure, then this command would drop the authors table. SQL Server protects against such unauthorized actions by requiring that users possess the appropriate permissions on the database objects referenced within dynamic SQL.

ANSI JOIN vs. OUTER JOIN

Can I improve performance by using the ANSI-style joins instead of the old-style joins?

You won’t realize performance gains by switching to the ANSI-style JOIN syntax. However, when you’re dealing with outer joins, the old-style JOIN (=*, *=) isn’t the exact equivalent of the ANSI-style JOIN.

We strongly suggest that you move to the ANSI-style JOIN coding style. Although old habits are hard to break (we know, we've been there), we’ve found that after you’ve become comfortable with the ANSI-style JOIN, you’ll quickly prefer using it to code queries. Using the ANSI-JOIN syntax gives you an important advantage: Because the join logic is cleanly separated from the filtering criteria, you can understand the query logic more quickly.

The one gotcha is that you shouldn’t expect to be able to mechanically convert an old-style OUTER JOIN to an ANSI-style JOIN and achieve the same output. You might not replicate the results, because the SQL Server old-style JOIN executes the filtering conditions before executing the joins, whereas the ANSI-style JOIN reverses this procedure (join logic precedes filtering).

Perhaps the most compelling argument for switching to the ANSI-style JOIN is that Microsoft has explicitly stated that SQL Server won’t support the old-style OUTER JOIN syntax indefinitely. Another important consideration is that the ANSI-style JOIN supports query constructions that the old-style JOIN syntax didn’t support.

ORDER BY Workaround

Why doesn't SQL Server permit an ORDER BY clause to be included in the definition of a view?

SQL Server excludes an ORDER BY clause from a view to comply with the ANSI SQL-92 standard. Because the rationale for this standard requires a discussion of SQL’s underlying structure and the mathematics upon which it is based, we can’t fully explain the restriction here. However, if you need to be able to specify an ORDER BY clause in a view, consider using the following workaround:

USE pubsGOCREATE VIEW AuthorsByNameASSELECT TOP 100 PERCENT *FROM   authorsORDER BY au_lname, au_fnameGO

The TOP construct, which Microsoft introduced in SQL Server 7.0, is most useful when you combine it with the ORDER BY clause. The only time that SQL Server supports an ORDER BY clause in a view is when it's used in conjunction with the TOP keyword.

Note that the TOP keyword is a SQL Server extension to the ANSI SQL-92 standard.

Bulk Copy Program Utility

How can I create a plain-text flat file from SQL Server as input to another application?

XML’s purpose is to solve challenges like this, but until all applications become XML-enabled, consider using our faithful standby, the bulk copy program (bcp) utility. This utility can do more than just dump a table; bcp also can take its input from a view instead of from a table. After you specify view as the input source, you can limit the output to a subset of columns or to a subset of rows by selecting appropriate filtering clauses (WHERE and HAVING).

More important, by using a view you can export data from multiple joined tables. The only thing you can't do is specify the sequence in which the rows are to be written to the flat file, because a view doesn't permit you to include an ORDER BY clause in it. However, you can use the workaround listed in the previous tip.

If you want to generate the data in a particular sequence, or if you can’t predict the content of the data you want to export, be aware that besides a view, bcp also supports using an actual query. The only "gotcha" on using a query other than a table or view is that you must specify "queryout" in place of "out" in the bcp command line.

For example, you can use bcp to generate from the Pubs database a list of authors who reside in California by writing the following code:

bcp "SELECT * FROM pubs..authors WHERE state = 'CA'" queryout c:CAauthors.txt -c –T -S