Click here to Skip to main content
15,509,706 members
Home / Discussions / Database
   

Database

 
AnswerRe: Advice on Perfomance Turning of the Query Pin
Mycroft Holmes28-Oct-14 13:59
professionalMycroft Holmes28-Oct-14 13:59 
GeneralRe: Advice on Perfomance Turning of the Query Pin
Vimalsoft(Pty) Ltd4-Nov-14 22:56
professionalVimalsoft(Pty) Ltd4-Nov-14 22:56 
QuestionCombine results from 2 foxpro DBF files using union Pin
jkirkerx27-Oct-14 13:30
professionaljkirkerx27-Oct-14 13:30 
AnswerMakes no sense how it works Pin
jkirkerx27-Oct-14 14:01
professionaljkirkerx27-Oct-14 14:01 
QuestionSelect count on 2 DBF files Pin
jkirkerx27-Oct-14 12:11
professionaljkirkerx27-Oct-14 12:11 
GeneralRe: Select count on 2 DBF files Pin
PIEBALDconsult27-Oct-14 12:25
professionalPIEBALDconsult27-Oct-14 12:25 
GeneralRe: Select count on 2 DBF files Pin
jkirkerx27-Oct-14 13:23
professionaljkirkerx27-Oct-14 13:23 
QuestionRewrit a Scalar function to a table valued function Pin
Ambertje21-Oct-14 1:00
Ambertje21-Oct-14 1:00 
Hi everyone,

I have a view with multiple outer joins and a on a lot of the fields I'm invoking a scalard functions.

The result is a very poor performance. I've been browsing the internet quite some time now (also read the article: Using Table-Valued Functions in SQL Server) but I can't seem to manage it on my own.

I know the difference between SVF and TVF and that's why I want to use TVF.

This is the Scalard function I want to recreate into a Table Valued Function:

The Shipping unit of my articles must be divided by 100 except for articles < 99999 or article 4859696


USE [Staging]
GO

/****** Object:  UserDefinedFunction [dbo].[Devide_Shipping_Unit]    Script Date: 21/10/2014 11:46:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER FUNCTION [dbo].[Devide_Shipping_Unit]

(
		@ValueString nvarchar(8),
		@ArticleString nvarchar(8) 
)
RETURNS Int

AS
BEGIN

DECLARE @result  int	

	IF ISNUMERIC(@ValueString)<> 1 OR ISNUMERIC(@ArticleString)<> 1
		BEGIN 
			set @result  = 0
		END 
	ELSE
		BEGIN 
						
			if  Cast(@ArticleString AS int) <= 99999  or @ArticleString = '4859696' 
				set @result  = 1 --CAST(@ValueString AS  int) / 1000
			Else 
				set @result  =CAST(@ValueString AS  int) / 100
				
		END 
	return @result

	END
GO



Another scalar function is a conversion of dates to a numeric datatype:
ALTER FUNCTION [dbo].[CONVERT_DATE_TO_NUMERIC]
(
		@dateValueString nvarchar(10)
)
RETURNS Numeric(8, 0)
AS
BEGIN

DECLARE @result  Numeric(8, 0)

		
	--DECLARE @dateValueString nvarchar(10)  = '03/12/2013'
	DECLARE @startdate nvarchar(10)  = ''

--		set @dateValueString = '2013-07-23'
	set @dateValueString = REPLACE(@dateValueString,'/','')
	set @dateValueString = REPLACE(@dateValueString,'-','')

	declare  @_year nvarchar(4) =  LEFT(@dateValueString,4)
	declare  @_month nvarchar(2) =  SUBSTRING(@dateValueString,5,2)
	declare  @_day nvarchar(2) =  RIGHT(@dateValueString,2)


	set @startdate = @_year + @_month + @_day

	IF ISNUMERIC(@startdate)<> 1
		BEGIN 
			set @result  = 0
		END 
	ELSE
		BEGIN 
			set @result  = @startdate 		END 


	return @result

	END


GO


Can anybody please help me I'm stuck Sigh | :sigh:
Ambertje
AnswerRe: Rewrit a Scalar function to a table valued function Pin
Mycroft Holmes21-Oct-14 1:25
professionalMycroft Holmes21-Oct-14 1:25 
GeneralRe: Rewrit a Scalar function to a table valued function Pin
Ambertje21-Oct-14 2:40
Ambertje21-Oct-14 2:40 
GeneralRe: Rewrit a Scalar function to a table valued function Pin
Richard Deeming21-Oct-14 3:00
mveRichard Deeming21-Oct-14 3:00 
GeneralRe: Rewrit a Scalar function to a table valued function Pin
Ambertje21-Oct-14 3:25
Ambertje21-Oct-14 3:25 
GeneralRe: Rewrit a Scalar function to a table valued function Pin
Mycroft Holmes21-Oct-14 13:52
professionalMycroft Holmes21-Oct-14 13:52 
SuggestionRe: Rewrit a Scalar function to a table valued function Pin
Richard Deeming21-Oct-14 2:55
mveRichard Deeming21-Oct-14 2:55 
AnswerRe: Rewrit a Scalar function to a table valued function Pin
Eddy Vluggen21-Oct-14 3:33
professionalEddy Vluggen21-Oct-14 3:33 
QuestionStored Procedure for Item Allotment Pin
avisharma@sharma19-Oct-14 21:01
avisharma@sharma19-Oct-14 21:01 
QuestionRe: Stored Procedure for Item Allotment Pin
Jörgen Andersson19-Oct-14 22:22
professionalJörgen Andersson19-Oct-14 22:22 
AnswerRe: Stored Procedure for Item Allotment Pin
avisharma@sharma20-Oct-14 0:04
avisharma@sharma20-Oct-14 0:04 
GeneralRe: Stored Procedure for Item Allotment Pin
Shweta N Mishra20-Oct-14 3:24
professionalShweta N Mishra20-Oct-14 3:24 
GeneralRe: Stored Procedure for Item Allotment Pin
avisharma@sharma27-Oct-14 3:42
avisharma@sharma27-Oct-14 3:42 
GeneralRe: Stored Procedure for Item Allotment Pin
Shweta N Mishra27-Oct-14 4:37
professionalShweta N Mishra27-Oct-14 4:37 
GeneralRe: Stored Procedure for Item Allotment Pin
Jörgen Andersson20-Oct-14 5:53
professionalJörgen Andersson20-Oct-14 5:53 
QuestionCheck if email for each specific recipient was sent or not sql server 2008 Pin
bjay tiamsic19-Oct-14 1:25
bjay tiamsic19-Oct-14 1:25 
AnswerRe: Check if email for each specific recipient was sent or not sql server 2008 Pin
Wendelius19-Oct-14 1:41
mveWendelius19-Oct-14 1:41 
GeneralRe: Check if email for each specific recipient was sent or not sql server 2008 Pin
bjay tiamsic19-Oct-14 18:58
bjay tiamsic19-Oct-14 18:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.