Grouping by other values, possibly from another table may look like this:

/**

* Test Data

**/

DECLARE@ParentItem TABLE(ParentID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, ParentName NVARCHAR(50), ParentDate DATETIME);

DECLARE@ChildItem TABLE(ChildID INT IDENTITY(1,1) NOT...

Data Error! Please rewind and try again.

Can't remember the first game I played. It would have been on a Vic20 at a friends house because we couldn't afford computers.

My first introduction to anything resembling programming was...

peter-757102 (10/2/2009)

---

Jeff Moden (10/2/2009)

---

peter-757102 (10/2/2009)

---

Jeff Moden (10/2/2009)

---

That's why I shifted to All_Columns for future code. Rumor has it that syscolumns is going away and Master.sys.All_Columns always has at least...

dbishop. See the reply to my first post above regarding the times where a static tally table is not possible.

The article is not about creating a tally table but creating...

Indeed, a very well written, descriptive and, as I said, interesting article. It's already been bookmarked so I can look at the interaction of the various elements in the code...

I agree. Definitely an interesting article but I would have liked to see tests against an already created and indexed tally table and the reasons for going dynamic rather than...

Paul.

It's good to know this and have it spelled out in the clear once and for all.

Surely using a constant can't be a negative though. I mean, COUNT(0) has the...

hmm, good point. I changed your second query to use * rather than be identical to the first and the plans were still the same.

Perhaps older DBs did not optimise...

Specify the columns you need and schema bind where possible and appropriate. When requirements change, so does the code.

I can't think of a case where it is sensible to use...

Lynn and Scott.

The issue with FOR XML is that it escapes out the CHAR(10) portion of CRLF. It CRs after the 13 and then inserts the hex for the 10.

This...

of course the ampersand#x0D; didn't display there.

Scott

That CR vs CRLF thing with FOR XML is annoying isn't it.

I've just been replacing the hex code rather than inserting placeholders for carriage returns. Using one of your examples:

SELECT...

In ApexSQL Edit you can add custom scripts as table options so that you can right click on a table, select your custom script and it will run the query...

But that's where good structure and documentation come in.

Without a doubt, SELECT * is a bad thing in many ways.

Besides, if you have a function with SELECT * and you...