Default constraints

Hello

I have about 40 tables where I need to increase the size of the admission number field from smallint to Int . The field used the tables' primary key or part of the key. I am a programmer, but fairly new to SQL Server. I have written some scripts to remove the defaults and primary key constraints off of this field in each table, do the field resize and then put the constraints back on. The scripts get the names and settings of the constraints from system tables before the constraints are dropped, so that they can be reapplied after the field size change.

Is this the best way to do it? Or should I be looking at a DTS package?

I would be grateful for your advice

Shirley

To me (unless you have a database design tool), this sounds like a sufficient way to do it. It is never perfectly easy or anything, but you can get most everything you need to generate a script using the system tables, so I would do that. Sounds messy of course, since you also have to deal with foreign key constraints, but if it is just 40 tables, that probably isn't too bad. (I assume you will make ths size plenty big for the next sixty years this time :)

|||

Thanks very much for that. Yes, Integer will definitely be a big enough field size for the forseeable future for our admission number!

Shirley

default clustered index when selecting primary key

Hi,
I have a list of table which I need to replicate to another database. First
of all, why do I need a primary key to achieve replication (sorry I am new
with replication)
Then I've noticed that if I just click the primary key button from EM on
Design table, then it defaults to a clustered index on the column I've
selected. Does anybody knwow why it defaults to clustered? I am not too kee
n
on clustered indexes for my database.
Thanks,
Panos.Panos Stavroulis. wrote:

> Hi,
> I have a list of table which I need to replicate to another database. Firs
t
> of all, why do I need a primary key to achieve replication (sorry I am new
> with replication)
Without a key the database can't uniquely identify a row in a table.
Every table should have a key. Why would you want a table without one?

> Then I've noticed that if I just click the primary key button from EM on
> Design table, then it defaults to a clustered index on the column I've
> selected. Does anybody knwow why it defaults to clustered? I am not too k
een
> on clustered indexes for my database.
I don't think there's a very good reason why it defaults to clustered.
You can easily change the setting so the default doesn't matter very
much. Also, I would not usually create indexes or keys in Enterprise
Manager. EM is very inefficient in the way it implements schema
changes. Usually it's better to write TSQL for your schema mods. EM
will even script the change for you to review if that helps you get
started.
Most of the time it does pay to have a clustered index on every table.
Tables without clustered indexes should be the exception rather than
the rule.
David Portas
SQL Server MVP
--|||> Does anybody knwow why it defaults to clustered?
A design decision MS made some 12 years ago. Perhaps because you can only ha
ve one CL IX on a table
and you can only have one PK for a table? Important to notice is that you ar
e free to override that
default.

> I am not too keen
> on clustered indexes for my database.
Why not? Clustered indexes are a very important performance tools, and most
DBAs avoid heap tables
and try to decide carefully which index to be the clustered index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in mes
sage
news:E07ADE7B-5424-43E1-AE34-936AF21D27AC@.microsoft.com...
> Hi,
> I have a list of table which I need to replicate to another database. Firs
t
> of all, why do I need a primary key to achieve replication (sorry I am new
> with replication)
> Then I've noticed that if I just click the primary key button from EM on
> Design table, then it defaults to a clustered index on the column I've
> selected. Does anybody knwow why it defaults to clustered? I am not too k
een
> on clustered indexes for my database.
> Thanks,
> Panos.|||You are opening a good subject here. Well the reason I don't use clustered
indexes for many tables is because I don't need to do a select statements
where col_id between 10 and 200 etc if column col_id was my primary key.
I normally select individual records. In fact which way do you think it's
faster? if I have a non clustered index and select where col_id = 100 or a
clustered index?
Thanks.
"Tibor Karaszi" wrote:

