Tag Archives: I need help

Puzzling TVFunctions

I had the opportunity to tune a highly-used function the other day, and was most puzzled by the difference between the old and the new function.  This was on SQL Server 2005, and I was able to repro the issue on-server, but not on my SQL Server 2012 instance.

The old function specified the return table; it actually inserted the data into a table variable, and returned that:

-- =============================================
-- The working, but slow, function:
-- =============================================
CREATE FUNCTION test.myfunction_tableVar ( @Data NVARCHAR(800) )
RETURNS @tableVar TABLE ( data VARCHAR(800) )
AS 
    BEGIN    

        INSERT  @tableVar
                ( data )
        VALUES  ( LTRIM(RTRIM(@data)) ) ;
        RETURN    
    END    

GO

Note that these have been greatly simplified from the original functions, but still showed the same odd behavior.

Anyway, when I created a new function for testing, I neglected to use a table variable; instead, I just specified a TABLE return:

-- =============================================
-- The new, faster, wrong-resultset function:
-- =============================================
CREATE FUNCTION test.myfunction_table ( @Data NVARCHAR(800) )
RETURNS TABLE
AS
RETURN
SELECT LTRIM(RTRIM(@data)) AS [data] ;

I tested these thoroughly; a given input produced a single row of output, and the output of one function was IDENTICAL to the output of the other in all cases. I even joined the two functions on a.data = b.data, proving the identical output. However, when I used these in a query against live data, I got different resultsets from the query:

-- This returned 915 rows, as it should:
SELECT  idnumber
FROM    test.myTable
        INNER JOIN Speak.test.myfunction_tableVar('nom') f ON CHARINDEX(f.data,
                                                              [notes]) > 0;
-- This returned 911 rows!
SELECT  idnumber
FROM    test.myTable
        INNER JOIN Speak.test.myfunction_table('nom') f ON CHARINDEX(f.data,
                                                              [notes]) > 0;

So, help me out here. How can two functions with the EXACT same resultset, and used in the exact same way, turn out different resultsets when used in a query? Is this a bug, or a feature?

Happy days,
Jen McCown
www.MidnightDBA.com/Jen

Convince me to read books (WHERE LEN(argument) <= 140)

While you wait for my awesome upcoming blog on data exception notification with SSRS, let’s pursue a whim of mine.

I want your techie book recommendations. Write me a tweet @MidnightDBA, or in the comments below, that compels me to read your book of choice. (Yes, you can recommend your own book.)

But the tweet better be pretty compelling.

I’ll kick this off: My recommendation is MVP Deep Dives II,because it’s 60 chapters by 60 MVPs on a variety of SQL topics, and benefits Project Smile. Compelling, no?

I’ll also add the Twitter answers below, as I get them. Happy tweeting!

Jen McCown
http://www.MidnightDBA.com/Jen

  • @ThomasRushton book to read: “Managing Smaller Projects“, Mike Watson, ISBN:1895186854. Makes a change from the heavy stuff for big projects.
  • @eccentricDBASQL Antipatterns” Bill Karwin.
  • @AmbivalentGeek  SQL and Relational Theory” by CJ Date http://amzn.com/0596523068  Helped answer all my “whys” about SQL, and very well written.
  • wshawnmelton I’m always like “what is that script to check for ___ issue?” Look no further than http://www.red-gate.com/our-company/about/book-store/accidental-dba  Good info
  • greeleygeek Clean Code by Robert Martin. Written from a Java perspective but applicable to make anyone a better coder (C#, T-SQL, etc).
  • zippy1981 finally, “the art of unix programming” by Eric S Raymond. To grok powershell, you need to understand the unix way.
  •  jayape Inside the Query Optimizer, a great read into how the optimizer works. Invaluable for understanding execution plans
  •  SQLSoldier  Just stop reading! Watch cartoons instead. :)
  •  zippy1981 The C Programming Language by K&R. Its a short book, and everyone in IT should know C even if they are not a dev
  •  peschkaj  Pragmatic Thinking & Learning. This book needs to advertising. It’s a study of how we learn and provides good techniques for you to improve your learning process. All based on legit research
    • @MikeReigler @Peschkaj ‘s pick +1 for me. That’s my fav.
  • AdamMachanic Code Complete.” I think I already recommended that to you quite a while ago, though. Did you read it?
  •  zippy1981 The Mythical Man month. Its more PM than techie, but it explains a lot of lessons the industry has yet to learn
    • ThomasRushton +1 for @zippy1981’s recommendation of Mythical Man Month. Yes, it’s an old book, but it’s still good.
  • sqlnikon  I enjoy reading Time Management for System Admins by Thomas A. Limoncelli. Keeps my day on track.

