Wrox Programmer Forums
| 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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #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?
  #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


Similar Threads
Thread Thread Starter Forum Replies Last Post
lower case kgoldvas XSLT 3 February 28th, 2007 07:43 AM
how to make lower case in dataformatstring hardik Classic ASP Databases 1 February 14th, 2006 05: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 04:23 PM
Checking for upper,lower case, number and symbol dumbdumb SQL Server 2000 1 March 18th, 2004 03:51 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.