Query

Hello folks,

Im new in database bussines. I will appreciate if someone can help me.
I need to insert new records to a table. First of all I dont know what is the syntax for insert command in SQL Server2000. Also one of the fields to insert is changing every second so I think I will need to include in the insert command a nested select statement.
For example,

The table has the ID field and this field is the one is changing every time. If in the n second I do a select to know what value such field has I could use the value equal to ID + 1. But while I build query, type and execute it will be the second n+1, so probably the value for ID now is already equal to ID + 1 and my query will fail because the duplicated record error.

I hope is clear what the proble I have,

Regards,You can look in BooksOnline (comes with the sql server) for the syntax on insert statements.
If your changing field is the ID for a record, you can use auto numbering on this field. This means that sql server itself will keep track of the value of the field and makes sure there are no duplicates.

hth|||So can I just use:

insert into table_name values(ID,'MNAME','F_NAME')...

I was thinking that some other query was needed to be introduced to get the new value for ID field, ie.

insert into table_name values(((select ID from TABLE)+1),'M_NAME','F_NAME')

Thanks|||Not completely correct yet, your insert statement. If the ID column is set to auto numbering, you don't include it in the insert statement. Your statement will be:
insert into table_name values('MNAME','F_NAME')|||Thanks, How I can know if the field is autonumbering?

How I can get the table fields with all it properties using SQL Command?

In db2 it was with command describe table...
It is the same?

Regards,|||in sql server, there is a stored proeedure for this information called 'sp_columns'. See BOL for more information. You can also find information about using auto numbering in BOL, just search on 'identity'.sql

Quer runs well on its own but when put into stored procedure, it runs slowly

Hi,

Appreciate if any one can provide some pointers on this issue on a stored procedure that I have.

The required indexes (both clustered and non-clustered) have been created in the database tables.

When the T-SQL statements in the stored procedure are run in the SQL Query directly, it performs well and returns the results back within seconds.

However, the stored procedure is run in the SQL Query window directly, it takes minutes before it returns.

When the execution plan is examined for both, they are both different and it appears that when executing the stored procedure, it doesn't make use of the indexes, whereas in the first case of executing the same T-SQL statements directly, it does make use of the indexes.

We tried using the WITH RECOMPILE attribute to refresh the execution plan, but it doesn't seem to work for the stored procedure.

Wonder if anyone got any ideas or encounter anything similar?

Thanks,

Desmond

Can you post the SP and the DDL/Indexes?

|||

Hi Desmond T,

> When the T-SQL statements in the stored procedure are run in the SQL Query directly, it performs well and

> returns the results back within seconds.

> However, the stored procedure is run in the SQL Query window directly, it takes minutes before it returns.

Can you show us some light, please?

Look in BOL for auto-parameterization and also about the statistics used by the query optimizer. If you executed the "select" statement from QA or SSMS, using variables to simulate the parameters in the stored procedure, like in:

Code Snippet

use northwind

go

select orderid, customerid, orderdate

into dbo.t1

from dbo.orders

go

create index t1_orderdate_nu_nc_ix

on dbo.t1(orderdate)

go

dbcc show_statistics('dbo.t1', 't1_orderdate_nu_nc_ix') with STAT_HEADER, DENSITY_VECTOR, HISTOGRAM

go

create procedure dbo.usp_p1

@.orderdate datetime

as

set nocount on

select orderid, customerid, orderdate

from dbo.t1

where orderdate = @.orderdate

go

dbcc freeproccache

dbcc dropcleanbuffers

go

declare @.orderdate datetime

set @.orderdate = '19960704'

select orderid, customerid, orderdate

from dbo.t1

where orderdate = @.orderdate

go

dbcc freeproccache

dbcc dropcleanbuffers

go

exec dbo.usp_p1 '19960704'

go

drop table dbo.t1

go

drop procedure dbo.usp_p1

go

They seem to be the same, but the query optimizer uses different statistics to estimate cardinality and based on those statistics, it will choose an execution plan. In the sp case, the query procesor will use the values from the histogram, but for the query, it will use the "All density" value for that column and could be that those values yield different execution plan.

In my box, I got these values for "Estimated Number of Rows".

sp --> 1

query using variables --> 1.72917 (0.002083333 * 830) See "All density" value from the result of dbcc

The query optimizer chose to scan the table for the query using variables and an "index seek" operation followed by bookmark lookup for the sp.

One way of testing the query in QA / SSMS is using sp_executesql, parameterizing the statement.

Code Snippet

declare @.sql nvarchar(4000)

set @.sql = N'select orderid, customerid, orderdate from dbo.t1 where orderdate = @.orderdate'

exec sp_executesql @.sql, N'@.orderdate datetime', '19960704'

go

Statistics Used by the Query Optimizer in Microsoft SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx

AMB

|||hi,

i had a similar problem before, can you post your proc?, i resolved my problem by tweaking the proc a bit.

/rh4m1ll3

QA/SSMS results caching

I am not sure this is the right forum for this question, but I would appreciate any pointers towards more appropriate forums.

I am attempting to replicate the data browsing performance of Microsoft's QA/SSMS in my own application. Even for very large datasets, one can typically scroll through a few hundred thousands rows without a problem. For queries this large, a generic DataGridView setup would take forever. Digging around using Process Explorer, I saw that QA/SSMS was loading the results into a (what I presume to be a memory-mapped) temporary file, which it used to cache the results for display.

Is there an example of how this is being done? I am assuming it is using unmanaged code, as I am not aware of any interfaces to memory mapped files in .NET. I don't have much experience here, so I would appreciate any help. Thanks.

Did you use the DataGridView with VirtualMode On ? You can even use pagination for the GridView so you don′t need to pull out all data at once from the server. We used that a lot and its pretty usable.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

QA/SSMS results caching

I am not sure this is the right forum for this question, but I would appreciate any pointers towards more appropriate forums.

I am attempting to replicate the data browsing performance of Microsoft's QA/SSMS in my own application. Even for very large datasets, one can typically scroll through a few hundred thousands rows without a problem. For queries this large, a generic DataGridView setup would take forever. Digging around using Process Explorer, I saw that QA/SSMS was loading the results into a (what I presume to be a memory-mapped) temporary file, which it used to cache the results for display.

Is there an example of how this is being done? I am assuming it is using unmanaged code, as I am not aware of any interfaces to memory mapped files in .NET. I don't have much experience here, so I would appreciate any help. Thanks.

Did you use the DataGridView with VirtualMode On ? You can even use pagination for the GridView so you don′t need to pull out all data at once from the server. We used that a lot and its pretty usable.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de