SQL 2012 :: Query Performance - Using Inline / Table Functions
Feb 26, 2014
I have query that doesn't even register a time when running it. But when I add the lines that are commented out in the code below, it takes between 20 and 30 seconds! When I run the code for functions directly,I know when I include it like this, it loses the Indexing capabilities?
SELECT
----, ISNULL(CAST(NULLIF(dbo.ufnGetRetail(I.ISBN13),0.00) AS VARCHAR(20)), 'N/A') RetailPrice
----, ISNULL(CAST(NULLIF(SP.LocalPrice,0.00) AS VARCHAR(20)),'on request') LocalPrice
[code]...
How to have the functions included but have the query response time come down?
View 4 Replies
ADVERTISEMENT
May 1, 2007
Help! Been doing the box step with BOL for several hours , Using tables in Adventureworks to create inline-table-valued function to provide a parameterized view of three JOINS - Have sucessfully created the function but can't figure out where to 'Declare' my variable "@SalesAgentID" need to be able to invoke the function with a particular ID - If you can help me cut this dance short I would REALLY Appreciate it.
View 7 Replies
View Related
May 13, 2008
Hi!
I need to expand resursion level for resursive CTE expression within CREATE FUNCTION statement for inline table function to a value greater than default. It turns out that OPTION clause for MAXRECURSION hint perfectly works if I use it outside CREATE FUNCTION (as well as CREATE VIEW for non-parametrized queries), but it does not within CREATE FUNCTION statement - I'm getting error:
Msg 156, Level 15, State 1, Procedure ExpandedCTE, Line 34
Incorrect syntax near the keyword 'option'.
Here is the function:
create FUNCTION [dbo].[ExpandedCTE]
(
@p_id int
)
RETURNS TABLE
AS
RETURN
(
with tbl_cte (id, tbl_id, lvl)
as
(
select
id, tbl_id, 0 lvl
from
tbl
where
id = @p_id
union all
select
t.id, t.tbl_id, lvl + 1
from
tbl_cte
inner join tbl t
on rnr.tbl_id = tbl_cte.id
)
select
id, tbl_id, lvl
from
tbl_cte
option (maxrecursion 0)
)
Please help!
Alexander.
P.S.
I'm really sorry if it is about syntax, but I could not find it in the documentation.
View 12 Replies
View Related
Dec 27, 2014
I have to create a table like this across a bunch of servers. I'm thinking that I'm overlooking something with needing two additional CTEs, but maybe not. I have it at 17 seconds, which isn't much faster than a while loop solution that's currently in place.
DECLARE @START DATETIME,
@msg NVARCHAR(MAX) = N''
USE tempdb
SELECT @START = GETDATE()
CREATE TABLE dbo.EulerSource ( [SID] INT, Euler BIGINT )
[Code] ....
View 9 Replies
View Related
Sep 25, 2013
The data I am pulling is correct I just cant figure out how to order by the last 8 numbers that is my NUMBER column. I tried adding FOR XML AUTO to my last line in my query: From AP_DETAIL_REG where AP_BATCH_ID = 1212 and NUMBER is not null order by NUMBER FOR XML AUTO) as Temp(DATA) where DATA is not null
but no change same error.
Output:
1234567890000043321092513 00050020
Select DATA from(
select '12345678'+
left( '0', 10-len(cast ( CONVERT(int,( INV_AMT *100)) as varchar))) +
cast (CONVERT(int,(INV_AMT*100)) as varchar) +
left('0',2-len(CAST (MONTH(DATE) as varchar(2))))+
CAST (MONTH(DATE) as varchar(2)) +
left('0',2-len(CAST (day(CHECK_DATE) as varchar(2)))) +
CAST (day(DATE) as varchar(2))+right(cast
(year(DATE)
[code]....
View 6 Replies
View Related
Mar 10, 2007
Hi, I'm trying to get MSSQL to choose which row to display from a result from a query, but it seems to need an 'IF' or something similar.
My query asks the database to pick out all rows that meet critera X, but sometimes (quite correctly) some of the rows created by query X are duplicates. (A data overlap nothing wrong with the query)
I want to be able to get the query to decide which one of these duplicates to display. There is a unique element.
Example Below
|ID | SYSNAME |DATE |ACTIVE
---------------------------------------------
1 | PC1 | 10/3/7 | 1
2 | PC1 | 10/3/7 | 0
3 | PC2 | 10/3/7 | 1
ID 1 + 2 is the duplicate.
In my example I want to remove ID 1 from the result, I can't use the 'active' column as this will remove ID3 fro the result.
I want the query to recognise the duplicate based on the sysname, then choose to display the result with 0 in it.
Does anyone have a clue what I'm talking about? I'm loosing the plot.
View 1 Replies
View Related
Oct 1, 2014
Is there a performance limit on the number of indexes per table / database ? With Filtered indexes there appear to be many more opportunities for more finely defined, and therefore smaller indexes resulting in many more indexes on a single table.
View 4 Replies
View Related
Oct 16, 2015
I have an inline query that I am trying to convert it into JOIN, results are not coming out the same:
Original query:
SELECT distinct
(select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE DAY(LastWorked) = DAY(GETDATE()) and MONTH(LastWorked) = MONTH(GETDATE()) and YEAR(LastWorked) = YEAR(GETDATE()) and PrimStat = '1' and Collector = '3') As DcountMy query:
[code]....
View 8 Replies
View Related
Feb 5, 2007
SQL Server User defined functions can be a powerful tool, but they can also create a substantial performance penalty in a query where they are called a large number of times. Sometimes it is something that must be accepted to get the job done, but there is often an alternative of putting the code from the function “in-line� in a SQL query. That also has a penalty in development time, so judgment about which way to go is called for.
I did some testing on three different methods of converting combinations of integer values of Year, Month, Day, Hour, Minute, and Second to Datetime values and compared the runtime of each. In the first method, I did the conversion in-line in the query. In the second method, I used a UDF to do the conversion using the same algorithm as the in-line query. In the third method, I used a UDF that called two more UDFs to do the conversion.
To perform the test, I loaded a table with 3,999,969 randomly generated date/times, along with the matching year, month, day, hour, minute, and seconds, in the range of 1753-01-01 to 9999-12-31. I re-indexed the table with fill factor of 100 to make the physical size as small as possible.
For the actual test, I ran queries that converted the year, month, day, hour, minute, and second on each row to a datatime, and compared it to the datetime from that row. I ran the query using the in-line conversion, single UDF (DateTime1), and with the UDF (DateTime2) that called two more UDFs (Date and Time). I ran the tests several times, and saw only minor variations in run time. The single UDF took over 8 times as long to run as the in-line conversion. The test with the UDF that called other UDFs took over 36 times as long to run as the in-line conversion, and took over 4 times as long to run as the single UDF.
These results show that there can be a substantial performance penalty for using a UDF in place of in-line code, and that UDFs that call other UDFs can also have a substantial performance penalty compared to a UDF that does not call other UDFs.
Code to load table with test data. The functions used in the script to load the test data can be found on these links:
Random Datetime Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69499
Number Table Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
create table T_DATE (
[DATE] datetime not null primary key clustered ,
[YEAR]smallint not null ,
[MONTH]tinyint not null ,
[DAY]tinyint not null ,
[HOUR]tinyint not null ,
[MINUTE]tinyint not null ,
[SECOND]tinyint not null
)
insert into T_DATE
select distinct top 100 percent
[DATE] = dateadd(ms,-datepart(ms,a.[DATE]),a.[DATE]),
[YEAR]= year(a.[DATE]),
[MONTH]= month(a.[DATE]),
[DAY]= day(a.[DATE]),
[HOUR]= datepart(hour,a.[DATE]),
[MINUTE]= datepart(minute,a.[DATE]),
[SECOND]= datepart(second,a.[DATE])
from
(
selecttop 100 percent
[DATE] =
[dbo].[F_RANDOM_DATETIME]( '17530101', '99991231',newid() )
from
f_table_number_range(1,4000000) aa
order by
1
) a
order by
a.[DATE]
dbcc dbreindex(T_DATE,'',100)
exec sp_spaceused 'T_DATE','true'
select count(*) from T_DATE
Code to create functions used in the test. These functions are based on functions that Jeff posted in his blog on this link, modified with some suggestions of mine:
http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx
create function DateTime1
(@Year int, @Month int, @Day int, @Hour int, @Minute int, @Second int)
-- returns a dateTime value for the date and time specified.
returns datetime
as
begin
returndateadd(month,((@Year-1900)*12)+@Month-1,
dateadd(ss,(@Hour*3600)+(@Minute*60)+@Second,@Day-1))
end
go
create function Date(@Year int, @Month int, @Day int)
-- returns a datetime value for the specified year, month and day
returns datetime
as
begin
return dateadd(month,((@Year-1900)*12)+@Month-1,@Day-1)
end
go
create function Time(@Hour int, @Minute int, @Second int)
-- Returns a datetime value for the specified time at the "base" date (1/1/1900)
returns datetime
as
begin
return dateadd(ss,(@Hour*3600)+(@Minute*60)+@Second,0)
end
go
create function DateTime2
(@Year int, @Month int, @Day int, @Hour int, @Minute int, @Second int)
-- returns a dateTime value for the date and time specified.
returns datetime
as
begin
return dbo.Date(@Year,@Month,@Day) + dbo.Time(@Hour, @Minute,@Second)
end
go
Test code:
set nocount on
go
select [T_DATE Rowcount] = count(*) from T_DATE
go
declare @count int
declare @st datetime
select @st = getdate()
select
@count = count(*)
from
T_DATE a
where
a.[DATE] <> 0+a.[DATE]
select [MS No Action] = datediff(ms,0,getdate()-@st)
go
declare @count int
declare @st datetime
select @st = getdate()
select
@count = count(*)
from
T_DATE a
where
a.[DATE] <>
dateadd(month,((a.YEAR-1900)*12)+a.MONTH-1,
dateadd(ss,(a.HOUR*3600)+(a.MINUTE*60)+a.SECOND,a.DAY-1))
select [MS No Function] = datediff(ms,0,getdate()-@st)
go
declare @count int
declare @st datetime
select @st = getdate()
select
@count = count(*)
from
T_DATE a
where
a.[DATE] <> dbo.DateTime1(a.YEAR,a.MONTH,a.DAY,a.HOUR,a.MINUTE,a.SECOND)
select [MS DateTime1] = datediff(ms,0,getdate()-@st)
go
declare @count int
declare @st datetime
select @st = getdate()
select
@count = count(*)
from
T_DATE a
where
a.[DATE] <> dbo.DateTime2(a.YEAR,a.MONTH,a.DAY,a.HOUR,a.MINUTE,a.SECOND)
select [MS DateTime2] = datediff(ms,0,getdate()-@st)
go
Sample test results:
T_DATE Rowcount
---------------
3999969
MS No Action
------------
1773
MS No Function
--------------
9923
MS DateTime1
------------
82213
MS DateTime2
------------
357683
CODO ERGO SUM
View 15 Replies
View Related
May 30, 2015
I have a stored proc that is being executed from an OLE DB Source component in my Data Flow. Takes one input parm, has several variables declared within, a derived table which is used in a join and all within a try catch block with transaction handling. No updates, just returning data, works great, except now I have been asked to replace these stored procs with inline queries.
ALTER PROCEDURE [dbo].[usp_Get_Test]
(
@numberOfMonthsint
)
AS
BEGIN
SET NOCOUNT ON
set transaction isolation level read uncommitted
[Code] ....
The problems I have run into so far are...
SQL command text in OLE DB Source Editor does not like:
- TRY/CATCH block
- Will not let me use my input parm (@numberOfMonths int)
- When I hard code in my input parm (select  @BeginDate = dateadd(MONTH, -1, GETDATE())) I can parse query and run the step but no results are returned. So I am let to assume that it does not like the @TESTY derived table.
The query here as a sample has had pivots removed as well, but research suggests this should be an issue in the SQL command text.
Also, I know not even to try the Build Query... cause it will complain about any variable declarations (i.e., declare @BeginDate datetime).
For instance, can I pull this off with an Execute SQL Task? The problem is I don't see this available in the toolbox for the Data Flow.Â
Also, would my error handling be done in the Event Handlers tab now and if so, is there a good example of this?
View 3 Replies
View Related
Jul 18, 2007
Hello everybody,
I need to create a function which takes a multi-value parameter. When I select more than one item, I get the error that I have too many arguments. Does anybody have a solution?
Or can I create a view and then do a "SELECT * FROM viewName WHERE columnName IN (@param)"?
Thanks in advance for your answers.
View 7 Replies
View Related
May 14, 2008
Hello,
I have a stored procedure where I run an insert statement. I want to knwo if it is possible to do it using a variable for the table name (either in-line or with an EXEC statement without building a string first and executing that string. See examples of what I am talking about in both cases below:
I want to be able to do this (with or without the EXEC) :
------------------------------------------------------------------------------------
DECLARE @NewTableNameOut as varchar(100)
Set @NewTableNameOut = 'TableToInsertInto'
EXEC(
Insert Into @NewTableNameOut
Select * From tableToSelectFrom
)
------------------------------------------------------------------------------------
I can not do the above because it says I need to declare/set the @NewTableNameOut variable (assuming it is only looking at this for the specific insert statement and not at the variable I set earlier in the stored procedure.
I can do it like this by creating a string with the variable built into the string and then executing the string but I want to know if I can do it like I have listed above.
------------------------------------------------------------------------------------
DECLARE @NewTableNameOut as varchar(100)
Set @NewTableNameOut = 'TableToInsertInto'
EXEC(
'Insert Into ' + @NewTableNameOut + ' ' +
'Select * From tableToSelectFrom'
)
------------------------------------------------------------------------------------
It is not an issue for my simple example above but I have some rather large queries that I am building and I want to run as described above without having to build it into a string.
Is this possible at all?
If you need more info please let me know.
View 1 Replies
View Related
Jun 22, 2015
I have a recursive CTE on an inline table valued function. I need to set the MAXRECURSION option on the CTE, but SQL Server is complaining with "Incorrect syntax near the keyword 'OPTION'".
It works fine on non-inline function. I couldn't find any documentation indicating this wasn't possible.
I can use the MAXRECURSION option in call to the function
SELECT * FROM MyFunction ()
OPTION ( MAXRECURSION 0 )
but that means that the user needs to know the "MyFunction" uses recursive CTE, which defeats the purpose of the abstraction.
View 5 Replies
View Related
Oct 5, 2006
Hi,
I'm trying to call a Stored Procedure from a Inline Table-Valued Function. Is it possible? If so can someone please tell me how? And also I would like to call this function from a view. Can it be possible? Any help is highly appreciated. Thanks
View 4 Replies
View Related
Jan 21, 2015
I'm attempting to convert some INSERT-EXEC structures into table-valued functions because the procedures are deeply nested and INSERT-EXEC doesn't like nesting (Error 3915: Cannot use the ROLLBACK statement within an INSERT-EXEC statement)
The procedure has a single select statement, so I created an inline table-valued function. When I ran it with sample data, I received this error (yes, twice):
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
After ruling out obvious mistakes, I started to deconstruct the select statement with its CTE and TVP. The result is the following, built in my local sandbox database:
CREATE TYPE test_list AS TABLE(a int);
GO
CREATE FUNCTION test_function (@p int, @theTable test_list READONLY)
RETURNS TABLE
AS
RETURN (
WITH cte
AS (SELECT a FROM @theTable)
SELECT cte.a
FROM cte);
GO
DECLARE @t test_list;
INSERT @t VALUES(1);
SELECT * FROM test_function(1, @t);
When I run this, I get the same error as noted above. I'm running on version 10.50.4000.0, Developer Edition. (2008 R2 SP2)
The function above does just about nothing and has redundancies because I stripped the actual function down to the essential elements to cause the error. The essential elements are:
- One of the parameters is a table-valued parameter (the UDTT definition does not seem to matter)
- The SELECT statement has a CTE
- The TVP is accessed within the CTE
- The outer FROM clause references the CTE
- There is also a scalar parameter on the function (scalar type does not seem to matter).
- The scalar parameter precedes the TVP in the parameter list.
So I have an easy work-around: put the TVP first in the parameter list.
View 5 Replies
View Related
Jul 9, 2001
In my database/MY SERVER (SQL7/Win2K), I run a simple query with a Table/10000 rows (without cluster index):
SELECT * FROM TABLE
it take over 30s. Why it's slow? How can I check for reason? How to configure my server to improve performance?
Thanks in advance.
TH
----------------------------------
SP_CONFIGURE's RESULT in MY SERVER
----------------------------------
Table 'spt_values'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
affinity mask 0 2147483647 0 0
allow updates 0 1 1 1
cost threshold for parallelism 0 32767 5 5
cursor threshold -1 2147483647 -1 -1
default language 0 9999 0 0
default sortorder id 0 255 52 52
extended memory size (MB) 0 2147483647 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 1600000 0 0
language in cache 3 100 3 3
language neutral full-text 0 1 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max async IO 1 255 32 32
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 2147483647 2147483647
max text repl size (B) 0 2147483647 65536 65536
max worker threads 10 1024 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 0 0
nested triggers 0 1 1 1
network packet size (B) 512 65535 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 1 1
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 5 5
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 0 0
resource timeout (s) 5 2147483647 10 10
scan for startup procs 0 1 0 0
set working set size 0 1 0 0
show advanced options 0 1 1 1
spin counter 1 2147483647 10000 10000
time slice (ms) 50 1000 100 100
two digit year cutoff 1753 9999 2049 2049
Unicode comparison style 0 2147483647 196609 196609
Unicode locale id 0 2147483647 1033 1033
user connections 0 32767 0 0
user options 0 4095 0 0
Table 'spt_values'. Scan count 43, logical reads 108, physical reads 0, read-ahead reads 0.
Table 'sysconfigures'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 2.
View 4 Replies
View Related
Jul 20, 2005
I am having performance issues on a SQL query in Access. My query isaccessing and joining several tables (one very large one). The tables arelinked ODBC. The client submits the query to the server, separated byseveral states. It appears the query is retrieving gigs of data from thetable and processing the joins on the client. Is there away to perform moreof the work on the server there by minimizing the amount of extraneous tabledata moving across the network and improving performance (woefully slowabout 6 hours)?
View 3 Replies
View Related
Jun 26, 2015
how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...
create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)
[code]...
View 4 Replies
View Related
Nov 6, 2014
I want to be able to return the rows from a table that have been updated since a specific time. My query returns results in less than 1 minute if I hard code the reference timestamp, but it keeps spinning if I load the reference timestamp in a table. See examples below (the "Reference" table has only one row with a value 2014-09-30 00:00:00.000)
select * from A where ReceiptTS > '2014-09-30 00:00:00.000'
select * from A where ReceiptTS > (select ReferenceTS from Reference)
View 5 Replies
View Related
Jul 29, 2015
To avoid locking/blocking, or in transaction scope, we are trying make a common practice of writing coide for update commands in our all SPs based on primary key columns in where clause. I have a following scenario...
UPDATE [dbo].[TL_CST_Locker_Issuance] SET
[isActive] = 0
WHERE
LockerIssuanceId IN (SELECT LockerIssuanceId
[Code] ...
What is the better approach and should be followed to avoid locks and gain performance or best approach.
View 7 Replies
View Related
May 31, 2015
How do I use the CAST or CONVERT function in the code below, I require a third column (named Diff) which Minus the StartTime from the EndTime and the result is outputted in the third column (named Diff).
Calculation: @Diff = (@EndTime - @StartTime)
I still want the variables (@StartTime and @EndTime) to remain as nvarchar.
The code:
DECLARE @StartTime nvarchar(10) = '12:10';
DECLARE @EndTime nvarchar(10) = '12:30';
DECLARE @Diff time(1) = '00:00';
SELECT @StartTime AS '@StartTime', @EndTime AS '@EndTimes', @Diff AS '@Diff';
View 7 Replies
View Related
Nov 3, 2015
I have order history table that tracks who worked on a order
ID. ColA. ColB. ColC
1. Process 1234
2. Work. 7666
3. Return. 6789
4. Work. Null Role1
5. Return. 6538
I want a query to return recent colB or ColC where colA or colB are not null. In this case row 4
I have order history table that tracks who worked on a order
Another example
ID. ColA. ColB. ColC
1. Work. 1234
2. Process. 7666
3. Return. 6789
I want a query to return most recent colB or ColC where colB or ColC are not null. In this case row 2
View 1 Replies
View Related
Jun 23, 2006
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies
View Related
Mar 21, 2014
I am doing sp tuning. It has several lines. SO I divided into several small queries and executed individually and check the execution plans. In one small query, I found table scan is happening. That query is basically retrieving all columns from a table but the table doesn't have any pk or Indexes. So is it better to create non-clustered index to remove table sca.
View 2 Replies
View Related
Dec 15, 2014
I have a view saved on server - mhsvi-datawarehousedatawarehouse.This view, in it's TSQL connects to a databasethat is set up as a linked server. That server is mhsvi-sql2008ainstance1.When I try to add the view to Excel in order to automatically refresh for users as a report - I get the following error - (I get it as well)
The query did not run, or the database table could not be opened.Check the database server or contact your database administrator. Make sure the external database is available and hasn't been moved or reorganized, then try the operation again.I have access to the database where the view is saved and the database that the TSQL calls.
View 6 Replies
View Related
Jan 21, 2015
I have a table named 'DepartmentItem' which is designed with hierarchy structure. The column 'ParentId' from table DepartmentItem indicates parent-child relationship and department root among records. I have written and run a user-defined function I use recursive approach, but the function runs slowly.
My question: is there a better way to query that hierarchy table instead of using recursive?
** The current user-defined function that is written using recursive:
CREATE FUNCTION dbo.fnGetDepartmentTree
(
@departmentItemId int
)
RETURNS TABLE
AS
RETURN
with DepartmentItemTree(DepartmentItemId , DepartmentItemTypeId , ParentId, ItemOrder, Level)
[code].....
** And definition of table 'DepartmentItem' :
DepartmentItemId int IDENTITY(1,1) NOT NULL,
ParentId int NULL, -- Each department root starts when this column is NULL or the current row is department root. If it is not NULL then the current row has ParentId whose record has DepartmentItemId = ParentId of the current row (see more below)
IsActive bit NOT NULL DEFAULT ((1)),
[Code] .....
View 2 Replies
View Related
Aug 22, 2014
If you use the LEFT() function for example it provides intellisense support for some of the required parameters.
Is there a way to get that same descriptive text in user defined functions?
Some comment block you define when creating the function?
View 4 Replies
View Related
Apr 3, 2014
I want to update one table with the value from variable using a set based approach.
so the line
>> select @List = coalesce(@list + ',','') + cast(id as varchar(10)) + cast(feetype as varchar(25))
works fine but I want to take @list and use it to update my customers table. here is the full code.
create table customers
(custid int, fee_history varchar(max))
insert into customers
(custid
, fee_history
[code]....
View 5 Replies
View Related
Mar 9, 2015
I have query which is used to dynamically insert value but not working. We are trying to get all table names and insert dynamically to all tables by looping through table names.
declare @a varchar(max),@i int;
declare @table table(rno int, name varchar(max))
declare @b varchar(max)
insert into @table
select row_number() over( order by table_name) rno, table_name from INFORMATION_SCHEMA.tables
declare @tblname varchar(max)
[Code] .....
View 1 Replies
View Related
Jul 2, 2015
I have started working with dynamic queries recently. I am using a table variable and need to add a join in query dynamically.
For Eg- @TableVariable
SET @query_from = @query_from + CHAR(10) + ' JOIN @TableVariable on ABC.ID = @TableVariable.ID '
BUt it gives an error that @TableVariable must be declared
View 8 Replies
View Related
Aug 27, 2015
I have a table (ScriptTable) which holds a groupID Nvarchar(10) ,SQLStatement Nvarchar (150)
Table Fields =
GroupID SQLStatement
1234 Select CUSTNO, CUSTNAME,CUSTADDRESS from custtable where customerNo = 'AB123'
9876 Select CUSTNO, CUSTNAME,CUSTADDRESS from custtable where customerNo = 'XY*'
What I need is to take each select statement in turn and add the data into a temp table. I can use any method but it needs to be the most efficient. There can also be a varying number of select statements to run through each time my job is run.
View 6 Replies
View Related
May 25, 2008
Hello,
It is possible to write stored procedures which take table names as parameters; is it also possible to do this with table valued functions?
For example, a simple stored procedure is this:
CREATE PROCEDURE SelectTop(@tableName sysname)
AS
BEGIN
Execute('Select top 10 * from ' + @tableName + ';')
END
I want to be able to do the analogous thing with a table valued function (so that I can query the result set, without having to create a temp table). How should I do this (i.e., pass a tablename as an argument to a table valued function)?
View 11 Replies
View Related
Nov 7, 2014
I have the following Games table:
CREATE TABLE [dbo].[Games](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Lge] [nvarchar](255) NULL,
[GameDate] [date] NULL,
[HomeTeam] [nvarchar](255) NULL,
[Home_Score] [float] NULL,
[AwayTeam] [nvarchar](255) NULL,
[Away_Score] [float] NULL)
with the following data:
INSERT INTO [dbo].[Games2]
([Lge]
,[GameDate]
,[HomeTeam]
,[Home_Score]
,[AwayTeam]
[Code] ....
This gives the standings as:
Team B4 - 1 -
Team C1 - 1 1.5
Team A2 - 5 3
How can I query the data to find the "games behind" at any date?
View 9 Replies
View Related