Un-SQL Friday: Stop SOPA!

This will be an unusually political Un-SQL Friday – and it’s two days early, to boot – but I’m making an exception to my no-politics-in-SQL rule – because it’s about internet freedoms.

I hereby declare this WEDNESDAY Un-SQL Friday. While T-SQL Tuesday is “…the SQL Server blogosphere’s first recurring, revolving blog party….on the chosen [SQL-related] topic,” UN-SQL Friday is a completely intermittant blog party about something SQL-adjacent.

This week’s Un-SQL Friday roundup is about SOPA, the Stop Online Piracy Act. Please take a couple minutes and read up…if this is meaningful to you, then blog and do what you can to spread the word on Wednesday, November 16 – American Censorship Day. Get involved however you feel you can best make a difference.

The internet is a wonderful, free place, and I believe that measures to combat piracy should be better targeted than SOPA.

Be sure to mention in your blog that you’re writing for Un-SQL Friday, and link to this post. Have it up ASAP…before midnight on Wednesday, November 16 is best. But we always take late submissions.

Reading

Here are a few highlights and articles to get you started.

11/17 addition: International human rights community vs.SOPA: “An enormous, diverse global coalition of press freedom and human rights groups have signed onto a letter (PDF) opposing America’s Stop Online Piracy Act, the worst proposed Internet law in the USA’s legislative history.”

PCWorld article: House hearing on Stop Online Piracy Act scheduled

BoingBoing: Stop SOPA Save the internet

  • The worst bill in Internet history is about to become law. …this law would give government and corporations the power to block sites like BoingBoing over infringing links on at least one webpage posted by their users.
  • …[government gets] unbelievable power to take down sites, threaten payment processors into stopping payment to sites on a blacklist, and throw people in jail for posting ordinary content …
  • Internet and democracy groups are planning an Internet-wide day of protest called American Censorship Day on Wednesday, November 16th for the day Congress holds a hearing on these bills to create the first American Internet censorship system. Every single person with a website can join and needs to.

Digital Digest: Internet Security And Innovation Under Threat From SOPA

  • The Stop Online Piracy Act (SOPA) could have a drastic effect on software development, and Internet security, the Electronic Frontier Foundation (EFF) warned.
    SOPA would allow the government to blacklist websites and services via the Domain Name System (DNS).
  • The EFF also warns that many Internet security tools, such as firewalls or the widely used SSH tool, come with proxy and VPN functions, and under SOPA, any of these tools could be made illegal.

More from Digital Digest: US House of Reps Introduce New Controversial Copyright Bill

  • …the new legislation gives the government broad powers to intervene in what is traditionally a civil matter, to allow domestic websites to be seized, and injunctions filed against foreign websites, at the behest of Hollywood and the music industry, the two major industries supporting the bill

ZDNet: New house of representatives bill may strangle the internet or nerf the first amendment

  • SOPA wants to give, well, pretty much anyone with a law degree the right to shut down Web sites and domains. SOPA has some nasty teeth. First, according to the EFF, it allows individual companies to force payment processors (think PayPal or VISA) to stop paying any site that might be considered to be engaging in, enabling, or facilitating any form of copyright infringement.Let’s first look at how this might impact you. What cloud-based services do you use? Gmail? Dropbox? Amazon’s music sharing service? What about eBay? What about Facebook? Or perhaps you simply host your corporate email at an Exchange hosting provider, like I do.Let’s use that last one, as an example. My hosting provider, like most, offers a free SharePoint account along with their email hosting. Let’s say one of their other customers uploads something they shouldn’t. This new legislation would allow any other private company (including my hosting provider’s competitors) to demand that payment agencies cut off payments, effectively strangling cash flow and shutting the company down.

CNet: SOPA Hollywoods latest effort to turn back time

Major thanks to BoingBoing, without whom I probably wouldn’t have seen this in time.

-Jen