Duplicate values

I have a table with 6 columns. I want to create a primary key for the first
two columns but I am getting a message that I already have duplicate values
in the combination of the two column.
Can someone help me with SQL to find the duplicate rows on two columns of
the same table ?
Thanks.SELECT T1.col1, T1.col2, T1.col3, T1.col4, T1.col5, T1.col6
FROM YourTable AS T1,
(SELECT col1, col2
FROM YourTable
GROUP BY col1, col2
HAVING COUNT(*)>1) AS T2
WHERE T1.col1 = T2.col2
AND T1.col1 = T2.col2
(untested)
David Portas
SQL Server MVP
--|||CORRECTION:
...
WHERE T1.col1 = T2.col1
AND T1.col2 = T2.col2
David Portas
SQL Server MVP
--|||Thanks......I found it........Now, I need to find a way to remove the
duplicates (Leave only one row).
Thanks.
"David Portas" wrote:

> CORRECTION:
> ...
> WHERE T1.col1 = T2.col1
> AND T1.col2 = T2.col2
>
> --
> David Portas
> SQL Server MVP
> --
>
>

Duplicate values

I have a table with 6 columns. I want to create a primary key for the first
two columns but I am getting a message that I already have duplicate values
in the combination of the two column.
Can someone help me with SQL to find the duplicate rows on two columns of
the same table ?
Thanks.
SELECT T1.col1, T1.col2, T1.col3, T1.col4, T1.col5, T1.col6
FROM YourTable AS T1,
(SELECT col1, col2
FROM YourTable
GROUP BY col1, col2
HAVING COUNT(*)>1) AS T2
WHERE T1.col1 = T2.col2
AND T1.col1 = T2.col2
(untested)
David Portas
SQL Server MVP
|||CORRECTION:
...
WHERE T1.col1 = T2.col1
AND T1.col2 = T2.col2
David Portas
SQL Server MVP
|||Thanks......I found it........Now, I need to find a way to remove the
duplicates (Leave only one row).
Thanks.
"David Portas" wrote:

> CORRECTION:
> ...
> WHERE T1.col1 = T2.col1
> AND T1.col2 = T2.col2
>
> --
> David Portas
> SQL Server MVP
> --
>
>

Duplicate values

I have a table with 6 columns. I want to create a primary key for the first
two columns but I am getting a message that I already have duplicate values
in the combination of the two column.
Can someone help me with SQL to find the duplicate rows on two columns of
the same table ?
Thanks.SELECT T1.col1, T1.col2, T1.col3, T1.col4, T1.col5, T1.col6
FROM YourTable AS T1,
(SELECT col1, col2
FROM YourTable
GROUP BY col1, col2
HAVING COUNT(*)>1) AS T2
WHERE T1.col1 = T2.col2
AND T1.col1 = T2.col2
(untested)
--
David Portas
SQL Server MVP
--|||CORRECTION:
...
WHERE T1.col1 = T2.col1
AND T1.col2 = T2.col2
David Portas
SQL Server MVP
--|||Thanks......I found it........Now, I need to find a way to remove the
duplicates (Leave only one row).
Thanks.
"David Portas" wrote:
> CORRECTION:
> ...
> WHERE T1.col1 = T2.col1
> AND T1.col2 = T2.col2
>
> --
> David Portas
> SQL Server MVP
> --
>
>

Duplicate tables

Hi
Is there a way for duplicating tables in two databases?
I have a db1 with 3 tables, and I would like to create 3 similar tables in
my db2 (same structure).
Is there a tool to make this easy?
The easiest way, IMO, is just to script the tables using Query Analyzer and
create them in the new database. Right-click on the table in the Object
Browser, select Script Object to New Window As, and click Create. Now just
change databases and apply the script.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Gonzalo Torres" <condormix2001@.yahoo.com.mx> wrote in message
news:%23pD0Ogc8EHA.3476@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is there a way for duplicating tables in two databases?
> I have a db1 with 3 tables, and I would like to create 3 similar tables in
> my db2 (same structure).
> Is there a tool to make this easy?
>
|||If you wish to include indexes, constraints, triggers, etc, use SQL
Enterprise Manager, Right Click your database ->All tasks_>Generate SQL
Script
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Gonzalo Torres" <condormix2001@.yahoo.com.mx> wrote in message
news:%23pD0Ogc8EHA.3476@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is there a way for duplicating tables in two databases?
> I have a db1 with 3 tables, and I would like to create 3 similar tables in
> my db2 (same structure).
> Is there a tool to make this easy?
>
|||Hi Gonzalo,
Simplest way is using SELECT INTO (check BOL). This will create table
(without index, constraints, foreign keys...) with the data.
Other methods are:
(a) Using BCP tool
(b) Script the table, apply it and then use INSERT INTO
Thanks
GYK

