Resaving / Rebuilding User Functions

I have about 10 User Defined Functions that all use another Function that we
can call funct1. These Functions in turn are used by around 50 Stored
Procedures. I recently made a change to funct1 to return an extra field and
it broke many of the stored procs and functions that called it. If I resaved
the functions then they started working again, even though no change was
made. I suppose they had cached the fields they were expecting from funct1
and needed to "rebuild" the list.
Is there some type of dbcc command that would automatically force everything
to be "rebuilt" or "resaved"? I tried DBCC FREEPROCCACHE but that evidently
only empties the execution plans.You can use sp_recompile to force a recompilation of the stored procedure on
next execution.
-oj
"Greg Steele" <Greg Steele@.discussions.microsoft.com> wrote in message
news:DEDFA89D-5592-42F3-A8F8-F40CFEE20919@.microsoft.com...
>I have about 10 User Defined Functions that all use another Function that
>we
> can call funct1. These Functions in turn are used by around 50 Stored
> Procedures. I recently made a change to funct1 to return an extra field
> and
> it broke many of the stored procs and functions that called it. If I
> resaved
> the functions then they started working again, even though no change was
> made. I suppose they had cached the fields they were expecting from funct1
> and needed to "rebuild" the list.
> Is there some type of dbcc command that would automatically force
> everything
> to be "rebuilt" or "resaved"? I tried DBCC FREEPROCCACHE but that
> evidently
> only empties the execution plans.|||Also, make sure that you are not using "SELECT *" in your functions,
because bad things may happen, for example:
USE tempdb
GO
CREATE FUNCTION dbo.FirstFunction()
RETURNS TABLE AS RETURN
SELECT 1 A, 2 B
GO
CREATE FUNCTION dbo.SecondFunction()
RETURNS TABLE AS RETURN
SELECT * FROM dbo.FirstFunction()
GO
CREATE PROCEDURE Procedure1
AS
SELECT A, B FROM dbo.SecondFunction()
GO
EXEC Procedure1
GO
ALTER FUNCTION dbo.FirstFunction()
RETURNS TABLE AS RETURN
SELECT 1 A, 3 C, 2 B
GO
EXEC Procedure1
EXEC sp_recompile 'Procedure1'
EXEC Procedure1
EXEC sp_recompile 'SecondFunction'
EXEC Procedure1
GO
ALTER FUNCTION dbo.SecondFunction()
RETURNS TABLE AS RETURN
SELECT * FROM dbo.FirstFunction()
GO
EXEC Procedure1
GO
DROP FUNCTION FirstFunction, SecondFunction
DROP PROCEDURE Procedure1
As you can see from the above example, if you are using "SELECT *" (in
a view or in-line function) and you add a new column before another
column (or change the order of the columns), this messes-up things
pretty badly: it returns data from the wrong columns; this cannot be
fixed by using sp_recompile, only by altering the UDF that contains
"SELECT *".
Razvan

Request: Help creating a difficult view.

Hello all.

I have a table defined in sql server as follows:

ROW_ID (identity)
DEPTH_FROM Number (8,3)
DEPTH_TO Number (8,3)
COLOUR Char(10)

With typical data like:
ROW_ID DEPTH_FROM DEPTH_TO COLOUR
-------------------
1 0 5
BLUE
2 5 8
BLUE
3 8 10
RED
4 10 12
GREEN
5 12 16
GREEN

I want to create a view that will 'compress/roll up' the data so
it appears like:

DEPTH_FROM DEPTH_TO COLOUR
------------------
0 8 BLUE
8 10 RED
10 16 GREEN

I have been working on this for several days, with no luck,
any help would be appreciated. BTW: there are no overlaps
allowed in the depth_from, depth_to values.

Thanks in advance.I'll assume that the colours don't always occur at consecutive depths
otherwise you could just do this:

SELECT MIN(depth_from), MAX(depth_to), colour
FROM ColDepths
GROUP BY colour

Here's the (assumed) DDL and sample data. It helps if you include this with
posts.

CREATE TABLE ColDepths (row_id INTEGER NOT NULL UNIQUE, depth_from INTEGER
NOT NULL, depth_to INTEGER NOT NULL, colour CHAR(10) NOT NULL,
CHECK(depth_from<depth_to), PRIMARY KEY (depth_from,depth_to))

