Jim Russell (4/18/2008)

---

"...but what happens when you do need to run a query that's going to take all night to compile its data?"

Then you get your favorite high-priced DBA (if...

Mike:

If I'm understanding you correctly, this should duplicate your many-to-many table (functionally):

create table HierarchyComplex (

ID int not null,

ParentID int not null,

constraint PK_HC primary key (id, parentid))

go

insert into dbo.hierarchycomplex (id, parentid)

select...

In this particular case, the dynamic version will use indexes better than the other version, because of all the IsNull() operators. So, yes, the static version has some slight...

And, kind of off-topic, just to show how the nested sets hierarchy performs compared to all three of those:

declare @Start int, @End int

select @start = setstart, @end = setend

from dbo.HierarchiesNodes

where...

I agree with most of what Jeff wrote. The one point I'd like to clarify is that I've tested recursive CTEs in hierarchy (adjacency) resolution, and they are faster...

You should change "if @ItemName != null" to "if @ItemName is not null". Unless you have ANSI NULLs turned off (which is usually a bad idea).

The Options window should have choices for:

Environment

Source Control

Text Editor

Query Execution

Query Results

Scripting

Designers

Does it not have all of those? If so, which version of SQL Server are you using? (Or...

In SQL 2005, there's a view called "sys.sql_modules", which has the definition (create script) for views, procs, etc. Books Online has the specifics.

Join that to sys.views to get the...

In Management Studio, under Tools, Options, Scripting Options, there is an option to Script Permissions. It defaults to False. Change that, and you'll have what you need.

They're all "number of transactions". If you look up the measures, it'll tell you precisely what it measures, and you can compare that to what you're trying to measure.

I don't think converting them to numbers would help. It might speed up the select, but then you're going to have to do two-way translation (to and from numbers),...

I have to ask, why not have a separate Days table? At 8 rows, it would be nearly instantaneous to query. Update it when you do your weekly...

(Pig Pile on Jim Russell!!!!)

Gail's right on this one.

Either no ad hoc queries at all, or set them up on a separate server. I prefer the second option.

Heck, I...

I guess the question becomes, how do you decide which row to keep the data from?

If the Load_AEQP_Staging table has multiple rows for the same ASSET_NBR, how do you decide...

Good article, by the way.