Create table + index + primary

for MS SQL 2000
how can I do this in one time (into the CREATE TABLE)

CREATE TABLE [dbo].[Users] (
[id_Users] [int] NOT NULL ,
[Name] [nvarchar] (100) NULL,
[Serial] [nvarchar] (100) NULL,
) ON [PRIMARY]

ALTER TABLE [dbo].[Users] WITH NOCHECK ADD
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[id_Users]
) ON [PRIMARY]

CREATE UNIQUE INDEX [IX_Users] ON [Users]([Serial]) ON [PRIMARY]

and that one

CREATE TABLE [dbo].[UsersExtra] (
[id_Users] [int] NOT NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[UsersExtra] ADD
CONSTRAINT [FK_UsersExtra_Users] FOREIGN KEY
(
[id_Users]
) REFERENCES [Users] (
[id_Users]
) ON DELETE CASCADE

thank youi am getting an error

CREATE TABLE [dbo].[Users] (
[id_Users] [int] NOT NULL PRIMARY KEY clustered,
[Name] [nvarchar] (100) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[UsersExtra] (
[id_UsersExtra] [int] NOT NULL REFERENCES [Users].[id_Users] ON DELETE CASCADE
) ON [PRIMARY]

Msg 1767, Level 16, State 0, Line 50
Foreign key 'FK__Users__id_Co__05D9AC15' references invalid table 'Users.id_Users'.
Msg 1750, Level 16, State 0, Line 50|||you are trying to reference a non-existing table, 'Users.id_Users'|||but I have created it just before

CREATE TABLE [dbo].[Users] (
[id_Users] [int] NOT NULL PRIMARY KEY clustered,
[Name] [nvarchar] (100) NULL
) ON [PRIMARY]|||To get back on the first question ...
You can't create a table and an index in one single statement, except when that index is the PK ... so more than one statement is required to get the job done. You can however combine the create and alter table statements into one. See BOL 'create table'.

Gr,
Yveau|||Add Go and try it again|||but I have created it just beforeyes, you did

however, that is not the source of your problem

you said REFERENCES [Users].[id_Users]

this is invalid syntax, because it is trying to reference a table called "id_Users" belonging to user called "Users"

for the correct syntax, please see the manual

:)

Create record each day from time frame

I have a table that has cost records for a specific item for a specific star
t
date.
Start, cost, item, PromoCode, end date
01/02/2006, 2.45, 1234, R,
01/05/2006, 2.00, 1234, P, 01/08/2006
01/10/2006, 2.55, 1234, R,
If a record has a start date then that new cost begains. And if it doesn't
have an end date it will go indefinitely. Also when a promo (P) ends then
the cost goes back to the Regular (R) cost record.
So the cost records that I would create from the above records is
01/02/2006, 2.45, 1234, R
01/03/2006, 2.45, 1234, R
01/04/2006, 2.45, 1234, R
01/05/2006, 2.00, 1234, P
01/06/2006, 2.00, 1234, P
01/07/2006, 2.00, 1234, P
01/08/2006, 2.45, 1234, R
01/09/2006, 2.45, 1234, R
01/10/2006, 2.55, 1234, R
01/10/2006, 2.55, 1234, R
.....
Looking for any help with how to start a stored procedure or query to come
up with these records.
Thanks!Suggest joining to an auxiliary calendar table
See http://www.aspfaq.com/2519

Create PIE/BAR Charts using DTS?

Hi All,
I'm using DTS in Sql Server 2000 for the first time, and would like to create Ad-Hoc reports with Pie/Charts and graphs. Is this possible in SQL Server/DTS, or would I need something like Cyrstal Reports?
Thanks!
MattDTS (Data Transformation Services) is an Extraction, Loading and Transformation tool (ETL). It is not a reporting tool.

For creating reports with charts and such, you want to look at SQL Server Reporting Services (SSRS). It comes with SQL 2005, but under certain circumstances, it may require a separate license.

Be forewarned, SSRS comes with a fairly steep learning curve.

Regards,

hmscott|||I agree with the poster of the previous post. DTS is an ETL tool designed to provide a set of interfaces for the import, export and within reason, the manipulation of data relevant to either of these processes.

Although DTS does provide support to invoke .VBS scripts and to call stored procedures and possibly other database objects, this functionality should be restricted for use to enhance the processes for which DTS is designed, import and export.

To accomplish your goal, I would recommend using DTS to manage your ETL requirments and then invoking a specific API designed specifically to render your PIE Chart. This process would involve two steps, the first one being optional. First, a database transaction, encapsulated within a stored procedure, would be created to produce a report of the data that is to be used as input to the graphing function.

The graphing functionality can be provided by any language or application. The decision will come down to what development languages your team is comfortable with, or what reporting packages the company has available. Two such packages that are quite popular in reporting are Business Objects and Microsoft Reporting Services.

Another option of course, for the graphical rendering of the data, is to develop a simple graphing function in a high level language such as .NET and Java. Your process can then be developed in one of these languages to call a stored procedure, to process the result set, and then to pass the set to a custom written graphing routine.

Regards,

create new sql tables correctly

Hi,

I need help to create tables the right way. this is my first time doing it.

My company asked me to create effective tables..

Here are things needs to be stored.

The goal is to keep track of the data.

Daily

--

- In the future they are expecting 50,000 + users...

- A user would be able to add data daily and every hour ( text can be from 25 characters to 100)

- And I would need to keep the data for 60-120 days after the data can be deleted. (I would have to write a script, that would run daily to check if the data is 120 days old than delete it, release space)

Got some questions:

- How much data a table can have?

- Can I create unlimited tables? or there is a max?
- I will be using freeSQL Server 2005 Express Edition, not sure if I need to buy the full version (SQL Server 2005 )

what I thought of doing is have 12 tables ( each has 1 week)...hmm.. doest sound good I know. I dont know how else to do.. (please advice)

|userId| DAY1 | DAY2 | DAY3 |DAY4 |DAY5 |DAY6 |DAY7

01 ...text will be added each hour... ..day2 text... ...etc

-->the problem is I dont know how much a table can handle text..

Please advice any ideas..

Thank you for reading..

this post is helpful to know limits of SQL Server 2005

http://www.sqlservercentral.com/articles/Administration/maximumcapacityspecificationsinsqlserver2005/1574/

I would suggest you should create only 1 normalized table with following fields
(userid, timestamp, message)

SQL Server can handle huge amount of data, in your above table design, it is not mandatory that user will enter data every hour, in that case that hour field will remain empty.

Hope it clears your doubt and might be useful.
Let me know in case of any further queries.

|||

Sorry, I am new to creating tables..

1) what does "normalized table" means?

