can we increment the column --autimaticallyis there any possiblity with tiggers

hi,
i am a beginner to ms sql server2000
i have a table
create table ddd (a int, b int)
by table structure is a b
now when i enter a value in b column suppose '2' in column b
bext time when i insert a value in the column a i have to get the value
in b as 3 is thi spossible with triggers
insert into gdg values (1,2)
a b
1 2
insert into gdg (a) values(2)
a b
2 3--> i have to get this 3 automatically
is there any method to get this
pls help me
satishis this homework?sql

Can we define multiple key columns for a mining structure?

Hi, all,

Just found that we are not able to define multiple key columns for a mining structure in SQL Server 2005 Data Mining engine, just wondering is there other way to define multiple key columns for a mining structure there? As in many cases, the table we are mining are with composite key consisting of different foriengn keys, e.g. A fact table are with transaction information and other foreign keys. If I am not able to define these composite key here for this fact table, I will have to have a named calculation in data source view to have a key column which is based on these original composite keys? Is this a better way to solve this problem or there is any other alternatives to figure it out?

Hope my question is clear for your help and I am looking forward to heaing from you shortly for your kind advices and help and thanks a lot in advance.

With best regards,

Yours sincerely,

Multiple key columns are not permitted in a mining structure. However, you can specify multiple bindings for the Key column in the mining structure: in the Mining Structure tab in BI Dev Studio, click on KeyColumns in the Properties pane and then click on the "..." button in the value field to bring up an editor that allows you add additional bindings. You will also need to specify a NameColumn binding (the next field in Properties) that binds to a source that contains unique names for the composite key you've specified via the multiple bindings under KeyColumns.

The option you suggest (adding a named calculation in the DSV) will also work.

|||

Hi, Raman,

Thanks for your advices.

But still we need a name column whenever we have a muitiple columns bindings for the key attribute?

With best regards,

Yours sincerely,

|||Yes, that's correct - otherwise the server does not know what to name the composite key value (it can't do a simple concatenation because of type differences).|||

Hi, Raman,

Thanks.

Best regards,

Yours sincerely,

Can we change table structure in Mirroring

Can we change table structure in Mirroring. Like adding or deleting columns and tables and how can we do it. Thank you very much.Yes, you can change table structure in Database Mirroring. Changing the structure in the primary database is reflected over the mirror database. Think of database mirroring as "first class" log shipping with automatic failover. Initially, I thought only the data is mirrored since the mirror database is on read-only mode. But testing showed that DDL changes also get "mirrored."|||

Database Mirroring was initially called "Real Time Log Shipping." It is basically taking the log stream and "restoring" it on the mirror server in real time.

So, yes, you can change the table structure in database mirroring.

Thanks,

Mark

Can we change table structure in Mirroring

Can we change table structure in Mirroring. Like adding or deleting columns and tables and how can we do it. Thank you very much.

Yes you can, and you do it just like you would if Mirroring was not being used (i.e. ALTER TABLE, CREATE TABLE, etc.)...DB mirroring moves logged transactions to the mirror as they are applied to the principle, and this includes DDL statements.

HTH,

sql

Can we change table structure in Mirroring

Can we change table structure in Mirroring. Like adding or deleting columns and tables and how can we do it. Thank you very much.

Yes you can, and you do it just like you would if Mirroring was not being used (i.e. ALTER TABLE, CREATE TABLE, etc.)...DB mirroring moves logged transactions to the mirror as they are applied to the principle, and this includes DDL statements.

HTH,

Can u swap the columns after creation of table

