Resource locks

hi,
I have a T-sql as part of ETL. The code selects all data
from table x (with 3 million rows) and populates table y
(which already has 1.5 million rows). There are no where
conditions no calculations - simple insert into table x
(col1,col2,...coln) select col1,col2...coln from table y.
When I run sp_lock I see a lot locks of type EXT and mode
X. Can I used hint TABLOCKX.
I noticed that if I used hint TABLOCKX for the table
inserted into then number of locks reduces drastically. If
I use hint TABLOCKX in the table selected from there is no
impact.
Anybody encountered similar issues? Any input will be
useful..
Thx,
DeepaThat means you are getting extent locks which are OK for this type operation
but if you don't have other users accessing it would be best to use a table
level lock but don't need to use TABLOCKX. Instead try TABLOCK but if you
have users in the table you are selecting from they may prevent this from
happeing.
--
Andrew J. Kelly SQL MVP
"Deepa" <anonymous@.discussions.microsoft.com> wrote in message
news:01cc01c3b51f$f2276eb0$a301280a@.phx.gbl...
> hi,
> I have a T-sql as part of ETL. The code selects all data
> from table x (with 3 million rows) and populates table y
> (which already has 1.5 million rows). There are no where
> conditions no calculations - simple insert into table x
> (col1,col2,...coln) select col1,col2...coln from table y.
> When I run sp_lock I see a lot locks of type EXT and mode
> X. Can I used hint TABLOCKX.
> I noticed that if I used hint TABLOCKX for the table
> inserted into then number of locks reduces drastically. If
> I use hint TABLOCKX in the table selected from there is no
> impact.
> Anybody encountered similar issues? Any input will be
> useful..
> Thx,
> Deepa
>|||Thank you Andrew. I am new to sqlserver and your help is
appreciated.
Now, this table has six indexes and dropped them before
the insert and recreate them after.
What I notice is for a brief moment the number of locks
jumps to 30,000+ before reducing to around 200 incase of
tablock and 4000 when used without tablock. Why is there a
spike initially.
Also I read that each lock resource uses 96k. My box has
2GB memory which means I can have upto (2*1024*1024*1024)/
(96*1024) which comes to about 21800 then how can the lock
resource grow to 30,000?
i encounter resource lock issues only at the time that the
o.s backup is happening. My systems people are not able to
advice me. Would you know if an o.s. backup would be heavy
on memory. I'm not a windows person either...
Many thanks,
Deepa|||Its 96 bytes Deepa and not 96KB :-)
"Deepa" <anonymous@.discussions.microsoft.com> wrote in message
news:002b01c3b5dd$f18aef40$a501280a@.phx.gbl...
> Thank you Andrew. I am new to sqlserver and your help is
> appreciated.
> Now, this table has six indexes and dropped them before
> the insert and recreate them after.
> What I notice is for a brief moment the number of locks
> jumps to 30,000+ before reducing to around 200 incase of
> tablock and 4000 when used without tablock. Why is there a
> spike initially.
> Also I read that each lock resource uses 96k. My box has
> 2GB memory which means I can have upto (2*1024*1024*1024)/
> (96*1024) which comes to about 21800 then how can the lock
> resource grow to 30,000?
> i encounter resource lock issues only at the time that the
> o.s backup is happening. My systems people are not able to
> advice me. Would you know if an o.s. backup would be heavy
> on memory. I'm not a windows person either...
> Many thanks,
> Deepa
>
>|||Yes as Hassan pointsout it is 96 bytes and not KB so you ae not as short on
memory as you think. The reason you see it spike initialy is that sql sever
starts out with row or page locks and will escalate to a table lock if it
can after a while. You say it is an OS backup, are you sure they aren't
using a sql plug-in to do sql backups as well? If you are doing a sql
backup it will take locks when it reads the data. If it is strictly an OS
backup they should eliminate the sql erver files from the backup as they are
useless from a sql server point of view and only can cause issues when
accessing the sql files.
--
Andrew J. Kelly SQL MVP
"Deepa" <anonymous@.discussions.microsoft.com> wrote in message
news:002b01c3b5dd$f18aef40$a501280a@.phx.gbl...
> Thank you Andrew. I am new to sqlserver and your help is
> appreciated.
> Now, this table has six indexes and dropped them before
> the insert and recreate them after.
> What I notice is for a brief moment the number of locks
> jumps to 30,000+ before reducing to around 200 incase of
> tablock and 4000 when used without tablock. Why is there a
> spike initially.
> Also I read that each lock resource uses 96k. My box has
> 2GB memory which means I can have upto (2*1024*1024*1024)/
> (96*1024) which comes to about 21800 then how can the lock
> resource grow to 30,000?
> i encounter resource lock issues only at the time that the
> o.s backup is happening. My systems people are not able to
> advice me. Would you know if an o.s. backup would be heavy
> on memory. I'm not a windows person either...
> Many thanks,
> Deepa
>
>|||Thank you both. Yes it is 96 bytes and not 96kb.. my bad!
I have been doing tests with drop index / populate /
recreate index and testing is in progress but this is
likely to resolve the error.
Its a standard o.s backup. The files being backed up are
the backup files created by my maintenance plans - no the
datafiles.
many thanks for the response.
>--Original Message--
>Yes as Hassan pointsout it is 96 bytes and not KB so you
ae not as short on
>memory as you think. The reason you see it spike
initialy is that sql sever
>starts out with row or page locks and will escalate to a
table lock if it
>can after a while. You say it is an OS backup, are you
sure they aren't
>using a sql plug-in to do sql backups as well? If you
are doing a sql
>backup it will take locks when it reads the data. If it
is strictly an OS
>backup they should eliminate the sql erver files from the
backup as they are
>useless from a sql server point of view and only can
cause issues when
>accessing the sql files.
>--
>Andrew J. Kelly SQL MVP
>
>"Deepa" <anonymous@.discussions.microsoft.com> wrote in
message
>news:002b01c3b5dd$f18aef40$a501280a@.phx.gbl...
>> Thank you Andrew. I am new to sqlserver and your help is
>> appreciated.
>> Now, this table has six indexes and dropped them before
>> the insert and recreate them after.
>> What I notice is for a brief moment the number of locks
>> jumps to 30,000+ before reducing to around 200 incase of
>> tablock and 4000 when used without tablock. Why is
there a
>> spike initially.
>> Also I read that each lock resource uses 96k. My box has
>> 2GB memory which means I can have upto
(2*1024*1024*1024)/
>> (96*1024) which comes to about 21800 then how can the
lock
>> resource grow to 30,000?
>> i encounter resource lock issues only at the time that
the
>> o.s backup is happening. My systems people are not able
to
>> advice me. Would you know if an o.s. backup would be
heavy
>> on memory. I'm not a windows person either...
>> Many thanks,
>> Deepa
>>
>
>.
>