2) The url you posted, describesSQL Server 2005 , and I wanted to use SQL Server 2005 Express Edition (free), are the specs the same? or there is a limitation?

3) I have checked the url, and under : Row per table, Tables per database says "LIMITED BY AVALABLE STORAGE" what does this mean?

--

back to my questions, you are saying put all the data to one table.

userID | timestamp | message |

0001 | (not sure what you mean by the timestamp) | Day1#9:text here#10text here..etc.. Day2... (this can be very long...) not sure if this what you mean?.

Please provide me an example of the table structure. ( i am trying to build, if the user does add text every hour)... (just to be safe)

Thank you for your help.

|||

1) what does "normalized table" means?
Ans:normalization is a technique for designingrelational databasetables to minimize duplication of information and, in so doing, to safeguard the database against certain types of logical or structural problems, namely data anomalies. http://en.wikipedia.org/wiki/Database_normalization

2) The url you posted, describesSQL Server 2005 , and I wanted to use SQL Server 2005 Express Edition (free), are the specs the same? or there is a limitation?
Ans: There is no difference in terms of database limitations between SQL Server 2005 and Express Edition. The only difference is some of the advance features are not available with Express Edition.

3) I have checked the url, and under : Row per table, Tables per database says "LIMITED BY AVALABLE STORAGE" what does this mean?
Ans: Limited by Available storage means, Free disk space of your server/computer. means there is no limit of storing data in tables.

