TRUNCATE TABLE master..sysdatabases

T-SQL Tuesday #025 – CHECK Constraint Tricks

2011-12-13T14:24:28Z

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

Queerie Memoree Spils

2011-10-18T17:24:04Z

Did you know that if you run a query in SQL Server, and it processes it as a hash match, and there's not enough memory to fully process it, the memory will spill to disk? You can find out all about it here.

Note: I know this makes even less sense than my usual blog posts do, so for more information, check out Jen's Awesomesauce blog entry about it. (Please don't sue me Jen!)

#sqlsue

SQL Saturday #89 in Atlanta!

2011-09-13T10:39:43Z

(Yeah yeah, technically it's in Alpharetta, but it's close enough.)

Saturday…Saturday….Saturday…. September 17th. TWO THOUSAND ELEVEN!

OK, it's not a tractor pull, but it's even better: FREE SQL SERVER TRAINING! They have a bunch of great speakers lined up, and for some reason, me. (Protip: be good friends with the program committee, have sufficient bribe funds, and if all else fails, lots of alcohol, drugs and a camera. Ba-ZING! You too can speak at SQL Saturday!)

I will be presenting Revenge: The SQL! in a new and improved SQL Saturday themed presentation. Actually, it's the same ol' presentation, I just updated the slide theme to match the new SQL Saturday website design. (Yeah guys, thanks for changing that a month ago. So much for coasting on the old format.)

Of course, you have your choice of three other SQL Saturdays in other cities that day, but come on, you really want to go to this one.

#sqlsat89 #sqlsaturday #sqlkilt #sqlpass

24 Hours of PASS: Whine, Whine, Whine

2011-09-07T12:12:12Z

24 Hours of Pass (or 24HOP) is a great program offered by PASS to provide free, online training for anyone who wants to learn more about SQL Server. They routinely have the best SQL Server presenters available for these sessions, and attract hundreds, perhaps even a thousand attendees from around the world. This is definitely one of the best things they've started doing in the past few years, and every session I've attended has been excellent.

So why am I so grumpy about it?

I'm not really, pretty much everything here is a minor annoyance that I can deal with. However since they're so minor they seem to be things that can be easily corrected and would make the process much better.

First off, this is my biggest gripe, the registration page:

https://www323.livemeeting.com/lrs/8000181573/Registration.aspx?pageName=lj6378f4fhf5hpdm

What grinds my gears about this? I have to scroll alllllllllllllllllllllllllllll the way to bottom to actually register for the sessions. This wouldn't be so bad except all the details of the session, including the presenter, is in a separate list at the top. Both lists contain info the other does not, and scrolling between them to determine "Should I make time to listen to this? Who is speaking at this time anyway?" is really unnecessary.

My preference would be to keep the top list and add the checkboxes and schedule info in separate columns. This is a full-width design, so there's plenty of space for this data, which is pretty small anyway. The other huge benefit is halving the size of the page, which improves performance and lowers bandwidth usage considerably. And if you know HTML/ASP.Net, and you view the page source, you can find PLENTY of other things that can be reduced even further. (not just viewstate)