> A design decision MS made some 12 years ago. Perhaps because you can only
have one CL IX on a table
> and you can only have one PK for a table? Important to notice is that you
are free to override that
> default.
>
> Why not? Clustered indexes are a very important performance tools, and mos
t DBAs avoid heap tables
> and try to decide carefully which index to be the clustered index.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in m
essage
> news:E07ADE7B-5424-43E1-AE34-936AF21D27AC@.microsoft.com...
>|||> I normally select individual records. In fact which way do you think it's
> faster? if I have a non clustered index and select where col_id = 100 or a
> clustered index?
A clustered index will be marginally faster.
But are you really saying that you have no range queries (lower selectivity
search arguments) at all
in the database? What about joins? Grouping? Sorting? Clustered indexes can
beneficial for a wide
type of operations.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in mes
sage
news:4AFF7C5A-C43F-4C27-AA5B-9A6F65DAAE6E@.microsoft.com...
> You are opening a good subject here. Well the reason I don't use clustered
> indexes for many tables is because I don't need to do a select statements
> where col_id between 10 and 200 etc if column col_id was my primary key.
> I normally select individual records. In fact which way do you think it's
> faster? if I have a non clustered index and select where col_id = 100 or a
> clustered index?
> Thanks.
> "Tibor Karaszi" wrote:
>|||I don't think you quite grasp the point of a clustered index.
I really don't feel like going into it right now, but let's look at your
example:
With a nonclustered index several index pages must be searched before
reaching the 100 value, while with a clustered index this is done in two
quick steps:
1) find the page where the 100 value resides; and
2) only go to that page.
ML
http://milambda.blogspot.com/|||The explanation that I remember best from an old DBA friend of mine is
that a clustered index is like page numbers in a book, whereas
non-clustered indexes are like the index in the back; although the
analogy is not a perfect fit, it does explain the relationship between
clustered and nonclustered indexes. Without page numbers in a
sequential order (clustering), it's tough to find the topic you're
looking for.
Stu|||Thanks for the answers. OK how about this.
We have a query that joins 2 tables, file & file_detail linked by column
col_id which is integer. If I was making a query to select all files and
their detail which are between 100 and 1000, then I would expect that this
query will be faster if I had a clustered index on the table.
However, I personally would expect if the query was "give me file 250" then
the non-clustered solution will be faster? Do you agree with this. How about
covering on non-clustered indexes, I would expect the non clustered option t
o
be faster. Thank you.
"Tibor Karaszi" wrote:

> A clustered index will be marginally faster.
> But are you really saying that you have no range queries (lower selectivit
y search arguments) at all
> in the database? What about joins? Grouping? Sorting? Clustered indexes ca
n beneficial for a wide
> type of operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in m
essage
> news:4AFF7C5A-C43F-4C27-AA5B-9A6F65DAAE6E@.microsoft.com...
>|||> We have a query that joins 2 tables, file & file_detail linked by column
> col_id which is integer. If I was making a query to select all files and
> their detail which are between 100 and 1000, then I would expect that this
> query will be faster if I had a clustered index on the table.
Cluster on which table and which column(s)? It is possible that a clustered
index o the fireign key
column in the file_detail table will improve that join, but that depends on
a lot of other factors.

> However, I personally would expect if the query was "give me file 250" the
n
> the non-clustered solution will be faster?
Non-clustered index on what? For a query with high-selectivity, a clustered
index on the search
column will still outperform a nonc-lustered (but the ihger selectivity, the
more marginally) as SQL
Server doesn't have to fetch each row in the datapage. Unless the non-cluste
red index covers the
query, of course.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in mes
sage
news:CE190BBB-A52C-4D06-9AA8-8E33D983B643@.microsoft.com...
> Thanks for the answers. OK how about this.
> We have a query that joins 2 tables, file & file_detail linked by column
> col_id which is integer. If I was making a query to select all files and
> their detail which are between 100 and 1000, then I would expect that this
> query will be faster if I had a clustered index on the table.
> However, I personally would expect if the query was "give me file 250" the
n
> the non-clustered solution will be faster? Do you agree with this. How abo
ut
> covering on non-clustered indexes, I would expect the non clustered option
to
> be faster. Thank you.
>
> "Tibor Karaszi" wrote:
>|||Sorry meant to say clustered index on col_id on both tables in one case and
nonclustered index on both tables in the second.
So basically basically since for most of the queries you need more
information (columns) than the columns contained in the index (covering
situation) then it's better to create a clustered index on the table as long
as the data arrive in the database in a sequential order and you don't get
page breaks.
Also do you have a view on clustered indexes on tables which are over 8K
long, ie maximum size of a page? Thanks.
"Tibor Karaszi" wrote:

> Cluster on which table and which column(s)? It is possible that a clustere
d index o the fireign key
> column in the file_detail table will improve that join, but that depends o
n a lot of other factors.
>
> Non-clustered index on what? For a query with high-selectivity, a clustere
d index on the search
> column will still outperform a nonc-lustered (but the ihger selectivity, t
he more marginally) as SQL
> Server doesn't have to fetch each row in the datapage. Unless the non-clus
tered index covers the
> query, of course.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in m
essage
> news:CE190BBB-A52C-4D06-9AA8-8E33D983B643@.microsoft.com...
>