Duplicate tables

Hi
Is there a way for duplicating tables in two databases?
I have a db1 with 3 tables, and I would like to create 3 similar tables in
my DB2 (same structure).
Is there a tool to make this easy?The easiest way, IMO, is just to script the tables using Query Analyzer and
create them in the new database. Right-click on the table in the Object
Browser, select Script Object to New Window As, and click Create. Now just
change databases and apply the script.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Gonzalo Torres" <condormix2001@.yahoo.com.mx> wrote in message
news:%23pD0Ogc8EHA.3476@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is there a way for duplicating tables in two databases?
> I have a db1 with 3 tables, and I would like to create 3 similar tables in
> my DB2 (same structure).
> Is there a tool to make this easy?
>|||If you wish to include indexes, constraints, triggers, etc, use SQL
Enterprise Manager, Right Click your database ->All tasks_>Generate SQL
Script
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Gonzalo Torres" <condormix2001@.yahoo.com.mx> wrote in message
news:%23pD0Ogc8EHA.3476@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is there a way for duplicating tables in two databases?
> I have a db1 with 3 tables, and I would like to create 3 similar tables in
> my DB2 (same structure).
> Is there a tool to make this easy?
>|||Hi Gonzalo,
Simplest way is using SELECT INTO (check BOL). This will create table
(without index, constraints, foreign keys...) with the data.
Other methods are:
(a) Using BCP tool
(b) Script the table, apply it and then use INSERT INTO
Thanks
GYK

Duplicate tables

Hi
Is there a way for duplicating tables in two databases?
I have a db1 with 3 tables, and I would like to create 3 similar tables in
my db2 (same structure).
Is there a tool to make this easy?The easiest way, IMO, is just to script the tables using Query Analyzer and
create them in the new database. Right-click on the table in the Object
Browser, select Script Object to New Window As, and click Create. Now just
change databases and apply the script.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Gonzalo Torres" <condormix2001@.yahoo.com.mx> wrote in message
news:%23pD0Ogc8EHA.3476@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is there a way for duplicating tables in two databases?
> I have a db1 with 3 tables, and I would like to create 3 similar tables in
> my db2 (same structure).
> Is there a tool to make this easy?
>|||If you wish to include indexes, constraints, triggers, etc, use SQL
Enterprise Manager, Right Click your database ->All tasks_>Generate SQL
Script
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Gonzalo Torres" <condormix2001@.yahoo.com.mx> wrote in message
news:%23pD0Ogc8EHA.3476@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is there a way for duplicating tables in two databases?
> I have a db1 with 3 tables, and I would like to create 3 similar tables in
> my db2 (same structure).
> Is there a tool to make this easy?
>|||Hi Gonzalo,
Simplest way is using SELECT INTO (check BOL). This will create table
(without index, constraints, foreign keys...) with the data.
Other methods are:
(a) Using BCP tool
(b) Script the table, apply it and then use INSERT INTO
--
Thanks
GYK

duplicate table

Hi,

I have a table with data in it, (tblT1)

A. How do I create a table tblT2 with the same columns in tblT1, but without data in (blank tblT2 table with columns).

B. And also how do I copy the enitre table's data (tblT1) into tblT2.

Thank you,

Give a look in books online to SELECT ... INTO.

Here are some previous posts:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=126194&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307375&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=691090&SiteID=1

|||

Got it . I will check it out.

Thank you.

Duplicate Foreign keys.

Hi all,

SQL server allows to create as many as foreign key constraints on a same table for a same column.

Will this affect the design or performance in anyway ?

Naming the constraint would be a good way to avoid this.But in case if someone has already created, How do I remove the existing duplicate keys ?

======================
For Example , I have 2 tables Author and Book. I could execute the below query n times and create as many as foreign keys I want.

ALTER TABLE Books
ADD
FOREIGN KEY (AuthorID)
REFERENCES Authors (AuthorID)

======================

Thanks is advance,

DBAnalyst

this may affect the performance. foreign keys shud be named..eg

ALTER TABLE Books with nocheck

ADD constraint fkname

FOREIGN KEY (AuthorID)

REFERENCES Authors (AuthorID).

if u want to check the existing foreign keys to check ne duplicacy use the REFERENTIAL_CONSTRAINTS view of information_schema..

select * from information_schema.REFERENTIAL_CONSTRAINTS

Duplicate Entries