One nice thing that PASS does is send iCal reminders to your email address so you can accept them to your calendar. Again, they leave off the presenter in the appointment details, while still duplicating the meeting title in the body. Sometimes I make decisions based on speaker rather than content (Natalie Portman is reading the Yellow Pages??? I'M THERE!) and having the speaker in the iCal is helpful.

Next minor annoyances are the necessity for providing a company name, and the survey questions. I know PASS needs to market themselves effectively, and they need information to do that, and since this is a free event it's really not worth complaining about, but why ask the survey question twice? (once at registration, once again when joining the LiveMeeting) Same thing for the company name. All of this should be tied to email address, so that's all I should need to enter when joining the LiveMeeting.

The last one is also minor, but it irks me in this day and age of multiple browsers and the decline of Internet Explorer as a dominant platform. The registration page was originally created in Visual Studio 2003, and has a lot of IE-specific crud representative of the browser situation of 2003. (IE5 references? really? and is the aforementioned viewstate big enough?) This causes some grief with other browsers like Firefox, Chrome, and sometimes IE8 or 9. And don't get me started on using the page on a Mac or in Safari.

My main point is that PASS is an international organization, welcoming everyone from all levels of SQL Server proficiency, and in that spirit I think it would help to accommodate a wider range of browser software, especially since the registration page is extremely simple. I recognize that this page is not hosted on the PASS website and may be maintained by some division of Microsoft, but to me that's even worse if MS can't update their own pages. They've deprecated IE6, so they don't need to maintain support on their own websites anymore.

OK, I'll shut up now.

#sqlpass #24HOP

How to do an ER Diagram

2011-09-01T14:21:43Z

Acad-anemic

2011-08-30T16:15:53Z

T-SQL Tuesday #21 - Crap!

2011-08-10T18:09:47Z

Adam Machanic's (blog | twitter) ever popular T-SQL Tuesday series is being held on Wednesday this time, and the topic is…

~~SHIT~~ CRAP.

No, not fecal material. But crap code. Crap SQL. Crap ideas that you thought were good at the time, or were forced to do due (doo-doo?) to lack of time.

The challenge for me is to look back on my SQL Server career and find something that WASN'T crap. Well, there's a lot that wasn't, but for some reason I don't remember those that well. So the additional challenge is to pick one particular turd that I really wish I hadn't squeezed out. Let's see if this outline fits the bill:

An ETL process on text files;
That had to interface between SQL Server and an AS/400 system;
That didn't use SSIS (should have) or BizTalk (ummm, no) but command-line scripting, using Unix utilities(!) via:
xp_cmdshell;
That had to email reports and financial data, some of it sensitive

Yep, the ~~stench~~ smell is coming back to me now, as if it was yesterday…

As to why SSIS and BizTalk were not options, basically I didn't know either of them well enough to get the job done (and I still don't). I also had a strict deadline of 3 days, in addition to all the other responsibilities I had, so no time to learn them. And seeing how screwed up the rest of the process was:

