asking help for sql query

hi everyone,
i got one sql query questions to ask you experts, i have attached a txt file with a set of sql tables,maybe u can take a look

i have this JOINED table
----------------
PM_REEFNO PM_ID PSUB_NAME PM_DATEUPDATED
----------------
C23 100 karen 2003-03-24 11:38 AM
C11 567 chris 2003-03-11 10:45 PM
C65 656 shihui 2003-03-26 09:23 AM
C10 546 yumei <NULL>
C9 209 sinlan <NULL>
C8 300 henry 2003-01-07 09:12 AM

i used this sql query to execute to get the query set above

SELECT * FROM PAYMENT_INFO , PROFILE_SUBSCRIBER WHERE PM_REFNO LIKE 'C%' AND PM_ID = PSUB_ID ORDER BY PM_REFNO ASC

I would like to know how to my sql query can execute in such a way that
i would like to arrange C10,C9 with the PM_REFNO where PM_DATEUPDATED IS NULL and then follow by PM_DATEUPDATED latest date. I would like my query results to be

PM_REEFNO PM_ID PSUB_NAME PM_DATEUPDATED
----------------
C10 546 yumei <NULL>
C9 209 sinlan <NULL>
C65 656 shihui 2003-03-26 09:23 AM
C23 100 karen 2003-03-24 11:38 AM
C11 567 chris 2003-03-11 10:45 PM
C8 300 henry 2003-01-07 09:12 AM

can i know how to build this querysomthing like this?

-- Set Option Value
-- -------- ----
-- textsize 64512
-- language us_english
-- dateformat mdy
-- datefirst 7
-- arithabort SET
-- nocount SET
-- remote_proc_transactions SET
-- ansi_null_dflt_on SET
-- disable_def_cnst_chk SET
-- ansi_warnings SET
-- ansi_padding SET
-- ansi_nulls SET
-- concat_null_yields_null SET

create table #Tmp(PM_REEFNO varchar(4), PM_ID int, PSUB_NAME varchar(15), PM_DATEUPDATED datetime)
go
insert into #Tmp values('C23',100,'karen','2003-03-24 11:38 AM')
insert into #Tmp values('C11',567,'chris','2003-03-11 10:45 PM')
insert into #Tmp values('C65',656,'shihui','2003-03-26 09:23 AM')
insert into #Tmp values('C10',546,'yumei',NULL)
insert into #Tmp values('C9',209,'sinlan',NULL)
insert into #Tmp values('C8',300,'henry','2003-01-07 09:12 AM')
go
select *
From #Tmp
order by case when PM_DATEUPDATED is null then '31-Dec-9999' else PM_DATEUPDATED end desc
, PM_REEFNO
go
drop table #Tmp
go|||hi everyone,
the above the user dexmix is correct using this sql statement

i tried to use this query he gives me
SELECT * FROM PAYMENT_INFO , PROFILE_SUBSCRIBER WHERE PM_REFNO LIKE 'C%' AND PM_ID = PSUB_ID ORDER BY IsNull(PM_DATEUPDATED,NULL), PM_DATEUPDATED DESC , PM_REFNO
the results are

PM_REEFNO PM_ID PSUB_NAME PM_DATEUPDATED
----------------
C10 546 yumei <NULL>
C9 209 sinlan <NULL>
C65 656 shihui 2003-03-26 09:23 AM
C23 100 karen 2003-03-24 11:38 AM
C11 567 chris 2003-03-11 10:45 PM
C8 300 henry 2003-01-07 09:12 AM
C2 209 sinlan 2003-01-06 09:13 AM
C1 546 yumei 2003-01-01 07:15 PM

how can i build another query that i would like the table to appear like this

PM_REEFNO PM_ID PSUB_NAME PM_DATEUPDATED
----------------
C10 546 yumei <NULL>
C1 546 yumei 2003-01-01 07:15 PM
C9 209 sinlan <NULL>
C2 209 sinlan 2003-01-06 09:13 AM
C65 656 shihui 2003-03-26 09:23 AM
C23 100 karen 2003-03-24 11:38 AM
C11 567 chris 2003-03-11 10:45 PM
C8 300 henry 2003-01-07 09:12 AM

and i tried to change the query to
SELECT * FROM PAYMENT_INFO , PROFILE_SUBSCRIBER WHERE PM_REFNO LIKE 'C%' AND PM_ID = PSUB_ID ORDER BY ISNULL(PM_DATEUPDATED,NULL) , PM_MSISDN ASC

but it does not work and the results display is not i want?
Can anyone please help?|||sorry i made some changes

this is my sql statement
SELECT * FROM PAYMENT_INFO , PROFILE_SUBSCRIBER WHERE PM_REFNO LIKE 'C%' AND PM_ID = PSUB_ID ORDER BY ISNULL(PM_DATEUPDATED,NULL) , PM_MSISDN ASC

i get this results

Table 1
---
PM_REEFNO PM_ID PSUB_NAME PM_DATEUPDATED
----------------
C10 546 yumei <NULL>
C9 209 sinlan <NULL>
C1 546 yumei 2003-01-01 07:15 PM
C2 209 sinlan 2003-01-06 09:13 AM
C8 300 henry 2003-01-07 09:12 AM
C11 567 chris 2003-03-11 10:45 PM
C23 100 karen 2003-03-24 11:38 AM
C65 656 shihui 2003-03-26 09:23 AM

how to i build this query?

Table 2
----
PM_REEFNO PM_ID PSUB_NAME PM_DATEUPDATED
----------------
C10 546 yumei <NULL>
C1 546 yumei 2003-01-01 07:15 PM
C9 209 sinlan <NULL>
C2 209 sinlan 2003-01-06 09:13 AM
C65 656 shihui 2003-03-26 09:23 AM
C23 100 karen 2003-03-24 11:38 AM
C11 567 chris 2003-03-11 10:45 PM
C8 300 henry 2003-01-07 09:12 AM

that means in my first post i want the order to be first order by PM_DateUPDATED IS NULL reflected in Table 2, Then By the PM_ID so that they can group together which is easier to view, then follow by PM_REFNO in DESC order .As u can see from Table 2 that C10 the date is null and the follow by a early date.However the problem lies with the in Table 1 cos the query forces ISNULL(PM_DATEUPDATED,NULL) to arrange in ASC|||Hi

Try this -

select *
From Tmp
order by
PSUB_NAME desc, case when PM_DATEUPDATED is null then '31-Dec-9999' else PM_DATEUPDATED end desc

I think it will work...

Cheers
Gola

Originally posted by verybrightstar
sorry i made some changes

this is my sql statement
SELECT * FROM PAYMENT_INFO , PROFILE_SUBSCRIBER WHERE PM_REFNO LIKE 'C%' AND PM_ID = PSUB_ID ORDER BY ISNULL(PM_DATEUPDATED,NULL) , PM_MSISDN ASC

i get this results

Table 1
---
PM_REEFNO PM_ID PSUB_NAME PM_DATEUPDATED
----------------
C10 546 yumei <NULL>
C9 209 sinlan <NULL>
C1 546 yumei 2003-01-01 07:15 PM
C2 209 sinlan 2003-01-06 09:13 AM
C8 300 henry 2003-01-07 09:12 AM
C11 567 chris 2003-03-11 10:45 PM
C23 100 karen 2003-03-24 11:38 AM
C65 656 shihui 2003-03-26 09:23 AM

how to i build this query?

Table 2
----
PM_REEFNO PM_ID PSUB_NAME PM_DATEUPDATED
----------------
C10 546 yumei <NULL>
C1 546 yumei 2003-01-01 07:15 PM
C9 209 sinlan <NULL>
C2 209 sinlan 2003-01-06 09:13 AM
C65 656 shihui 2003-03-26 09:23 AM
C23 100 karen 2003-03-24 11:38 AM
C11 567 chris 2003-03-11 10:45 PM
C8 300 henry 2003-01-07 09:12 AM

that means in my first post i want the order to be first order by PM_DateUPDATED IS NULL reflected in Table 2, Then By the PM_ID so that they can group together which is easier to view, then follow by PM_REFNO in DESC order .As u can see from Table 2 that C10 the date is null and the follow by a early date.However the problem lies with the in Table 1 cos the query forces ISNULL(PM_DATEUPDATED,NULL) to arrange in ASC

Asian characters converted to ? in ntext field

I have an ntext field labeled answer in one of my tables.
I had an error in one of my applications where the following sqlstatement
was executed:
INSERT INTO tblRESPONSE(ANSWER) VALUES('AsianCharactersHere')
The correct statment would have included the leadning "N" as follows:
INSERT INTO tblRESPONSE(ANSWER) VALUES(N'AsianCharactersHere')
This error resulted in question marks being inserted into the database
instead of the asian characters.
Is there *any* way to retrieve the asian characters? It appears that each
of the characters was truncated which is where the question marks come from.
I tried to use lumigent log explorer to recreate the erroneous rows from the
logs, but without success. Is the original insert command stored somewhere?
Any help would be greatly appreciated.
I just talked to microsoft sqlserver tech support. If you use the default
character set, there is no way to retrieve the data. The text is converted
to the question mark characters before it is recorded anywhere in the sql
engine.
"PJBerry" wrote:

> I have an ntext field labeled answer in one of my tables.
> I had an error in one of my applications where the following sqlstatement
> was executed:
> INSERT INTO tblRESPONSE(ANSWER) VALUES('AsianCharactersHere')
> The correct statment would have included the leadning "N" as follows:
> INSERT INTO tblRESPONSE(ANSWER) VALUES(N'AsianCharactersHere')
> This error resulted in question marks being inserted into the database
> instead of the asian characters.
> Is there *any* way to retrieve the asian characters? It appears that each
> of the characters was truncated which is where the question marks come from.
> I tried to use lumigent log explorer to recreate the erroneous rows from the
> logs, but without success. Is the original insert command stored somewhere?
> Any help would be greatly appreciated.
>

Asian characters converted to ? in ntext field

I have an ntext field labeled answer in one of my tables.
I had an error in one of my applications where the following sqlstatement
was executed:
INSERT INTO tblRESPONSE(ANSWER) VALUES('AsianCharactersHere')
The correct statment would have included the leadning "N" as follows:
INSERT INTO tblRESPONSE(ANSWER) VALUES(N'AsianCharactersHere')
This error resulted in question marks being inserted into the database
instead of the asian characters.
Is there *any* way to retrieve the asian characters? It appears that each
of the characters was truncated which is where the question marks come from.
I tried to use lumigent log explorer to recreate the erroneous rows from the
logs, but without success. Is the original insert command stored somewhere?
Any help would be greatly appreciated.I just talked to microsoft sqlserver tech support. If you use the default
character set, there is no way to retrieve the data. The text is converted
to the question mark characters before it is recorded anywhere in the sql
engine.
"PJBerry" wrote:
> I have an ntext field labeled answer in one of my tables.
> I had an error in one of my applications where the following sqlstatement
> was executed:
> INSERT INTO tblRESPONSE(ANSWER) VALUES('AsianCharactersHere')
> The correct statment would have included the leadning "N" as follows:
> INSERT INTO tblRESPONSE(ANSWER) VALUES(N'AsianCharactersHere')
> This error resulted in question marks being inserted into the database
> instead of the asian characters.
> Is there *any* way to retrieve the asian characters? It appears that each
> of the characters was truncated which is where the question marks come from.
> I tried to use lumigent log explorer to recreate the erroneous rows from the
> logs, but without success. Is the original insert command stored somewhere?
> Any help would be greatly appreciated.
>