I have an issue where certain parts of data are repeated several times after i create my query. Without providing my SQL code for now could anyone suggest possibly the main reason(s) for data being duplicated?

Thanks

I reckon its because either.....

a) you have duplicate data in your tables

b) you are doing a JOIN to a table with a one -> many relationship

And as a solution i reckon you could....

a) use DISTINCT in your select statement

Am I close?

|||

I think your very close:

a) Definately not as i recreated another query without all the fields that i originally needed and had NO duplicate items.

b) This maybe it but ill have to investigate it and get back to you.

Based on your answers and solution

1. How can i easily define which tables may have 1-Many relationship?

2. Where would i use the Distinct statement?

Thanks

|||

1. Don't think you can programmatically find out 1->Many relationships. Hopefully you have your relationships defined by foreign keys which should point you in the right direction. Maybe use sp_fkeys to help identify them eg EXEC sp_fkey 'YourTable', 'dbo'

2. SELECT DISTINCT Col1, Col2, Col3.....

Check Books Online for more details.

sql

Duplicate an existing SQL Login Account

How does one create a copy of an existing SQL login account. I have a
database that has a single login with everything setup. I just want to be
able to copy/duplicate the existing account to a new user so I can set a
different
password.
Thanks!You might be able to get what you want by "Generating SQL Script" from
the right-click context menu on the database you want to add the user
to. Place the checkmarks on the first tab to create all types of
objects, uncheck the options to create the CREATE and DROP statements on
the second tab, and go to the 3rd tab and check "Script object-level
permissions".
When you build the script, it should include commands that grant the
permissions to the current account that holds the permissions. You will
have to manually pick through the script commands to find the
appropriate ones and change the name of the user to replace the old
user's username with the new one to build an entirely new script.
While not the answer you're looking for, I think you should consider
creating one or more roles configured to grant permissions that match
the account that is configured properly now, then create the new account
and add the both user accounts to the role you created.
Good luck,
Tony Sebion
"John Williams" <JohnWilliams@.discussions.microsoft.com> wrote in
message news:F0AEB5FA-2AFD-4785-9217-184B22E3CFE0@.microsoft.com:

> How does one create a copy of an existing SQL login account. I have a
> database that has a single login with everything setup. I just want to be
> able to copy/duplicate the existing account to a new user so I can set a
> different
> password.
> Thanks!|||Scripting it out will work, the only downside would be remebering to
propogate all changes from user to user as things change. And things
always do change...
I'd recommend setting the permissions that are on user account to a
role instead. Then you can assign the role to the users that need
those permissions.
If needed you can deny access to objects on a user by user basis if
necessary or create another role that has those denied permissions and
assign that to the users.
HTH
Jason Strate

Duplicate an existing SQL Login Account

How does one create a copy of an existing SQL login account. I have a
database that has a single login with everything setup. I just want to be
able to copy/duplicate the existing account to a new user so I can set a
different
password.
Thanks!
You might be able to get what you want by "Generating SQL Script" from
the right-click context menu on the database you want to add the user
to. Place the checkmarks on the first tab to create all types of
objects, uncheck the options to create the CREATE and DROP statements on
the second tab, and go to the 3rd tab and check "Script object-level
permissions".
When you build the script, it should include commands that grant the
permissions to the current account that holds the permissions. You will
have to manually pick through the script commands to find the
appropriate ones and change the name of the user to replace the old
user's username with the new one to build an entirely new script.
While not the answer you're looking for, I think you should consider
creating one or more roles configured to grant permissions that match
the account that is configured properly now, then create the new account
and add the both user accounts to the role you created.
Good luck,
Tony Sebion
"John Williams" <JohnWilliams@.discussions.microsoft.com> wrote in
message news:F0AEB5FA-2AFD-4785-9217-184B22E3CFE0@.microsoft.com:

> How does one create a copy of an existing SQL login account. I have a
> database that has a single login with everything setup. I just want to be
> able to copy/duplicate the existing account to a new user so I can set a
> different
> password.
> Thanks!
|||Scripting it out will work, the only downside would be remebering to
propogate all changes from user to user as things change. And things
always do change...
I'd recommend setting the permissions that are on user account to a
role instead. Then you can assign the role to the users that need
those permissions.
If needed you can deny access to objects on a user by user basis if
necessary or create another role that has those denied permissions and
assign that to the users.
HTH
Jason Strate

Duplicate an existing SQL Login Account