resolving permission conflicts

I have been trying to set up permissions for a SQL user ID
on a specific table. The user will access this table using
MS Access 2000 with a standard ODBC connection. I only
want the user to be able to view data from this table.
When I set up a user role in SQL associated with this
user, the permissions on this role are set to deny insert,
update and delete capabilities. This does not appear to
work. However, if I change the permissions on the public
role to deny insert, update, and delete capabilities this
works.
I know every user assumes the public role but I can't seem
to override the permissions set on the public role.
The SQL books online documentation states that a denied
permission always takes prcedent. I cannot seem to make
this work when I have a specific role for this user which
has the deny permissions set.
Thanks,
Jim d'HulstIs the user perhaps an admin on the server and thus inheriting sysadmin via
the Builtin\System role created by default? ( I normall remove this pretty q
uickly)
Alicia
www.sqlporn.co.uk|||Alicia, the user is jnot an admin on the server nor does
this SQL user id belong to any system administration roles.
What I find is if I use windows NT authentication and
assign the permissions the a windows users it works fine.
It is just when I use a SQL user ID that this doesn't seem
to work.
Thanks,
Jim
>--Original Message--
>Is the user perhaps an admin on the server and thus
inheriting sysadmin via the Builtin\System role created by
default? ( I normall remove this pretty quickly)
>Alicia
>www.sqlporn.co.uk
>.
>|||Jim,
Overriding public should be quite doable. Since it is working for your
domain account, I would expect it to work for a SQL account as well. With
all due deference, I suggest that it is probably something simple, but just
hard to see. Silly possibilities:
There is a guest account and the user name is misspelled such that the user
comes in as guest.
The user is also in the db_owner role for the database.
Russell Fields
"jim.dhulst@.am.dynonobel.com" <anonymous@.discussions.microsoft.com> wrote in
message news:fba601c43e7d$96ba1c70$a301280a@.phx.gbl...[vbcol=seagreen]
> Alicia, the user is jnot an admin on the server nor does
> this SQL user id belong to any system administration roles.
> What I find is if I use windows NT authentication and
> assign the permissions the a windows users it works fine.
> It is just when I use a SQL user ID that this doesn't seem
> to work.
> Thanks,
> Jim
> inheriting sysadmin via the Builtin\System role created by
> default? ( I normall remove this pretty quickly)

