Any way to use files from separate directories in mgmt studio?

Is there any way to add files from 2 different directories and have them retain their directory identity within the solution? When I attempt to add a file from another directory, mgmt studio makes a copy of the file in the default directory for the solution, which is not what I want. I want to be able to edit the script and have it stay (and update) within the directory from which it came. The only way I see now is to have multiple solutions, but since mgmt studio appears to only allow me to have one solution open at a time, this doesn't work either. Any suggestions?How are you performing this change, DTS or script?

Any resources about Stored Procedures & Scope Identity?

i am a beginner looking for such resources, MSDN is one but can be a little difficult to understand

For general and extensive information go tohttp://www.sqlservercentral.com/

However, if you have a specific problem or question, post it here and someone will know the answer.

Kind regards

Scotty

|||

Books On Line (free download) from MS is the best resource.

|||There are some good stored procedure tutorials athttp://www.codeproject.com/database/hkstoredproc.asp (Everything you wanted to know about Stored Procedures)

Any performance issues if we use BigInt

In the past, I've always used "int" for the Identity field. But as the
application usage increases, it seems to demand for values that exceed the
"int" scope. I'm thinking of upgrading all my Primary Keys that were "Int"
to "BigInt".
I'd like to know if there's any performance degradation by doing so.
Also, what would be a compatible datatype of BigInt in Classic ASP and in
ASP.NET 2.0 C#.
Thanks for your response.Bob (spamfree@.nospam.com) writes:
> In the past, I've always used "int" for the Identity field. But as the
> application usage increases, it seems to demand for values that exceed
> the "int" scope. I'm thinking of upgrading all my Primary Keys that
> were "Int" to "BigInt".
> I'd like to know if there's any performance degradation by doing so.
There is one, although not dramatic. bigint takes up eight bytes, where
as int takes up four bytes. This means that you row size increases, and
you can fit fewer rows per page, so to read the same number of rows, SQL
Server will have to access more pages.
int goes all the way to 2,147 milliards, which is quite lot, so int may
last longer than you believe.

> Also, what would be a compatible datatype of BigInt in Classic ASP and in
> ASP.NET 2.0 C#.
No idea.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> Also, what would be a compatible datatype of BigInt in Classic ASP and in
> ASP.NET 2.0 C#.
Bigint is a 64-bit number so you can use Int64 in .NET languages. Assuming
you are using VBScript in ASP, all data types are variant. I believe
variant subtype double would be used for a large bigint value.
Hope this helps.
Dan Guzman
SQL Server MVP
"Bob" <spamfree@.nospam.com> wrote in message
news:u$pzOOF%23FHA.740@.TK2MSFTNGP11.phx.gbl...
> In the past, I've always used "int" for the Identity field. But as the
> application usage increases, it seems to demand for values that exceed the
> "int" scope. I'm thinking of upgrading all my Primary Keys that were
> "Int"
> to "BigInt".
> I'd like to know if there's any performance degradation by doing so.
> Also, what would be a compatible datatype of BigInt in Classic ASP and in
> ASP.NET 2.0 C#.
> Thanks for your response.
>|||The right answer is not to ever use IDENTITY, but to find a relational
key.
You don't seem to know that fields and columns are totally different,
which is probably why you ask this kind of question about a table
property.
In effect, if this was a furniture forum you would be asking for the
best kind of rocks to smash screws into furniture. The kludge answer
is "Granite!", but the right answer is to take the time to learn about
screws, screw drivers, nails, glue, etc.|||> int goes all the way to 2,147 milliards,
Erland, you can set the seed value to -2 billion and get twice as much
values out of a regular int, right?|||Alexander Kuznetsov (AK_TIREDOFSPAM@.hotmail.COM) writes:
> Erland, you can set the seed value to -2 billion and get twice as much
> values out of a regular int, right?
Correct, as long as you plan for it ahead. Not very fun of changing all
that when you are about reach 2^31-1.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Any harm in adding a primary key...

