| Downloads |
|---|
| 37575.zip |
"Small" Difference Between Smallint and Tinyint
Itzik Ben-Gan once told me that no correction is too small, but clarifying the difference between the smallint and the tinyint data types is certainly a "small" matter. In his T-SQL Black Belt column "No Table? No Problem" (January 2003, InstantDoc ID 27051), Ben-Gan says, "The smallint data type is sufficient for up to eight types, which is usually more than enough" for a day_type column. However, his table uses the tinyint data type and shows 4 bits commented out. Technically, the code is correct, but it doesn't match the text explanation, which talks about smallint. And unless SQL Server is using the extra 4 bits in the column for something else, the smallint data type is actually sufficient for up to 16—not 8—types.
You're absolutely right. The table correctly uses the tinyint data type, which contains 8 flags, but in the text, I mistakenly refer to the smallint data type, which contains 16 flags.
Be Sensitive About Case
I found Gregory A. Larsen's article "Avoiding the Red Zone" (December 2002, InstantDoc ID 26874) very valuable. Usually, DBAs manually gather information about the space their databases use, but the article describes an easy and convenient two-step process to stay informed about your server space usage. I wanted to note, however, that the script that the article's usp_get_dbstats stored procedure generates works only on SQL Servers that are configured to use the case-insensitive setting. In the stored procedure, the author creates temporary table #tmplg with column names in upper- and lowercase, then references the column names later in the script in just lowercase. Listing 1 highlights the column names as they appear in the article's procedure. If you execute the script in the case-sensitive environment, you'll receive the following error message:
Server: Msg 207, Level 16, State 3, Line 6 Invalid column name 'dbname'.
Thanks for finding this error. Developers get used to writing code for their own environment and sometimes forget the big picture. Microsoft lets you install SQL Server with a number of different preferences, depending on your organization's needs. In our organization, we decided to set up our SQL Server to use the case-insensitive setting.
The flaw you identified brings up an important issue. When developing applications, we all need to be aware of how different server settings might affect our code. Some server settings, such as case preference, might even cause code to fail if you don't take care to match case on all identifier references.