--

back to my questions, you are saying put all the data to one table.

userID | timestamp | message |

here timestamp is dateandtime user has posted the message
e.g. data can be like this
1 12/12/2007 10:00 some message
2 12/12/2007 11:00 some message
3 12/12/2007 13:00 some message

I hope this answers your questions.

|||

aadreja:

2) The url you posted, describesSQL Server 2005 , and I wanted to use SQL Server 2005 Express Edition (free), are the specs the same? or there is a limitation?
Ans: There is no difference in terms of database limitations between SQL Server 2005 and Express Edition. The only difference is some of the advance features are not available with Express Edition.

See the following link

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

Two important limitations of Express are:
Maximum database size of 4 GB
SQL Backup is NOT supported.

|||

Thanks for your help,

Sorry I just got 2 more questions,

1) I just noticed at the specs that it says "Database size is Maximum 1~ terabytes" but number of rows/tables are unlimited, so its not realy unlimited, it has a limittation? or am I missing something?

2) the table example structure you showed:

aadreja:

1 12/12/2007 10:00 some message
2 12/12/2007 11:00 some message
3 12/12/2007 13:00 some message

that only shows that each user entered one hour a day. but if for example user#1 entered text for 9,10,11,12..etc would I store all in one table?

example:

1 12/12/2007 10:00 some message
1 12/12/2007 11:00 some message
1 12/12/2007 12:00 some message
1 12/12/2007 13:00 some message
1 12/12/2007 14:00 some message
2 12/12/2007 10:00 some message
2 12/12/2007 11:00 some message
3 12/12/2007 12:00 some message
3 12/12/2007 13:00 some message
3 12/12/2007 14:00 some message ...etc..

so it will be a very long table.. do you think it will be okay? or should it been designed differently? if each user will have 15 rows and It might have 50,000 + user.. Will it support that ?

Thank you for your help

|||

There is no limit to the number of rows in a tabe or or tables in your database. Only a limit to the size of the database.

We run a database which is logging about 50 measurements every 30 seconds and has been doing this for over a year now - at the moment this table holds 208,495,020 rows and there's no speed problem.

We are using SQL Server 2005 Workgroup edition, not express, and on a fairly powerful dual core dual processor server.

I think you should trial the system on Express - you can always upgrade to a more "full-bodied" version later if you need to.

Create Multiple Store Procedures in 1 SQL statement

Hi guys , may I know is that possible to create multiple store procedures in 1 time using 1 SQL statement? Thx for the assistance.

Best Regards,

Hans

I can't get your Question..

Need more information

|||

I hope you need to write sp like this way.

/*
sp1
*/
CREATE PROC MultipleStoreProcedures;1
AS
BEGIN
SELECT [CategoryID], [CategoryName], [Description], [Picture]
FROM [Northwind].[dbo].[Categories]
END

GO

/*
sp2
*/
CREATE PROC MultipleStoreProcedures;2
AS
BEGIN
SELECT [CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]
FROM [Northwind].[dbo].[Customers]
END
GO

**
--execute sp1
exec MultipleStoreProcedures;1
--execute sp2
exec MultipleStoreProcedures;2
**

|||

thx. it helps. :) However, if using 2005 for creating, then the 'GO' statement will be dissappear when u modify the store procedure. But it seems ok anyway. Thx alot.

Best Regards,

Hans

|||

The 'GO' isn't part of a T-SQL statement, it's just a marker that identifies where the end of batch is.
So, the above is, if you save it all as one file, and run it all at the same time, still two batches ( or statements if you will ), but it looks like it's working for you wanted anyway =:o)

/Kenneth

'CREATE INDEX' Statement...Faster?

