create an Indexes/Keys Property with T-SQL

is there a function that i can use in a store procedure that allow me to create an Indexes/Keys Property
thanxYes. CREATE INDEX.

-PatP|||Yes. CREATE INDEX.

-PatP

excuse me while I climb back on my barstool...um office chair...

LNHockey...seriously though... alittle more background on what you're trying to do...

"create index"....why I outta.....|||Yes ok..excuse me.

i do this
<b>
ALTER TABLE TblSalle ADD [IdTypeTaxe] [int] NOT NULL default(0)<br>
CREATE INDEX PK_TblSalle ON TblSalle (IdTypeTaxe)</b>

and would like to assign is "Selected index value" to IX_IdTypeTaxe that i userly have in a combobox when i use the sql manager

but i want to do it in a store proc..

or how can i modify that value after i add the new column to my table|||Yup...I'm lost..

Huh?|||You know when you go to design mode of the table and go properties of the selected column and select the tab "Indexes/keys". under that you can switch the type "Primary key to Index" ?? !!!

just wondering if we can do the same thing in a Store proc using a function kind of thing

thanx !

create a empty DB from a existing DB

how to: create a empty DB from a existing DB keeping all fields and keys intact

A clean way to create an empty database from an existing populated database is to script all the database objects in the database including Stored Procedures, Tables, User-defined data types and Views. Follow these steps in SQL Server 2005:

1. In SQL Server Management Studio, right-click the database and click Tasks > Generate Scripts...

2. Click the Next button on the Welcome dialog box.

3. Check the "Script all objects in the selected database" option and click the Finish button. The script will be generated and dumped into a query window.

4. Save the script as a SQL Server script file.

5. Run the script file on your destination server to recreate the database without the data.

Hope this helps.

|||Is there a way to generate scripts that also contain the data?

create a empty DB from a existing DB

how to: create a empty DB from a existing DB keeping all fields and keys intact

A clean way to create an empty database from an existing populated database is to script all the database objects in the database including Stored Procedures, Tables, User-defined data types and Views. Follow these steps in SQL Server 2005:

1. In SQL Server Management Studio, right-click the database and click Tasks > Generate Scripts...

2. Click the Next button on the Welcome dialog box.

3. Check the "Script all objects in the selected database" option and click the Finish button. The script will be generated and dumped into a query window.

4. Save the script as a SQL Server script file.

5. Run the script file on your destination server to recreate the database without the data.

Hope this helps.

|||Is there a way to generate scripts that also contain the data?

covering Primary Keys.. What's too much

