Question about restrictions

Hi. I'm writing delete queries and I have the following question: if a table
has foreign keys, and I delete the fields in the tables that references the
table I want to delete, can I delete the table or must I delete the
restrictions too?
I'm having errors and I'm thinking if that's the problem.
Regards,
Diego F.You cannot drop the table that has a column referenced by another table. You
will have to remove the constraint before you can do that.
Anith|||There are basically two general ways to do this:
1) you can declare foreign keys with the ON DELETE CASCADE option - that way
when you delete the referenced (primary key table) row the referring (foreig
n
key table) rows are deleted; or
2) you delete referring rows manually (in the procedure) or use triggers to
delete referring rows when attempting to delete the referenced row.
Of course there are a few other ways to do this, however, the most important
thing to consider is the logical goal you are trying to achieve. Are you
deleteing erroneous records or are you deleting old records - the former and
the latter can also be archived, deactivated etc. Consider all options, and
use the appropriate one(s).
ML

Question about Primary Keys in MSDE

I upsized an Access database into MSDE to begin scalling it up to SQL Server.
I have a curious problem: my queries don't return any results if the table has a Access primary key before they are upsized.
I've deleted and re-created the database to check this, and if I use Access to remove the primary key before upsizing, the queries will return a result; if I re-instate the primary key and upsize again, the queries return nothing.
(This is true regardless of using VS Net, Web Matrix, or by hand...)
I'm a newbie with MSDE and with upsizing from Access. Is this expected -- or have I maybe done something wrong, again?
- Tinker
Nevermind... The whole MSDE-Access-Upsizing thing just got to be too much and I switched over to SQL Server 2000. That solved EVERYthing.
Tinkersql

question about DBCC checkident