How does one create a copy of an existing SQL login account. I have a
database that has a single login with everything setup. I just want to be
able to copy/duplicate the existing account to a new user so I can set a
different
password.
Thanks!You might be able to get what you want by "Generating SQL Script" from
the right-click context menu on the database you want to add the user
to. Place the checkmarks on the first tab to create all types of
objects, uncheck the options to create the CREATE and DROP statements on
the second tab, and go to the 3rd tab and check "Script object-level
permissions".
When you build the script, it should include commands that grant the
permissions to the current account that holds the permissions. You will
have to manually pick through the script commands to find the
appropriate ones and change the name of the user to replace the old
user's username with the new one to build an entirely new script.
While not the answer you're looking for, I think you should consider
creating one or more roles configured to grant permissions that match
the account that is configured properly now, then create the new account
and add the both user accounts to the role you created.
Good luck,
Tony Sebion
"John Williams" <JohnWilliams@.discussions.microsoft.com> wrote in
message news:F0AEB5FA-2AFD-4785-9217-184B22E3CFE0@.microsoft.com:
> How does one create a copy of an existing SQL login account. I have a
> database that has a single login with everything setup. I just want to be
> able to copy/duplicate the existing account to a new user so I can set a
> different
> password.
> Thanks!|||Scripting it out will work, the only downside would be remebering to
propogate all changes from user to user as things change. And things
always do change...
I'd recommend setting the permissions that are on user account to a
role instead. Then you can assign the role to the users that need
those permissions.
If needed you can deny access to objects on a user by user basis if
necessary or create another role that has those denied permissions and
assign that to the users.
HTH
Jason Strate

Duplicate an existig SQL user login account

How does one create a copy of an existing SQL login account. I have a
database that has a single login with everything setup. I just want to be
able to copy the existing account to a new user so I can set a different
password.
Thanks!You might be able to get what you want by "Generating SQL Script" from
the right-click context menu on the database you want to add the user
to. Place the checkmarks on the first tab to create all types of
objects, uncheck the options to create the CREATE and DROP statements on
the second tab, and go to the 3rd tab and check "Script object-level
permissions".
When you build the script, it should include commands that grant the
permissions to the current account that holds the permissions. You will
have to manually pick through the script commands to find the
appropriate ones and change the name of the user to replace the old
user's username with the new one to build an entirely new script.
While not the answer you're looking for, I think you should consider
creating one or more roles configured to grant permissions that match
the account that is configured properly now, then create the new account
and add the both user accounts to the role you created.
Good luck,
Tony Sebion
"John Williams" <JohnWilliams@.discussions.microsoft.com> wrote in
message news:5EA1D83E-15E9-44B9-9707-C4AAB5C9CDB6@.microsoft.com:

> How does one create a copy of an existing SQL login account. I have a
> database that has a single login with everything setup. I just want to be
> able to copy the existing account to a new user so I can set a different
> password.
> Thanks!sql

Dumb trigger question

