database collation: identity

Showing posts with label identity. Show all posts

Sunday, March 25, 2012

Changing the identity seed & increment programatically

Hi,
I want to change the identity values of an identity column of a table
that already has rows in it, such that, it begins at zero and goes up
in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
usual 1, 2, 3, 4, etc...
The current values of the identity seed & identity increment are the
default values of "1" respectively.
I'm going to use the following commands. Let me know if you think
these commands will do the trick will you please?. The table name is
"A" for the sake of simplicity:-
Firstly change the identity increment value to 2
ALTER TABLE A
ALTER COLUMN identityColumn
IDENTITY (1, 2)
Then reseed all values so that odd become
DBCC CHECKIDENT('A', RESEED, 0)
Comments/corrections/suggestions much appreciated.
Al.Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157033831.447625.273890@.b28g2000cwb.googlegroups.com...
> Hi,
>
> I want to change the identity values of an identity column of a table
> that already has rows in it, such that, it begins at zero and goes up
> in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
> usual 1, 2, 3, 4, etc...
> The current values of the identity seed & identity increment are the
> default values of "1" respectively.
> I'm going to use the following commands. Let me know if you think
> these commands will do the trick will you please?. The table name is
> "A" for the sake of simplicity:-
> Firstly change the identity increment value to 2
> ALTER TABLE A
> ALTER COLUMN identityColumn
> IDENTITY (1, 2)
> Then reseed all values so that odd become
> DBCC CHECKIDENT('A', RESEED, 0)
>
> Comments/corrections/suggestions much appreciated.
> Al.
>|||I think that the best option for you is to create a new table with the
IDENTITY seed value you desire, and copy the current table (without the
IDENTITY column) to the new table, then drop the old table and rename the
new table to the old name.
I don't think that DBCC CHECKIDENT() will renumber the existing data.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157033831.447625.273890@.b28g2000cwb.googlegroups.com...
> Hi,
>
> I want to change the identity values of an identity column of a table
> that already has rows in it, such that, it begins at zero and goes up
> in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
> usual 1, 2, 3, 4, etc...
> The current values of the identity seed & identity increment are the
> default values of "1" respectively.
> I'm going to use the following commands. Let me know if you think
> these commands will do the trick will you please?. The table name is
> "A" for the sake of simplicity:-
> Firstly change the identity increment value to 2
> ALTER TABLE A
> ALTER COLUMN identityColumn
> IDENTITY (1, 2)
> Then reseed all values so that odd become
> DBCC CHECKIDENT('A', RESEED, 0)
>
> Comments/corrections/suggestions much appreciated.
> Al.
>|||Hi Arnie,
Thanks for that but do you know how I would renumber the values
from 1, 2, 3, 4, to the 1, 3, 5, 7 sequence?
Puzzled,
Al.|||On the new table, set the IDENTITY seed and increment values as you wish
before you copy the old table values.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157046915.027819.46040@.b28g2000cwb.googlegroups.com...
> Hi Arnie,
> Thanks for that but do you know how I would renumber the values
> from 1, 2, 3, 4, to the 1, 3, 5, 7 sequence?
> Puzzled,
> Al.
>

Changing the identity seed & increment programatically

Hi,
I want to change the identity values of an identity column of a table
that already has rows in it, such that, it begins at zero and goes up
in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
usual 1, 2, 3, 4, etc...
The current values of the identity seed & identity increment are the
default values of "1" respectively.
I'm going to use the following commands. Let me know if you think
these commands will do the trick will you please?. The table name is
"A" for the sake of simplicity:-
Firstly change the identity increment value to 2
ALTER TABLE A
ALTER COLUMN identityColumn
IDENTITY (1, 2)
Then reseed all values so that odd become
DBCC CHECKIDENT('A', RESEED, 0)
Comments/corrections/suggestions much appreciated.
Al.--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157033831.447625.273890@.b28g2000cwb.googlegroups.com...
> Hi,
>
> I want to change the identity values of an identity column of a table
> that already has rows in it, such that, it begins at zero and goes up
> in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
> usual 1, 2, 3, 4, etc...
> The current values of the identity seed & identity increment are the
> default values of "1" respectively.
> I'm going to use the following commands. Let me know if you think
> these commands will do the trick will you please?. The table name is
> "A" for the sake of simplicity:-
> Firstly change the identity increment value to 2
> ALTER TABLE A
> ALTER COLUMN identityColumn
> IDENTITY (1, 2)
> Then reseed all values so that odd become
> DBCC CHECKIDENT('A', RESEED, 0)
>
> Comments/corrections/suggestions much appreciated.
> Al.
>|||I think that the best option for you is to create a new table with the
IDENTITY seed value you desire, and copy the current table (without the
IDENTITY column) to the new table, then drop the old table and rename the
new table to the old name.
I don't think that DBCC CHECKIDENT() will renumber the existing data.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157033831.447625.273890@.b28g2000cwb.googlegroups.com...
> Hi,
>
> I want to change the identity values of an identity column of a table
> that already has rows in it, such that, it begins at zero and goes up
> in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
> usual 1, 2, 3, 4, etc...
> The current values of the identity seed & identity increment are the
> default values of "1" respectively.
> I'm going to use the following commands. Let me know if you think
> these commands will do the trick will you please?. The table name is
> "A" for the sake of simplicity:-
> Firstly change the identity increment value to 2
> ALTER TABLE A
> ALTER COLUMN identityColumn
> IDENTITY (1, 2)
> Then reseed all values so that odd become
> DBCC CHECKIDENT('A', RESEED, 0)
>
> Comments/corrections/suggestions much appreciated.
> Al.
>|||Hi Arnie,
Thanks for that but do you know how I would renumber the values
from 1, 2, 3, 4, to the 1, 3, 5, 7 sequence?
Puzzled,
Al.|||On the new table, set the IDENTITY seed and increment values as you wish
before you copy the old table values.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157046915.027819.46040@.b28g2000cwb.googlegroups.com...
> Hi Arnie,
> Thanks for that but do you know how I would renumber the values
> from 1, 2, 3, 4, to the 1, 3, 5, 7 sequence?
> Puzzled,
> Al.
>