I am indexing a 32 millon row table with a unqine clustered index and
the run time for the create index statement is over 4 hours long. This
runs is too slow and may cost me a job If i cant find a faster way to
create index on large table.
Can anyone help.
Hi
What is the version of SQL Server?
<GreenHillCourt@.gmail.com> wrote in message
news:a7661fdd-12e9-4a12-969f-6ec961eab992@.n1g2000prb.googlegroups.com...
>I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
> Can anyone help.
|||i don't think it would be faster. Clustered Index is the way data is stored
physically in the hard disk.
bye!
"GreenHillCourt@.gmail.com" wrote:

> I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
> Can anyone help.
>
|||Setting the database to simple recovery should improve compared to full recovery. At least it will
cut down on the logging. But the data shuffling still has to occur of course...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"DarthSidious" <DarthSidious@.discussions.microsoft.com> wrote in message
news:15C6F565-D67E-4912-8424-BFBDF0EC88A2@.microsoft.com...[vbcol=seagreen]
>i don't think it would be faster. Clustered Index is the way data is stored
> physically in the hard disk.
> bye!
> "GreenHillCourt@.gmail.com" wrote:
|||<GreenHillCourt@.gmail.com> wrote in message
news:a7661fdd-12e9-4a12-969f-6ec961eab992@.n1g2000prb.googlegroups.com...
>I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
As others have said, if you're creating the clustered index, not much you
can do other than perhaps look at a faster disk subsystem (say raid 10 vs
RAID 5, etc.)
If you mean you're creating a non-clustered index on a table that has an
existing clustered index, one thing that can certainly help is putting the
non-clustered index on its own set of disks.

> Can anyone help.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Have you tried creating the index without clustering? If I'm not mistaken,
it should then not have to physically re-order the data.
<GreenHillCourt@.gmail.com> wrote in message
news:a7661fdd-12e9-4a12-969f-6ec961eab992@.n1g2000prb.googlegroups.com...
>I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
> Can anyone help.
|||"GreenHillCourt@.gmail.com" wrote:
> I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
> Can anyone help.
- drop all unnecessary nonclustered indexes
- if possible, connect in single user mode
- add extra memory to the server, don't "pin" tables
- make sure you have enough I/O and bandwidth, both for your table, the
TempDB and the log file
- make sure you have enough free space before you start, at least 1.5
times the table size
- set the database recovery mode to simple
- choose an appropriate fillfactor. Any value below 70 is probably a bad
idea
4 hours seems excessive. How much space does the table occupy (before
you start)? What is your I/O system (number of disks, etc.)?
Gert-Jan
SQL Server MVP

'CREATE INDEX' Statement...Faster?

I am indexing a 32 millon row table with a unqine clustered index and
the run time for the create index statement is over 4 hours long. This
runs is too slow and may cost me a job If i cant find a faster way to
create index on large table.
Can anyone help.Hi
What is the version of SQL Server?
<GreenHillCourt@.gmail.com> wrote in message
news:a7661fdd-12e9-4a12-969f-6ec961eab992@.n1g2000prb.googlegroups.com...
>I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
> Can anyone help.|||i don't think it would be faster. Clustered Index is the way data is stored
physically in the hard disk.
bye!
"GreenHillCourt@.gmail.com" wrote:
> I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
> Can anyone help.
>|||Setting the database to simple recovery should improve compared to full recovery. At least it will
cut down on the logging. But the data shuffling still has to occur of course...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"DarthSidious" <DarthSidious@.discussions.microsoft.com> wrote in message
news:15C6F565-D67E-4912-8424-BFBDF0EC88A2@.microsoft.com...
>i don't think it would be faster. Clustered Index is the way data is stored
> physically in the hard disk.
> bye!
> "GreenHillCourt@.gmail.com" wrote:
>> I am indexing a 32 millon row table with a unqine clustered index and
>> the run time for the create index statement is over 4 hours long. This
>> runs is too slow and may cost me a job If i cant find a faster way to
>> create index on large table.
>> Can anyone help.|||<GreenHillCourt@.gmail.com> wrote in message
news:a7661fdd-12e9-4a12-969f-6ec961eab992@.n1g2000prb.googlegroups.com...
>I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
As others have said, if you're creating the clustered index, not much you
can do other than perhaps look at a faster disk subsystem (say raid 10 vs
RAID 5, etc.)
If you mean you're creating a non-clustered index on a table that has an
existing clustered index, one thing that can certainly help is putting the
non-clustered index on its own set of disks.
> Can anyone help.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Have you tried creating the index without clustering? If I'm not mistaken,
it should then not have to physically re-order the data.
<GreenHillCourt@.gmail.com> wrote in message
news:a7661fdd-12e9-4a12-969f-6ec961eab992@.n1g2000prb.googlegroups.com...
>I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
> Can anyone help.|||"GreenHillCourt@.gmail.com" wrote:
> I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
> Can anyone help.
- drop all unnecessary nonclustered indexes
- if possible, connect in single user mode
- add extra memory to the server, don't "pin" tables
- make sure you have enough I/O and bandwidth, both for your table, the
TempDB and the log file
- make sure you have enough free space before you start, at least 1.5
times the table size
- set the database recovery mode to simple
- choose an appropriate fillfactor. Any value below 70 is probably a bad
idea
4 hours seems excessive. How much space does the table occupy (before
you start)? What is your I/O system (number of disks, etc.)?
--
Gert-Jan
SQL Server MVP

