Post reply

run query with a string input into an int field

  • December 2, 2009 at 7:34 pm

    #1086850

    dmoldovan (12/2/2009)

    Let me recommend some excellent resources about using arrays as input parameters:

    - http://www.sqlmag.com/Article/ArticleID/100657/sql_server_100657.html

    - http://www.sqlmag.com/Article/ArticleID/100975/sql_server_100975.html

    - http://www.sommarskog.se/arrays-in-sql-2005.html

    I just don't understand why people think they need CLR's for such a thing. There are a lot of ways to do this without CLR's... here's one...

    http://www.sqlservercentral.com/articles/T-SQL/63003/

    --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)

  • December 2, 2009 at 7:46 pm

    #1086856

    What's a CLR?

  • December 2, 2009 at 8:42 pm

    #1086872

    From Books Online...

    The common language runtime (CLR) is the heart of the Microsoft .NET Framework and provides the execution environment for all .NET Framework code. Code that runs within the CLR is referred to as managed code. The CLR provides various functions and services required for program execution, including just-in-time (JIT) compilation, allocating and managing memory, enforcing type safety, exception handling, thread management, and security. See the .NET Framework SDK for more information.

    With the CLR hosted in Microsoft SQL Server (called CLR integration), you can author stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code. Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.

    Pay particular attention to the words "in some scenarios". CLR's are NOT a panacea for high performance code and someone skilled in the art of writing high performance T-SQL can and will write T-SQL code that can blow the doors off of many CLR's.

    Most folks that I know that write CLR's use C# but I suppose any language capable of creating a DLL will do.

    It's a bit of a paradox, sometimes.... lot's of folks will write a CLR to do something they can't figure out how to write in T-SQL but to write a good CLR, you have to know what works in a high performance manner in the database. There's also some overhead associated with implementing and using a CLR so the CLR really needs to be good in order for it to be faster than some well written T-SQL.

    I'll admit that there are some places where a well written CLR will beat some equally well written T-SQL... but there aren't many.

    --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)

Viewing 3 posts - 16 through 17 (of 17 total)

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