INSERT INTO ColDepths VALUES (1,0,5, 'BLUE')
INSERT INTO ColDepths VALUES (2,5,8, 'BLUE')
INSERT INTO ColDepths VALUES (3,8,10, 'RED')
INSERT INTO ColDepths VALUES (4,10,12, 'GREEN')
INSERT INTO ColDepths VALUES (5,12,16, 'GREEN')

Here's my query.

SELECT MIN(A.depth_from) AS depth_from,
MAX(A.depth_to) AS depth_to, A.colour
FROM ColDepths AS A
JOIN
(SELECT c1.row_id, MIN(C2.depth_to) AS next_depth
FROM ColDepths AS C1
LEFT JOIN ColDepths AS C2
ON C1.colour <> C2.colour
AND (C1.depth_from < C2.depth_from
OR (C1.depth_from = C2.depth_from)
AND C1.depth_to <= C2.depth_to)
GROUP BY c1.row_id) AS B
ON A.row_id = B.row_id
GROUP BY A.colour, B.next_depth

--
David Portas
----
Please reply only to the newsgroup
--|||Is this what you have in mind?

create table foo
(ROW_ID int, /* my datatypes vary from yours for my convenience
*/
DEPTH_FROM int,
DEPTH_TO int,
COLOUR Char(10))
go
insert foo values (1,0,5,'blue')
insert foo values (2,5,8,'blue')
insert foo values (3,8,10,'red')
insert foo values (4,10,12,'green')
insert foo values (5,12,16,'green')
go
select min(depth_from) as depth_from, max(depth_to) as depth_to, colour
from foo
group by colour

depth_from depth_to colour
---- ---- ----
0 8 blue
10 16 green
8 10 red

(3 row(s) affected)

You may want an ORDER BY clause, too. Order of rows returned with GROUP BY
is not guaranteed/predictable.

"Dave Pylatuk" <davep@.centurysystems.net> wrote in message
news:pZgcb.4184$1H3.311803@.news20.bellglobal.com.. .
> Hello all.
> I have a table defined in sql server as follows:
> ROW_ID (identity)
> DEPTH_FROM Number (8,3)
> DEPTH_TO Number (8,3)
> COLOUR Char(10)
> With typical data like:
> ROW_ID DEPTH_FROM DEPTH_TO COLOUR
> -------------------
> 1 0 5
> BLUE
> 2 5 8
> BLUE
> 3 8 10
> RED
> 4 10 12
> GREEN
> 5 12 16
> GREEN
> I want to create a view that will 'compress/roll up' the data so
> it appears like:
> DEPTH_FROM DEPTH_TO COLOUR
> ------------------
> 0 8 BLUE
> 8 10 RED
> 10 16 GREEN
> I have been working on this for several days, with no luck,
> any help would be appreciated. BTW: there are no overlaps
> allowed in the depth_from, depth_to values.
> Thanks in advance.|||"Dave Pylatuk" <davep@.centurysystems.net> wrote...

> I want to create a view that will 'compress/roll up' the data so
> it appears like:
> DEPTH_FROM DEPTH_TO COLOUR
> ------------------
> 0 8 BLUE
> 8 10 RED
> 10 16 GREEN

I don't use SqlServer but... wouldn't this work?

select min(depth_from), max(depth_to), colour from <tablename> group by
colour|||Testing these suggestions right now, thanks to all

"Dave Pylatuk" <davep@.centurysystems.net> wrote in message
news:pZgcb.4184$1H3.311803@.news20.bellglobal.com.. .
> Hello all.
> I have a table defined in sql server as follows:
> ROW_ID (identity)
> DEPTH_FROM Number (8,3)
> DEPTH_TO Number (8,3)
> COLOUR Char(10)
> With typical data like:
> ROW_ID DEPTH_FROM DEPTH_TO COLOUR
> -------------------
> 1 0 5
> BLUE
> 2 5 8
> BLUE
> 3 8 10
> RED
> 4 10 12
> GREEN
> 5 12 16
> GREEN
> I want to create a view that will 'compress/roll up' the data so
> it appears like:
> DEPTH_FROM DEPTH_TO COLOUR
> ------------------
> 0 8 BLUE
> 8 10 RED
> 10 16 GREEN
> I have been working on this for several days, with no luck,
> any help would be appreciated. BTW: there are no overlaps
> allowed in the depth_from, depth_to values.
> Thanks in advance.|||Hi Dave,