Create index on col with data and without data, any time differenc

Hello:
In terms of the time it takes to create an index (non-clustered) in a
column, would it be faster if the column contains only null? Or the time it
takes to create an index will be the same whether this column has data or not.
Thanks,
Q
Hi
I don't think there will be a significant difference in the time taken to
create the index, but if you then significantly change the data in the
column, the index may be fragmented and the statistics out of date, therefore
it would be better to build the index once populated.
John
"Q" wrote:

> Hello:
> In terms of the time it takes to create an index (non-clustered) in a
> column, would it be faster if the column contains only null? Or the time it
> takes to create an index will be the same whether this column has data or not.
> Thanks,
> Q
|||No rows are omitted from an index. Not even rows where the indexed
column is NULL. So the time to index the column would be the same,
regardless of any value (or NULL) (assuming fixed size data type).
Gert-Jan
Q wrote:
> Hello:
> In terms of the time it takes to create an index (non-clustered) in a
> column, would it be faster if the column contains only null? Or the time it
> takes to create an index will be the same whether this column has data or not.
> Thanks,
> Q

Create index on col with data and without data, any time differenc

Hello:
In terms of the time it takes to create an index (non-clustered) in a
column, would it be faster if the column contains only null? Or the time it
takes to create an index will be the same whether this column has data or no
t.
Thanks,
QHi
I don't think there will be a significant difference in the time taken to
create the index, but if you then significantly change the data in the
column, the index may be fragmented and the statistics out of date, therefor
e
it would be better to build the index once populated.
John
"Q" wrote:

> Hello:
> In terms of the time it takes to create an index (non-clustered) in a
> column, would it be faster if the column contains only null? Or the time
it
> takes to create an index will be the same whether this column has data or
not.
> Thanks,
> Q|||No rows are omitted from an index. Not even rows where the indexed
column is NULL. So the time to index the column would be the same,
regardless of any value (or NULL) (assuming fixed size data type).
Gert-Jan
Q wrote:
> Hello:
> In terms of the time it takes to create an index (non-clustered) in a
> column, would it be faster if the column contains only null? Or the time
it
> takes to create an index will be the same whether this column has data or
not.
> Thanks,
> Q

Create index on col with data and without data, any time differenc

Hello:
In terms of the time it takes to create an index (non-clustered) in a
column, would it be faster if the column contains only null? Or the time it
takes to create an index will be the same whether this column has data or not.
Thanks,
QHi
I don't think there will be a significant difference in the time taken to
create the index, but if you then significantly change the data in the
column, the index may be fragmented and the statistics out of date, therefore
it would be better to build the index once populated.
John
"Q" wrote:
> Hello:
> In terms of the time it takes to create an index (non-clustered) in a
> column, would it be faster if the column contains only null? Or the time it
> takes to create an index will be the same whether this column has data or not.
> Thanks,
> Q|||No rows are omitted from an index. Not even rows where the indexed
column is NULL. So the time to index the column would be the same,
regardless of any value (or NULL) (assuming fixed size data type).
Gert-Jan
Q wrote:
> Hello:
> In terms of the time it takes to create an index (non-clustered) in a
> column, would it be faster if the column contains only null? Or the time it
> takes to create an index will be the same whether this column has data or not.
> Thanks,
> Q

Create Excel File

Is there any way we can create the Excel File on the run time through any
SQL Command or any script.
Thanks
You could by using the sp_OAxxx stored procedures but it
really wouldn't be a good idea. You can probably accomplish
what you want in a cleaner way by using DTS.
-Sue
On Thu, 15 Dec 2005 11:21:16 -0500, "Rogers"
<naissani@.hotmail.com> wrote:

>Is there any way we can create the Excel File on the run time through any
>SQL Command or any script.
>
>Thanks
>

Create database on NAS