after my table has been created and populated with data?
I have an identity column (named 'CID') on my customers table. This
value is often used in my queries and I'd like to take advantage of a
clustered
index (since none currently exist on the table).
Will making this column a primary key have any adverse effects? There are
currently 3,000 rows in the table and it's 10 columns wide."Eric" <Eric@.discussions.microsoft.com> wrote in message
news:D344667A-6AF7-42E1-B907-8C0C945D6C2E@.microsoft.com...
> after my table has been created and populated with data?
> I have an identity column (named 'CID') on my customers table. This
> value is often used in my queries and I'd like to take advantage of a
> clustered
> index (since none currently exist on the table).
> Will making this column a primary key have any adverse effects? There are
> currently 3,000 rows in the table and it's 10 columns wide.
1. Why wasn't the table created with a PK in place already.
2. Just because you make it a primary key, doesn't make it a clustered
index. When you create a Primary Key in SQL Server it will choose Clustered
by default, but it could be non-clustered if you tell it to.
So to answer your question, you could just as easily create a clustered
index on that CID column as create a Primary key on it. Should it be the
Primary Key? <shrug> Who knows, you didn't post DDL, so it's hard to say.
Since it is not a PK, I will assume that you are not using FK constraints
either. This could be a very serious issue in your database. The PK and FK
constraints are there to ensure the relational integrity of your data. If
you have your customers table as described above, and an Orders table.
Without these constraints, there is really nothing stopping someone from
adding Orders to your database that don't map to any Customer, or delete
Customers who may have orders pending for shipment.
You should really finish modelling your system and include the PK and FK
constraints, and any other constraints necessary to ensure the various
integreties of your system (entity integrity, relational integrity and
domain integrity).
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||1) I have no idea as I did not design the db.
2) I wanted to take advantage of using a clustered index.
I understand the importance of using the PK/FK for integrity. We are
planning a db redesign in the near future, but for the time being, I just
wanted to add the clustered index via a PK for performance purposes.
"Rick Sawtell" wrote:

> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:D344667A-6AF7-42E1-B907-8C0C945D6C2E@.microsoft.com...
>
> 1. Why wasn't the table created with a PK in place already.
> 2. Just because you make it a primary key, doesn't make it a clustered
> index. When you create a Primary Key in SQL Server it will choose Cluster
ed
> by default, but it could be non-clustered if you tell it to.
>
> So to answer your question, you could just as easily create a clustered
> index on that CID column as create a Primary key on it. Should it be the
> Primary Key? <shrug> Who knows, you didn't post DDL, so it's hard to say
.
> Since it is not a PK, I will assume that you are not using FK constraints
> either. This could be a very serious issue in your database. The PK and
FK
> constraints are there to ensure the relational integrity of your data. I
f
> you have your customers table as described above, and an Orders table.
> Without these constraints, there is really nothing stopping someone from
> adding Orders to your database that don't map to any Customer, or delete
> Customers who may have orders pending for shipment.
> You should really finish modelling your system and include the PK and FK
> constraints, and any other constraints necessary to ensure the various
> integreties of your system (entity integrity, relational integrity and
> domain integrity).
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:7585798D-DD21-4D84-9135-A21EB8F786DA@.microsoft.com...
> 1) I have no idea as I did not design the db.
> 2) I wanted to take advantage of using a clustered index.
> I understand the importance of using the PK/FK for integrity. We are
> planning a db redesign in the near future, but for the time being, I just
> wanted to add the clustered index via a PK for performance purposes.
> "Rick Sawtell" wrote:
>
Then by all means create a PK and specify Clustered.
ALTER TABLE dbo.Customers
ADD CONSTRAINT PK_Customers_CID
PRIMARY KEY CLUSTERED (CID)
To get even better performance, you should also create indexes on what will
eventually be your FK columns, so if you had an Orders table, you might do
the following.
CREATE NONCLUSTERED INDEX IX_Orders_CID ON dbo.Customers (CID)
Rick Sawtell
MCT, MCSD, MCDBA|||Eric wrote:
> 1) I have no idea as I did not design the db.
> 2) I wanted to take advantage of using a clustered index.
> I understand the importance of using the PK/FK for integrity. We are
> planning a db redesign in the near future, but for the time being, I just
> wanted to add the clustered index via a PK for performance purposes.
> "Rick Sawtell" wrote:
>
Your question is whether adding the cluster key could do any harm. If
your database is this weakly designed then I wouldn't take anything for
granted. Maybe they left out an ORDER BY on a cursor somewhere and
adding an extra index would cause some logic to fail. Test, test, test.
David Portas
SQL Server MVP
--|||David:
If you could please clarify the effect a cursor may have w/an omitted 'order
by' cluase. I'm using a cursor in only one instance on my db. I would like
to add a clustered index to one of my tables (Company.ID) but this table is
in fact included in my cursor statement. Appreciate your input.
"David Portas" wrote:

