Can you trust your foreign key? (Or, feature #821 I didn’t already know about SQL Server)

Hey friends! Long time, no see. I know, you’re wondering where I’ve been. Lately, I’ve had the luxury of taking some time to work on Microsoft certifications. I just got through 70-448, Microsoft SQL Server 2008, Business Intelligence Development and Maintenance. Now, I’m working on 70-433, Microsoft SQL Server 2008, Database Development. I’ll admit… I thought the Database Development exam would be a no-brainer. Heck, I’ve been doing this for years. Much to my chagrin, I’ve learned a few things I should have already known. (Isn’t that how it always ends up?)

Anyway, I was working my way through the Self-Paced Training Kit, and stumbled across one thing that I hadn’t known before and am so excited about that I want to share it with all 12 of our readers. Here we go…

First, because good bloggers give credit where credit is due, major props to the team that wrote the training kit for exam 70-433. They include: Tobias Thernstrom, Ann Weber, Mike Hotek, and GrandMasters. It’s a well-written book, and they obviously snuck some things in there that were more about good design and development and less about answering test questions. Kudos, gentlemen and lady! I’m basically re-writing something that you’ve already covered in your book, but I think it’s really, really cool and want everyone to see it regardless of whether they’ve bought the book or not. Given that, buy the book, dear readers. Even if you’re not studying for the exam. I promise you’ll learn something new.

Okay, to the point… Do me a favor, get on your local database and run this query. Go ahead, I’ll wait for you.

SELECT name, is_not_trusted

FROM sys.foreign_keys;

Good to have you back. I missed you. I stared pensively into the horizon while I awaited your return. I even wrote a poem and a folk song. Sorry, I digress… one too many rom-coms lately. Did you see any “1” values in the is_not_trusted column? Did you even know that foreign keys could be trustworthy? Nope, neither did I. What does it mean? It means that your foreign key hasn’t been verified by the system. How does this happen? Well, remember that optional clause called WITH CHECK | NOCHECK when you create a foreign key constraint? Yup. That did it.

So why does this matter? Well, it actually has an effect on your query execution plan in some cases. You know, that Query Optimizer is pretty darn smart. Let’s look at an example from the trusty old AdventureWorks database. I’m using AdventureWorks2008R2, but it should work with the older AdventureWorks databases. The scenario is this: I want to know if I have any sales orders that have invalid customers. CustomerID is a NOT NULL column in Sales.SalesOrderHeader. I know that if I count all the rows in Sales.SalesOrderHeader and the number of rows returned in the query we’re about to run, I should get the same number of rows back each time. But, sometimes rogue values slip in, especially when the database design has been refined over time. This could happen for any number of reasons: constraints that were added after the fact, legacy data, disabled constraints, etc.

Run this query, but turn on Include Actual Execution Plan (Ctrl+M) before you do.

SELECT soh.*

FROM Sales.SalesOrderHeader soh

WHERE EXISTS (SELECT * FROM Sales.Customer c WHERE soh.CustomerID = c.CustomerID);

Note that I’m using WHERE EXISTS rather than an IN clause with a subquery. This is because in this business scenario, I just want a boolean result (true or false), and I want it to run FAST.

Check out the execution plan. Note that the Customer table was never accessed. Why? Because that foreign key is trusted! Since it was verified on creation, we know that no rogue CustomerID’s snuck into the Sales.SalesOrderHeader table. It doesn’t even need to look at it.

Now, let’s muck with the foreign key and make it un-trusted. We’re disabling the foreign key with this statement. Books Online has a good article about what this means.

ALTER TABLE Sales.SalesOrderHeader

NOCHECK CONSTRAINT FK_SalesOrderHeader_Customer_CustomerID;

Verify that the foreign key is disabled by checking sys.foreign_keys again:

SELECT name, is_disabled, is_not_trusted

FROM sys.foreign_keys

WHERE name = ‘FK_SalesOrderHeader_Customer_CustomerID’;

If we run our query again, we see a completely different execution plan. Now, the query optimizer has to go look at the Sales.Customer table to get us an answer:

SELECT soh.*

FROM Sales.SalesOrderHeader soh

WHERE EXISTS (SELECT * FROM Sales.Customer c WHERE soh.CustomerID = c.CustomerID);

The execution plan had to change because SQL Server cannot guarantee that a CustomerID wasn’t entered while the foreign key constraint was disabled.

Here’s where it gets interesting. Enable the foreign key by executing the following:

ALTER TABLE Sales.SalesOrderHeader

CHECK CONSTRAINT FK_SalesOrderHeader_Customer_CustomerID;

Check out your sys.foreign_keys table again.

SELECT name, is_disabled, is_not_trusted

FROM sys.foreign_keys

WHERE name = ‘FK_SalesOrderHeader_Customer_CustomerID’;

What? It’s enabled, but it’s still not trusted! If we execute our query, we’re still going to get the execution plan that looks at Sales.Customer. Why? Well, that CHECK keyword up there just said to enable the foreign key, it didn’t say verify it. We have to issue this statement (Of course, if an invalid CustomerID snuck in while your FK was disabled, this ALTER is going to fail):

ALTER TABLE Sales.SalesOrderHeader

WITH CHECK —> this clause will make your FK trustworthy again

CHECK CONSTRAINT FK_SalesOrderHeader_Customer_CustomerID;

If we run our query now, we’ll get the sleeker, more efficient plan, because now our foreign key is enabled and trusted.

SELECT soh.*

FROM Sales.SalesOrderHeader soh

WHERE EXISTS (SELECT * FROM Sales.Customer c WHERE soh.CustomerID = c.CustomerID);

Cool, huh? The point is, this one teensy-tiny flag in the foreign key metadata makes a huge difference in how the query optimizer handles your query. It might not be much, but why not make sure that you can get as many trusted foreign keys as possible? You might just end up looking like a rock star for improving performance without having to modify any actual queries.

Now if I could just figure out put to get a is_not_trusted flag on people…

Why you wanna treat me this way? (Or Extra Special Super Secret Security in SS 2008 Reporting Services)

I agree with Audrey.  I too, heart Microsoft.  For me Microsoft’s SQL Server has been the cornerstone of a very rewarding career.  But in every relationship there are moments of great disappointment.  I will now share one of those moments in this thing me and SQL Server got going. 

I have a lot of SSIS experience, a bit of SSAS and practically no SSRS experience.    I have written many reports in “Access”, and have “heard” that of the three tiers in the BIDS stack SSRS is the easiest, but I wanted more than theory and hearsay.  Since no “real” projects came my way, I decided to do the genuine geek thing and just play with it myself.  The simple plan:  I would set up a reporting server on my laptop, a lovely ASUS Notebook U80V loaded with Windows Vista Home Edition.

I got started.  I did an install of SSRS. It’s just a series of clicks through the SQL Server Install wizard and I would be on my way to deploying reports on my very own server.  I took all the defaults.  Everything I looked up online said that would be ok to do.  Once I had my green success icon on the install I knew what to next (now that I had looked up what to do next).  I went to internet explorer, typed in http://myservername/reports to open up the Report Manager.  This is where I would be greeted with a GUI which would gracefully guide me through deploying some reports.  Everything in windows is intuitive, right?  Now that I knew how to open the report manager  , I would be able to figure out the rest easily.  (I’m pretty good with these computer thingies).  (Stop laughing at me). 

Here is what I was greeted with:

Awesome!  No errors! 

Now I just upload an RDL (report) file from the Contents Tab using the Upload File button and page.   …….   Um …… just , wait, it’s got to be here somewhere…… let me check the links —-Home.  (already home).  Um, My subscriptions ——(don’t have any).  Help——  (too much help). 

Well that’s weird.  I can’t “do” anything.  This doesn’t look like all those pictures of “functional” pages of Report Manager I’ve seen online:

Looking at it, it seems like a permissions problem but ….it can’t be a permission problem because this is my personal, stand alone laptop.  There is only me as the user and I’m an administrator.  The server is installed under my user (an administrator btw).  Every user, every service, every application I touch should have absolute God rights on this machine.  Can’t be permissions.

I took a deep breath.  I knew what I was in for now.  Dedicating a portion of my life to fishing online for the (probably annoying) solution.  From topics on MSDN/BOL  I started with “report manager” .   I was optimistic that the answer was close.  And it was about halfway down the page I saw this note:

 Note:
If you are using Windows Vista or Windows Server 2008, you must configure the report server for local administration before you can use Report Manager to manage a local report server instance. For instructions on how to configure the server, see How to: Configure a Report Server for Local Administration on Windows Vista and Windows Server 2008.

 

Hmm.  I was indeed using Windows Vista.  But wasn’t I already configured for local administration?   After all, this is my personal, stand alone laptop.  There is only me as the user and I’m an administrator.  The server is installed under my user (an administrator btw).  Every user, every service, every application I touch should have absolute God rights on this machine.  Can’t be permissions.

What the heck, I’m only ten minutes in at this point.  I followed the link.  

There was the answer.  Right at the top. 

——————————————————————————

Deploying Reporting Services on Windows Vista and Windows Server 2008 requires additional configuration steps if you want to administer a report server instance locally. Both Windows Vista and Windows Server 2008 limit the overuse of elevated permissions by removing administrator permissions when accessing applications. Because the operating system removes permissions, members of the local Administrators group run most applications as if they using the Standard User account.

While this practice improves the overall security of your system, it prevents you from using the predefined, built-in role assignments that Reporting Services creates for local administrators. However, with additional configuration on your part, you can effectively manage report server content and operations using standard user permissions if you do the following:

———————————————————————————–

Folks,  I have to say that the verbiage above is so unbelievable to me as to not need any embellishment for comic effect.  It’s like the SNL skit when Tina Fey played Sarah Palin in the Katie Couric interview and all she did was read from the transcript of the actual interview.

My personal favorite tidbits from the above:

  • “limit the overuse of elevated permissions”  you mean like permissions to execute all designed functionality?  Without a warning, flag, or tool tip in sight?
  • “requires additional configuration steps IF you want to administer report server instance locally”.  Maybe just assume that if a user installs it then that user may want to use it, “locally” being a popular choice for “how”.

Anyway I got my server working, using the below instructions.  I post them here now from MSDN/BOL for the sake of the children everywhere:

————————–

  To configure local report server administration on Windows Vista and Windows Server 2008

  1. Open a browser window with Run as administrator permissions. From the Start menu, click All Programs, right-click Internet Explorer, and select Run as administrator.
  2. Click Allow to continue.
  3. In the URL address, enter the Report Manager URL. For instructions, see How to: Start Report Manager.
  4. Click Tools.
  5. Click Internet Options.
  6. Click Security.
  7. Click Trusted Sites.
  8. Click Sites.
  9. Add http://<your-server-name>.
  10. Clear the check box Require server certification (https:) for all sites in this zone if you are not using HTTPS for the default site.
  11. Click Add.
  12.  Click OK.
  13.  In Report Manager, on the Home page, click Properties.
  14. Click New Role Assignment.
  15. Type your Windows user account in this format: <domain>\<user>.
  16. Select Content Manager.
  17. Click OK.
  18.  Click Site Settings in the upper corner of the Home page.
  19. Click Configure Site-wide security.
  20. Click New Role Assignment.
  21. Type your Windows user account in this format: <domain>\<user>
  22. Select System Administrator.
  23. Click OK.
  24. Close Report Manager.
  25. Re-open Report Manager in Internet Explorer, without using Run as administrator.

  ————————— 

So, even though the server was my personal, stand alone laptop, even though there was only me as the user and I was an administrator, even though the server was installed under my user (an administrator btw) and even though every user, every service, every application I touched should have had absolute God rights on the machine, it was the Permissions.

But I’m over it now.  Things are good again between me and SQL Server.  If anything, I think it’s made our relationship stronger. 😉

SSIS 2008 – Annotations are Word-wrap-less (is that a word?)

I just have to throw this rant out there… I’m trying to be a good little developer and put a comment about a weird update we’re making based on a new business rule.  I add an Execute SQL task, but my pretty update statement in there, and connect all the lines back up.  Go to put in an annotation, (right-click on design surface, click “Add Annotation”), and start typing away.  In case you don’t know me well, I tend to be a little, um, wordy at times.  I see the text box getting longer and longer, and I’m assuming that when I click off, it’ll word wrap for me.  You know… word wrap.  Even Notepad has word wrap!

Lo and behold, no wrapping.  Just one loooong line of text.  I Google, and here’s what MSDN has to say:  “The text block does not provide word wrap, but instead scrolls the text as the text exceeds the width of the text block. Press Ctrl-Enter to add a new line to the text block.”  Ladies and gentlemen, welcome to SSIS 2008, where we can handle complex, enterprise-level ETL processes, but we can’t wrap some text in a little box.  Forgive me, Microsoft.  I heart you, even if you did just waste 15 minutes of my day.  Even sadder, I spent 5 minutes repeatedly hitting “Enter”, trying to figure out what was wrong with it before I decided to go to the Google. 

Ctrl-Enter, my friends.