Hi all,
In our application we use a special table(only 2 columns, one of which
is identity) to generate unique keys to use in our client application.One of
my recent requests was to create a procedure that would reserve a set of
keys in the table and return it to client.
The procedure I wrote:
1. Inserts a new row into the table to get the current identity
2. execute dbcc checkident with reseed parameter and the
blocksize+current identity.
3. Another insert into the table to ensure the identity is reset
properly. (I added this step only because in testing I found that this makes
identity setup work correctly).
I have included the code for the procedure at the end of the message.
Now this procedure works fine for a single user. However in multiuser
scenario with more than 100 users running this procedure concurrently,
application server has started crashing.
While trying to simulate this problem, I created a batch process that runs
125 concurrent processes running this procedure. I found something strange
in this. One of things I observed is that the sessions that successfully
run, show the following dbcc output:
"C:\CBORD\split tables>osql -E -S APK -d cbord -n -i"test_blockinsert.sql"
Checking identity information: current identity value '153757', current
column value '153906'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator."
But some sessions do not report this messages and I think this sessions are
failing the DBCC CheckIdent call siliently. There are no error messages in
sql server error log.
Has anyone seen or experienced this before. Is running DBCC checkident for
such a high number of concurrent users very bad?
Thanx, Amol.
ALTER procedure getnextkey_range(@.as_tablename varchar(128),@.ai_blockSize
integer,@.al_startkey integer output)
as
begin
declare @.ls_revision varchar(40);
declare @.ls_msgprefix varchar(100);
declare @.ls_sql varchar(1024);
declare @.li_range_end integer;
set @.ls_revision='$Revision: 1.7 $';
set @.ls_sql='insert into ' + rtrim(ltrim(@.as_tablename)) + '_nextkey with
(tablockx) (dummyvalue) values (1)';
execute(@.ls_sql);
set @.al_startkey=@.@.identity;
set @.li_range_end = @.al_startkey + @.ai_blockSize - 1; -- -1 to account for
the previous insert;
set @.ls_sql = 'dbcc checkident (''' + rtrim(ltrim(@.as_tablename)) +
'_nextkey'',reseed,' + cast(@.li_range_end as varchar(8)) + ')';
execute (@.ls_sql)
set @.ls_sql='insert into ' + rtrim(ltrim(@.as_tablename)) + '_nextkey
(dummyvalue) values (' + cast(@.li_range_end as varchar(8)) + ')';
execute(@.ls_sql);
endDon't do it like that. You can create a simple table and sp that will allow
you to get the next ID for a specific table very easily without using
Identities. Have a look at this example:
CREATE TABLE [dbo].[NEXT_ID] (
[ID_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NEXT_VALUE] [int] NOT NULL ,
CONSTRAINT [PK_NEXT_ID_NAME] PRIMARY KEY CLUSTERED
(
[ID_NAME]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE get_next_id
@.ID_Name VARCHAR(20) ,
@.ID int OUTPUT
AS
UPDATE NEXT_ID SET @.ID = NEXT_VALUE = (NEXT_VALUE + 1)
WHERE ID_NAME = @.ID_Name
RETURN (@.@.ERROR)
Andrew J. Kelly SQL MVP
"Amol" <apk@.nospam.cbord.com> wrote in message
news:OxXVhERHFHA.2420@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> In our application we use a special table(only 2 columns, one of which
> is identity) to generate unique keys to use in our client application.One
> of my recent requests was to create a procedure that would reserve a set
> of keys in the table and return it to client.
> The procedure I wrote:
> 1. Inserts a new row into the table to get the current identity
> 2. execute dbcc checkident with reseed parameter and the
> blocksize+current identity.
> 3. Another insert into the table to ensure the identity is reset
> properly. (I added this step only because in testing I found that this
> makes identity setup work correctly).
> I have included the code for the procedure at the end of the message.
> Now this procedure works fine for a single user. However in multiuser
> scenario with more than 100 users running this procedure concurrently,
> application server has started crashing.
> While trying to simulate this problem, I created a batch process that runs
> 125 concurrent processes running this procedure. I found something strange
> in this. One of things I observed is that the sessions that successfully
> run, show the following dbcc output:
> "C:\CBORD\split tables>osql -E -S APK -d cbord -n -i"test_blockinsert.sql"
> Checking identity information: current identity value '153757', current
> column value '153906'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator."
>
> But some sessions do not report this messages and I think this sessions
> are failing the DBCC CheckIdent call siliently. There are no error
> messages in sql server error log.
> Has anyone seen or experienced this before. Is running DBCC checkident for
> such a high number of concurrent users very bad?
> Thanx, Amol.
>
>
> ALTER procedure getnextkey_range(@.as_tablename varchar(128),@.ai_blockSize
> integer,@.al_startkey integer output)
> as
> begin
> declare @.ls_revision varchar(40);
> declare @.ls_msgprefix varchar(100);
> declare @.ls_sql varchar(1024);
> declare @.li_range_end integer;
> set @.ls_revision='$Revision: 1.7 $';
> set @.ls_sql='insert into ' + rtrim(ltrim(@.as_tablename)) + '_nextkey with
> (tablockx) (dummyvalue) values (1)';
> execute(@.ls_sql);
> set @.al_startkey=@.@.identity;
> set @.li_range_end = @.al_startkey + @.ai_blockSize - 1; -- -1 to account
> for the previous insert;
> set @.ls_sql = 'dbcc checkident (''' + rtrim(ltrim(@.as_tablename)) +
> '_nextkey'',reseed,' + cast(@.li_range_end as varchar(8)) + ')';
> execute (@.ls_sql)
> set @.ls_sql='insert into ' + rtrim(ltrim(@.as_tablename)) + '_nextkey
> (dummyvalue) values (' + cast(@.li_range_end as varchar(8)) + ')';
> execute(@.ls_sql);
> end
>
>

Question about database design and primary keys

Hi!
I have seen two approaches to primary keys. First one - and it is likedefault - is to use surrogate key as primary key. For each table I willcreate some autonumeric field hat cannot be changed once it has value.Some materials refer to this key also as technical primary key. Idesign my databases this way usually.
The other approach is to create primary key of fields that make primarykey on database logical model. This approach is not so popular and hassome side effects like a little bit clumpsy looking joins andunconvenient use in applications.
Question: What is the main idea behind second approach? Or how explain their preference database designers who are using second approach?
If a natural key can be used as the primary key, even if this is a composite key made up of a number of fields, I would generally use itonly when I need to later synchronize the data with a database that would not know about my autonumber key. I generally use the surrogate key.|||Synchronization is not so deep in point that I'm looking for. Maybe there is no point at all. :)
But... Even if you are using surrogate key you have unique constraintor index on natural primary key fields. So you can use it whensynchronizing.
|||Perhaps, but in the case I was thinking of, the table involved will be wiped out and recreated periodically, so any use of the surrogate key will not be helpful.|||It refers to somekind of a temporary table/temporary data solution. Butmy question is about "usual" tables. I just took over one system wherethis kind of approach is used and I'm not very sure I want to modify~140 tables and ~400 stored procedures. Just trying to understand whathad previous programmer in his mind. :)
|||This topic generates lots of debate. If you search Google for:

"Natural Key" "Surrogate Key"

you will see a lot of the arguments for and against eachapproach. Joe Celko is a major proponent of the philosophy thatproperly normalized and constructed databases should use natural keys.
I generally use surrogate keys. I have yet to read anything onthis topic that has swayed me to believe that natural keys arebetter. The only advantage I have seen discussed is purelyacademic -- that it is the "right" way to do things.
That being said -- is your current database structure causing youproblems? Personally I would just leave well enough alone.

|||No problems with database, I just was curious about pros of natural keys approach as it means more processing usually (complex primary key) on joins and it isnot so convenient to use in web applications.

Question about converting bigint field to int field

We made a poor decision a long time ago when designing our database
structure. We used bigint data types as the identity keys for many of
our base tables. For many reasons I would like to change these fields
to int at the largest. The largest data in these fields is around
200,000. I know that int can easily store this.

What should I be worried about when changing these fields from bigint
to int? If anything. Your help is appreciated. I did several
searches without much luck.I think you've covered the one biggy - make sure your existing data will
fit!

Others...

a) Make sure anything you join with are the same type, basically make
sure you change it everywhere including your foreign keys table.
b) Remember to do the stored procedures, udfs, triggers that may use them
as parameter.
c) You'll need to drop any constraints on your column definied with the
identity property, see example problem...