Resolving duplicates entries in table among 10 databases

Hi

Our product uses MS-SQL Server 2000. One of our customer has 10
installations with each installation stroring data in its own database.
Now the customer wants to consolidate these databases into one and we
already have plan for that by consolidating one DB at a time. But first
they want to find how many unique or duplicate entries they have across
all the 10 databases

Assumptions:
1. All the databases reside on the same server. (This is just an
assumption, not the real environment at customer site)
2. Databases can not be merged before it is found how many unique or
duplicate rows exist.

Table under consideration:
Message
(
HashID PK,
...
)

# of rows in Message table in each of databases: 1 Million

Here is my question: How can I find how many unique or duplicate
entries they have across all the 10 databases. I easily find unique
rows for two databases with a query like this:

SELECT COUNT(A.HasID) FROM db1.dbo.Message A LEFT OUTER JOIN ON
db2.dbo.Message B ON A.HashID = B.HashID WHERE B.HashID IS NULL

How can I do this for 10 databases. This will require factorial of 10
queries to solve this problem.

I will appreciate if someone can provide hint on this.

Regards
AK> Here is my question: How can I find how many unique or duplicate
> entries they have across all the 10 databases.

The following will list the count of unique values (Duplicates = 0) as well
has the non-unique values grouped by the number of duplicates (1-9).

SELECT
Duplicates,
(Duplicates + 1) * COUNT(*) AS TotalHashIDCount
FROM (
SELECT HashID, COUNT(*) - 1 AS Duplicates
FROM (
SELECT HashID FROM db1.dbo.Message
UNION ALL SELECT HashID FROM db2.dbo.Message
UNION ALL SELECT HashID FROM db3.dbo.Message
UNION ALL SELECT HashID FROM db4.dbo.Message
UNION ALL SELECT HashID FROM db5.dbo.Message
UNION ALL SELECT HashID FROM db6.dbo.Message
UNION ALL SELECT HashID FROM db7.dbo.Message
UNION ALL SELECT HashID FROM db8.dbo.Message
UNION ALL SELECT HashID FROM db9.dbo.Message
UNION ALL SELECT HashID FROM db10.dbo.Message
) AS Messages
GROUP BY HashID) AS HashIDCounts
GROUP BY Duplicates
ORDER BY Duplicates

--
Hope this helps.

Dan Guzman
SQL Server MVP

"AK" <ambkh@.yahoo.com> wrote in message
news:1139679057.443925.49210@.g44g2000cwa.googlegro ups.com...
> Hi
> Our product uses MS-SQL Server 2000. One of our customer has 10
> installations with each installation stroring data in its own database.
> Now the customer wants to consolidate these databases into one and we
> already have plan for that by consolidating one DB at a time. But first
> they want to find how many unique or duplicate entries they have across
> all the 10 databases
> Assumptions:
> 1. All the databases reside on the same server. (This is just an
> assumption, not the real environment at customer site)
> 2. Databases can not be merged before it is found how many unique or
> duplicate rows exist.
> Table under consideration:
> Message
> (
> HashID PK,
> ...
> )
> # of rows in Message table in each of databases: 1 Million
> Here is my question: How can I find how many unique or duplicate
> entries they have across all the 10 databases. I easily find unique
> rows for two databases with a query like this:
> SELECT COUNT(A.HasID) FROM db1.dbo.Message A LEFT OUTER JOIN ON
> db2.dbo.Message B ON A.HashID = B.HashID WHERE B.HashID IS NULL
> How can I do this for 10 databases. This will require factorial of 10
> queries to solve this problem.
> I will appreciate if someone can provide hint on this.
> Regards
> AK|||Thank you Dan. This is exactly what I needed (in fact more than what I
needed :)

Regards
AK

