Case Sensitive of MSSQL2005

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=246692&SiteID=1
Form reply of "Absolute_Zero":
In order to get the case senstive to work, go inside SQL 2005 and right click the column and choose modify. Under neith the table designer field click the Collation button which will bring you to more advanced options. Next, choose Windows Collation | Dictionary Sort | Case Sensitive. Then queries ran against the column will now check for case sensitivity.

I got a problem.
1) MSSQL2005, by default, data is non case-sensitive?
2) How to use SQL cmd to change "Windows Collation | Dictionary Sort | Case Sensitive"?
3) How to make MSSQL2005, default is case-sensitive?

The default collation is set upon instalaltion time, for changing either tables / database or the whole server, refer to:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36707

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Working with collations is documented in the SQL Server Books Online.

Working with collations

Setting and changing collations

Cheers,

Dan

sql

Case in SSAS2005 KPI:s

Mosha posted this interesting entry in his Blog: http://www.sqljunkies.com/WebLog/mosha/archive/2007/01/28/iif_performance.aspx

MDX CASE is a performance killer in SSAS 2005 and Mosha have many useful advices in this post about how to avoid the problems.

My short question is simply that I would like a follow up on this because the Adventure Works demo cube uses CASE frequently. I also now from reading the book that Edward Melomed participated in that when you write a KPI a lot of things are happening that you cannot see, like the creation of calculated members.

How should I make sure that my KPI:s will not hurt performance of my cubes, if I use CASE to frequently?

Should I rewrite them as IIF:s?

Kind Regards

Thomas Ivarsson

Hello Thomas,

As a person who wrote that chapter, I have to clarify a little bit. For KPIs AS indded creates calculated measures under the covers, but those members are hidden, and won't affect performance of your cube, unless you explicitly call them from MDX. So, MDX expressions used as KPI properties will only affect performance of KPIs themselfs. My gut-feeling tells me that it not so critical to optimize KPIs, I don't expect them to be executed over a large dataset, I expect them to be run on the aggregated values. If you really concerned about performace of KPIs you can consider converting to IIF, but take into consideration that the best solution proposed by Mosha-using SCOPE operator, won't work in this case, since you really need to compare the cell values.

Irina

|||

Thank's Irina.

It can be useful to drill down on KPI:s but probably not down to the leaf level.

Regards

Thomas Ivarsson