I was too shy to express it so forcefully but yes I agree.

Sean Lange (3/3/2016)

---

As the OP stated this data is created by multiple steps in the process. Let's say for example that InsuranceCarrierID would be required for this to be valid...

"But for the sanity of folks who use database software, DON'T HAVE YOUR RDBMS VENDOR HARD WIRE MEANINGS INTO NULLS."

Speaking from bitter experience perhaps ?

rstone (2/29/2016)

---

I think nulls are used more often than required, but they have their place. If we start with a rule that forbids nulls in the database, then we are...

Indeed, I forgot that I should have also added the metadata on top of each side table for each nullable field. An extra layer of complication yet.

Yes, my fix did imply one specific meaning for a null - if you cannot name the judge for instance it is because it has not been done yet. Cannot...

Well you can always "fix"...

kenambrose (2/26/2016)

---

it's actually pretty easy to work with an absolute ban.

You just take any column where you would otherwise need nulls, and make a related table for it and ...

roger.plowman (2/26/2016)

---

j-1064772 (2/26/2016)

---

Otherwise how do you publish the information if no field can be left NULL ? Real life cannot be constrained by database design requirements. Life is full of...

An absolute ban on NULL can impose excessive constraints in real life. Events cannot all be reserved for after-the-fact data entry.

For instance, when drawing up a tentative schedule for court...

JediSQL (10/31/2014)

---

...

The one big exception is a CHECK CONSTRAINT. With a CHECK CONSTRAINT the assertion just has to be not provably FALSE. Say we have [font="Courier New"]......

The execution plans do not tell the whole story. I have seen some cases by Gila Monster where the execution was simplified but the load on the server was not...

davidawest00 (2/17/2016)

---

I have encountered this precise scenario a dozen times.

You can do the same thing with a correlated sub-query, and they usually scale better than any CTE because a CTE...

Re: Alternative method for those without LEAD() and LAG()

The original code WITH LEAD() and LAG() seems easier to read for me.

The alternative method would apply in a version of SSQL...

redundant comment - deleted.