Changing the identity range values with database name keeping the same.

Hello,
What are the steps for changing the identity range values?
We have backup the database that contained initial publication with
identity range set to 1000. Now when I delete the existing database with
replication, and then create a new DB with same name and restore the
database it maintains the publication and does not allow me to change
the identity range values. Also constraints are not modified. If I
create a new publication two identity range constraints are generated.
However when I create a new DB with a new name, restore the database and
then create another publication it correctly modifies the identity range
constraints with new value say 20000.
I want database with same name to be created. Please help.
Neha Magia
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Replication does not remove the constraints it uses to maintain the identity
ranges on the subscriber tables. You will have to remove these manually.
It may be possible that these constraints are also in place on the
publisher. You will have to drop the publication, remove the constraints is
they still exists, and then rebuild it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Neha Magia" <nehamagia9@.yahoo.com> wrote in message
news:ussPRbZ7EHA.1292@.TK2MSFTNGP10.phx.gbl...
> Hello,
> What are the steps for changing the identity range values?
> We have backup the database that contained initial publication with
> identity range set to 1000. Now when I delete the existing database with
> replication, and then create a new DB with same name and restore the
> database it maintains the publication and does not allow me to change
> the identity range values. Also constraints are not modified. If I
> create a new publication two identity range constraints are generated.
> However when I create a new DB with a new name, restore the database and
> then create another publication it correctly modifies the identity range
> constraints with new value say 20000.
> I want database with same name to be created. Please help.
> Neha Magia
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Saturday, February 25, 2012

Changing of @@IDENTITY item in a trigger

Hello!

I have a instead of insert trigger that after doing some validation, inserts a record into a specific table. Another table requires the key generated by this insert in order to add a record, which is done at the end of the trigger. When called from a client (via a recordset.update call) the newly created key from the first insert is overridden with the key from the second insert. Any ideas on how I can save the first key, then pass it back to the client? The client currently receives the second key created.

Thanks in advance,

EverettI have exactly the same problem and although I don't have a solution (yet), I have found out that this appears to be by design. ADO does a "Select @.@.IDENTITY" after an insert to retrieve the key of the record inserted (See Chapter 11 of "Programming ADO" by David Sceppa at http://www.microsoft.com/mspress/books/sampchap/3445a.asp). If there was some way to modify ADO's behaviour to use instead "Select IDENT_CURRENT('<tablename>')" then problem solved. The article describes a "Update Resync" dynamic record set property but it doesn't appear to be flexible enough to tell ADO to use IDENT_CURRENT.

The hunt continues...

Friday, February 24, 2012

Changing local variable inside query