Dan Guzman wrote:
> > Here is my question: How can I find how many unique or duplicate
> > entries they have across all the 10 databases.
> The following will list the count of unique values (Duplicates = 0) as well
> has the non-unique values grouped by the number of duplicates (1-9).
> SELECT
> Duplicates,
> (Duplicates + 1) * COUNT(*) AS TotalHashIDCount
> FROM (
> SELECT HashID, COUNT(*) - 1 AS Duplicates
> FROM (
> SELECT HashID FROM db1.dbo.Message
> UNION ALL SELECT HashID FROM db2.dbo.Message
> UNION ALL SELECT HashID FROM db3.dbo.Message
> UNION ALL SELECT HashID FROM db4.dbo.Message
> UNION ALL SELECT HashID FROM db5.dbo.Message
> UNION ALL SELECT HashID FROM db6.dbo.Message
> UNION ALL SELECT HashID FROM db7.dbo.Message
> UNION ALL SELECT HashID FROM db8.dbo.Message
> UNION ALL SELECT HashID FROM db9.dbo.Message
> UNION ALL SELECT HashID FROM db10.dbo.Message
> ) AS Messages
> GROUP BY HashID) AS HashIDCounts
> GROUP BY Duplicates
> ORDER BY Duplicates
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "AK" <ambkh@.yahoo.com> wrote in message
> news:1139679057.443925.49210@.g44g2000cwa.googlegro ups.com...
> > Hi
> > Our product uses MS-SQL Server 2000. One of our customer has 10
> > installations with each installation stroring data in its own database.
> > Now the customer wants to consolidate these databases into one and we
> > already have plan for that by consolidating one DB at a time. But first
> > they want to find how many unique or duplicate entries they have across
> > all the 10 databases
> > Assumptions:
> > 1. All the databases reside on the same server. (This is just an
> > assumption, not the real environment at customer site)
> > 2. Databases can not be merged before it is found how many unique or
> > duplicate rows exist.
> > Table under consideration:
> > Message
> > (
> > HashID PK,
> > ...
> > )
> > # of rows in Message table in each of databases: 1 Million
> > Here is my question: How can I find how many unique or duplicate
> > entries they have across all the 10 databases. I easily find unique
> > rows for two databases with a query like this:
> > SELECT COUNT(A.HasID) FROM db1.dbo.Message A LEFT OUTER JOIN ON
> > db2.dbo.Message B ON A.HashID = B.HashID WHERE B.HashID IS NULL
> > How can I do this for 10 databases. This will require factorial of 10
> > queries to solve this problem.
> > I will appreciate if someone can provide hint on this.
> > Regards
> > AK|||Better too much than too little :-)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"AK" <ambkh@.yahoo.com> wrote in message
news:1139936166.210532.262820@.g43g2000cwa.googlegr oups.com...
> Thank you Dan. This is exactly what I needed (in fact more than what I
> needed :)
> Regards
> AK
> Dan Guzman wrote:
>> > Here is my question: How can I find how many unique or duplicate
>> > entries they have across all the 10 databases.
>>
>> The following will list the count of unique values (Duplicates = 0) as
>> well
>> has the non-unique values grouped by the number of duplicates (1-9).
>>
>> SELECT
>> Duplicates,
>> (Duplicates + 1) * COUNT(*) AS TotalHashIDCount
>> FROM (
>> SELECT HashID, COUNT(*) - 1 AS Duplicates
>> FROM (
>> SELECT HashID FROM db1.dbo.Message
>> UNION ALL SELECT HashID FROM db2.dbo.Message
>> UNION ALL SELECT HashID FROM db3.dbo.Message
>> UNION ALL SELECT HashID FROM db4.dbo.Message
>> UNION ALL SELECT HashID FROM db5.dbo.Message
>> UNION ALL SELECT HashID FROM db6.dbo.Message
>> UNION ALL SELECT HashID FROM db7.dbo.Message
>> UNION ALL SELECT HashID FROM db8.dbo.Message
>> UNION ALL SELECT HashID FROM db9.dbo.Message
>> UNION ALL SELECT HashID FROM db10.dbo.Message
>> ) AS Messages
>> GROUP BY HashID) AS HashIDCounts
>> GROUP BY Duplicates
>> ORDER BY Duplicates
>>
>> --
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>>
>> "AK" <ambkh@.yahoo.com> wrote in message
>> news:1139679057.443925.49210@.g44g2000cwa.googlegro ups.com...
>> > Hi
>>> > Our product uses MS-SQL Server 2000. One of our customer has 10
>> > installations with each installation stroring data in its own database.
>> > Now the customer wants to consolidate these databases into one and we
>> > already have plan for that by consolidating one DB at a time. But first
>> > they want to find how many unique or duplicate entries they have across
>> > all the 10 databases
>>> > Assumptions:
>> > 1. All the databases reside on the same server. (This is just an
>> > assumption, not the real environment at customer site)
>> > 2. Databases can not be merged before it is found how many unique or
>> > duplicate rows exist.
>>> > Table under consideration:
>> > Message
>> > (
>> > HashID PK,
>> > ...
>> > )
>>> > # of rows in Message table in each of databases: 1 Million
>>> > Here is my question: How can I find how many unique or duplicate
>> > entries they have across all the 10 databases. I easily find unique
>> > rows for two databases with a query like this:
>>> > SELECT COUNT(A.HasID) FROM db1.dbo.Message A LEFT OUTER JOIN ON
>> > db2.dbo.Message B ON A.HashID = B.HashID WHERE B.HashID IS NULL
>>> > How can I do this for 10 databases. This will require factorial of 10
>> > queries to solve this problem.
>>> > I will appreciate if someone can provide hint on this.
>>> > Regards
>> > AK
>sql