hi,
my doubt is --can u swap the columns of the table
ie..,
create table dd(f int ,e int ,g int)
table structure ;
f e g
1 2 4
now i want the columns to be swaped as : e f g
i know i can get the answer by using a select statement
select e,f,g from dd
but i want to get the table struncture as as e f g
when i write -- select * from dd
i have to get the structure as said above
then i have to insert the values can any one help me
satishIt is recommended to never do SELECT * or INSERT without column list in prod
uction code, which makes
that code independent of the order of columns in a table.
Anyhow, no, there is no way to change column order in a table without re-cre
ating the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"satish" <satishkumar.gourabathina@.gmail.com> wrote in message
news:1141642885.735134.249180@.i39g2000cwa.googlegroups.com...
> hi,
> my doubt is --can u swap the columns of the table
> ie..,
> create table dd(f int ,e int ,g int)
> table structure ;
> f e g
> 1 2 4
> now i want the columns to be swaped as : e f g
> i know i can get the answer by using a select statement
> select e,f,g from dd
> but i want to get the table struncture as as e f g
> when i write -- select * from dd
> i have to get the structure as said above
> then i have to insert the values can any one help me
> satish
>|||thanking you for clarifyig my doubt -- i think at the design state
of the data base or table structure -more information to be collected
thanks
satish

Can u swap the columns after creation of table

hi,
my doubt is --can u swap the columns of the table
ie..,
create table dd(f int ,e int ,g int)
table structure ;
f e g
1 2 4
now i want the columns to be swaped as : e f g
i know i can get the answer by using a select statement
select e,f,g from dd
but i want to get the table struncture as as e f g
when i write -- select * from dd
i have to get the structure as said above
then i have to insert the values can any one help me
satish
It is recommended to never do SELECT * or INSERT without column list in production code, which makes
that code independent of the order of columns in a table.
Anyhow, no, there is no way to change column order in a table without re-creating the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"satish" <satishkumar.gourabathina@.gmail.com> wrote in message
news:1141642885.735134.249180@.i39g2000cwa.googlegr oups.com...
> hi,
> my doubt is --can u swap the columns of the table
> ie..,
> create table dd(f int ,e int ,g int)
> table structure ;
> f e g
> 1 2 4
> now i want the columns to be swaped as : e f g
> i know i can get the answer by using a select statement
> select e,f,g from dd
> but i want to get the table struncture as as e f g
> when i write -- select * from dd
> i have to get the structure as said above
> then i have to insert the values can any one help me
> satish
>
|||thanking you for clarifyig my doubt -- i think at the design state
of the data base or table structure -more information to be collected
thanks
satish
sql

Can u swap the columns after creation of table

hi,
my doubt is --can u swap the columns of the table
ie..,
create table dd(f int ,e int ,g int)
table structure ;
f e g
1 2 4
now i want the columns to be swaped as : e f g
i know i can get the answer by using a select statement
select e,f,g from dd
but i want to get the table struncture as as e f g
when i write -- select * from dd
i have to get the structure as said above
then i have to insert the values can any one help me
satishIt is recommended to never do SELECT * or INSERT without column list in production code, which makes
that code independent of the order of columns in a table.
Anyhow, no, there is no way to change column order in a table without re-creating the table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"satish" <satishkumar.gourabathina@.gmail.com> wrote in message
news:1141642885.735134.249180@.i39g2000cwa.googlegroups.com...
> hi,
> my doubt is --can u swap the columns of the table
> ie..,
> create table dd(f int ,e int ,g int)
> table structure ;
> f e g
> 1 2 4
> now i want the columns to be swaped as : e f g
> i know i can get the answer by using a select statement
> select e,f,g from dd
> but i want to get the table struncture as as e f g
> when i write -- select * from dd
> i have to get the structure as said above
> then i have to insert the values can any one help me
> satish
>|||thanking you for clarifyig my doubt -- i think at the design state
of the data base or table structure -more information to be collected
thanks
satish

Can this be optimized? Newbie question

Hi,
I'm running the following SQL to get values for 4 fields. It is
unacceptably slow. I have no control over the structure of the
database, field names, indexes etc. - what I'm given as far as DB
design is all I'm going to get. If anyone could make any suggestions
I'd really appreciate it!

Thanks,
Bill