Dedub query

I am trying to join two table using a primary key, my problem is that one table has multiple listing of that primary key, I only want to join to the primary key once. Can anyone show me how this can be done?

Table1

acct_no sale_am tran_cd

123 50 2

123 54 1

113 20 9

124 30 7

Table2

acct_no exp_am res_am

123 50 20

113 24 30

124 60 10

What I need:

acct_no sum(sale_am) sum(exp_am) sum(res_am)

123 104 50 20

113 20 24 30

124 30 60 10

Thanks

There are several possibilities. And each one could provide different resultsets.

Please let us know what is your expected output and we can better assist you.

|||

Assuming you want the latest tran_cd value, you can just use a derived table and the ROW_NUMBER() windowed function:

select *

from (select acct_no, sale_am, row_number() over (partition by acct_no order by tran_cd desc) as rowNbr

from table1) as table1
join table2
on table1.acct_no = table2.acct_no

and table1.rowNbr = 1

If this is something that you do often, especially something that needs a lot of performance, I might consider implementing a current_row_flag in your table to denote the row you want to usually use (especially if those rows don't change much)

|||

Code Snippet

createtable #Table1( acct_no int, sale_am money, tran_cd int)

insertinto #Table1

select 123, 50, 2

union allselect 123, 54, 1

union allselect 113, 20, 9

union allselect 124, 30, 7

createtable #Table2( acct_no int, exp_am money, res_am money)

insertinto #Table2

select 123, 50, 20

union allselect 113, 24, 30

union allselect 124, 60, 10

select t1.acct_no,sum(t1.sale_am)as sale_am,

sum(t2.exp_am)as exp_am,sum(t2.res_am)as res_am

from #Table1 t1

innerjoin #Table2 t2

on t1.acct_no = t2.acct_no

groupby t1.acct_no

sql

Dedicated Database Connection

We are looking at ways to optimize our process for retrieving primary keys.
Currently, we are creating a new database call to get each ID we need. We
are considering the idea of keeping a database connection open with a single
responsibility of retrieving a new primary key. Can you provide me a
response to the following questions?:
Are there any timeouts on open connections to the database from within SQL
Server?
Can you think of negative impacts this could have on the database to have a
consistent open connections?newie wrote:
> We are looking at ways to optimize our process for retrieving primary
> keys. Currently, we are creating a new database call to get each ID
> we need. We are considering the idea of keeping a database
> connection open with a single responsibility of retrieving a new
> primary key. Can you provide me a response to the following
> questions?:
> Are there any timeouts on open connections to the database from
> within SQL Server?
> Can you think of negative impacts this could have on the database to
> have a consistent open connections?
Conistent open connections are the standard for many client-server
applications. Even something like IIS would likely use connection
pooling and keep a certain number of connections open. There is no
problem with an open connection. In fact, in most cases it can improve
application performance.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||take a look at connection pooling
http://sqlservercode.blogspot.com/
"newie" wrote:
> We are looking at ways to optimize our process for retrieving primary keys.
> Currently, we are creating a new database call to get each ID we need. We
> are considering the idea of keeping a database connection open with a single
> responsibility of retrieving a new primary key. Can you provide me a
> response to the following questions?:
> Are there any timeouts on open connections to the database from within SQL
> Server?
> Can you think of negative impacts this could have on the database to have a
> consistent open connections?
>sql

Dedicated Database Connection

We are looking at ways to optimize our process for retrieving primary keys.
Currently, we are creating a new database call to get each ID we need. We
are considering the idea of keeping a database connection open with a single
responsibility of retrieving a new primary key. Can you provide me a
response to the following questions?:
Are there any timeouts on open connections to the database from within SQL
Server?
Can you think of negative impacts this could have on the database to have a
consistent open connections?
newie wrote:
> We are looking at ways to optimize our process for retrieving primary
> keys. Currently, we are creating a new database call to get each ID
> we need. We are considering the idea of keeping a database
> connection open with a single responsibility of retrieving a new
> primary key. Can you provide me a response to the following
> questions?:
> Are there any timeouts on open connections to the database from
> within SQL Server?
> Can you think of negative impacts this could have on the database to
> have a consistent open connections?
Conistent open connections are the standard for many client-server
applications. Even something like IIS would likely use connection
pooling and keep a certain number of connections open. There is no
problem with an open connection. In fact, in most cases it can improve
application performance.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||take a look at connection pooling
http://sqlservercode.blogspot.com/
"newie" wrote:

> We are looking at ways to optimize our process for retrieving primary keys.
> Currently, we are creating a new database call to get each ID we need. We
> are considering the idea of keeping a database connection open with a single
> responsibility of retrieving a new primary key. Can you provide me a
> response to the following questions?:
> Are there any timeouts on open connections to the database from within SQL
> Server?
> Can you think of negative impacts this could have on the database to have a
> consistent open connections?
>

Dedicated Database Connection

We are looking at ways to optimize our process for retrieving primary keys.
Currently, we are creating a new database call to get each ID we need. We
are considering the idea of keeping a database connection open with a single
responsibility of retrieving a new primary key. Can you provide me a
response to the following questions?:
Are there any timeouts on open connections to the database from within SQL
Server?
Can you think of negative impacts this could have on the database to have a
consistent open connections?newie wrote:
> We are looking at ways to optimize our process for retrieving primary
> keys. Currently, we are creating a new database call to get each ID
> we need. We are considering the idea of keeping a database
> connection open with a single responsibility of retrieving a new
> primary key. Can you provide me a response to the following
> questions?:
> Are there any timeouts on open connections to the database from
> within SQL Server?
> Can you think of negative impacts this could have on the database to
> have a consistent open connections?
Conistent open connections are the standard for many client-server
applications. Even something like IIS would likely use connection
pooling and keep a certain number of connections open. There is no
problem with an open connection. In fact, in most cases it can improve
application performance.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||take a look at connection pooling
http://sqlservercode.blogspot.com/
"newie" wrote:

> We are looking at ways to optimize our process for retrieving primary keys
.
> Currently, we are creating a new database call to get each ID we need. We
> are considering the idea of keeping a database connection open with a sing
le
> responsibility of retrieving a new primary key. Can you provide me a
> response to the following questions?:
> Are there any timeouts on open connections to the database from within SQL
> Server?
> Can you think of negative impacts this could have on the database to have
a
> consistent open connections?
>

declaring primary/foreign keys in sql

Hi

If i have two foreign keys in a table which make up my primary key, do i actually write in the SQL that they are the primary keys?

I know you can't declare more than one primary key in a table, eg

CREATE TABLE FACILITIES(
venue_id INTEGER PRIMARY KEY REFERENCES VENUE NOT NULL,
facility_id INTEGER PRIMARY KEY REFERENCES FACILITY_TYPE NOT NULL
)

...so do i just reference them as foreign keys? :S

eg

CREATE TABLE FACILITIES(
venue_id INTEGER REFERENCES VENUE NOT NULL,
facility_id INTEGER REFERENCES FACILITY_TYPE NOT NULL
)

thanks in advance!create table facilities
( venue_id integer not null references venue
, facility_id integer not null references facility_type
, primary key (venue_id, facility_id)
)|||create table facilities
( venue_id integer not null references venue
, facility_id integer not null references facility_type
, primary key (venue_id, facility_id)
)

Ahh right, i see, so you have to define the primary key seperatly! - i have a 450+ page book on database design and it doesn't mention how to do that :mad: ...thanks again r937|||oh, it's gotta be in there somewhere!!

i cannot imagine a database book that overlooks a composite primary key

tell me, does it discuss how to implement a many-to-many relationship? what does the table in the middle look like?|||It tells you that you can create a primary key using two foreign keys (composite) and shows it in the ERD, but i couldn't seem to find an example on how to actually create it!|||too bad, eh -- well, now you know :)

and of course your database system's documentation would cover it in its CREATE TABLE syntax

DECLARE in SQL CE

Can I use DECLARE in SQL 2005 Compact, and if not, how do I do INSERTs into tables which have columns with Primary Key constraints?

Matt

You do not have to specify a value for fields with primary key/identity constraints. See this help topic for more info:

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

|||Sorry, I didn't mean primary key constraints. I meant, how do I insert into Table1 if it has a Column which is a Foreign Key referencing Table2?

Matt
|||If you are referring to IDENTITY columns, you can query "SELECT @.@.IDENTITY" (on the same open connection where you just did the INSERT), and use this value as the foeign key in the next INSERT.

deceptively simple join / select question

Ok, I have two tables with a child/parent or one -> many relationship:

parent_table:
pid int primary key
pname varchar

child_table:
cid int primary key
pid int
cname varchar

Say the contents of these two tables are:

parent_table:
pid pname:
1 Ben
2 Jesse
3 Michael

child_table
pid cid cname
1 1 ben_Child1
1 2 ben_Child2
1 3 ben_Child3
2 4 jesse_Child1
2 5 jesse_Child2
2 6 jesse_Child3
3 7 michael_Child1
3 8 michael_Child2
3 9 michael_Child3

Now what I would like to be able to do is:

select pname, cname
from
parent table a,
child_table b
where a.pid = b.pid

Except! Instead of getting the results in the form of:

Ben ben_Child1
Ben ben_Child2
Ben ben_Child3
...

I would like them in

Ben ben_Child1 ben_Child2

Now normally this would be impossible (I think) since the query would return an unknown number of columns. But in this case I only care about the FIRST TWO children for each parent. So I'm sure there's some way to do this with a simple select, but I don't know how. Anyone?Are you trying to get everything from both tables in the form pname cname? If so what about a Cross Join?

Originally posted by blm14_cu
Ok, I have two tables with a child/parent or one -> many relationship:

parent_table:
pid int primary key
pname varchar

child_table:
cid int primary key
pid int
cname varchar

Say the contents of these two tables are:

parent_table:
pid pname:
1 Ben
2 Jesse
3 Michael

child_table
pid cid cname
1 1 ben_Child1
1 2 ben_Child2
1 3 ben_Child3
2 4 jesse_Child1
2 5 jesse_Child2
2 6 jesse_Child3
3 7 michael_Child1
3 8 michael_Child2
3 9 michael_Child3

Now what I would like to be able to do is:

select pname, cname
from
parent table a,
child_table b
where a.pid = b.pid

Except! Instead of getting the results in the form of:

Ben ben_Child1
Ben ben_Child2
Ben ben_Child3
...

I would like them in

Ben ben_Child1 ben_Child2

Now normally this would be impossible (I think) since the query would return an unknown number of columns. But in this case I only care about the FIRST TWO children for each parent. So I'm sure there's some way to do this with a simple select, but I don't know how. Anyone?|||Originally posted by JODonnell
Are you trying to get everything from both tables in the form pname cname? If so what about a Cross Join?

No, a cross join would give me EVERYTHING. I am trying to get a subset of the results but in addition I am trying to map two rows to two columns eg instead of:

pname1 cname1
pname1 cname2

I want:

pname1 cname1 cname2|||Originally posted by blm14_cu
No, a cross join would give me EVERYTHING. I am trying to get a subset of the results but in addition I am trying to map two rows to two columns eg instead of:

pname1 cname1
pname1 cname2

I want:

pname1 cname1 cname2

What about GROUP BY:

Select p.*,c.* From Ptable P Join Ctable C ON P.pid = C.pid Where [P.pid = C.pid] GROUP BY P.pid

Sorry for the mess but it's almost 5.

John|||Still no good. The group by wont help because I'm not doing any sums or avgs or counts or anything. Adding the group by wont change the results at all actually, from what I know.|||I was bored.

I think this is what you're looking for

Rgds,
Jim.

declare @.d_id int;
declare @.c_name varchar(100);
declare @.c_arr varchar(2000);
declare @.tmp varchar(100);

declare @.x table([id] int, [name] varchar(2000))

DECLARE d cursor for
select depid
from dept;

OPEN d
FETCH NEXT FROM d INTO @.d_id
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.tmp='';
set @.c_arr='';

DECLARE c CURSOR FOR
SELECT name
FROM emp
where deptid = @.d_id

OPEN c
FETCH next from c into @.c_name
while @.@.fetch_status = 0
BEGIN
print @.d_id
print @.c_name

set @.tmp = @.c_arr
set @.c_arr = @.c_name+','+@.tmp
fetch next from c into @.c_name
END
CLOSE c
DEALLOCATE c
if (len(@.c_arr)>1)
begin Insert @.x values(@.d_id, substring(@.c_arr,1,len(@.c_arr)-1))end

FETCH NEXT FROM d INTO @.d_id
END
CLOSE d
DEALLOCATE d

select id, name as name from @.x
GO|||You might check yesterday's thread (http://www.dbforums.com/t989683.html) on this topic.

-PatP