resizing a subreport

Hello:
I have a subreport displaying a title (actually a table with some rows of
information) that is common to many different reports.
I'll like to implement this title in a subreport and link it from the main
reports, but some of them are in portrait format while others are landscape.
Is there any way of resizing a subreport?
Thanks,
Daniel Bello Urizarri.Daniel Bello wrote:
> Hello:
> I have a subreport displaying a title (actually a table with some rows of
> information) that is common to many different reports.
> I'll like to implement this title in a subreport and link it from the main
> reports, but some of them are in portrait format while others are landscape.
> Is there any way of resizing a subreport?
> Thanks,
> Daniel Bello Urizarri.
As far as I know, subreports can be minimally formatted size-wise;
however, it is very limited and portrait versus layout formatting is
not feasible. The subreport could be put inside a list, which could
control the layout a little better. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer

Resize rectangle

I am putting a rectangle around my table. Table's height can grow alone with
a number of rows.
Can I resize a height of the rectangle accordingly?
ThanksOn Apr 4, 12:12 pm, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> I am putting a rectangle around my table. Table's height can grow alone with
> a number of rows.
> Can I resize a height of the rectangle accordingly?
> Thanks
If I'm understanding you correctly, the rectangle will automatically
grow w/the size of the table.
Regards,
Enrique Martinez
Sr. Software Consultant

Resetting the Identity field

I have a composite pk in a table 'table' in ms sql server. value in one field 'table.a' is fk to another table 'table1.a'
value in field table.b is a id field. i need to reset this field 'table.b' to 1 each time the 'table.a' changes.

Any suggestions.Not sure what you mean.
if table.a is part of the primary key it should never change otherwise it shouldn't be part of the primary key.
It sounds like you might want a trigger but maybe you could post an example.|||i see what you mean. I have changed it and i have a field table1.a and table1.b. both .a and .b are not in the keys, though .a is a fk to table2.a.
i need to increment .b by 1 on each input of .a where .a = 'x' (say). as soon as .a = 'y' (say) i need to reset .b to 0 and auto increment as new values for .a='y' are inserted.
hope this makes sense.
thanks|||ok
you have table1(a,b)
a is an id and you want b to be the sequence number within a?

put a trigger on the table

create trigger tr_table1_ins on table1 for insert
as
set rowcount 1
while exists(select * from table1 where b is null)
begin
update table1
set b = (select max(b)+1 from table1 t1 where table1.a = t1.a)
where b is null
set rowcount 0
go

if you only ever insert one row at a time then you can just do the update without the loop.

Another option is to put the current value for b on table2 and increment it within a transaction on inserts and use it with the insert.|||We've done something like this at our site. When we needed to know the occurence of a record, example "2 of 5". We implemented a TRIGGER like nigelrivett has suggested. To use a trigger you should JOIN with the INSERTED table to update only those records that were Inserted.

SET NOCOUNT ON
GO
CREATE
TABLE Occurrence
(
syID int IDENTITY (1, 1) NOT NULL ,
colA char(3),
colB int NOT NULL DEFAULT 0
)
GO
CREATE
TRIGGER tri_Occurrence
ON Occurrence
FOR Insert
AS

--
-- If no records were effected then return
--
IF (@.@.ROWCOUNT = 0) BEGIN
RETURN
END

UPDATE o
SET colB = (SELECT MAX(o.colB) + 1 FROM Occurrence o WHERE i.colA = o.colA)
FROM Occurrence o,
Inserted i
WHERE o.syID = i.syID

RETURN
GO

INSERT Occurrence (colA) values ('A')
INSERT Occurrence (colA) values ('A')
INSERT Occurrence (colA) values ('B')
INSERT Occurrence (colA) values ('A')
INSERT Occurrence (colA) values ('C')
INSERT Occurrence (colA) values ('C')
GO

SELECT *
FROM Occurrence

syID colA colB
---- -- ----
1 A 1
2 A 2
3 B 1
4 A 3
5 C 1
6 C 2|||Unfortunately that only works for single row inserts.
And assumes an ID on the table.
Apart from that is the same as my trigger.|||You are one to get the last word in. I'm sorry that I replied to the posting with my answer. I felt that a person could cut and paste this and see a working example.

