March 4, 2008 at 2:52 am
well "overkill"
?????????????????
Whats the alternative then?
because alternative people over here are mentioning are not working for me.
"EXEC @sql;" doesnt work in a UDF and if put in a stored procedure, that stored procedure cannot be used as a derived column's formula?
what to do then?
please tell me.
March 4, 2008 at 4:55 am
IMO if you'd enabled database owner chaining, things might have worked from the start on.
Post your clr-code and you have a good enough chance Jeff or someone else comes up with a classic tsql solution serving you even better.
Don't show up with 'select *from tablea ' concepts,
but post your actual code !
btw I've had an instance go down because of huge memory consumption
by a single CLR-proc. ( http://qa.sqlservercentral.com/Forums/FindPost463562.aspx )
Most people seem to forget things could also be accomplished in a pre-CLR era. :w00t:
http://qa.sqlservercentral.com/Forums/Topic458140-386-1.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 4, 2008 at 6:12 am
Muhammad Furqan (3/4/2008)
well "overkill"?????????????????
Whats the alternative then?
because alternative people over here are mentioning are not working for me.
"EXEC @sql;" doesnt work in a UDF and if put in a stored procedure, that stored procedure cannot be used as a derived column's formula?
what to do then?
please tell me.
With as little information as what you provided, I did try to show you. Take a look at the URL in my signature line... provide some data that easy for us to load using that format and like ALZDBA said, you'd be amazed at what can be done using just plain ol' T-SQL and with good peformance, too.
Just as a side bar, Matt Miller and I have tested the heck out of various CLR vs T-SQL solutions... with the exception of some awesome RegEx replacement solutions that Matt came up with, the T-SQL solutions always beat the CLR solutions.
--Jeff Moden
March 4, 2008 at 10:05 am
The big question is - why do you want to do it in a User-defined Function to begin with? What are you using it for? What is the purpose for doing all this?
Knowing what the data looks like is important - please do follow up on Jeff's request for DDL and test data, etc.... But - knowing the "big picture" is also really important. Trying to shove this into a UDF is going to severely limit your options no matter what - so figuring out why you feel it's important will help us come up with something that works.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 5, 2008 at 3:51 am
Ok, here is the code below. Let me explain once again.
I have a bunch of tables in which data is populated daily, and data may vary, data may change for each "object_id".
What i wanted to do is to create a table in which i would insert
object_id,
previous_date,
currentt_date,
columnName
IF OBJECT_ID('TempDB..#T1','U') IS NOT NULL DROP TABLE #T1
create table #t1(object_id int,date smalldatetime,data1 int,data2 int)
IF OBJECT_ID('TempDB..#T2','U') IS NOT NULL DROP TABLE #T2
create table #t2(object_id int,date smalldatetime,data1 int,data2 int)
IF OBJECT_ID('TempDB..#changes','U') IS NOT NULL DROP TABLE #changes
create table #changes(object_id int,thetable varchar(25),previous_date smalldatetime,currentt_date smalldatetime,columnName varchar(25))
insert into #T1
select 1,'01/01/2008',1,1 union all
select 1,'01/02/2008',1,1 union all
select 1,'01/03/2008',3,1 union all
select 2,'01/01/2008',1,1 union all
select 2,'01/02/2008',2,1 union all
select 2,'01/03/2008',1,10 union all
select 3,'01/01/2008',1,1 union all
select 3,'01/02/2008',1,1 union all
select 4,'01/03/2008',1,1
insert into #T2
select 1,'01/01/2008',25,1 union all
select 1,'01/02/2008',25,144 union all
select 1,'01/03/2008',30,1 union all
select 2,'01/01/2008',1,1 union all
select 2,'01/02/2008',1,1 union all
select 2,'01/03/2008',1,1 union all
select 3,'01/01/2008',1,1 union all
select 3,'01/02/2008',55,1 union all
select 4,'01/03/2008',1,1
insert into #changes
select a.object_id,'#T1','01/01/2008','01/02/2008','data1' from (select object_id,data1 as data from #T1 where date='01/01/2008')as a,(select object_id,data1 as data from #T2 where date='01/02/2008')as b where a.object_id=b.object_id and not a.data=b.data union all
select a.object_id,'#T1','01/01/2008','01/02/2008','data2' from (select object_id,data2 as data from #T1 where date='01/01/2008')as a,(select object_id,data2 as data from #T2 where date='01/02/2008')as b where a.object_id=b.object_id and not a.data=b.data union all
select a.object_id,'#T2','01/01/2008','01/02/2008','data1' from (select object_id,data1 as data from #T2 where date='01/01/2008')as a,(select object_id,data1 as data from #T2 where date='01/02/2008')as b where a.object_id=b.object_id and not a.data=b.data union all
select a.object_id,'#T2','01/01/2008','01/02/2008','data2' from (select object_id,data2 as data from #T2 where date='01/01/2008')as a,(select object_id,data2 as data from #T2 where date='01/02/2008')as b where a.object_id=b.object_id and not a.data=b.data
Up to this point is fine, working fine. Next what i want are two derived columns, "Previous_Value" and "Current_Value" which are defined as.
Previous_Value:Showing the data in column "columnName" for "object_id" for "previous_date"
Currentt_Value:Showing the data in column "columnName" for "object_id" for "current_date"
So that i can get a table as shown in attached JPEG.
"Current_value" and "previous_value" are derived. But tables are not fixed, columns to be obtained are not fixed.
Any idea?
I am really grateful to have such a group of nice and really helpful friends at such an active community.
Thanks in advance.
March 5, 2008 at 5:09 am
just a first glimps for a simple start
This select shows the values from within the same table...
Concept may be a good start..
select T1.[object_id], T1.[date], T1.data1 as T1_Data1, T1.data2 as T1_Data2
, Tn.[date] as Tn_date, Tn.data1 as Tn_Data1, Tn.data2 as Tn_Data2
from #t1 T1
inner join (Select A.[object_id], A.[date], min(B.[date]) as Next_Date
from #t1 A
inner join #t1 B
on A.[object_id] = B.[object_id]
and A.[date] < B.[date]
group by A.[object_id], A.[date]
) TheNext
on T1.[object_id] = TheNext.[object_id]
and T1.[date] = TheNext.[date]
left join #t1 Tn
on Tn.[object_id] = T1.[object_id]
and Tn.[date] = TheNext.Next_Date
Where T1.data1 <> Tn.data1 -- can they contain nulls ?
or T1.data2 <> Tn.data2 -- can they contain nulls ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 6, 2008 at 5:15 am
Well, Good. That was a really good start.
This query can be used to observe values changed within a table.
Welllllllllllllll
My scenario is a little bit different. I need to put that in a tabular form, like the one as i showed in JPG attached. Please tell me if you guys are having any confusion of the scenario.
Thanks.
March 6, 2008 at 6:03 am
Heh... WELLLLLLLLLLL!!!!!!
I'm pretty sure all you need is one select per column per table. It there where some rhyme or reason to the table names you
wanted to attack, we could probably do this automatically by generating a little dynamic SQL to create a view for you. In
the mean time, "we don't need no stinkin' CLR" 😀 And I added a "Table_Name" column because it made sense to do so...
remove it if you want...
[font="Courier New"]SELECT y1.Object_ID,y2.Date AS [Current_Date], y1.Date AS Previous_Date,
        '#T1' AS TableName, 'Data1' AS ColumnName, y1.Data1 Previous_Value, y2.Data1 AS Current_Value
   FROM #T1 y1,
        #T1 y2
  WHERE y1.Object_ID = y2.Object_ID
    AND y1.Date+1 = y2.Date
    AND y1.Data1 <> y2.Data1
UNION ALL
 SELECT y1.Object_ID,y2.Date AS [Current_Date], y1.Date AS Previous_Date,
        '#T1' AS TableName, 'Data2' AS ColumnName, y1.Data2 Previous_Value, y2.Data2 AS Current_Value
   FROM #T1 y1,
        #T1 y2
  WHERE y1.Object_ID = y2.Object_ID
    AND y1.Date+1 = y2.Date
    AND y1.Data2 <> y2.Data2
UNION ALL
 SELECT y1.Object_ID,y2.Date AS [Current_Date], y1.Date AS Previous_Date,
        '#T2' AS TableName, 'Data1' AS ColumnName, y1.Data1 Previous_Value, y2.Data1 AS Current_Value
   FROM #T2 y1,
        #T2 y2
  WHERE y1.Object_ID = y2.Object_ID
    AND y1.Date+1 = y2.Date
    AND y1.Data1 <> y2.Data1
UNION ALL
 SELECT y1.Object_ID,y2.Date AS [Current_Date], y1.Date AS Previous_Date,
        '#T2' AS TableName, 'Data2' AS ColumnName, y1.Data2 Previous_Value, y2.Data2 AS Current_Value
   FROM #T2 y1,
        #T2 y2
  WHERE y1.Object_ID = y2.Object_ID
    AND y1.Date+1 = y2.Date
    AND y1.Data2 <> y2.Data2
ORDER BY TableName,ColumnName,[Current_Date][/font]
Do notice that all you had to do was study ALZDBA's code a bit and you probably would have come to the
same conclusion. 😉
--Jeff Moden
March 6, 2008 at 9:24 am
Indeed, if you know you always have to compare data that strictly
defers 1 day, use the join with y1.Date + 1 = y2.Date
and don't use the "dynamic"
inner join ( Select A.[object_id]
, A.[date]
, min(B.[date]) as Next_Date
from #t1 A
inner join #t1 B
on A.[object_id] = B.[object_id]
and A.[date] < B.[date]
group by A.[object_id]
, A.[date]
) TheNext
on T1.[object_id] = TheNext.[object_id]
and T1.[date] = TheNext.[date]
Johan
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 10, 2008 at 12:27 am
woww!
you guys are awesome.
This really puts me into thinking about some alternatives like creating views 🙂
There are a few restrictions. I don't know whether they are really some restrictions/problems or i am taking them like that.
Firstly. Columns are not just as Data1,Data2. They are just samples. In fact, i am having hundreds of columns in each of nearly eight tables. An audit of all those needs to be done, and why a fixed view cant work is that "columns keep on adding dynamically". However, one plus point is that number of tables keep fixed. ie. they are eight.
So, do you think the solution provided there can work for the scenario where there are columns being added dynamically??
Thanks in advance guys.
March 10, 2008 at 2:03 am
Executing it for 50 columns took me 2 minutes. You can estimate the time taken for say 1000 columns.
I cannot keep the user waiting for that long. Thats why i needed the system where columnName, tableName, and the dates between which the change occurred in a table, and then get values for those columns, dates and objects only.
Thats why, i do need a derived column that extracts the values of "those" columns from "those" tables!!!
I think scenario is little more clear now!!!
March 10, 2008 at 3:31 am
you could start with the using the new sql2005 except join
then unpivot the resultset
and then check for the differences.
remarks: datatypes will get converted to varchar(max)
(I did put max , but you know the impact 😉 so restrict it as much as you can)
IF OBJECT_ID('TempDB..#T1','U') IS NOT NULL DROP TABLE #T1
create table #t1(object_id int,date smalldatetime,data1 int,data2 int)
IF OBJECT_ID('TempDB..#T2','U') IS NOT NULL DROP TABLE #T2
create table #t2(object_id int,date smalldatetime,data1 int,data2 int)
IF OBJECT_ID('TempDB..#changes','U') IS NOT NULL DROP TABLE #changes
create table #changes(object_id int,thetable varchar(25),previous_date smalldatetime,currentt_date smalldatetime,columnName varchar(25))
insert into #T1
select 1,'01/01/2008',1,1 union all
select 1,'01/02/2008',1,1 union all
select 1,'01/03/2008',3,1 union all
select 2,'01/01/2008',1,1 union all
select 2,'01/02/2008',2,1 union all
select 2,'01/03/2008',1,10 union all
select 3,'01/01/2008',1,1 union all
select 3,'01/02/2008',1,1 union all
select 4,'01/03/2008',1,1
insert into #T2
select 1,'01/01/2008',25,1 union all
select 1,'01/02/2008',25,144 union all
select 1,'01/03/2008',30,1 union all
select 2,'01/01/2008',1,1 union all
select 2,'01/02/2008',1,1 union all
select 2,'01/03/2008',1,1 union all
select 3,'01/01/2008',1,1 union all
select 3,'01/02/2008',55,1 union all
select 4,'01/03/2008',1,1
IF OBJECT_ID('TempDB..#Tc','U') IS NOT NULL DROP TABLE #Tc
create table #Tc(SourceTable sysname not null, [object_id] int,date smalldatetime,data1 int,data2 int)
insert into #Tc
Select 'ChangedT1vsT2', *
from (Select T1.*
from #t1 T1
except (select * from #t2)
) ChangedRowsForT1
insert into #Tc
Select 'ChangedT2vsT1', *
from (Select T2.*
from #t2 T2
except (select * from #t1)
) ChangedRowsForT2
Declare @myUnpivot varchar(Max)
set @myUnpivot = ''
Select @myUnpivot = @myUnpivot
+ case when @myUnpivot = '' then ''
else ' Union All ' end
+ 'SELECT SourceTable, [object_id], [date], ''' + name +''' as TheColumn, convert(varchar(max), [' + name +'] ) as TheValue
FROM #Tc'
from tempdb.sys.columns
where [object_id] = OBJECT_ID('TempDB..#Tc','U')
and name not in ( 'SourceTable', 'object_id', 'date' )
print @myUnpivot
IF OBJECT_ID('TempDB..#T_unpvt','U') IS NOT NULL DROP TABLE #T_unpvt
create table #T_unpvt ( SourceTable sysname not null, [object_id] int,date smalldatetime,TheColumn varchar(128), TheValue varchar(max) )
--Unpivot the table.
exec ( 'insert into #T_unpvt ' + @myUnpivot )
Select *
from #T_unpvt
All you need to do now, is replace
Select * from #T_unpvt
by the propper change detection query :w00t:
Will this do the job for you ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 10, 2008 at 3:59 am
In fact, i am having hundreds of columns in each of nearly eight tables.
I'm all done taking pot shots at a problem that keeps changing... Please, clearly define the problem just once...
--Jeff Moden
March 10, 2008 at 11:03 pm
i am sorry for missing that point out that there are hundreds of columns. i just posted two Data Columns just for simplicity, but forgot to mention the number of columns i am having. Sorry about that sir!
March 11, 2008 at 10:54 pm
Guys!
Any idea?
Have i made the problem worse ?
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply