Shortcut method for n columns compared to the same constant?

Question

I can not find the answer anyway. Pretty simple question with very little details.

Is there a better way to do something like this:

SELECT * FROM tablename tn WHERE tn.col1 = 1 AND tn.col2 = 1 AND tn.col3 = 1 AND tn.col4 = 1 ... AND tn.coln = 1 

Where do multiple columns need to be compared the same with a single constant?

Answer 1

I think this is the best way to do this. As far as I know, there is no other way to do this.

Answer 2

The only thing I can think of (and it would be useful only in certain circumstances) is to add a constant computed field to the table itself, which uses logic, as shown below. I would not recommend this approach for a highly transactional table and would really even consider it if it is a filter used in many places and quite often. There is one value that you can filter and index to optimize SELECT.

 ALTER TABLE [tablename] ADD SAMEVALUE AS (CASE WHEN col1 = col2 AND col1 = col3 AND col1 = col4 AND .. col1 = coln THEN col1 ELSE NULL) PERSISTED 

Answer 3

I assume that "better" here means less typing? If so, you can speed things up with Intellisense or a third-party tool that does the same, or if you have a large number of columns, then use your favorite scripting language (Perl, Python, PowerShell, whatever) to create WHERE for you. You can even use SQL itself:

 select 'tn.' + quotename(name) + ' = 1 and ' from sys.columns where object_id = object_id('MyTable') and name like 'col%' 

But remember that you spend much more time reading code than writing it, so the most important thing is that the meaning of the request should be as clear as possible, and your current request is already very clear.