If there is no overlap but there can be gaps between intervals, this is the
query you want:

select DEPTH_FROM,
DEPTH_TO = (select min(DEPTH_TO)
from T T3
where DEPTH_TO not in (select DEPTH_FROM
from T T4
where T3.COLOUR = T4.COLOUR
and T3.DEPTH_FROM <>
T4.DEPTH_FROM
)
and T3.DEPTH_TO > T1.DEPTH_FROM
),
COLOUR
from T T1
where DEPTH_FROM not in (select DEPTH_TO
from T T2
where T1.COLOUR = T2.COLOUR
and T1.DEPTH_FROM <> T2.DEPTH_FROM
)
order by DEPTH_FROM

Good Luck,
Shervin

"Dave Pylatuk" <davep@.centurysystems.net> wrote in message
news:pZgcb.4184$1H3.311803@.news20.bellglobal.com.. .
> Hello all.
> I have a table defined in sql server as follows:
> ROW_ID (identity)
> DEPTH_FROM Number (8,3)
> DEPTH_TO Number (8,3)
> COLOUR Char(10)
> With typical data like:
> ROW_ID DEPTH_FROM DEPTH_TO COLOUR
> -------------------
> 1 0 5
> BLUE
> 2 5 8
> BLUE
> 3 8 10
> RED
> 4 10 12
> GREEN
> 5 12 16
> GREEN
> I want to create a view that will 'compress/roll up' the data so
> it appears like:
> DEPTH_FROM DEPTH_TO COLOUR
> ------------------
> 0 8 BLUE
> 8 10 RED
> 10 16 GREEN
> I have been working on this for several days, with no luck,
> any help would be appreciated. BTW: there are no overlaps
> allowed in the depth_from, depth_to values.
> Thanks in advance.|||"Dave Pylatuk" <davep@.centurysystems.net> wrote in message
news:pZgcb.4184$1H3.311803@.news20.bellglobal.com.. .
> Hello all.
> I have a table defined in sql server as follows:
> ROW_ID (identity)
> DEPTH_FROM Number (8,3)
> DEPTH_TO Number (8,3)
> COLOUR Char(10)
> With typical data like:
> ROW_ID DEPTH_FROM DEPTH_TO COLOUR
> -------------------
> 1 0 5
> BLUE
> 2 5 8
> BLUE
> 3 8 10
> RED
> 4 10 12
> GREEN
> 5 12 16
> GREEN
> I want to create a view that will 'compress/roll up' the data so
> it appears like:
> DEPTH_FROM DEPTH_TO COLOUR
> ------------------
> 0 8 BLUE
> 8 10 RED
> 10 16 GREEN
> I have been working on this for several days, with no luck,
> any help would be appreciated. BTW: there are no overlaps
> allowed in the depth_from, depth_to values.
> Thanks in advance.

This will also handle gaps between consecutive depth intervals.

CREATE TABLE ColorDepths
(
depth_from INT NOT NULL PRIMARY KEY,
depth_to INT NOT NULL,
color CHAR(10) NOT NULL,
CHECK (depth_from <= depth_to)
)

-- Your sample data augmented to better exercise code
INSERT INTO ColorDepths (depth_from, depth_to, color)
VALUES (0,5, 'BLUE')
INSERT INTO ColorDepths (depth_from, depth_to, color)
VALUES (5,8, 'BLUE')
INSERT INTO ColorDepths (depth_from, depth_to, color)
VALUES (8,10, 'RED')
INSERT INTO ColorDepths (depth_from, depth_to, color)
VALUES (11,12, 'GREEN')
INSERT INTO ColorDepths (depth_from, depth_to, color)
VALUES (12,15, 'GREEN')
INSERT INTO ColorDepths (depth_from, depth_to, color)
VALUES (16, 18, 'BLUE')
INSERT INTO ColorDepths (depth_from, depth_to, color)
VALUES (18, 24, 'BLUE')
INSERT INTO ColorDepths (depth_from, depth_to, color)
VALUES (26, 30, 'BLUE')

