CREATE RULE

Question

CREATE RULE

Steve Jones - SSC Editor

SSC Guru

Points: 734449
More actions
January 10, 2018 at 9:39 pm

#409946

Comments posted to this topic are about the item CREATE RULE

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Stewart "Arturius" Campbell SSC Guru Points: 72498 More actions · Answer 1

Nice question ,thanks Steve.
just a note - anyone copy/pasting the query will encounter an error - "Unclosed quotation mark after the character string 'dbo.Employees.Salary"

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

HappyGeek SSCoach Points: 18726 More actions · Answer 2

Good question thanks Steve, also spotted the typo.

...

morlindk SSCrazy Points: 2294 More actions · Answer 3

Still, it's deprecated?! https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-bindrule-transact-sql
You are encouraged to use Constraints instead!
/morten lintrup

Kaye Cahs SSCarpal Tunnel Points: 4135 More actions · Answer 4

Just as I thought I was learning something new, I discovered that the "new" thing is deprecated.
I did all that learning for nothing. (;

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 5

Yep, even my old books online from SQL Server 2008 listed it as being deprecated. I find it funny how long some deprecated things actually hang on. :satisfied:

Eric M Russell SSC Guru Points: 125522 More actions · Answer 6

Trick question. Don't do it; use a constraint instead.
I've worked in a dozen IT shops over the years, and I don't think I've encountered usage of RULE based constraints in the wild. Time forgot them long ago.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Thomas Franz Hall of Fame Points: 3874 More actions · Answer 7

Why is usefull stuff as this deprecated? Everyone is talking about normalisation, but I still have to write the same code in a CHECK CONSTRAINT again and again and again, instead of just applying a single rule to all my fields (I believe this is one reason, why they are often not used - be honest, how many fields in your database have a check to be >= 0, because it must not contain negative numbers)

God is real, unless declared integer.

Stewart "Arturius" Campbell SSC Guru Points: 72498 More actions · Answer 8

t.franz - Friday, January 12, 2018 1:22 AM
Why is usefull stuff as this deprecated? Everyone is talking about normalisation, but I still have to write the same code in a CHECK CONSTRAINT again and again and again, instead of just applying a single rule to all my fields (I believe this is one reason, why they are often not used - be honest, how many fields in your database have a check to be >= 0, because it must not contain negative numbers)

I believe it is because a UDF can be written and applied in the check constraints

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

t.franz - Friday, January 12, 2018 1:22 AM
Why is usefull stuff as this deprecated?

Heh... you sound like a kindred spirit.

My answer would be, for the same reason that other useful stuff is never implemented. For example, the "Tally" table has been an open CONNECT item for more than a decade now.

What's that reason?

One can only guess but I suspect it has something to do with marketing and whether or not MS actually wants to spend the money supporting something. From a business standpoint, that's a pretty smart thing for MS to do. From a customer standpoint, it's a terrible practice. The company and the customer simply don't have the same goals and, for the customers that do get ticked off about stuff like this, what are they going to do? Trash their entire data world to shift to some other product? Few can afford to actually do that even over time. It's the old "Big Blue" syndrome.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 10

No idea, but I suspect that someone liked constraints better and decided to not maintain this feature. MS announced somewhere that they aren't removing code anymore. With modern tools and the cloud, I think things like Rules will live on forever in limbo. No moving forward, no one taking the time to remove this.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 11

Steve Jones - SSC Editor - Friday, January 12, 2018 8:43 AM
No idea, but I suspect that someone liked constraints better and decided to not maintain this feature. MS announced somewhere that they aren't removing code anymore. With modern tools and the cloud, I think things like Rules will live on forever in limbo. No moving forward, no one taking the time to remove this.

By "Limbo", you mean "not supported but still functional"? If so, I wonder if they'll also drop it from BOL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eric M Russell SSC Guru Points: 125522 More actions · Answer 12

Steve Jones - SSC Editor - Friday, January 12, 2018 8:43 AM
No idea, but I suspect that someone liked constraints better and decided to not maintain this feature. MS announced somewhere that they aren't removing code anymore. With modern tools and the cloud, I think things like Rules will live on forever in limbo. No moving forward, no one taking the time to remove this.

Perhaps RULES and other old or obsolete syntax won't be dropped from the engine, but to use it you may need to drop the database down to a lower compatibility level. It seems that the concept of database compatibility level has been carried over to Azure.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 13

Jeff Moden - Friday, January 12, 2018 9:03 AM
By "Limbo", you mean "not supported but still functional"? If so, I wonder if they'll also drop it from BOL.

My understanding is no. They'll live forever under the deprecated but not removed status.

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 14

Eric M Russell - Friday, January 12, 2018 10:19 AM
Perhaps RULES and other old or obsolete syntax won't be dropped from the engine, but to use it you may need to drop the database down to a lower compatibility level. It seems that the concept of database compatibility level has been carried over to Azure.

Why do you think this? Most items that are still enabled continue to work in current versions. I would suspect this isn't the case as adding code to check this wouldn't be worth the effort.