Asian characters converted to ? in ntext field

I have an ntext field labeled answer in one of my tables.
I had an error in one of my applications where the following sqlstatement
was executed:
INSERT INTO tblRESPONSE(ANSWER) VALUES('AsianCharactersHere')
The correct statment would have included the leadning "N" as follows:
INSERT INTO tblRESPONSE(ANSWER) VALUES(N'AsianCharactersHere')
This error resulted in question marks being inserted into the database
instead of the asian characters.
Is there *any* way to retrieve the asian characters? It appears that each
of the characters was truncated which is where the question marks come from.
I tried to use lumigent log explorer to recreate the erroneous rows from the
logs, but without success. Is the original insert command stored somewhere?
Any help would be greatly appreciated.I just talked to microsoft sqlserver tech support. If you use the default
character set, there is no way to retrieve the data. The text is converted
to the question mark characters before it is recorded anywhere in the sql
engine.
"PJBerry" wrote:

> I have an ntext field labeled answer in one of my tables.
> I had an error in one of my applications where the following sqlstatement
> was executed:
> INSERT INTO tblRESPONSE(ANSWER) VALUES('AsianCharactersHere')
> The correct statment would have included the leadning "N" as follows:
> INSERT INTO tblRESPONSE(ANSWER) VALUES(N'AsianCharactersHere')
> This error resulted in question marks being inserted into the database
> instead of the asian characters.
> Is there *any* way to retrieve the asian characters? It appears that each
> of the characters was truncated which is where the question marks come fro
m.
> I tried to use lumigent log explorer to recreate the erroneous rows from t
he
> logs, but without success. Is the original insert command stored somewher
e?
> Any help would be greatly appreciated.
>

AS/400 DB2 TO SQL SERVER

