Non-clustered index on Sals Shipment Line table

kriki

David Singleton wrote:

And to be honest I am pretty sick and tired of threads like this, where it seams that trying to do the right thing only gets you in trouble, better when some one asks "is it possible to increase the length of the "No." field in the Item table, just to answer YES and close the thread.

What is the best:
1)Answering NO and explaining why and knowing it will get you into trouble. But also that others will support you!
or
2)Answering YES and knowing you are lying.

I prefer 1)

Or there is a third option: close your eyes, take a deep breath and go to the next topic.

stryk

David Singleton wrote:

whilst I totally agree with this, I wonder if you do? Because this statement completely contradicts the argument that you are supporting.

You think so? Hmmm, I don't ... Maybe I couldn't express myself right ...

IMHO "skill, knowledge & experience" is something everyone could acquire. I don't say it is easy to acquire, but it is possible - it's a matter of "work & effort".
So even though it IS indeed quite tiresome (at least sometimes) I think is necessary to discuss technical options, looking at the matter from different angles, exchanging arguments, showing up the PROs & CONs. So the dear reader of such a thread could learn that 1) YES, it is possible but 2) NO there might be risks. A multilateral discussion should open the mind to reflect on the readers own "skill, knowledge & experience" so he could make the decision about how he wants to proceed.

Those who are stupid enough to be satisfied with the brief YES, ignoring the BUT will regret it soon enough, but that's not us to blame. This forum is platform to exchange thoughts, not an authoritative solution database, isn't it?

Uhm, I guess this thread is now way off topic and I'd prefer to close this here. These "philosophical matters" should be discussed around a table with a beer at hand

DenSter

stryk wrote:

These "philosophical matters" should be discussed around a table with a beer at hand

My thoughts exactly

rhpnt

DenSter wrote:

It doesn't happen every time, when people do start modifying the table design directly on SQL Server, and there are problems, those problems are generally very significant. You wouldn't believe some of the problems that I've seen caused by changing table design directly, and it always starts with "can I add an index?". Then another index is added, and then an existing index is changed, and then a data type is "fixed", or fields get added.

I can only repeat myself - no one is (was) talking about "design" changing in SQL directly - you're mixing apples with oranges. The thread was started for a simple reason - ADDING ONE simple, independent, new index - nothing more.

DenSter wrote:

In my opinion, when you need an additional index, the best way to go about doing that is to add it in the NAV table designer, and we'll just have to agree to disagree there.The inconvenience of having users restart their NAV session is minor compared to the problems that could be caused by doing something wrong.

There's nothing to agree on that. I'd like to see how your working day evolves when 600+ users get the message they should restart NAV just because the NAV guy thinks it's more convenient this way...

DenSter wrote:

It's much easier to manage, because this way the table design is inside the NAV object at all times, and you don't have the additional layer of objects to manage.

The table design is always in NAV but the indexes are in SQL Server. I'm beginning to get the impression that long time NAV experts just don't want to accept the fact that the times of the "one man band" and "one world" in NAV are over.

rhpnt

matttrax wrote:

I've seen trigger code added that deleted an order during posting, and then when the NAV code came along that did the same thing it errored out. Couldn't post for a couple of days, and because it was done directly in SQL, but happening in NAV, it was damn near impossible to figure out for the NAV developer (i.e. me).

Well once I've seen someone delete a NAV database but does that mean no one should open the SQL Management Studio?

matttrax wrote:

Anyway, like Denster said, the point is to be careful if you're doing things outside of NAV.

No one wrote "fire at will".

rhpnt

stryk wrote:

Those who are stupid enough to be satisfied with the brief YES, ignoring the BUT will regret it soon enough, but that's not us to blame. This forum is platform to exchange thoughts, not an authoritative solution database, isn't it?

My point exactly!

stryk wrote:

Uhm, I guess this thread is now way off topic and I'd prefer to close this here. These "philosophical matters" should be discussed around a table with a beer at hand

No, no, there's nothing philosophical abut this thread. I think we just scratched the surface of a real important issue here. I'm getting the impression that some people would rather sweep things under the carpet instead of facing the facts. Generalizing and diverting topics is a well known method of people who are afraid to lose control over something they consider "theirs only" and the only argument for doing so is "because we are doing it so long". Maybe it's because of that attitude that NAV is lacking behind other ERP's in technology and content!? Nevertheless, because of this, there's a lot of potential in NAV and the further evolvement of the product must be pushed in the right direction. MS made the right step in making NAV a SQL Server/three tier ERP. Every NAV expert must be aware that this step also brings tectonic changes in how NAV is to be developed and maintained in the future.

rhpnt

kriki wrote:

I can add something about a not so "any other SQL Server DBA": a year or 2 ago, I talked to an Italian SQL Server MVP(!), specialised in performance tuning, and he told me that he has been asked a few times to tune a DB that turned out to be a NAV DB. He said to the customer that he doesn't touch that kind of DB's because he knows he can do more damage than heal.

Well then, what kind of a database WOULD this MVP touch? The kind that only he created? Every DBA is trained to optimize any database - well, the only kind of db's I know who need optimization are in some way connected to an ERP system. I had a similar experience myself when a MVP was called to improve a NAV database. Same scenario - the only things he offered to do were in the knowledge range of an IT graduate. When the topic came to improving the NAV db he refused to do anything with vague to no argument. I reported the guy to MS, maybe you should do that too.