/*Given*/
CREATE TABLE [_T1sub] (
[PK] [int] IDENTITY (1, 1) NOT NULL ,
[FK] [int] NULL ,
[St] [char] (2) NULL ,
[Wt] [int] NULL ,
CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED
(
[PK]
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10)
INSERT INTO _T1sub (FK,St,Wt) VALUES (2,'nv',20)
INSERT INTO _T1sub (FK,St,Wt) VALUES (3,'wa',30)
/*
Is something like the following possible.
The point is to change the value of the variable
inside the query and use it in the calculated field.
This doesn't compile of course, but is there
a way to accomplish the same thing?
*/
DECLARE @.ndx int
SET @.ndx = 1
SELECT
(a.FK+ (CASE WHEN @.ndx > 0
THEN (SELECT @.ndx = b.Wt
FROM _T1sub b
WHERE b.Wt = a.Wt)
ELSE 0 END)
) as FKplusWT
FROM _T1sub a
/*Output would look like this:*/
FKplusWT
11
22
33
/*
I know, I can get this output just by adding
FK+WT. This is not about that.
This is about setting vars inside a query
*/
thanks, Otto Porter
On Sat, 02 Oct 2004 12:21:54 -0600, Otto Porter wrote:
(snip)
Hi Otto,
I just answered this question in comp.databases.ms-sqlserver. Please do
not post the same question independently to multiple newsgroups.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Changing local variable inside query

/*Given*/
CREATE TABLE [_T1sub] (
[PK] [int] IDENTITY (1, 1) NOT NULL ,
[FK] [int] NULL ,
[St] [char] (2) NULL ,
[Wt] [int] NULL ,
CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED
(
[PK]
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10)
INSERT INTO _T1sub (FK,St,Wt) VALUES (2,'nv',20)
INSERT INTO _T1sub (FK,St,Wt) VALUES (3,'wa',30)
/*
Is something like the following possible.
The point is to change the value of the variable
inside the query and use it in the calculated field.
This doesn't compile of course, but is there
a way to accomplish the same thing?
*/
DECLARE @.ndx int
SET @.ndx = 1
SELECT
(a.FK+ (CASE WHEN @.ndx > 0
THEN (SELECT @.ndx = b.Wt
FROM _T1sub b
WHERE b.Wt = a.Wt)
ELSE 0 END)
) as FKplusWT
FROM _T1sub a
/*Output would look like this:*/
FKplusWT
--
11
22
33
/*
I know, I can get this output just by adding
FK+WT. This is not about that.
This is about setting vars inside a query
*/
thanks, Otto PorterOn Sat, 02 Oct 2004 12:21:54 -0600, Otto Porter wrote:
(snip)
Hi Otto,
I just answered this question in comp.databases.ms-sqlserver. Please do
not post the same question independently to multiple newsgroups.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Sunday, February 19, 2012

Changing identity seed and increment after the fact

Hi all,

I've been thrown a curve ball late in the game on an application I'm developing. Without getting into the specifics of the application I store a unique employee ID number for all person records in it. This ID is provided to us by the companies we're servicing. Up until this point in time we didn't have need of an externally visable ID other than the one provided to us. Now, a need for an internally generated (by our application) unique ID has been discovered. This number needs to be a minimum 5 digits (e.x. 10001, 10002, ...). I could achieve this nicely by seeding an identity column at 10000 with an increment of 1. However, I've alredy made different settings for this and there are records in the db with the old ones.

My question is: If I initially set the seed / increment at 1/1 can I change this after the fact without causing data integrity problems. Will all subsequent insertions into this table just start at the new values?

Or, ideally I'd like to create a new column seperate from the PK Identity column already in place that serves the same function, incrementing a 5 digit number by 1 for each new record. Seems that you're only allowed one identity column per table though. Is there another way to achieve get the same result as identity?

Thanks!You can use DBCC Checkident to reseed the seeding of the identity column.|||Thanks,

I'm Looking at the help files on DBCC checkident now. It doesn't look like it will convert any existing records in the table. If there are 100 records in the table with identity values 1 through 100 and I change the seed/increment valuse to 10000/+1, I'm asuming that the existing 100 records will maintain their current ident values?|||Yes, the value will be the same even you execute the DBCC. You can save the table to a temp, delete all rows out of the existing table, change the identity seeding, and then reload the data. That will do it.|||I think that will work. Thanks again for the help!

Changing Identity Seed

Hi All,
I am using SQL Server 2000 and I am trying to change the identity seed in a
table. I want to change this via a script. I have used the following command:
DBCC CHECKIDENT (dbo, RESEED, value)
where
dbo = the table in the database whose seed I want to change
value = the value I would like to change the value in Identity seed too.
I get the following result:
"Checking identity information: current identity value '100017140', current
column value '100017148'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator."
Then when I go to the table --> right click --> desigh --> the identity seed
has not changed to what the message above has said it has changed too.
I have tried refreshing the table but still no luck.
Can anyone help. I am looking to have the value to be changed in the design
view for Identity seed.
Thanks.
A
Thanks
AaaaaAaaa
It works just fine (why do you call the table 'dbo'?)
create table test (c int not null identity(1,1))
go
insert into test default values
insert into test default values
insert into test default values
go
select * from test --we have 3 rows
/*
c
--
1
2
3
*/
go
dbcc checkident (test, RESEED, 1)
insert into test default values
insert into test default values
insert into test default values
select * from test
drop table test
"Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
news:CB7CB183-88B8-462E-B696-CC0380A459F2@.microsoft.com...
> Hi All,
> I am using SQL Server 2000 and I am trying to change the identity seed in
> a
> table. I want to change this via a script. I have used the following
> command:
> DBCC CHECKIDENT (dbo, RESEED, value)
> where
> dbo = the table in the database whose seed I want to change
> value = the value I would like to change the value in Identity seed too.
> I get the following result:
> "Checking identity information: current identity value '100017140',
> current
> column value '100017148'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator."
> Then when I go to the table --> right click --> desigh --> the identity
> seed
> has not changed to what the message above has said it has changed too.
> I have tried refreshing the table but still no luck.
> Can anyone help. I am looking to have the value to be changed in the
> design
> view for Identity seed.
> Thanks.
> A
> Thanks
> Aaaaa|||Hi Uri
Thanks for responding. I only wrote dbo instead of test.
I created a test table like you suggested. Then when you right click on the
test table --> go to design --> at the bottom is a table where the following
is written:
Columns
Description
...
..
Identity Yes
Identity Seed 1 --> this is what i
would like to
Idenitity Increment 1 change, but
the code does not
.... do
so.
....
I am new to SQL Server 2000 and this forum, I am not sure if I am typing or
checking something wrong.
Please bear with the little knowledge I have.
Thank you so much.
A
Thanks
Aaaaa
"Uri Dimant" wrote:
> Aaaa
> It works just fine (why do you call the table 'dbo'?)
> create table test (c int not null identity(1,1))
> go
> insert into test default values
> insert into test default values
> insert into test default values
> go
> select * from test --we have 3 rows
> /*
> c
> --
> 1
> 2
> 3
> */
> go
> dbcc checkident (test, RESEED, 1)
> insert into test default values
> insert into test default values
> insert into test default values
> select * from test
> drop table test
>
> "Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
> news:CB7CB183-88B8-462E-B696-CC0380A459F2@.microsoft.com...
> > Hi All,
> >
> > I am using SQL Server 2000 and I am trying to change the identity seed in
> > a
> > table. I want to change this via a script. I have used the following
> > command:
> >
> > DBCC CHECKIDENT (dbo, RESEED, value)
> > where
> > dbo = the table in the database whose seed I want to change
> > value = the value I would like to change the value in Identity seed too.
> >
> > I get the following result:
> > "Checking identity information: current identity value '100017140',
> > current
> > column value '100017148'.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator."
> >
> > Then when I go to the table --> right click --> desigh --> the identity
> > seed
> > has not changed to what the message above has said it has changed too.
> >
> > I have tried refreshing the table but still no luck.
> >
> > Can anyone help. I am looking to have the value to be changed in the
> > design
> > view for Identity seed.
> >
> > Thanks.
> > A
> >
> > Thanks
> > Aaaaa
>
>|||> Then when I go to the table --> right click --> desigh --> the identity
> seed
> has not changed to what the message above has said it has changed too.
> I have tried refreshing the table but still no luck.
Did you try inserting a row into the table? Did you try closing and
re-opening Management Studio?
--
Aaron Bertrand
SQL Server MVP|||> Did you try inserting a row into the table? Did you try closing and
> re-opening Management Studio?
I inserted a new table, shut down the application and opened it up. When it
opened the table had been updated (test --> right click --> open table -->
return all rows)
But then when I check design --> column --> identity seed the value has not
changed.
This is where the problem lies.
A
--
Thanks
Aaaaa
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Then when I go to the table --> right click --> desigh --> the identity
> > seed
> > has not changed to what the message above has said it has changed too.
> >
> > I have tried refreshing the table but still no luck.
> Did you try inserting a row into the table? Did you try closing and
> re-opening Management Studio?
> --
> Aaron Bertrand
> SQL Server MVP
>
>|||On Mon, 30 Jul 2007 06:36:01 -0700, Aaaaa
<Aaaaa@.discussions.microsoft.com> wrote:
>I inserted a new table, shut down the application and opened it up. When it
>opened the table had been updated (test --> right click --> open table -->
>return all rows)
>But then when I check design --> column --> identity seed the value has not
>changed.
>This is where the problem lies.
Be sure to right-click on the table in the tree and choose REFRESH.
Roy Harvey
Beacon Falls, CT|||Hi
I definately have refreshed each time. Still no joy. It seems to update
the table but not the design view.
A
--
Thanks
Aaaaa
"Roy Harvey" wrote:
> On Mon, 30 Jul 2007 06:36:01 -0700, Aaaaa
> <Aaaaa@.discussions.microsoft.com> wrote:
> >I inserted a new table, shut down the application and opened it up. When it
> >opened the table had been updated (test --> right click --> open table -->
> >return all rows)
> >But then when I check design --> column --> identity seed the value has not
> >changed.
> >
> >This is where the problem lies.
> Be sure to right-click on the table in the tree and choose REFRESH.
> Roy Harvey
> Beacon Falls, CT
>|||> Be sure to right-click on the table in the tree and choose REFRESH.
No, this really is a bug in the table designer. I can reproduce it in 2000,
2005 and even in Katmai. There doesn't seem to be a way to make the table
designer reflect numerous changes to the identity seed value...
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289675
(The question, of course, is where on earth does SQL Server store the 1,1
from initial creation? They must be coming from somewhere. Yes, I'm too
lazy this morning to fire up profiler.)
--
Aaron Bertrand
SQL Server MVP|||Thanks Aaron
I have tried to run a trace via SQL Profiler though I am having some issues
with the parameters returning more indepth information on where (1,1) is
stored. Could you advise on what I could include with this for more
information?
Morning by the way (evening for me)
Thanks
Ads
Aaaaa
--
Thanks
Aaaaa
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Be sure to right-click on the table in the tree and choose REFRESH.
> No, this really is a bug in the table designer. I can reproduce it in 2000,
> 2005 and even in Katmai. There doesn't seem to be a way to make the table
> designer reflect numerous changes to the identity seed value...
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289675
> (The question, of course, is where on earth does SQL Server store the 1,1
> from initial creation? They must be coming from somewhere. Yes, I'm too
> lazy this morning to fire up profiler.)
> --
> Aaron Bertrand
> SQL Server MVP
>
>|||In SQL 2008, I picked this up from TSQL:StmtCompleted:
select col.name, col.column_id,
st.name as DT_name,
schema_name(st.schema_id) as DT_schema,
col.max_length, col.precision, col.scale, bt.name as BT_name,
col.collation_name, col.is_nullable, col.is_ansi_padded,
col.is_rowguidcol, col.is_identity,
case when(idc.column_id is null)
then null else CONVERT(nvarchar(40), idc.seed_value) end,
case when(idc.column_id is null) then null
else CONVERT(nvarchar(40), idc.increment_value) end,
CONVERT(bit, case when(cmc.column_id is null) then 0 else 1 end)
as is_computed,
convert(bit, ColumnProperty(col.object_id, col.name, N'IsIdNotForRepl'))
as IsIdNotForRepl,
col.is_replicated,
col.is_non_sql_subscribed, col.is_merge_published,
col.is_dts_replicated, col.rule_object_id, robj.name as Rul_name,
schema_name(robj.schema_id) as Rul_schema, col.default_object_id,
OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst,
dobj.name as def_name, schema_name(dobj.schema_id) as def_schema,
CONVERT(bit, case when (ftc.column_id is null) then 0 else 1 end) as
is_FullTextCol,
col_name(col.object_id, ftc.type_column_id) FT_type_column,
ftc.language_id as FT_language_id,
case when(cmc.column_id is null) then null else cmc.definition end as
formular,
case when(cmc.column_id is null) then null else cmc.is_persisted end as
is_persisted,
defCst.definition, COLUMNPROPERTY(col.object_id, col.name,
'IsDeterministic')
as IsDeterministic, xmlcoll.name as xmlSchema_name,
schema_name(xmlcoll.schema_id)
as xmlSchema_schema, col.is_xml_document from sys.columns col
left outer join sys.types st on st.user_type_id = col.user_type_id left
outer join
sys.types bt on bt.user_type_id = col.system_type_id
left outer join sys.objects robj on robj.object_id = col.rule_object_id
and robj.type = 'R' left outer join sys.objects dobj on
dobj.object_id = col.default_object_id and dobj.type = 'D' left outer join
sys.default_constraints defCst on defCst.parent_object_id = col.object_id
and defCst.parent_column_id = col.column_id left outer join
sys.identity_columns idc on idc.object_id = col.object_id and idc.column_id
= col.column_id left outer join sys.computed_columns cmc on cmc.object_id =col.object_id and cmc.column_id = col.column_id left outer join
sys.fulltext_index_columns ftc on ftc.object_id = col.object_id and
ftc.column_id = col.column_id left outer join sys.xml_schema_collections
xmlcoll on xmlcoll.xml_Collection_id = col.xml_Collection_id where
col.object_id = object_id(N'dbo.foo') order by col.column_id
Which I whittled down to:
SELECT name,seed_value,increment_value,last_value
FROM sys.identity_columns
WHERE [object_id] = OBJECT_ID('dbo.foo');
The result was:
bar, 1, 1, 5
Notice that seed_value in sys.identity_columns has not changed, though
current_value has.
--
Aaron Bertrand
SQL Server MVP
"Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
news:4ECE6DDB-05C9-4DEE-8E39-ED16EE5D8820@.microsoft.com...
> Thanks Aaron
> I have tried to run a trace via SQL Profiler though I am having some
> issues
> with the parameters returning more indepth information on where (1,1) is
> stored. Could you advise on what I could include with this for more
> information?
> Morning by the way (evening for me)
> Thanks
> Ads
> Aaaaa
> --
> Thanks
> Aaaaa
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>> > Be sure to right-click on the table in the tree and choose REFRESH.
>> No, this really is a bug in the table designer. I can reproduce it in
>> 2000,
>> 2005 and even in Katmai. There doesn't seem to be a way to make the
>> table
>> designer reflect numerous changes to the identity seed value...
>> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289675
>> (The question, of course, is where on earth does SQL Server store the 1,1
>> from initial creation? They must be coming from somewhere. Yes, I'm too
>> lazy this morning to fire up profiler.)
>> --
>> Aaron Bertrand
>> SQL Server MVP
>>
>>|||That was very well shrunk BUT i still failed miserably and this code did not
work. Too many errors,
> SELECT name,seed_value,increment_value,last_value
> FROM sys.identity_columns
> WHERE [object_id] = OBJECT_ID('dbo.foo');
The system did not like 'sys.identity_columns' for a start.
Sorry about bugging you on this. This bug is really bugging me.
Would the first line be formatted like this?
SELECT test,24,1,21
Ads
--
Thanks
Aaaaa
"Aaron Bertrand [SQL Server MVP]" wrote:
> In SQL 2008, I picked this up from TSQL:StmtCompleted:
> select col.name, col.column_id,
> st.name as DT_name,
> schema_name(st.schema_id) as DT_schema,
> col.max_length, col.precision, col.scale, bt.name as BT_name,
> col.collation_name, col.is_nullable, col.is_ansi_padded,
> col.is_rowguidcol, col.is_identity,
> case when(idc.column_id is null)
> then null else CONVERT(nvarchar(40), idc.seed_value) end,
> case when(idc.column_id is null) then null
> else CONVERT(nvarchar(40), idc.increment_value) end,
> CONVERT(bit, case when(cmc.column_id is null) then 0 else 1 end)
> as is_computed,
> convert(bit, ColumnProperty(col.object_id, col.name, N'IsIdNotForRepl'))
> as IsIdNotForRepl,
> col.is_replicated,
> col.is_non_sql_subscribed, col.is_merge_published,
> col.is_dts_replicated, col.rule_object_id, robj.name as Rul_name,
> schema_name(robj.schema_id) as Rul_schema, col.default_object_id,
> OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst,
> dobj.name as def_name, schema_name(dobj.schema_id) as def_schema,
> CONVERT(bit, case when (ftc.column_id is null) then 0 else 1 end) as
> is_FullTextCol,
> col_name(col.object_id, ftc.type_column_id) FT_type_column,
> ftc.language_id as FT_language_id,
> case when(cmc.column_id is null) then null else cmc.definition end as
> formular,
> case when(cmc.column_id is null) then null else cmc.is_persisted end as
> is_persisted,
> defCst.definition, COLUMNPROPERTY(col.object_id, col.name,
> 'IsDeterministic')
> as IsDeterministic, xmlcoll.name as xmlSchema_name,
> schema_name(xmlcoll.schema_id)
> as xmlSchema_schema, col.is_xml_document from sys.columns col
> left outer join sys.types st on st.user_type_id = col.user_type_id left
> outer join
> sys.types bt on bt.user_type_id = col.system_type_id
> left outer join sys.objects robj on robj.object_id = col.rule_object_id
> and robj.type = 'R' left outer join sys.objects dobj on
> dobj.object_id = col.default_object_id and dobj.type = 'D' left outer join
> sys.default_constraints defCst on defCst.parent_object_id = col.object_id
> and defCst.parent_column_id = col.column_id left outer join
> sys.identity_columns idc on idc.object_id = col.object_id and idc.column_id
> = col.column_id left outer join sys.computed_columns cmc on cmc.object_id => col.object_id and cmc.column_id = col.column_id left outer join
> sys.fulltext_index_columns ftc on ftc.object_id = col.object_id and
> ftc.column_id = col.column_id left outer join sys.xml_schema_collections
> xmlcoll on xmlcoll.xml_Collection_id = col.xml_Collection_id where
> col.object_id = object_id(N'dbo.foo') order by col.column_id
> Which I whittled down to:
> SELECT name,seed_value,increment_value,last_value
> FROM sys.identity_columns
> WHERE [object_id] = OBJECT_ID('dbo.foo');
> The result was:
> bar, 1, 1, 5
> Notice that seed_value in sys.identity_columns has not changed, though
> current_value has.
> --
> Aaron Bertrand
> SQL Server MVP
>
>
> "Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
> news:4ECE6DDB-05C9-4DEE-8E39-ED16EE5D8820@.microsoft.com...
> > Thanks Aaron
> >
> > I have tried to run a trace via SQL Profiler though I am having some
> > issues
> > with the parameters returning more indepth information on where (1,1) is
> > stored. Could you advise on what I could include with this for more
> > information?
> >
> > Morning by the way (evening for me)
> >
> > Thanks
> > Ads
> > Aaaaa
> > --
> > Thanks
> > Aaaaa
> >
> >
> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >
> >> > Be sure to right-click on the table in the tree and choose REFRESH.
> >>
> >> No, this really is a bug in the table designer. I can reproduce it in
> >> 2000,
> >> 2005 and even in Katmai. There doesn't seem to be a way to make the
> >> table
> >> designer reflect numerous changes to the identity seed value...
> >>
> >> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289675
> >>
> >> (The question, of course, is where on earth does SQL Server store the 1,1
> >> from initial creation? They must be coming from somewhere. Yes, I'm too
> >> lazy this morning to fire up profiler.)
> >>
> >> --
> >> Aaron Bertrand
> >> SQL Server MVP
> >>
> >>
> >>
> >>
>
>|||> That was very well shrunk BUT i still failed miserably and this code did
> not
> work. Too many errors,
I know, as I stated, this was for SQL 2005/2008, not for 2000. You will
have to turn Profiler on, including TSQL:StmtCompleted, and then open the
table in design view. There will be a bunch of statements there, and one of
them will include something about identity / seed.
--
Aaron Bertrand
SQL Server MVP|||Hi
I tried turning on profiler and then checking the table in design view.
Unfortunately was not able to find any relating table to find where the
identity seed in design view is entered. Any other ideas?
Thank you so much for all your help.
Ads
--
Thanks
Aaaaa
"Aaron Bertrand [SQL Server MVP]" wrote:
> > That was very well shrunk BUT i still failed miserably and this code did
> > not
> > work. Too many errors,
> I know, as I stated, this was for SQL 2005/2008, not for 2000. You will
> have to turn Profiler on, including TSQL:StmtCompleted, and then open the
> table in design view. There will be a bunch of statements there, and one of
> them will include something about identity / seed.
> --
> Aaron Bertrand
> SQL Server MVP
>
>|||Does it really matter where it's stored? It's WRONG! I'd fire up profiler
and look at the same queries for you, but I don't think it's really worth
it. They're not likely to fix this for SQL Server 2000 anyway. But there
is a chance they will fix it for 2005, and an even better chance they will
fix it in 2008.
--
Aaron Bertrand
SQL Server MVP
"Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
news:83E7C60B-C39B-4B64-B7A6-60AA1DD09DB2@.microsoft.com...
> Hi
> I tried turning on profiler and then checking the table in design view.
> Unfortunately was not able to find any relating table to find where the
> identity seed in design view is entered. Any other ideas?
> Thank you so much for all your help.

Changing Identity Seed

Changing identity column value

Hi. Is there a way to remove a gap between identity column values? Some records were deleted and now we have identity column values that jump all over the place.

Normally, this is not a good idea. There are typically a number of problems that you add to your list of problems whenever you try to "solve this kind of problem." Yes, this can be done, however, some problems that you will have to address include:

Taking the table "offline"

Resolving referential integrity problems.

Dropping and recreating the table

verifying that the change is "correct"

|||Programatically you can do that using :

SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }

(see Books Online)

if you have the order of identity:
1,2,5,6

with command above you can insert the records 3 and 4

Friday, February 10, 2012

Changing data type

What's the best way to change numeric column to Identity column?
Table: Orders
Field: OrderID (numeric) must be change to Identity
Records: 120,000
Thanks,
WalterTo change it programmatically you have to create a new table and then
insert the existing data. If you make the change in the Enterprise
Manager interface it will automatically generate the script for you.
For 120K rows it should be feasible to recreate the table that way.
David Portas
SQL Server MVP
--|||Walter,
There is not an easy way to do this,. SQL Server does not let you add the
IDENTITY property to an existing column, so you have to create a temporary
table, add existing rows, drop foreign key constraints referencing the old
column, rename the table, create fk constraints again, etc. Do it using EM,
while in design mode, change the identity property of the column and click
"save change script" button (third from left to right in the tool bar) and
you will see the script.
AMB
"Walter" wrote:

> What's the best way to change numeric column to Identity column?
>
> Table: Orders
> Field: OrderID (numeric) must be change to Identity
> Records: 120,000
>
> Thanks,
> Walter
>
>

Changing Data Format On Select

Hey all,
I have a basic table that looks something like this.

CREATE TABLE MyTable
(
ID INT IDENTITY PRIMARY KEY,
Company_ID INT NOT NULL,
Round VARCHAR(50) NOT NULL,
Details VARCHAR(250) NOT NULL
)

It has a few rows of data that look like this:

Identity Company_ID Round Details
--------------
1 5 A Blah, blah.
2 5 B Generic data, blah blah.
3 5 WERT More generic blah blah.

Now what i'm trying to do during my select statement is select all the rows
that belong to company_id 5 but if any of the rows round value contains the
text "WERT" convert that text into just a "--" for presentation purposes,
but still select that row. I can't seem to figure out how i would transform
the text in the select statement? My immediate thought was substring /
replace but i would need to combine it with an if else statement which i've
no idea how to make work in a select (sub-query maybe?) statement. Is this
possible? Perhaps i'm stuck iterating through the returned data within the
client application before presenting?

Any help, as always, would be greatly appreciated.

MuhdMuhd,

SELECT ID, Company_ID,
[Round] = CASE [Round] WHEN 'WERT' THEN '--' ELSE [Round] END,
Details
FROM MyTable
WHERE Company_ID = 5

-Andy

"Muhd" <eat@.joes.com> wrote in message news:7MZ4d.91084$%S.84951@.pd7tw2no...
> Hey all,
> I have a basic table that looks something like this.
> CREATE TABLE MyTable
> (
> ID INT IDENTITY PRIMARY KEY,
> Company_ID INT NOT NULL,
> Round VARCHAR(50) NOT NULL,
> Details VARCHAR(250) NOT NULL
> )
> It has a few rows of data that look like this:
> Identity Company_ID Round Details
> --------------
> 1 5 A Blah, blah.
> 2 5 B Generic data, blah blah.
> 3 5 WERT More generic blah blah.
>
> Now what i'm trying to do during my select statement is select all the
> rows that belong to company_id 5 but if any of the rows round value
> contains the text "WERT" convert that text into just a "--" for
> presentation purposes, but still select that row. I can't seem to figure
> out how i would transform the text in the select statement? My immediate
> thought was substring / replace but i would need to combine it with an if
> else statement which i've no idea how to make work in a select (sub-query
> maybe?) statement. Is this possible? Perhaps i'm stuck iterating through
> the returned data within the client application before presenting?
> Any help, as always, would be greatly appreciated.
> Muhd|||Although Andy has shown you how you can accomplish the task using
Transact-SQL, formatting data for presentation purposes is generally best
handled on the client side, IMHO. Most programming languages provide a
number of methods to format data.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Muhd" <eat@.joes.com> wrote in message news:7MZ4d.91084$%S.84951@.pd7tw2no...
> Hey all,
> I have a basic table that looks something like this.
> CREATE TABLE MyTable
> (
> ID INT IDENTITY PRIMARY KEY,
> Company_ID INT NOT NULL,
> Round VARCHAR(50) NOT NULL,
> Details VARCHAR(250) NOT NULL
> )
> It has a few rows of data that look like this:
> Identity Company_ID Round Details
> --------------
> 1 5 A Blah, blah.
> 2 5 B Generic data, blah blah.
> 3 5 WERT More generic blah blah.
>
> Now what i'm trying to do during my select statement is select all the
> rows that belong to company_id 5 but if any of the rows round value
> contains the text "WERT" convert that text into just a "--" for
> presentation purposes, but still select that row. I can't seem to figure
> out how i would transform the text in the select statement? My immediate
> thought was substring / replace but i would need to combine it with an if
> else statement which i've no idea how to make work in a select (sub-query
> maybe?) statement. Is this possible? Perhaps i'm stuck iterating through
> the returned data within the client application before presenting?
> Any help, as always, would be greatly appreciated.
> Muhd|||While the front end is a very good place for presentation or value decoding
...
having a record transformation in the database allows the logic to be
centralized and recorded somewhere.

