Function test questionnaire help

  • frdrckmitchell7

    Ten Centuries

    Points: 1337

    qq

    Hi clever people!

    The following is part of a TEST QUESTIONNAIRE. Although in the following two part question it

    seems silly to create a function and there are other, better ways to do this the test questionnaire

    is about CREATING A FUNCTION. Please bear that in mind.

    The following are the two tables followed by the two questions the test poses:

    CREATE TABLE TestTable1

    (

    ID INT,

    Name VARCHAR(255)

    )

    INSERT INTO TestTable1

    VALUES(1, 'Test1'),

    (2, 'Test2'),

    (3, 'Test3'),

    (4, 'Test4'),

    (5, 'Test5'),

    (6, 'Test6')

    CREATE TABLE TestTable2

    (

    ID INT,

    JobDescription VARCHAR(255),

    [Hours] INT

    )

    INSERT INTO TestTable2

    VALUES (1, 'Developer', 10),

    (2, 'Web Developer', 8),

    (3, 'Architect', 12)

    Here are the questions:

    a) Create A FUNCTION that takes in a name as parameter and returns the ID for the

    corresponding name from TestTable1.

    (For the sake of the test the function is called TestTableFunction but feel free to use your own if you want)

    b) How would yo optimize the following query taking into account that any text could be passed

    as a parameter to the function created above, not just 'Test2'?

    SELECT *

    FROM TestTable1 a

    JOIN TestTable2 b

    ON b.ID = a.ID

    WHERE a.ID = TestTableFunction('Test2')

    I'm looking for answers that are better than mine so I'm asking you clever people for help.

    Kind regards

  • Mr. Brian Gale

    SSC-Insane

    Points: 23075

    You say you have tried things and are looking for answers that are "better than" yours.  To avoid us posting identical things than what you already have, could you post the answers you came up with?

    To me, the two questions sound fairly easy.  For the first one, the function takes a single parameter and returns a single int.  I would do that with just a simple select and a where from the table.  Nothing complicated there.

    As for number 2, an index could help, removing the function could help, indexes could help (if not already present), selecting only the columns you care about could help, MIGHT get a better execution plan if you change the function lookup in the WHERE clause into a variable lookup...  I personally would start by running the query and checking the execution plan; then I'd look at ways to rewrite the query to improve the execution plan and test them out.

  • mikeroy

    Newbie

    Points: 1

    Mr. Brian Gale wrote:

    You say you have tried things and are looking for answers that are "better than" yours.  To avoid us posting identical things than what you already have, could you post the answers you came up with?

    To me, the two questions sound fairly easy.  For the first one, the function takes a single parameter and returns a single int.  I would do that with just a simple select and a where from the table.  Nothing complicated there.

    As for number 2, an index could help, removing the function could help, indexes could help (if not already present), selecting only the columns you care about could help, MIGHT get a better execution plan if you change the function lookup in the WHERE clause into a variable lookup...  I personally would start by running the query and checking the execution plan; then I'd look at ways to rewrite the query to improve the execution plan and test them out.

    This helped me save time

  • Chris Harshman

    SSC-Forever

    Points: 42146

    You still never gave us what your answer was...

Viewing 4 posts - 1 through 4 (of 4 total)

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