-- Associate consecutive depth intervals with natural numbers
CREATE VIEW OrderedColorDepths (depth_from, depth_to, color, seq)
AS
SELECT D1.depth_from, D1.depth_to, D1.color, COUNT(*)
FROM ColorDepths AS D1
INNER JOIN
ColorDepths AS D2
ON D2.depth_from <= D1.depth_from
GROUP BY D1.depth_from, D1.depth_to, D1.color

-- Using above natural numbers, find endpoints
CREATE VIEW ColorDepthEnds (color, seq)
AS
SELECT OD1.color, OD1.seq
FROM OrderedColorDepths AS OD1
LEFT OUTER JOIN
OrderedColorDepths AS OD2
ON OD2.seq = OD1.seq + 1
WHERE OD2.color <> OD1.color OR -- consecutive depths w/ diff. colors
OD2.color IS NULL OR -- last (greatest) depth
OD2.depth_from > OD1.depth_to -- gap between consecutive depths

SELECT color,
MIN(depth_from) AS depth_from , MAX(depth_to) AS depth_to
FROM (SELECT OD.color, OD.depth_from, OD.depth_to,
MIN(DE.seq) AS seq
FROM OrderedColorDepths AS OD
INNER JOIN
ColorDepthEnds AS DE
ON DE.seq >= OD.seq AND
DE.color = OD.color
GROUP BY OD.depth_from, OD.depth_to, OD.color) AS R
GROUP BY seq, color
ORDER BY depth_from

color depth_from depth_to
BLUE 0 8
RED 8 10
GREEN 11 15
BLUE 16 24
BLUE 26 30

Regards,
jag

Re-Post Execution Plan