what if in addition to WERT you need to ignore SPAM, just add a row to the
table. no front end changes needed.

create a decoder table and join to that returning --

create view round_decoder
(
Round VARCHAR(50) NOT NULL,
DisplayName VARCHAR(50) NOT NULL
)

insert into round_decoder ( 'WERT' , '--' )

SELECT
Identity,
Company_ID ,
IsNull( d.DisplayName , x.Round ) as Round ,
Details
FROM MyTable x
LEFT JOIN round_decoder d on x.Round = d.Round

you can query this decoder table for drop down lists or other data
selection / presentation stuff in the front end.
i would recommend a reusable object that get's this data and does the
decoding in the front end.

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:pfd6d.1939$zc1.1654@.newssvr12.news.prodigy.co m...
> Although Andy has shown you how you can accomplish the task using
> Transact-SQL, formatting data for presentation purposes is generally best
> handled on the client side, IMHO. Most programming languages provide a
> number of methods to format data.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Muhd" <eat@.joes.com> wrote in message
news:7MZ4d.91084$%S.84951@.pd7tw2no...
> > Hey all,
> > I have a basic table that looks something like this.
> > CREATE TABLE MyTable
> > (
> > ID INT IDENTITY PRIMARY KEY,
> > Company_ID INT NOT NULL,
> > Round VARCHAR(50) NOT NULL,
> > Details VARCHAR(250) NOT NULL
> > )
> > It has a few rows of data that look like this:
> > Identity Company_ID Round Details
> > --------------
> > 1 5 A Blah, blah.
> > 2 5 B Generic data, blah
blah.
> > 3 5 WERT More generic blah blah.
> > Now what i'm trying to do during my select statement is select all the
> > rows that belong to company_id 5 but if any of the rows round value
> > contains the text "WERT" convert that text into just a "--" for
> > presentation purposes, but still select that row. I can't seem to
figure
> > out how i would transform the text in the select statement? My
immediate
> > thought was substring / replace but i would need to combine it with an
if
> > else statement which i've no idea how to make work in a select
(sub-query
> > maybe?) statement. Is this possible? Perhaps i'm stuck iterating
through
> > the returned data within the client application before presenting?
> > Any help, as always, would be greatly appreciated.
> > Muhd|||Thanks for that, David... Great idea!

