Forum Replies Created

Viewing 15 posts - 2,416 through 2,430 (of 2,462 total)

  • RE: Dynamic File Location

    Kirby1367 (8/3/2012)

    I have written a series of script that uses xp_cmdshell (I know the various views on this so don't worry). This is just a very small snippet but...

    "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

    August 3, 2012 at 4:32 pm

    #1521671

  • RE: index size for sql server 2008

    I agree with everyone re: "Why the big ol' index?"

    Do you have any idea how many reads this table gets vs writes?

    If the index is not used often...

    "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

    August 3, 2012 at 3:16 pm

    #1521649

  • RE: Addition Of Digits

    Here is my solution:

    DECLARE @i varchar(50)='122333444455555';

    WITH val(x,n) AS

    (

    SELECT LEFT(@i,LEN(@i)),'0'

    UNION ALL

    SELECT LEFT(x,LEN(x)-1),RIGHT(x,1) FROM val WHERE LEN(x)>0

    )

    SELECT SUM(CAST(n AS int)) FROM val

    What's cool is you can replace SELECT SUM(CAST(n...

    "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

    August 3, 2012 at 2:59 pm

    #1521642

  • RE: Addition Of Digits

    SomewhereSomehow (8/3/2012)

    Here is my solution

    declare @i int = 985;

    with nums(n) as(select n from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10))nums(n))

    select sum(convert(int,substring(convert(varchar(10),@i),n,1)))

    from nums where n <= len(convert(varchar(10),@i))

    This is good. A couple things to note:

    First, the...

    "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

    August 3, 2012 at 2:43 pm

    #1521641

  • RE: Script to convert MS Access queries to T-SQL

    james.massey (8/3/2012)

    Well, that's a shame. I'm pretty much restricted to operating this way for this need - but it matches up with the fact that I could find no...

    "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

    August 3, 2012 at 12:09 pm

    #1521571

  • RE: Applying join

    Kirby1367 (8/2/2012)

    Koen Verbeeck (8/2/2012)

    SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_NAME = 'Table A'

    INTERSECT

    SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_NAME = 'Table B';

    You can also use a INNER JOIN instead of INTERSECT to accomplish the...

    "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

    August 2, 2012 at 4:36 pm

    #1521181

  • RE: How to manage Transaction Log.

    GilaMonster (8/1/2012)

    Don't shrink the log to 0 (unless the next operation is going to be a grow)

    Growing the log is an expensive operation, the file has to be zeroed out...

    "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

    August 1, 2012 at 4:29 pm

    #1520589

  • RE: How to manage Transaction Log.

    sestell1 (8/1/2012)

    There shouldn't be any need to shrink the log file while doing the inserts. This just adds extra overhead as the log has to auto-grow back out as...

    "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

    August 1, 2012 at 4:10 pm

    #1520582

  • RE: trying to add alias for value generated by Where clause

    polkadot (7/31/2012)

    Unless I comment out the alias 'AS RunningTotal' the query fails, but I need an alias for the column the WHERE STATEMENT generates. Will someone please show me...

    "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

    August 1, 2012 at 3:57 pm

    #1520579

  • RE: How to manage Transaction Log.

    prasadau2006 (7/31/2012)

    Hi All,

    Is their a way we can manage Transaction log even before it fills up? I have some archive tables, my requirement is to add fields to this tables...

    "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

    August 1, 2012 at 1:53 pm

    #1520499

  • RE: Removing tags stored in a ntext field

    jshahan (7/25/2012)

    XMLSQLNinja, thank for your time and alternative approach. I'm afraid that the garbage in my data is not well formed and this method stumbles as a result as...

    "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

    July 25, 2012 at 2:27 pm

    #1517632

  • RE: Get running total in the following query

    Lynn Pettis (7/25/2012)

    Thoughts? It all depends on where you run the code. On my development VM the Quircky Update beats your rCTE every time. Using your 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

    July 25, 2012 at 2:03 pm

    #1517627

  • RE: Get running total in the following query

    Lynn Pettis (7/23/2012)

    Okay, now modify the rCTE to run against this dataset. It will contain 1,000,000 rows of data and potentially 10,000 unique cID's, not 1 (cID = 123)...

    "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

    July 25, 2012 at 1:23 pm

    #1517600

  • RE: Get running total in the following query

    Gosh, that's quite confrontational.

    My sincere apologies if I came off as confrontational; that was not my intent.

    I, too, get quite a bit of information on the Internet...

    "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

    July 25, 2012 at 1:16 pm

    #1517596

  • RE: Get running total in the following query

    Jeff Moden (7/23/2012)

    XMLSQLNinja (7/23/2012)

    ChrisM@home (7/20/2012)

    XMLSQLNinja (7/18/2012)

    ...

    There are other ways to do this but using recursion is generally the fastest.

    Recursion is the third fastest; CLR > "Quirky Update" > rCTE >...

    "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

    July 23, 2012 at 10:34 pm

    #1516583

Viewing 15 posts - 2,416 through 2,430 (of 2,462 total)