Table with last UpdatedBy Column and LastUpdatedOn Column.
Want to create Insert and Update Triggers that would automatically write
Logged in User Name and System Date Time to each field.
I Think User is SUSER_SNAME function and System date Is GETDATE() but can't
figure out how to write these two to row from within a trigger in table.
Any help greatly appreciated.
BobYou can use the inserted psuedo-table to identify the updated rows. One
method:
CREATE TRIGGER TR__Update_MyTable
ON MyTable FOR UPDATE
AS
UPDATE MyTable
SET UpdatedBy = SUSER_SNAME(),
LastUpdatedOn = GETDATE()
WHERE EXISTS
(
SELECT *
FROM inserted
WHERE inserted.PK = MyTable.PK
)
Hope this helps.
Dan Guzman
SQL Server MVP
"Bob" <bdufour@.sgiims.com> wrote in message
news:uwISI5NzFHA.3188@.TK2MSFTNGP14.phx.gbl...
> Table with last UpdatedBy Column and LastUpdatedOn Column.
> Want to create Insert and Update Triggers that would automatically write
> Logged in User Name and System Date Time to each field.
> I Think User is SUSER_SNAME function and System date Is GETDATE() but
> can't figure out how to write these two to row from within a trigger in
> table.
> Any help greatly appreciated.
> Bob
>|||Use an INSTEAD OF trigger. The deleted pseudotable is a copy of any
existing rows that are about to be updated, the inserted pseudotable
contains the new values. The deleted pseudotable will be empty if the
operation was INSERT. The inserted pseudotable will be empty if the
operation was DELETE. Both pseudotables will have the same number of rows
if the operation was UPDATE. The reason you should use an INSTEAD OF
trigger is that it allows you to change values before it hits the database.
The only problem with them is that the cascade kludge won't coexist, which
in my opinion is a good thing.
In an INSTEAD OF UPDATE trigger, you simply issue an update statement:
UPDATE tableName
SET column1 = inserted.column1,
column2 = inserted.column2,
..,
UpdatedBy = SUSER_SNAME(),
LastUpdatedOn = GETDATE()
FROM inserted
WHERE primaryKeyColumn = inserted.primaryKeyColumn
The only time this is a problem is if you're using natural keys. If that's
the case, then you need to test for multiple rows
put code similar to this at the top of the trigger
--Don't put anything before this!!!
DECLARE @._ROWCOUNT INT SET @._ROWCOUNT = @.@.ROWCOUNT
IF @._ROWCOUNT = 0 RETURN
IF @._ROWCOUNT > 1 AND (UPDATE(primaryKeyColumn1) OR
UPDATE(primaryKeyColumn2))
BEGIN
ROLLBACK TRANSACTION
RAISERROR('ATTEMPT TO UPDATE Primary Key using set-based operation', 16, 0)
RETURN
END
"Bob" <bdufour@.sgiims.com> wrote in message
news:uwISI5NzFHA.3188@.TK2MSFTNGP14.phx.gbl...
> Table with last UpdatedBy Column and LastUpdatedOn Column.
> Want to create Insert and Update Triggers that would automatically write
> Logged in User Name and System Date Time to each field.
> I Think User is SUSER_SNAME function and System date Is GETDATE() but
> can't figure out how to write these two to row from within a trigger in
> table.
> Any help greatly appreciated.
> Bob
>|||Thank you both very much
Bob
"Bob" <bdufour@.sgiims.com> wrote in message
news:uwISI5NzFHA.3188@.TK2MSFTNGP14.phx.gbl...
> Table with last UpdatedBy Column and LastUpdatedOn Column.
> Want to create Insert and Update Triggers that would automatically write
> Logged in User Name and System Date Time to each field.
> I Think User is SUSER_SNAME function and System date Is GETDATE() but
> can't figure out how to write these two to row from within a trigger in
> table.
> Any help greatly appreciated.
> Bob
>

Dumb questions on attribute relationships and multiple hierachy relationships

Hi,

This is a couple of general questions about how to create robust attribute hierarchies. I'm sure the answera are somewhere on the MS web sites, but I can't find it.

1) How precisely does the inclusion of attribute relationships affect the design of aggregations and the resulting speed?

I'm led to believe that defining the attribute relationships will effectively force AS to use these hierarchies when creating aggregations, and so putting them in improves the speed. This seems to be the case - any known best practices?

2) What do all the properties such as "Fully Optimised" for attributes mean?

3) Imagine have, for example, a time dimension table which has Year, Month, Week, Date columns on it, where weeks do not roll into months, but weeks and months roll into years. I build two user defined hierarchies Year-Month-Date and Year-Week-Date, then when I try to put attribute relationships on both these tables, I can get odd results, for example all the weeks appearing in the first year only.

This may be finger trouble, or complete stupidity, but has anyone else seen this? The actual case was not time as above, but was a similar structure.

As ever, thanks for all the input.

Richard

1) If you defined relationship between say your State and your City. And if you are asking for the State data, you can get answer from aggregation that contains City, or from cache created as result of City request. Now if you've created relationships for all the attributes in your dimensions, you need few aggregations to cover big space of potential requests. And therefore you can achieve performance. It is almost imperative to create attribute relationships.

2) Fully Optimized tells the server whenever to build indexes for this attribute or not. Sometimes you might want your attribute to exist but to be hidden. In this case makes sense to tell the server not to create indexes for it.

3) Not sure what you mean here. If you have correct relationships in the relational database, you should get right results when you browse you cube. Please note, when you define your relationships Analysis Server will try and find a parent for every individual week member. For instance if you have "Week 1 Dec 2001" it will have Dec 2001 parent in Week-Month hierarchy , or 2001 in Week-Year hierarchy.

Edward Melomed (MSFT)

---

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

|||To add a comment on 3: This normally indicates a problem with a key. AS 2005 is very sensitive with keys. "1" may be a bad key for a week... "200601" is a good key... (the same applies to months)...|||

refering to question #3

It sounds like you have not defined the key correctly. It needs to be unique.

so in the case of your hierarchy of year>month>week>date you need to define the key

for month and week to also include the year. In AS2005 you cannot rely on the hierarchy

to define a unique attribute. If you do a search on time dimension you should get much

more details on how to correctly define it.

Dumb questions on attribute relationships and multiple hierachy relationships

Hi,

This is a couple of general questions about how to create robust attribute hierarchies. I'm sure the answera are somewhere on the MS web sites, but I can't find it.

