T-SQL Tuesday #025 – CHECK Constraint Tricks
- by Most Valuable Yak (Rob Volk)
Allen White (blog | twitter), marathoner, SQL Server MVP and presenter, and all-around awesome author is hosting this month's T-SQL Tuesday on sharing SQL Server Tips and Tricks. And for those of you who have attended my Revenge: The SQL presentation, you know that I have 1 or 2 of them. You'll also know that I don't recommend using anything I talk about in a production system, and will continue that advice here…although you might be sorely tempted. Suffice it to say I'm not using these examples myself, but I think they're worth sharing anyway.
Some of you have seen or read about SQL Server constraints and have applied them to your table designs…unless you're a vendor ;)…and may even use CHECK constraints to limit numeric values, or length of strings, allowable characters and such. CHECK constraints can, however, do more than that, and can even provide enhanced security and other restrictions.
One tip or trick that I didn't cover very well in the presentation is using constraints to do unusual things; specifically, limiting or preventing inserts into tables. The idea was to use a CHECK constraint in a way that didn't depend on the actual data:
-- create a table that cannot accept data
CREATE TABLE dbo.JustTryIt(a BIT NOT NULL PRIMARY KEY,
CONSTRAINT chk_no_insert CHECK (GETDATE()=GETDATE()+1))
INSERT dbo.JustTryIt VALUES(1)
I'll let you run that yourself, but I'm sure you'll see that this is a pretty stupid table to have, since the CHECK condition will always be false, and therefore will prevent any data from ever being inserted. I can't remember why I used this example but it was for some vague and esoteric purpose that applies to about, maybe, zero people. I come up with a lot of examples like that.
However, if you realize that these CHECKs are not limited to column references, and if you explore the SQL Server function list, you could come up with a few that might be useful. I'll let the names describe what they do instead of explaining them all:
CREATE TABLE NoSA(a int not null,
CONSTRAINT CHK_No_sa CHECK (SUSER_SNAME()<>'sa'))
CREATE TABLE NoSysAdmin(a int not null,
CONSTRAINT CHK_No_sysadmin CHECK (IS_SRVROLEMEMBER('sysadmin')=0))
CREATE TABLE NoAdHoc(a int not null,
CONSTRAINT CHK_No_AdHoc CHECK (OBJECT_NAME(@@PROCID) IS NOT NULL))
CREATE TABLE NoAdHoc2(a int not null,
CONSTRAINT CHK_No_AdHoc2 CHECK (@@NESTLEVEL>0))
CREATE TABLE NoCursors(a int not null,
CONSTRAINT CHK_No_Cursors CHECK (@@CURSOR_ROWS=0))
CREATE TABLE ANSI_PADDING_ON(a int not null,
CONSTRAINT CHK_ANSI_PADDING_ON CHECK (@@OPTIONS & 16=16))
CREATE TABLE TimeOfDay(a int not null,
CONSTRAINT CHK_TimeOfDay CHECK (DATEPART(hour,GETDATE()) BETWEEN 0 AND 1))
GO
-- log in as sa or a sysadmin server role member, and try this:
INSERT NoSA VALUES(1)
INSERT NoSysAdmin VALUES(1)
-- note the difference when using sa vs. non-sa
-- then try it again with a non-sysadmin login
-- see if this works:
INSERT NoAdHoc VALUES(1)
INSERT NoAdHoc2 VALUES(1)
GO
-- then try this:
CREATE PROCEDURE NotAdHoc @val1 int, @val2 int AS
SET NOCOUNT ON;
INSERT NoAdHoc VALUES(@val1)
INSERT NoAdHoc2 VALUES(@val2)
GO
EXEC NotAdHoc 2,2
-- which values got inserted?
SELECT * FROM NoAdHoc
SELECT * FROM NoAdHoc2
-- and this one just makes me happy :)
INSERT NoCursors VALUES(1)
DECLARE curs CURSOR FOR SELECT 1
OPEN curs
INSERT NoCursors VALUES(2)
CLOSE curs
DEALLOCATE curs
INSERT NoCursors VALUES(3)
SELECT * FROM NoCursors
I'll leave the ANSI_PADDING_ON and TimeOfDay tables for you to test on your own, I think you get the idea. (Also take a look at the NoCursors example, notice anything interesting?)
The real eye-opener, for me anyway, is the ability to limit bad coding practices like cursors, ad-hoc SQL, and sa use/abuse by using declarative SQL objects. I'm sure you can see how and why this would come up when discussing Revenge: The SQL.;) And the best part IMHO is that these work on pretty much any version of SQL Server, without needing Policy Based Management, DDL/login triggers, or similar tools to enforce best practices.
All seriousness aside, I highly recommend that you spend some time letting your mind go wild with the possibilities and see how far you can take things. There are no rules!
(Hmmmm, what can I do with rules?)
#TSQL2sDay