kriki

rhpnt wrote:

kriki wrote:

I can add something about a not so "any other SQL Server DBA": a year or 2 ago, I talked to an Italian SQL Server MVP(!), specialised in performance tuning, and he told me that he has been asked a few times to tune a DB that turned out to be a NAV DB. He said to the customer that he doesn't touch that kind of DB's because he knows he can do more damage than heal.

Well then, what kind of a database WOULD this MVP touch? The kind that only he created? Every DBA is trained to optimize any database - well, the only kind of db's I know who need optimization are in some way connected to an ERP system. I had a similar experience myself when a MVP was called to improve a NAV database. Same scenario - the only things he offered to do were in the knowledge range of an IT graduate. When the topic came to improving the NAV db he refused to do anything with vague to no argument. I reported the guy to MS, maybe you should do that too.

Being also a NAV performance specialist, and knowing that the SQL specialists can really mess up things with a NAV DB on SQL if they do it the pure SQL way. I think he proved worthy of his SQL Server MVP status because he knew it was possible to do more damage than good on this DB!

matttrax

rhpnt wrote:

stryk wrote:

Those who are stupid enough to be satisfied with the brief YES, ignoring the BUT will regret it soon enough, but that's not us to blame. This forum is platform to exchange thoughts, not an authoritative solution database, isn't it?

My point exactly!

I think you're contradicting yourself here a bit. You're agreeing that the forums are a place to exchange thoughts, yet you're acting as though your solution is the only one that counts for anything.

It's been a few days since I read through the thread, but I don't think anyone has said that NAV is the only place to do this. I don't remember anyone giving a solution that was just flat out wrong. NAV has been on SQL for what now, at least a decade? I would absolutely hope that there are people out there who know what can / should be done in SQL by now vs. what can / should be done in NAV. You may be one of those people, but that doesn't mean that everyone else is. Everyone who contributes here responds based on what they have experienced with the product, and that's different for everyone.

rhpnt wrote:

I'm getting the impression that some people would rather sweep things under the carpet instead of facing the facts. Generalizing and diverting topics is a well known method of people who are afraid to lose control over something they consider "theirs only" and the only argument for doing so is "because we are doing it so long".

I think that's the majority of developers, no matter what the language or product is. Most developers think they know better than the next person, a fact that I think has shown itself to be true here. The statement above could be just as easily applied to a SQL Admin saying that SQL is the only place to do it even though NAV is also capable. So much of what is the "best solution" depends on the user, the company, the situation. In questions like this there is no one absolutely 100% you should do it this way every time type of answer.

rhpnt wrote:

Every NAV expert must be aware that this step also brings tectonic changes in how NAV is to be developed and maintained in the future.

Of course this is true, but you can be aware that there are other ways to do things and not know how to do them in every way possible, or what is best practice given the wide array of conditions and situations you'll find out there. They just need to know that things are possible, like using .NET Add-ins, or SQL Reporting. NAV, especially large NAV, systems, haven't been a "theirs only" thing for a long time, which is precisely why everyone doesn't know how to do everything anymore. Development and database design / administration, while related obviously, are two very separate things. That's the whole point of building a team with members that complement each other, so that multiple ideas and view points can be shared and people can learn from each other. So that you can determine the best way to do something from the people who know that part of the system inside and out. The people in this thread are an example of such a team. Some are expert NAV Developers, some are expert SQL Admins / Developers, and some may be other things or combinations, but we are all working towards a common goal of providing the best solution given what we know.

rhpnt

kriki wrote:

Being also a NAV performance specialist, and knowing that the SQL specialists can really mess up things with a NAV DB on SQL if they do it the pure SQL way. I think he proved worthy of his SQL Server MVP status because he knew it was possible to do more damage than good on this DB!

It's always possible to do more damage than good - if you're a beginner. An MVP status proves a person has the expertise and knowledge to take on any (in this case) SQL Server job. In my case this expert had all the NAV resources at hand but refused to do anything in that way. I would understand if he would say: "I don't know how NAV works so I need more time on this...", but he didn't even bother asking. We called this guy only because of formal reasons (contract obligations), eventually we ended up doing everything by ourselves. Is that MVP worthy?

DenSter

You're right rhpnt, and everyone else is wrong

kriki

rhpnt wrote:

An MVP status proves a person has the expertise and knowledge to take on any (in this case) SQL Server job.

Definitely not true: SQL server has so many things that there is NO SQL Server MVP that knows everything. Most of them specialize in 1 or only a few things.

rhpnt wrote:

In my case this expert had all the NAV resources at hand but refused to do anything in that way. I would understand if he would say: "I don't know how NAV works so I need more time on this...", but he didn't even bother asking.

Would you pay for that extra time? And we are not talking about a few hours or days, but weeks and months to know enough of NAV to be able to do something.

rhpnt wrote:

We called this guy only because of formal reasons (contract obligations), eventually we ended up doing everything by ourselves. Is that MVP worthy?

Like I said, an MVP doesn't know everything of SQL server (and the same goes for Dynamics NAV MVP's and all other MVP's).