Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Clustered Index Analyser Expand / Collapse
Author
Message
Posted Thursday, July 1, 2010 5:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 10, 2014 3:37 AM
Points: 1, Visits: 255
Thanks for sharing this script. i just tried it on 2008, working fine. But getting errors while running this script on 2000.
Post #946096
Posted Friday, February 18, 2011 3:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, December 14, 2014 5:56 PM
Points: 114, Visits: 459
Wonderful script, Cameron. Thanks!

I have to come up with an Auditing solution for data changes and you script helped me happily find out that most of my tables have only one column for the clustered index which is almost always the primary key. This will make my Auditing solution easier.

Very useful, thank you so much.




G. Milner
Post #1066678
Posted Thursday, July 14, 2011 4:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 12:41 PM
Points: 20, Visits: 94
Everything worked well until the script encountered a partitioned table.
Post #1141610
Posted Tuesday, July 19, 2011 8:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 14, 2014 1:44 AM
Points: 15, Visits: 464
Hello Cameron,
I am always trying to learn something.
I ran your script and got:
Msg 468, Level 16, State 9, Line 152
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

I do not know enough about the scripts to make it running but possibly you can handle that.

Thanks,
Dewes.


Dewes van Lohuizen,
DBA at CSC Netherlands
Private interest: www.mikondo.nl
Post #1144138
Posted Tuesday, July 19, 2011 7:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 20, 2014 9:49 PM
Points: 20, Visits: 164
Hi Dewes,

I'll try and explain this in layman's terms, as these are the terms I understand.

This issue is not uncommon I believe. Collation's describe the ordering of data within columns. Mainly collation's are used to separate different languages alphabet ordering, as these differ between languages.

Specifically for your issue, Some columns on the database you are running the script on are using collation Latin1_General_CI_AS, whereas other columns are using SQL_Latin1_General_CP1_CI_AS.

- SQL_Latin1_General_CP1_CI_AS (legacy SQL Specific collation, which doesn't support unicode, and and will be removed from SQL Server one day)
- Latin1_General_CI_AS (windows collation which supports unicode)

Run this script to identify the columns on your database and their different collations:

SELECT
C.Name,
C.Collation_Name
FROM SYS.COLUMNS C

JOIN SYS.OBJECTS O
ON O.Object_id = C.Object_id

WHERE C.OBJECT_ID > 100
--AND C.Collation_Name <> 'Latin1_General_CI_AS'
AND O.Type = 'U'

I believe that because these collations aren't consistent, the error is occurring when running my script. Other scripts may also have a similar issue with the inconsistent collations.

You can overcome this by casting collations at every join, but i wont be updating my script to account for this as it will be time consuming. I'd advise that your column collations be consistent.

Regards,

Cameron
Post #1144624
Posted Wednesday, July 20, 2011 1:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 14, 2014 1:44 AM
Points: 15, Visits: 464
Hi Cameron,
thanks for your explanation.


Dewes van Lohuizen,
DBA at CSC Netherlands
Private interest: www.mikondo.nl
Post #1144756
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse