Prev: setting text result set more then 8192
Next: Best way to store vectors?
From: SqlBeginner on 9 Jul 2010 11:12
Thanks for the suggestion Eric. I am using SQL 2008. Would With SchemaBinding
help in this version as well?

Regards
pradeep

"Eric Isaacs" wrote:

> I think that the use of the string parsing function is good, but
> depending on which version of SQL Server you're using, since this
> isn't touching any tables, you'll get better performance (at least in
> SQL Server 2005) if you use WITH SCHEMABINDING in your function
> declaration.
>
> > CREATE FUNCTION [dbo].[fnStringmanipulation]
> > (
> > @Source varchar(2048),
> > @Begin varchar(512),
> > @End varchar(512),
> > @SIndex int = 1,
> > @SBegin char(1) = 'N'
> > )
> > RETURNS varchar(2048)
> WITH SCHEMABINDING ---ADD THIS LINE
> > AS
> > BEGIN
> ....
>
> Yes, you can have multiple insert statements in a trigger. I was
> saying that you could technically do this in an instead of trigger
> without actually inserting any data in any tables except for the
> destination tables using an INSTEAD OF INSERT trigger. If you just
> use an insert trigger, you can still execute other insert statements
> besides what is going into the table.
>
> I think having the data go into the tblDest table is a sound idea for
> debugging and testing. Since there are 50K rows per day of data
> though, doing it in an instead of trigger makes sense, because if
> you're not testing, you really don't need those rows to go into either
> of those tables.
>
> Assuming that you know all the divisions and where the data will be
> going, you can do multiple inserts in the trigger to each one of the
> division tables. If you add a division, you'll need to change your
> trigger, however. You might be able to work around that issue with
> dynamic sql in the trigger, but that gets even more messy, but still
> technically feasible.
>
> -Eric Isaacs
>
>
>
>
>
> .
>
From: Eric Isaacs on 9 Jul 2010 23:29
From what I know, it's still the case with SQL Server 2008 as well.
It certainly doesn't hurt to add WITH SCHEMABINDING to a UDF that
doesn't reference any SQL Server objects. It also makes it possible
for those objects to be called from other schema-bound objects or
indexed views.

http://www.mssqltips.com/tip.asp?tip=1692

-Eric Isaacs
First  |  Prev  | 
Pages: 1 2
Prev: setting text result set more then 8192
Next: Best way to store vectors?