Payment files from multiple vendors in multiple formats;
Sent via FTP, PGP encrypted email, or some other wizardry;
Manually opened/downloaded and saved to a particular set of folders (couldn't change this);
Once processed, had to be placed BACK in the same folders with the original archived;
x2 divisions that had to run separately;
Plus an additional vendor file in another format on a completely different schedule;
So that they could be MANUALLY uploaded into the AS/400 system (couldn't change this either, even if it was technically possible)

I didn't feel so bad about the solution I came up with, which was naturally:

Copy the payment files to the local SQL Server drives, using xp_cmdshell
Run batch files (via xp_cmdshell) to parse the different formats using sed, a Unix utility (this was before Powershell)
Use other Unix utilities (join, split, grep, wc) to process parsed files and generate metadata (size, date, checksum, line count)
Run sqlcmd to execute a stored procedure that passed the parsed file names so it would bulk load the data to do a comparison
bcp the compared data out to ANOTHER text file so that I could grep that data out of the original file
Run another stored procedure to import the matched data into SQL Server so it could process the payments, including file metadata
Process payment batches and log which division and vendor they belong to
Email the payment details to the finance group (since it was too hard for them to run a web report with the same data…which they ran anyway to compare the emailed file against…which always matched, surprisingly)
Email another report showing unmatched payments so they could manually void them…about 3 months afterward
All in "Excel" format, using xp_sendmail (SQL 2000 system)
Copy the unmatched data back to the original folder locations, making sure to match the file format exactly (if you've ever worked with ACH files, you'll understand why this sucked)

If you're one of the 10 people who have read my blog before, you know that I love the DOS "for" command. Like passionately. Like fairy-tale love. So my batch files were riddled with for loops, nested within other for loops, that called other batch files containing for loops. I think there was one section that had 4 or 5 nested for commands. It was wrong, disturbed, and completely un-maintainable by anyone, even myself. Months, even a year, after I left the company I got calls from someone who had to make a minor change to it, and they called me to talk them out of spraying the office with an AK-47 after looking at this code. (for you Star Trek TOS fans)

The funniest part of this, well, one of the funniest, is that I made the deadline…sort of, I was only a day late…and the DAMN THING WORKED practically unchanged for 3 years. Most of the problems came from the manual parts of the overall process, like forgetting to decrypt the files, or missing/late files, or saved to the wrong folders. I'm definitely not trying to toot my own horn here, because this was truly one of the dumbest, crappiest solutions I ever came up with. Fortunately as far as I know it's no longer in use and someone has written a proper replacement. Today I would knuckle down and do it in SSIS or Powershell, even if it took me weeks to get it right.

The real lesson from this crap code is to make things MAINTAINABLE and UNDERSTANDABLE. sed scripting regular expressions doesn't fit that criteria in any way. If you ever find yourself under pressure to do something fast at all costs, DON'T DO IT. Stop and consider long-term maintainability, not just for yourself but for others on your team. If you can't explain the basic approach in under 5 minutes, it ultimately won't succeed.

And while you may love to leave all that crap behind, it may follow you anyway, and you'll step in it again.

P.S. - if you're wondering about all the manual stuff that couldn't be changed, it was because the entire process had gone through Six Sigma, and was deemed the best possible way. Phew! Talk about stink!

I spoke at SQL Saturday #77 and all I got was this really awesome speaker's shirt!

2011-06-17T17:39:00Z

Yeah, it was 2 weeks ago, but I'm finally blogging about something!

I presented Revenge: The SQL! at SQL Saturday #77 in Pensacola on June 4. The session abstract is here, and you can download the slides from that page too. You can see how I look in the speaker's shirt here.

Overall it went pretty well. I discovered a new bit of evil just that morning and in a carefully considered, agonizing decision-making process that was full documented, tested, and approved…nah, I just went ahead and added it at the last minute. Which worked out even better than (not) planned, since it screwed me up a bit and made my point perfectly. I had a few fans in the audience, and one of them recorded it for ~~blackmail material~~ posterity.

I'd like to thank Karla Landrum (blog | twitter) and all the volunteers for putting together such a great event, and for being kind enough to let me present. (Note to Karla: I'll get the next $100 to you as soon as I can. Might need a few extra days on the next $100.)

Thanks to Audrey (blog | twitter), Peg, and Dorothy for attending and keeping the heckling down. Thanks also to Aaron (blog | twitter) for providing room and board and also not heckling. Thanks to Julie (blog | twitter) for coming up with the title for the presentation. (boo to Julie for getting sick and bailing out on us) And thanks to all of them for listening to a preview and offering their suggestions and advice!

Cross your fingers that I get accepted at SQL Saturday 81 in Birmingham, SQL Saturday 85 in Orlando, or SQL Saturday 89 in Atlanta, or just attend them anyway!

HSSFS Part 2.1 - Parsing @@VERSION

2010-10-04T17:32:29Z

For Part 2 of the Handy SQL Server Function Series I decided to tackle parsing useful information from the @@VERSION function, because I am an idiot. It turns out I was confused about CHARINDEX() vs. PATINDEX() and it pretty much invalidated my original solution. All is not lost though, this mistake turned out to be informative for me, and hopefully for you.

Referring back to the "Version" view in the prelude I started with the following query to extract the version number:

SELECT DISTINCT SQLVersion, SUBSTRING(VersionString,PATINDEX('%-%',VersionString)+2, 12) VerNum
FROM VERSION

I used PATINDEX() to find the first hyphen "-" character in the string, since the version number appears 2 positions after it, and got these results:

SQLVersion  VerNum
----------- ------------
2000        8.00.2055 (I
2005        9.00.3080.00
2005        9.00.4053.00
2008        10.50.1600.1

As you can see it was good enough for most of the values, but not for the SQL 2000 @@VERSION. You'll notice it has only 3 version sections/octets where the others have 4, and the SUBSTRING() grabbed the non-numeric characters after. To properly parse the version number will require a non-fixed value for the 3rd parameter of SUBSTRING(), which is the number of characters to extract.

The best value is the position of the first space to occur after the version number (VN), the trick is to figure out how to find it. Here's where my confusion about PATINDEX() came about. The CHARINDEX() function has a handy optional 3rd parameter:

CHARINDEX (expression1 ,expression2 [ ,start_location ] )

While PATINDEX():

PATINDEX ('%pattern%',expression )

Does not. I had expected to use PATINDEX() to start searching for a space AFTER the position of the VN, but it doesn't work that way. Since there are plenty of spaces before the VN, I thought I'd try PATINDEX() on another character that doesn't appear before, and tried "(":

SELECT SQLVersion, SUBSTRING(VersionString,PATINDEX('%-%',VersionString)+2, PATINDEX('%(%',VersionString))
FROM VERSION

Unfortunately this messes up the length calculation and yields:

SQLVersion  VerNum
----------- ---------------------------
2000        8.00.2055 (Intel X86) 
    Dec 16 2008 19:4
2005        9.00.3080.00 (Intel X86) 
    Sep  6 2009 01:
2005        9.00.4053.00 (Intel X86) 
    May 26 2009 14:
2008        10.50.1600.1 (Intel X86) 
    Apr
2008        10.50.1600.1 (X64) 
    Apr 2 20

Yuck. The problem is that PATINDEX() returns position, and SUBSTRING() needs length, so I have to subtract the VN starting position:

SELECT SQLVersion, SUBSTRING(VersionString,PATINDEX('%-%',VersionString)+2, PATINDEX('%(%',VersionString)-PATINDEX('%-%',VersionString)) VerNum
FROM VERSION

And the results are:

SQLVersion  VerNum
----------- --------------------------------------------------------
2000        8.00.2055 (I
2005        9.00.4053.00 (I
Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

Ummmm, whoops. Turns out SQL Server 2008 R2 includes "(RTM)" before the VN, and that causes the length to turn negative.

So now that that blew up, I started to think about matching digit and dot (.) patterns. Sadly, a quick look at the first set of results will quickly scuttle that idea, since different versions have different digit patterns and lengths.

At this point (which took far longer than I wanted) I decided to cut my losses and redo the query using CHARINDEX(), which I'll cover in Part 2.2.

So to do a little post-mortem on this technique:

PATINDEX() doesn't have the flexibility to match the digit pattern of the version number;
PATINDEX() doesn't have a "start" parameter like CHARINDEX(), that allows us to skip over parts of the string;
The SUBSTRING() expression is getting pretty complicated for this relatively simple task!

This doesn't mean that PATINDEX() isn't useful, it's just not a good fit for this particular problem. I'll include a version in the next post that extracts the version number properly.

UPDATE: Sorry if you saw the unformatted version of this earlier, I'm on a quest to find blog software that ACTUALLY WORKS.

HSSFS Part 3: SQL Saturday is Awesome! And DEFAULT_DOMAIN(), and how I found it

2010-10-04T10:31:09Z

Just a quick post I should've done yesterday but I was recovering from SQL Saturday #48 in Columbia, SC, where I went to some really excellent sessions by some very smart experts. If you have not yet attended a SQL Saturday, or its been more than 1 month since you last did, SIGN UP NOW!

While searching the OBJECT_DEFINITION() of SQL Server system procedures I stumbled across the DEFAULT_DOMAIN() function in xp_grantlogin and xp_revokelogin. I couldn't find any information on it in Books Online, and it's a very simple, self-explanatory function, but it could be useful if you work in a multi-domain environment. It's also the kind of neat thing you can find by using this query:

SELECT OBJECT_SCHEMA_NAME([object_id]) object_schema, name
FROM sys.all_objects
WHERE OBJECT_DEFINITION([object_id]) LIKE '%()%' 
ORDER BY 1,2

I'll post some elaborations and enhancements to this query in a later post, but it will get you started exploring the functional SQL Server sea.

UPDATE: I goofed earlier and said SQL Saturday #46 was in Columbia. It's actually SQL Saturday #48, and SQL Saturday #46 was in Raleigh, NC.