I have created a database in AS/400 DB2 with 250 tables and
23GB data in the database, now I want to shift from DB2 to SQL Server.
How can import the database from AS/400 DB2 to SQL Server, without
corrupting any of the constraints and data. Can anyone explain in
detail. Data is very critical.
ramkumar.nv@.gmail.com wrote:
> I have created a database in AS/400 DB2 with 250 tables and
> 23GB data in the database, now I want to shift from DB2 to SQL Server.
> How can import the database from AS/400 DB2 to SQL Server, without
> corrupting any of the constraints and data. Can anyone explain in
> detail. Data is very critical.
>
I'm not sure that you can get the constraints carried over, so that
might be manual work.
For the data, you can set up a linked server and then script the data
over or you can use DTS/Integration services.
Regards
Steen
|||With db2lookup and db2move commands.I can create the script and ixf
files. Then should I run script for 250 tables manually.
|||So if i create the tables manually by running the script then how to
port the data into the tables from ixf files. How to use the DTS
utility in SQL Server.
|||ramkumar wrote:
> So if i create the tables manually by running the script then how to
> port the data into the tables from ixf files. How to use the DTS
> utility in SQL Server.
>
I know very little about the ixf format/files, so maybe somebody else
can add something on that part?
If you want to read about the DTS utility, you can look it up in Books
On Line. Another option is simply to use SELECT...INSERT to insert all
your data.
No matter how you do it, I think it will require quite a bit of work
to get every imported correctly...:-(.
Regards
Steen
|||Thanq Steen for your response
|||Ramkumar,
how did you create 250 tables? You must have had them scripted, right? If
not, remember, if any object is not in script, it's nowhere. Or script all
your tables on as/400 into script files. remember, as/400 is sql database
and one can derive sql compliant script out of it (not an expert here).
Adjust your scripts to sql server format and recreate them.
Define linked server against your as/4000, use information_schema and
regular character concatenation to create insert into sqltable ...select
from linkedserver.catalog.schema.table.
this should do it.
thanks
farmer
<ramkumar.nv@.gmail.com> wrote in message
news:1143783206.245490.202040@.e56g2000cwe.googlegr oups.com...
> I have created a database in AS/400 DB2 with 250 tables and
> 23GB data in the database, now I want to shift from DB2 to SQL Server.
> How can import the database from AS/400 DB2 to SQL Server, without
> corrupting any of the constraints and data. Can anyone explain in
> detail. Data is very critical.
>

AS/400 DB2 TO SQL SERVER

I have created a database in AS/400 DB2 with 250 tables and
23GB data in the database, now I want to shift from DB2 to SQL Server.
How can import the database from AS/400 DB2 to SQL Server, without
corrupting any of the constraints and data. Can anyone explain in
detail. Data is very critical.ramkumar.nv@.gmail.com wrote:
> I have created a database in AS/400 DB2 with 250 tables and
> 23GB data in the database, now I want to shift from DB2 to SQL Server.
> How can import the database from AS/400 DB2 to SQL Server, without
> corrupting any of the constraints and data. Can anyone explain in
> detail. Data is very critical.
>
I'm not sure that you can get the constraints carried over, so that
might be manual work.
For the data, you can set up a linked server and then script the data
over or you can use DTS/Integration services.
Regards
Steen|||With db2lookup and db2move commands.I can create the script and ixf
files. Then should I run script for 250 tables manually.|||So if i create the tables manually by running the script then how to
port the data into the tables from ixf files. How to use the DTS
utility in SQL Server.|||ramkumar wrote:
> So if i create the tables manually by running the script then how to
> port the data into the tables from ixf files. How to use the DTS
> utility in SQL Server.
>
I know very little about the ixf format/files, so maybe somebody else
can add something on that part?
If you want to read about the DTS utility, you can look it up in Books
On Line. Another option is simply to use SELECT...INSERT to insert all
your data.
No matter how you do it, I think it will require quite a bit of work
to get every imported correctly...:-(.
Regards
Steen|||Thanq Steen for your response|||Ramkumar,
how did you create 250 tables? You must have had them scripted, right? If
not, remember, if any object is not in script, it's nowhere. Or script all
your tables on as/400 into script files. remember, as/400 is sql database
and one can derive sql compliant script out of it (not an expert here).
Adjust your scripts to sql server format and recreate them.
Define linked server against your as/4000, use information_schema and
regular character concatenation to create insert into sqltable ...select
from linkedserver.catalog.schema.table.
this should do it.
thanks
farmer
<ramkumar.nv@.gmail.com> wrote in message
news:1143783206.245490.202040@.e56g2000cwe.googlegroups.com...
> I have created a database in AS/400 DB2 with 250 tables and
> 23GB data in the database, now I want to shift from DB2 to SQL Server.
> How can import the database from AS/400 DB2 to SQL Server, without
> corrupting any of the constraints and data. Can anyone explain in
> detail. Data is very critical.
>

AS/400 DB2 TO SQL SERVER

I have created a database in AS/400 DB2 with 250 tables and
23GB data in the database, now I want to shift from DB2 to SQL Server.
How can import the database from AS/400 DB2 to SQL Server, without
corrupting any of the constraints and data. Can anyone explain in
detail. Data is very critical.ramkumar.nv@.gmail.com wrote:
> I have created a database in AS/400 DB2 with 250 tables and
> 23GB data in the database, now I want to shift from DB2 to SQL Server.
> How can import the database from AS/400 DB2 to SQL Server, without
> corrupting any of the constraints and data. Can anyone explain in
> detail. Data is very critical.
>
I'm not sure that you can get the constraints carried over, so that
might be manual work.
For the data, you can set up a linked server and then script the data
over or you can use DTS/Integration services.
Regards
Steen|||With db2lookup and db2move commands.I can create the script and ixf
files. Then should I run script for 250 tables manually.|||So if i create the tables manually by running the script then how to
port the data into the tables from ixf files. How to use the DTS
utility in SQL Server.|||ramkumar wrote:
> So if i create the tables manually by running the script then how to
> port the data into the tables from ixf files. How to use the DTS
> utility in SQL Server.
>
I know very little about the ixf format/files, so maybe somebody else
can add something on that part?
If you want to read about the DTS utility, you can look it up in Books
On Line. Another option is simply to use SELECT...INSERT to insert all
your data.
No matter how you do it, I think it will require quite a bit of work
to get every imported correctly...:-(.
Regards
Steen|||Thanq Steen for your response|||Ramkumar,
how did you create 250 tables? You must have had them scripted, right? If
not, remember, if any object is not in script, it's nowhere. Or script all
your tables on as/400 into script files. remember, as/400 is sql database
and one can derive sql compliant script out of it (not an expert here).
Adjust your scripts to sql server format and recreate them.
Define linked server against your as/4000, use information_schema and
regular character concatenation to create insert into sqltable ...select
from linkedserver.catalog.schema.table.
this should do it.
thanks
farmer
<ramkumar.nv@.gmail.com> wrote in message
news:1143783206.245490.202040@.e56g2000cwe.googlegroups.com...
> I have created a database in AS/400 DB2 with 250 tables and
> 23GB data in the database, now I want to shift from DB2 to SQL Server.
> How can import the database from AS/400 DB2 to SQL Server, without
> corrupting any of the constraints and data. Can anyone explain in
> detail. Data is very critical.
>

AS/400 DB2 and Analysis services

I am trying to build a cube.

The data is on the AS/400

I can make a successful connection and can see the table.

When I pull the tables in a Data Source View and try to make my own relationships,

there is no columns. In fact when I try to Explore data, it return an error:

Object reference not set to an instance of an object.

Can someone tell me how to resolve this?

Hi,

Can you please tell me if this happens with Analysis Services SP1 and using Microsoft's OleDB provider for DB2 (that is the only provider supported)? Also, in case you tried, does it work against UDB?

--
Raymond

This posting is provided "AS IS" with no warranties, and confers no rights.|||

Hi,

Try to use the IBM Client Access ODBC Driver.

Regards, Christian

|||

Looks like I don have the proper version. I download the Microsoft OLEDB Provider for DB2.

Went to install it and got this:

Setup cannot continue because a supported version of SQL Server 2005 is not installed. Supported versions include Enterprise, Developer, or Enterprise Evaluation.

Thanks.

BTW I did try the IBM DB2 UDB version 5.x and that is what I was trying to use. Apparently, IBM has reported a bug. They working on it and it won't be available until the next fix. Whenever that will be.....

As tables grow, how to make text beside table stay fixed on a page

I have a table on the left hand side of a page and some fixed text on the right hand side of the page. Unfortunately, as the table grows, the text on the right hand side of the page is forced down the page. How can I keep the text fixed at a particular point on a page, no matter the size of the table on the left?
Thanks,
Mike DeardorffAnything that starts below the table will get pushed down as the table
grows.
The easiest way to prevent this is to move the textbox.
The second easiest way is to put the textbox (and potentially other items
beside the table) into a rectangle which starts before the bottom of the
table.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"MDeardorff" <MDeardorff@.discussions.microsoft.com> wrote in message
news:2AFFC019-A5BD-463B-B4C3-A7B8CDBC4D42@.microsoft.com...
> I have a table on the left hand side of a page and some fixed text on the
right hand side of the page. Unfortunately, as the table grows, the text on
the right hand side of the page is forced down the page. How can I keep the
text fixed at a particular point on a page, no matter the size of the table
on the left?
> Thanks,
> Mike Deardorff

as DTS in MS SQl Server what is ther in oracle

Hi All,
I used DTS tool of SQL server.using DTS we can map two tables on column
by column basis.
I want to know what tool is there to map two oracle tables on colum by
column basis.
Thanks & Regards,
PrakashOn 12 Sep 2006 04:17:56 -0700, sainiprakash@.gmail.com wrote:

>I used DTS tool of SQL server.using DTS we can map two tables on column
>by column basis.
>I want to know what tool is there to map two oracle tables on colum by
>column basis.
DTS works with Oracle, at least to some degree, if you don't find any
other solution.
Roy Harvey
Beacon Falls, CT

as DTS in MS SQl Server what is ther in oracle

Hi All,
I used DTS tool of SQL server.using DTS we can map two tables on column
by column basis.
I want to know what tool is there to map two oracle tables on colum by
column basis.
Thanks & Regards,
PrakashOn 12 Sep 2006 04:17:56 -0700, sainiprakash@.gmail.com wrote:
>I used DTS tool of SQL server.using DTS we can map two tables on column
>by column basis.
>I want to know what tool is there to map two oracle tables on colum by
>column basis.
DTS works with Oracle, at least to some degree, if you don't find any
other solution.
Roy Harvey
Beacon Falls, CT

Articles in more than one Category - How to organize tables?

Greetings,
I have one table, named Article, and one table name Category.
The problem is, one Article could be in just one or in several categories.
What is the best way to connect data between Article and Category according to fast search performance?
I have several ideas:
1. To have third cross table Article_Category with fields Article_ID and Category_ID, and search Article_Category table
2. To have several INTEGER columns in Article table (like Category_ID1, Category_ID2,..) and search those columns
3. Add one VARCHAR field in Article table where I could write Category ID's delimited by some character (e.g. by comma), and do text search in only that column.
What is recommended for solving problems like this?

Option 1 makes the most sense. This is how many-to-many relationships are generally implemented.

|||Thanks for advice Adam|||Yes, that is how I implemented them, it works well. Then you can use an inner join to join the article info to the category info.|||

Will you have:
Option A: Many-to-Many (category 1 has article 1 and 2, category 2 has article 3, etc)

Option B: 1-to-Many (Category 1 has article 2, category 2 has article 2, category 3 has article 5, etc)
If B, you can put your article_id in your category table. This would give you the fastest search performance and allow your joins to be simpler.

Nick

|||Hi bmains,
how large are your tables?
Do you run it on Web with ASP.NET?
Do you satisfied with search speed?|||Hi Nick,
one Category will have many articles,
one Article could be in more than one category.

There is 500 000 articles and almost 2000 categories.
Regards|||

If you are talking a join between the join table and the two main tables, then no, I wouldn't worry about speed; it shouldn't be worse performance-wise. Though, if you are talking a lot of joins, then you need to worry more. outer joins are worse than inner joins; inner joins aren't bad; here we have a requirement that you have to try to rewrite a query to use an inner join if possible, when an outer join is used.

Arrows in tables?

Hey all,

I think the answer is no, but I was wondering if it was possible to make trend arrows like you would see on a stock ticker or something similar inside a data table in reporting services. Thanks for the continued help!

Keith

Yes, you can do this. The first step is to place an image control in the table where you want your trend arrow to appear.

Then you have two options, you can return the image from your dataset, and set the source of your image from your database query, or you can embed the arrow images in your report, and then in the cell where you want your trend arrow to appear, you can use an expression to determine which of your embedded images will appear.

Try playing around with the properties of an image control (in vs you have to use the properties pane, not the properties dialog box that appears when you right-click on the image and select properties.) If you can't figure it out, post back and I'll try to walk you through it.

|||

So just put the embedded images in the same spot and use an expression to decide on one?

|||Embedded images are stored behind the scenes in the report. From the report menu in VisualStudio, you can select embeded images and capture any images you want available. Then, when you add an image control you can select those images as the source, or use an expression to dynamically choose the source.|||I got it, thank you very much!

Arranging data on multiple rows into a sigle row (converting rows into columns)

Hello,

I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row:

user1 answer1
user1 answer2
user1 answer3
user2 answer1
user2 answer2
user2 answer3

For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so thateach user will be on a single row with all of that user's answers on that single row (A column for each answer):

user1 answer1 answer2 answer3
user2 answer1 answer2 answer3

How can this be done? How can all answers of a user appear on a single row

Thanx,
Danny.

sql server 2005 or 2000?

In sql server 2005, I believe the answer is with the new pivot or unpivot commands. In 2000, it gets much trickier.

Of course, I think excel can do it's own pivoting as well.

Arkhan:Re: putting dbo explicitly in select staetement

I have witten a lot of stored procedures in my project where I did not put dbo before the user tables.My colleague told me that I have to put dbo for all statements other there could be a problem.

Any thought?,

Please assist.

Arkhan:

There are at least a couple of places in which the owner name prefix -- dbo -- is required including (1) naming of a scalar functions and (2) objects used with schemabinding. In addition, if your "shop standards" are to always designate object names with the owner name then you need to do so.

I can think of at least one situation in which I prefer that objects NOT be owned by dbo. This is at least somewhat controversial so take it with a grain of salt. In DTS "staging" tables I like to have a designated table owner so that that owner has the right to truncate the table without needing the database owner privilege. This does not figure to be relevant to your problem. It is good practice to include the owner name as part of your qualified name. One thing that worries about your question has to do with the practice of deployment of privileges.

I do not like it when I see scores of tables or other database objects that are owned by many different database users. When I see this type of stuff my knee-jerk reaction is that privileges are beging deployed to liberally. And I would guess that if you and your colleagues are seeing many problems from NOT including the owner name that you likely have this privilege problem.

Short answer: Include the dbo portion of the name.

Dave

|||

I've heard it said that not including the schema owner (usually DBO) on your object prefix can result in a "Compile lock" against your stored procedure while the client determines whether there is an object in existence for its own schema. In this situation, multiple users executing the same stored procedure would suffer from a queuing effect as each would place a compile lock on the procedure (or statement).

That being said, I've never been able to repro this is a testing environment and I have yet to see any white papers or KB articles that discuss this so I'd love it if somebody could chime in on this.

|||

Specifying the owner can help the system find the stored procedure faster. It also prevents issues if someone creates the same name procedure with a different owner (or schema in 2005) by mistake. At least if everyone uses two-part names, they will be less likely to make mistakes.

As for the compile locks, I have reproduced recompile locks in my stored procedures. I'm not 100% how I did it, but it seemed to be with temporary tables stored procedures. I added dbo in the front of each stored procedure and table name within the stored procedure and it elliminated most of the problems we had.

Arithmetic overflow error when doing outer joins

Hey everyone,

I have two tables, one is a large table (v_userviews) containing a list of all the servers and various information about those servers. The other table (l_printers) contains printer information for those servers. I am working on a view to consolidate the printer information in l_printers with the other server information in v_userviews.

I've been trying to get outer joins to work but I am getting this error:
"Server: Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type int.
Warning: Null value is eliminated by an aggregate or other SET operation."

Here is my select statement:

select u.propid, u.address,
SUM((CASE u.Tree WHEN 'tree1' then 1 ELSE 0 END)) AS One,
SUM((CASE u.Tree WHEN 'tree2' then 1 ELSE 0 END)) AS Two,
SUM((CASE u.Tree WHEN 'tree3' then 1 ELSE 0 END)) AS Three,
SUM((CASE u.Tree WHEN 'tree4' then 1 ELSE 0 END)) AS Four,
SUM((CASE u.Tree WHEN 'tree5' then 1 ELSE 0 END)) AS Five,
SUM((CASE u.Tree WHEN 'tree6' then 1 ELSE 0 END)) AS Six,
SUM((CASE u.Tree WHEN 'tree7' then 1 ELSE 0 END)) AS Seven,
SUM((CASE u.Tree WHEN 'tree8' then 1 ELSE 0 END)) AS Eight,
SUM((CASE u.Tree WHEN 'tree9' then 1 ELSE 0 END)) AS Nine,
SUM((CASE u.Tree WHEN 'tree10' then 1 ELSE 0 END)) AS Ten,
SUM((CASE u.Tree WHEN 'tree11' then 1 ELSE 0 END)) AS Eleven,
SUM((CASE u.Tree WHEN 'tree12' then 1 ELSE 0 END)) AS Twelve,
SUM((CASE u.Tree WHEN 'tree13' then 1 ELSE 0 END)) AS Thirteen,
SUM((CASE u.Tree WHEN 'tree14' then 1 ELSE 0 END)) AS Fourteen,

count(u.server) as totalservers,
sum(cast(left(u.totalspace,len(u.totalspace)-2) as int)) as totalspace,
sum(cast(left(u.totalusedspace,len(u.totalusedspac e)-2) as int)) as totalusedspace,
count(p.printer) as numprinters

from serverops.dbo.v_userviews u LEFT OUTER JOIN novell_twr.dbo.l_printers p ON u.propid = p.propid
where u.os='netware'and u.state in ('ny', 'nj', 'fl')
group by u.propid, u.address

the l_printers table is in this format:

Printers Server Propid
nvarchar nvarchar varchar

Thanks for all your help. :beer:Try commenting out all the sums, and see what the count of total servers looks like, I am going to guess it is somewhat larger than you expect. If so, then you will have to look over how you are joining the two tables.|||Try commenting out all the sums, and see what the count of total servers looks like, I am going to guess it is somewhat larger than you expect. If so, then you will have to look over how you are joining the two tables.

You were right, the total servers was definitely larger than expected. Will I have to use another method other than outer joins to consolidate the two tables?|||How many servers do you have?|||How many servers do you have?

Around 4800|||what's defined as tiny int?

bigint

Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).

int

Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).

smallint

Integer data from 2^15 (-32,768) through 2^15 - 1 (32,767).

tinyint

Integer data from 0 through 255.

post some ddl|||what's defined as tiny int?

post some ddl

Nothing is defined as tinyint

Here are some DDL

L_Printers:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[L_Printers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[L_Printers]
GO

CREATE TABLE [dbo].[L_Printers] (
[Printer] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Server] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PropID] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

R_Servers (v_userviews is actually just a distinct top 100 view of r_servers):
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[R_Servers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[R_Servers]
GO

CREATE TABLE [dbo].[R_Servers] (
[Server] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Type] [varchar] (51) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Classification] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPX Internal] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Secondary IP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DNS_IP1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DNS_IP2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DNS_RIBIP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PropID] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Branch #] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OS] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OSVersion] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InProduction] [bit] NULL ,
[NOTES] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Tree] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NWContext] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NTDomain] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NTDomainRole] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LASTXEUpdate] [datetime] NULL ,
[MacAddress] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CommonName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Zip] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TFloors] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Bank_Floors] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TotalEmp] [float] NULL ,
[Address] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Street Address] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Room] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Floor] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPResponse] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ResponseOK] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TapeDrive] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TapeType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DriveCapacity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IP Address] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RIBIP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RIBPW] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Remote PW] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Console PW] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DeviceStatus] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RIBInstalled] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SPack] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DeviceOwner] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ASource] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BackupDetails] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ServerID] [numeric](10, 0) NOT NULL ,
[Model] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Serial] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AssetNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Memory] [numeric](18, 0) NULL ,
[ROM] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CPU] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CPUSpeed] [decimal](18, 0) NULL ,
[TotalCPUs] [int] NULL ,
[Vendor] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PODate] [datetime] NULL ,
[ReceivedDate] [smalldatetime] NULL ,
[ActivationDate] [smalldatetime] NULL ,
[RefreshDate] [smalldatetime] NULL ,
[WarrantyEndDate] [smalldatetime] NULL ,
[TowerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TowerID] [int] NULL ,
[DistrictID] [int] NULL ,
[Priority] [int] NULL ,
[Severity] [int] NULL ,
[SupportComment] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SiteContact] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SupportStaff] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PSRegion] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DistrictManager] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DMName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Area] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SupportQueue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[District] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Domain] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[inProductionDate] [smalldatetime] NULL ,
[Region] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TotalSpace] [nvarchar] (257) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TotalUsedSpace] [nvarchar] (257) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RackID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RackPosition] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RackRow] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OSType] [int] NULL ,
[Project#] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ClusterID] [numeric](18, 0) NULL ,
[RIBLicenseKey] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Prop_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastUpdate] [datetime] NULL ,
[CheckSum] [int] NULL ,
[ManagmentServer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastAudit] [datetime] NULL ,
[RecordAuditor] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrimarySupport] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KVM] [bit] NULL ,
[KVMType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[expanse] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOBOwner] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LocationCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrimaryRecordOwner] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreationDate] [datetime] NULL ,
[Strategic] [bit] NULL ,
[ServiceBillingCC] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HardwareSupport] [bit] NULL ,
[Updated] [datetime] NOT NULL
) ON [PRIMARY]
GO

