Jeez - I'm going senile. I read the index name wrong. Here is the correct index def, that it is actually using.

CREATE NONCLUSTERED INDEX [ix_AkcDruhaEvidence] ON [dbo].[Podrobnosti]
(
[AkcesAutoID]...

All the indexes show non-clustered. I'm sure I had a clustered index on this originally, but I've done a good bit of expermenting with this table, and probably forgot this...

Oh yeah, I forgot about the filter. It's actually the same filter as is used in my original query, but I forgot to put it back in when testing this...

I think the optimiser doesn't use the index because there's no benefit in doing so. If you scale up the row count to something sensible and add a column...

The max list is easy enough to produce.  Sorry, I don't fully understand the 'OK' part well enough yet to add that to the query.  'OK' just meaning...

• This reply was modified 5 years, 11 months ago by pdanes.

SELECT a, MAX(CAST(b AS varchar(2)) + c) 
FROM (VALUES 
('A', 1, 'a'),
('A', 1, 'b'),
('A', 1, 'c'),
('A', 2, 'a'),
('B', 1, ''),
('B', 1, 'a'),
('B', 1,...

SELECT a, MAX(CAST(b AS varchar(2)) + c) 
FROM (VALUES 
('A', 1, 'a'),
('A', 1, 'b'),
('A', 1, 'c'),
('A', 2, 'a'),
('B', 1, ''),
('B', 1, 'a'),
('B', 1, 'b'),
('B',...

The max list is easy enough to produce.  Sorry, I don't fully understand the 'OK' part well enough yet to add that to the query.  'OK' just meaning that...

SELECT a, MAX(CAST(b AS varchar(2)) + c) 
FROM (VALUES 
('A', 1, 'a'),
('A', 1, 'b'),
('A', 1, 'c'),
('A', 2, 'a'),
('B', 1, ''),
('B', 1, 'a'),
('B', 1, 'b'),
('B', 2, ''),
('C',...

• This reply was modified 5 years, 11 months ago by pdanes.

Okay, I made up a few sample rows. If you need more, let me know. The code is what I'm using now, and you can see both what it produces,...

• This reply was modified 5 years, 11 months ago by pdanes.

Would you please provide directly usable data, i.e. CREATE TABLE and INSERT statement(s) for the sample data.  [A splat of data on the screen does us no good to...

• This reply was modified 5 years, 11 months ago by pdanes.

Seriously? An official, company-wide mandate to DB coders to use this? That...

• This reply was modified 5 years, 11 months ago by pdanes.

It's amazing what you sometimes find, but we probably shouldn't be too harsh on the author. I've certainly found my share of garbage in my own code on later reviews....

Maybe I'm missing something, but I have to wonder why this was ever done in dynamic. I use a fair amount of dynamic, but only after first trying without. Only...

I've thought a few times about applying for a job at Microsoft, not in the normal way, that I want to work for the company, but with the specific goal...

• This reply was modified 6 years, 4 months ago by pdanes.