I have a table which I'd like to create a Primary Key that would have to
cover 6 fields to enforce uniqueness.
My question is, is that too much. I know it's common to have tables with
PK's that cover 2-3 fields, but 6 seems like a little much. [3 int fields, 2
varchar(5) fields, 1 tinyint]
I know I can use a surrogate "ID" identity field as the PK, but I prefer to
use PK's that are more natural if possible.
Any thoughts on this?
Thanks,
ChrisWhatever has to be unique must be constrained to be unique. IOW, if your
business rules require that this table have a 6-col combination that is
unique, then you should make that unique - either via a PRIMARY KEY or
UNIQUE constraint. That said, if you will have other tables referencing
this table, then it would make sense to create a surrogate key and then use
that key in the FOREIGN KEY constraints.
That said, consider the surrogate key anyway and cluster on it. Make it,
say, your PK. Then, create a UNIQUE constraint on the 6 col combo. Inserts
into the table - if you use an identity for the PK, will perform well and
the table (i.e. clustered index) will not need to be defragged. The
nonclustered index on the UNIQUE constraint would likely need a fill factor
< 100 to mitigate fragmentation.
HTH
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Chris" <rooster575@.hotmail.com> wrote in message
news:%23VyZlhmhGHA.4864@.TK2MSFTNGP05.phx.gbl...
I have a table which I'd like to create a Primary Key that would have to
cover 6 fields to enforce uniqueness.
My question is, is that too much. I know it's common to have tables with
PK's that cover 2-3 fields, but 6 seems like a little much. [3 int fields, 2
varchar(5) fields, 1 tinyint]
I know I can use a surrogate "ID" identity field as the PK, but I prefer to
use PK's that are more natural if possible.
Any thoughts on this?
Thanks,
Chris|||This table holds only FK's and could be considered a "detail" table.
It's also almost completely a "read" table. Writing is completed and then
done again very seldomly.
If I add an "ID" column as a PK, it will never be referenced by another
table. [No FK's will exist], which makes me lean more towards the 6-key PK
option. Does this sway your answer at all?
I appreciate your help. After re-reading your response, the solution seems
clear. What do you think?
-Chris
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ORD4WpmhGHA.3896@.TK2MSFTNGP02.phx.gbl...
> Whatever has to be unique must be constrained to be unique. IOW, if your
> business rules require that this table have a 6-col combination that is
> unique, then you should make that unique - either via a PRIMARY KEY or
> UNIQUE constraint. That said, if you will have other tables referencing
> this table, then it would make sense to create a surrogate key and then
> use
> that key in the FOREIGN KEY constraints.
> That said, consider the surrogate key anyway and cluster on it. Make it,
> say, your PK. Then, create a UNIQUE constraint on the 6 col combo.
> Inserts
> into the table - if you use an identity for the PK, will perform well and
> the table (i.e. clustered index) will not need to be defragged. The
> nonclustered index on the UNIQUE constraint would likely need a fill
> factor
> < 100 to mitigate fragmentation.
> HTH
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Chris" <rooster575@.hotmail.com> wrote in message
> news:%23VyZlhmhGHA.4864@.TK2MSFTNGP05.phx.gbl...
> I have a table which I'd like to create a Primary Key that would have to
> cover 6 fields to enforce uniqueness.
> My question is, is that too much. I know it's common to have tables with
> PK's that cover 2-3 fields, but 6 seems like a little much. [3 int fields,
> 2
> varchar(5) fields, 1 tinyint]
> I know I can use a surrogate "ID" identity field as the PK, but I prefer
> to
> use PK's that are more natural if possible.
> Any thoughts on this?
> Thanks,
> Chris
>|||If that is the case, then you're probably fine without the surrogate key.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Chris" <rooster575@.hotmail.com> wrote in message
news:%23P2RCumhGHA.3996@.TK2MSFTNGP03.phx.gbl...
This table holds only FK's and could be considered a "detail" table.
It's also almost completely a "read" table. Writing is completed and then
done again very seldomly.
If I add an "ID" column as a PK, it will never be referenced by another
table. [No FK's will exist], which makes me lean more towards the 6-key PK
option. Does this sway your answer at all?
I appreciate your help. After re-reading your response, the solution seems
clear. What do you think?
-Chris
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ORD4WpmhGHA.3896@.TK2MSFTNGP02.phx.gbl...
> Whatever has to be unique must be constrained to be unique. IOW, if your
> business rules require that this table have a 6-col combination that is
> unique, then you should make that unique - either via a PRIMARY KEY or
> UNIQUE constraint. That said, if you will have other tables referencing
> this table, then it would make sense to create a surrogate key and then
> use
> that key in the FOREIGN KEY constraints.
> That said, consider the surrogate key anyway and cluster on it. Make it,
> say, your PK. Then, create a UNIQUE constraint on the 6 col combo.
> Inserts
> into the table - if you use an identity for the PK, will perform well and
> the table (i.e. clustered index) will not need to be defragged. The
> nonclustered index on the UNIQUE constraint would likely need a fill
> factor
> < 100 to mitigate fragmentation.
> HTH
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Chris" <rooster575@.hotmail.com> wrote in message
> news:%23VyZlhmhGHA.4864@.TK2MSFTNGP05.phx.gbl...
> I have a table which I'd like to create a Primary Key that would have to
> cover 6 fields to enforce uniqueness.
> My question is, is that too much. I know it's common to have tables with
> PK's that cover 2-3 fields, but 6 seems like a little much. [3 int fields,
> 2
> varchar(5) fields, 1 tinyint]
> I know I can use a surrogate "ID" identity field as the PK, but I prefer
> to
> use PK's that are more natural if possible.
> Any thoughts on this?
> Thanks,
> Chris
>|||Thanks again.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eebldFnhGHA.1612@.TK2MSFTNGP04.phx.gbl...
> If that is the case, then you're probably fine without the surrogate key.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Chris" <rooster575@.hotmail.com> wrote in message
> news:%23P2RCumhGHA.3996@.TK2MSFTNGP03.phx.gbl...
> This table holds only FK's and could be considered a "detail" table.
> It's also almost completely a "read" table. Writing is completed and then
> done again very seldomly.
> If I add an "ID" column as a PK, it will never be referenced by another
> table. [No FK's will exist], which makes me lean more towards the 6-key PK
> option. Does this sway your answer at all?
> I appreciate your help. After re-reading your response, the solution seems
> clear. What do you think?
> -Chris
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ORD4WpmhGHA.3896@.TK2MSFTNGP02.phx.gbl...
>|||>> I have a table which I'd like to create a Primary Key that would have to cove
r 6 fields [sic] to enforce uniqueness. My question is, is that too much. <<
This is rare but possible. Since we do not have any specs or knwo
anything about the problem, all we can do is generalize. I have seen
some people create super-keys (sertial number + manufacturer, not
knowning that serial number has manufacturer in it).
1) it is not a surrogate key because it is exposed to the user; that is
the definition from Dr. Codd.
2) you will need to use a UNIQUE (c1,.. ,c6) constraint anyway, so you
now have two indexes
3) You will need code to assure that the IDENTITY is always validate
and points to the right target.

