Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Beginning T-SQL with Microsoft SQL Server 2005 and 2008 ISBN: 978-0-470-25703-6
This is the forum to discuss the Wrox book Beginning T-SQL with Microsoft SQL Server 2005 and 2008 by Paul Turley, Dan Wood; ISBN: 9780470257036
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning T-SQL with Microsoft SQL Server 2005 and 2008 ISBN: 978-0-470-25703-6 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old September 9th, 2009, 10:19 AM
Authorized User
 
Join Date: Mar 2009
Posts: 64
Thanks: 0
Thanked 1 Time in 1 Post
Default Use of upper and lower case

I know enough to be dangerous in that I have been using SQL Server 2005 by following examples from the code of others and using the help system. In reading your very enjoyable book I am being informed of a number of subtle and not so subtle bits that will make my work easier.

In using SQL I have been pretty lax about the use of case. I have found that "SELECT" and "select" work equally well. Further, that case has not seemed to matter in my work in referring to database objects.

In the AdventureWorks database case does seem to matter. The example at the bottom of p. 180 does not work since the column name is "ProductSubcategoryID" not "ProductSubCategoryID". I have not seen this in the databases I use.

Is there some setting for a given database that makes the case of object names important?
Reply With Quote
  #2 (permalink)  
Old September 9th, 2009, 02:19 PM
Registered User
 
Join Date: Dec 2008
Location: Silverdale, WA.
Posts: 2
Thanks: 1
Thanked 0 Times in 0 Posts
Default Case Sensitive Object Names

In a default installation of SQL Server the collation for the server and its databases is generally set to "SQL_Latin1_General_CP1_CI_AS" CP1 = Code Page 1, CI = Case Insensitive and AS = Accent Sensitive.
However, collation can be set at the database level:

CREATE DATABASE [BinaryDB] ON PRIMARY
( NAME = N'BinaryDB'
, FILENAME = N'D:\SQLData\BinaryDB.mdf' )
LOG ON
( NAME = N'BinaryDB_log'
, FILENAME = N'E:\SQLLog\BinaryDB_log.ldf')
COLLATE Latin1_General_BIN

and at the column level of a table:

CREATE TABLE MyBinarySortTable
(Column1 varchar(10) COLLATE SQL_Latin1_General_BIN)

If the database collation is set to a Binary collation or a Case-Sensitive collation like
"SQL_Latin1_General_CP1_CS_AS" then all references to object names must be case-correct. From your post I would assume that your installation of AdventureWorks is case-sensitive. You can check this by looking at the Options page of Database properties in Management Studio or query the sys.databases system view:
SELECT name, collation_name FROM sys.databases

SQL commands are not case-sensitive regardless of the server collation so "select" and "where" will work just as well as "SELECT" and "WHERE" will.

There is a good description of the collation settings and their effect on data retrieval in Chapter two of the Beginning SQL Server 2008(2005) Administration books. (Shameless plug for another WROX Book <grin>)


Hope this helps!
-Dan Wood
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
lower case kgoldvas XSLT 3 February 28th, 2007 06:43 AM
how to make lower case in dataformatstring hardik Classic ASP Databases 1 February 14th, 2006 04:45 AM
check for lower case (LCase)? crmpicco Excel VBA 2 May 12th, 2005 03:42 PM
search string either Upper case or lower case rylemer Beginning VB 6 3 March 24th, 2004 03:23 PM
Checking for upper,lower case, number and symbol dumbdumb SQL Server 2000 1 March 18th, 2004 02:51 AM



All times are GMT -4. The time now is 01:12 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.