SELECT DISTINCT
T_MULTILIST_GRADE.grade, T_MULTILIST.description, T_MULTILIST.code,
T_RECEIVING_DETAIL.amount
FROM T_MULTILIST, T_RECEIVING_DETAIL,
T_MULTILIST_GRADE,T_REQUISITION,T_REQUISITION_DETA IL,T_ORDER,T_DEPOSITORY,
T_RECEIVING
WHERE
(
T_RECEIVING_DETAIL.invoice_number =T_RECEIVING.invoice_number
AND T_RECEIVING_DETAIL.order_id =T_ORDER.id
AND T_ORDER.depository_id =T_DEPOSITORY.id
AND T_REQUISITION.id =T_ORDER.requisition_id
AND T_REQUISITION_DETAIL.requisition_id =T_REQUISITION.id
AND T_REQUISITION_DETAIL.multilist_code
=T_MULTILIST_GRADE.multilist_code
AND T_MULTILIST_GRADE.multilist_code =T_MULTILIST.code

AND T_ORDER.requisition_time_stamp BETWEEN '05/31/2005' AND
'06/01/2006'
AND T_MULTILIST.expiration_year > '2005'
AND T_MULTILIST.code IN ('0043','1043')
AND T_DEPOSITORY.depository_type = 'PRIVATE'
AND T_RECEIVING.status <> 'PAID'
)
wgblackmon@.yahoo.com wrote:

> Hi,
> I'm running the following SQL to get values for 4 fields. It is
> unacceptably slow. I have no control over the structure of the
> database, field names, indexes etc. - what I'm given as far as DB
> design is all I'm going to get. If anyone could make any suggestions
> I'd really appreciate it!
> Thanks,
> Bill

Well, it sounds like you're pretty much screwed. How many rows
does the query return? Is it appreciably faster if you remove
the 'DISTINCT' and do you have the opportunity to detect
and ignore duplicates at the client? Can you even find out
what indexes are on the tables or get the query plan for this?
There may be other query criteria that you could drop, and
instead post-qualify rows in the client.
Hope this (or someone else smarter) helps,
Joe Weinstein at BEA Systems

> SELECT DISTINCT
> T_MULTILIST_GRADE.grade, T_MULTILIST.description, T_MULTILIST.code,
> T_RECEIVING_DETAIL.amount
> FROM T_MULTILIST, T_RECEIVING_DETAIL,
> T_MULTILIST_GRADE,T_REQUISITION,T_REQUISITION_DETA IL,T_ORDER,T_DEPOSITORY,
> T_RECEIVING
> WHERE
> (
> T_RECEIVING_DETAIL.invoice_number =T_RECEIVING.invoice_number
> AND T_RECEIVING_DETAIL.order_id =T_ORDER.id
> AND T_ORDER.depository_id =T_DEPOSITORY.id
> AND T_REQUISITION.id =T_ORDER.requisition_id
> AND T_REQUISITION_DETAIL.requisition_id =T_REQUISITION.id
> AND T_REQUISITION_DETAIL.multilist_code
> =T_MULTILIST_GRADE.multilist_code
> AND T_MULTILIST_GRADE.multilist_code =T_MULTILIST.code
> AND T_ORDER.requisition_time_stamp BETWEEN '05/31/2005' AND
> '06/01/2006'
> AND T_MULTILIST.expiration_year > '2005'
> AND T_MULTILIST.code IN ('0043','1043')
> AND T_DEPOSITORY.depository_type = 'PRIVATE'
> AND T_RECEIVING.status <> 'PAID'
> )|||I'm using this query (and up to 20 similar ones combined with 'UNION')
in a Crystal Report. The report may or may not be able to remove dupes,
but I doubt it (I'm new at Crystal Reports). I'm using DBArtisan to
design the query. The database is an undocumented nightmare with few
indexes. I know it's hideous, but I was hoping I was missing something
really obvious...:)|||Bill,

There is nothing wrong with the query, except that maybe the DISTINCT is
not necessary and could save some time if you dropped it.

The key of this query's performance is in the available indexes (and
maybe the hardware configuration). If no usuable indexes are available
and the tables are large then this query will run like a dog. You should
really turn to the DBA who can put the proper indexes in place...

Gert-Jan

