In theory, it should be just tender enough to not fall apart. In practice, that's hard to do and the reason brisket is so hard to master. It's damn near impossible at a BBQ restaurant where the brisket has to be pre-cooked and kept warm (i.e. still cooking) for multiple hours until it is served. The stuff you see on TV is "perfect" but probably doesn't represent what they serve day in and day out.
On the other hand, some places don't cook their brisket enough and it comes out more like flank steak. Hell, Mac's Speed Shop in CLT actually puts their smoked brisket slices back on the grill for char marks before serving. They are cut thick and not even close to done/tender. Awful example of brisket IMO. Midwood Smokehouse in CLT does an excellent brisket though. You can choose lean (flat), fatty (point), or a mix and they somehow seem to serve it without it being overdone most of the time.