> Eric wrote:
> Your question is whether adding the cluster key could do any harm. If
> your database is this weakly designed then I wouldn't take anything for
> granted. Maybe they left out an ORDER BY on a cursor somewhere and
> adding an extra index would cause some logic to fail. Test, test, test.
> --
> David Portas
> SQL Server MVP
> --
>|||I'm not David, but:
Some developer seems to think that the order of rows returned from a SELECT
statement can be
determined by having a certain index in place, or some other physical attrib
ute of the table. That
is not true. The only way to guarantee that the result from a SELECT stateme
nt is ordered in any
certain way is to have ORDER BY. This is not exclusive to cursor operations,
btw.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:29804387-5EF8-434A-8BFA-D05A1303CBAE@.microsoft.com...
> David:
> If you could please clarify the effect a cursor may have w/an omitted 'ord
er
> by' cluase. I'm using a cursor in only one instance on my db. I would li
ke
> to add a clustered index to one of my tables (Company.ID) but this table i
s
> in fact included in my cursor statement. Appreciate your input.
> "David Portas" wrote:
>|||Eric wrote:
> David:
> If you could please clarify the effect a cursor may have w/an omitted 'ord
er
> by' cluase. I'm using a cursor in only one instance on my db. I would li
ke
> to add a clustered index to one of my tables (Company.ID) but this table i
s
> in fact included in my cursor statement. Appreciate your input.
> "David Portas" wrote:
>
If you write cursor code without specifying ORDER BY in the DECLARE
statement and if that cursor uses logic that assumes a particular
ordering to the data then it may break at some point if the execution
plan changes. This is made more likely if you create or drop indexes -
i.e. the assumed order of the cursor may change. Always specify ORDER
BY if the ordering is important to you.
If you avoid cursors and stick to set-based code you'll avoid this
potential problem. Most cursors are unnecessary and are written by
people who don't know SQL well enough to do any better.
David Portas
SQL Server MVP
--|||If CID is unique (and it must be unique since it is an identity column), and
there are no natural key candidates, then do make it the primary key. Every
table needs a primary key of some type.
http://www.aspfaq.com/show.asp?id=2504
Also, 3000 rows x 10 columns is practically nothing, and if it's being hit
frequently, SQL Server is probably keeping a complete in the buffer cache
anyway and reducing the need to read it from disk. It would not benefit from
a clustered index (although it wouldn't hurt).
http://msdn.microsoft.com/library/d...br />
1zu4.asp
http://msdn.microsoft.com/library/d...br />
3339.asp
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:D344667A-6AF7-42E1-B907-8C0C945D6C2E@.microsoft.com...
> after my table has been created and populated with data?
> I have an identity column (named 'CID') on my customers table. This
> value is often used in my queries and I'd like to take advantage of a
> clustered
> index (since none currently exist on the table).
> Will making this column a primary key have any adverse effects? There are
> currently 3,000 rows in the table and it's 10 columns wide.|||On Wed, 28 Dec 2005 08:25:05 -0800, "Eric"
<Eric@.discussions.microsoft.com> wrote:
>after my table has been created and populated with data?
No harm.

>I have an identity column (named 'CID') on my customers table. This
>value is often used in my queries and I'd like to take advantage of a
>clustered index (since none currently exist on the table).
PK is one thing, clustering is an option.
Alternatively clustering is one thing, PK is another.
But if you want to do both at once, go ahead.
Are there any other existing indexes? Creating a clustered index does
mean rewriting the table and rebuilding the other indexes.

>Will making this column a primary key have any adverse effects?
Only that it will then enforce uniqueness, can't have null values,
just the standard behaviors of a PK.

>There are
>currently 3,000 rows in the table and it's 10 columns wide.
Extremely modest by modern standards.
J.