1) How precisely does the inclusion of attribute relationships affect the design of aggregations and the resulting speed?

I'm led to believe that defining the attribute relationships will effectively force AS to use these hierarchies when creating aggregations, and so putting them in improves the speed. This seems to be the case - any known best practices?

2) What do all the properties such as "Fully Optimised" for attributes mean?

3) Imagine have, for example, a time dimension table which has Year, Month, Week, Date columns on it, where weeks do not roll into months, but weeks and months roll into years. I build two user defined hierarchies Year-Month-Date and Year-Week-Date, then when I try to put attribute relationships on both these tables, I can get odd results, for example all the weeks appearing in the first year only.

This may be finger trouble, or complete stupidity, but has anyone else seen this? The actual case was not time as above, but was a similar structure.

As ever, thanks for all the input.

Richard

1) If you defined relationship between say your State and your City. And if you are asking for the State data, you can get answer from aggregation that contains City, or from cache created as result of City request. Now if you've created relationships for all the attributes in your dimensions, you need few aggregations to cover big space of potential requests. And therefore you can achieve performance. It is almost imperative to create attribute relationships.

2) Fully Optimized tells the server whenever to build indexes for this attribute or not. Sometimes you might want your attribute to exist but to be hidden. In this case makes sense to tell the server not to create indexes for it.

3) Not sure what you mean here. If you have correct relationships in the relational database, you should get right results when you browse you cube. Please note, when you define your relationships Analysis Server will try and find a parent for every individual week member. For instance if you have "Week 1 Dec 2001" it will have Dec 2001 parent in Week-Month hierarchy , or 2001 in Week-Year hierarchy.

Edward Melomed (MSFT)

---

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

|||To add a comment on 3: This normally indicates a problem with a key. AS 2005 is very sensitive with keys. "1" may be a bad key for a week... "200601" is a good key... (the same applies to months)...|||

refering to question #3

It sounds like you have not defined the key correctly. It needs to be unique.

so in the case of your hierarchy of year>month>week>date you need to define the key

for month and week to also include the year. In AS2005 you cannot rely on the hierarchy

to define a unique attribute. If you do a search on time dimension you should get much

more details on how to correctly define it.

dumb question

Hello, I'm an MSDE n00b but I do have a basic understanding of SQL Server
2000. I have used Enterprise manager to create/manage users/permissions and
replication before. I am however not familiar with MSDE. I am using MSDE
because I'm playing with asp.net. Since there is no user interface I am
lost. I created my tables and stored procedures via the Web Matrix asp.net
development tool.
1) I need to configure the MSDE 2000 installation user access. Its currently
setup for integrated windows authentication, which I want to keep. But how
do I add/manage the users and their permissions. For example with Enterprise
Manager, even with Integrated authentication, you have to add the
users/groups from your windows domain before SQL server can use them. Do I
need to do this in MSDE? how?
2) how can I view current user accounts on the system? and their
permissions?
3) while I'm at it here... how can I retrieve the currently logged in user
from my asp.net web application?
any info is appreciated. Thanks.
If you have a copy of Enterprise Manager on a different machine you should be
able to connect to the msde install and see it in the familar enterprise
manager.
"djc" wrote:

> Hello, I'm an MSDE n00b but I do have a basic understanding of SQL Server
> 2000. I have used Enterprise manager to create/manage users/permissions and
> replication before. I am however not familiar with MSDE. I am using MSDE
> because I'm playing with asp.net. Since there is no user interface I am
> lost. I created my tables and stored procedures via the Web Matrix asp.net
> development tool.
> 1) I need to configure the MSDE 2000 installation user access. Its currently
> setup for integrated windows authentication, which I want to keep. But how
> do I add/manage the users and their permissions. For example with Enterprise
> Manager, even with Integrated authentication, you have to add the
> users/groups from your windows domain before SQL server can use them. Do I
> need to do this in MSDE? how?
> 2) how can I view current user accounts on the system? and their
> permissions?
> 3) while I'm at it here... how can I retrieve the currently logged in user
> from my asp.net web application?
> any info is appreciated. Thanks.
>
>
|||hi,
txghia58 wrote:
> If you have a copy of Enterprise Manager on a different machine you
> should be able to connect to the msde install and see it in the
> familar enterprise manager.
bu only in development/test scenario... you are not licensed to use SQL
Server Client Tools in production...
you have to resort on home made tools and/or 3rd party tools to manage MSDE
in production...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Dumb index question