Are there any limitations on tables to be published for republishi

Hey. I have successfully setup Merge and transactional pubs on a serverA as
publisher and serverB as subscriber. Now, I want to partition the data in
ServerB and send it back to serverA in a different db. This is in testing. In
prod, it'll be going to a different server. The problem is that the tables
from the transactional publication can't be republished. Is this true or am i
doing something wrong? Can you guide me a bit through this? Thank you...
I'm sorry. I found the solution for the problem.

are temp tables written to disk?

So are you saying that since every database write goes to memory for a temp
table and that the write will not take place till later, there will be no
performance difference on this point?
Let me ask again, "Will temp tables write to disk at some point in time even
during or after the stored procedure is over, OR is there a certain
condition where temp table DO NOT write to disk ever?"
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:%232WwUHBtFHA.2076@.TK2MSFTNGP14.phx.gbl...
Temp tables vs table variables is a fairly common debate topic.
The person you're talking about is kind of right. Every database write
happens to memory first and so, in theory, query performance shouldn't
really be hindered by I/O to disk (as that is essentially done
asynchronously by a background spid). But this assumes you have no great
memory pressure.
IMHO there's really not enough difference, performance-wise, to be
terribly concerned about it. Temp tables are just like any other permanent
table (except that they are automatically dropped when they go out of scope)
and as such SQL Server maintains statistics on the columns in those temp
tables (SQL Server does not collect statistics on table variables). As such
the query optimiser can often come up with better plans for temp tables
(than it can with table variables) and manipulate the data in them faster.
However, table variables implement less locking generally...so it can swing
the other way too. The main reason I would use a table variable would be if
I wanted to return a result set from a UDF, because you can only do that
using a table variable. So that would be a functionality reason more so
than a performance reason.
Aaron Bertrand (SQL MVP) has a great website called ASPFAQ that has heaps
of different articles about different things people ask about SQL Server.
Here's what he says about temp tables vs table variables:
Should I use a #temp table or a @.table variable?
mike hodgson
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
I "heard" that since SQL Server 2000 caches the database, that temp
tables are written to memory before disk? and thus there is no significant
performance difference between temp table and table variables if you have
lots of memory to begin with.
I really never heard of this, but I really think this person was just
trying to cover their butt on their lack of knowledge as well as critical
mistake
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:em9qWbAtFHA.3236@.TK2MSFTNGP09.phx.gbl...
Yes they are written to disk, although it's often for only a short
period of time. All database writes, including writes to tempdb which is in
effect handled just like normal user databases, in SQL Server are written to
memory first (making the page in memory "dirty") and then flushed to disk
when the lazy writer process gets around to it.
I guess, in theory, the temp table may be dropped before the lazy
writer writes that data to disk and so it might not ever make it to the disk
but I've never tried to analyse that scenario.
mike hodgson
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
are temp tables written to disk? or are they cached like the database is
cached on queries.That really did NOT help.
Is there a case or situation where a #temp table writes to DISK?
And if so, what is this case or situation where a temp table is written to
disk?
"doller" <sufianarif@.gmail.com> wrote in message
news:1126143644.984768.213460@.z14g2000cwz.googlegroups.com...
> Hi,
> If u talk about Temp Table then u should know the meaning of temporary.
> When u create a table using # then MS-SQl Server creates a
> table(structure in Buffer to hold the data)
> and as u finh working with that it frees the memory. Temporary table
> name is basically a alias name for the buffer reserved for
> that temporary data.
> If u execute a query from QA (select * into #temp from temp) then it
> will be in that connection scope till u do not close the connection.
> If u execute a Stored Procedure then as the stored produre execution
> finished the scope of temo file will be end.
> hope this helps u
> from
> Doller
>
>
> Mike Hodgson wrote:
<http://www.microsoft.com/communitie...ams=%7eCMTYData
SvcParams%5e%7earg+Name%3d%22guid%22+Val
ue%3d%22cf9c9e5b-abcf-4495-925b-0cd5
15fcdc89%22%2f%5e%7esParams%5e%7e%2fsPar
ams%5e%7e%2fCMTYDataSvcParams%5e>
is
>|||Ok, it sounds that temp tables in the tempdb will somehow. and in some way.
eventually some other time, make a write to disk.
Thus, the person who I heard that temp tables have the same performance with
regards to table variables is wrong as temp tables somehow write to disk. A
nd in so writing to disk, temp tables would be a performance hit at some poi
nt in time.
Thus, one should try to use temp variables just like it says from microsoft
"if you can and if you need to" before using temp tables. right? I know whe
n I did and it makes a huge difference
http://support.microsoft.com/defaul...kb;en-us;305977
[at the very bottom of the article]
" In general, you use table variables whenever possible except when there is
a significant volume of data and there is repeated use of the table. In tha
t case, you can create indexes on the temporary table to increase query perf
ormance. However, each scenario may be different. Microsoft recommends that
you test if table variables are more helpful than temporary tables for a par
ticular query or stored procedure."
NEVERTHELESS, this part below is a really bad answer from microsoft does NOT
seem true in REAL LIFE as I know from experience, whether I had enough memo
ry or not, table variables are done in memory first, and then , I THINK, ove
rflow are written to disk if necessary. As for Temp Tables, well, one is not
sure what's going on, is it memory or disk? And who is to say SQL Server is
managing this memory correctly anyway and if it's flushed in time for the n
ext stored procedure execution.
Q4: Are table variables memory-only structures that are assured better perfo
rmance as compared to temporary or permanent tables, because they are mainta
ined in a database that resides on the physical disk?
A4: A table variable is not a memory-only structure. Because a table variabl
e might hold more data than can fit in memory, it has to have a place on dis
k to store data. Table variables are created in the tempdb database similar
to temporary tables. If memory is available, both table variables and tempor
ary tables are created and processed while in memory (data cache).
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:uKi
Ol1BtFHA.1168@.TK2MSFTNGP10.phx.gbl...
In thinking this through logically in my own mind, I would say that writes t
o temp tables will always result in one or more writes to disk (although tha
t write may be deferred). This is because once the page is marked dirty in
RAM, even if the page is changed yet again at a later stage to its original
values (by dropping the temp table), the lazy writer will come along and flu
sh that page to disk (i.e. copy what's in memory for that 8K page and write
in in the appropriate place in the relevant data file). The temp table gett
ing dropped (whether automatically or manually) will not reset the dirty pag
e status; only flushing the page to disk will reset the status of that page.
Prior to SQL 7 you used to be able to force SQL Server to materialise tempdb
only in RAM, which would mean temp table writes wouldn't make it to disk, b
ut that's no longer supported in SQL 2000 (tempdb is always on disk).
If nothing else, the transactions associated with creating, populating, mani
pulating and dropping the temp table result in the creation of transaction l
og records. With tempdb those records are continually truncated but not bef
ore they have been committed & flushed to disk. So temp table writes will r
esult in transaction log I/O activity in tempdb.
These are just my thoughts and are based on my understanding of how SQL Serv
er works, not on any particular whitepaper I've read or anything like that.
Perhaps someone better qualified (like Kalen Delaney, Tibor Karaszi, Andrew
Kelly, Paul Randal, Mike Epprecht, etc.) might be able to clarify more.
Having said that, this whitepaper does a pretty good job of explaining SQL I
/O including flushing dirty pages, async I/O, lazy writes and flushing log r
ecords, but skimming it I can't see that it addresses your specific question
exactly, but is very good background info about SQL I/O:
SQL Server 2000 I/O Basics
Hope this helps.
mike hodgson
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
So are you saying that since every database write goes to memory for a temp
table and that the write will not take place till later, there will be no pe
rformance difference on this point?
Let me ask again, "Will temp tables write to disk at some point in time even
during or after the stored procedure is over, OR is there a certain conditi
on where temp table DO NOT write to disk ever?"
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:%23
2WwUHBtFHA.2076@.TK2MSFTNGP14.phx.gbl...
Temp tables vs table variables is a fairly common debate topic.
The person you're talking about is kind of right. Every database write happ
ens to memory first and so, in theory, query performance shouldn't really be
hindered by I/O to disk (as that is essentially done asynchronously by a ba
ckground spid). But this assumes you have no great memory pressure.
IMHO there's really not enough difference, performance-wise, to be terribly
concerned about it. Temp tables are just like any other permanent table (ex
cept that they are automatically dropped when they go out of scope) and as s
uch SQL Server maintains statistics on the columns in those temp tables (SQL
Server does not collect statistics on table variables). As such the query
optimiser can often come up with better plans for temp tables (than it can w
ith table variables) and manipulate the data in them faster. However, table
variables implement less locking generally...so it can swing the other way
too. The main reason I would use a table variable would be if I wanted to r
eturn a result set from a UDF, because you can only do that using a table va
riable. So that would be a functionality reason more so than a performance
reason.
Aaron Bertrand (SQL MVP) has a great website called ASPFAQ that has heaps of
different articles about different things people ask about SQL Server. Her
e's what he says about temp tables vs table variables:
Should I use a #temp table or a @.table variable?
mike hodgson
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
I "heard" that since SQL Server 2000 caches the database, that temp tables a
re written to memory before disk? and thus there is no significant performa
nce difference between temp table and table variables if you have lots of me
mory to begin with.
I really never heard of this, but I really think this person was just trying
to cover their butt on their lack of knowledge as well as critical mistake
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:em9
qWbAtFHA.3236@.TK2MSFTNGP09.phx.gbl...
Yes they are written to disk, although it's often for only a short period of
time. All database writes, including writes to tempdb which is in effect h
andled just like normal user databases, in SQL Server are written to memory
first (making the page in memory "dirty") and then flushed to disk when the
lazy writer process gets around to it.
I guess, in theory, the temp table may be dropped before the lazy writer wri
tes that data to disk and so it might not ever make it to the disk but I've
never tried to analyse that scenario.
mike hodgson
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
are temp tables written to disk? or are they cached like the database is
cached on queries.|||I think the take home message is test it.
Try both certainly. Sometimes temp tables will be quicker, sometimes
table variables will be quicker. Each has their place. When you need
to create indexes on your temporary objects - temp tables. When you
need to return datasets from functions (and not just a single scalar) -
table variables. Scope is definitely simpler with table variables. The
reason there are less recompilations of procs using table variables (as
opposed to temp tables) is because the query optimiser doesn't need to
come up with a plan of how to access the data in the table variables
(it's always the same) as there is only 1 way, a table scan, but with
temp tables there can be indexes & density statistics, which make many
more access paths possible.
I wouldn't say the person you were talking to was wrong per se. He was
pretty close. And, unless we're talking about HUGE amounts of temp
table/table variable activity, I'd say debating the performance hit due
to I/O is really splitting straws.
I did do a few tests with this though (with 10 sample iterations for
each average) and came up with the following (and this is on a
completely unloaded, dedicated server - dual Xeon, 2GB RAM, local RAID1
array):
_
_*avg batch duration (msec)*
_ #rows_ _table variable_ _temp table_
512 15.1 14.5
1024 17.9 29.4
2048 29.2 39.9
4096 61.8 71.6
8192 127.9 145.7
16384 254.2 291.7
32768 544.6 583.0
65536 1117.4 1103.4
131072 2174.3 2224.2
262144 4377.1 4553.2
524288 8852.1 8402.0
1048576 17846.6 16188.6
2097152 36335.5 32739.4
As you can see, for small batches the duration is almost identical,
although temp tables are, in general slightly slower. And as the
batches become larger the averages tended to drift apart a bit with temp
tables being quicker. In any case, when you graph this, the 2 lines are
almost on top of each other.
The batches consisted of just this loop essentially (with #tmp instead
of @.mytabvar for the temp table runs):
declare @.i int
set @.i = 1
insert into @.mytabvar (blah) values ('This is a line of text')
while (@.i < 22)
begin
insert into @.mytabvar (blah) select blah from @.mytabvar
set @.i = @.i + 1
end
So you can see it was just doing selects & inserts. The temp table #tmp
was defined as exactly the same as @.mytabvar, with no indexes.
Obviously, the difference, performance-wise, between table variables &
temp tables, comes into play much more when things get more complex -
locking contention, many spids executing the same code simultaneously,
indexes, complex queries, etc.
I also noticed that using table variables always resulted in a user
table being created in tempdb, just like with temp tables:
declare @.mytabvar TABLE
(
ID int identity(1,1) not null,
blah varchar(30) null
)
select * from tempdb.dbo.sysobjects where type = 'U'
CREATE TABLE #tmp
(
ID int identity(1,1) not null,
blah varchar(30) null
)
select * from tempdb.dbo.sysobjects where type = 'U'
drop table #tmp
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:

> Ok, it sounds that temp tables in the tempdb will somehow. and in some
> way. eventually some other time, make a write to disk.
> Thus, the person who I heard that temp tables have the same
> performance with regards to table variables is wrong as temp tables
> somehow write to disk. And in so writing to disk, temp tables would be
> a performance hit at some point in time.
> Thus, one should try to use temp variables just like it says from
> microsoft "if you can and if you need to" before using temp tables.
> right? I know when I did and it makes a huge difference
> http://support.microsoft.com/defaul...kb;en-us;305977
>
> [at the very bottom of the article]
> " In general, you use table variables whenever possible except
> when there is a significant volume of data and there is repeated
> use of the table. In that case, you can create indexes on the
> temporary table to increase query performance. However, each
> scenario may be different. Microsoft recommends that you test if
> table variables are more helpful than temporary tables for a
> particular query or stored procedure."
>
> NEVERTHELESS, this part below is a really bad answer from microsoft
> does NOT seem true in REAL LIFE as I know from experience, whether I
> had enough memory or not, table variables are done in memory first,
> and then , I THINK, overflow are written to disk if necessary. As for
> Temp Tables, well, one is not sure what's going on, is it memory or
> disk? And who is to say SQL Server is managing this memory correctly
> anyway and if it's flushed in time for the next stored procedure
> execution.
>
> *Q4: Are table variables memory-only structures that are assured
> better performance as compared to temporary or permanent tables,
> because they are maintained in a database that resides on the
> physical disk?*
> *A4:* A table variable is not a memory-only structure. Because a
> table variable might hold more data than can fit in memory, it has
> to have a place on disk to store data. Table variables are created
> in the *tempdb* database similar to temporary tables. If memory is
> available, both table variables and temporary tables are created
> and processed while in memory (data cache).
>
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
> news:uKiOl1BtFHA.1168@.TK2MSFTNGP10.phx.gbl...
> In thinking this through logically in my own mind, I would say that
> writes to temp tables will always result in one or more writes to disk
> (although that write may be deferred). This is because once the page
> is marked dirty in RAM, even if the page is changed yet again at a
> later stage to its original values (by dropping the temp table), the
> lazy writer will come along and flush that page to disk (i.e. copy
> what's in memory for that 8K page and write in in the appropriate
> place in the relevant data file). The temp table getting dropped
> (whether automatically or manually) will not reset the dirty page
> status; only flushing the page to disk will reset the status of that page.
> Prior to SQL 7 you used to be able to force SQL Server to materialise
> tempdb only in RAM, which would mean temp table writes wouldn't make
> it to disk, but that's no longer supported in SQL 2000 (tempdb is
> always on disk).
> If nothing else, the transactions associated with creating,
> populating, manipulating and dropping the temp table result in the
> creation of transaction log records. With tempdb those records are
> continually truncated but not before they have been committed &
> flushed to disk. So temp table writes will result in transaction log
> I/O activity in tempdb.
> These are just my thoughts and are based on my understanding of how
> SQL Server works, not on any particular whitepaper I've read or
> anything like that. Perhaps someone better qualified (like Kalen
> Delaney, Tibor Karaszi, Andrew Kelly, Paul Randal, Mike Epprecht,
> etc.) might be able to clarify more.
> Having said that, this whitepaper does a pretty good job of explaining
> SQL I/O including flushing dirty pages, async I/O, lazy writes and
> flushing log records, but skimming it I can't see that it addresses
> your specific question exactly, but is very good background info about
> SQL I/O:
> SQL Server 2000 I/O Basics
> Hope this helps.
> --
> mike hodgson
> blog: http://sqlnerd.blogspot.com
>
> Yu6454 wrote:
> So are you saying that since every database write goes to memory for a
> temp table and that the write will not take place till later, there
> will be no performance difference on this point?
> Let me ask again, "Will temp tables write to disk at some point in
> time even during or after the stored procedure is over, OR is there a
> certain condition where temp table DO NOT write to disk ever?"
>
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
> news:%232WwUHBtFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Temp tables vs table variables is a fairly common debate topic.
> The person you're talking about is kind of right. Every database
> write happens to memory first and so, in theory, query performance
> shouldn't really be hindered by I/O to disk (as that is essentially
> done asynchronously by a background spid). But this assumes you have
> no great memory pressure.
> IMHO there's really not enough difference, performance-wise, to be
> terribly concerned about it. Temp tables are just like any other
> permanent table (except that they are automatically dropped when they
> go out of scope) and as such SQL Server maintains statistics on the
> columns in those temp tables (SQL Server does not collect statistics
> on table variables). As such the query optimiser can often come up
> with better plans for temp tables (than it can with table variables)
> and manipulate the data in them faster. However, table variables
> implement less locking generally...so it can swing the other way too.
> The main reason I would use a table variable would be if I wanted to
> return a result set from a UDF, because you can only do that using a
> table variable. So that would be a functionality reason more so than
> a performance reason.
> Aaron Bertrand (SQL MVP) has a great website called ASPFAQ that has
> heaps of different articles about different things people ask about
> SQL Server. Here's what he says about temp tables vs table variables:
> Should I use a #temp table or a @.table variable?
> --
> mike hodgson
> blog: http://sqlnerd.blogspot.com
>
> Yu6454 wrote:
> I "heard" that since SQL Server 2000 caches the database, that temp
> tables are written to memory before disk? and thus there is no
> significant performance difference between temp table and table
> variables if you have lots of memory to begin with.
> I really never heard of this, but I really think this person was just
> trying to cover their butt on their lack of knowledge as well as
> critical mistake
>
>
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
> news:em9qWbAtFHA.3236@.TK2MSFTNGP09.phx.gbl...
> Yes they are written to disk, although it's often for only a short
> period of time. All database writes, including writes to tempdb which
> is in effect handled just like normal user databases, in SQL Server
> are written to memory first (making the page in memory "dirty") and
> then flushed to disk when the lazy writer process gets around to it.
> I guess, in theory, the temp table may be dropped before the lazy
> writer writes that data to disk and so it might not ever make it to
> the disk but I've never tried to analyse that scenario.
> --
> mike hodgson
> blog: http://sqlnerd.blogspot.com
>
> Yu6454 wrote:
> are temp tables written to disk? or are they cached like the database is
> cached on queries.
>
>|||I haven't read all the stuff in this thread, so please forgive me if I'm rep
eating or stating the
obvious below. Just my 2 cents:
Temp tables and table variables are handled internally very very similar. Th
ere is slightly less
logging to the transaction log for table variables (can easily be verified c
hecking number of log
records after a set of modifications). So, please don't forget that for both
temp tables and table
variables, you still have writing to transaction log, same way as for regula
r tables (except a bit
less logging, no REDO information).
As for usage, a rough rule is to use table variables for smaller tables, les
s risks for recompiles,
tiny less over head (no statistics). For bigger tables, use temp tables (sta
tistics, you can create
indexes etc).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:%23j2jMVEtFHA.3908@.tk2msftngp13.phx.gbl...
>I think the take home message is test it.
> Try both certainly. Sometimes temp tables will be quicker, sometimes
> table variables will be quicker. Each has their place. When you need
> to create indexes on your temporary objects - temp tables. When you
> need to return datasets from functions (and not just a single scalar) -
> table variables. Scope is definitely simpler with table variables. The
> reason there are less recompilations of procs using table variables (as
> opposed to temp tables) is because the query optimiser doesn't need to
> come up with a plan of how to access the data in the table variables
> (it's always the same) as there is only 1 way, a table scan, but with
> temp tables there can be indexes & density statistics, which make many
> more access paths possible.
> I wouldn't say the person you were talking to was wrong per se. He was
> pretty close. And, unless we're talking about HUGE amounts of temp
> table/table variable activity, I'd say debating the performance hit due
> to I/O is really splitting straws.
> I did do a few tests with this though (with 10 sample iterations for
> each average) and came up with the following (and this is on a
> completely unloaded, dedicated server - dual Xeon, 2GB RAM, local RAID1
> array):
> _
> _*avg batch duration (msec)*
> _ #rows_ _table variable_ _temp table_
> 512 15.1 14.5
> 1024 17.9 29.4
> 2048 29.2 39.9
> 4096 61.8 71.6
> 8192 127.9 145.7
> 16384 254.2 291.7
> 32768 544.6 583.0
> 65536 1117.4 1103.4
> 131072 2174.3 2224.2
> 262144 4377.1 4553.2
> 524288 8852.1 8402.0
> 1048576 17846.6 16188.6
> 2097152 36335.5 32739.4
> As you can see, for small batches the duration is almost identical,
> although temp tables are, in general slightly slower. And as the
> batches become larger the averages tended to drift apart a bit with temp
> tables being quicker. In any case, when you graph this, the 2 lines are
> almost on top of each other.
> The batches consisted of just this loop essentially (with #tmp instead
> of @.mytabvar for the temp table runs):
> declare @.i int
> set @.i = 1
> insert into @.mytabvar (blah) values ('This is a line of text')
> while (@.i < 22)
> begin
> insert into @.mytabvar (blah) select blah from @.mytabvar
> set @.i = @.i + 1
> end
> So you can see it was just doing selects & inserts. The temp table #tmp
> was defined as exactly the same as @.mytabvar, with no indexes.
> Obviously, the difference, performance-wise, between table variables &
> temp tables, comes into play much more when things get more complex -
> locking contention, many spids executing the same code simultaneously,
> indexes, complex queries, etc.
> I also noticed that using table variables always resulted in a user
> table being created in tempdb, just like with temp tables:
> declare @.mytabvar TABLE
> (
> ID int identity(1,1) not null,
> blah varchar(30) null
> )
> select * from tempdb.dbo.sysobjects where type = 'U'
> CREATE TABLE #tmp
> (
> ID int identity(1,1) not null,
> blah varchar(30) null
> )
> select * from tempdb.dbo.sysobjects where type = 'U'
> drop table #tmp
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Yu6454 wrote:
>
>|||Also have a look at
http://toponewithties.blogspot.com/...nd.h
tml
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eevY$dEtFHA.1136@.TK2MSFTNGP12.phx.gbl...
>I haven't read all the stuff in this thread, so please forgive me if I'm
>repeating or stating the obvious below. Just my 2 cents:
> Temp tables and table variables are handled internally very very similar.
> There is slightly less logging to the transaction log for table variables
> (can easily be verified checking number of log records after a set of
> modifications). So, please don't forget that for both temp tables and
> table variables, you still have writing to transaction log, same way as
> for regular tables (except a bit less logging, no REDO information).
> As for usage, a rough rule is to use table variables for smaller tables,
> less risks for recompiles, tiny less over head (no statistics). For bigger
> tables, use temp tables (statistics, you can create indexes etc).
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
> news:%23j2jMVEtFHA.3908@.tk2msftngp13.phx.gbl...
>|||>
http://toponewithties.blogspot.com/...nd.h
tml
Very informative post. You should remove #8, however. Inserts in SQL
Server never generate a parallel execution plan. The source select or exec
may execute in parallel, but the insert itself--including the generation of
IDENTITY values--always executes in series. This behavior is the same
regardless of whether you're inserting into a table variable, a temporary
table or a normal table. Therefore, there is no difference as far as
parallelism goes between a table variable and a local temporary table.
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:uuqGmIFtFHA.3252@.TK2MSFTNGP10.phx.gbl...
> Also have a look at
>
http://toponewithties.blogspot.com/...riable-and.html[color
=darkred]
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote[/color]
in
> message news:eevY$dEtFHA.1136@.TK2MSFTNGP12.phx.gbl...
similar.
variables
bigger
The
temp
are
#tmp
>|||Additionally, I would like to add that this is an insert.
But in regards to performance, an important category is when there are simul
taneous executions like from a web orders system. So I am guessing an large
number of spid's if I am correct in using that term to begin with.
Reading this article explanation of when table variables and temp tables wri
te to disk is very POOR.
http://support.microsoft.com/defaul...kb;en-us;305977
Q4: Are table variables memory-only structures that are assured better perfo
rmance as compared to temporary or permanent tables, because they are mainta
ined in a database that resides on the physical disk?
A4: A table variable is not a memory-only structure. Because a table variabl
e might hold more data than can fit in memory, it has to have a place on dis
k to store data. Table variables are created in the tempdb database similar
to temporary tables. If memory is available, both table variables and tempor
ary tables are created and processed while in memory (data cache).
It's very unclear of when a table variable writes to disk, it just says, "it
can write to disk if there is need to write to disk when there is no memory
left. HUGE DIFFERENCE.
THIS IS WHAT I THINK.
Table Variables, as microsoft said before, should be used before temp tables
if the data is not huge or very large and several other things. Table varia
bles also clean up after themselves with regard to memory whereas temp table
s it's anyone's guess especially if there are multiple spids and the same sp
roc is being called over and over again.
One has got to ask themselves why table variable were created in SQL 2000 to
begin with versus temp tables that were already available in SQL 7.Thus, my
reasoning is table variables are faster by far as SQL 2000 will use RAM fir
st before going to disk. But with Temp tables it could easily start writing
to disk when you don't want it to even though there is plenty of RAM not in
use.
I am also things SCOPE is very important with regards to memory usage. As fo
r Temp Tables, some please enlighten me on scope as that has to be managed r
ight?
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:#j2
jMVEtFHA.3908@.tk2msftngp13.phx.gbl...
I think the take home message is test it.
Try both certainly. Sometimes temp tables will be quicker, sometimes table
variables will be quicker. Each has their place. When you need to create i
ndexes on your temporary objects - temp tables. When you need to return dat
asets from functions (and not just a single scalar) - table variables. Scop
e is definitely simpler with table variables. The reason there are less rec
ompilations of procs using table variables (as opposed to temp tables) is be
cause the query optimiser doesn't need to come up with a plan of how to acce
ss the data in the table variables (it's always the same) as there is only 1
way, a table scan, but with temp tables there can be indexes & density stat
istics, which make many more access paths possible.
I wouldn't say the person you were talking to was wrong per se. He was pret
ty close. And, unless we're talking about HUGE amounts of temp table/table
variable activity, I'd say debating the performance hit due to I/O is really
splitting straws.
I did do a few tests with this though (with 10 sample iterations for each av
erage) and came up with the following (and this is on a completely unloaded,
dedicated server - dual Xeon, 2GB RAM, local RAID1 array):
avg batch duration (msec)
#rows table variable temp table
512 15.1 14.5
1024 17.9 29.4
2048 29.2 39.9
4096 61.8 71.6
8192 127.9 145.7
16384 254.2 291.7
32768 544.6 583.0
65536 1117.4 1103.4
131072 2174.3 2224.2
262144 4377.1 4553.2
524288 8852.1 8402.0
1048576 17846.6 16188.6
2097152 36335.5 32739.4
As you can see, for small batches the duration is almost identical, although
temp tables are, in general slightly slower. And as the batches become lar
ger the averages tended to drift apart a bit with temp tables being quicker.
In any case, when you graph this, the 2 lines are almost on top of each ot
her.
The batches consisted of just this loop essentially (with #tmp instead of @.m
ytabvar for the temp table runs):
declare @.i int
set @.i = 1
insert into @.mytabvar (blah) values ('This is a line of text')
while (@.i < 22)
begin
insert into @.mytabvar (blah) select blah from @.mytabvar
set @.i = @.i + 1
end
So you can see it was just doing selects & inserts. The temp table #tmp was
defined as exactly the same as @.mytabvar, with no indexes. Obviously, the
difference, performance-wise, between table variables & temp tables, comes i
nto play much more when things get more complex - locking contention, many s
pids executing the same code simultaneously, indexes, complex queries, etc.
I also noticed that using table variables always resulted in a user table be
ing created in tempdb, just like with temp tables:
declare @.mytabvar TABLE
(
ID int identity(1,1) not null,
blah varchar(30) null
)
select * from tempdb.dbo.sysobjects where type = 'U'
CREATE TABLE #tmp
(
ID int identity(1,1) not null,
blah varchar(30) null
)
select * from tempdb.dbo.sysobjects where type = 'U'
drop table #tmp
mike hodgson
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
Ok, it sounds that temp tables in the tempdb will somehow. and in some way.
eventually some other time, make a write to disk.
Thus, the person who I heard that temp tables have the same performance with
regards to table variables is wrong as temp tables somehow write to disk. A
nd in so writing to disk, temp tables would be a performance hit at some poi
nt in time.
Thus, one should try to use temp variables just like it says from microsoft
"if you can and if you need to" before using temp tables. right? I know whe
n I did and it makes a huge difference
http://support.microsoft.com/defaul...kb;en-us;305977
[at the very bottom of the article]
" In general, you use table variables whenever possible except when there is
a significant volume of data and there is repeated use of the table. In tha
t case, you can create indexes on the temporary table to increase query perf
ormance. However, each scenario may be different. Microsoft recommends that
you test if table variables are more helpful than temporary tables for a par
ticular query or stored procedure."
NEVERTHELESS, this part below is a really bad answer from microsoft does NOT
seem true in REAL LIFE as I know from experience, whether I had enough memo
ry or not, table variables are done in memory first, and then , I THINK, ove
rflow are written to disk if necessary. As for Temp Tables, well, one is not
sure what's going on, is it memory or disk? And who is to say SQL Server is
managing this memory correctly anyway and if it's flushed in time for the n
ext stored procedure execution.
Q4: Are table variables memory-only structures that are assured better perfo
rmance as compared to temporary or permanent tables, because they are mainta
ined in a database that resides on the physical disk?
A4: A table variable is not a memory-only structure. Because a table variabl
e might hold more data than can fit in memory, it has to have a place on dis
k to store data. Table variables are created in the tempdb database similar
to temporary tables. If memory is available, both table variables and tempor
ary tables are created and processed while in memory (data cache).
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:uKi
Ol1BtFHA.1168@.TK2MSFTNGP10.phx.gbl...
In thinking this through logically in my own mind, I would say that writes t
o temp tables will always result in one or more writes to disk (although tha
t write may be deferred). This is because once the page is marked dirty in
RAM, even if the page is changed yet again at a later stage to its original
values (by dropping the temp table), the lazy writer will come along and flu
sh that page to disk (i.e. copy what's in memory for that 8K page and write
in in the appropriate place in the relevant data file). The temp table gett
ing dropped (whether automatically or manually) will not reset the dirty pag
e status; only flushing the page to disk will reset the status of that page.
Prior to SQL 7 you used to be able to force SQL Server to materialise tempdb
only in RAM, which would mean temp table writes wouldn't make it to disk, b
ut that's no longer supported in SQL 2000 (tempdb is always on disk).
If nothing else, the transactions associated with creating, populating, mani
pulating and dropping the temp table result in the creation of transaction l
og records. With tempdb those records are continually truncated but not bef
ore they have been committed & flushed to disk. So temp table writes will r
esult in transaction log I/O activity in tempdb.
These are just my thoughts and are based on my understanding of how SQL Serv
er works, not on any particular whitepaper I've read or anything like that.
Perhaps someone better qualified (like Kalen Delaney, Tibor Karaszi, Andrew
Kelly, Paul Randal, Mike Epprecht, etc.) might be able to clarify more.
Having said that, this whitepaper does a pretty good job of explaining SQL I
/O including flushing dirty pages, async I/O, lazy writes and flushing log r
ecords, but skimming it I can't see that it addresses your specific question
exactly, but is very good background info about SQL I/O:
SQL Server 2000 I/O Basics
Hope this helps.
mike hodgson
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
So are you saying that since every database write goes to memory for a temp
table and that the write will not take place till later, there will be no pe
rformance difference on this point?
Let me ask again, "Will temp tables write to disk at some point in time even
during or after the stored procedure is over, OR is there a certain conditi
on where temp table DO NOT write to disk ever?"
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:%23
2WwUHBtFHA.2076@.TK2MSFTNGP14.phx.gbl...
Temp tables vs table variables is a fairly common debate topic.
The person you're talking about is kind of right. Every database write happ
ens to memory first and so, in theory, query performance shouldn't really be
hindered by I/O to disk (as that is essentially done asynchronously by a ba
ckground spid). But this assumes you have no great memory pressure.
IMHO there's really not enough difference, performance-wise, to be terribly
concerned about it. Temp tables are just like any other permanent table (ex
cept that they are automatically dropped when they go out of scope) and as s
uch SQL Server maintains statistics on the columns in those temp tables (SQL
Server does not collect statistics on table variables). As such the query
optimiser can often come up with better plans for temp tables (than it can w
ith table variables) and manipulate the data in them faster. However, table
variables implement less locking generally...so it can swing the other way
too. The main reason I would use a table variable would be if I wanted to r
eturn a result set from a UDF, because you can only do that using a table va
riable. So that would be a functionality reason more so than a performance
reason.
Aaron Bertrand (SQL MVP) has a great website called ASPFAQ that has heaps of
different articles about different things people ask about SQL Server. Her
e's what he says about temp tables vs table variables:
Should I use a #temp table or a @.table variable?
mike hodgson
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
I "heard" that since SQL Server 2000 caches the database, that temp tables a
re written to memory before disk? and thus there is no significant performa
nce difference between temp table and table variables if you have lots of me
mory to begin with.
I really never heard of this, but I really think this person was just trying
to cover their butt on their lack of knowledge as well as critical mistake
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:em9
qWbAtFHA.3236@.TK2MSFTNGP09.phx.gbl...
Yes they are written to disk, although it's often for only a short period of
time. All database writes, including writes to tempdb which is in effect h
andled just like normal user databases, in SQL Server are written to memory
first (making the page in memory "dirty") and then flushed to disk when the
lazy writer process gets around to it.
I guess, in theory, the temp table may be dropped before the lazy writer wri
tes that data to disk and so it might not ever make it to the disk but I've
never tried to analyse that scenario.
mike hodgson
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
are temp tables written to disk? or are they cached like the database is
cached on queries.|||Brian,
8. Parallelism is not possible when you insert into a table variable.
I got the above information from the NG Posts of SQL MVP Erland
Sommarskog.
In several posts Sommarskog confirms that "When you insert
into a table variable, SQL Server can because of implementation reasons
not use parallellism. "
http://tinyurl.com/7znmx
If you search this group, you can also find SK and Tom confirming the
same.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:eqYkfpFtFHA.4076@.TK2MSFTNGP11.phx.gbl...
> http://toponewithties.blogspot.com/...nd
.html
> Very informative post. You should remove #8, however. Inserts in SQL
> Server never generate a parallel execution plan. The source select or
> exec
> may execute in parallel, but the insert itself--including the generation
> of
> IDENTITY values--always executes in series. This behavior is the same
> regardless of whether you're inserting into a table variable, a temporary
> table or a normal table. Therefore, there is no difference as far as
> parallelism goes between a table variable and a local temporary table.
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:uuqGmIFtFHA.3252@.TK2MSFTNGP10.phx.gbl...
> http://toponewithties.blogspot.com/...nd
.html
> in
> similar.
> variables
> bigger
> The
> temp
> are
> #tmp
>|||> Table variables also clean up after themselves with regard to memory
> whereas temp tables it's anyone's guess especially if there are multiple
> spids and the same sproc is being called over and over again.
Why on earth would you think this is "anyone's guess"? Temp tables are
cleaned up when the procedure or batch or session goes out of scope. And
each spid gets its own scope (and hence each spid that calls the same SP
gets its own copy of the temp table), so if the implication is that SQL
Server gets when multiple spids are calling the same proc, then you
are wrong.

> Thus, my reasoning is table variables are faster by far as SQL 2000 will
> use RAM first before going to disk.
I don't think you've entirely read all of the content before making your
assumptions. Maybe you should read this article as well:
http://www.aspfaq.com/2475

are temp tables written to disk?

are temp tables written to disk? or are they cached like the database is
cached on queries.
Yes they are written to disk, although it's often for only a short
period of time. All database writes, including writes to tempdb which
is in effect handled just like normal user databases, in SQL Server are
written to memory first (making the page in memory "dirty") and then
flushed to disk when the lazy writer process gets around to it.
I guess, in theory, the temp table may be dropped before the lazy writer
writes that data to disk and so it might not ever make it to the disk
but I've never tried to analyse that scenario.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:

>are temp tables written to disk? or are they cached like the database is
>cached on queries.
>
>
|||I "heard" that since SQL Server 2000 caches the database, that temp tables are written to memory before disk? and thus there is no significant performance difference between temp table and table variables if you have lots of memory to begin with.
I really never heard of this, but I really think this person was just trying to cover their butt on their lack of knowledge as well as critical mistake
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:em9qWbAtFHA.3236@.TK2MSFTNGP09.phx.gbl...
Yes they are written to disk, although it's often for only a short period of time. All database writes, including writes to tempdb which is in effect handled just like normal user databases, in SQL Server are written to memory first (making the page in memory "dirty") and then flushed to disk when the lazy writer process gets around to it.
I guess, in theory, the temp table may be dropped before the lazy writer writes that data to disk and so it might not ever make it to the disk but I've never tried to analyse that scenario.
mike hodgson
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
are temp tables written to disk? or are they cached like the database is
cached on queries.
|||Temp tables vs table variables is a fairly common debate topic.
The person you're talking about is kind of right. Every database write
happens to memory first and so, in theory, query performance shouldn't
really be hindered by I/O to disk (as that is essentially done
asynchronously by a background spid). But this assumes you have no
great memory pressure.
IMHO there's really not enough difference, performance-wise, to be
terribly concerned about it. Temp tables are just like any other
permanent table (except that they are automatically dropped when they go
out of scope) and as such SQL Server maintains statistics on the columns
in those temp tables (SQL Server does *not* collect statistics on table
variables). As such the query optimiser can often come up with better
plans for temp tables (than it can with table variables) and manipulate
the data in them faster. However, table variables implement less
locking generally...so it can swing the other way too. The main reason
I would use a table variable would be if I wanted to return a result set
from a UDF, because you can only do that using a table variable. So
that would be a functionality reason more so than a performance reason.
Aaron Bertrand
<http://www.microsoft.com/communities...taSvcParams%5e>
(SQL MVP) has a great website called ASPFAQ <http://www.aspfaq.com> that
has heaps of different articles about different things people ask about
SQL Server. Here's what he says about temp tables vs table variables:
Should I use a #temp table or a @.table variable?
<http://www.aspfaq.com/show.asp?id=2475>
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
[vbcol=seagreen]
> I "heard" that since SQL Server 2000 caches the database, that temp
> tables are written to memory before disk? and thus there is no
> significant performance difference between temp table and table
> variables if you have lots of memory to begin with.
> I really never heard of this, but I really think this person was just
> trying to cover their butt on their lack of knowledge as well as
> critical mistake
>
>
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com
> <mailto:mike.hodgson@.mallesons.nospam.com>> wrote in message
> news:em9qWbAtFHA.3236@.TK2MSFTNGP09.phx.gbl...
> Yes they are written to disk, although it's often for only a short
> period of time. All database writes, including writes to tempdb
> which is in effect handled just like normal user databases, in SQL
> Server are written to memory first (making the page in memory
> "dirty") and then flushed to disk when the lazy writer process
> gets around to it.
> I guess, in theory, the temp table may be dropped before the lazy
> writer writes that data to disk and so it might not ever make it
> to the disk but I've never tried to analyse that scenario.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Yu6454 wrote:
|||So are you saying that since every database write goes to memory for a temp table and that the write will not take place till later, there will be no performance difference on this point?
Let me ask again, "Will temp tables write to disk at some point in time even during or after the stored procedure is over, OR is there a certain condition where temp table DO NOT write to disk ever?"
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:%232WwUHBtFHA.2076@.TK2MSFTNGP14.phx.gbl...
Temp tables vs table variables is a fairly common debate topic.
The person you're talking about is kind of right. Every database write happens to memory first and so, in theory, query performance shouldn't really be hindered by I/O to disk (as that is essentially done asynchronously by a background spid). But this assumes you have no great memory pressure.
IMHO there's really not enough difference, performance-wise, to be terribly concerned about it. Temp tables are just like any other permanent table (except that they are automatically dropped when they go out of scope) and as such SQL Server maintains statistics on the columns in those temp tables (SQL Server does not collect statistics on table variables). As such the query optimiser can often come up with better plans for temp tables (than it can with table variables) and manipulate the data in them faster. However, table variables implement less locking generally...so it can swing the other way too. The main reason I would use a table variable would be if I wanted to return a result set from a UDF, because you can only do that using a table variable. So that would be a functionality reason more so than a performance reason.
Aaron Bertrand (SQL MVP) has a great website called ASPFAQ that has heaps of different articles about different things people ask about SQL Server. Here's what he says about temp tables vs table variables:
Should I use a #temp table or a @.table variable?
mike hodgson
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
I "heard" that since SQL Server 2000 caches the database, that temp tables are written to memory before disk? and thus there is no significant performance difference between temp table and table variables if you have lots of memory to begin with.
I really never heard of this, but I really think this person was just trying to cover their butt on their lack of knowledge as well as critical mistake
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:em9qWbAtFHA.3236@.TK2MSFTNGP09.phx.gbl...
Yes they are written to disk, although it's often for only a short period of time. All database writes, including writes to tempdb which is in effect handled just like normal user databases, in SQL Server are written to memory first (making the page in memory "dirty") and then flushed to disk when the lazy writer process gets around to it.
I guess, in theory, the temp table may be dropped before the lazy writer writes that data to disk and so it might not ever make it to the disk but I've never tried to analyse that scenario.
mike hodgson
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
are temp tables written to disk? or are they cached like the database is
cached on queries.
|||Hi,
If u talk about Temp Table then u should know the meaning of temporary.
When u create a table using # then MS-SQl Server creates a
table(structure in Buffer to hold the data)
and as u finh working with that it frees the memory. Temporary table
name is basically a alias name for the buffer reserved for
that temporary data.
If u execute a query from QA (select * into #temp from temp) then it
will be in that connection scope till u do not close the connection.
If u execute a Stored Procedure then as the stored produre execution
finished the scope of temo file will be end.
hope this helps u
from
Doller
Mike Hodgson wrote:[vbcol=seagreen]
> Temp tables vs table variables is a fairly common debate topic.
> The person you're talking about is kind of right. Every database write
> happens to memory first and so, in theory, query performance shouldn't
> really be hindered by I/O to disk (as that is essentially done
> asynchronously by a background spid). But this assumes you have no
> great memory pressure.
> IMHO there's really not enough difference, performance-wise, to be
> terribly concerned about it. Temp tables are just like any other
> permanent table (except that they are automatically dropped when they go
> out of scope) and as such SQL Server maintains statistics on the columns
> in those temp tables (SQL Server does *not* collect statistics on table
> variables). As such the query optimiser can often come up with better
> plans for temp tables (than it can with table variables) and manipulate
> the data in them faster. However, table variables implement less
> locking generally...so it can swing the other way too. The main reason
> I would use a table variable would be if I wanted to return a result set
> from a UDF, because you can only do that using a table variable. So
> that would be a functionality reason more so than a performance reason.
> Aaron Bertrand
> <http://www.microsoft.com/communities...taSvcParams%5e>
> (SQL MVP) has a great website called ASPFAQ <http://www.aspfaq.com> that
> has heaps of different articles about different things people ask about
> SQL Server. Here's what he says about temp tables vs table variables:
> Should I use a #temp table or a @.table variable?
> <http://www.aspfaq.com/show.asp?id=2475>
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Yu6454 wrote:
|||That really did NOT help.
Is there a case or situation where a #temp table writes to DISK?
And if so, what is this case or situation where a temp table is written to
disk?
"doller" <sufianarif@.gmail.com> wrote in message
news:1126143644.984768.213460@.z14g2000cwz.googlegr oups.com...[vbcol=seagreen]
> Hi,
> If u talk about Temp Table then u should know the meaning of temporary.
> When u create a table using # then MS-SQl Server creates a
> table(structure in Buffer to hold the data)
> and as u finh working with that it frees the memory. Temporary table
> name is basically a alias name for the buffer reserved for
> that temporary data.
> If u execute a query from QA (select * into #temp from temp) then it
> will be in that connection scope till u do not close the connection.
> If u execute a Stored Procedure then as the stored produre execution
> finished the scope of temo file will be end.
> hope this helps u
> from
> Doller
>
>
> Mike Hodgson wrote:
<http://www.microsoft.com/communities...ms=%7eCMTYData
SvcParams%5e%7earg+Name%3d%22guid%22+Value%3d%22cf 9c9e5b-abcf-4495-925b-0cd5
15fcdc89%22%2f%5e%7esParams%5e%7e%2fsParams%5e%7e% 2fCMTYDataSvcParams%5e>[vbcol=seagreen]
is
>
|||You should just keep your thoughts to yourself if you have nothing
useful to add. (BTW, I fully understand the difference between, and
scope of, a permanent table, a local temp table and global temp table.)
*mike hodgson*
blog: http://sqlnerd.blogspot.com
doller wrote:

>Hi,
>If u talk about Temp Table then u should know the meaning of temporary.
>When u create a table using # then MS-SQl Server creates a
>table(structure in Buffer to hold the data)
>and as u finh working with that it frees the memory. Temporary table
>name is basically a alias name for the buffer reserved for
>that temporary data.
>If u execute a query from QA (select * into #temp from temp) then it
>will be in that connection scope till u do not close the connection.
>If u execute a Stored Procedure then as the stored produre execution
>finished the scope of temo file will be end.
>hope this helps u
>from
>Doller
>
>
>Mike Hodgson wrote:
>
>
>
|||In thinking this through logically in my own mind, I would say that
writes to temp tables will always result in one or more writes to disk
(although that write may be deferred). This is because once the page is
marked dirty in RAM, even if the page is changed yet again at a later
stage to its original values (by dropping the temp table), the lazy
writer will come along and flush that page to disk (i.e. copy what's in
memory for that 8K page and write in in the appropriate place in the
relevant data file). The temp table getting dropped (whether
automatically or manually) will not reset the dirty page status; only
flushing the page to disk will reset the status of that page.
Prior to SQL 7 you used to be able to force SQL Server to materialise
tempdb only in RAM, which would mean temp table writes wouldn't make it
to disk, but that's no longer supported in SQL 2000 (tempdb is always on
disk).
If nothing else, the transactions associated with creating, populating,
manipulating and dropping the temp table result in the creation of
transaction log records. With tempdb those records are continually
truncated but not before they have been committed & flushed to disk. So
temp table writes will result in transaction log I/O activity in tempdb.
These are just my thoughts and are based on my understanding of how SQL
Server works, not on any particular whitepaper I've read or anything
like that. Perhaps someone better qualified (like Kalen Delaney, Tibor
Karaszi, Andrew Kelly, Paul Randal, Mike Epprecht, etc.) might be able
to clarify more.
Having said that, this whitepaper does a pretty good job of explaining
SQL I/O including flushing dirty pages, async I/O, lazy writes and
flushing log records, but skimming it I can't see that it addresses your
specific question exactly, but is very good background info about SQL I/O:
SQL Server 2000 I/O Basics
<http://www.microsoft.com/technet/pro...lIObasics.mspx>
Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
[vbcol=seagreen]
> So are you saying that since every database write goes to memory for a
> temp table and that the write will not take place till later, there
> will be no performance difference on this point?
> Let me ask again, "Will temp tables write to disk at some point in
> time even during or after the stored procedure is over, OR is there a
> certain condition where temp table DO NOT write to disk ever?"
>
>
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com
> <mailto:mike.hodgson@.mallesons.nospam.com>> wrote in message
> news:%232WwUHBtFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Temp tables vs table variables is a fairly common debate topic.
> The person you're talking about is kind of right. Every database
> write happens to memory first and so, in theory, query performance
> shouldn't really be hindered by I/O to disk (as that is
> essentially done asynchronously by a background spid). But this
> assumes you have no great memory pressure.
> IMHO there's really not enough difference, performance-wise, to be
> terribly concerned about it. Temp tables are just like any other
> permanent table (except that they are automatically dropped when
> they go out of scope) and as such SQL Server maintains statistics
> on the columns in those temp tables (SQL Server does *not* collect
> statistics on table variables). As such the query optimiser can
> often come up with better plans for temp tables (than it can with
> table variables) and manipulate the data in them faster. However,
> table variables implement less locking generally...so it can swing
> the other way too. The main reason I would use a table variable
> would be if I wanted to return a result set from a UDF, because
> you can only do that using a table variable. So that would be a
> functionality reason more so than a performance reason.
> Aaron Bertrand
> <http://www.microsoft.com/communities...taSvcParams%5e>
> (SQL MVP) has a great website called ASPFAQ
> <http://www.aspfaq.com> that has heaps of different articles about
> different things people ask about SQL Server. Here's what he says
> about temp tables vs table variables:
> Should I use a #temp table or a @.table variable?
> <http://www.aspfaq.com/show.asp?id=2475>
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Yu6454 wrote:
|||Ok, it sounds that temp tables in the tempdb will somehow. and in some way. eventually some other time, make a write to disk.
Thus, the person who I heard that temp tables have the same performance with regards to table variables is wrong as temp tables somehow write to disk. And in so writing to disk, temp tables would be a performance hit at some point in time.
Thus, one should try to use temp variables just like it says from microsoft "if you can and if you need to" before using temp tables. right? I know when I did and it makes a huge difference
http://support.microsoft.com/default...b;en-us;305977
[at the very bottom of the article]
" In general, you use table variables whenever possible except when there is a significant volume of data and there is repeated use of the table. In that case, you can create indexes on the temporary table to increase query performance. However, each scenario may be different. Microsoft recommends that you test if table variables are more helpful than temporary tables for a particular query or stored procedure."
NEVERTHELESS, this part below is a really bad answer from microsoft does NOT seem true in REAL LIFE as I know from experience, whether I had enough memory or not, table variables are done in memory first, and then , I THINK, overflow are written to disk if necessary. As for Temp Tables, well, one is not sure what's going on, is it memory or disk? And who is to say SQL Server is managing this memory correctly anyway and if it's flushed in time for the next stored procedure execution.
Q4: Are table variables memory-only structures that are assured better performance as compared to temporary or permanent tables, because they are maintained in a database that resides on the physical disk?
A4: A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:uKiOl1BtFHA.1168@.TK2MSFTNGP10.phx.gbl...
In thinking this through logically in my own mind, I would say that writes to temp tables will always result in one or more writes to disk (although that write may be deferred). This is because once the page is marked dirty in RAM, even if the page is changed yet again at a later stage to its original values (by dropping the temp table), the lazy writer will come along and flush that page to disk (i.e. copy what's in memory for that 8K page and write in in the appropriate place in the relevant data file). The temp table getting dropped (whether automatically or manually) will not reset the dirty page status; only flushing the page to disk will reset the status of that page.
Prior to SQL 7 you used to be able to force SQL Server to materialise tempdb only in RAM, which would mean temp table writes wouldn't make it to disk, but that's no longer supported in SQL 2000 (tempdb is always on disk).
If nothing else, the transactions associated with creating, populating, manipulating and dropping the temp table result in the creation of transaction log records. With tempdb those records are continually truncated but not before they have been committed & flushed to disk. So temp table writes will result in transaction log I/O activity in tempdb.
These are just my thoughts and are based on my understanding of how SQL Server works, not on any particular whitepaper I've read or anything like that. Perhaps someone better qualified (like Kalen Delaney, Tibor Karaszi, Andrew Kelly, Paul Randal, Mike Epprecht, etc.) might be able to clarify more.
Having said that, this whitepaper does a pretty good job of explaining SQL I/O including flushing dirty pages, async I/O, lazy writes and flushing log records, but skimming it I can't see that it addresses your specific question exactly, but is very good background info about SQL I/O:
SQL Server 2000 I/O Basics
Hope this helps.
mike hodgson
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
So are you saying that since every database write goes to memory for a temp table and that the write will not take place till later, there will be no performance difference on this point?
Let me ask again, "Will temp tables write to disk at some point in time even during or after the stored procedure is over, OR is there a certain condition where temp table DO NOT write to disk ever?"
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:%232WwUHBtFHA.2076@.TK2MSFTNGP14.phx.gbl...
Temp tables vs table variables is a fairly common debate topic.
The person you're talking about is kind of right. Every database write happens to memory first and so, in theory, query performance shouldn't really be hindered by I/O to disk (as that is essentially done asynchronously by a background spid). But this assumes you have no great memory pressure.
IMHO there's really not enough difference, performance-wise, to be terribly concerned about it. Temp tables are just like any other permanent table (except that they are automatically dropped when they go out of scope) and as such SQL Server maintains statistics on the columns in those temp tables (SQL Server does not collect statistics on table variables). As such the query optimiser can often come up with better plans for temp tables (than it can with table variables) and manipulate the data in them faster. However, table variables implement less locking generally...so it can swing the other way too. The main reason I would use a table variable would be if I wanted to return a result set from a UDF, because you can only do that using a table variable. So that would be a functionality reason more so than a performance reason.
Aaron Bertrand (SQL MVP) has a great website called ASPFAQ that has heaps of different articles about different things people ask about SQL Server. Here's what he says about temp tables vs table variables:
Should I use a #temp table or a @.table variable?
mike hodgson
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
I "heard" that since SQL Server 2000 caches the database, that temp tables are written to memory before disk? and thus there is no significant performance difference between temp table and table variables if you have lots of memory to begin with.
I really never heard of this, but I really think this person was just trying to cover their butt on their lack of knowledge as well as critical mistake
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:em9qWbAtFHA.3236@.TK2MSFTNGP09.phx.gbl...
Yes they are written to disk, although it's often for only a short period of time. All database writes, including writes to tempdb which is in effect handled just like normal user databases, in SQL Server are written to memory first (making the page in memory "dirty") and then flushed to disk when the lazy writer process gets around to it.
I guess, in theory, the temp table may be dropped before the lazy writer writes that data to disk and so it might not ever make it to the disk but I've never tried to analyse that scenario.
mike hodgson
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
are temp tables written to disk? or are they cached like the database is
cached on queries.