May 3, 2010 at 2:32 pm
Paul White NZ (5/1/2010)
Garadin (5/1/2010)
Also, even though you have the overhead of a scalar UDF that accesses data, due to the pattern that I'm imagining this would take (Passing just the table name and column name to the function and getting back a value), that value would be cached in memory after the first hit and subsequent hits should be much faster (That's my thought anyways, correct me if I'm wrong on that).
I'm not quite sure I see how this function would work, since dynamic SQL is not allowed in a function.
Fleshed out example of what I was referring to:
CREATE FUNCTION dbo.SCA_DefVal(
@TableNamevarchar(50),
@ColNamevarchar(50)
)
RETURNS sql_variant
AS
BEGIN
DECLARE @DV sql_variant
SELECT @DV = VALUE
FROM DefValuesTable
WHERE TableName = @TableName
AND ColName = @ColName
RETURN @DV
END
GO
CREATE TABLE DataTable1(
ColAvarchar(10)DEFAULT CONVERT(varchar(10),dbo.SCA_DefVal('DataTable1','ColA')) NOT NULL,
ColBvarchar(10) DEFAULT CONVERT(varchar(10),dbo.SCA_DefVal('DataTable1','ColB')) NOT NULL
)
GO
CREATE TABLE DataTable2(
ColAvarchar(10) DEFAULT CONVERT(varchar(10),dbo.SCA_DefVal('DataTable2','ColA')) NOT NULL,
ColBvarchar(10) DEFAULT CONVERT(varchar(10),dbo.SCA_DefVal('DataTable2','ColB')) NOT NULL
)
GO
CREATE TABLE DefValuesTable(
TableNamevarchar(50),
ColNamevarchar(50),
VALUEsql_variant
)
GO
INSERT INTO DefValuesTable(TableName, ColName, Value)
VALUES('DataTable1','ColA','T1CA'),
('DataTable1','ColB','T1CB'),
('DataTable2','ColA','T2CA'),
('DataTable2','ColB','T2CB')
GO
INSERT INTO DataTable1(ColA)
VALUES ('TestVal')
INSERT INTO DataTable2(ColB)
VALUES ('TestVal')
SELECT * FROM DataTable1
SELECT * FROM DataTable2
DROP TABLE DataTable1
DROP TABLE DataTable2
DROP TABLE DefValuesTable
DROP FUNCTION dbo.SCA_DefVal
May 3, 2010 at 8:05 pm
Goldie Graber (5/3/2010)
1. Impact. How much would insert speed slow down?
Very little. Run some tests.
2. Complexity. As mentioned before, the trigger logic would be complex. This would probably impact speed as well.
Use Peter's streamlined version in this post: http://www.sqlservercentral.com/Forums/FindPost913674.aspx
It's a single statement, regardless of the number of columns with defaults. Couldn't be easier or less complex.
3. Maintainability. If I start down this path I will have a numerous triggers in the database. I generally try to avoid triggers because they become hard to maintain, and are easily lost.
Generally try to avoid - sure. But you have an unusual requirement that is best implemented with an INSTEAD OF trigger. "Generally try to avoid" does not mean "never". Nothing in your production databases should be "easily lost"! Do you lose procedures and functions? 😉
I also have a question about how you implemented the trigger. Why did you hard-code the column default in the second version that you posted? To me that seems to miss the point of not hard-coding the values in the stored procedure.
You are referring to the wrong code. See Peter's version of my trigger (post link above).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 3, 2010 at 8:09 pm
Garadin (5/3/2010)
Fleshed out example of what I was referring to:
Thanks for the code Seth! That explained it nicely.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 5, 2010 at 6:58 am
Hello,
You have to write cursor here to update your lunchperiod data in class table from student table. First of all insert what ever data you have in class table. Then write cursor and take those class id where lunchperiod column is null in class table. Update those null values according to match from student table.
Thanks
Rajneesh
May 5, 2010 at 7:40 am
rajn.knit07 (5/5/2010)
Hello,You have to write cursor here to update your lunchperiod data in class table from student table. First of all insert what ever data you have in class table. Then write cursor and take those class id where lunchperiod column is null in class table. Update those null values according to match from student table.
Thanks
Rajneesh
I think the previous 30 responses to this topic provide proof that you do not have to do anything of the sort.
May 5, 2010 at 7:56 am
Paul & Seth -- Thanks so much for your help!
May 5, 2010 at 8:59 am
Garadin (5/5/2010)
rajn.knit07 (5/5/2010)
Hello,You have to write cursor here to update your lunchperiod data in class table from student table. First of all insert what ever data you have in class table. Then write cursor and take those class id where lunchperiod column is null in class table. Update those null values according to match from student table.
Thanks
Rajneesh
I think the previous 30 responses to this topic provide proof that you do not have to do anything of the sort.
Nicely put, Seth 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply