How to determine what object is on what filegroup?

I have a vendor product that has 15 filegroups and I would like to determine
what objects(tables,indx..) reside on what filegroup? I used generate
script and looking at each object to see where it was created and this seems
the hard way to go about it. Is there a sp or a script that would list the
objects and where they exist with having to dig through these items.
TIA
C
CD
This script has written by Itzik Ben-Gan
create database test
on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
filegroup user_fg
(name = 'datafile2', filename = 'c:\temp\datafile2')
log on
(name = 'logfile1', filename = 'c:\temp\logfile1')
go
use test
go
create table t1(col1 int)
create table t2(col1 int) on [primary]
create table t3(col1 int) on user_fg
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
and i.indid < 2
and i.groupid = s.groupid
table_name filegroup
-- --
t1 PRIMARY
t2 PRIMARY
t3 user_fg
alter database test modify filegroup user_fg default
create table t4(col1 int)
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('t1'), object_id('t2'),
object_id('t3'),object_id('t4'))
and i.indid < 2
and i.groupid = s.groupid
table_name filegroup
-- --
t1 PRIMARY
t2 PRIMARY
t3 user_fg
drop database test
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:uC38SPPQFHA.3880@.tk2msftngp13.phx.gbl...
> I have a vendor product that has 15 filegroups and I would like to
determine
> what objects(tables,indx..) reside on what filegroup? I used generate
> script and looking at each object to see where it was created and this
seems
> the hard way to go about it. Is there a sp or a script that would list
the
> objects and where they exist with having to dig through these items.
> TIA
> C
>
sql

How to determine what object is on what filegroup?

I have a vendor product that has 15 filegroups and I would like to determine
what objects(tables,indx..) reside on what filegroup? I used generate
script and looking at each object to see where it was created and this seems
the hard way to go about it. Is there a sp or a script that would list the
objects and where they exist with having to dig through these items.
TIA
CCD
This script has written by Itzik Ben-Gan
create database test
on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
filegroup user_fg
(name = 'datafile2', filename = 'c:\temp\datafile2')
log on
(name = 'logfile1', filename = 'c:\temp\logfile1')
go
use test
go
create table t1(col1 int)
create table t2(col1 int) on [primary]
create table t3(col1 int) on user_fg
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
and i.indid < 2
and i.groupid = s.groupid
table_name filegroup
-- --
t1 PRIMARY
t2 PRIMARY
t3 user_fg
alter database test modify filegroup user_fg default
create table t4(col1 int)
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('t1'), object_id('t2'),
object_id('t3'),object_id('t4'))
and i.indid < 2
and i.groupid = s.groupid
table_name filegroup
-- --
t1 PRIMARY
t2 PRIMARY
t3 user_fg
drop database test
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:uC38SPPQFHA.3880@.tk2msftngp13.phx.gbl...
> I have a vendor product that has 15 filegroups and I would like to
determine
> what objects(tables,indx..) reside on what filegroup? I used generate
> script and looking at each object to see where it was created and this
seems
> the hard way to go about it. Is there a sp or a script that would list
the
> objects and where they exist with having to dig through these items.
> TIA
> C
>

How to determine what object is on what filegroup?

I have a vendor product that has 15 filegroups and I would like to determine
what objects(tables,indx..) reside on what filegroup? I used generate
script and looking at each object to see where it was created and this seems
the hard way to go about it. Is there a sp or a script that would list the
objects and where they exist with having to dig through these items.
TIA
CCD
This script has written by Itzik Ben-Gan
create database test
on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
filegroup user_fg
(name = 'datafile2', filename = 'c:\temp\datafile2')
log on
(name = 'logfile1', filename = 'c:\temp\logfile1')
go
use test
go
create table t1(col1 int)
create table t2(col1 int) on [primary]
create table t3(col1 int) on user_fg
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
and i.indid < 2
and i.groupid = s.groupid
table_name filegroup
-- --
t1 PRIMARY
t2 PRIMARY
t3 user_fg
alter database test modify filegroup user_fg default
create table t4(col1 int)
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('t1'), object_id('t2'),
object_id('t3'),object_id('t4'))
and i.indid < 2
and i.groupid = s.groupid
table_name filegroup
-- --
t1 PRIMARY
t2 PRIMARY
t3 user_fg
drop database test
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:uC38SPPQFHA.3880@.tk2msftngp13.phx.gbl...
> I have a vendor product that has 15 filegroups and I would like to
determine
> what objects(tables,indx..) reside on what filegroup? I used generate
> script and looking at each object to see where it was created and this
seems
> the hard way to go about it. Is there a sp or a script that would list
the
> objects and where they exist with having to dig through these items.
> TIA
> C
>

How to design this?

I know this group is about programming, and there is no other group (or
is there?) about database design. So I'll just ask it here.
A product can be sourced from multiple suppliers (worldwide). So I came
up with the tables below.
CREATE TABLE Product (
ProductCode NVARCHAR(10) NOT NULL PRIMARY KEY,
ProductName NVARCHAR(50) NOT NULL,
..
)
CREATE TABLE Supplier (
SupplierCode NVARCHAR(10) NOT NULL PRIMARY KEY,
SupplierName NVARCHAR(50) NOT NULL,
..
)
CREATE TABLE ProductSupplier (
ProductCode NVARCHAR(10) NOT NULL
REFERENCES Product(ProductCode),
SupplierCode NVARCHAR(10) NOT NULL
REFERENCES Supplier(SupplierCode),
..,
PRIMARY KEY (ProductCode, SupplierCode)
)
Now, for a product that is source from a supplier, I have multiple
prices, depending on the quantity I ordered. How would I model this? Is
the following correct?
CREATE TABLE ProductSupplierPrice (
ProductCode NVARCHAR(10) NOT NULL
REFERENCES Product(ProductCode),
SupplierCode NVARCHAR(10) NOT NULL
REFERENCES Supplier(SupplierCode),
Price MONEY NOT NULL,
Qty BIGINT NOT NULL,
PRIMARY KEY (ProductCode, SupplierCode)
)
Or, should I just have an identity column in ProductSupplier, and then
references it from ProductSupplierPrice? Like this:
CREATE TABLE ProductSupplier (
ProductSupplierId INT NOT NULL PRIMARY,
ProductCode NVARCHAR(10) NOT NULL
REFERENCES Product(ProductCode),
SupplierCode NVARCHAR(10) NOT NULL
REFERENCES Supplier(SupplierCode),
..,
? UNIQUE INDEX (ProductCode, SupplierCode)
)
CREATE TABLE ProductSupplierPrice (
ProductSupplierPriceId INT NOT NULL PRIMARY,
ProductSupplierId INT NOT NULL
REFERENCES ProductSupplier(ProductSupplierId),
Price MONEY NOT NULL,
Qty BIGINT NOT NULL,
PRIMARY KEY (ProductCode, SupplierCode)
)> CREATE TABLE ProductSupplierPrice (
> ProductCode NVARCHAR(10) NOT NULL
> REFERENCES Product(ProductCode),
> SupplierCode NVARCHAR(10) NOT NULL
> REFERENCES Supplier(SupplierCode),
> Price MONEY NOT NULL,
> Qty BIGINT NOT NULL,
> PRIMARY KEY (ProductCode, SupplierCode)
> )
is incorrect

> CREATE TABLE ProductSupplierPrice (
> ProductSupplierPriceId INT NOT NULL PRIMARY,
> ProductSupplierId INT NOT NULL
> REFERENCES ProductSupplier(ProductSupplierId),
> Price MONEY NOT NULL,
> Qty BIGINT NOT NULL,
> PRIMARY KEY (ProductCode, SupplierCode)
> )
is incorrect too because (as you've mentioned above) there are (possible)
several prices for each product from each supplier depending on quantity.
So, Qty column should also be a part of the primary key of
ProductSupplierPrice table. But you may add surrogate key column to this
table (not null'ed Identity with Unique index on it) for use in FK
constraints, if needed.
WBR, Evergray
--
Words mean nothing...
"Michael Wong" <nospam@.email.here> wrote in message
news:%23uJb5FzQGHA.5152@.TK2MSFTNGP10.phx.gbl...
>I know this group is about programming, and there is no other group (or is
>there?) about database design. So I'll just ask it here.
> A product can be sourced from multiple suppliers (worldwide). So I came up
> with the tables below.
> CREATE TABLE Product (
> ProductCode NVARCHAR(10) NOT NULL PRIMARY KEY,
> ProductName NVARCHAR(50) NOT NULL,
> ...
> )
> CREATE TABLE Supplier (
> SupplierCode NVARCHAR(10) NOT NULL PRIMARY KEY,
> SupplierName NVARCHAR(50) NOT NULL,
> ...
> )
> CREATE TABLE ProductSupplier (
> ProductCode NVARCHAR(10) NOT NULL
> REFERENCES Product(ProductCode),
> SupplierCode NVARCHAR(10) NOT NULL
> REFERENCES Supplier(SupplierCode),
> ...,
> PRIMARY KEY (ProductCode, SupplierCode)
> )
> Now, for a product that is source from a supplier, I have multiple prices,
> depending on the quantity I ordered. How would I model this? Is the
> following correct?
> CREATE TABLE ProductSupplierPrice (
> ProductCode NVARCHAR(10) NOT NULL
> REFERENCES Product(ProductCode),
> SupplierCode NVARCHAR(10) NOT NULL
> REFERENCES Supplier(SupplierCode),
> Price MONEY NOT NULL,
> Qty BIGINT NOT NULL,
> PRIMARY KEY (ProductCode, SupplierCode)
> )
> Or, should I just have an identity column in ProductSupplier, and then
> references it from ProductSupplierPrice? Like this:
> CREATE TABLE ProductSupplier (
> ProductSupplierId INT NOT NULL PRIMARY,
> ProductCode NVARCHAR(10) NOT NULL
> REFERENCES Product(ProductCode),
> SupplierCode NVARCHAR(10) NOT NULL
> REFERENCES Supplier(SupplierCode),
> ...,
> ? UNIQUE INDEX (ProductCode, SupplierCode)
> )
> CREATE TABLE ProductSupplierPrice (
> ProductSupplierPriceId INT NOT NULL PRIMARY,
> ProductSupplierId INT NOT NULL
> REFERENCES ProductSupplier(ProductSupplierId),
> Price MONEY NOT NULL,
> Qty BIGINT NOT NULL,
> PRIMARY KEY (ProductCode, SupplierCode)
> )|||Thanks for the hint.
Evergray wrote:
>
> is incorrect
>
>
> is incorrect too because (as you've mentioned above) there are (possible)
> several prices for each product from each supplier depending on quantity.
> So, Qty column should also be a part of the primary key of
> ProductSupplierPrice table. But you may add surrogate key column to this
> table (not null'ed Identity with Unique index on it) for use in FK
> constraints, if needed.
>|||>> I have multiple prices, depending on the quantity I ordered. How would I
model this? <<
I know it is an example, but you might want to use reasonable data
types. Do you really expect a BIGINT quantity?
CREATE TABLE ProductQuantiyBreaks
(product_code CHAR(13) NOT NULL -- upc codes?
REFERENCES Products (product_code)
ON DELETE CASCADE
ON UPDATE CASCADE,
,supplier_id CHAR(10) NOT NULL --duns number
REFERENCES Suppliers(supplier_id),
ON DELETE CASCADE
ON UPDATE CASCADE,
low_qty INTEGER NOT NULL
CHECK(low_qty > 0),
high_qty INTEGER NOT NULL,
CHECK(low_qty < high_qty),
unit_price DECIMAL (12,5) NOT NULL,
PRIMARY KEY (product_code, supplier_id, low_qty));
Now fill in the discounts by (low_qty, high_qty) ranges so you can use
a BETWEEN predicate to locate the price of one unit. Never use
IDENTITY, since it is non-relational; never use MONEY since it is
proprietary and has weird math results.|||Yeah, that's definitely easier when querying with the low_qty and high_qty.
Just one question, having
PRIMARY KEY (product_code, supplier_id, low_qty), does that mean the
table ProductQuantiyBreaks is independent of table ProductSupplier?
Sorry to ask this, but I'm still new to these composite keys.
Thanks
--CELKO-- wrote:
>
> I know it is an example, but you might want to use reasonable data
> types. Do you really expect a BIGINT quantity?
> CREATE TABLE ProductQuantiyBreaks
> (product_code CHAR(13) NOT NULL -- upc codes?
> REFERENCES Products (product_code)
> ON DELETE CASCADE
> ON UPDATE CASCADE,
> ,supplier_id CHAR(10) NOT NULL --duns number
> REFERENCES Suppliers(supplier_id),
> ON DELETE CASCADE
> ON UPDATE CASCADE,
> low_qty INTEGER NOT NULL
> CHECK(low_qty > 0),
> high_qty INTEGER NOT NULL,
> CHECK(low_qty < high_qty),
> unit_price DECIMAL (12,5) NOT NULL,
> PRIMARY KEY (product_code, supplier_id, low_qty));
> Now fill in the discounts by (low_qty, high_qty) ranges so you can use
> a BETWEEN predicate to locate the price of one unit. Never use
> IDENTITY, since it is non-relational; never use MONEY since it is
> proprietary and has weird math results.
>|||having PRIMARY KEY (product_code, supplier_id, low_qty), does that
mean the table ProductQuantiyBreaks is independent of table
ProductSupplier? <<
It means that price breaks are VERY dependent on suppliers. In fact
that important; you need to pick the cheapest guy
product_code supplier_id low_qty high_qty unit_price
========================================
================
1234567890123 1111122222 1 10 10.00
1234567890123 3333344444 1 5 10.00
1234567890123 3333344444 6 10 9.50
Both suppliers are the same up to quantity 5, so I would need to make a
decision. After that, go with 3333344444.|||Ok, I got it to work with:
REFERENCES ProductSupplier(Productcode, SupplierCode)
--CELKO-- wrote:
> having PRIMARY KEY (product_code, supplier_id, low_qty), does that
> mean the table ProductQuantiyBreaks is independent of table
> ProductSupplier? <<
> It means that price breaks are VERY dependent on suppliers. In fact
> that important; you need to pick the cheapest guy
> product_code supplier_id low_qty high_qty unit_price
> ========================================
================
> 1234567890123 1111122222 1 10 10.00
> 1234567890123 3333344444 1 5 10.00
> 1234567890123 3333344444 6 10 9.50
> Both suppliers are the same up to quantity 5, so I would need to make a
> decision. After that, go with 3333344444.
>

How to design "product kits"

Hi,

I've run into a bit of a sticky design issue. We have products in
three categories which I will call 'A', 'B' and 'C'. We have "kits"
which contain three products, one from each category.

Below is some sample SQL to set things up, but I need to ensure that
each kit gets three products -- one from each category. Obviously,
this basic SQL doesn't allow that. Any suggestions? Do I need a
different schema design, or is there something else I should be
looking at?

Cheers,
Curtis

CREATE TABLE category (
id int identity primary key,
name varchar(30)
);

CREATE TABLE products (
id int identity primary key,
name varchar(30),
category_id int references category(id)
);

CREATE TABLE kits (
id int identity primary key,
name varchar(30)
);

CREATE TABLE kit_products (
kit_id int references kits(id),
product_id int references products(id)
);>> We have products in three categories which I will call 'A', 'B' and
'C'. <<

... and you declared them as INTEGER.

>> We have "kits" which contain three products, one from each
category. <<

So, do you have only three categories??

>> Do I need a different schema design, ... <<

Oh yeah! You do not have any keys (IDENTITY is never a key by
definition) and "id" is to vague to be a data element name (read
ISO-11179 rules). Category is singular, while the other table names
are plural; ergo, category must have one and only one row? All the
important data is NULL-able.

I am going to assume that you have so many categories that they
require a separate table; if not, put them in a CHECK() clause.

CREATE TABLE Categories
(category_id INTEGER PRIMARY KEY,
category_name VARCHAR(30) NOT NULL);

CREATE TABLE Products
(product_name VARCHAR(30) NOT NULL,
product_id INTEGER NOT NULL UNIQUE,
category_id INTEGER NOT NULL
REFERENCES Categories(id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (product_id, category_id));

CREATE TABLE ProductKits
(kit_id INTEGER NOT NULL
kit_name VARCHAR(30) NOT NULL,
product_id_1 INTEGER NOT NULL,
category_id_1 INTEGER NOT NULL
FOREIGN KEY (product_id_1, category_id_1)
REFERENCES Products (product_id, category_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
product_id_2 INTEGER NOT NULL,
category_id_2 INTEGER NOT NULL
FOREIGN KEY (product_id_2, category_id_2)
REFERENCES Products (product_id, category_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
product_id_3 INTEGER NOT NULL,
category_id_3 INTEGER NOT NULL
FOREIGN KEY (product_id_3, category_id_3)
REFERENCES Products (product_id, category_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
CHECK (category_id_1 = 1
AND category_id_2 = 2
AND category_id_3 = 3));

The sneaky trick is to put both (product_id, category_id) in the
primary key of Products, so both can be referenced. The product_id is
still unique (another assumption, since your original schema allowed a
product to be named NULL or repeated under a thousand different
IDENTITY numbers, making data integrity impossible). I also assume
that the categories for the kits is (1, 2, 3) instead of ('a', 'b',
'c').

If there are onlyn three categories, then use this and no separate
Categories table:

CREATE TABLE Products
(product_name VARCHAR(30) NOT NULL,
product_id INTEGER NOT NULL UNIQUE,
category_id INTEGER NOT NULL
CHECK(category_id IN (3,2,1))
PRIMARY KEY (product_id, category_id));

How to define the periodicity_Hint value

Hi, all experts here,

I am having a question on defining the value of Periodicity_hint. e.g, I wanna predict the monthly sales amont of a product, and assume the data follows monthly patterns, then in this case, what value should we set for the periodicity_hint parametre?

Thanks a lot for your kind attention and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

The preiodicity hint should be specified as a string that looks as follows:

{a,b,c}

where a,b and c are the hints you specify. In your example, if you have a periodicity in the data of 12, the hint string would look like {12}.

|||

Hi, Shuvro,

Thanks a lot for your kind advices.

With best regards,

Yours sincerely,

|||

But my another question on it is: assume we did not know the periodicity of the data, so it is always better to leave this propertity blank in which case the system willl automatically detect the periodicity of the data?

Thank you very much and I am looking forward to hearing from you shortly for your further advices.

With best regards,

Yours sincerely,

|||Exactly: the system tries to detect the periodicity of the data.
If you leave this blank, the results of the detection are used.

You should provide a value for this parameter when:
- you know the periodicity (and want to hint the system, before it runs the detection step) OR
- you know the periodicity and want to correct the algorithm's auto-detection results

In both cases, your periodicity hints will be analyzed together with the periods automatically detected by the algorithm|||

Hi, all friends,

Thanks a lot for your kind advices.

With best regards,

Yours sincerely,

How to define the periodicity_Hint value

Hi, all experts here,

I am having a question on defining the value of Periodicity_hint. e.g, I wanna predict the monthly sales amont of a product, and assume the data follows monthly patterns, then in this case, what value should we set for the periodicity_hint parametre?

Thanks a lot for your kind attention and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

The preiodicity hint should be specified as a string that looks as follows:

{a,b,c}

where a,b and c are the hints you specify. In your example, if you have a periodicity in the data of 12, the hint string would look like {12}.

|||

Hi, Shuvro,

Thanks a lot for your kind advices.

With best regards,

Yours sincerely,

|||

But my another question on it is: assume we did not know the periodicity of the data, so it is always better to leave this propertity blank in which case the system willl automatically detect the periodicity of the data?

Thank you very much and I am looking forward to hearing from you shortly for your further advices.

With best regards,

Yours sincerely,

|||Exactly: the system tries to detect the periodicity of the data.
If you leave this blank, the results of the detection are used.

You should provide a value for this parameter when:
- you know the periodicity (and want to hint the system, before it runs the detection step) OR
- you know the periodicity and want to correct the algorithm's auto-detection results

In both cases, your periodicity hints will be analyzed together with the periods automatically detected by the algorithm|||

Hi, all friends,

Thanks a lot for your kind advices.

With best regards,

Yours sincerely,

How to define a parameter to show the true values for any of 35 different products?

My boss database is in a way that customers might have baught any of the 35 products of the company. Say if they have bought product# 16, and 27, these two values are true for that customer and the rest are false (the table has 36 columns: 35 for products and 1 for customer ID). How can I show if a customer has bought anything at all (some of them has not bought anything), and if so which numbers as a parameter? I mean I want to have a parameter that user defines to see the result for customers who have number 10, 17 as true? or number 6 as false? I probably need two parameters, one for true and false and one for the number. Although I think if I have just one parameter, that might work too. Let's say the user checks numbers 5,9, and 14 and see the customers who have bought those. So my drop down should have 35 rows for the user to choose from. But how can I make this parameter to work (define wise or query wise)?

Thanks.

I am still stuck on this. Can you people (lots of experts here) help me on this?

Lots of thanks,

Alexan

|||

Its really confusing your question anyway can u explain clearly

You need to Hide some records right?

|||

Thanks for answering Kiran.

Ok. Here is how it goes. We have a table in our database that holds the records for different products. I am going to scale down from 35 to 3 just for making the question easier. Here is how it is:

I have a table with these columns:

- Customer (Holds customer name string)

- Bought Product 1? (Holds Yes or No, indicating if this customer has bought Product 1)

- Bought Product 2? (Holds Yes or No, indicating if this customer has bought Product 2)

- Bought Product 3? (Holds Yes or No, indicating if this customer has bought Product 3)

Now I want to have a parameter so the user can choose to see only the customers who have bought product numbers passed by the parameter.

e.g. The user only wants to see which customers have bought Product 1 & 3, Or which customers have not bought product 1.

I hope this clarifies a little. Please if you still need some more clarification, send a post and I'll be explain more with details.

Waiting to hear from you guys.

I am new in databases and MS SQL, so Could you please help me out?

Lots of thanks,

|||Help please|||

Hi, ok assuming that you have SQL Server 2005 which holds the multivalue parameter and under the assumption your table structure is simplified as follows:

OrderDetail
========
Orderid
PosId
ProductId

Order
====
OrderId
CustomerID

You would have to do a Query like the following:

SELECT CustomerID From Order O
INNER JOIN OderDetail Od
ON O.OrderID = Od.OrderID
WHERE ProductID IN (@.YourParameter)

For the customername you would have to join additionally the Customer table to retrieve this data.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de