Hi,
Say if you had a store procedure called p_SelectData
defined as follows with the parameter @.WhereClause can by
set to either
"@.Where = ' Where Surname = ''Jones''...
or
@.Where = Where Surname = ''Jones'' and Forename ''Paul''.
or
@.Where = Where Surname = ''Jones'' and Forename ''Paul''
and HasDetails = 1
You get the idea, a dynamic where clause.
The SP is as follows
CREATE procedure dbo.p_SelectData
@.WhereClause varchar(100) as
DECLARE @.SQLString varchar(255)
SET @.SQLString = 'Select * From MockTable ' + @.WhereClause
EXECUTE ( @.SQLString)
My question is can a proper execution plan be formed
internally by SQL Server, or does the exec with the where
clause stop it from forming ?
N.B. No I didn't implement this it was there when I got
there.
Thanks.Hi,
Firstly, you will always get an execution plan, but I'm guessing you are asking if it will cache it and re-use it.
I would say that in your simple example, you will get an execution plan cached for the stored procedure. In complex stored procedures, it is possible that the execution plan will be re-compiled during execution, but you can read more about that in BOL.
As for the EXEC statment, I don't believe that the plan will be cached. The reason being that the table name is not qualified with the owner/schema. It is possible to have two tables with the same name but different owners, and data and/or indexing is different enough to result in a different execution plan. If the table name was fully qualified, that I feel that it would use auto-parameterization.
"Jimbo" wrote:
> Hi,
> Say if you had a store procedure called p_SelectData
> defined as follows with the parameter @.WhereClause can by
> set to either
> "@.Where = ' Where Surname = ''Jones''...
> or
> @.Where = Where Surname = ''Jones'' and Forename ''Paul''.
> or
> @.Where = Where Surname = ''Jones'' and Forename ''Paul''
> and HasDetails = 1
> You get the idea, a dynamic where clause.
> The SP is as follows
> CREATE procedure dbo.p_SelectData
> @.WhereClause varchar(100) as
> DECLARE @.SQLString varchar(255)
> SET @.SQLString = 'Select * From MockTable ' + @.WhereClause
> EXECUTE ( @.SQLString)
> My question is can a proper execution plan be formed
> internally by SQL Server, or does the exec with the where
> clause stop it from forming ?
> N.B. No I didn't implement this it was there when I got
> there.
> Thanks.
>
>|||Thanks Al, that was my thought on it as well, but I
thought I had better check my facts before continuing with
it.
>--Original Message--
>Hi,
>Firstly, you will always get an execution plan, but I'm
guessing you are asking if it will cache it and re-use it.
>I would say that in your simple example, you will get an
execution plan cached for the stored procedure. In complex
stored procedures, it is possible that the execution plan
will be re-compiled during execution, but you can read
more about that in BOL.
>As for the EXEC statment, I don't believe that the plan
will be cached. The reason being that the table name is
not qualified with the owner/schema. It is possible to
have two tables with the same name but different owners,
and data and/or indexing is different enough to result in
a different execution plan. If the table name was fully
qualified, that I feel that it would use auto-
parameterization.
>"Jimbo" wrote:
>> Hi,
>> Say if you had a store procedure called p_SelectData
>> defined as follows with the parameter @.WhereClause can
by
>> set to either
>> "@.Where = ' Where Surname = ''Jones''...
>> or
>> @.Where = Where Surname = ''Jones'' and
Forename ''Paul''.
>> or
>> @.Where = Where Surname = ''Jones'' and
Forename ''Paul''
>> and HasDetails = 1
>> You get the idea, a dynamic where clause.
>> The SP is as follows
>> CREATE procedure dbo.p_SelectData
>> @.WhereClause varchar(100) as
>> DECLARE @.SQLString varchar(255)
>> SET @.SQLString = 'Select * From MockTable ' +
@.WhereClause
>> EXECUTE ( @.SQLString)
>> My question is can a proper execution plan be formed
>> internally by SQL Server, or does the exec with the
where
>> clause stop it from forming ?
>> N.B. No I didn't implement this it was there when I got
>> there.
>> Thanks.
>>
>.
>|||If you want to do a bit of experimenting, have a look at the Stored Procedure Events of the Profiler. These can show individual statements within the Stored Procedure being executed and if there has been a Cache Hit or Cache Miss. That way it will be possible to see if the EXEC call goes to the Cache or not
"Jimbo" wrote:
> Thanks Al, that was my thought on it as well, but I
> thought I had better check my facts before continuing with
> it.
>
> >--Original Message--
> >Hi,
> >
> >Firstly, you will always get an execution plan, but I'm
> guessing you are asking if it will cache it and re-use it.
> >
> >I would say that in your simple example, you will get an
> execution plan cached for the stored procedure. In complex
> stored procedures, it is possible that the execution plan
> will be re-compiled during execution, but you can read
> more about that in BOL.
> >
> >As for the EXEC statment, I don't believe that the plan
> will be cached. The reason being that the table name is
> not qualified with the owner/schema. It is possible to
> have two tables with the same name but different owners,
> and data and/or indexing is different enough to result in
> a different execution plan. If the table name was fully
> qualified, that I feel that it would use auto-
> parameterization.
> >
> >"Jimbo" wrote:
> >
> >> Hi,
> >>
> >> Say if you had a store procedure called p_SelectData
> >> defined as follows with the parameter @.WhereClause can
> by
> >> set to either
> >>
> >> "@.Where = ' Where Surname = ''Jones''...
> >> or
> >> @.Where = Where Surname = ''Jones'' and
> Forename ''Paul''.
> >> or
> >> @.Where = Where Surname = ''Jones'' and
> Forename ''Paul''
> >> and HasDetails = 1
> >>
> >> You get the idea, a dynamic where clause.
> >>
> >> The SP is as follows
> >>
> >> CREATE procedure dbo.p_SelectData
> >> @.WhereClause varchar(100) as
> >> DECLARE @.SQLString varchar(255)
> >> SET @.SQLString = 'Select * From MockTable ' +
> @.WhereClause
> >> EXECUTE ( @.SQLString)
> >>
> >> My question is can a proper execution plan be formed
> >> internally by SQL Server, or does the exec with the
> where
> >> clause stop it from forming ?
> >>
> >> N.B. No I didn't implement this it was there when I got
> >> there.
> >>
> >> Thanks.
> >>
> >>
> >>
> >.
> >
>