Case Sensitive Settings

Congratulations to Dimitar Dimitrov and Marcos Kirchner, a database consultant and autonomous web developer in Blumenau, Santa Catarina, Brazil. Dimitar won first prize of $100 for the best solution to the July Reader Challenge, "Case Sensitive Settings." Marcos won second prize of $50. Here’s a recap of the problem and the solution to the July Reader Challenge.

Problem:

Marat is a software developer in a company that develops data-modeling applications. The database-modeling application includes some modifications for SQL Server 2000. One of the features lets users create databases on the server. As part of the database creation process, Marat wants to detect if the SQL Server supports use of database names that are case sensitive. Help Marat determine this setting of the SQL Server so that the application can handle it appropriately.

Solution:

Marat can use the system stored procedure sp_server_info to get various attributes of the SQL Server installation. Some of these attributes include length of identifiers, maximum number of columns in an index, and if descending indexes are allowed. One of the attributes is IDENTIFIER_CASE, which shows if names of tables, columns, and stored procedures in the database are case-sensitive. The system stored procedure sp_server_info takes the attribute identifier as an input parameter and Marat can use this to retrieve the setting for IDENTIFIER_CASE. The command to get the attribute follows (the attribute ID for IDENTIFIER_CASE is 16):

EXEC master.dbo.sp_server_info 16-- Sample output is shown below/*attribute_idattribute_name attribute_value------------    ---------------  ---------------16        IDENTIFIER_CASEMIXED*/

Finally, Marat also needs to ensure that the stored procedure is called using the fully qualified name so that execution occurs in the context of the master database and thus will return identifier settings specific to that database. Because the master database contains the names of all the other databases on the server, this will indicate if the names of the databases are case-sensitive. To view the full set of identifier settings on any server, run

USE masterGOEXEC sp_server_info

AUGUST READER CHALLENGE:

Now, test your SQL Server savvy in the August Reader Challenge, "Partitioning Tables" (below). Submit your solution in an email message to [email protected] by July 21. Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We’ll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Problem:

Dan is a software consultant who works with SQL Server 2000. One of his clients has a table that uses a uniqueidentifier column as a primary key. The volume of data in the table is increasing considerably, resulting in performance and management problems. One of the solutions Dan is considering is to partition the existing table based on the primary key column values. The script below creates a sample table with a uniqueidentifier column:

SELECT TOP 10000 newid() AS guidINTO SalesDetFROM master.dbo.sysobjects AS o1 CROSS JOIN master.dbo.sysobjects AS o2 CROSS JOIN master.dbo.sysobjects as o3ALTER TABLE SalesDet ALTER COLUMN guid uniqueidentifier NOT NULLALTER TABLE SalesDet ADD CONSTRAINT pk_SalesDet PRIMARY KEY( guid )

Help Dan write a query that he can use to partition the table into a specified number of buckets. For example, Dan wants to partition the table into 13 different partitions based on the guid column and have the rows evenly distributed between the partitions.