"wgblackmon@.yahoo.com" wrote:
> Hi,
> I'm running the following SQL to get values for 4 fields. It is
> unacceptably slow. I have no control over the structure of the
> database, field names, indexes etc. - what I'm given as far as DB
> design is all I'm going to get. If anyone could make any suggestions
> I'd really appreciate it!
> Thanks,
> Bill
> SELECT DISTINCT
> T_MULTILIST_GRADE.grade, T_MULTILIST.description, T_MULTILIST.code,
> T_RECEIVING_DETAIL.amount
> FROM T_MULTILIST, T_RECEIVING_DETAIL,
> T_MULTILIST_GRADE,T_REQUISITION,T_REQUISITION_DETA IL,T_ORDER,T_DEPOSITORY,
> T_RECEIVING
> WHERE
> (
> T_RECEIVING_DETAIL.invoice_number =T_RECEIVING.invoice_number
> AND T_RECEIVING_DETAIL.order_id =T_ORDER.id
> AND T_ORDER.depository_id =T_DEPOSITORY.id
> AND T_REQUISITION.id =T_ORDER.requisition_id
> AND T_REQUISITION_DETAIL.requisition_id =T_REQUISITION.id
> AND T_REQUISITION_DETAIL.multilist_code
> =T_MULTILIST_GRADE.multilist_code
> AND T_MULTILIST_GRADE.multilist_code =T_MULTILIST.code
> AND T_ORDER.requisition_time_stamp BETWEEN '05/31/2005' AND
> '06/01/2006'
> AND T_MULTILIST.expiration_year > '2005'
> AND T_MULTILIST.code IN ('0043','1043')
> AND T_DEPOSITORY.depository_type = 'PRIVATE'
> AND T_RECEIVING.status <> 'PAID'
> )|||T_MULTILIST.description may be wide. Sorting wide result sets may be
slow. try removing duplicates before joining with T_MULTILIST. Look up
article "The Less SQL Server Sorts, the Faster It Responds"|||wgblackmon@.yahoo.com (wgblackmon@.yahoo.com) writes:
> I'm running the following SQL to get values for 4 fields. It is
> unacceptably slow. I have no control over the structure of the
> database, field names, indexes etc. - what I'm given as far as DB
> design is all I'm going to get. If anyone could make any suggestions
> I'd really appreciate it!

My newsserver had an outage, so the reply I posted originally got lost.
What I said in that post was not that fantastic:

Without know the tables and indexes it's about impossible to give
suggestions. If you post the CREATE TABLE and CREATE INDEX statements
(don't forget constraints!), as well some indication of table sizes,
we might be able to give some tips.

Even better if you can run:

SET STATISTICS PROFILE ON
go
-- query goes here
go
SET STATISTICS PROFILE OFF
go

and post the output. (Preferably in an attachment, as the output is far too
wide for news article).

However, a few minutes later one more thing occurred to me, and that was
when I discovered that the newsserver was sick.

Anyway, what you could try is to run DBCC DBREINDEX on all involved tables.
While it is not going to cause the query to run with the speed of light
all of a sudden, you could see an improvement with 20-30% if there is
serious fragmentation of the tables.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

can the talbe name be a parameter of a query line?

the tables are created by month, and the table names are like
wt01,wt02.........
and the structure of tables are all the same,
can the talbe name be a parameter of a query line?
like select * from @.TableName ?
if not, how to make it possible to query different tables by parameter?declare @.tbl sysname
exec ('select * from ' + @.tbl)
OR
="Select * from " & Parameters!Tablename.Value
either should work
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Spirit" wrote:
> the tables are created by month, and the table names are like
> wt01,wt02.........
> and the structure of tables are all the same,
> can the talbe name be a parameter of a query line?
> like select * from @.TableName ?
> if not, how to make it possible to query different tables by parameter?
>|||but when I execute the code in the query Analysis,it reports 170 error
and there is some error near from...|||thank you for your help
it is ok now

Can Query Designer Handle Subqueries`

When a subquery is part of an insert, update or just a from or where clause, it doesn't seem to have a way to structure it. Is there a procedure for that?

Thanks,

DavidHi David -
The only structuring functionality for your scenario is to use the block indent/unident function. You can find these menu items/shortcuts under the Edit menu.

Michael Raheem
Program Manager
SQL Server Tools Team|||Hi David,
One other alternative starting with the APril CTP is to use the query designer within the Query Editor to design each sub-query by selecting the text to design and then issuing the Design Query in Editor command.
Thank you,
Bill Ramos