<div>
The Tree table is defined collation Latin1_General_CI_AS, the CI meaning Case Insensitive, so as you are experiencing lower case will be treated the same as upper case for comparisons. 
 
You can <a href="https://msdn.microsoft.com/en-us/library/ms184391.aspx" rel="ugc">specify the collation in a SELECT clause</a>, without changing the collation of the entire database, to identify differences. 
 
&lt;total air code, not abundantly guaranteed&gt; 

<pre><code class="code-1 nolinks" id="code-20-41723459-1">SELECT ParentId, COUNT(DISTINCT Name COLLATE Latin1_General_CS_AS) as count_name
FROM TREE
GROUP BY ParentId
ORDER BY ParentId
</code></pre>
</div>

<div>
You could use the following select statement: 

<pre><code class="code-1 nolinks" id="code-20-41723498-1">SELECT COUNT(*) as cnt, ParentId, UPPER(Name)
FROM TREE
GROUP BY ParentId, UPPER(Name)
HAVING cnt &gt; 1
</code></pre>
 
Hopefully the syntax is ok, I didn't use MSSQL for a while but the idea should be clear.
</div>

<div>
Thanks. &nbsp;I think that gives me a start but I'm having trouble interpreting the results: 
 
<a href="https://filedb.experts-exchange.com/incoming/2016/07_w30/1107821/results.PNG" target="_blank" title="results (6 KB)"><img alt="results" class="file-block lazyload" style="max-width: 183px;" data-src="https://filedb.experts-exchange.com/incoming/2016/07_w30/1107821/results.PNG" /></a> 
When I investigate the first record, one object is returned but this does not indicate a duplicate record. 
<a href="https://filedb.experts-exchange.com/incoming/2016/07_w30/1107822/results2.PNG" target="_blank" title="results2 (3 KB)"><img alt="results2" class="file-block lazyload" style="max-width: 283px;" data-src="https://filedb.experts-exchange.com/incoming/2016/07_w30/1107822/results2.PNG" /></a> 
Not sure if my explanation is confusing. &nbsp;Is it possible to list duplicate Names, regardless of case, by distinct ParentID? &nbsp;I wouldn't expect anything to return if only ONE row is returned for that ParentID, since we're looking for duplicate Names. 
 
Hope I'm making sense..... &nbsp;Thanks.
</div>

results.PNG

results2.PNG

<div>
&gt;When I investigate the first record, one object is returned but this does not indicate a duplicate record. 
You can filter out the count=1's with a HAVING clause, which is the same as WHERE but it filters based on aggregate numbers. 
 

<pre><code class="code-1 nolinks" id="code-20-41723525-1">SELECT ParentId, COUNT(DISTINCT Name COLLATE Latin1_General_CS_AS) as count_name
FROM TREE
GROUP BY ParentId
HAVING COUNT(DISTINCT Name COLLATE Latin1_General_CS_AS) &gt; 1
ORDER BY ParentId
</code></pre>
 
&gt;Is it possible to list duplicate Names, regardless of case, by distinct ParentID? 
I would suspect that the code I provided does that. &nbsp;If it doesn't please spell out where it doesn't, and we'll work from there.
</div>

<div>
@PetrJ 
Thanks. &nbsp;This only shows uppercase results. &nbsp;I need to identify rows with a duplicate Name (regardless of case) that share the same ParentID. 
 
So if the ParentID=1212 and there are three rows, each with the following Name: 
SpreadSheet1.xls 
spreadsheet1.xls 
EmployeeSchedule.xls 
 
I would expect TWO results - SpreadSheet1.xls and spreadsheet.xls
</div>

<div>
Key is the value of count_name. It indicates number of occurences - you need to be concerned when it's more than 1. 
 
You can filter it using the following statement: 

<pre><code class="code-1 nolinks" id="code-20-41723529-1">SELECT *
FROM (
SELECT ParentId, COUNT(DISTINCT Name COLLATE Latin1_General_CS_AS) as count_name
FROM TREE
GROUP BY ParentId
ORDER BY ParentId)
WHERE count_name &gt; 1
</code></pre>
I hope the syntax is OK for MSSQL.
</div>

<div>
Having issues with the syntax in MSSQL. &nbsp;Trying to convert.
</div>

<div>
Msg 1033, Level 15, State 1, Line 6 
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
</div>

<div>
<div class="content wysiwyg-content">
Hi, 
Our application is tied to a database on SQL server. &nbsp;The application upgrade is failing because duplicate objects are detected in a table which share the same ParentID - these objects contain the same Name but with different case sensitivity. &nbsp;It appears collation was changed on the Name column -- collation settings below. 
 
Example: 
 
select * from TREE 
where ParentID=4178385 
 
Results: 
<a href="https://filedb.experts-exchange.com/incoming/2016/07_w30/1107809/ss1.jpg" target="_blank" title="results (32 KB)"><img alt="results" class="file-block lazyload" style="max-width: 445px;" data-src="https://filedb.experts-exchange.com/incoming/2016/07_w30/1107809/ss1.jpg" /></a> 
I'm looking for a way to locate all objects in the TREE table that contains: 
1. The same ParentID 
2. The same Name, regardless of case sensitivity 
 
Is there a SQL query that can help locate the requirements above? &nbsp;I'm doing my best with Google but haven't found what I'm looking for. 
 
Collation: 
SQL 2014 instance: SQL_Latin1_General_CI_AS. 
ACME database: Latin1_General_CI_AS. 
TREE table: Latin1_General_CI_AS. 
NAME column: &nbsp;SQL_Latin1_General_CP1_CS_AS 
 
Thank you.
</div>
</div>

ss1.jpg

Hi,
Our application is tied to a database on SQL server.  The application upgrade is failing because duplicate objects are detected in a table which share the same ParentID - these objects contain the same Name but with different case sensitivity.  It appears collation was changed on the Name column -- collation settings below.

Example:

select * from TREE
where ParentID=4178385

Results:


I'm looking for a way to locate all objects in the TREE table that contains:
1. The same ParentID
2. The same Name, regardless of case sensitivity

Is there a SQL query that can help locate the requirements above?  I'm doing my best with Google but haven't found what I'm looking for.

Collation:
SQL 2014 instance: SQL_Latin1_General_CI_AS.
ACME database:  Latin1_General_CI_AS.
TREE table:  Latin1_General_CI_AS.
NAME column:  SQL_Latin1_General_CP1_CS_AS

Thank you.

Find duplicate/case sensitive records in SQL database

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.

Databases

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.