Forum Replies Created

Viewing 15 posts - 46 through 60 (of 2,458 total)

  • Reply To: working days

    That function looks like a horror movie and it's not even Halloween yet.  The function below, ifn_workdays, is what you need. It does not handle holidays. Here's an example and...

    "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

    October 3, 2019 at 5:32 pm

    #3685804

  • Reply To: How can I force a select statement NOT to use a specific index ?

    It would be cool if there were a WITHOUT keyword.

    ...

    FROM <table> WITHOUT (INDEX(<index(s) you dont want to use>))
    "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

    October 3, 2019 at 3:03 pm

    #3685742

  • Reply To: Tsql/SP Code Optimization

    Redgate SQL Prompt is useful for this kind of thing. It's not as intuitive as you would describe but it will help you identify unused variables, parameters and columns, which...

    "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

    September 17, 2019 at 11:13 pm

    #3680889

  • Reply To: select several lines randomly according to several categories

    And thanks to your lack of netiquette, we have no DDL.

    I think what I posted should answer the OP's question as well as yours Joe.

     

    "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

    September 16, 2019 at 7:01 pm

    #3680461

  • Reply To: select several lines randomly according to several categories

    You can use TOP and ORDER BY NEWID() like this:

    -- Sample data
    DECLARE @yourdata TABLE 
    (
    Family INT,
    Room INT,
    subfamily INT
    );
    INSERT @yourdata VALUES (1,5,6),(2,5,7),(3,5,7),(4,2,3),(4,2,4);

    -- Number of Rows 
    DECLARE @rows INT...
    "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

    September 16, 2019 at 4:19 pm

    #3680412

  • Reply To: The Scientific Method: a call to action

    . This particular article, well I regret reading and discussing it. Yet again, SSC disappoints.

    I would ask for your money back.

    "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

    September 12, 2019 at 5:37 pm

    #3679625

  • Reply To: SSRS Interview

    As already mentioned - you don't want to pass other people's experience off as your own. I would never hire someone who did this nor would I respect someone who...

    "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

    September 10, 2019 at 8:47 pm

    #3678912

  • Reply To: Are the posted questions getting worse?

    Neil Burton wrote:

    And they've changed their minds.

    "UK to stay in Interrail scheme after U-turn"

    ba dum chhh

    "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 8, 2019 at 9:42 pm

    #3670068

  • Reply To: week startdate and enddate

    Speedy Pletcher beat me to it... More details about the requirement are in order. Here's my solution:

    DECLARE @startDate DATE = '20181230', @endDate DATE = '20191230';

    WITH 
    E1 AS...

    • This reply was modified 6 years, 10 months ago by Alan Burstein. Reason: Forgot ORDER BY

    "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 30, 2019 at 5:26 pm

    #3667295

  • Reply To: Is there CLEAN function, or similar, similar to the Excel function>

    PatExclude or PatReplace can do the trick.

    For example, this will remove any characters that are not alphanumeric while preserving spaces (note the space after the "Z"):

    "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 22, 2019 at 9:59 pm

    #3665222

  • Reply To: Is there any justification for really using SQL CLR

    Old thread, I know but there is something that has not been discussed here that I think is super important. Correctly designed CLR scalar UDFs don't have the problems that...

    • This reply was modified 6 years, 10 months ago by Alan Burstein.

    "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 22, 2019 at 9:39 pm

    #3665220

  • Reply To: The "Numbers" or "Tally" Table: What it is and how it replaces a loop

    You forgot to change the WHERE to ON for the INNER JOIN

    Good catch John, thanks.

    Does anyone know why you shouldn't use CROSS APPLY for everything? Or does it sometimes come...

    "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 14, 2019 at 1:23 am

    #3662718

  • Reply To: The "Numbers" or "Tally" Table: What it is and how it replaces a loop

    Emph mine...

    ANSI-89 style may have been a standard back in the day, but has long been supplemented. In fact it returns incomplete sets. There used to be a page in...

    • This reply was modified 6 years, 10 months ago by Alan Burstein.
    • This reply was modified 6 years, 10 months ago by Alan Burstein. Reason: Changed WHERE to ON for INNER JOIN

    "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 13, 2019 at 7:25 pm

    #3662692

  • Reply To: ssms crashes when editing .sql script

    I have this type of problem when I have too many SQL windows open or too much stuff running on my PC. The problem persists, often, even after closing 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

    April 18, 2019 at 8:41 pm

    #3456855

  • Reply To: Are the posted questions getting worse?

    My $0.02 about the new site...

    First, I miss the count of likes (for threads and individual comments). No nobody knows if you "liked" a thread or comment.

    As someone with articles...

    "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

    April 10, 2019 at 9:15 pm

    #3099890

    • Viewing 15 posts - 46 through 60 (of 2,458 total)