Nice question Sean. Thanks for reminding the basics!

Nice question and a good discussion indeed!

Happy Holidays to all 🙂

Cheers

demonfox (12/21/2012)

---

Well Said Lokesh !!

Here a reference and statement for learning

1. COUNT(*) returns the number of items in a group. This includes NULL values and duplicates.

2. COUNT(ALL expression) evaluates...

Yggaz (12/21/2012)

---

Lokesh Vij (12/21/2012)

I do not agree with this. COUNT(*) only considers counting the number of rows. It does not even bother to take a look at the columns and...

Yggaz (12/21/2012)

---

There is just one variant of one aggregate function that DOES take nulls into account: COUNT(*).

However, it is uncompatible with DISTINCT clause.

SELECT COUNT(col1) as cnt_col1

...

I think your script is fine!

Cadavre (12/20/2012)

---

So I guess my advice is to always post sample data and expected results in the future. But for this problem, you need to figure out whether any of...

Gald that you found out a way to improve the query using an Index 🙂

Good Question!

And very rightly pointed out in the explanation that aggregate functions does not take NULL values into account. This is the reason you get the following warning when using...

SQLRNNR (12/20/2012)

---

Stewart "Arturius" Campbell (12/20/2012)

---

IMHO, I think the correct answer here will be "It depends".

It depends on whether filtered indexes are utilised or not, etc.

However, it could be that...

Here you go..

-- Creating table

CREATE TABLE year_week_value

(

year INT,

week INT,

value INT

...

Yggaz (12/19/2012)

---

They are definitely not....

I am sorry to say, looking at the correct answer, the question seems to be confusing! The question should have mentioned "Unique Index" instead of "Unique Constraint". Ideally, both Unique...

Not sure why you said this as "inconsistency"...it's just the property of TRUNCATE command!