|
Books Online has a full discussion of the under CREATE TABLE. It has a section on Temporary Tables that explains about the # table name prefix.
#MyTempTable: Local temp table visible only within the current SQL session. This is the one you want to use if you have multple users creating temporary tables with the same name.
##MyTempTable: Global temp table that all can see.
|
|
|
|
|
Thanks Michael, Thats exactly the information I was looking for. What is books online? I went to www.bookOnline.com... I must be going to the wrong place anyhow thanks for the info.
|
|
|
|
|
Books Online is the Transact-SQL help that is found in the SQL Query Analyzer that comes with SQL Server.
You will not have it if you are using MSDE only.
|
|
|
|
|
DOWNLOAD Books Online HERE[^]
Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg
|
|
|
|
|
Hi all,
I'm hoping that somebody can help me with the performance of my a slow stored procedure that I have. This is a part of my scenario:
I have many Projects each project can have many samples and each sample can have many paramters. W
When I run my stored procedure to return all projects I have a funtion that I call on each row that gets all the samples(cursor) for a project. Then for each sample I get all the paramters(cursor).
Project -> samples -> paramters
for each sample I run through a cursor and concatnate a string of paramters that I find in a function I call.
With in the paramters function I run through the cursor and concatnate a string of paramters in the format of paramtername, paramtername, paramtername.
Now I have reason to belive this query is slow becuase of all the cursors we use. One of my collegues belives that the cursors are not the problem, infact he belives they are not slow at all that is all hype that cursors are poor in perforance is this right? Does anybody have any suggestions?
|
|
|
|
|
Cursors are slow. For example, last year I rewrote a stored procedure that used cursors to do the same thing without. I went from taking 20 minutes down to sub-one minute. A little bit more tweaking and the final result was about 7 seconds. So, while there may be other factors slowing down a stored procedure, eliminating cursors will almost certainly speed up things.
Remember that database systems are designed to work on sets. If you can give it a set of information to process then it works more efficiently than if you give it one row at a time.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Right now I am trying to figure out how I can eliminate the different cursors I have. I can not seem to think of the logic required to get say ten rows from a select statement and then concatnate a string based on a value from each row. Could this be done with a temp table? would a temp table be faster then a cursor? Is there perhaps a way to do this with a select statement to concatenate the string together kind of like when you use the sum method?
|
|
|
|
|
Here is some sample code to concat a strings without using a cursor. You will still have to loop through to append the results but it will eliminate one of your cursors.
DECLARE @ParamStr VARCHAR(1000)
SELECT @ParamStr = ISNULL(@ParamStr + ', ','') + ParameterName
FROM Parameters
PRINT @ParamStr
|
|
|
|
|
Thanks Michael this just might be what I'm looking for. I'll let you know how it turns out. My original query returns 318 records and takes about 1 min to run. I'm hopeing to knock it down to a few seconds.
|
|
|
|
|
Thanks for everyones help, I got my query down to 3 seconds from 1 min. I used a combination of a temp table and removed some user defined funtions that were absolutly useless and slowing my SP down. I normally hate working with SP's but I enjoyed learning about how to optomize a SP.
|
|
|
|
|
Hi, I am creating a dataset, by adding up to 10000 rows at a time. At present, I add each of these rows using a stored procedure. Is there anyway I can add the entire dataset instead of doing this row by row?
Thanks
|
|
|
|
|
Hi all,
How to make "ERD diagram" in SQL Server 2000.
plz discuss in detail
Thanx in advance
Sajjad
|
|
|
|
|
|
Hi,
Is there any way to define stored procedute to return nText(memo).
I need this to convert my memo field to my own data.
Any help?
Be simple and Be sample.
|
|
|
|
|
i would like to store farsi date in ms sql server, can any body know how to do it
ramesh
Gourigari Ramesh Reddy
Hyderabad
|
|
|
|
|
Hi there,
I cannot manage to use the greek character in mysql db.
I try using the greek character set and collation, but no way, it doesn't working.
Does someone have already success to work with mysql and non latin character ?
If yes can you help by giving me some advices plz.
Thx in advance.
|
|
|
|
|
hi
i imported an ms access db to SQL SERVER 2000 using the tool "import and export data".
Now all my relations from my access db are not shown or created in the sql server db.
How does this come? Is there a way to also import the relations? or do i have to lay them back one by one by hand?
grtz & thx
|
|
|
|
|
|
Hi all,
I wanna know about, r Sql Server 2000 support the Booleon data type, if yes plz tell how can it use...
and also tell about some common Data type like datatime,varchar,numeric...
Thanx in advance
Sajjad
|
|
|
|
|
imshally81 wrote:
Sql Server 2000 support the Booleon data type
Yes, in SQL Server it is called bit
imshally81 wrote:
tell about some common Data type
That's a vig vague. What do you want to know?
The base datatypes are:
binary Bigint bit Char datetime decimal Float image Int Money
nchar Ntext nvarchar Numeric Real smalldatetime smallint smallmoney sql_variant sysname text timestamp tinyint varbinary varchar uniqueidentifier
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Thanx for reply,
I just wanna know small introduction of common data types and their usage....
Sajjad
|
|
|
|
|
|
|
I have a table on an SQLServer 2000 box with a DateTime field called CallTime.
I want to select all the records between 19:00 and 06:59 over a period of days
Is there a more efficent way of doing the select in a stored procedure than this,
WHERE (DATEPART(hh, dbo.JobData.calltime) * 60 + DATEPART(mi, dbo.JobData.calltime) BETWEEN
DATEPART(hh, '19:00') * 60 + DATEPART(mi, '19:00') AND DATEPART(hh, '23:59') * 60 + DATEPART(mi,
'23:59')) OR (DATEPART(hh, dbo.JobData.calltime) * 60 + DATEPART(mi, dbo.JobData.calltime) BETWEEN
DATEPART(hh, '00:00') * 60 + DATEPART(mi, '00:00') AND DATEPART(hh, '06:59') * 60 + DATEPART(mi,
'06:59'))
Michael
CP Blog [^] Development Blog [^]
|
|
|
|
|