But I forget that once nigelrivett answers, we should lock the posting, case closed.|||Sorry - just thought I'd point out a problem, which is quite common, with the trigger you posted.

Resetting rows count

I have a table in Database where I have added and removed rows, during the
period of developing applications. Now I have removed all row, but when new
are added, their IDs don't start from 1.
Is there a way to reset some counter or something?
hi Nikolay,
"Nikolay Petrov" <johntup2@.mail.bg> ha scritto nel messaggio
news:%23AvGpW5gEHA.1184@.TK2MSFTNGP12.phx.gbl...
> I have a table in Database where I have added and removed rows, during the
> period of developing applications. Now I have removed all row, but when
new
> are added, their IDs don't start from 1.
> Is there a way to reset some counter or something?
>
you are probably referring to a table colum's property known as IDENTITY...
in order to reset it's internal value, you can have a look at the DBCC
CHECKIDENT
(..)http://msdn.microsoft.com/library/de.../en-us/tsqlref
/ts_dbcc_5lv8.asp action...
if you want to delete all rows from a user table and reset the IDENTITY
property on the same time, you can issue a TRUNCATE TABLE statement instead
of DELETE FROM...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

resetting identiy seed

I have designated a instId column in a table as an identity column/primary key.
Having recently added loads of data and truncating the table/deleting the
table, how could I reset the identiy seed such that instId=1 for the next
record I insert?
Check out DBCC CHECKIDENT. Also, if you empty the table using TRUNCATE TABLE instead of DELETE, the
identity will be reset for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?
|||Check out DBCC CHECKIDENT in SQL BOL.
ALI
Patrick wrote:
> I have designated a instId column in a table as an identity column/primary key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?
|||Patrick
If you issue TRUNCATE Table SQL Server will reset an Identity property
otherwise take look at DBCC CHECKIDENT command in the BOL
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?
|||DBCC CHECKIDENT
Checks the current identity value for the specified table and, if needed,
corrects the identity value.
Syntax
DBCC CHECKIDENT
( 'table_name'
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
}
]
)
See books online for some good examples.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?

resetting identiy seed

I have designated a instId column in a table as an identity column/primary k
ey.
Having recently added loads of data and truncating the table/deleting the
table, how could I reset the identiy seed such that instId=1 for the next
record I insert?Check out DBCC CHECKIDENT. Also, if you empty the table using TRUNCATE TABLE
instead of DELETE, the
identity will be reset for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||Check out DBCC CHECKIDENT in SQL BOL.
ALI
Patrick wrote:
> I have designated a instId column in a table as an identity column/primary
key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||Patrick
If you issue TRUNCATE Table SQL Server will reset an Identity property
otherwise take look at DBCC CHECKIDENT command in the BOL
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||DBCC CHECKIDENT
Checks the current identity value for the specified table and, if needed,
corrects the identity value.
Syntax
DBCC CHECKIDENT
( 'table_name'
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
}
]
)
See books online for some good examples.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?

resetting identiy seed

I have designated a instId column in a table as an identity column/primary key.
Having recently added loads of data and truncating the table/deleting the
table, how could I reset the identiy seed such that instId=1 for the next
record I insert?Check out DBCC CHECKIDENT. Also, if you empty the table using TRUNCATE TABLE instead of DELETE, the
identity will be reset for you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||Check out DBCC CHECKIDENT in SQL BOL.
ALI
Patrick wrote:
> I have designated a instId column in a table as an identity column/primary key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||Patrick
If you issue TRUNCATE Table SQL Server will reset an Identity property
otherwise take look at DBCC CHECKIDENT command in the BOL
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||DBCC CHECKIDENT
Checks the current identity value for the specified table and, if needed,
corrects the identity value.
Syntax
DBCC CHECKIDENT
( 'table_name'
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
}
]
)
See books online for some good examples.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?sql

resetting Identity Seed on change of primary key

I have a table that has a Primary key and a foreign key. The primary key is NOT an Identity field, however, the foreign key is. I would like to know if there is a way to have the foreign key reset itself to the value of 1 when the Primary key changes. For example if I add the following 3 records to the table: 1st record - Primary key is 1, foreign key is 1; 2nd record - Primary key is 1, foreign key is 2; third record - Primary key is 2, foreign key is 3, but I want the foreign key to be reset to 1.

Quote:

Originally Posted by Rick Kay