If I create a compound PK on tab1(col1, col2) where col1 is also a FK to
another table, is SQL Server 2000 (or 2005) creating 1, or 2 underlying
indexes?
Even if it only creates one compound index, if I run the query: select *
from tab1 where col1 = x
Will it still use the compound index, if nothing better is available,
because the column heads the index.
I am expecting the answer to be: 2 indexes and Duh/yes!On Wed, 15 Aug 2007 16:42:16 -0700, "Jay" <nospam@.nospam.org> wrote:

>If I create a compound PK on tab1(col1, col2) where col1 is also a FK to
>another table, is SQL Server 2000 (or 2005) creating 1, or 2 underlying
>indexes?
>Even if it only creates one compound index, if I run the query: select *
>from tab1 where col1 = x
>Will it still use the compound index, if nothing better is available,
>because the column heads the index.
>
>I am expecting the answer to be: 2 indexes and Duh/yes!
A compound index is one index, so if the only index you create is a
two column primary key there is one index. A foreign key is a good
candidate for an index, but no index ix created just because of a FK
definition. Only a PK definition creates an index.
A multi-column index can be used if at least the left-most column is
provided to search on. So in your example of a compound PK on
tab1(col1, col2), the query SELECT * FROM tab1 WHERE col1 = 'x' can
use the index.
Roy Harvey
Beacon Falls, CT|||In addition to Roy's answer , try to avoid using SELECT * in production
environment.
Always specify columns that you need to return. For example if you run
SELECT col1,col2 FROM tbl WHERE col2='blblbl' then SQL Server will create
most fate execution plan as your index covers columns in SELECT statement.
But if you run SELECT col10 FROM tbl WHERE col2='blblbl' will use bookmark
to clustered index key (contains data) to return the data from col10 which
is not part of index
Just my two cenys
"Jay" <nospam@.nospam.org> wrote in message
news:e9LqqX53HHA.600@.TK2MSFTNGP05.phx.gbl...
> If I create a compound PK on tab1(col1, col2) where col1 is also a FK to
> another table, is SQL Server 2000 (or 2005) creating 1, or 2 underlying
> indexes?
> Even if it only creates one compound index, if I run the query: select *
> from tab1 where col1 = x
> Will it still use the compound index, if nothing better is available,
> because the column heads the index.
>
> I am expecting the answer to be: 2 indexes and Duh/yes!
>|||On Aug 15, 6:42 pm, "Jay" <nos...@.nospam.org> wrote:
> If I create a compound PK on tab1(col1, col2) where col1 is also a FK to
> another table, is SQL Server 2000 (or 2005) creating 1, or 2 underlying
> indexes?
> Even if it only creates one compound index, if I run the query: select *
> from tab1 where col1 = x
> Will it still use the compound index, if nothing better is available,
> because the column heads the index.
> I am expecting the answer to be: 2 indexes and Duh/yes!
It is very easy to see for yourself. Create a FK constraint and see if
it created an index. You may use GUI or select from system views such
as sysindexes and sysindexkeys.
Alex Kuznetsov, SQL Server MVP
http://sqlserver-tips.blogspot.com/

Dumb index question

If I create a compound PK on tab1(col1, col2) where col1 is also a FK to
another table, is SQL Server 2000 (or 2005) creating 1, or 2 underlying
indexes?
Even if it only creates one compound index, if I run the query: select *
from tab1 where col1 = x
Will it still use the compound index, if nothing better is available,
because the column heads the index.
I am expecting the answer to be: 2 indexes and Duh/yes!
On Wed, 15 Aug 2007 16:42:16 -0700, "Jay" <nospam@.nospam.org> wrote:

