Easy way to determine a column in a recordset has same values

In an SQL recordset we would like to find a simple way to determine if a column has the same values for each row. Currently we can use AVG function to determine this for int and numeric fields, but do not know how to do for text (varchar and nvarchar). Grateful for ideas.
pld51Asked:
Who is Participating?
 
pcelbaConnect With a Mentor Commented:
The second solution returns 1 if whole column has NULL value whereas the first solution returns 0.
If there are some equal values and some nulls in the column then the first solution returns 1 and the second one returns 2.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
can you please give a sample recordset and the expected resultset
0
 
pcelbaCommented:
You can use COUNT(DISTINCT YourColumn) to determine if all values are equal in almost any data type column.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
pcelbaCommented:
If you need to count all NULL valuees as one occurence of distinct value you may use following conversion:

SELECT COUNT(DISTINCT YourColumn) + COUNT(DISTINCT CASE WHEN YourColumn IS NULL THEN 1 ELSE null END)
0
 
pld51Author Commented:
Thanks pceiba, this looks like the solution. I did not quite understand your 2nd comment about null. If the whole column has the same null value, does this apply or do you propose for another use?
0
 
pld51Author Commented:
Thanks, very helpful and fast!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.