Counting items

I want to run a select query and also using that items keys get the count of
the items in the inventory db, kinda like this below but does not Parse
SELECT dbo.Pattern.Pattern_ID, dbo.ProductType.ProdType_ID,
dbo.Manufact_Company.ManuComp_ID, XCount AS
(SELECT Count(ID)
FROM wholesaleinv
WHERE Pattern_ID = dbo.Pattern.Pattern_ID
AND ManuComp_ID = dbo.Manufact_Company.ManuComp_ID AND
Prodtype_ID = dbo.PTC2CLEAN.ProdType_ID)
FROM dbo.Pattern INNER JOIN
dbo.PTC2CLEAN ON dbo.Pattern.Pattern_ID =
dbo.PTC2CLEAN.Pattern_ID INNER JOIN
dbo.ProductType ON dbo.PTC2CLEAN.ProdType_ID =
dbo.ProductType.ProdType_ID INNER JOIN
dbo.Manufact_Company ON dbo.PTC2CLEAN.ManuComp_ID =
dbo.Manufact_Company.ManuComp_IDPlease post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"TdarTdar" <TdarTdar@.discussions.microsoft.com> wrote in message
news:231CE73E-0716-46C4-A4C4-B7C688023022@.microsoft.com...
>I want to run a select query and also using that items keys get the count
>of
> the items in the inventory db, kinda like this below but does not Parse
> SELECT dbo.Pattern.Pattern_ID, dbo.ProductType.ProdType_ID,
> dbo.Manufact_Company.ManuComp_ID, XCount AS
> (SELECT Count(ID)
> FROM wholesaleinv
> WHERE Pattern_ID = dbo.Pattern.Pattern_ID
> AND ManuComp_ID = dbo.Manufact_Company.ManuComp_ID AND
> Prodtype_ID = dbo.PTC2CLEAN.ProdType_ID)
> FROM dbo.Pattern INNER JOIN
> dbo.PTC2CLEAN ON dbo.Pattern.Pattern_ID =
> dbo.PTC2CLEAN.Pattern_ID INNER JOIN
> dbo.ProductType ON dbo.PTC2CLEAN.ProdType_ID =
> dbo.ProductType.ProdType_ID INNER JOIN
> dbo.Manufact_Company ON dbo.PTC2CLEAN.ManuComp_ID =
> dbo.Manufact_Company.ManuComp_ID|||A quick glance reveals a peculiarity - "..., XCount AS (select..." is wrong.
Replace it with "(select ...) as XCount".
Any other help will be available if you share your DDL with us.
ML