-Andy

"David Rawheiser" <rawhide58@.hotmail.com> wrote in message
news:4Cx6d.645799$Gx4.11917@.bgtnsc04-news.ops.worldnet.att.net...
> While the front end is a very good place for presentation or value
> decoding
> ...
> having a record transformation in the database allows the logic to be
> centralized and recorded somewhere.
> what if in addition to WERT you need to ignore SPAM, just add a row to the
> table. no front end changes needed.
> create a decoder table and join to that returning --
> create view round_decoder
> (
> Round VARCHAR(50) NOT NULL,
> DisplayName VARCHAR(50) NOT NULL
> )
> insert into round_decoder ( 'WERT' , '--' )
> SELECT
> Identity,
> Company_ID ,
> IsNull( d.DisplayName , x.Round ) as Round ,
> Details
> FROM MyTable x
> LEFT JOIN round_decoder d on x.Round = d.Round
> you can query this decoder table for drop down lists or other data
> selection / presentation stuff in the front end.
> i would recommend a reusable object that get's this data and does the
> decoding in the front end.
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:pfd6d.1939$zc1.1654@.newssvr12.news.prodigy.co m...
>> Although Andy has shown you how you can accomplish the task using
>> Transact-SQL, formatting data for presentation purposes is generally best
>> handled on the client side, IMHO. Most programming languages provide a
>> number of methods to format data.
>>
>> --
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>>
>> "Muhd" <eat@.joes.com> wrote in message
> news:7MZ4d.91084$%S.84951@.pd7tw2no...
>> > Hey all,
>> > I have a basic table that looks something like this.
>>> > CREATE TABLE MyTable
>> > (
>> > ID INT IDENTITY PRIMARY KEY,
>> > Company_ID INT NOT NULL,
>> > Round VARCHAR(50) NOT NULL,
>> > Details VARCHAR(250) NOT NULL
>> > )
>>> > It has a few rows of data that look like this:
>>> > Identity Company_ID Round Details
>> > --------------
>> > 1 5 A Blah, blah.
>> > 2 5 B Generic data, blah
> blah.
>> > 3 5 WERT More generic blah blah.
>>>> > Now what i'm trying to do during my select statement is select all the
>> > rows that belong to company_id 5 but if any of the rows round value
>> > contains the text "WERT" convert that text into just a "--" for
>> > presentation purposes, but still select that row. I can't seem to
> figure
>> > out how i would transform the text in the select statement? My
> immediate
>> > thought was substring / replace but i would need to combine it with an
> if
>> > else statement which i've no idea how to make work in a select
> (sub-query
>> > maybe?) statement. Is this possible? Perhaps i'm stuck iterating
> through
>> > the returned data within the client application before presenting?
>>> > Any help, as always, would be greatly appreciated.
>>> > Muhd
>>>
>>|||Thanks all !!!