drop table t

create table t (
mycol bigint identity primary key,
t char(1) )

insert t ( t) values( 'a' )

alter table t alter column mycol int not null

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

<mamorgan1@.gmail.com> wrote in message
news:1137768775.133857.115810@.g44g2000cwa.googlegr oups.com...
> We made a poor decision a long time ago when designing our database
> structure. We used bigint data types as the identity keys for many of
> our base tables. For many reasons I would like to change these fields
> to int at the largest. The largest data in these fields is around
> 200,000. I know that int can easily store this.
> What should I be worried about when changing these fields from bigint
> to int? If anything. Your help is appreciated. I did several
> searches without much luck.|||Just curious... What problems are there with having bigint as an
identity column?|||There aren't any problems - it works just fine.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"pb648174" <google@.webpaul.net> wrote in message
news:1138052526.544803.80680@.z14g2000cwz.googlegro ups.com...
> Just curious... What problems are there with having bigint as an
> identity column?|||I think its because
Bigint takes 8 bytes storage and Int takes 4 bytes.
SQL Server will not automatically promote other integer data types
(tinyint, smallint, and int) to bigint.

Regards
Amish Shah|||> What problems are there with having bigint as an identity column?

extra storage space causing slower performance of everything

Question about a Select Statement

Hello,

I'm quite new to SQL and so my question may sound a bit strange.

Let me say I have the primary Keys 2049, 2090,4080,7803

For those 4 primary Keys I want to Select the rows from a table. How ca I do this in 1 SQL-Statement.

My way until now: Select [column-List] From tab1 Where ID=2049;
Select [column-list] From tab1 Where ID = 2090
...

So I split up into 4 Statements

There MUST be a way doing this in one statement!!!!!

Can you help me?

Kind regardsTry this:

where ID in (2049,2090,4080,7803)