>If I create a compound PK on tab1(col1, col2) where col1 is also a FK to
>another table, is SQL Server 2000 (or 2005) creating 1, or 2 underlying
>indexes?
>Even if it only creates one compound index, if I run the query: select *
>from tab1 where col1 = x
>Will it still use the compound index, if nothing better is available,
>because the column heads the index.
>
>I am expecting the answer to be: 2 indexes and Duh/yes!
A compound index is one index, so if the only index you create is a
two column primary key there is one index. A foreign key is a good
candidate for an index, but no index ix created just because of a FK
definition. Only a PK definition creates an index.
A multi-column index can be used if at least the left-most column is
provided to search on. So in your example of a compound PK on
tab1(col1, col2), the query SELECT * FROM tab1 WHERE col1 = 'x' can
use the index.
Roy Harvey
Beacon Falls, CT
|||In addition to Roy's answer , try to avoid using SELECT * in production
environment.
Always specify columns that you need to return. For example if you run
SELECT col1,col2 FROM tbl WHERE col2='blblbl' then SQL Server will create
most fate execution plan as your index covers columns in SELECT statement.
But if you run SELECT col10 FROM tbl WHERE col2='blblbl' will use bookmark
to clustered index key (contains data) to return the data from col10 which
is not part of index
Just my two cenys
"Jay" <nospam@.nospam.org> wrote in message
news:e9LqqX53HHA.600@.TK2MSFTNGP05.phx.gbl...
> If I create a compound PK on tab1(col1, col2) where col1 is also a FK to
> another table, is SQL Server 2000 (or 2005) creating 1, or 2 underlying
> indexes?
> Even if it only creates one compound index, if I run the query: select *
> from tab1 where col1 = x
> Will it still use the compound index, if nothing better is available,
> because the column heads the index.
>
> I am expecting the answer to be: 2 indexes and Duh/yes!
>
|||On Aug 15, 6:42 pm, "Jay" <nos...@.nospam.org> wrote:
> If I create a compound PK on tab1(col1, col2) where col1 is also a FK to
> another table, is SQL Server 2000 (or 2005) creating 1, or 2 underlying
> indexes?
> Even if it only creates one compound index, if I run the query: select *
> from tab1 where col1 = x
> Will it still use the compound index, if nothing better is available,
> because the column heads the index.
> I am expecting the answer to be: 2 indexes and Duh/yes!
It is very easy to see for yourself. Create a FK constraint and see if
it created an index. You may use GUI or select from system views such
as sysindexes and sysindexkeys.
Alex Kuznetsov, SQL Server MVP
http://sqlserver-tips.blogspot.com/

Dumb index question

If I create a compound PK on tab1(col1, col2) where col1 is also a FK to
another table, is SQL Server 2000 (or 2005) creating 1, or 2 underlying
indexes?
Even if it only creates one compound index, if I run the query: select *
from tab1 where col1 = x
Will it still use the compound index, if nothing better is available,
because the column heads the index.
I am expecting the answer to be: 2 indexes and Duh/yes!On Wed, 15 Aug 2007 16:42:16 -0700, "Jay" <nospam@.nospam.org> wrote:
>If I create a compound PK on tab1(col1, col2) where col1 is also a FK to
>another table, is SQL Server 2000 (or 2005) creating 1, or 2 underlying
>indexes?
>Even if it only creates one compound index, if I run the query: select *
>from tab1 where col1 = x
>Will it still use the compound index, if nothing better is available,
>because the column heads the index.
>
>I am expecting the answer to be: 2 indexes and Duh/yes!
A compound index is one index, so if the only index you create is a
two column primary key there is one index. A foreign key is a good
candidate for an index, but no index ix created just because of a FK
definition. Only a PK definition creates an index.
A multi-column index can be used if at least the left-most column is
provided to search on. So in your example of a compound PK on
tab1(col1, col2), the query SELECT * FROM tab1 WHERE col1 = 'x' can
use the index.
Roy Harvey
Beacon Falls, CT|||In addition to Roy's answer , try to avoid using SELECT * in production
environment.
Always specify columns that you need to return. For example if you run
SELECT col1,col2 FROM tbl WHERE col2='blblbl' then SQL Server will create
most fate execution plan as your index covers columns in SELECT statement.
But if you run SELECT col10 FROM tbl WHERE col2='blblbl' will use bookmark
to clustered index key (contains data) to return the data from col10 which
is not part of index
Just my two cenys
"Jay" <nospam@.nospam.org> wrote in message
news:e9LqqX53HHA.600@.TK2MSFTNGP05.phx.gbl...
> If I create a compound PK on tab1(col1, col2) where col1 is also a FK to
> another table, is SQL Server 2000 (or 2005) creating 1, or 2 underlying
> indexes?
> Even if it only creates one compound index, if I run the query: select *
> from tab1 where col1 = x
> Will it still use the compound index, if nothing better is available,
> because the column heads the index.
>
> I am expecting the answer to be: 2 indexes and Duh/yes!
>|||On Aug 15, 6:42 pm, "Jay" <nos...@.nospam.org> wrote:
> If I create a compound PK on tab1(col1, col2) where col1 is also a FK to
> another table, is SQL Server 2000 (or 2005) creating 1, or 2 underlying
> indexes?
> Even if it only creates one compound index, if I run the query: select *
> from tab1 where col1 = x
> Will it still use the compound index, if nothing better is available,
> because the column heads the index.
> I am expecting the answer to be: 2 indexes and Duh/yes!
It is very easy to see for yourself. Create a FK constraint and see if
it created an index. You may use GUI or select from system views such
as sysindexes and sysindexkeys.
Alex Kuznetsov, SQL Server MVP
http://sqlserver-tips.blogspot.com/