I am using SQL 2005 std edtition. Everyone time I create a new database with
files being located to say \\nas\db2. I get 5110 error.(5110 (File
'file_name' Is On A Network Device Not Supported For Database Files).
I read about traceon and other MS KBs but no luck. Anyone ?
thanks(a) I strongly recommend NOT using a NAS for SQL Server... this is not
exactly a robust environment to be running a high performance database
application.
(b) if you really want to go through with it, then you should be mapping a
driver letter for the SQL Server service account user (and any other user
that will need to access it). Ideally, SQL Server uses a local drive (even
if we are talking SAN / direct attached storage), but barring that, it at
least needs to *look* like a local drive.
A
"andyoye" <andyoye@.nospam.com> wrote in message
news:%23RA0%23q04HHA.1484@.TK2MSFTNGP06.phx.gbl...
>I am using SQL 2005 std edtition. Everyone time I create a new database
>with files being located to say \\nas\db2. I get 5110 error.(5110 (File
>'file_name' Is On A Network Device Not Supported For Database Files).
> I read about traceon and other MS KBs but no luck. Anyone ?
> thanks
>
>|||And here's a KB on the topic:
http://support.microsoft.com/default.aspx?scid=kb;en-us;304261
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uiZtE104HHA.4476@.TK2MSFTNGP06.phx.gbl...
> (a) I strongly recommend NOT using a NAS for SQL Server... this is not exactly a robust
> environment to be running a high performance database application.
> (b) if you really want to go through with it, then you should be mapping a driver letter for the
> SQL Server service account user (and any other user that will need to access it). Ideally, SQL
> Server uses a local drive (even if we are talking SAN / direct attached storage), but barring
> that, it at least needs to *look* like a local drive.
> A
>
> "andyoye" <andyoye@.nospam.com> wrote in message news:%23RA0%23q04HHA.1484@.TK2MSFTNGP06.phx.gbl...
>>I am using SQL 2005 std edtition. Everyone time I create a new database with files being located
>>to say \\nas\db2. I get 5110 error.(5110 (File 'file_name' Is On A Network Device Not Supported
>>For Database Files).
>> I read about traceon and other MS KBs but no luck. Anyone ?
>> thanks
>>
>

Create Customer Ranking in Cube

Hey guys,

is there a way i can create a Rank Member or measure in my cube based on sales for that customer, no matter what time heirarchy we are looking at.

I have reporting requirements where they want to see all customers, ordered by Sales Amount. It could be at a year level, month level, or even a week/day level..

Im new to SSAS and MDX, i have looked at a few examples posted here, but cannot work out where i put the script in my cube. Is it a measure or do i do it as a calculated column in the DSV.

Any help is much appreciated.

Thanks

Scotty

Here's an Adventure Works ranking query, which uses a query-scoped calculated measure (assuming that the Customer hierarchy is on the rows of the query):

>>

with member [Measures].[CustSalesRank] as

Rank([Customer].[Customer Geography].CurrentMember,

Order(Extract(Axis(1),[Customer].[Customer Geography]),

[Measures].[Internet Sales Amount], BDESC)),

NON_EMPTY_BEHAVIOR = [Measures].[Internet Sales Amount]

select [Date].[Fiscal Year].Members *

{[Measures].[Internet Sales Amount], [Measures].[CustSalesRank]} on 0,

NON EMPTY [Customer].[Customer Geography].[Country].Members on 1

from [Adventure Works]

--

All Periods All Periods FY 2002 FY 2002 FY 2003 FY 2003 FY 2004 FY 2004 FY 2005 FY 2005
Internet Sales Amount CustSalesRank Internet Sales Amount CustSalesRank Internet Sales Amount CustSalesRank Internet Sales Amount CustSalesRank Internet Sales Amount CustSalesRank
Australia $9,061,000.58 2 $2,568,701.39 1 $2,099,585.43 1 $4,383,479.54 2 $9,234.23 3
Canada $1,977,844.86 6 $573,100.97 3 $305,010.69 6 $1,088,879.50 6 $10,853.70 2
France $2,644,017.71 5 $414,245.32 6 $633,399.70 4 $1,592,880.75 5 $3,491.95 6
Germany $2,894,312.34 4 $513,353.17 5 $593,247.24 5 $1,784,107.09 4 $3,604.83 5
United Kingdom $3,391,712.21 3 $550,507.33 4 $696,594.97 3 $2,140,388.50 3 $4,221.41 4
United States $9,389,789.51 1 $2,452,176.07 2 $1,434,296.26 2 $5,483,882.67 1 $19,434.51 1

>>

|||Please remove this line, as it is wrong:

NON_EMPTY_BEHAVIOR = [Measures].[Internet Sales Amount]

Obviously, even if [Internet Sales Amount] is NULL, the rank will never be NULL - it is always an integer value.

|||

Ok guys, thanks for the replys..

I added the following into a calculated measure that i called CustSalesRank .. (taken from the 1st line of code) is that Correct?

When i added this to the expression box in the calculated member, i get a red undeline on the "with" statement.. What am i doing wrong?

CREATE MEMBER CURRENTCUBE.[MEASURES].[CustSalesRank]

AS with member [measures].[CustSalesRank] as

Rank([Customer].[Customer Group Name].CurrentMember,

Order(Extract(Axis(1),[Customer].[Customer Group Name]),

[Measures].[Sales Amount], BDESC)),

select [Date].[Fiscal Hierarchy].Members *

{[Measures].[Sales Amount], [Measures].[CustSalesRank]} on 0,

NON EMPTY [Customer].[Customer Group Name].Members on 1

from [Sales By Market],

VISIBLE = 1 ;

--

|||

the error i get when i try to deploy is:

Error 2 MdxScript(Sales By Market) (17, 5) Parser: The syntax for 'with' is incorrect. 0 0

|||

Hi Mosha,

You're right in the strict sense - the reason I added NON_EMPTY_BEHAVIOR was to remove rows with no sales for this specific query (ie. a query ranking only among customers with sales data), as in this example:

>>

with member [Measures].[CustSalesRank] as

Rank([Customer].[Customer Geography].CurrentMember,

Order(Extract(Axis(1),[Customer].[Customer Geography]),

[Measures].[Internet Sales Amount], BDESC))

select

{[Measures].[Internet Sales Amount], [Measures].[CustSalesRank]} on 0,

NON EMPTY DrillDownLevel([Customer].[Customer Geography].[Country].&[Canada]) on 1

from [Adventure Works]

-

Internet Sales Amount CustSalesRank
Canada $1,977,844.86 1
Alberta $22,467.80 3
British Columbia $1,955,340.10 2
Brunswick (null) 5
Manitoba (null) 6
Ontario $36.96 4
Quebec (null) 7

versus:

with member [Measures].[CustSalesRank] as

Rank([Customer].[Customer Geography].CurrentMember,

Order(Extract(Axis(1),[Customer].[Customer Geography]),

[Measures].[Internet Sales Amount], BDESC)),

NON_EMPTY_BEHAVIOR = [Measures].[Internet Sales Amount]

select

{[Measures].[Internet Sales Amount], [Measures].[CustSalesRank]} on 0,

NON EMPTY DrillDownLevel([Customer].[Customer Geography].[Country].&[Canada]) on 1

from [Adventure Works]

-

Internet Sales Amount CustSalesRank
Canada $1,977,844.86 1
Alberta $22,467.80 3
British Columbia $1,955,340.10 2
Ontario $36.96 4

>>

|||

> You're right in the strict sense - the reason I added NON_EMPTY_BEHAVIOR was to remove rows with no sales for this specific query (ie. a query ranking only among customers with sales data), as in this example:

Deepak - this is very very dangerous path. NON_EMPTY_BEHAVIOR is not a semantic feature, it is a performance hint. It doesn't cause rows to be removed by NON EMPTY. They might get removed sometimes when NEB is defined incorrectly, like in the example above, but it is purely a hint, and in other situations they won't get removed. So you will get inconsistant and even wrong results.

To properly remove rows with no sales, there must be specific IIF for that, i.e.

with member [Measures].[CustSalesRank] as

IIF( IsEmpty([Measures].[Internet Sales Amount]), NULL, Rank([Customer].[Customer Geography].CurrentMember,

Order(Extract(Axis(1),[Customer].[Customer Geography]),

[Measures].[Internet Sales Amount], BDESC))),

NON_EMPTY_BEHAVIOR = [Measures].[Internet Sales Amount]

Now NON_EMPTY_BEHAVIOR is correctly defined. Another idea is to optimize performance here would be to remove Order from inside Rank, and use version of Rank with 3 parameters.

|||

Mosha,

Thanks for your input.. Could you please kindly answer my question for me.. I just need to know where i put this code, i put it in a calculated measure and it didnt work. I removed all things from the select statement and down, but im not sure if it is working correctly..

Also, with all this code talk, im not 100% sure what i should be putting in now...

Thanks

Scotty

|||

For Adventure Works, you can put something like that inside MDX Script:

CREATE [CustSalesRank] = Rank([Customer].[Customer Geography].CurrentMember,[Customer].[Customer Geography].CurrentMember.Level.Members,[Measures].[Internet Sales Amount]);

|||

Thanks Mosha.. this worked great..

If i may ask for one more piece of advise, when i do this,, all the customers that have 0 sales are still shown with the lowest rank number.. in my case 81 . what i can i add to expression that will supress customers with 0 sales..?

Thanks

scotty

|||

Thanks for clarifying that, Mosha - I had (mistakenly) assumed that, in AS 2005, NON_EMPTY_BEHAVIOR would cause those rows to be removed.

On the use of Rank() with 3 parameters in lieu of Order() - has the semantics of Rank() changed in AS 2005, because BOL still says the following:

http://msdn2.microsoft.com/en-us/library/ms144726.aspx

>>

SQL Server 2005 Books Online

Rank (MDX)

Updated: 17 July 2006

...

The Rank function does not order the set.

>>

|||

The comment that Rank function doesn not order the set is correct, although I can see how it can be misleading. It probably refers to the fact that internal implementation of Rank doesn't need to order the set in order to find the rank. Please see detailed discussion about Rank and algorithms behind it in this blog:

http://www.sqljunkies.com/WebLog/mosha/archive/2006/03/14/mdx_ranking.aspx

|||

Thanks - since the same comment also appeared in AS 2000 BOL, it could be confusing. But the MDX Solutions chapter, referenced in your blog entry, draws this distinction:

"..The semantics for this function have changed between Analysis Services 2000 and 2005. When the expression is provided, it is used to determine if ties exist and what the right rank should be. In Analysis Services 2000, the expression was used when the tuple was found to search neighbors in the set and determine fair ranking numbers.."