Simple CLR request

  • Alan Burstein

    SSC Guru

    Points: 61079

    I'm trying to create what I believe should be a very simple Table Valued Function CLR. Here's a T-SQL version of what I'm trying to create:

    CREATE FUNCTION dbo.NGLoop(@string varchar(8000), @N int)

    RETURNS @NG TABLE (Position int, Token varchar(8000)) AS

    BEGIN

    DECLARE @token varchar(8000), @position int = 0;

    WHILE @position < LEN(@string) - (@N-1) AND (@N > 0 AND @N <= LEN(@string))

    BEGIN

    SELECT @token = SUBSTRING(@string,@position+1,@N),

    @position = @position+1;

    INSERT @NG VALUES(@position, @token)

    END

    RETURN;

    END

    GO

    Here's some examples of what the results should look like (note the comments):

    DECLARE @string varchar(8000) = 'abc123';

    SELECT * FROM dbo.NGLoop(@string,1); -- should return 6 rows: (1,a), (2,b), (3,c), etc...

    SELECT * FROM dbo.NGLoop(@string,2); -- should return 5 rows: (1,ab), (2,bc), (3,c1), etc...

    SELECT * FROM dbo.NGLoop(@string,3); -- should return 4 rows: (1,abc), (2,bc1), (3,c12), etc...

    SELECT * FROM dbo.NGLoop(NULL,1); -- should return nothing

    SELECT * FROM dbo.NGLoop('xxx',-1); -- should return nothing

    SELECT * FROM dbo.NGLoop('xxx',NULL); -- should return nothing

    SELECT * FROM dbo.NGLoop('xxx',10); -- should return nothing

    Hopefully this is simple and self-explanatory enough to understand what I'm trying to do. a C# or VB.Net version should be fine. I've been racking my brain here, any help would be greatly appreciated. Thanks!

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Con Alexis

    Old Hand

    Points: 331

    Shouldn't be too hard using Linq...

    using System.Linq;

    [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "clrNGLoop_FillRow", TableDefinition = "Position int, Token nvarchar(4000)")]

    public static IEnumerable clrNGLoop(SqlString value, SqlInt32 n)

    {

    int idx = 1;

    Lookup<int, char> l = (Lookup < int, char> )value.Value.ToLookup(x => idx++, y => y);

    return l.Select(x => new Item() { Position = x.Key, Token = new string(l.Where(y => y.Key >= x.Key && y.Key < (x.Key + n.Value)).Select(z => z.First()).ToArray()) }).Where(v=>v.Token.Length == n.Value);

    }

    public static void clrNGLoop_FillRow(Object obj, out SqlInt32 position, out SqlString token)

    {

    Item item = (Item)obj;

    position = new SqlInt32(item.Position);

    token = new SqlString(item.Token);

    }

    public class Item

    {

    public int Position;

    public string Token;

    }

  • Luis Cazares

    SSC Guru

    Points: 183633

    Just curious on why you want to use CLR for this. I hope that you made that function just to make it easy to translate to procedural programming.

    If anyone else tries to do something like this in T-SQL, this should be a lot better.

    CREATE FUNCTION dbo.NGLoop(@string varchar(8000), @N int)

    RETURNS TABLE AS

    RETURN

    WITH

    E(n) AS(

    SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)

    ),

    E2(n) AS(

    SELECT a.n FROM E a, E b

    ),

    E4(n) AS(

    SELECT a.n FROM E2 a, E2 b

    ),

    cteTally(n) AS(

    SELECT TOP(LEN(@string)) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n

    FROM E4

    )

    SELECT n, SUBSTRING( @string, n, @N) Token

    FROM cteTally

    WHERE n <= LEN(@string) - @N + 1

    AND @N > 0;

    GO

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Jeff Moden

    SSC Guru

    Points: 996689

    Luis Cazares (6/17/2016)


    Just curious on why you want to use CLR for this.

    +1000

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Alan Burstein

    SSC Guru

    Points: 61079

    Con Alexis thank you sir. That's what I was looking for. Sorry for the late reply, yesterday was my birthday.

    Luis. It's for an article I'm working on. I have a tally table based version and wanted to compare the performance to a CLR. I figured that a more people who could write CLRs would understand the procedural code.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Luis Cazares

    SSC Guru

    Points: 183633

    Alan.B (6/18/2016)


    Con Alexis thank you sir. That's what I was looking for. Sorry for the late reply, yesterday was my birthday.

    Luis. It's for an article I'm working on. I have a tally table based version and wanted to compare the performance to a CLR. I figured that a more people who could write CLRs would understand the procedural code.

    I hope you had a nice birthday!

    Seems like I wasn't too far away from the truth :-). I just got puzzled because the code didn't seem like your usual code. And I just thought on leaving the alternative for someone else.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Solomon Rutzky

    SSCoach

    Points: 16259

    Hey Alan. Interesting request. I do have a suggestion for the code, but first I would like to mention that it is probably best to state at the beginning that the desire for the code is to do performance comparisons as it will help inform / guide responders. For example, it would help when deciding between expediency of writing the code and efficiency of processing the code. Case in point: LINQ is very powerful, flexible, and expressive. However, that does come at a cost at runtime.

    Also, when comparing performance between T-SQL and SQLCLR code, it is important to test both a VARCHAR(4000) and NVARCHAR(4000) versions of the T-SQL code since SQLCLR can only do NVARCHAR. So testing both using NVARCHAR(4000) will get the truest sense of performance difference, and testing with VARCHAR(4000) will provide a pragmatic difference since if one knows that their data will only ever be VARCHAR, then that reality won't be constrained with trying to be fair in testing ;-). Please note that I am using 4000 instead of 8000 for the VARCHAR datatypes since it doesn't help to test 8000 characters against SQLCLR using NVARCHAR(4000) as the extra 4000 characters will be silently truncated, resulting in a different input string given to the SQLCLR object.

    Here is the iTVF with Tally Table I used (I created the VARCHAR(4000) version by copying and pasting it, changing the name, and changing "@String" to be VARCHAR(4000)):

    CREATE FUNCTION dbo.NGramITVF(@String NVARCHAR(4000), @N INT)

    RETURNS TABLE

    --WITH SCHEMABINDING

    AS RETURN

    WITH nums AS

    (

    SELECT TOP (LEN(@string) - (@N - 1))

    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS [Num]

    FROM [sys].[all_columns]

    )

    SELECT nums.[Num] AS [Position],

    SUBSTRING(@string, nums.[Num], @N) AS [Token]

    FROM nums;

    And here is the .NET / C# code that I used for the SQLCLR streaming TVF:

    [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "NGramSolomon_FillRow",

    TableDefinition = "Position INT, Token NVARCHAR(4000)")]

    public static IEnumerable NGramSolomon(

    [SqlFacet(MaxSize = 4000)] SqlChars InputString, SqlInt32 TokenSize)

    {

    if (InputString.IsNull || TokenSize.IsNull)

    {

    yield break;

    }

    int _Take = TokenSize.Value;

    int _Limit = ((int)InputString.Length - _Take);

    if (_Take < 1 || _Limit < 0)

    {

    yield break;

    }

    ResultRow _Result = new ResultRow();

    char[] _Chars = InputString.Value;

    char[] _Temp = new char[_Take];

    for (int _Index = 0; _Index <= _Limit; _Index++)

    {

    Array.Copy(_Chars, _Index, _Temp, 0, _Take);

    _Result.Position = (_Index + 1);

    _Result.Token = new SqlChars(_Temp);

    yield return _Result;

    }

    }

    public class ResultRow

    {

    public int Position;

    public SqlChars Token;

    }

    public static void NGramSolomon_FillRow(Object obj,

    out SqlInt32 position, out SqlChars token)

    {

    ResultRow _Item = (ResultRow)obj;

    position = new SqlInt32(_Item.Position);

    token = _Item.Token;

    }

    Test results:

    NVARCHAR(4000) Tests (4000 characters):

    NGLoop : 60 - 80 ms

    SQLCLR_ConAlexis : 375 - 400 ms

    iTVF_TallyTable : 2 - 6 ms

    iTVF_TallyTableVC : 2 - 6 ms

    SQLCLR_Solomon : 2 - 9 ms

    NVARCHAR(MAX) Tests (40,000 characters):

    iTVF_TallyTable : 34 - 50 ms

    SQLCLR_ConAlexis : 39,000 ms

    SQLCLR_Solomon : 34 - 56 ms

    As you can see, the T-SQL version is only a few milliseconds faster than the SQLCLR. Of course, perhaps Alan's version using his "numbersAB" iTVF will be slightly faster, or maybe had I done an inline Tally Table then the T-SQL version might have been slightly faster. But that still wouldn't be much of a difference.

    Still, it seems that there is no compelling reason to use SQLCLR for this particular task. HOWEVER, in so far as the NGram stuff might be used for string searches, it should be noted that depending on what those strings are, there could be a case for using SQLCLR. That case would be if the input strings use combining characters. Combining characters are accents and other marks that can be added to many letters and will display in the same position as the base character, appearing as a single character. And multiple combining characters can be used. When present, the base character and any number of combining characters that follow it need to be treated as a single unit. T-SQL has no facility for handling combining sequences, but .NET does. And no, combining characters are not the same as Supplementary Characters, which can be handled correctly in T-SQL when using a Collation ending in _SC, which were introduced in SQL Server 2012.

    Take care,

    Solomon..

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • Alan Burstein

    SSC Guru

    Points: 61079

    Solomon Rutzky (6/26/2016)


    Hey Alan. Interesting request. I do have a suggestion for the code, but first I would like to mention that it is probably best to state at the beginning that the desire for the code is to do performance comparisons as it will help inform / guide responders. For example, it would help when deciding between expediency of writing the code and efficiency of processing the code. Case in point: LINQ is very powerful, flexible, and expressive. However, that does come at a cost at runtime.

    Also, when comparing performance between T-SQL and SQLCLR code, it is important to test both a VARCHAR(4000) and NVARCHAR(4000) versions of the T-SQL code since SQLCLR can only do NVARCHAR. So testing both using NVARCHAR(4000) will get the truest sense of performance difference, and testing with VARCHAR(4000) will provide a pragmatic difference since if one knows that their data will only ever be VARCHAR, then that reality won't be constrained with trying to be fair in testing ;-). Please note that I am using 4000 instead of 8000 for the VARCHAR datatypes since it doesn't help to test 8000 characters against SQLCLR using NVARCHAR(4000) as the extra 4000 characters will be silently truncated, resulting in a different input string given to the SQLCLR object.

    Here is the iTVF with Tally Table I used (I created the VARCHAR(4000) version by copying and pasting it, changing the name, and changing "@String" to be VARCHAR(4000)):

    CREATE FUNCTION dbo.NGramITVF(@String NVARCHAR(4000), @N INT)

    RETURNS TABLE

    --WITH SCHEMABINDING

    AS RETURN

    WITH nums AS

    (

    SELECT TOP (LEN(@string) - (@N - 1))

    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS [Num]

    FROM [sys].[all_columns]

    )

    SELECT nums.[Num] AS [Position],

    SUBSTRING(@string, nums.[Num], @N) AS [Token]

    FROM nums;

    And here is the .NET / C# code that I used for the SQLCLR streaming TVF:

    [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "NGramSolomon_FillRow",

    TableDefinition = "Position INT, Token NVARCHAR(4000)")]

    public static IEnumerable NGramSolomon(

    [SqlFacet(MaxSize = 4000)] SqlChars InputString, SqlInt32 TokenSize)

    {

    if (InputString.IsNull || TokenSize.IsNull)

    {

    yield break;

    }

    int _Take = TokenSize.Value;

    int _Limit = ((int)InputString.Length - _Take);

    if (_Take < 1 || _Limit < 0)

    {

    yield break;

    }

    ResultRow _Result = new ResultRow();

    char[] _Chars = InputString.Value;

    char[] _Temp = new char[_Take];

    for (int _Index = 0; _Index <= _Limit; _Index++)

    {

    Array.Copy(_Chars, _Index, _Temp, 0, _Take);

    _Result.Position = (_Index + 1);

    _Result.Token = new SqlChars(_Temp);

    yield return _Result;

    }

    }

    public class ResultRow

    {

    public int Position;

    public SqlChars Token;

    }

    public static void NGramSolomon_FillRow(Object obj,

    out SqlInt32 position, out SqlChars token)

    {

    ResultRow _Item = (ResultRow)obj;

    position = new SqlInt32(_Item.Position);

    token = _Item.Token;

    }

    Test results:

    NVARCHAR(4000) Tests (4000 characters):

    NGLoop : 60 - 80 ms

    SQLCLR_ConAlexis : 375 - 400 ms

    iTVF_TallyTable : 2 - 6 ms

    iTVF_TallyTableVC : 2 - 6 ms

    SQLCLR_Solomon : 2 - 9 ms

    NVARCHAR(MAX) Tests (40,000 characters):

    iTVF_TallyTable : 34 - 50 ms

    SQLCLR_ConAlexis : 39,000 ms

    SQLCLR_Solomon : 34 - 56 ms

    As you can see, the T-SQL version is only a few milliseconds faster than the SQLCLR. Of course, perhaps Alan's version using his "numbersAB" iTVF will be slightly faster, or maybe had I done an inline Tally Table then the T-SQL version might have been slightly faster. But that still wouldn't be much of a difference.

    Still, it seems that there is no compelling reason to use SQLCLR for this particular task. HOWEVER, in so far as the NGram stuff might be used for string searches, it should be noted that depending on what those strings are, there could be a case for using SQLCLR. That case would be if the input strings use combining characters. Combining characters are accents and other marks that can be added to many letters and will display in the same position as the base character, appearing as a single character. And multiple combining characters can be used. When present, the base character and any number of combining characters that follow it need to be treated as a single unit. T-SQL has no facility for handling combining sequences, but .NET does. And no, combining characters are not the same as Supplementary Characters, which can be handled correctly in T-SQL when using a Collation ending in _SC, which were introduced in SQL Server 2012.

    Take care,

    Solomon..

    Great stuff Solomon! Its been a busy couple of weeks. I'm going to compile and play around with this code later today and post back. Thanks again sir.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Alan Burstein

    SSC Guru

    Points: 61079

    Ok Solomon, I tried to figure this out and failed. Forgive my CLR noobness (I create about 1 CLR/year) and I'm sure this is simple but I'm having a hard time compiling your code. I've been playing around with this on/off for the past week.

    I've been trying with the csc.exe in .NET 3.5 and 2.0 I've added (then removed when it did not help) this to the top using <namespace> statements...

    using System;

    using System.Data;

    using Microsoft.SqlServer.Server;

    I saved your code as NGramsNV4000.cs and have been attempting to compile the code using this command:

    C:\Windows\Microsoft.NET\Framework\v2.0.50727>csc.exe /target:library NGramsNV4000.cs

    This is what I get in v2.0.50727:

    C:\Windows\Microsoft.NET\Framework\v2.0.50727>csc.exe /target:library NGramsNV4000.cs

    Microsoft (R) Visual C# 2005 Compiler version 8.00.50727.5483

    for Microsoft (R) Windows (R) 2005 Framework version 2.0.50727

    Copyright (C) Microsoft Corporation 2001-2005. All rights reserved.

    NGramsNV4000.cs(5,15): error CS1518: Expected class, delegate, enum, interface,

    or struct

    NGramsNV4000.cs(6,29): error CS1518: Expected class, delegate, enum, interface,

    or struct

    NGramsNV4000.cs(21,26): error CS1518: Expected class, delegate, enum, interface,

    or struct

    NGramsNV4000.cs(23,7): error CS1001: Identifier expected

    NGramsNV4000.cs(23,9): error CS1518: Expected class, delegate, enum, interface,

    or struct

    NGramsNV4000.cs(24,7): error CS1001: Identifier expected

    NGramsNV4000.cs(24,9): error CS1518: Expected class, delegate, enum, interface,

    or struct

    NGramsNV4000.cs(24,21): error CS1518: Expected class, delegate, enum, interface,

    or struct

    NGramsNV4000.cs(24,32): error CS1518: Expected class, delegate, enum, interface,

    or struct

    NGramsNV4000.cs(31,23): error CS1518: Expected class, delegate, enum, interface,

    or struct

    NGramsNV4000.cs(34,2): error CS1022: Type or namespace definition, or

    end-of-file expected

    NGramsNV4000.cs(6,27): error CS0101: The namespace '<global namespace>' already

    contains a definition for '?'

    NGramsNV4000.cs(4,57): (Location of symbol related to previous error)

    NGramsNV4000.cs(23,7): error CS0101: The namespace '<global namespace>' already

    contains a definition for '?'

    NGramsNV4000.cs(4,57): (Location of symbol related to previous error)

    NGramsNV4000.cs(24,7): error CS0101: The namespace '<global namespace>' already

    contains a definition for '?'

    NGramsNV4000.cs(4,57): (Location of symbol related to previous error)

    NGramsNV4000.cs(24,31): error CS0101: The namespace '<global namespace>' already

    contains a definition for '?'

    NGramsNV4000.cs(4,57): (Location of symbol related to previous error)

    This is what I get in v3.5:

    C:\Windows\Microsoft.NET\Framework\v3.5>csc.exe /target:library NGramsNV4000.cs

    Microsoft (R) Visual C# 2008 Compiler version 3.5.30729.5420

    for Microsoft (R) .NET Framework version 3.5

    Copyright (C) Microsoft Corporation. All rights reserved.

    NGramsNV4000.cs(8,15): error CS1518: Expected class, delegate, enum, interface,

    or struct

    NGramsNV4000.cs(9,29): error CS1518: Expected class, delegate, enum, interface,

    or struct

    NGramsNV4000.cs(24,26): error CS1518: Expected class, delegate, enum, interface,

    or struct

    NGramsNV4000.cs(26,7): error CS1001: Identifier expected

    NGramsNV4000.cs(26,9): error CS1518: Expected class, delegate, enum, interface,

    or struct

    NGramsNV4000.cs(27,7): error CS1001: Identifier expected

    NGramsNV4000.cs(27,9): error CS1518: Expected class, delegate, enum, interface,

    or struct

    NGramsNV4000.cs(27,21): error CS1518: Expected class, delegate, enum, interface,

    or struct

    NGramsNV4000.cs(27,32): error CS1518: Expected class, delegate, enum, interface,

    or struct

    NGramsNV4000.cs(34,23): error CS1518: Expected class, delegate, enum, interface,

    or struct

    NGramsNV4000.cs(37,2): error CS1022: Type or namespace definition, or

    end-of-file expected

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Jeff Moden

    SSC Guru

    Points: 996689

    Alan.B (7/7/2016)


    Forgive my CLR noobness (I create about 1 CLR/year)...

    You're ahead of me, there. My record has been an average of about -1 per year. That's not a mistake. I've been replacing SQLCLR with T-SQL. 😀 The best replacement, so far, has been for SQLCLR generic audit triggers at work, which were crushing performance. It was taking 4 minutes to update just 4 columns of just 10,000 rows of some 100+ column tables (not my design, for sure). The rewrite got that down to less than 800ms (yeah, I know... I'm slipping :-D) on the widest of the tables.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Solomon Rutzky

    SSCoach

    Points: 16259

    Alan.B (7/7/2016)


    Ok Solomon, I tried to figure this out and failed. Forgive my CLR noobness (I create about 1 CLR/year) and I'm sure this is simple but I'm having a hard time compiling your code. I've been playing around with this on/off for the past week.

    I've been trying with the csc.exe in .NET 3.5 and 2.0 I've added (then removed when it did not help) this to the top using <namespace> statements...

    using System;

    using System.Data;

    using Microsoft.SqlServer.Server;

    I saved your code as NGramsNV4000.cs and have been attempting to compile the code using this command:

    C:\Windows\Microsoft.NET\Framework\v2.0.50727>csc.exe /target:library NGramsNV4000.cs

    Hey there, Alan. No need to apologize for anything.

    Is there a reason why you aren't using Visual Studio? It makes working with .NET soooo much easier. The "community" edition is free. I can't think of a reason to not use it. Please try that and let me know how it goes.

    Take care,

    Solomon...

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • Solomon Rutzky

    SSCoach

    Points: 16259

    Alan, if it helps, I just posted the full C# file on PasteBin at:

    SQLCLR UDF to parse a string into NGrams

    Take care,

    Solomon...

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • Joe Celko

    SSCertifiable

    Points: 5578

    -1 is a wonderful answer. Mixed systems (those with more than one language embedded in each other) are always in efficient and a nightmare for anyone to read or maintain. Converting datatypes alone between languages is awful, but perhaps the biggest hit is finding someone that reads and writes fluently in two or more programming languages (forget about natural languages, we are we are worse). Since 80 to 90% of the cost of a system is in maintaining it, all this does is add to the overhead and total expense of the system the highest level.

    Books in Celko Series for Morgan-Kaufmann Publishing
    Analytics and OLAP in SQL
    Data and Databases: Concepts in Practice
    Data, Measurements and Standards in SQL
    SQL for Smarties
    SQL Programming Style
    SQL Puzzles and Answers
    Thinking in Sets
    Trees and Hierarchies in SQL

  • Alan Burstein

    SSC Guru

    Points: 61079

    Solomon Rutzky (7/9/2016)


    Alan.B (7/7/2016)


    Ok Solomon, I tried to figure this out and failed. Forgive my CLR noobness (I create about 1 CLR/year) and I'm sure this is simple but I'm having a hard time compiling your code. I've been playing around with this on/off for the past week.

    I've been trying with the csc.exe in .NET 3.5 and 2.0 I've added (then removed when it did not help) this to the top using <namespace> statements...

    using System;

    using System.Data;

    using Microsoft.SqlServer.Server;

    I saved your code as NGramsNV4000.cs and have been attempting to compile the code using this command:

    C:\Windows\Microsoft.NET\Framework\v2.0.50727>csc.exe /target:library NGramsNV4000.cs

    Hey there, Alan. No need to apologize for anything.

    Is there a reason why you aren't using Visual Studio? It makes working with .NET soooo much easier. The "community" edition is free. I can't think of a reason to not use it. Please try that and let me know how it goes.

    Take care,

    Solomon...

    Hey Solomon - I just wanted to post a quick update. I recently got a new PC and have not installed Visual Studio yet. I installed VS 2012 and SSDT today and plan on revisiting this - this week. I appreciate your help so much sir. Thanks!

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Alan Burstein

    SSC Guru

    Points: 61079

    CELKO (7/12/2016)


    -1 is a wonderful answer.

    To what? Who are you trying to communicate with here?

    Mixed systems (those with more than one language embedded in each other) are always in efficient and a nightmare for anyone to read or maintain.

    First, with all due respect, this statement is absolute nonsense. Second, who are you trying to communicate with here? Me? Are you trying to discourage me from using a CLR? Re-read this thread, particularly my reply to Luis' question.

    ...but perhaps the biggest hit is finding someone that reads and writes fluently in two or more programming languages (forget about natural languages, we are we are worse).

    Also nonsense. Complete and absolute meaningless blather.

    Welcome back Joe. I missed you.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 15 posts - 1 through 15 (of 24 total)

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