I have a table that has a Primary key and a foreign key. The primary key is NOT an Identity field, however, the foreign key is. I would like to know if there is a way to have the foreign key reset itself to the value of 1 when the Primary key changes. For example if I add the following 3 records to the table: 1st record - Primary key is 1, foreign key is 1; 2nd record - Primary key is 1, foreign key is 2; third record - Primary key is 2, foreign key is 3, but I want the foreign key to be reset to 1.

You should read the topic
DBCC CHECKIDENT
in books on-line help. If I understand correctly what you are trying, it won't work.

You will have to write code to generate your own FK values.

Tom.|||

Quote:

Originally Posted by folderol

You should read the topic
DBCC CHECKIDENT
in books on-line help. If I understand correctly what you are trying, it won't work.

You will have to write code to generate your own FK values.

Tom.

Tom, that's exactly what I thought, but I wanted to be sure someone else agreed with me. Thanks for your response.|||This will reseed the identity no for a column in a table.

declare @.intCounter int
set @.intCounter = 0
update (YOUR_TABLE)
SET @.intCounter = (YOUR_COLUMN) = @.intCounter + 1

resetting identity columns

Is there a way besides truncate table to reset the identity column of a tabl
e?
Thanks,
JoeLook at DBCC CHECKIDENT in Books Online.
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:109E8A34-65B2-4E8D-8FA3-C99D00C25D20@.microsoft.com...
> Is there a way besides truncate table to reset the identity column of a
> table?
> Thanks,
> Joe
>|||Jaylou,
Yes. DBCC CHECKIDENT ...RESEED argument.
HTH
Jerry
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:109E8A34-65B2-4E8D-8FA3-C99D00C25D20@.microsoft.com...
> Is there a way besides truncate table to reset the identity column of a
> table?
> Thanks,
> Joe
>|||Thank you!
I knew there was something but I couldn't find it.|||Thank you!
I knew there was something but I couldn't find it.

resetting id values

hi guys i was wondering if anyone could help me, i have a table with a field called id that did have numbers 1,2,3,4,5,6,7,8 and so on! but after some tinkering i have removed a few value and added more so i now have 1, 4,8,19,20 and so on!

i was wondering if i can run a query to update those value and return them to 1,2,3,4,5,6,7,8 and so on?

Cheers

Tupps

This article might help

http://www.juliankuiters.id.au/article.php/sql2000-reset-identity

|||

sorted thanks!! couldnt get an answer! but thats cos i wasnt using the correct term cheers!!

Tupps

resetting database

hi!
i'm new to sql server and enterprise manager and i accidentally deleted all data in a table. how do i recover them? the table (CaptureManager) is still there but empty. i have the following information:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CaptureManager]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CaptureManager]
GO

CREATE TABLE [dbo].[CaptureManager] (
[ID] [tinyint] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Ext] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

The easiet way is to restore the database from backup and do INSERT INTO the old table which means everything remains the same you just return the deleted data. The Backup and restore Wizard lets you restore the database with any name you like. Hope this helps.|||unfortunately, i dont have a backup.|||If you buy some expensive log viewing software you may still have the original inserts logged. But really the chance of getting your data back is pretty minimal. I guess it's called learning by your mistakes, sorry :(
|||Try this link to test drive some tools. Hope this helps.
http://www.sql-server-performance.com/greg_robidoux_interview.asp

Reseting the unique id if a table.

I'm building a web site. there is a database.

I've set a primary key and unique of a field.also I set it to auto numbering.

Everytime I insert a record , that field will increase 1 (type bigint , start from 1).

After lots time of inserting record , the id going to be larger number.. I wondering how can I reset that to zero?

Run query Truncate Table YuorTableName

Reseting the Auto-Number for a table back to zero and Compact/Repair or Unload/Reload for

After deleting all the test data from all tables in a SQL 2000 database, is there a way to reset all the auto-incrementing fields back to zero in one shot? In Access, you can run the Compact and Repair option. Also, in Sybase SQL, there was an "unload/reload" option to reduce the database size. Is there a similar function in SQL2000? Thanks for all the help

Try to use truncate instead of delete to remove records from your table, it will remove records and reset identity fields to starting values.

Thanks

Reseting Select Permissions for Public Role

Every night, there are some stored procedures that run to recreate tables so that the information in the table is updated. After the tables are droped and recreated I have to go in and check the select box under the permissions for the public role. If i don't do this users will not be able to select from theres tables.

What can I do so that users are able to select from these tables after they are created?

Would you be able to specify the select permissions for the public role in the script that creates the table or run a script that gives all those tables select permissions for the public role?

