detecting Identity in INFORMATION_SCHEMA

I'm developing a SQL Utility program intended to work with multiple brands
of database. Right now we just want to get it working with SQL Server. I
am using INFORMATION_SCHEMA to get information on tables, columns, keys,
etc.
But... I can't seem to find where the INFORMATION_SCHEMA tells me if a
primary key is an Identity column.
Where can I look?
Thanks,
T
Tina,
Try using function columnproperty.
Example:
use northwind
go
select TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where columnproperty(object_id(quotename(TABLE_SCHEMA) + '.' +
quotename(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') = 1
go
AMB
"Tina" wrote:

> I'm developing a SQL Utility program intended to work with multiple brands
> of database. Right now we just want to get it working with SQL Server. I
> am using INFORMATION_SCHEMA to get information on tables, columns, keys,
> etc.
> But... I can't seem to find where the INFORMATION_SCHEMA tells me if a
> primary key is an Identity column.
> Where can I look?
> Thanks,
> T
>
>
|||AMB,
Thanks. That worked great!
T
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:6194C838-2DE4-44E0-A895-C99E57E0597A@.microsoft.com...[vbcol=seagreen]
> Tina,
> Try using function columnproperty.
> Example:
> use northwind
> go
> select TABLE_NAME, COLUMN_NAME
> from INFORMATION_SCHEMA.COLUMNS
> where columnproperty(object_id(quotename(TABLE_SCHEMA) + '.' +
> quotename(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') = 1
> go
>
> AMB
> "Tina" wrote:
sql

detecting Identity in INFORMATION_SCHEMA

I'm developing a SQL Utility program intended to work with multiple brands
of database. Right now we just want to get it working with SQL Server. I
am using INFORMATION_SCHEMA to get information on tables, columns, keys,
etc.
But... I can't seem to find where the INFORMATION_SCHEMA tells me if a
primary key is an Identity column.
Where can I look?
Thanks,
TTina,
Try using function columnproperty.
Example:
use northwind
go
select TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where columnproperty(object_id(quotename(TABLE_SCHEMA) + '.' +
quotename(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') = 1
go
AMB
"Tina" wrote:
> I'm developing a SQL Utility program intended to work with multiple brands
> of database. Right now we just want to get it working with SQL Server. I
> am using INFORMATION_SCHEMA to get information on tables, columns, keys,
> etc.
> But... I can't seem to find where the INFORMATION_SCHEMA tells me if a
> primary key is an Identity column.
> Where can I look?
> Thanks,
> T
>
>|||AMB,
Thanks. That worked great!
T
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:6194C838-2DE4-44E0-A895-C99E57E0597A@.microsoft.com...
> Tina,
> Try using function columnproperty.
> Example:
> use northwind
> go
> select TABLE_NAME, COLUMN_NAME
> from INFORMATION_SCHEMA.COLUMNS
> where columnproperty(object_id(quotename(TABLE_SCHEMA) + '.' +
> quotename(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') = 1
> go
>
> AMB
> "Tina" wrote:
>> I'm developing a SQL Utility program intended to work with multiple
>> brands
>> of database. Right now we just want to get it working with SQL Server.
>> I
>> am using INFORMATION_SCHEMA to get information on tables, columns, keys,
>> etc.
>> But... I can't seem to find where the INFORMATION_SCHEMA tells me if a
>> primary key is an Identity column.
>> Where can I look?
>> Thanks,
>> T
>>

detecting Identity in INFORMATION_SCHEMA

I'm developing a SQL Utility program intended to work with multiple brands
of database. Right now we just want to get it working with SQL Server. I
am using INFORMATION_SCHEMA to get information on tables, columns, keys,
etc.
But... I can't seem to find where the INFORMATION_SCHEMA tells me if a
primary key is an Identity column.
Where can I look?
Thanks,
TTina,
Try using function columnproperty.
Example:
use northwind
go
select TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where columnproperty(object_id(quotename(TABLE
_SCHEMA) + '.' +
quotename(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') = 1
go
AMB
"Tina" wrote:

> I'm developing a SQL Utility program intended to work with multiple brands
> of database. Right now we just want to get it working with SQL Server. I
> am using INFORMATION_SCHEMA to get information on tables, columns, keys,
> etc.
> But... I can't seem to find where the INFORMATION_SCHEMA tells me if a
> primary key is an Identity column.
> Where can I look?
> Thanks,
> T
>
>|||AMB,
Thanks. That worked great!
T
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:6194C838-2DE4-44E0-A895-C99E57E0597A@.microsoft.com...[vbcol=seagreen]
> Tina,
> Try using function columnproperty.
> Example:
> use northwind
> go
> select TABLE_NAME, COLUMN_NAME
> from INFORMATION_SCHEMA.COLUMNS
> where columnproperty(object_id(quotename(TABLE
_SCHEMA) + '.' +
> quotename(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') = 1
> go
>
> AMB
> "Tina" wrote:
>

Detect and Passing measure name in Action

Is there a way when launching action to call a URL, I can pass the name of the measure select by users?

We have a cube with multiple measures (included calculated member measures), when a user select a cell under measure A, I need to pass the name A to the URL to ASP.NET application. Similarly, when a user click measure B cell to fire up action, I need to pass measuer name B on URL the the web applicatoin.

Currently, I can pass dimension CurrentMember w/o problem, is there a similar measure CurrentMember function I can use?

Thanks!

CurrentMember should work on the Measures dimension in exactly the same way as on other dimensions, so by using something like Measures.CurrentMember.Name you should be able to get hold of the name of the current measure. Using the UrlEscapeFragment function (see http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!517.entry) will get rid of any url-unfriendly characters.

Chris

Details section does not show all details

I have a data entry application where the user is allowed to enter multiple funding, goals and objective selections for each Project ID record. I have a database that lists each Project by ID and a relational table for each selection and its corresponding project id. For example:

projid type descr

1 Funding Capital

1 Goal PI-1 To develop a project that will come in on schedule

1 Objective PI-1.1 Schedule objective 1

1 Goal T-3 To develop a project that will come in on budget

1 Objective T-3.1 Budget objective 1

1 Objective T-3.2 Budget objective 2

1 Objective T-3.2 Budget objective 3

The report only prints the following in the details section:

projid type descr

1 Funding Capital

1 Goal PI-1 To develop a project that will come in on schedule

1 Objective PI-1.1 Schedule objective 1

Any help is greatly appreciated!

I started a brand new report starting with the details and then adding the two groups and now all records and data shows and sort correctly.

|||

I started a new report and now all related data is showing in the details for each record. But there is another problem...the report sorts by Project Owner then Project Name. If the project name is the same in 2 different records (i.e. Record id = 89 with project name = "Project" and Record id=90 and project name = "Project" the report show record 89 but in the details it shows related detail for record id's 89 and 90 and then doesn't show the individual record id = 90 at all. I don't understand why the details for each record are dependent on the project name. I am new to SQL Reporting and appreciate any help.

Details of SubscriptionClass Class

I just have a question around the SubscriptionClass class. I'm not grasping why there would be multiple rules for a single SubscriptionClass. Can someone give me a good use case for it? It seems to me that since the primary use of Rules are to generate Notifications from Subscriptions. The only reason I can see then is if we used multiple Rules to generate different Notifications. However, then I would assume the Subscription class to be associated with the Subscriber and Notification, not the Subscriber and SubscriptionClass.

A much-related question is: Why is the SubscriptionEventRules (and the SubscriptionConditionEventRules, etc.) a collection? While maybe I could understand being able to choose from a SubscriptionEventRule and a SubscriptionConditionEventRule, I don't understand the multiple SubscriptionEventRules. (And even in the other case, the Subscription doesn't seem to specify a particular rule association).

One final thought is the possibility that the design is for the Subscriber to subscribe to a particular SubscriptionClass and receive all Notifications associated with the SubscriptionClass. Is that the case? If anyone could clarify this for me, it'd be much appreciated.

-Francis

Note that I did see the RuleName property of the Subscription class, but in the SDK it says it's for rules with condition actions.

Designing groups in multiple columns

hello,
> i am designing a grade report and i got a problem, the problem is that me divided the report into two columns, me grouped the records on the basis of semesters. now i want to show the first semester's records in first column and second semester's records in second column, then third semester's records in first column and so on.. but it doesn't hapens so. i want to set the layout of the group but there is not any option to set layout of the group.there is layout option for section but not for group. what me should do ? ? ?
iam desiginging one report for one student. each student have multiple semester and each semester consist of multiple subjects.I hace CR XI, go to details, section expert, layout with multiple columns, you should see a layout tab pop up in the upper right, click on it, you can control the direction the data flows as well as turn on, layout groups with multiple columns.

Design: multiple columns for primary key

Hi,
A very basic question:
Imagine I have a table with multiple fields, some strings, some integers
etc.
None of these fields are unique on their own, but all fields together is
unique per record and thus can be used as the primary key.
However, referring to a record based on all these columns is quite
cumbersome (and not efficient, or does indexing take care of this nearly
entirely compared to a single identity field?), so how is this situation
best solved in practice.
Do I use an ugly IDENTITY column just for 'convenience' or continue to use
all fields, resulting in huge queries that lose legibility, or is there
another way?
LisaLisa Pearlson wrote:

> Do I use an ugly IDENTITY column just for 'convenience' or continue
> to use all fields, resulting in huge queries that lose legibility, or
> is there another way?
I would use "an ugle IDENTITY column" without doubt! Maybe others have
different opinions. It'll make your life a less easier. Otherwise if
you want to refer to that row in a foreign key you need to include all
the columns of the key which isn't really doable.
Kind regards,
Stijn Verrept.|||Lisa Pearlson (no@.spam.plz) writes:
> A very basic question:
> Imagine I have a table with multiple fields, some strings, some integers
> etc.
> None of these fields are unique on their own, but all fields together is
> unique per record and thus can be used as the primary key.
> However, referring to a record based on all these columns is quite
> cumbersome (and not efficient, or does indexing take care of this nearly
> entirely compared to a single identity field?), so how is this situation
> best solved in practice.
> Do I use an ugly IDENTITY column just for 'convenience' or continue to use
> all fields, resulting in huge queries that lose legibility, or is there
> another way?
It doesn't have to be IDENTITY, you can roll your own as well. But judging
from the shallow description you give, it appears that this could be a
solution. But you should add UNIQUE constraint on the other columns as well
to ensure their uniqueness.
I like to point out that adding a surrogate key does not always make things
simpler. I had a table with a four-column key, and then I needed to add a
sub-table with two more keys. Since a six-column key sounded too much, I
added a surrogate key to the main table. Years later I had reason to
write code to maintain these tables. Turned out that the surrogate key
made this a whole lot more complex. So the next time I had revise those
tables, I removed the surrogate key. (I was also able to remove one the
columns in the four-column key, and one of the keys in the sub-table.)
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|||Using the terms "field" and "record" will bring down the wrath of Celko!
I use IDENTITY for a number of reasons, and none of them are for
convenience:
(1) An IDENTITY value is immutable.
(2) Cascading updates are not required to maintain integrity.
(3) IDENTITY reduces redundancy.
(4) Joins generally perform better on IDENTITY values.
(5) IDENTITY reduces lock contention.
There are other reasons, and I could expound further on the merits of each
of the above.
It is not unusual in a logical database design to have tables with compound
primary keys; however, when it comes time to implement the design,
surrogates--including IDENTITY--make a lot more sense.
One more thing: if you do use an IDENTITY PRIMARY KEY, be sure to create a
UNIQUE constraint or index on the combination of columns that are together
unique per row. Alternate keys should be enforced by the database with a
UNIQUE constraint.
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:%23hOXsSz9FHA.3928@.TK2MSFTNGP11.phx.gbl...
> Hi,
> A very basic question:
> Imagine I have a table with multiple fields, some strings, some integers
> etc.
> None of these fields are unique on their own, but all fields together is
> unique per record and thus can be used as the primary key.
> However, referring to a record based on all these columns is quite
> cumbersome (and not efficient, or does indexing take care of this nearly
> entirely compared to a single identity field?), so how is this situation
> best solved in practice.
> Do I use an ugly IDENTITY column just for 'convenience' or continue to use
> all fields, resulting in huge queries that lose legibility, or is there
> another way?
> Lisa
>|||I understand 3, 4 and take 5 for granted (some internal DBMS matter I
presume), but could you elaborate a bit on 1 and 2?
1) immutable means you can't do UPDATE MyTable SET identcol=123 WHERE
identcol=456 ?
2) What does cascading have to do with it? (as I understand cascading, it's
like triggers where change in one record triggers changes in other
tables/records?)
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:OIHDWpz9FHA.3168@.TK2MSFTNGP10.phx.gbl...
> Using the terms "field" and "record" will bring down the wrath of Celko!
> I use IDENTITY for a number of reasons, and none of them are for
> convenience:
> (1) An IDENTITY value is immutable.
> (2) Cascading updates are not required to maintain integrity.
> (3) IDENTITY reduces redundancy.
> (4) Joins generally perform better on IDENTITY values.
> (5) IDENTITY reduces lock contention.
> There are other reasons, and I could expound further on the merits of each
> of the above.
> It is not unusual in a logical database design to have tables with
> compound primary keys; however, when it comes time to implement the
> design, surrogates--including IDENTITY--make a lot more sense.
> One more thing: if you do use an IDENTITY PRIMARY KEY, be sure to create a
> UNIQUE constraint or index on the combination of columns that are together
> unique per row. Alternate keys should be enforced by the database with a
> UNIQUE constraint.
>
> "Lisa Pearlson" <no@.spam.plz> wrote in message
> news:%23hOXsSz9FHA.3928@.TK2MSFTNGP11.phx.gbl...
>|||Lisa Pearlson (no@.spam.plz) writes:
> I understand 3, 4 and take 5 for granted (some internal DBMS matter I
> presume), but could you elaborate a bit on 1 and 2?
> 1) immutable means you can't do UPDATE MyTable SET identcol=123 WHERE
> identcol=456 ?
> 2) What does cascading have to do with it? (as I understand cascading,
> it's like triggers where change in one record triggers changes in other
> tables/records?)
Actually, I think of the reasons that Brian listed, only 5 is really
applicable to IDENTITY columns, although 1 has a touch to it: if you
have an IDENTITY property on the key, you know that the value cannot
be updated, not even by mistake.
Points 2-4 applies to surrogate keys in general, no matter if they have
IDENTITY or not.
I guess what Brian means with cascading is that if you use entirely
natural keys these can change. Say that you get the idea to use the
stock symbol as the key for financial instruments. Then the company changes
the name, and gets a new symbol. With the symbol as key, you have to update
all tables where the symbol appears. With a surrogate key, there is no
need to.
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|||Immutable primary keys yield many benefits.
A mutable primary key will make your code less stable. It's possible in a
concurrent environment to read a row with a particular primary key value and
while you're working on it for another transaction or transactions to make
changes so that when you initiate your update, it appears that the row no
longer exists, or even worse, that the row exists even though it's really
another row. Here's a simple example: Assume that you're tracking a part
as it moves from machine to machine on an assembly line. Assume also that
only one part can be manipulated by one machine at one time. The mutable
primary key is this example is {PartNo, Location}. Now, you read the row
with key {'45G', 'PRESS01'} prior to some manipulation. While you're
working on the row, the part is moved from 'PRESS01' to 'FORMER01', so it
now appears to you that the row no longer exists. If another part '45G' is
moved onto 'PRESS01' prior to your update, then it will appear to you that
the row still exists even though it's a different part, and you may
erroneously update the row for the wrong part. To counter this, you must
either lock and hold the row when you read it (not a very attractive
prospect because it will severely reduce concurrency and will preclude the
use of disconnected datasets, message queues, etc.), or write a ton of code
on the client end to detect the change--which may not always be possible or
practical. Note that this problem increases in complexity when there are
related tables, because it's possible for a row to look the same on the
primary key table, but to actually refer to a different row with a different
set of related rows. With an IDENTITY primary key, as each part is placed
in production a new row is added and a new IDENTITY value is generated.
Because that value cannot change, when you go to perform your update, you
can determine not only that the part has moved, but exactly where it is now.
In addition, the problem with related tables cannot occur, because the
related rows refer to a value that cannot change.
Another problem lies with UPDATE triggers that are used for auditing or to
implement transition constraints. SQL Server update triggers have two
pseudotables, deleted and inserted, which contain the old and new values for
each row that was updated. When an update affects more than one row, there
is no supported mechanism to determine which row in the inserted pseudotable
corresponds to each row in the deleted pseudotable. (Oracle has a FOR EACH
ROW trigger, which I've been begging Microsoft to implement.) IDENTITY
solves this problem because since the key cannot change, you can join the
deleted and inseted pseudotables on the IDENTITY column and determine
exactly what happened to each row.
Cascading updates cause more problems than they're worth.
The most common form of avoidable deadlock is caused by multiple
transactions obtaining and holding locks on rows in more than one table in a
different order. To combat this, you must make sure that you obtain locks
on tables in the same order in every procedure, function, trigger, and
batch. Cascading updates throw a wrench into this. There is no way to
determine with any degree of certainty the order in which related rows will
be locked when there is a cascading heirarchy present. This makes it much
more difficult--if not impossible--to determine a locking order that will
eliminate avoidable deadlocks.
Another problem with cascading updates is that whenever a change is made,
the rowversion (timestamp) on each affected row is updated. Do you really
want to indicate that a Sales Order has been changed when only the
salesperson's employee number has been changed? It makes sense to indicate
a change when the Sales Order is assigned to another salesperson, but in
this case the change is cosmetic, not material, and in my opinion should not
occur.
This brings up another problem: triggers on every affected table throughout
the cascading heirarchy also fire. It's much more difficult to determine
whether they fired for simply a cosmetic change, or if there is a material
change that should be validated against business rules.
All of these problems occur because a database that uses natural primary
keys is riddled with redundancy. For this reason among others, I advocate
the use of surrogate keys and in particular, IDENTITY at the physical level.
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:%23nPAi%2309FHA.3884@.TK2MSFTNGP10.phx.gbl...
>I understand 3, 4 and take 5 for granted (some internal DBMS matter I
>presume), but could you elaborate a bit on 1 and 2?
> 1) immutable means you can't do UPDATE MyTable SET identcol=123 WHERE
> identcol=456 ?
> 2) What does cascading have to do with it? (as I understand cascading,
> it's like triggers where change in one record triggers changes in other
> tables/records?)
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:OIHDWpz9FHA.3168@.TK2MSFTNGP10.phx.gbl...
>|||>> Let's get back to the basics of an RDBMS. Rows are not records; fields ar
e not columns; tables are not files.
If (a,b,c) is a key in the data model, then you have to make it a key
in the schema. Well, you want things screwed.
Ignoring that BY DEFINITION, the proprietary IDENTITY is not a data
type, not a relational and not verifiable, it is redundant in the face
of a real key.
How do you guarantee that you have the IDENTITY and the real key in
synch? Most "ID-iots" do not bother with a real key (they mimic a
sequential file and pointer chains instead) and get redundant rows when
someone posts the same data multiple times.
Answer: it is impossible and therefore data integrity is impossible. I
am just starting to do some SOX consulting work; I will flunk your
database for this. There was some discussion of this at CA a few ws
ago.
A good RDBMS will handle the access for you, so that you do not have to
drop down to that level.|||--CELKO-- (jcelko212@.earthlink.net) writes:
> Ignoring that BY DEFINITION, the proprietary IDENTITY is not a data
> type, not a relational and not verifiable, it is redundant in the face
> of a real key.
New readers should note that this is Joe Celko's private definition, and
thus nothing to bother about.
I could say that by definition Joe Celko is always wrong, but that would
not be a very strong argument. A much stronger argument is that far too many
of his posts consists of inaccurate standard rants, and where the main
rationale for the post is to insult the person who asked.

> How do you guarantee that you have the IDENTITY and the real key in
> synch?
In Lisa's case, it appears to be simple: she should have a UNIQUE
cosntraints on the real key. However, there are plentyful of cases where
there is no real key, or where any "real key" is far beyond being
practically usable.
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|||So, DB2, Oracle, Sybase and Microsoft SQL Server are not 'good RDBMS'? Seems
the market disagrees.
The whole point about using a surrogate key with the IDENTITY property is so
that you gain better performance, reduce complexity for backups, security
etc... as well as being able to change your natural key without having to
build a horrendous transaction of multiple update statements that will lock
up your tables while its being done - usually people cluster on the primary
key (natural key).
People only get redundant rows if they forget to add constraints to their
table, the surrogate key definition would be,..
create table individual (
id int not null identity constraint sk_individual unique
clustered,
last_name varchar(50) not null,
first_name varchar(50) not null,
dob int not null,
constraint pk_individual primary key nonclustered( last_name,
first_name, dob )
)
Mind you, we both know that the natural key on individual isn't the above,
in fact their isn't an easy one - consider a user group like my own, do you
want me to ask people for the NI (social security) number ? I wouldn't get
many members, I'd probably not even get my registration under the Data
Protection act here in the UK passed either.
Well fud - go out and get some bloody experience on real systems please!
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1133661996.695550.257580@.o13g2000cwo.googlegroups.com...
> If (a,b,c) is a key in the data model, then you have to make it a key
> in the schema. Well, you want things screwed.
> Ignoring that BY DEFINITION, the proprietary IDENTITY is not a data
> type, not a relational and not verifiable, it is redundant in the face
> of a real key.
> How do you guarantee that you have the IDENTITY and the real key in
> synch? Most "ID-iots" do not bother with a real key (they mimic a
> sequential file and pointer chains instead) and get redundant rows when
> someone posts the same data multiple times.
> Answer: it is impossible and therefore data integrity is impossible. I
> am just starting to do some SOX consulting work; I will flunk your
> database for this. There was some discussion of this at CA a few ws
> ago.
> A good RDBMS will handle the access for you, so that you do not have to
> drop down to that level.
>

design issue: better speed performance by separating into different databases?

Hi,
From my limited knowledge, I think we can gain some speed performance by
breaking up a database out into multiple groups of tables and create one
database for each group - given that I don't care about the relationship as
much. The reason is, each database's dasta is saved down to a physical data
file (not quite, I realize that we can create filegroups) so sometimes
reading and writing operations must get into each other ways and then end up
get serialized by sqlserver automatically. I'm hoping to get some advice
from the experienced people in this area. Would using multiple filegroups
for a single db is better in speed performance than using multiple
databases?
I'm using sqlserver 2005 Enterprise edition.
Thank you for your comment or advice.Zen,
There are a host of approaches for increasing performance of a database
system. Which one/ones to use are dependent on the circumstances and the
performance issues. Many issues can be resolved my careful analysis and
modification of the query and/or indexing. What performance problem are you
experiencing? Can you narrow it down to make the resolution easier to
identify?
HTH
Jerry
"Zen" <zen@.nononospam.com> wrote in message
news:%23S0j7ZCgGHA.4004@.TK2MSFTNGP04.phx.gbl...
> Hi,
> From my limited knowledge, I think we can gain some speed performance by
> breaking up a database out into multiple groups of tables and create one
> database for each group - given that I don't care about the relationship
> as much. The reason is, each database's dasta is saved down to a physical
> data file (not quite, I realize that we can create filegroups) so
> sometimes reading and writing operations must get into each other ways and
> then end up get serialized by sqlserver automatically. I'm hoping to get
> some advice from the experienced people in this area. Would using multiple
> filegroups for a single db is better in speed performance than using
> multiple databases?
> I'm using sqlserver 2005 Enterprise edition.
> Thank you for your comment or advice.
>|||I don't have performance issue yet, but I'm just designing a solution that
maximize the read operations - so I'm concerned about the write operations
on the shared resource (physical file etc...). It's a small part of the
bigger design. Modification of the query and indexing will be done on top
of what we can do to avoid resource sharing (if it makes perf difference)
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eQTxueCgGHA.3860@.TK2MSFTNGP02.phx.gbl...
> Zen,
> There are a host of approaches for increasing performance of a database
> system. Which one/ones to use are dependent on the circumstances and the
> performance issues. Many issues can be resolved my careful analysis and
> modification of the query and/or indexing. What performance problem are
> you experiencing? Can you narrow it down to make the resolution easier to
> identify?
> HTH
> Jerry
> "Zen" <zen@.nononospam.com> wrote in message
> news:%23S0j7ZCgGHA.4004@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> From my limited knowledge, I think we can gain some speed performance by
>> breaking up a database out into multiple groups of tables and create one
>> database for each group - given that I don't care about the relationship
>> as much. The reason is, each database's dasta is saved down to a physical
>> data file (not quite, I realize that we can create filegroups) so
>> sometimes reading and writing operations must get into each other ways
>> and then end up get serialized by sqlserver automatically. I'm hoping to
>> get some advice from the experienced people in this area. Would using
>> multiple filegroups for a single db is better in speed performance than
>> using multiple databases?
>> I'm using sqlserver 2005 Enterprise edition.
>> Thank you for your comment or advice.
>|||Zen wrote:
> I don't have performance issue yet, but I'm just designing a solution that
> maximize the read operations - so I'm concerned about the write operations
> on the shared resource (physical file etc...). It's a small part of the
> bigger design. Modification of the query and indexing will be done on top
> of what we can do to avoid resource sharing (if it makes perf difference)
>
That is absolutely no reason to consider using separate databases. As
Jerry says, there is a whole range of ways to optimize database
performance and all of them are available to you in a single database.
The valid reasons you might need separate databases are if the data has
different requirements for availability, security, backup or for other
administrative issues, not for performance. I'd also add that you are
going about your design the wrong way if you are considering
performance issues before you've planned and tested a logical database
design.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"Zen" <zen@.nononospam.com> wrote in message
news:%23S0j7ZCgGHA.4004@.TK2MSFTNGP04.phx.gbl...
> Hi,
> From my limited knowledge, I think we can gain some speed performance by
> breaking up a database out into multiple groups of tables and create one
> database for each group - given that I don't care about the relationship
> as much. The reason is, each database's dasta is saved down to a physical
> data file (not quite, I realize that we can create filegroups) so
> sometimes reading and writing operations must get into each other ways and
> then end up get serialized by sqlserver automatically. I'm hoping to get
> some advice from the experienced people in this area. Would using multiple
> filegroups for a single db is better in speed performance than using
> multiple databases?
>
Yes. There is absolutely, positively no reason to use multiple databases
for performance reasons.
Filegroups will allow you to slice up the storage any way you want.
But, no offense meant here, your comments reveal a nearly complete lack of
knoledge of SQL Server internals.
Unless you understand the relationship between the Data Files, the Buffer
Cache and the Transaction Log, and how a query is processed, you shouldn't
make design decisions based on performance. Just create a design that is
simple, models your business domain reasonably well and is reasonably
relationally correct, and performance will usually take care of itself. In
fact, even if you do know all the performance details, you still shoudn't
make design decisions based on performance.
To learn more about how SQL Server stores data and processes queries, there
are some OK topic in Books Online.
Write-Ahead Transaction Log
http://msdn2.microsoft.com/en-us/library/ms186259.aspx
I/O Architecture
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa_3339.asp
And Inside Microsoft SQL Server 2000 is the standard reference. Inside
Microsoft SQL Server 2005 is in the process of beging released in multiple
volumes.
David|||I guess the key part of of my question is: assuming that logical database
design correction is the same, would it make perf difference if they are
separate in different databases? thanks!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1148585796.728951.103090@.38g2000cwa.googlegroups.com...
> Zen wrote:
>> I don't have performance issue yet, but I'm just designing a solution
>> that
>> maximize the read operations - so I'm concerned about the write
>> operations
>> on the shared resource (physical file etc...). It's a small part of the
>> bigger design. Modification of the query and indexing will be done on
>> top
>> of what we can do to avoid resource sharing (if it makes perf difference)
> That is absolutely no reason to consider using separate databases. As
> Jerry says, there is a whole range of ways to optimize database
> performance and all of them are available to you in a single database.
> The valid reasons you might need separate databases are if the data has
> different requirements for availability, security, backup or for other
> administrative issues, not for performance. I'd also add that you are
> going about your design the wrong way if you are considering
> performance issues before you've planned and tested a logical database
> design.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Zen wrote:
> I guess the key part of of my question is: assuming that logical database
> design correction is the same, would it make perf difference if they are
> separate in different databases? thanks!
>
Not really the right question because performance depends on other
factors. Put it this way: using separate databases doesn't give you any
extra options for enhancing performance - for example data can be
partitioned across multiple files even within the same database. So it
would be more accurate to say that you can achieve the same performance
with one database as you can with multiple databases - assuming that
all the other database administration factors are uniform across your
data.
You haven't given us any idea what kind of data volumes you are talking
about. Multiple-terabyte databases are common currency today. If you
are at or approaching terabyte scale however, you should hire some
proper expertise now because mistakes are easily made and can be very
expensive to fix.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Zen,
It "could" in certain situations i.e., distributed partitioned views in a
federation of servers however this is a high-availability solution for
specific demonstrated performance issues and not generally a first pass
optimization technique. I would recommend you stick with one database in
this case.
HTH
Jerry
"Zen" <zen@.nononospam.com> wrote in message
news:O3G$64DgGHA.2456@.TK2MSFTNGP04.phx.gbl...
>I guess the key part of of my question is: assuming that logical database
>design correction is the same, would it make perf difference if they are
>separate in different databases? thanks!
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1148585796.728951.103090@.38g2000cwa.googlegroups.com...
>> Zen wrote:
>> I don't have performance issue yet, but I'm just designing a solution
>> that
>> maximize the read operations - so I'm concerned about the write
>> operations
>> on the shared resource (physical file etc...). It's a small part of the
>> bigger design. Modification of the query and indexing will be done on
>> top
>> of what we can do to avoid resource sharing (if it makes perf
>> difference)
>>
>> That is absolutely no reason to consider using separate databases. As
>> Jerry says, there is a whole range of ways to optimize database
>> performance and all of them are available to you in a single database.
>> The valid reasons you might need separate databases are if the data has
>> different requirements for availability, security, backup or for other
>> administrative issues, not for performance. I'd also add that you are
>> going about your design the wrong way if you are considering
>> performance issues before you've planned and tested a logical database
>> design.
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>|||Jerry Spivey wrote:
> Zen,
> It "could" in certain situations i.e., distributed partitioned views in a
> federation of servers however this is a high-availability solution for
> specific demonstrated performance issues and not generally a first pass
> optimization technique. I would recommend you stick with one database in
> this case.
> HTH
>
Ah, that's a good point. If "separate databases" also means "separate
servers" then there obviously might be a reason for doing it.
The point I was trying to make was this: If you take one database and
naively make an identical copy of that database on the same server then
you won't achieve anything useful. True, you will have created at least
two new files (one data, one log) and depending on the placement of
those files you may perhaps see some performance impact. But then you
might have achieved the same thing by creating those extra files in one
database instead of two. So performance always depends on things other
than the number of databases. As you say, it depends on the number of
servers for instance :-)
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

design issue: better speed performance by separating into different databases?

Hi,
From my limited knowledge, I think we can gain some speed performance by
breaking up a database out into multiple groups of tables and create one
database for each group - given that I don't care about the relationship as
much. The reason is, each database's dasta is saved down to a physical data
file (not quite, I realize that we can create filegroups) so sometimes
reading and writing operations must get into each other ways and then end up
get serialized by sqlserver automatically. I'm hoping to get some advice
from the experienced people in this area. Would using multiple filegroups
for a single db is better in speed performance than using multiple
databases?
I'm using sqlserver 2005 Enterprise edition.
Thank you for your comment or advice.Zen,
There are a host of approaches for increasing performance of a database
system. Which one/ones to use are dependent on the circumstances and the
performance issues. Many issues can be resolved my careful analysis and
modification of the query and/or indexing. What performance problem are you
experiencing? Can you narrow it down to make the resolution easier to
identify?
HTH
Jerry
"Zen" <zen@.nononospam.com> wrote in message
news:%23S0j7ZCgGHA.4004@.TK2MSFTNGP04.phx.gbl...
> Hi,
> From my limited knowledge, I think we can gain some speed performance by
> breaking up a database out into multiple groups of tables and create one
> database for each group - given that I don't care about the relationship
> as much. The reason is, each database's dasta is saved down to a physical
> data file (not quite, I realize that we can create filegroups) so
> sometimes reading and writing operations must get into each other ways and
> then end up get serialized by sqlserver automatically. I'm hoping to get
> some advice from the experienced people in this area. Would using multiple
> filegroups for a single db is better in speed performance than using
> multiple databases?
> I'm using sqlserver 2005 Enterprise edition.
> Thank you for your comment or advice.
>|||I don't have performance issue yet, but I'm just designing a solution that
maximize the read operations - so I'm concerned about the write operations
on the shared resource (physical file etc...). It's a small part of the
bigger design. Modification of the query and indexing will be done on top
of what we can do to avoid resource sharing (if it makes perf difference)
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eQTxueCgGHA.3860@.TK2MSFTNGP02.phx.gbl...
> Zen,
> There are a host of approaches for increasing performance of a database
> system. Which one/ones to use are dependent on the circumstances and the
> performance issues. Many issues can be resolved my careful analysis and
> modification of the query and/or indexing. What performance problem are
> you experiencing? Can you narrow it down to make the resolution easier to
> identify?
> HTH
> Jerry
> "Zen" <zen@.nononospam.com> wrote in message
> news:%23S0j7ZCgGHA.4004@.TK2MSFTNGP04.phx.gbl...
>|||Zen wrote:
> I don't have performance issue yet, but I'm just designing a solution that
> maximize the read operations - so I'm concerned about the write operations
> on the shared resource (physical file etc...). It's a small part of the
> bigger design. Modification of the query and indexing will be done on top
> of what we can do to avoid resource sharing (if it makes perf difference)
>
That is absolutely no reason to consider using separate databases. As
Jerry says, there is a whole range of ways to optimize database
performance and all of them are available to you in a single database.
The valid reasons you might need separate databases are if the data has
different requirements for availability, security, backup or for other
administrative issues, not for performance. I'd also add that you are
going about your design the wrong way if you are considering
performance issues before you've planned and tested a logical database
design.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"Zen" <zen@.nononospam.com> wrote in message
news:%23S0j7ZCgGHA.4004@.TK2MSFTNGP04.phx.gbl...
> Hi,
> From my limited knowledge, I think we can gain some speed performance by
> breaking up a database out into multiple groups of tables and create one
> database for each group - given that I don't care about the relationship
> as much. The reason is, each database's dasta is saved down to a physical
> data file (not quite, I realize that we can create filegroups) so
> sometimes reading and writing operations must get into each other ways and
> then end up get serialized by sqlserver automatically. I'm hoping to get
> some advice from the experienced people in this area. Would using multiple
> filegroups for a single db is better in speed performance than using
> multiple databases?
>
Yes. There is absolutely, positively no reason to use multiple databases
for performance reasons.
Filegroups will allow you to slice up the storage any way you want.
But, no offense meant here, your comments reveal a nearly complete lack of
knoledge of SQL Server internals.
Unless you understand the relationship between the Data Files, the Buffer
Cache and the Transaction Log, and how a query is processed, you shouldn't
make design decisions based on performance. Just create a design that is
simple, models your business domain reasonably well and is reasonably
relationally correct, and performance will usually take care of itself. In
fact, even if you do know all the performance details, you still shoudn't
make design decisions based on performance.
To learn more about how SQL Server stores data and processes queries, there
are some OK topic in Books Online.
Write-Ahead Transaction Log
http://msdn2.microsoft.com/en-us/library/ms186259.aspx
I/O Architecture
http://msdn.microsoft.com/library/d...br />
3339.asp
And Inside Microsoft SQL Server 2000 is the standard reference. Inside
Microsoft SQL Server 2005 is in the process of beging released in multiple
volumes.
David|||I guess the key part of of my question is: assuming that logical database
design correction is the same, would it make perf difference if they are
separate in different databases? thanks!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1148585796.728951.103090@.38g2000cwa.googlegroups.com...
> Zen wrote:
> That is absolutely no reason to consider using separate databases. As
> Jerry says, there is a whole range of ways to optimize database
> performance and all of them are available to you in a single database.
> The valid reasons you might need separate databases are if the data has
> different requirements for availability, security, backup or for other
> administrative issues, not for performance. I'd also add that you are
> going about your design the wrong way if you are considering
> performance issues before you've planned and tested a logical database
> design.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Zen wrote:
> I guess the key part of of my question is: assuming that logical database
> design correction is the same, would it make perf difference if they are
> separate in different databases? thanks!
>
Not really the right question because performance depends on other
factors. Put it this way: using separate databases doesn't give you any
extra options for enhancing performance - for example data can be
partitioned across multiple files even within the same database. So it
would be more accurate to say that you can achieve the same performance
with one database as you can with multiple databases - assuming that
all the other database administration factors are uniform across your
data.
You haven't given us any idea what kind of data volumes you are talking
about. Multiple-terabyte databases are common currency today. If you
are at or approaching terabyte scale however, you should hire some
proper expertise now because mistakes are easily made and can be very
expensive to fix.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Zen,
It "could" in certain situations i.e., distributed partitioned views in a
federation of servers however this is a high-availability solution for
specific demonstrated performance issues and not generally a first pass
optimization technique. I would recommend you stick with one database in
this case.
HTH
Jerry
"Zen" <zen@.nononospam.com> wrote in message
news:O3G$64DgGHA.2456@.TK2MSFTNGP04.phx.gbl...
>I guess the key part of of my question is: assuming that logical database
>design correction is the same, would it make perf difference if they are
>separate in different databases? thanks!
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1148585796.728951.103090@.38g2000cwa.googlegroups.com...
>|||Jerry Spivey wrote:
> Zen,
> It "could" in certain situations i.e., distributed partitioned views in a
> federation of servers however this is a high-availability solution for
> specific demonstrated performance issues and not generally a first pass
> optimization technique. I would recommend you stick with one database in
> this case.
> HTH
>
Ah, that's a good point. If "separate databases" also means "separate
servers" then there obviously might be a reason for doing it.
The point I was trying to make was this: If you take one database and
naively make an identical copy of that database on the same server then
you won't achieve anything useful. True, you will have created at least
two new files (one data, one log) and depending on the placement of
those files you may perhaps see some performance impact. But then you
might have achieved the same thing by creating those extra files in one
database instead of two. So performance always depends on things other
than the number of databases. As you say, it depends on the number of
servers for instance :-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

DESIGN FLAWS IN MANAGEMENT STUDIO ??

Greetings,
Please tell me we don't have to live with this annoying thing where if
you try to open multiple .sql files it opens a new instance of
Management Studio and prompts you to connect to a server'
I JUST want to modify some sql scripts dangitt.
If so, MS please get on the ball to get this corrected. I am switching
my .sql files back to opening in Visual Studio and refuse to use this
tool until corrected. In addition it seems very bloated and load times
for the application are annoying. Am I the only one seeing this?
Thanks,
MikeI agree Mike. I like the way that Enterprise Manager and Query Analyzer are
integrated into one tool, but Management Studio is clunky and much slower
than the old tools. It drives me nuts when I open a SQL file, and MS creates
a new tabbed window and connection instead of opening the file in my existing
window.
Also, when generating the script for a table, you no longer gives you the
ability to leave out the junk you don't want, like collation, unless you go
through the script wizard, which is slow. I just want to show the script for
a single table for crying out loud.
I just installed SP1 hoping that they changed some of these things, but it
looks like Microsoft ignored Management Studio.
Tom
"mike.mcneer@.gmail.com" wrote:
> Greetings,
> Please tell me we don't have to live with this annoying thing where if
> you try to open multiple .sql files it opens a new instance of
> Management Studio and prompts you to connect to a server'
> I JUST want to modify some sql scripts dangitt.
> If so, MS please get on the ball to get this corrected. I am switching
> my .sql files back to opening in Visual Studio and refuse to use this
> tool until corrected. In addition it seems very bloated and load times
> for the application are annoying. Am I the only one seeing this?
> Thanks,
> Mike
>|||http://lab.msdn.microsoft.com/productfeedback/Default.aspx
You can file bugs as well as suggestions out here. Filing a suggestion that
basically says "it sucks, fix it" is a quick way to be completely ignored.
Detail exactly what you are seeing, what you expect it to do, and what it
actually does.
Then the nice thing about this site? The entire world has the ability to
vote on suggestions as well as post comments also. If enough people agree
that something should be done, it will get done. Having messages scattered
across a newsgroup related to product improvements isn't going to help
determine what is important to all of us and what isn't.
I personally don't find an issue with the performance of Management Studio.
BUT, I use Query Analyzer for my day to day SQL Server 2005 tasks. It's
like any managed code application I've ever come across, it is VERY slow the
first time it is launched, because all kinds of assemblies and other stuff
have to be loaded. Everytime I start it after that, it takes very little
time to launch. So, I launch SSMS when I start my machine up and since I
don't reboot it unless something forces me to, it get decent performance out
of SSMS for weeks at a time.
As for the connection dialog, well, it didn't work that way initially. Back
in the early betas, SSMS would launch without ever requiring a connection
and scripts would also open up without requiring a connection. However, a
bunch of people screamed about it not being connected and those voices were
a lot louder than the ones who didn't want to always connect to an instance,
so SSMS now pops up a dialog to connect to an instance when you launch it.
If enough people want it the other way, it isn't that hard to make the
change.
Also, you are taking about functionality changes. There isn't an actual bug
in anything that you pointed out. So, don't expect anything to be done
about it until the next version. They are very careful to not introduce new
functionality, which is what you are asking for, in a service pack.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:E68D3248-4B78-4BC7-9B40-319A660C29C2@.microsoft.com...
>I agree Mike. I like the way that Enterprise Manager and Query Analyzer
>are
> integrated into one tool, but Management Studio is clunky and much slower
> than the old tools. It drives me nuts when I open a SQL file, and MS
> creates
> a new tabbed window and connection instead of opening the file in my
> existing
> window.
> Also, when generating the script for a table, you no longer gives you the
> ability to leave out the junk you don't want, like collation, unless you
> go
> through the script wizard, which is slow. I just want to show the script
> for
> a single table for crying out loud.
> I just installed SP1 hoping that they changed some of these things, but it
> looks like Microsoft ignored Management Studio.
> Tom
> "mike.mcneer@.gmail.com" wrote:
>> Greetings,
>> Please tell me we don't have to live with this annoying thing where if
>> you try to open multiple .sql files it opens a new instance of
>> Management Studio and prompts you to connect to a server'
>> I JUST want to modify some sql scripts dangitt.
>> If so, MS please get on the ball to get this corrected. I am switching
>> my .sql files back to opening in Visual Studio and refuse to use this
>> tool until corrected. In addition it seems very bloated and load times
>> for the application are annoying. Am I the only one seeing this?
>> Thanks,
>> Mike
>>|||On Fri, 28 Apr 2006 09:07:02 -0700, Tom wrote:
> It drives me nuts when I open a SQL file, and MS creates
>a new tabbed window and connection instead of opening the file in my existing
>window.
Hi Tom,
What would yu prefer, then? Would you want MS to discard whatever is
currently in the active query window and replace it with the contents of
the file you double-clicked? What would your reaction be if you lost two
hours worth of query-writing because you accidentally doouble-clicked a
SQL file?
BTW, I have set notepad to be the default application for the .sql
suffix. Maybe an idea for you too?
>Also, when generating the script for a table, you no longer gives you the
>ability to leave out the junk you don't want, like collation, unless you go
>through the script wizard, which is slow. I just want to show the script for
>a single table for crying out loud.
Agreed - this is a major pain.
--
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:60u4521197dgmlfjkvhn0uo88h7tdhsm5c@.4ax.com...
> On Fri, 28 Apr 2006 09:07:02 -0700, Tom wrote:
> > It drives me nuts when I open a SQL file, and MS creates
> >a new tabbed window and connection instead of opening the file in my
existing
> >window.
> Hi Tom,
> What would yu prefer, then? Would you want MS to discard whatever is
> currently in the active query window and replace it with the contents of
> the file you double-clicked? What would your reaction be if you lost two
> hours worth of query-writing because you accidentally doouble-clicked a
> SQL file?
>
Not sure what exactly he's referring to, but I know what annoys me.
With QA with SQL 2000, if I had say 5 .SQL files in a directory and opened
each one (double clicking) it would open a new window in the existing copy
of QA (I believe using the current connection info).
Now, with SMS, it opens a new COPY of SMS each time. Which is much slower
and a royal pain.
I've also noticed that for some strange reason I seem to "mistype" the
password in SMS when I try to connect a LOT more than in QA. It's very
weird, it's almost like it doesn't see me type a character, or is missing it
when I hit the shift key. I know at least 1-2 others that have said they
have a similar experience.
> BTW, I have set notepad to be the default application for the .sql
> suffix. Maybe an idea for you too?
That's probably what I'm going to do.
> >Also, when generating the script for a table, you no longer gives you the
> >ability to leave out the junk you don't want, like collation, unless you
go
> >through the script wizard, which is slow. I just want to show the script
for
> >a single table for crying out loud.
> Agreed - this is a major pain.
> --
> Hugo Kornelis, SQL Server MVP|||On Fri, 28 Apr 2006 21:57:42 -0400, Greg D. Moore (Strider) wrote:
(snip)
>With QA with SQL 2000, if I had say 5 .SQL files in a directory and opened
>each one (double clicking) it would open a new window in the existing copy
>of QA (I believe using the current connection info).
>Now, with SMS, it opens a new COPY of SMS each time. Which is much slower
>and a royal pain.
Hi Greg,
Strange. If I restore the original default action for .SQL files and
doubleclick one, it loads in a new tab in my open SSMS (though it does
bug me for connection details).
If I drag across a bunch of .SQL files to select them alll and hit
enter, than I do get several instances of SSMS. One of the query loads
in the existing SSMS, the others all load a new instance. Not good.
>I've also noticed that for some strange reason I seem to "mistype" the
>password in SMS when I try to connect a LOT more than in QA.
I use Windows Authentication, so that's not a problem for me. :-)
--
Hugo Kornelis, SQL Server MVP|||I agree totally :-( Something went terribly wrong in the year 2005. Not only
this, but also Visausl Studio 2005 has serious problems :-(
PS: I still use Query analyzer: much much faster :-)|||thanks guys, I agree I shouldn't have probably griped too much but, I
just wanted to make sure I wasn't the only one dealing with this and
maybe there was a way around other than default my sql files back to
something else. When you have 40 Storedp rocedures, 10 triggers and
table change scripts for a build every other week you need something
speedy and quick to get those opened and compiled.

DESIGN FLAWS IN MANAGEMENT STUDIO ??

Greetings,
Please tell me we don't have to live with this annoying thing where if
you try to open multiple .sql files it opens a new instance of
Management Studio and prompts you to connect to a server'
I JUST want to modify some sql scripts dangitt.
If so, MS please get on the ball to get this corrected. I am switching
my .sql files back to opening in Visual Studio and refuse to use this
tool until corrected. In addition it seems very bloated and load times
for the application are annoying. Am I the only one seeing this?
Thanks,
MikeI agree Mike. I like the way that Enterprise Manager and Query Analyzer are
integrated into one tool, but Management Studio is clunky and much slower
than the old tools. It drives me nuts when I open a SQL file, and MS create
s
a new tabbed window and connection instead of opening the file in my existin
g
window.
Also, when generating the script for a table, you no longer gives you the
ability to leave out the junk you don't want, like collation, unless you go
through the script wizard, which is slow. I just want to show the script fo
r
a single table for crying out loud.
I just installed SP1 hoping that they changed some of these things, but it
looks like Microsoft ignored Management Studio.
Tom
"mike.mcneer@.gmail.com" wrote:

> Greetings,
> Please tell me we don't have to live with this annoying thing where if
> you try to open multiple .sql files it opens a new instance of
> Management Studio and prompts you to connect to a server'
> I JUST want to modify some sql scripts dangitt.
> If so, MS please get on the ball to get this corrected. I am switching
> my .sql files back to opening in Visual Studio and refuse to use this
> tool until corrected. In addition it seems very bloated and load times
> for the application are annoying. Am I the only one seeing this?
> Thanks,
> Mike
>|||http://lab.msdn.microsoft.com/produ...ck/Default.aspx
You can file bugs as well as suggestions out here. Filing a suggestion that
basically says "it sucks, fix it" is a quick way to be completely ignored.
Detail exactly what you are seeing, what you expect it to do, and what it
actually does.
Then the nice thing about this site? The entire world has the ability to
vote on suggestions as well as post comments also. If enough people agree
that something should be done, it will get done. Having messages scattered
across a newsgroup related to product improvements isn't going to help
determine what is important to all of us and what isn't.
I personally don't find an issue with the performance of Management Studio.
BUT, I use Query Analyzer for my day to day SQL Server 2005 tasks. It's
like any managed code application I've ever come across, it is VERY slow the
first time it is launched, because all kinds of assemblies and other stuff
have to be loaded. Everytime I start it after that, it takes very little
time to launch. So, I launch SSMS when I start my machine up and since I
don't reboot it unless something forces me to, it get decent performance out
of SSMS for weeks at a time.
As for the connection dialog, well, it didn't work that way initially. Back
in the early betas, SSMS would launch without ever requiring a connection
and scripts would also open up without requiring a connection. However, a
bunch of people screamed about it not being connected and those voices were
a lot louder than the ones who didn't want to always connect to an instance,
so SSMS now pops up a dialog to connect to an instance when you launch it.
If enough people want it the other way, it isn't that hard to make the
change.
Also, you are taking about functionality changes. There isn't an actual bug
in anything that you pointed out. So, don't expect anything to be done
about it until the next version. They are very careful to not introduce new
functionality, which is what you are asking for, in a service pack.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:E68D3248-4B78-4BC7-9B40-319A660C29C2@.microsoft.com...[vbcol=seagreen]
>I agree Mike. I like the way that Enterprise Manager and Query Analyzer
>are
> integrated into one tool, but Management Studio is clunky and much slower
> than the old tools. It drives me nuts when I open a SQL file, and MS
> creates
> a new tabbed window and connection instead of opening the file in my
> existing
> window.
> Also, when generating the script for a table, you no longer gives you the
> ability to leave out the junk you don't want, like collation, unless you
> go
> through the script wizard, which is slow. I just want to show the script
> for
> a single table for crying out loud.
> I just installed SP1 hoping that they changed some of these things, but it
> looks like Microsoft ignored Management Studio.
> Tom
> "mike.mcneer@.gmail.com" wrote:
>|||On Fri, 28 Apr 2006 09:07:02 -0700, Tom wrote:

> It drives me nuts when I open a SQL file, and MS creates
>a new tabbed window and connection instead of opening the file in my existi
ng
>window.
Hi Tom,
What would yu prefer, then? Would you want MS to discard whatever is
currently in the active query window and replace it with the contents of
the file you double-clicked? What would your reaction be if you lost two
hours worth of query-writing because you accidentally doouble-clicked a
SQL file?
BTW, I have set notepad to be the default application for the .sql
suffix. Maybe an idea for you too?

>Also, when generating the script for a table, you no longer gives you the
>ability to leave out the junk you don't want, like collation, unless you go
>through the script wizard, which is slow. I just want to show the script f
or
>a single table for crying out loud.
Agreed - this is a major pain.
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:60u4521197dgmlfjkvhn0uo88h7tdhsm5c@.
4ax.com...
> On Fri, 28 Apr 2006 09:07:02 -0700, Tom wrote:
>
existing[vbcol=seagreen]
> Hi Tom,
> What would yu prefer, then? Would you want MS to discard whatever is
> currently in the active query window and replace it with the contents of
> the file you double-clicked? What would your reaction be if you lost two
> hours worth of query-writing because you accidentally doouble-clicked a
> SQL file?
>
Not sure what exactly he's referring to, but I know what annoys me.
With QA with SQL 2000, if I had say 5 .SQL files in a directory and opened
each one (double clicking) it would open a new window in the existing copy
of QA (I believe using the current connection info).
Now, with SMS, it opens a new COPY of SMS each time. Which is much slower
and a royal pain.
I've also noticed that for some strange reason I seem to "mistype" the
password in SMS when I try to connect a LOT more than in QA. It's very
weird, it's almost like it doesn't see me type a character, or is missing it
when I hit the shift key. I know at least 1-2 others that have said they
have a similar experience.

> BTW, I have set notepad to be the default application for the .sql
> suffix. Maybe an idea for you too?
That's probably what I'm going to do.

>
go[vbcol=seagreen]
for[vbcol=seagreen]
> Agreed - this is a major pain.
> --
> Hugo Kornelis, SQL Server MVP|||On Fri, 28 Apr 2006 21:57:42 -0400, Greg D. Moore (Strider) wrote:
(snip)
>With QA with SQL 2000, if I had say 5 .SQL files in a directory and opened
>each one (double clicking) it would open a new window in the existing copy
>of QA (I believe using the current connection info).
>Now, with SMS, it opens a new COPY of SMS each time. Which is much slower
>and a royal pain.
Hi Greg,
Strange. If I restore the original default action for .SQL files and
doubleclick one, it loads in a new tab in my open SSMS (though it does
bug me for connection details).
If I drag across a bunch of .SQL files to select them alll and hit
enter, than I do get several instances of SSMS. One of the query loads
in the existing SSMS, the others all load a new instance. Not good.

>I've also noticed that for some strange reason I seem to "mistype" the
>password in SMS when I try to connect a LOT more than in QA.
I use Windows Authentication, so that's not a problem for me. :-)
Hugo Kornelis, SQL Server MVP|||I agree totally :-( Something went terribly wrong in the year 2005. Not only
this, but also Visausl Studio 2005 has serious problems :-(
PS: I still use Query analyzer: much much faster :-)|||thanks guys, I agree I shouldn't have probably griped too much but, I
just wanted to make sure I wasn't the only one dealing with this and
maybe there was a way around other than default my sql files back to
something else. When you have 40 Storedp rocedures, 10 triggers and
table change scripts for a build every other week you need something
speedy and quick to get those opened and compiled.

Design advise

I have been given a DB that currently stores Organizations. I need to add the ability to track multiple contacts for the Organizations. The DB has several one-to-many relationships on the Organizations table, such as Documents and Notes. I would like to track these by contact as well as the organization.

Is it possible to add a tblcontact and a one-to-many relationships to the Organizations table as well as to the tblDocuments and tblNotes?

I appreciate any suggestions.

tblOrg (OrgID)

tblDocuments (DocID, OrgID)

tblNotes (NoteID, OrgID)

tblContact ?

I also wanted to mention that we would be tracking organiztions and individuals.

I appreciate any suggestions on how to add to the existing Database.

|||

Hi Jack,

sure we can add a relation between the tblContact and tblOrg.

If your Documents and Notes belongs to a contact not Org, you need to create on-to-many relationship from tblContact to tblDocuments. When you need to track documents of an Org, you can first get the contacts and then get their docs.

|||I think I'm going to stick with the one org table and put everyone in that table. I will add a way to determine if the record is an organization or an individual (record type). I can create a relation table that contains the followng four fields (orgid, relationshipid, relatedorgid, reciprocalid).

Design - Multiple field relationships between 2 tables

This is a design question but I couldn't find the appropriate newsgroup for
SQL Server Design so I am posting this here.
I have 2 tables Customer and Company. Customer has fields like
AntiVirusCompanyID, EmailCompanyID, SpamFilteringCompanyID and
WebFilteringCompanyID . All these company ID's are stored in the same Compan
y
lookup table. In most cases (not all), the same CompanyID is used for each o
f
the companyID fields in customers.
One way, of course, is to maintain 4 different lookup tables for
AntiVirusCompany, EmailCompany, SpamFilteringCompany and WebFilteringCompany
and do joins from each of them to their corresponding CompanyID's in the
Customer table but this seems problematic because
1) I need to create 4 new tables
2) Duplication of information across the 4 tables
3) 4 joins while retreiving data.
Is there a better way to implement this scenario?
Thanks,
NaveenIf the CompanyIDs are already stored in the one Company table, why would you
need to create four new tables?
Are you asking how to write the JOIN syntax for this, in which case it's
always best to provide the actual DDL for the tables involved? How about :
SELECT CU.<Field>,
COAV.CompanyName AS AVCompany,
COEM.CompanyName AS EmailCompany,
COSP.CompanyName AS SpamFilteringCompany,
COWF.CompanyName AS WebFilteringCompany
FROM Customer AS CU
JOIN CompanyName AS COAV ON CU.AntiVirusCompanyID = COAV.CompanyID
JOIN CompanyName AS COEM ON CU.EmailCompanyID = COEM.CompanyID
JOIN CompanyName AS COSP ON CU.SpamFilteringCompanyID = COSP.CompanyID
JOIN CompanyName AS COWF ON CU.WebFilteringCompanyID = COWF.CompanyID
HTH
Michael MacGregor
Database Architect
"Naveen" <Naveen@.discussions.microsoft.com> wrote in message
news:35A647D6-4BE5-448C-B313-F058C6AC9C86@.microsoft.com...
> This is a design question but I couldn't find the appropriate newsgroup
for
> SQL Server Design so I am posting this here.
> I have 2 tables Customer and Company. Customer has fields like
> AntiVirusCompanyID, EmailCompanyID, SpamFilteringCompanyID and
> WebFilteringCompanyID . All these company ID's are stored in the same
Company
> lookup table. In most cases (not all), the same CompanyID is used for each
of
> the companyID fields in customers.
> One way, of course, is to maintain 4 different lookup tables for
> AntiVirusCompany, EmailCompany, SpamFilteringCompany and
WebFilteringCompany
> and do joins from each of them to their corresponding CompanyID's in the
> Customer table but this seems problematic because
> 1) I need to create 4 new tables
> 2) Duplication of information across the 4 tables
> 3) 4 joins while retreiving data.
> Is there a better way to implement this scenario?
> Thanks,
> Naveen|||No, you're doing it right with only one table. When you write a query, join
ti that same table four times, once for each FK column in the Customer Table
,
and alias the results ofeach join as a sdifferent alias, (IN THE QUERY)
as in
Select C.Name Customer,
A.CompanyName AntiVirusCompany,
E.CompanyName EMailCompany,
S.CompanyName SpamCompany,
W.CompanyName WebCompany
From Customer C
Left Join Company A -- For Anti-Virus
On A.CompanyID = C.AntiVirusCompanyID
Left Join Company E -- For eMail
On E.CompanyID = C.EmailCompanyID
Left Join Company Sp-- For Spam
On S.CompanyID = C.SpamFilteringCompanyID
Left Join Company W -- For Web
On W.CompanyID = C.WebFilteringCompanyID
"Naveen" wrote:

> This is a design question but I couldn't find the appropriate newsgroup fo
r
> SQL Server Design so I am posting this here.
> I have 2 tables Customer and Company. Customer has fields like
> AntiVirusCompanyID, EmailCompanyID, SpamFilteringCompanyID and
> WebFilteringCompanyID . All these company ID's are stored in the same Comp
any
> lookup table. In most cases (not all), the same CompanyID is used for each
of
> the companyID fields in customers.
> One way, of course, is to maintain 4 different lookup tables for
> AntiVirusCompany, EmailCompany, SpamFilteringCompany and WebFilteringCompa
ny
> and do joins from each of them to their corresponding CompanyID's in the
> Customer table but this seems problematic because
> 1) I need to create 4 new tables
> 2) Duplication of information across the 4 tables
> 3) 4 joins while retreiving data.
> Is there a better way to implement this scenario?
> Thanks,
> Naveen|||Thanks for the answer. It resolves what I was asking. Thanks to Michael too.
Michael:
My refined question (after reading your answers) was if I should set a
foreign key relationship in the rdbms design stage itself between the 2
tables or if i should do a dynamic join whenever I need to access these
values. Obviously from both your answers it seems a dynamic join makes more
sense. Thanks.
"CBretana" wrote:
> No, you're doing it right with only one table. When you write a query, jo
in
> ti that same table four times, once for each FK column in the Customer Tab
le,
> and alias the results ofeach join as a sdifferent alias, (IN THE QUERY)
> as in
> Select C.Name Customer,
> A.CompanyName AntiVirusCompany,
> E.CompanyName EMailCompany,
> S.CompanyName SpamCompany,
> W.CompanyName WebCompany
> From Customer C
> Left Join Company A -- For Anti-Virus
> On A.CompanyID = C.AntiVirusCompanyID
> Left Join Company E -- For eMail
> On E.CompanyID = C.EmailCompanyID
> Left Join Company Sp-- For Spam
> On S.CompanyID = C.SpamFilteringCompanyID
> Left Join Company W -- For Web
> On W.CompanyID = C.WebFilteringCompanyID
> "Naveen" wrote:
>|||Oh you can set up multiple FKs from one table to another that isn't a
problem, in fact it's a good idea for any developers or DBAs who come after
you, just make sure you name each one appropriately.
Michael MacGregor
Database Architect
"Naveen" <Naveen@.discussions.microsoft.com> wrote in message
news:80CD40AA-5959-4BDD-ABBA-6A0435954288@.microsoft.com...
> Thanks for the answer. It resolves what I was asking. Thanks to Michael
too.
> Michael:
> My refined question (after reading your answers) was if I should set a
> foreign key relationship in the rdbms design stage itself between the 2
> tables or if i should do a dynamic join whenever I need to access these
> values. Obviously from both your answers it seems a dynamic join makes
more
> sense. Thanks.
> "CBretana" wrote:
>
join
Table,
newsgroup for
Company
each of
WebFilteringCompany
the|||Naveen,
Actually, you shopuld do both The DRI constraint should be tehre to validate
data being entered, tio ensure that all the FK values exist, and that no
orphan recrods are created. The dynamicx join is just for the purpose of
EXTRACTING data.
"Naveen" wrote:
> Thanks for the answer. It resolves what I was asking. Thanks to Michael to
o.
> Michael:
> My refined question (after reading your answers) was if I should set a
> foreign key relationship in the rdbms design stage itself between the 2
> tables or if i should do a dynamic join whenever I need to access these
> values. Obviously from both your answers it seems a dynamic join makes mor
e
> sense. Thanks.
> "CBretana" wrote:
>