I've decided to keep the presentation logic on the presentation tier but i
did rig up a "decoder" table to give it a try and it worked for me really
well. It's something i'll have to add to my toolkit of tricks in case i
need it in the future.

Thanks,
Muhd.

"Andy Williams" <f_u_b_a_r_1_1_1_9@.y_a_h_o_o_._c_o_m> wrote in message
news:c1z6d.11644$Qv5.5768@.newssvr33.news.prodigy.c om...
> Thanks for that, David... Great idea!
> -Andy
> "David Rawheiser" <rawhide58@.hotmail.com> wrote in message
> news:4Cx6d.645799$Gx4.11917@.bgtnsc04-news.ops.worldnet.att.net...
>> While the front end is a very good place for presentation or value
>> decoding
>> ...
>> having a record transformation in the database allows the logic to be
>> centralized and recorded somewhere.
>>
>> what if in addition to WERT you need to ignore SPAM, just add a row to
>> the
>> table. no front end changes needed.
>>
>> create a decoder table and join to that returning --
>>
>> create view round_decoder
>> (
>> Round VARCHAR(50) NOT NULL,
>> DisplayName VARCHAR(50) NOT NULL
>> )
>>
>> insert into round_decoder ( 'WERT' , '--' )
>>
>> SELECT
>> Identity,
>> Company_ID ,
>> IsNull( d.DisplayName , x.Round ) as Round ,
>> Details
>> FROM MyTable x
>> LEFT JOIN round_decoder d on x.Round = d.Round
>>
>> you can query this decoder table for drop down lists or other data
>> selection / presentation stuff in the front end.
>> i would recommend a reusable object that get's this data and does the
>> decoding in the front end.
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:pfd6d.1939$zc1.1654@.newssvr12.news.prodigy.co m...
>>> Although Andy has shown you how you can accomplish the task using
>>> Transact-SQL, formatting data for presentation purposes is generally
>>> best
>>> handled on the client side, IMHO. Most programming languages provide a
>>> number of methods to format data.
>>>
>>> --
>>> Hope this helps.
>>>
>>> Dan Guzman
>>> SQL Server MVP
>>>
>>> "Muhd" <eat@.joes.com> wrote in message
>> news:7MZ4d.91084$%S.84951@.pd7tw2no...
>>> > Hey all,
>>> > I have a basic table that looks something like this.
>>>>> > CREATE TABLE MyTable
>>> > (
>>> > ID INT IDENTITY PRIMARY KEY,
>>> > Company_ID INT NOT NULL,
>>> > Round VARCHAR(50) NOT NULL,
>>> > Details VARCHAR(250) NOT NULL
>>> > )
>>>>> > It has a few rows of data that look like this:
>>>>> > Identity Company_ID Round Details
>>> > --------------
>>> > 1 5 A Blah, blah.
>>> > 2 5 B Generic data, blah
>> blah.
>>> > 3 5 WERT More generic blah blah.
>>>>>>> > Now what i'm trying to do during my select statement is select all the
>>> > rows that belong to company_id 5 but if any of the rows round value
>>> > contains the text "WERT" convert that text into just a "--" for
>>> > presentation purposes, but still select that row. I can't seem to
>> figure
>>> > out how i would transform the text in the select statement? My
>> immediate
>>> > thought was substring / replace but i would need to combine it with an
>> if
>>> > else statement which i've no idea how to make work in a select
>> (sub-query
>>> > maybe?) statement. Is this possible? Perhaps i'm stuck iterating
>> through
>>> > the returned data within the client application before presenting?
>>>>> > Any help, as always, would be greatly appreciated.
>>>>> > Muhd
>>>>>
>>>
>>
>>

Sunday, March 25, 2012

Changing the identity seed & increment programatically

Changing the identity seed & increment programatically

Changing the identity range values with database name keeping the same.

Saturday, February 25, 2012

Changing of @@IDENTITY item in a trigger

Friday, February 24, 2012

Changing local variable inside query

Changing local variable inside query

Sunday, February 19, 2012

Changing identity seed and increment after the fact

Changing Identity Seed

Changing Identity Seed

Changing Identity Seed

Changing identity column value

Friday, February 10, 2012

Changing data type

Changing Data Format On Select

database collation

Blog Archive

About Me