All help is appreciated.I'd use GRANT SELECT ON (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ga-gz_8odw.asp) within the script.

-PatP|||That did the trick. Thanks for all the help.sql

Reseting Identity Seed

hi all..
I want to reset the identity seed value for a table... How can I do that...
one method is to truncate table... but if table is used as parent in foreign
key relationship; it does not allow to truncate the table... any other
method?
Ansari
Check out DBCC CHECKIDENT.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ansari" <mudasar_ansari@.yahoo.com> wrote in message news:uf4BRq4AFHA.4028@.TK2MSFTNGP15.phx.gbl...
> hi all..
>
> I want to reset the identity seed value for a table... How can I do that...
> one method is to truncate table... but if table is used as parent in foreign
> key relationship; it does not allow to truncate the table... any other
> method?
> Ansari
>
>

Reseting Identity Seed

hi all..
I want to reset the identity seed value for a table... How can I do that...
one method is to truncate table... but if table is used as parent in foreign
key relationship; it does not allow to truncate the table... any other
method?
AnsariCheck out DBCC CHECKIDENT.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ansari" <mudasar_ansari@.yahoo.com> wrote in message news:uf4BRq4AFHA.4028@.TK2MSFTNGP15.phx.
gbl...
> hi all..
>
> I want to reset the identity seed value for a table... How can I do that..
.
> one method is to truncate table... but if table is used as parent in forei
gn
> key relationship; it does not allow to truncate the table... any other
> method?
> Ansari
>
>

Reseting Identity Seed

O.k. here's my deal. I have a table who's items get assigned an ID based on
the identity seed. The identity seed is incremented by 1, and the seed
length is 4 . When my program is run, some of these items get moved into
another table for future use and the others are deleted. The items that get
moved are used again the next time the program is run(which is only once a
day). The one items get deleted is based on whether or not they match off t
o
another item read in by the program. Ex. If the data in my table is 1 2 3
4
5 and the program reads in 1 2 4 5, then the 3 is moved to another table and
the 1 2 4 5 get deleted b/c they were matched off. The identity seed is the
n
reset using DBCC CHECKIDENT which works fine, but I need to be able to have
the value it is reset to tied in to something like the date so that it is
unique each time it is reset. This way the next time my program reads in a
3, I need to make sure it does get the same identity seed as the previous 3
that was saved from the first time. I hope you can understand all that and
give me some insight as to how/if this can be done. Thanks.Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"CD" wrote:

> O.k. here's my deal. I have a table who's items get assigned an ID based
on
> the identity seed. The identity seed is incremented by 1, and the seed
> length is 4 . When my program is run, some of these items get moved into
> another table for future use and the others are deleted. The items that g
et
> moved are used again the next time the program is run(which is only once a
> day). The one items get deleted is based on whether or not they match off
to
> another item read in by the program. Ex. If the data in my table is 1 2
3 4
> 5 and the program reads in 1 2 4 5, then the 3 is moved to another table a
nd
> the 1 2 4 5 get deleted b/c they were matched off. The identity seed is t
hen
> reset using DBCC CHECKIDENT which works fine, but I need to be able to hav
e
> the value it is reset to tied in to something like the date so that it is
> unique each time it is reset. This way the next time my program reads in
a
> 3, I need to make sure it does get the same identity seed as the previous
3
> that was saved from the first time. I hope you can understand all that an
d
> give me some insight as to how/if this can be done. Thanks.|||Here's how I read your *requirement* as opposed to what you are
actually asking for. It seems like you have a table and you need to
determine which is the 1st, 2nd, 3rd or Nth row inserted to that table
on any particular day. So add a DATETIME column to the table:
CREATE TABLE YourTable (creation_date DATETIME NOT NULL UNIQUE DEFAULT
CURRENT_TIMESTAMP, ...)
The derive the sequence number like this:
SELECT T1.creation_date, COUNT(*) AS seq
FROM YourTable AS T1
JOIN YourTable AS T2
ON T2.creation_date >= '20050225'
AND T1.creation_date < '20050226'
AND T1.creation_date >= T2.creation_date
GROUP BY T1.creation_date
ORDER BY T1.creation_date
Resetting the seed and relying on IDENTITY to do the same thing is a
really bad idea. IDENTITY sequences can have gaps.
If I've completely misunderstood then the standard advice applies:
Please post DDL, sample data, required results to maximize your chance
of getting a good answer. See:
http://www.aspfaq.com/etiquette.asp?id=5006
Hope this helps.
David Portas
SQL Server MVP
--sql