All Questions

Filter by
Sorted by
Tagged with
0
votes
1answer
46 views

Refactor SQL to left join on TVF requiring input variable

I would like to join two tables and a table-valued function. The TVF take a date as input variable. The expected output is: Date, LimitName, Exposure, ProductValueEligible, ProductValueNonEligible ...
-3
votes
3answers
64 views

if statement in a query [closed]

I am getting an error while running this code: Incorrect syntax near the keyword 'IF' I need the output through function only with table return . CREATE FUNCTION [dbo].[...
2
votes
1answer
165 views

'Ungroup' data based on column

I'm using SQL Server 2012 and I have a table A that has the Id and PseudoId columns and a bunch of others I am not interested in. What I am interested in, is the PseudoId especially. This PseudoId has ...
2
votes
1answer
48 views

Query involving TVF very slow on SQL Server 2008

I have a query that runs in under 1 second on SQL Server 2014. A similar query takes over 12 minutes when run against a table on a linked server running SQL Server 2008. I don't know if it's because ...
-1
votes
1answer
32 views

How to acquire a table result set from “Table - valued function” when the result set has variable structures?

I need to write a Table - valued function from which I can acquire a table result set with different structure each time. another thing is that I need to declare variables inside my function . I've ...
1
vote
2answers
584 views

SQL Server: Pass table name into table valued function as a parameter

All of my tables have those two columns: [Id] [uniqueidentifier] NOT NULL [Revision] [bigint] IDENTITY(1, 1) NOT NULL Every other time I write a query, I want to eliminate some of the rows. The ...
2
votes
1answer
145 views

Efficiency of Scalar UDF vs TVF

I am trying to optimize the rollup code for my company and ran into a very peculiar issue. I converted many Scalar functions to be TVFs and they all seem to run more quickly than the original, which ...
1
vote
0answers
332 views

Scalar function with while loop to TVF

Hi I have a view which is used in lots of search queries in my application. The issue is application queries which use this view is is running very slow.I am investigating this and i found out a ...
1
vote
1answer
124 views

Table Valued UDF vs Stored Proc for passing values to a SELECT statement

I work with an application that, in many places, uses stored procedures to return data to the application, kind of like a view, but these procedures take in parameters (which isn't possible in a SQL ...
6
votes
2answers
8k views

How to recompile all stored procedures and table valued functions in SQL Server database?

In a SQL Server database sp_recompile can be run on a stored procedure to update the execution plan. I would like to run this on all stored procedures in a database. Also, I would like to run its ...
0
votes
2answers
109 views

TSQL - Intermediate results returned by User Defined Functions (UDF) and used within Inline Table Valued Function (iTVF)

I have recently run into this issue and wanted to know whether my understanding was correct. I am by DNA a backend developer (not DBA) but has regular interaction with SQL. I created two identical ...
5
votes
2answers
662 views

T-SQL Daylight Saving lookup table - poorly performing table-valued function

I've created a "Daylight Savings" lookup calendar table for the GMT region. The function I'm using to query the table to return the local datetime from a UTC datetime is performing poorly. Any help ...
1
vote
2answers
141 views

Why would adding a column to a view corrupted a table-valued function?

I have a TVP myTVP implemented as select val.* from vw_MyView val. The other day, I modified vw_MyView, replacing: select Foo,Bar from DB with select Foo, Bash, Bar from DB. This had a strange side ...
3
votes
1answer
2k views

Should I add set nocount for functions?

I've been reading about the benefits for all triggers and procedures having "set nocount on" implemented unless you make use of DONE_IN_PROC. I have a scalar-valued function that is called from an ...
0
votes
2answers
37 views

Get value of math operations SQL SERVER

I have a problem on this query, how do I use the result of the query contained in CostoUnitario field. I see better explain, after: ... as CostoUnitario.. I should insert ,CostoUnitario* Quantita ...
22
votes
1answer
2k views

Does SQL Server cache the result of a multi-statement table-valued function?

A multi-statement table-valued function returns its result in a table variable. Are these results ever reused, or is the function always fully evaluated every time it is called?
0
votes
1answer
719 views

Can a TVF use multiple CTE statements internally?

I'm trying to make a multi-statement table valued function and failing due to what I think is a limitation of functions and CTEs. The intended function defines an input parameter(@Param) and and ...
0
votes
1answer
50 views

Check for duplicate data from called functions

I'm new to SQL and am not sure how to go about getting the result set I am after. Below is the closest I have gotten to my desired result set although I am seeing duplicate data. I have a stored ...
2
votes
1answer
161 views

Audit use of CLR Table-valued function

I have written some CLR table-valued functions that invoke a web service and I would like to audit their use. Does anyone know of any way this can be achieved in the CLR code itself? I would be nice ...
18
votes
1answer
794 views

Multi-statement TVF vs Inline TVF Performance

Comparing some of the answers on the Palindrome question(10k+ users only, since I've deleted the answer), I'm getting confusing results. I proposed a multi-statement, schema-bound TVF which I thought ...
6
votes
5answers
4k views

UNION ALL or other ways to return first row of result-set

I am writing a table valued function to geocode an address based on its Suburb, State and Postcode. I try to geocode the address using different methods, in order of decreasing accuracy: Exact match ...
2
votes
1answer
763 views

Incorrect statistics are used on index seek

I have a table-valued function and ordinary table and need to perform INNER JOIN on them. Because, the engine thinks that the function is returning always one row, I try to store the results in ...
-1
votes
1answer
798 views

Impact of WHERE Clause on Table-Valued Function and query performance

I have taken over a legacy application that has had many performance issues. One I found is on a table valued function. The TVF runs fine as a basic SELECT. However, it is incredibly slow when it has ...
0
votes
1answer
426 views

How to write function in sql which accept table as input and return result back as table?

I want to pass a table variable to a fuction (which would contains different value at different time) as input, returning a calculated result in the form of another table, which would be declared in ...
0
votes
0answers
129 views

How can I replace the cursor in the below query?

I have this below query which calls a stored procedure that takes only 1 item's attributes at a time. But because of performance problems we are required to remove the cursor. How can I replace the ...