Temporal Table Vs. Table Variable
Nov 26, 2004Hello, everyone:
I want to save some temporal data in the stored procedure. Comparing temporal table and table type variable, which one is better regarding to the performance?
Thanks
ZYT
ADVERTISEMENT
SQL Server 2014 :: Not Able To Create Temporal Table
Jul 18, 2015I was trying to create temporal table feature which gets introduced in SQL Server 2016 but I am getting this error while trying to create it; Cannot enable compression for object 'Department_History'. Only SQL Server Enterprise Edition supports compression
I have installed SQL Server CTP 2.0 on my system.
Microsoft SQL Server 2016 (CTP2.0) - 13.0.200.172 (Intel X86) May 21 2015 11:16:44 Copyright (c) Microsoft Corporation Express Edition on Windows NT 6.2 <X86> (Build 9200: )
What Is The Difference Between: A Table Create Using Table Variable And Using # Temporary Table In Stored Procedure
Aug 29, 2007which is more efficient...which takes less memory...how is the memory allocation done for both the types.
View 1 Replies View RelatedIs A Temp Table Or A Table Variable Used In UDF's Returning A Table?
Sep 17, 2007In a table-valued UDF, does the UDF use a table variable or a temp table to form the resultset returned?
Power Pivot :: Temp Table Or Table Variable In Query (not Stored Procedure)?
Jul 19, 2012I don't know if it's a local issue but I can't use temp table or table variable in a PP query (so not in a stored procedure).
Environment: W7 enterprise desktop 32 + Office 2012 32 + PowerPivot 2012 32
Simple example:
declare @tTable(col1 int)
insert into @tTable(col1) values (1)
select * from @tTable
Works perfectly in SQL Server Management Studio and the database connection is OK to as I may generate PP table using complex (or simple) queries without difficulty.
But when trying to get this same result in a PP table I get an error, idem when replacing table variable by a temporary table.
Message: OLE DB or ODBC error. .... The current operation was cancelled because another operation the the transaction failed.
SQL Server 2012 :: Update Table From Variable Table Column?
Oct 6, 2014I am trying to use a stored procedure to update a column in a sql table using the value from a variable table I getting errors because my syntax is not correct. I think table aliases are not allowed in UPDATE statements.
This is my statement:
UPDATE [dbo].[sessions_teams] stc
SET stc.[Talks] = fmt.found_talks_type
FROM @Find_Missing_Talks fmt
WHERE stc.sessionid IN (SELECT sessionid FROM @Find_Missing_Talks)
AND stc.coupleid IN (SELECT coupleid FROM @Find_Missing_Talks)
Indexes On Table Variable Of Table Valued Function
Jan 6, 2004Hi there,
Can someone tell me if it is possible to add an index to a Table variable that is declare as part of a table valued function ? I've tried the following but I can't get it to work.
ALTER FUNCTION dbo.fnSearch_GetJobsByOccurrence
(
@param1 int,
@param2 int
)
RETURNS @Result TABLE (resultcol1 int, resultcol2 int)
AS
BEGIN
CREATE INDEX resultcol2_ind ON @Result
-- do some other stuff
RETURN
END
How To Create Index On Table Variable (Table Don't Have Primary Key)
Feb 26, 2008
Hi all,
my stored procedure have one table variable (@t_Replenishment_Rpt).I want to create an Index on this table variable.please advise any of them in this loop...
below is my table variable and I need to create 3 indexes on this...
DECLARE @t_Replenishment_Rpt TABLE
(
Item_Nbr varchar(25) NULL,
Item_Desc varchar(255) NULL,
Trx_Date datetime NULL,
Balance int NULL,
Trx_Type char(10) NULL,
Issue_Type char(10) NULL,
Location char(25) NULL,
Min_Stock int NULL,
Order_Qty int NULL,
Unit char(10) NULL,
Issue_Qty int NULL,
Vendor varchar(10) NULL,
WO_Nbr varchar(10) NULL,
Lead_Time int NULL,
PO_Nbr char(10) NULL,
PO_Status char(10) NULL,
Currency char(10) NULL,
Last_Cost money NULL,
Dept_No varchar(20) NULL,
MSDSNbr varchar(10) NULL,
VendorName varchar(50) NULL,
Reviewed varchar(20) NULL
)
I tryed all below senarios...it is giving error...
--Indexing the @t_Replenishment_Rpt table on the column Names Item Number, Vender , Department Number
--EXEC sp_executesql(CREATE UNIQUE CLUSTERED INDEX Replenishment_index ON @t_Replenishment_Rpt (Item_Nbr))
--CREATE UNIQUE CLUSTERED INDEX Idx1 ON @t_Replenishment_Rpt.Item_Nbr
INDEX_COL ( '@t_Replenishment_Rpt' , ind_Replenishment_id , Item_Nbr )
--EXEC sp_executesql('SELECT INDEXPROPERTY('+ '@t_Replenishment_Rpt' + ', ' + 'Item_Nbr' + ',' + 'IsPadIndex' + ')')
--EXEC sp_executesql(SELECT INDEXPROPERTY('@t_Replenishment_Rpt', 'Vendor','IsPadIndex'))
--EXEC sp_executesql(SELECT INDEXPROPERTY('@t_Replenishment_Rpt', 'Dept_No','IsPadIndex'))
The Age Old Argument Of Temp Table Vs Table Variable
Jan 26, 2007Hi All,Hope someone can help me...Im trying to highlight the advantages of using table variables asapposed to temp tables within single scope.My manager seems to believe that table variables are not advantageousbecause they reside in memory.He also seems to believe that temp tables do not use memory...Does anyone know how SQL server could read data from a temp tablewithout passing the data contained therein through memory???Is this a valid advantage/disadvantage of table variables VS temptables?
View 2 Replies View RelatedDelete SQL Table Using A Variable That Refers To The Table Name
Jul 20, 2005SQLLY challenged be gentle --Trying to create code that will drop a table using a variable as theTable Name.DECLARE @testname as char(50)SELECT @testname = 'CO_Line_of_Business_' +SUBSTRING(CAST(CD_LAST_EOM_DATEAS varchar), 5, 2) + '_' + LEFT(CAST(CD_LAST_EOM_DATE AS varchar),4)+ '_' + 'EOM'FROM TableNamePrint @testname = 'blah...blah...blah' (which is the actual tablename on the server)How can I use this variable (@testname) to drop the table? Undersevere time constraints so any help would be greatly appreciated.
View 2 Replies View RelatedTable Variable Versus #Temp Table
Jan 17, 2008In a previous post "Could #TempTable within SP cause lock on tempdb?" http://forums.microsoft.com/msdn/showpost.aspx?postid=2691763&siteid=1
It was obvious that we have to limit the use of #Temp table to a minimum. Let assume that some of the temp tables are really difficult to replace and we have to live with them.
Would it be easier on tempdb if the #TempTable is replaced by a table variable? Or do they all end up in tempdb?
Thanks in advance for any help.
Table-valued Function Run Once For Each Row In A Table Variable.
Mar 19, 2008I have a stored produre. Inside this stored procedure I have table variable with one column. Once the table variable is populated with rows, I would like to pass each value in the table, into a table-valued function. The table-valued function may return any number of rows. I would like all the rows the TVF returns to be returned from the stored procedure as a single result set. I would also like to do this without defining a table variable to hold the results of the table-value function.
Code Snippet
declare @IdTable table
(
EmployeeId nvarchar( 16 ) not null
)
insert into @IdTable
select EmployeeNumber from Employees
/*
I need to run this query for every EmployeeId value in @IdTable and return the results from the stored proc as a single result set.
*/
select * from fn_GetEmployeeById( EmployeeId )
Any help is very much appreciated.
Andrew
Table-valued Function Into A @table Variable
Feb 22, 2008In my stored procedure i have a multi-valued varchar(max) parameter and I wrote a table-valued function that takes the varchar(max) and return a table back to the stored procedure where i inserted into a @table. Just wondering is there a better and faster way of doing this?
ALTER PROCEDURE [dbo].[rpt]
(
@CourtIDs as nvarchar(MAX) -- @CourtIDs = '1231,3432,1234,3421'
)
AS
--split CourtIDs into a table
DECLARE @tbCourtIDs table(CourtID int NOT NULL PRIMARY KEY)
INSERT INTO @tbCourtIDs
select * from dbo.Split(@CourtIDs, ',')
Transact SQL :: Insert Values From Variable Into Table Variable
Nov 4, 2015CREATE TABLE #T(branchnumber VARCHAR(4000))
insert into #t(branchnumber) values (005)
insert into #t(branchnumber) values (090)
insert into #t(branchnumber) values (115)
insert into #t(branchnumber) values (210)
insert into #t(branchnumber) values (216)
[code]....
I have a parameter which should take multiple values into it and pass that to the code that i use. For, this i created a parameter and temporarily for testing i am passing some values into it.Using a dynamic SQL i am converting multiple values into multiple records as rows into another variable (called @QUERY). My question is, how to insert the values from variable into a table (table variable or temp table or CTE).OR Is there any way to parse the multiple values into a table. like if we pass multiple values into a parameter. those should go into a table as rows.
Should I Use Temporary Table Or Table Variable?
Apr 1, 2007hi all, if i have a function which it returns a table and i need to work with the table retured many times in the stored procedure, then should i use a temporary table or a table variable to store the returned table ? or it's there a better way in doing that?
View 1 Replies View RelatedTable Variable Vs Temp Table
Oct 15, 2004I have read alot of articles saying that we should use Table variable for small volume of data, Temp table for large volume of table.
However, how to determine small and large??? Willing for your comment and answer..
Update Table Variable From Another Table
Sep 10, 2007I need to update a table variable with a sum from another table. Something like this:
DECLARE @MyTableVariable Table
(
Location varchar(10),
ItemNumber Varchar(10)
Amount money,
Quantity int
)
UPDATE @MyTableVariable
SET Quantity =( SELECT SUM(Quantity)
FROM myTable
WHERE @MyTableVariable.[Location] = myTable.[Location]
and
@MyTableVariable.[ItemNumber] = myTable.[ItemNumber] )
The where clause does not work though. Is there a way to do this?
Temporary Table And Table Variable
Apr 14, 2008what is the difference between Temporary table and Table variable ,need one example with both(#t,@t) to know the scope
View 10 Replies View RelatedTable Variable V/s Temp Table
Sep 20, 2007
Hello,
Which is better? Using a table variable or a temporary table?
Mannu.
When To Use Temp Table And Table Variable
Nov 17, 2015in order to store huge data, should i use temp table or table variable(performance wise) and why?
View 2 Replies View RelatedDeclaring A Table Variable Within A Select Table Joined To Other Select Tables In Query
Oct 15, 2007Hello,
I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.
I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.
This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.
So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?
Thanks in advance,
Andy
Select * from
(
declare @years table (years int);
insert into @years
select
CASE
WHEN month(getdate()) in (1) THEN year(getdate())-1
WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())
END
select
u.fullname
, sum(tx.Dm_Time) LastMonthBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-1
and
year(dm_date) = (select years from @years)
and tx.dm_billable = 1
group by u.fullname
) as A
left outer join
(select
u.FullName
, sum(tx.Dm_Time) Billhours
, ((sum(tx.Dm_Time))
/
((day(getdate()) * ((5.0)/(7.0))) * 8)) perc
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
tx.Dm_Billable = '1'
and
month(tx.Dm_Date) = month(GetDate())
and
year(tx.Dm_Date) = year(GetDate())
group by u.fullname) as B
on
A.Fullname = B.Fullname
Left Outer Join
(
select
u.fullname
, sum(tx.Dm_Time) TwomosagoBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-2
group by u.fullname
) as C
on
A.Fullname = C.Fullname
Row Of Table, To A Variable
Oct 17, 2005I'm a new member of forum,
I'm a Brazilian, and don't speak english very good...
But, i need help...
private void Page_Load(object sender, System.EventArgs e)
{
string ConnectionString = "server=Server;database=db;user
id=XXX;pwd=YYY";
string CommandText = "SELECT * FROM usuario";
SqlConnection myConnection = new SqlConnection(ConnectionString);
SqlCommand myCommand = new SqlCommand(CommandText, myConnection);
myConnection.Open();
dgrid.DataSource =
myCommand.ExecuteReader(CommandBehavior.CloseConnection);
dgrid.DataBind();
}
I'm want pass a value of database table to a variable, how to make it ?
Variable As Table Name In T-SQL
Jun 5, 2001I cannot seem to pass a table name to an sp as a variable without having to construct the sql string first.
i.e i want to do this...
CREATE Procedure dbo.proc_sectranssummary
@tblname nvarchar (30)
AS
/************************************************** ******************/
CREATE TABLE @tblname (
Heading nvarchar (100),
Count_BatchID integer,
Sum_Units float,
Sum_AccruedIntDays integer
)
etc.
BUT it wont work and can only do it this way...
CREATE Procedure dbo.proc_sectranssummary
@tblname nvarchar (30)
AS
/************************************************** ********************/
DECLARE @sql nvarchar (2000)
SELECT @sql =
'CREATE TABLE ' + @tblname + '
(
Heading nvarchar (100),
Count_BatchID integer,
Sum_Units float,
Sum_AccruedIntDays integer
)'
EXEC(@sql)
etc.
but this makes it difficult when going on to use the table later on in INSERTS etc in the same sp.
pls Help!!
Palmy
Using A Variable As A Table Name
Jan 2, 2003Help!
I am writing a cursor that goes out and retrieves all the tables that contain a certain column name and then inserts specific data from those tables into a new table. The problem i'm having is even though I can load the table names into a variable I can't use that variable as a table name to do the insert. It gives me an error that the variable hasn't been declared even though it has. Is there any tricks I can do to make SQL treat the variable as a table name so I can run the Insert from these table names?
Dave
Here is the what I've got so far:
Declare @table_name varchar
Declare table_name_cursor CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES as t, syscolumns as s
WHERE t.TABLE_NAME = OBJECT_NAME((s.ID)) and s.Name='ModBy'and t.Table_Type = 'Base Table'
CREATE TABLE
##oldusers
(
NameIDint,
OldestDt Datetime
)
;
Open table_name_cursor
Fetch Next From table_name_cursor
Into @table_name
WHILE @@FETCH_STATUS = 0
Begin
Insert into ##oldusers(NameID,OldestDt)
Select Addby, Max(AddDt)
FROM @table_name<---This is the problem.
group by Addby
Fetch Next From table_name_cursor
Into @table_name
End
Close table_name_cursor
Deallocate table_name_cursor
GO
Table Name As A Variable? UDF?????
Jun 10, 2002HELP!!!!!!!!!!!!!!!!!!!!!!!!!!!
I have a UDF that will return a fully qualified table name given a few parameters. What I would like to do is call my UDF in a SQL statement and NOT have to use the sp_executesql and pass my query in as a string.
The reason for this is I have no promises from my client of the server and database name for the tables that I am going to use so I would like to build a UDF that I could change the server name and database name in ONE place and have all my queries using the right database. I basically have 3 "applications' that I need to do business with. For simplicity sake we'll just call them 'APP1','APP2','APP3'.
Example:
If my table is called 'TABLEA' and it's a table for 'APP1', what I have is UDF where you pass in the application and table and it returns the fully qualified path to the table. For example:
Select @TheTableName = dbo.MYUDF('app1','tablea')
Result is @TheTableName would be 'SERVER_FOR_APP1.DBO.TABLEA'
What I have found that I had to do is build a nvarchar(????) string of my query and then call sp_executesql and pass it the query via the nvarchar string. There are cases where this will not work - For example:
Select @somevalue = sometable.field from server_for_app1.dbo.sometable where someprimarykey = 123
Ideally I could code this as:
Select @somevalue = sometable.field from MyUDF('APP1','SOMETABLE') where someprimarykey = 123
Again, "MyUDF" returns a varchar() of the fully qualified table name including server and database name.
Does anyone know how I can make this work?
Thanks for any help you can provide and apologies for any cross posting!
Sincerely,
Dave Dulong
Table Variable
Jun 8, 2004May I have an idea about table variable?I want to know the scope and capacity of a table variable?How much data can it hold maximum?
View 1 Replies View RelatedVariable Table Name?
Nov 23, 2004How can i make a table name using a variable?
ALTER PROCEDURE [P_Batch_Temp_Table] @User nvarchar(100), @Prog nvarchar(2), @Type nvarchar(4) as
DECLARE @UserN nvarchar(100)
SET @UserN = @User
select * into ##Temp_Batch_ + @UserN
from tbl_batch WHERE PROD = @progN and TYPE = @TypeN and [User] = @UserN
Use Variable As Table Name
Nov 18, 2013is it possible to create a temp table and iterate through each database in the temp table? Something like and this would iterate both tables in the databse and display the results. (Of course I would actually be doing this on a much larger scale and not just selecting *
Code:
Declare @CountCheck int
Create table #databse
[code]...
Using A Variable As The Name For A New Table
May 7, 2008I work in a Decision Support role and we are moving from Access to SQL Server 2005.
One process we often do in Access is create unique tables for individuals and then export them out as Excel files. We read the person's name from a file and use that variable as the file name.
But I can't find a way to do that in SQL.
I want to do a select into and use the variable as the name but it doesn't work.
Using A Table As A Variable
Mar 17, 2006Is this possible ?
DECLARE @item nvarchar(50)
DECLARE @tabel varchar (50)
SET @item = 'ZVIN'
SET @tabel = 'u_tbl_inhuizen_openstaand_nw_deze_week'
select * from @tabel as tt where itemtype = @item
Variable As A Table Name
Jun 18, 2006Hi,
INSERT INTO [Raw].dbo.[@year]...
How to use a variable in such a statement above? i need to use variable as a table name, please help me.
thanks
Table Variable (bug)?
Jan 15, 2008I ran into this while testing some stuff and was wonding if it is some known behavior or not?
All that I'm doing is create a table variable, loading it with some data and then updating a random number of rows to a value. In this example I'm trying to update 3 rows and setting the Code columm to 'A'.
You will notice that there are 3 declarations of the table variable at the top of the script. I get why the first one may not work, but I would expect the second one to wokr correctly, but only the thirsd one works correctly. By "correctly" I mean updates 3 and only 3 rows.
(EDIT: I should probably note that I just hit "refresh" a bunch of times to notice that the first 2 table declarations updated a variable number of rows, for waht it's worth).
Maybe I can;t see the forest for the tree or somethig, but here is the script:DECLARE @Foo TABLE (ID INT IDENTITY, Bar INT, Code CHAR(1))
--DECLARE @Foo TABLE (ID INT IDENTITY(1, 1), Bar INT, Code CHAR(1))
--DECLARE @Foo TABLE (ID INT IDENTITY(1, 1) PRIMARY KEY, Bar INT, Code CHAR(1))
DECLARE @Count INT
INSERT @Foo
SELECT 1, NULL
UNION ALL SELECT 2, NULL
UNION ALL SELECT 3, NULL
UNION ALL SELECT 4, NULL
UNION ALL SELECT 5, NULL
UNION ALL SELECT 6, NULL
UNION ALL SELECT 7, NULL
SET @Count = 3
SELECT *
FROM @Foo
UPDATE Foo
SET Code = 'A'
FROM @Foo AS Foo
INNER JOIN
(
SELECT
ID,
ROW_NUMBER() OVER(ORDER BY NEWID()) AS RowNum
FROM
@Foo
) AS Temp
ON Foo.ID = Temp.ID
WHERE
Temp.RowNum <= @Count
SELECT *
FROM @Foo
Variable Name For Table Name?
Sep 28, 2007In SQL Server 2000, I'm trying to create a procedure that backsup a table based on variable name and then deletes the old table which will be recreate later. But get this error "Line 13: Incorrect syntax near '@new_Table'. "
Any suggestions? Just @new_Table but that doesn't work either.
Thanks!
USE db_name;
CREATE PROCEDURE BackUp_Tables
@tableName AS VARCHAR(50)
AS
DECLARE @new_Table NVARCHAR(50)
SET @new_Table = 'AD_' + @tableName
--IF EXISTS(SELECT name FROM sysobjects WHERE name = N'@new_Table AND xtype='U'')
IF OBJECT_ID(N'@new_Table', N'U') IS NOT NULL
DROP TABLE TableName
SELECT * INTO @new_Table FROM @tableName