Showing posts with label columns. Show all posts
Showing posts with label columns. Show all posts

Friday, March 30, 2012

If exists update, if it doesn't exist create

What I am looking for is a table that has two columns we'll say FName and Timestamp. What I want to do is to see if that name exists in my records. If it does I want to update it with a new timestamp. If it does not I want to create a new record in my table. This is going to be a check from vb 6.0 that is running continuously, so it will run the stored procedure to look for Redundancy in the FName column, if it finds it I want it to update the timestamp in that record to show the most up to date time.

Thank you in advance.First, check if the record exists, if not then add it to the table :

IF NOT EXISTS (SELECT (1) FROM 'table' WHERE FNAME = 'name')
Insert new record into table

Now update the table

UPDATE 'table'
SET TimeStamp = CURRENT_TIMESTAMP
WHERE fname = 'name'|||

Quote:

Originally Posted by SkinHead

First, check if the record exists, if not then add it to the table :

IF NOT EXISTS (SELECT (1) FROM 'table' WHERE FNAME = 'name')
Insert new record into table

Now update the table

UPDATE 'table'
SET TimeStamp = CURRENT_TIMESTAMP
WHERE fname = 'name'


Thanks for the reply. I guess I wrote my question wrong. I need this stored procedure to search a table for any names that it finds to be the same, not just a specific one, and replace the timestamp with a new timestamp.|||

Quote:

Originally Posted by JReneau35

Thanks for the reply. I guess I wrote my question wrong. I need this stored procedure to search a table for any names that it finds to be the same, not just a specific one, and replace the timestamp with a new timestamp.


You do have a specific name your searching for though right? This method will update all of the records matching your input parameter.

You come in with a name and you want it to search for that name in a table. If it doesnt find it, you want one new record created with a timestamp. If it does find it, no matter how many times it finds it, you want all of them updated.

Am I understanding you right?

IF exists UPDATE ELSE INSERT problem

Hi,

I have a 'Products' table (with: 'uid' and 'CatName' columns) and 'ProductCategory' table (with: 'uid', 'ProductID', 'CategoryID' columns).

I got stored procedure below to update or insert new row to 'ProductCategory' table whenever 'Products' table has been updated or new products has been added to it.

Update part works just fine but when new row has been added to 'Products' this storedProc dosn't insert it into 'ProductCategory' table, it does that only when 'ProductCategory' table is empty, I'm afraid it's because first column 'uid' in 'ProductCategory' table is an Identity column... I'm not sure how should I go about that problem. This is my stored procedure:


DECLARE @.CatNo INT, @.CatName varchar(10)
SET @.CatNo = 2
SET @.CatName = 'bracket'

IF exists (SELECT ProductID from ProductCategory, Products where ProductCategory.ProductID = Products.uid and Products.CatName = @.CatName )
BEGIN
UPDATE ProductCategory SET CategoryID = @.CatNo
FROM Products WHERE Products.CatName = @.CatName and ProductCategory.ProductID = Products.uid
END
ELSE
BEGIN
INSERT INTO ProductCategory ( ProductID, CategoryID)
SELECT uid, @.CatNo FROM Products
WHERE Products.CatName = @.CatName
END

SET @.CatNo = 3
SET @.CatName = 'cable'

IF exists (SELECT ProductID from ProductCategory, Products where ProductCategory.ProductID = Products.uid and Products.CatName = @.CatName )
BEGIN
UPDATE ProductCategory SET CategoryID = @.CatNo
FROM Products WHERE Products.CatName = @.CatName and ProductCategory.ProductID = Products.uid
END
ELSE
BEGIN
INSERT INTO ProductCategory ( ProductID, CategoryID)
SELECT uid, @.CatNo FROM Products
WHERE Products.CatName = @.CatName
END
(... Goes for another 37 categories)

Thank you for help.

KoobaWhat kind of error message returned?
Check if there are unique index? or referencial integratiies ?

If you insert Identity column, make sure turn on IDENTITY_INSERT.|||Do you actually have that exact same code 39 times? That's not good. You can cut out all of that excess code with a few smart statements and a table with your category names.

Example:

Create a table:
Cat (CatNo, CatName)

Data:
(2, 'bracket')
(3, 'cable')
etc...

with your 39 cats. Then you can get rid of all those tedious repeated SQL statements with just 2 SQL statements:


update ProductCategory
set CategoryID = Cat.CatNo
from Products
join ProductCategory
on ProductCategory.ProductID = Products.uid
join Cat
on Products.CatName = Cat.CatName

insert into ProductCategory
(ProductID,
CategoryID)
select uid,
Cat.CatNo
from Products
join Cat
on Products.CatName = Cat.CatName
left join ProductCategory
on ProductCategory.ProductID = Products.uid
where ProductCategory.ProductID is null


This 2 statements will do exactly the same as your 78.|||Looking at your question again I'm pretty sure your DB is not normalised. From your vague description, I believe your tables should be:

ProductCategory (ProductID, CategoryID)
Category (CategoryID, CategoryName) -- CategoryID should be auto-increment identity
Product (ProductID, CategoryID) --ProductID should be auto-increment identity

I also understand from your question and your existing stored proc that you link all categories into all products, in which case the ProdutCategory table becomes redundant unless it stores another column or two.

Wednesday, March 28, 2012

If exists from within a select statement.

I have a simple SQL statement, I need to populate columns from several
tables.
I need to populate some of the columns depending if there is data in a
child table that matched the parent table (a one to many relationship).
How can I put in an "if exists" into a select statement?
I need results that will be
file_a.first, file_a.second, file_b.first (when file_a.third = "y"),
file_b.first (when file_a.fourth = "y") ..
so kinda like :
select file_a.first, file_a.second,
if file_a.third = "y" then select file_b.first from file_b ..
if file_a.fourth= "y" then select file_b.first from file_b ..You can=B4t use specify dynnamic resultssets until you use dynmaic SQL
to display the columns.
http://www.sommarskog.se/dynamic_sql.html
Without dynamic SQL you could display the columns but fill them in
conditionally:
SELECT
OneTable.SomeColumn0
CASE OneTable.SomeColumn0 WHEN 'Y' THEN OtherTable.SomeColumn0 END...
But assuming that fileA is your parent table which attributes from
which row do you want to display ? If there are two rows returned for
one parent row, your query like the above one wouldn=B4t be reliable.
Perhaps it would be better to post your DDL and sample data that our
thoughts would=B4nt bw based on assumptions:
http://www.aspfaq.com/5006
HTH, jens Suessmeyer.sql

If Else question URGENT

I want to run a query that will insert rows into another table.

I also want to do some calculations on a couple of the columns:


SELECT
KEYCODESTRINGDESCRIPTION,
STRING,
Mailed,
Sales,
Orders,
CATALOGTITLE,
Response = Orders / Mailed,
[Average Invoice] = Sales / Orders,
SMP = (Sales / Mailed) * 1000

INTO TP_GA_REPORT
FROM TP_GA_REPORT_TEMP

I have a condition where some of the colums might have a 0 in them, which of course causes a "Divide by 0" error. What I would like to do is put an IF statement in the query to deal with this 0.

i.e.

if orders = 0 then response=0
else response = Response = Orders / Mailed

Hope this makes sense!

Thanks KenTry using SQL CASE:

SELECT ...,
CASE WHEN Mailed = 0 THEN 0 ELSE Orders / Mailed END,
...
FROM ...|||Thank you!!! So very much, worked like a charm!

if else

Hi all,

what is the best possible query to achieve something like this, if these are the columns.

S.no Name Join Date post Sal

1 Abraham 12-03-2007 E.O 2500 $

2 Abrham Null E.O 2500$

3 John Null Trainee 500$

the o/p

S.no Name Join Date post Sal

1 Abraham 12-03-2007 E.O 2500 $

3 John Null Trainee 500$

in short if there is a date value get the latest results, if no date is there get all possible distinct values ?

What you are seeking is not clear.

Easy to understand the part about returning the rows where there is a [Join Date] value.

Not sure what you desire for the non-qualifying rows. (Why is s.no 3 in your results instead of s.no 2?)

|||

This particular thing is what the fourth normal form is all about. If E.O in the different rows actually indicates the same thing, then you really ought to have multiple tables to indicate the Join Date and then the the post and the Salary.

Your schema makes no sense at all. What does a NULL join date mean? Can you explain what the meaning of the first two rows actually are?

I think you are asking if you can have a distinct operation that ignores NULL, and depending on what else your data can do, it might be possible, but can there be three rows? What if two of the rows have distinctly different values, but one has null? If that is the case, you will probably need a cursor to work with your schema....

|||

Sorry if the post wa not clear. Agreed that the schema is in total bad shape, but now we have a requirement wherein we have to select the record which is the latest, base don date and if there is no date then get the other details only.

say like if

A b (date) C

1 today a

1 yestrerday a

1 day bfore sday a

2 Null b

3 NUll b

I have to get the o/p like

1 today a

2 Null b

3 NUll b

Sorry if I had confused in the earlier post. Thanks for the replies...

|||

Would this work for you?

select a, max(b) as b, c
from #x
where b is not null
group by a, c
union
select a, b, c
from #x
where b is null

/Kenneth

Friday, March 23, 2012

identity, indexes, datetime

Hi,
We have an largish audit table with many columns. There are
inserts/deletes only on this table, no updates. Its never been indexed
or had a pk. Reports are run against this table, usually on date ranges
(datetime col) with joins to other tables on userid etc.
Recently an identity column was added, to improve performance. For start
I'm wondering would this do anything at all for performance? Presumably
the table is sorted by this field in the absence of any other pk/index?
If so what benefit is this if the column is never used?
Surely the best strategy would be to put a clustered index on the
datetime field and not bother with the identity?
Is there much difference in time overhead adding a clustered,
non-clustered or identity field to existing tables with many rows (this
database is used by many client and the time taken for the schema update
is a concern).
Thanks
JB.jb
Well, choosing clustered index may not be so easy , you will have to
investigate many things. So, choosing "right" column/s will speed up the
query/is and improve perfomance ...

> Is there much difference in time overhead adding a clustered,
> non-clustered or identity field to existing tables with many rows (this
> database is used by many client and the time taken for the schema update
> is a concern).
Take a look at CREATE INDEX... WITH DROP EXISTSING option in the BOL
"jb" <b@.b.com> wrote in message
news:OoI2jPCfHHA.4636@.TK2MSFTNGP03.phx.gbl...
> Hi,
> We have an largish audit table with many columns. There are
> inserts/deletes only on this table, no updates. Its never been indexed or
> had a pk. Reports are run against this table, usually on date ranges
> (datetime col) with joins to other tables on userid etc.
> Recently an identity column was added, to improve performance. For start
> I'm wondering would this do anything at all for performance? Presumably
> the table is sorted by this field in the absence of any other pk/index? If
> so what benefit is this if the column is never used?
> Surely the best strategy would be to put a clustered index on the datetime
> field and not bother with the identity?
> Is there much difference in time overhead adding a clustered,
> non-clustered or identity field to existing tables with many rows (this
> database is used by many client and the time taken for the schema update
> is a concern).
> Thanks
> JB.|||Sory, should be DROP_EXISTING
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O62QTeCfHHA.3956@.TK2MSFTNGP03.phx.gbl...
> jb
> Well, choosing clustered index may not be so easy , you will have to
> investigate many things. So, choosing "right" column/s will speed up the
> query/is and improve perfomance ...
>
>
> Take a look at CREATE INDEX... WITH DROP EXISTSING option in the BOL
>
>
>
> "jb" <b@.b.com> wrote in message
> news:OoI2jPCfHHA.4636@.TK2MSFTNGP03.phx.gbl...
>|||> Recently an identity column was added, to improve performance. For start I'm wondering wou
ld this
> do anything at all for performance?
No. Well, it will increase row size a little so it will decrease performance
slightly.

> Presumably the table is sorted by this field in the absence of any other pk/index?

Nope. Identity is a logical construct, and will have no bearing on physical
structure.

> If so what benefit is this if the column is never used?
None, from a performance standpoint. From a logical standpoint, we get into
the discussion of
identifying rows, natural vs. surrogate keys, but that is another topic.

> Surely the best strategy would be to put a clustered index on the datetime
field and not bother
> with the identity?
That seems like the natural thing to do. However, another candidate is to cl
uster over the join
column(s). Do some testing what will give most gain. Whever you cluster on,
you probably want to
have non-clustered index(es) on the other(s).

> Is there much difference in time overhead adding a clustered, non-clustere
d or identity field to
> existing tables with many rows (this database is used by many client and t
he time taken for the
> schema update is a concern).
Yes. Creating a cl ix will physicall re-sort the table, during you will have
exclusive lock (unless
you are on 2005 and create using ONLINE option). Creating nc index will not
re-sort data, only build
the index during which you have shared lock (unless using ONLINE). Adding an
identity column will
proably mean SQL Server has to touch every row, end result can be similar to
creating a cl ix
(sometimes, this can be deferred, but I very very much doubt adding an ident
ity column can be
deferred, considering the identity value need to be populated).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jb" <b@.b.com> wrote in message news:OoI2jPCfHHA.4636@.TK2MSFTNGP03.phx.gbl...">
> Hi,
> We have an largish audit table with many columns. There are inserts/delete
s only on this table, no
> updates. Its never been indexed or had a pk. Reports are run against this
table, usually on date
> ranges (datetime col) with joins to other tables on userid etc.
> Recently an identity column was added, to improve performance. For start I
'm wondering would this
> do anything at all for performance? Presumably the table is sorted by this
field in the absence of
> any other pk/index? If so what benefit is this if the column is never used
?
> Surely the best strategy would be to put a clustered index on the datetime
field and not bother
> with the identity?
> Is there much difference in time overhead adding a clustered, non-clustere
d or identity field to
> existing tables with many rows (this database is used by many client and t
he time taken for the
> schema update is a concern).
> Thanks
> JB.|||It is possible that adding an identity might make index rows on other
indexes smaller (if any other indexes exist). I know the OP stated there
were no other indexes on this particular table, but for argument's sake,
consider what happens when a heap table with NCIXs has an integer identity
column added. The ROWIDs in the NCIXs are reduced in size from 8bytes to
4bytes, reducing the storage size of those NCIXs. For NCIXs with only a
column or two, this can be a substantial reduction in overall size of the
index..
Is this important? Probably not terribly much, but I just thought it's worth
pointing out that although adding an identity column does widen the width of
table row storage, it can actually have the reverse effect on non-clustered
indexes. If performance critical queries rely on those NCIXs, it can
sometimes be a useful technique.. Where the identity is a BigInt however,
there's obviously no such gain..
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%2340HOgCfHHA.3932@.TK2MSFTNGP02.phx.gbl...
> No. Well, it will increase row size a little so it will decrease
> performance slightly.
>
> Nope. Identity is a logical construct, and will have no bearing on
> physical structure.
>
> None, from a performance standpoint. From a logical standpoint, we get
> into the discussion of identifying rows, natural vs. surrogate keys, but
> that is another topic.
>
> That seems like the natural thing to do. However, another candidate is to
> cluster over the join column(s). Do some testing what will give most gain.
> Whever you cluster on, you probably want to have non-clustered index(es)
> on the other(s).
>
> Yes. Creating a cl ix will physicall re-sort the table, during you will
> have exclusive lock (unless you are on 2005 and create using ONLINE
> option). Creating nc index will not re-sort data, only build the index
> during which you have shared lock (unless using ONLINE). Adding an
> identity column will proably mean SQL Server has to touch every row, end
> result can be similar to creating a cl ix (sometimes, this can be
> deferred, but I very very much doubt adding an identity column can be
> deferred, considering the identity value need to be populated).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "jb" <b@.b.com> wrote in message
> news:OoI2jPCfHHA.4636@.TK2MSFTNGP03.phx.gbl...
>|||>> Yes. Creating a cl ix will physicall re-sort the table, during you will[vbcol=seagreen]
Tested this on 2.2 million records:
Clustered [on datetime field] - 5 mins
Non-clustered - 30 secs
Adding identity - ~1minsql

Wednesday, March 21, 2012

identity, indexes, datetime

Hi,
We have an largish audit table with many columns. There are
inserts/deletes only on this table, no updates. Its never been indexed
or had a pk. Reports are run against this table, usually on date ranges
(datetime col) with joins to other tables on userid etc.
Recently an identity column was added, to improve performance. For start
I'm wondering would this do anything at all for performance? Presumably
the table is sorted by this field in the absence of any other pk/index?
If so what benefit is this if the column is never used?
Surely the best strategy would be to put a clustered index on the
datetime field and not bother with the identity?
Is there much difference in time overhead adding a clustered,
non-clustered or identity field to existing tables with many rows (this
database is used by many client and the time taken for the schema update
is a concern).
Thanks
JB.jb
Well, choosing clustered index may not be so easy , you will have to
investigate many things. So, choosing "right" column/s will speed up the
query/is and improve perfomance ...
> Is there much difference in time overhead adding a clustered,
> non-clustered or identity field to existing tables with many rows (this
> database is used by many client and the time taken for the schema update
> is a concern).
Take a look at CREATE INDEX... WITH DROP EXISTSING option in the BOL
"jb" <b@.b.com> wrote in message
news:OoI2jPCfHHA.4636@.TK2MSFTNGP03.phx.gbl...
> Hi,
> We have an largish audit table with many columns. There are
> inserts/deletes only on this table, no updates. Its never been indexed or
> had a pk. Reports are run against this table, usually on date ranges
> (datetime col) with joins to other tables on userid etc.
> Recently an identity column was added, to improve performance. For start
> I'm wondering would this do anything at all for performance? Presumably
> the table is sorted by this field in the absence of any other pk/index? If
> so what benefit is this if the column is never used?
> Surely the best strategy would be to put a clustered index on the datetime
> field and not bother with the identity?
> Is there much difference in time overhead adding a clustered,
> non-clustered or identity field to existing tables with many rows (this
> database is used by many client and the time taken for the schema update
> is a concern).
> Thanks
> JB.|||Sory, should be DROP_EXISTING
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O62QTeCfHHA.3956@.TK2MSFTNGP03.phx.gbl...
> jb
> Well, choosing clustered index may not be so easy , you will have to
> investigate many things. So, choosing "right" column/s will speed up the
> query/is and improve perfomance ...
>> Is there much difference in time overhead adding a clustered,
>> non-clustered or identity field to existing tables with many rows (this
>> database is used by many client and the time taken for the schema update
>> is a concern).
>
> Take a look at CREATE INDEX... WITH DROP EXISTSING option in the BOL
>
>
>
> "jb" <b@.b.com> wrote in message
> news:OoI2jPCfHHA.4636@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> We have an largish audit table with many columns. There are
>> inserts/deletes only on this table, no updates. Its never been indexed or
>> had a pk. Reports are run against this table, usually on date ranges
>> (datetime col) with joins to other tables on userid etc.
>> Recently an identity column was added, to improve performance. For start
>> I'm wondering would this do anything at all for performance? Presumably
>> the table is sorted by this field in the absence of any other pk/index?
>> If so what benefit is this if the column is never used?
>> Surely the best strategy would be to put a clustered index on the
>> datetime field and not bother with the identity?
>> Is there much difference in time overhead adding a clustered,
>> non-clustered or identity field to existing tables with many rows (this
>> database is used by many client and the time taken for the schema update
>> is a concern).
>> Thanks
>> JB.
>|||> Recently an identity column was added, to improve performance. For start I'm wondering would this
> do anything at all for performance?
No. Well, it will increase row size a little so it will decrease performance slightly.
> Presumably the table is sorted by this field in the absence of any other pk/index?
Nope. Identity is a logical construct, and will have no bearing on physical structure.
> If so what benefit is this if the column is never used?
None, from a performance standpoint. From a logical standpoint, we get into the discussion of
identifying rows, natural vs. surrogate keys, but that is another topic.
> Surely the best strategy would be to put a clustered index on the datetime field and not bother
> with the identity?
That seems like the natural thing to do. However, another candidate is to cluster over the join
column(s). Do some testing what will give most gain. Whever you cluster on, you probably want to
have non-clustered index(es) on the other(s).
> Is there much difference in time overhead adding a clustered, non-clustered or identity field to
> existing tables with many rows (this database is used by many client and the time taken for the
> schema update is a concern).
Yes. Creating a cl ix will physicall re-sort the table, during you will have exclusive lock (unless
you are on 2005 and create using ONLINE option). Creating nc index will not re-sort data, only build
the index during which you have shared lock (unless using ONLINE). Adding an identity column will
proably mean SQL Server has to touch every row, end result can be similar to creating a cl ix
(sometimes, this can be deferred, but I very very much doubt adding an identity column can be
deferred, considering the identity value need to be populated).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jb" <b@.b.com> wrote in message news:OoI2jPCfHHA.4636@.TK2MSFTNGP03.phx.gbl...
> Hi,
> We have an largish audit table with many columns. There are inserts/deletes only on this table, no
> updates. Its never been indexed or had a pk. Reports are run against this table, usually on date
> ranges (datetime col) with joins to other tables on userid etc.
> Recently an identity column was added, to improve performance. For start I'm wondering would this
> do anything at all for performance? Presumably the table is sorted by this field in the absence of
> any other pk/index? If so what benefit is this if the column is never used?
> Surely the best strategy would be to put a clustered index on the datetime field and not bother
> with the identity?
> Is there much difference in time overhead adding a clustered, non-clustered or identity field to
> existing tables with many rows (this database is used by many client and the time taken for the
> schema update is a concern).
> Thanks
> JB.|||It is possible that adding an identity might make index rows on other
indexes smaller (if any other indexes exist). I know the OP stated there
were no other indexes on this particular table, but for argument's sake,
consider what happens when a heap table with NCIXs has an integer identity
column added. The ROWIDs in the NCIXs are reduced in size from 8bytes to
4bytes, reducing the storage size of those NCIXs. For NCIXs with only a
column or two, this can be a substantial reduction in overall size of the
index..
Is this important? Probably not terribly much, but I just thought it's worth
pointing out that although adding an identity column does widen the width of
table row storage, it can actually have the reverse effect on non-clustered
indexes. If performance critical queries rely on those NCIXs, it can
sometimes be a useful technique.. Where the identity is a BigInt however,
there's obviously no such gain..
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%2340HOgCfHHA.3932@.TK2MSFTNGP02.phx.gbl...
>> Recently an identity column was added, to improve performance. For start
>> I'm wondering would this do anything at all for performance?
> No. Well, it will increase row size a little so it will decrease
> performance slightly.
>
>> Presumably the table is sorted by this field in the absence of any other
>> pk/index?
> Nope. Identity is a logical construct, and will have no bearing on
> physical structure.
>
>> If so what benefit is this if the column is never used?
> None, from a performance standpoint. From a logical standpoint, we get
> into the discussion of identifying rows, natural vs. surrogate keys, but
> that is another topic.
>
>> Surely the best strategy would be to put a clustered index on the
>> datetime field and not bother with the identity?
> That seems like the natural thing to do. However, another candidate is to
> cluster over the join column(s). Do some testing what will give most gain.
> Whever you cluster on, you probably want to have non-clustered index(es)
> on the other(s).
>
>> Is there much difference in time overhead adding a clustered,
>> non-clustered or identity field to existing tables with many rows (this
>> database is used by many client and the time taken for the schema update
>> is a concern).
> Yes. Creating a cl ix will physicall re-sort the table, during you will
> have exclusive lock (unless you are on 2005 and create using ONLINE
> option). Creating nc index will not re-sort data, only build the index
> during which you have shared lock (unless using ONLINE). Adding an
> identity column will proably mean SQL Server has to touch every row, end
> result can be similar to creating a cl ix (sometimes, this can be
> deferred, but I very very much doubt adding an identity column can be
> deferred, considering the identity value need to be populated).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "jb" <b@.b.com> wrote in message
> news:OoI2jPCfHHA.4636@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> We have an largish audit table with many columns. There are
>> inserts/deletes only on this table, no updates. Its never been indexed or
>> had a pk. Reports are run against this table, usually on date ranges
>> (datetime col) with joins to other tables on userid etc.
>> Recently an identity column was added, to improve performance. For start
>> I'm wondering would this do anything at all for performance? Presumably
>> the table is sorted by this field in the absence of any other pk/index?
>> If so what benefit is this if the column is never used?
>> Surely the best strategy would be to put a clustered index on the
>> datetime field and not bother with the identity?
>> Is there much difference in time overhead adding a clustered,
>> non-clustered or identity field to existing tables with many rows (this
>> database is used by many client and the time taken for the schema update
>> is a concern).
>> Thanks
>> JB.
>|||>> Yes. Creating a cl ix will physicall re-sort the table, during you will
>> have exclusive lock (unless you are on 2005 and create using ONLINE
>> option). Creating nc index will not re-sort data, only build the index
>> during which you have shared lock (unless using ONLINE). Adding an
>> identity column will proably mean SQL Server has to touch every row, end
>> result can be similar to creating a cl ix (sometimes, this can be
>> deferred, but I very very much doubt adding an identity column can be
>> deferred, considering the identity value need to be populated).
Tested this on 2.2 million records:
Clustered [on datetime field] - 5 mins
Non-clustered - 30 secs
Adding identity - ~1min

Monday, March 19, 2012

Identity Ranges

I'm using Merge replication on a database that was designed using integer identity columns for primary keys. When I create a publisher it's great because Sql Server will create rowguid columns for me on most of the tables; actually all but one table.

The problem comes when I try and use identity ranges for the subscribers. As a test I set up a range on one table, it only allowed for 10 in the range with an 80% threshold. I wanted to see what would happen when say my publisher db inserts 11 new rows. Well, I found out that after the 8th new row it wouldn't insert anymore as the range was exceeded and it gave me an error message saying
The identity range managed by replication is full and must be updated by a replication agent.

The question is; which agent needs to run in order for the new range to be assigned to the publisher? I have seen some people talk about an sp_ that can be run, but in a production environment I wont this to be automatic.

My altenative to ranged identies is using guid uniqueidentifiers. See my other post on this!!

regards

GrahamI assume this is SQL Server 2000.

To answer your questions:
Maybe the table on which merge is not adding the rowguid col is because the table already has a rowguid column?

And since you have 80% threashold, it is failing after the 8th row and I assume you have pub_idrange and range values each set to 10.
You can get by this situation by increasing the numbers, so that the probablity of them running out of numbers is less. Say like 10,000 or 100,000.

And the agent to be run when the id range is full is the merge agent. Running the merge agent will refresh the ranges on publisher and subscriber.
On the publisher, you can also run the sp_adjustpublisheridentityrange to refresh the range and that way you dont need to run the merge agent.

But typically in a production scenario, it is recommended that:
1. You have a decent sized value for the publisher and subscriber id range values
2. Merge agent to run frequently. That way the ranges will be refreshed (if needed) when merge agent runs.

Friday, March 9, 2012

Identity columns problem in replication among n no of subscribers

I have created Transaction Replication.
Nearly 100's of tables there in my database.
I have created Recordid (identity) column with primary key in each table and
all are incremented by 1.
Front End application in VB6 is 100% dependable on this column. Changes in
this column may spoil my work of VB6.
Synchronisation is done @. subscribers side using below activex component in
VB6
Set objSQLDist = CreateObject("SQLDistribution.SQLDistribution.2")
Subscribers side does not show identity columns with primary key. It shows
only name of the column as INT.
Front application is failed to run @. subscribers side.
Pls suggest how to Replicate identity column among n no. of subscribers.
Best Regards
Sanjay
You will have to create different identity seeds and an increments of 2 on
both sides - on the subscriber a seed of 2 with an increment of 2, and on
the publisher an seed of 1 with an increment of 1.
Make sure the identity column has the not for replication property on it.
Then set your articles up to not modify the existing tables on the
subscriber and in your pre-snapshot script have your table creation scripts
(along with their respective indexes).
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
news:OUx4ONMBHHA.4472@.TK2MSFTNGP03.phx.gbl...
>I have created Transaction Replication.
> Nearly 100's of tables there in my database.
> I have created Recordid (identity) column with primary key in each table
> and all are incremented by 1.
> Front End application in VB6 is 100% dependable on this column. Changes in
> this column may spoil my work of VB6.
> Synchronisation is done @. subscribers side using below activex component
> in VB6
> Set objSQLDist = CreateObject("SQLDistribution.SQLDistribution.2")
> Subscribers side does not show identity columns with primary key. It shows
> only name of the column as INT.
> Front application is failed to run @. subscribers side.
> Pls suggest how to Replicate identity column among n no. of subscribers.
> Best Regards
> Sanjay
>
>
>
>
>
|||Dear Friend Hilary
Thanks for the help.
But one more solution can be there if I give different IDENTITY ranges to
each subscribers.
Pls help me If you know how to give diff IDENTITY ranges to each
subscriber.
Thanks in Advance
Best Regards
Sanjay
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ut$xlYMBHHA.4292@.TK2MSFTNGP02.phx.gbl...
> You will have to create different identity seeds and an increments of 2 on
> both sides - on the subscriber a seed of 2 with an increment of 2, and on
> the publisher an seed of 1 with an increment of 1.
> Make sure the identity column has the not for replication property on it.
> Then set your articles up to not modify the existing tables on the
> subscriber and in your pre-snapshot script have your table creation
> scripts (along with their respective indexes).
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
> news:OUx4ONMBHHA.4472@.TK2MSFTNGP03.phx.gbl...
>
|||You certainly can, so you can go to your subscriber and do a
dbcc('mytablename', checkident, reseed, 10000000) and it will probably work.
However you will always have to monitor the range you assigned and adjust it
as necessary.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
news:%23e4pfjMBHHA.1220@.TK2MSFTNGP04.phx.gbl...
> Dear Friend Hilary
> Thanks for the help.
> But one more solution can be there if I give different IDENTITY ranges to
> each subscribers.
> Pls help me If you know how to give diff IDENTITY ranges to each
> subscriber.
> Thanks in Advance
> Best Regards
> Sanjay
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ut$xlYMBHHA.4292@.TK2MSFTNGP02.phx.gbl...
>
|||Why do you need the identity property on the subscribers if you have
transactional replication, where the subscribers are treated as RO?
Perhaps this is being used as a failover server? If this is the case, the
easiest way to set it up is to enable automatic identity range management,
large range sizes and queued updating subscribers.
This way the subscriber can start entering data once the publisher is down
without any meddling with identity ranges on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Dear friend Hilary
Thanks for your full co-operation. My all doubts are clear now except below
2.
1. If I am having file called EMP with identity column Recid.
I want to give automatic range control on publisher for n no. subscribers
what will be the command ?
2. If I give range 1001 to 2000 to subscriber1, will he (subscriber1) be
able to store or pull others data range from 1 to 1000 or 2001 to 3000.
Best Regards
Sanjay
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u5x4t7MBHHA.4428@.TK2MSFTNGP04.phx.gbl...
> You certainly can, so you can go to your subscriber and do a
> dbcc('mytablename', checkident, reseed, 10000000) and it will probably
> work. However you will always have to monitor the range you assigned and
> adjust it as necessary.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
> news:%23e4pfjMBHHA.1220@.TK2MSFTNGP04.phx.gbl...
>
|||For automatic identity range management on the publisher for queued and
merge replication you have to drop your publication and subscriptions and
enable this feature - in the articles tab of the create publication wizard.
A subscriber with a range of 1000-2000 will be able to receive replicated
commands for other ranges if the not for replication constraint is enabled.
Otherwise he will not.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
news:%239Zlc%23yBHHA.3380@.TK2MSFTNGP04.phx.gbl...
> Dear friend Hilary
> Thanks for your full co-operation. My all doubts are clear now except
> below 2.
> 1. If I am having file called EMP with identity column Recid.
> I want to give automatic range control on publisher for n no. subscribers
> what will be the command ?
> 2. If I give range 1001 to 2000 to subscriber1, will he (subscriber1) be
> able to store or pull others data range from 1 to 1000 or 2001 to 3000.
> Best Regards
> Sanjay
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u5x4t7MBHHA.4428@.TK2MSFTNGP04.phx.gbl...
>

Identity Columns on replicated data

CREATE TABLE SALES
(sale_id INT IDENTITY (1,1),
sale_region CHAR(2) )
When replicated, the IDENTITY property is lost. If the Publisher crashed and
I wanted to restore the replicated data off the subscriber, I could manually
edit the table and turn on the IDENTITY property under Enterprise Manager. Is
there another way? I can not find a way to do this with the ALTER TABLE
statement. TIA
The best way is to use a custom script in sp_addarticle to put the identity
property with the NFR clause on the subscriber.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:95BE6E91-BDD0-4893-9FB8-47F6F92017B9@.microsoft.com...
> CREATE TABLE SALES
> (sale_id INT IDENTITY (1,1),
> sale_region CHAR(2) )
> When replicated, the IDENTITY property is lost. If the Publisher crashed
> and
> I wanted to restore the replicated data off the subscriber, I could
> manually
> edit the table and turn on the IDENTITY property under Enterprise Manager.
> Is
> there another way? I can not find a way to do this with the ALTER TABLE
> statement. TIA

Identity columns no longer identities

Hi,
I have an entire db that for no reason I can see has had every identity
column's identity property set to 'no'. I have lots of inserts into the
affected tables which obviously produces errors inserting null into a column
that can't be null.
I could let 1 or 2 tables go as a wayward click in ent manager but *every*
identity in the db has been turned off. Is there a db wide setting that can
do this? Has anyone ever seen anthying like this before?
The scarey thing is this box goes into production next week and now Im
living in fear of the same thing happening again.
Any help appreciated.
Al"badlydressedboy" <badlydressedboy@.discussions.microsoft.com> wrote in
message news:D9C6489B-70E2-42A7-B643-DDA55BD74077@.microsoft.com...
> Hi,
> I have an entire db that for no reason I can see has had every identity
> column's identity property set to 'no'. I have lots of inserts into the
> affected tables which obviously produces errors inserting null into a
> column
> that can't be null.
> I could let 1 or 2 tables go as a wayward click in ent manager but *every*
> identity in the db has been turned off. Is there a db wide setting that
> can
> do this? Has anyone ever seen anthying like this before?
> The scarey thing is this box goes into production next week and now Im
> living in fear of the same thing happening again.
> Any help appreciated.
> Al
Umm, unless someone specifically scripted it, the only real way I can see
that happening is if this database was created as a replication subscriber.
In cases like that, default behavior is for the replicated tables to be
created w/o IDENTITY columns.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||On Mar 23, 12:06 pm, badlydressedboy
<badlydressed...@.discussions.microsoft.com> wrote:
> Hi,
> I have an entire db that for no reason I can see has had every identity
> column's identity property set to 'no'. I have lots of inserts into the
> affected tables which obviously produces errors inserting null into a column
> that can't be null.
> I could let 1 or 2 tables go as a wayward click in ent manager but *every*
> identity in the db has been turned off. Is there a db wide setting that can
> do this? Has anyone ever seen anthying like this before?
> The scarey thing is this box goes into production next week and now Im
> living in fear of the same thing happening again.
> Any help appreciated.
> Al
Was this DB "copied" from another DB by doing "SELECT * INTO" '|||I've never heard about this before and there's no database wise setting. In fact, you cannot remove
the identity property from a column. Most likely, someone used a script - export - import tool (like
DTS or SSIS) to move the data into a new database and missed configuring the tool to bring over the
identity attribute.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"badlydressedboy" <badlydressedboy@.discussions.microsoft.com> wrote in message
news:D9C6489B-70E2-42A7-B643-DDA55BD74077@.microsoft.com...
> Hi,
> I have an entire db that for no reason I can see has had every identity
> column's identity property set to 'no'. I have lots of inserts into the
> affected tables which obviously produces errors inserting null into a column
> that can't be null.
> I could let 1 or 2 tables go as a wayward click in ent manager but *every*
> identity in the db has been turned off. Is there a db wide setting that can
> do this? Has anyone ever seen anthying like this before?
> The scarey thing is this box goes into production next week and now Im
> living in fear of the same thing happening again.
> Any help appreciated.
> Al|||"Tracy McKibben" wrote:
> On Mar 23, 12:06 pm, badlydressedboy
> <badlydressed...@.discussions.microsoft.com> wrote:
> > Hi,
> >
> > I have an entire db that for no reason I can see has had every identity
> > column's identity property set to 'no'. I have lots of inserts into the
> > affected tables which obviously produces errors inserting null into a column
> > that can't be null.
> > I could let 1 or 2 tables go as a wayward click in ent manager but *every*
> > identity in the db has been turned off. Is there a db wide setting that can
> > do this? Has anyone ever seen anthying like this before?
> > The scarey thing is this box goes into production next week and now Im
> > living in fear of the same thing happening again.
> >
> > Any help appreciated.
> >
> > Al
> Was this DB "copied" from another DB by doing "SELECT * INTO" '
>
The crazy thing is that db creation was fine - its been up and running using
all the affected tables for a couple of weeks. We ran a bunch of data in (its
an ETL/OLAP db) in the morning and then went to do the same in the afternoon
but the identities had left the building.
There was an event that deleted them as they were definately there after db
creation otherwise the ETL wouldn't have worked at all. Its looking like that
event was somebody accidentally running a script (quite a bizarre script that
I am sure they would remember writing/running) but nobody is owning up.
Pretty weird stuff.|||On Mar 23, 12:45 pm, badlydressedboy
<badlydressed...@.discussions.microsoft.com> wrote:
> "Tracy McKibben" wrote:
> > On Mar 23, 12:06 pm, badlydressedboy
> > <badlydressed...@.discussions.microsoft.com> wrote:
> > > Hi,
> > > I have an entire db that for no reason I can see has had every identity
> > > column's identity property set to 'no'. I have lots of inserts into the
> > > affected tables which obviously produces errors inserting null into a column
> > > that can't be null.
> > > I could let 1 or 2 tables go as a wayward click in ent manager but *every*
> > > identity in the db has been turned off. Is there a db wide setting that can
> > > do this? Has anyone ever seen anthying like this before?
> > > The scarey thing is this box goes into production next week and now Im
> > > living in fear of the same thing happening again.
> > > Any help appreciated.
> > > Al
> > Was this DB "copied" from another DB by doing "SELECT * INTO" '
> The crazy thing is that db creation was fine - its been up and running using
> all the affected tables for a couple of weeks. We ran a bunch of data in (its
> an ETL/OLAP db) in the morning and then went to do the same in the afternoon
> but the identities had left the building.
> There was an event that deleted them as they were definately there after db
> creation otherwise the ETL wouldn't have worked at all. Its looking like that
> event was somebody accidentally running a script (quite a bizarre script that
> I am sure they would remember writing/running) but nobody is owning up.
> Pretty weird stuff.
SELECT INTO has nothing to do with DB creation, but it could have
everything to do with the creation of the TABLES. Does this ETL
process use SELECT INTO to build/populate the tables? Consider the
following example:
CREATE TABLE MyTable
(
IDColumn INT IDENTITY(1,1),
SomeVal1 CHAR(10),
SomeVal2 CHAR(10)
)
INSERT INTO MyTable (SomeVal1, SomeVal2) VALUES ('A', 'B')
SELECT * INTO MyNewTable FROM MyTable
SELECT * FROM MyNewTable
The structure of MyNewTable will appear to be the same as MyTable, but
it's NOT. The identity property of the IDColumn column doesn't get
copied.
If your ETL process involves the dropping of tables and then re-
creating them using SELECT INTO, that's your problem.

Identity columns no longer identities

Hi,
I have an entire db that for no reason I can see has had every identity
column's identity property set to 'no'. I have lots of inserts into the
affected tables which obviously produces errors inserting null into a column
that can't be null.
I could let 1 or 2 tables go as a wayward click in ent manager but *every*
identity in the db has been turned off. Is there a db wide setting that can
do this? Has anyone ever seen anthying like this before?
The scarey thing is this box goes into production next week and now Im
living in fear of the same thing happening again.
Any help appreciated.
Al
"badlydressedboy" <badlydressedboy@.discussions.microsoft.com> wrote in
message news:D9C6489B-70E2-42A7-B643-DDA55BD74077@.microsoft.com...
> Hi,
> I have an entire db that for no reason I can see has had every identity
> column's identity property set to 'no'. I have lots of inserts into the
> affected tables which obviously produces errors inserting null into a
> column
> that can't be null.
> I could let 1 or 2 tables go as a wayward click in ent manager but *every*
> identity in the db has been turned off. Is there a db wide setting that
> can
> do this? Has anyone ever seen anthying like this before?
> The scarey thing is this box goes into production next week and now Im
> living in fear of the same thing happening again.
> Any help appreciated.
> Al
Umm, unless someone specifically scripted it, the only real way I can see
that happening is if this database was created as a replication subscriber.
In cases like that, default behavior is for the replicated tables to be
created w/o IDENTITY columns.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
|||On Mar 23, 12:06 pm, badlydressedboy
<badlydressed...@.discussions.microsoft.com> wrote:
> Hi,
> I have an entire db that for no reason I can see has had every identity
> column's identity property set to 'no'. I have lots of inserts into the
> affected tables which obviously produces errors inserting null into a column
> that can't be null.
> I could let 1 or 2 tables go as a wayward click in ent manager but *every*
> identity in the db has been turned off. Is there a db wide setting that can
> do this? Has anyone ever seen anthying like this before?
> The scarey thing is this box goes into production next week and now Im
> living in fear of the same thing happening again.
> Any help appreciated.
> Al
Was this DB "copied" from another DB by doing "SELECT * INTO" ?
|||"Tracy McKibben" wrote:

> On Mar 23, 12:06 pm, badlydressedboy
> <badlydressed...@.discussions.microsoft.com> wrote:
> Was this DB "copied" from another DB by doing "SELECT * INTO" ?
>
The crazy thing is that db creation was fine - its been up and running using
all the affected tables for a couple of weeks. We ran a bunch of data in (its
an ETL/OLAP db) in the morning and then went to do the same in the afternoon
but the identities had left the building.
There was an event that deleted them as they were definately there after db
creation otherwise the ETL wouldn't have worked at all. Its looking like that
event was somebody accidentally running a script (quite a bizarre script that
I am sure they would remember writing/running) but nobody is owning up.
Pretty weird stuff.
|||On Mar 23, 12:45 pm, badlydressedboy
<badlydressed...@.discussions.microsoft.com> wrote:
> "Tracy McKibben" wrote:
>
>
> The crazy thing is that db creation was fine - its been up and running using
> all the affected tables for a couple of weeks. We ran a bunch of data in (its
> an ETL/OLAP db) in the morning and then went to do the same in the afternoon
> but the identities had left the building.
> There was an event that deleted them as they were definately there after db
> creation otherwise the ETL wouldn't have worked at all. Its looking like that
> event was somebody accidentally running a script (quite a bizarre script that
> I am sure they would remember writing/running) but nobody is owning up.
> Pretty weird stuff.
SELECT INTO has nothing to do with DB creation, but it could have
everything to do with the creation of the TABLES. Does this ETL
process use SELECT INTO to build/populate the tables? Consider the
following example:
CREATE TABLE MyTable
(
IDColumn INT IDENTITY(1,1),
SomeVal1 CHAR(10),
SomeVal2 CHAR(10)
)
INSERT INTO MyTable (SomeVal1, SomeVal2) VALUES ('A', 'B')
SELECT * INTO MyNewTable FROM MyTable
SELECT * FROM MyNewTable
The structure of MyNewTable will appear to be the same as MyTable, but
it's NOT. The identity property of the IDColumn column doesn't get
copied.
If your ETL process involves the dropping of tables and then re-
creating them using SELECT INTO, that's your problem.

Identity columns no longer identities

Hi,
I have an entire db that for no reason I can see has had every identity
column's identity property set to 'no'. I have lots of inserts into the
affected tables which obviously produces errors inserting null into a column
that can't be null.
I could let 1 or 2 tables go as a wayward click in ent manager but *every*
identity in the db has been turned off. Is there a db wide setting that can
do this? Has anyone ever seen anthying like this before?
The scarey thing is this box goes into production next week and now Im
living in fear of the same thing happening again.
Any help appreciated.
Al"badlydressedboy" <badlydressedboy@.discussions.microsoft.com> wrote in
message news:D9C6489B-70E2-42A7-B643-DDA55BD74077@.microsoft.com...
> Hi,
> I have an entire db that for no reason I can see has had every identity
> column's identity property set to 'no'. I have lots of inserts into the
> affected tables which obviously produces errors inserting null into a
> column
> that can't be null.
> I could let 1 or 2 tables go as a wayward click in ent manager but *every*
> identity in the db has been turned off. Is there a db wide setting that
> can
> do this? Has anyone ever seen anthying like this before?
> The scarey thing is this box goes into production next week and now Im
> living in fear of the same thing happening again.
> Any help appreciated.
> Al
Umm, unless someone specifically scripted it, the only real way I can see
that happening is if this database was created as a replication subscriber.
In cases like that, default behavior is for the replicated tables to be
created w/o IDENTITY columns.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||I've never heard about this before and there's no database wise setting. In
fact, you cannot remove
the identity property from a column. Most likely, someone used a script - ex
port - import tool (like
DTS or SSIS) to move the data into a new database and missed configuring the
tool to bring over the
identity attribute.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"badlydressedboy" <badlydressedboy@.discussions.microsoft.com> wrote in messa
ge
news:D9C6489B-70E2-42A7-B643-DDA55BD74077@.microsoft.com...
> Hi,
> I have an entire db that for no reason I can see has had every identity
> column's identity property set to 'no'. I have lots of inserts into the
> affected tables which obviously produces errors inserting null into a colu
mn
> that can't be null.
> I could let 1 or 2 tables go as a wayward click in ent manager but *every*
> identity in the db has been turned off. Is there a db wide setting that ca
n
> do this? Has anyone ever seen anthying like this before?
> The scarey thing is this box goes into production next week and now Im
> living in fear of the same thing happening again.
> Any help appreciated.
> Al|||On Mar 23, 12:06 pm, badlydressedboy
<badlydressed...@.discussions.microsoft.com> wrote:
> Hi,
> I have an entire db that for no reason I can see has had every identity
> column's identity property set to 'no'. I have lots of inserts into the
> affected tables which obviously produces errors inserting null into a colu
mn
> that can't be null.
> I could let 1 or 2 tables go as a wayward click in ent manager but *every*
> identity in the db has been turned off. Is there a db wide setting that ca
n
> do this? Has anyone ever seen anthying like this before?
> The scarey thing is this box goes into production next week and now Im
> living in fear of the same thing happening again.
> Any help appreciated.
> Al
Was this DB "copied" from another DB by doing "SELECT * INTO" '|||"Tracy McKibben" wrote:

> On Mar 23, 12:06 pm, badlydressedboy
> <badlydressed...@.discussions.microsoft.com> wrote:
> Was this DB "copied" from another DB by doing "SELECT * INTO" '
>
The crazy thing is that db creation was fine - its been up and running using
all the affected tables for a couple of weeks. We ran a bunch of data in (it
s
an ETL/OLAP db) in the morning and then went to do the same in the afternoon
but the identities had left the building.
There was an event that deleted them as they were definately there after db
creation otherwise the ETL wouldn't have worked at all. Its looking like tha
t
event was somebody accidentally running a script (quite a bizarre script tha
t
I am sure they would remember writing/running) but nobody is owning up.
Pretty weird stuff.|||On Mar 23, 12:45 pm, badlydressedboy
<badlydressed...@.discussions.microsoft.com> wrote:
> "Tracy McKibben" wrote:
>
>
>
>
> The crazy thing is that db creation was fine - its been up and running usi
ng
> all the affected tables for a couple of weeks. We ran a bunch of data in (
its
> an ETL/OLAP db) in the morning and then went to do the same in the afterno
on
> but the identities had left the building.
> There was an event that deleted them as they were definately there after d
b
> creation otherwise the ETL wouldn't have worked at all. Its looking like t
hat
> event was somebody accidentally running a script (quite a bizarre script t
hat
> I am sure they would remember writing/running) but nobody is owning up.
> Pretty weird stuff.
SELECT INTO has nothing to do with DB creation, but it could have
everything to do with the creation of the TABLES. Does this ETL
process use SELECT INTO to build/populate the tables? Consider the
following example:
CREATE TABLE MyTable
(
IDColumn INT IDENTITY(1,1),
SomeVal1 CHAR(10),
SomeVal2 CHAR(10)
)
INSERT INTO MyTable (SomeVal1, SomeVal2) VALUES ('A', 'B')
SELECT * INTO MyNewTable FROM MyTable
SELECT * FROM MyNewTable
The structure of MyNewTable will appear to be the same as MyTable, but
it's NOT. The identity property of the IDColumn column doesn't get
copied.
If your ETL process involves the dropping of tables and then re-
creating them using SELECT INTO, that's your problem.

identity columns and XML columns - OK with mirroring ?

Just to confirm, do identity columns and XML columns work OK with database mirroring ? That is, all data types are supported with mirroring, and identities aren't an issue ?

Transactional replication with identity columns was a right pain in the **** in SQL 2000. I'm assuming that mirroring doesn't have these issues, but want to be sure.

Hi Andrew. Yes, mirroring supports and works seamlessly with identity columns and XML columns, as well as many other built-in database functionality in SQL 2005 (full text search for example, et al.). Mirroring is really more similar to real-time log shipping than replication, they use totally different technologies and methods for duplicating data.

HTH,

Wednesday, March 7, 2012

Identity columns and instead-of triggers

I am sure this has been dealt with many times by others, so I'm hoping
someone can provide a clue. Lets say I have a table (SQL 2000) with an
identity column. Then I create a view with an instead of trigger. Now I
try to insert into the view without specifying a value for the ID column.
This works fine if inserting into the table directly, but not inserting into
the view.
Thanks very much for any suggestions.
Neil
ps, if replying by email remove REMOVEYou have to be explicit.
e.g.
create table t(i int identity,j int)
go
create view _v
with schemabinding
as
select i,j
from dbo.t
go
create trigger _tr on t
instead of insert
as
insert t
select j from inserted
go
insert t(j) values(1)
go
insert _v(j) values(2)
go
select * from t
go
drop view _v
drop table t
go
-oj
"Neil W" <neilw@.REMOVEnetlib.com> wrote in message
news:XoA9e.15715$ZQ1.11651@.fe11.lga...
>I am sure this has been dealt with many times by others, so I'm hoping
> someone can provide a clue. Lets say I have a table (SQL 2000) with an
> identity column. Then I create a view with an instead of trigger. Now I
> try to insert into the view without specifying a value for the ID column.
> This works fine if inserting into the table directly, but not inserting
> into
> the view.
> Thanks very much for any suggestions.
> Neil
> ps, if replying by email remove REMOVE
>
>

Identity columns and date columns on transactional replication

Hi,

I am planning to use transacational replication (instead of merge replication) on my SQL server 2000. My application is already live and is being used by real users.

How can I ensure that replicated data on different server would have exact same values of identity columns and date columns (where every I set default date to getdate())?

It is very important for me to have a mirror image of data (without using clustering servers).

Any help would be appreciated.

Thanks,

-Niraj

By default, the data should be replicated as it is, regardless the default values. You can look at the article properties to see your options, you also have option to not replicate identity value if you don't want to.

Identity columns after failover

Hi,
I was doing some testing of Identity columns with 'Not For Replication'
and failovers. I wanted to test Identity column value ranges. Here is some
back ground info. Both the Publisher and Subscriber have Identity column with
the 'Not For Replication' clause. Both have the same (default) value range
(seed) and increment of 1. On the Publisher, I can insert or add new rows of
data with the new Identity column value being replicated to the standby
successfully. When I break replication and make my subscriber the primary
(and only) server, the first time I insert a new row I get the 'duplicate
key' error. I get this regardless of what tool or application I use. It also
does seem to make a difference if I insert the new row immediately after the
failover or 20 minutes later. However, on the second and all other attempts
to insert a new row it works with no errors what so ever. This is true
regardless if I've set up replication to replicate back to the original
publisher (now the subscriber) or not.
So, my question is, why is this happening? What am I over looking on
failover that gives me the duplicate key error?
Thanks for your replies.
JD
JD,
you'll either need to enable automatic range management with an appropriate
seed, or manually reseed the subscriber. Replication is doing the identity
insert for you, but if you use dbcc checkident you'll see that the seed
value isn't changing, so the first value added after failover has the value
of 1 which was already taken.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thank you for the answer.
Joe
"Paul Ibison" wrote:

> JD,
> you'll either need to enable automatic range management with an appropriate
> seed, or manually reseed the subscriber. Replication is doing the identity
> insert for you, but if you use dbcc checkident you'll see that the seed
> value isn't changing, so the first value added after failover has the value
> of 1 which was already taken.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||One more quick question Paul,
Besides the DBCC CHECKIDENT to check and reseed the column, is there a
Stored Procedure that can be used? I heard there was but I haven't found any
supporting documentation.
Thanks again,
JD
"JD" wrote:
[vbcol=seagreen]
> Paul,
> Thank you for the answer.
> Joe
> "Paul Ibison" wrote:
|||To reseed this is the only way. There are things like truncate table
which'll reseed as a side effect, but practically dbcc checkident is the
only way.
Rgds,
Paul Ibison
"JD" <John316@.online.nospam> wrote in message
news:1134F064-2FD4-4DBF-BD18-20ABD6830CAB@.microsoft.com...[vbcol=seagreen]
> One more quick question Paul,
> Besides the DBCC CHECKIDENT to check and reseed the column, is there a
> Stored Procedure that can be used? I heard there was but I haven't found
> any
> supporting documentation.
> Thanks again,
> JD
> "JD" wrote:
|||Once again, Thank you Paul.
"Paul Ibison" wrote:

> To reseed this is the only way. There are things like truncate table
> which'll reseed as a side effect, but practically dbcc checkident is the
> only way.
> Rgds,
> Paul Ibison
>
> "JD" <John316@.online.nospam> wrote in message
> news:1134F064-2FD4-4DBF-BD18-20ABD6830CAB@.microsoft.com...
>
>

Identity Columns - Design Question


Is it a good practice to use identty columns as primary keys? or should
we generate our own keys using a seed table?
Does using Identity give any performance advantages?
Thank you,Hi

> Is it a good practice to use identty columns as primary keys? or should
> we generate our own keys using a seed table?
If you don't care about gaps you can use an IDENTITY propertry as a
PFRIMARY KEY , just thinking about your business requirements which I don't
know , actuallty there was lots of discussions about this in this newsgpoup

> Does using Identity give any performance advantages?
Yes, it is , especially if it has a CLUSTERED INDEX .
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1149591940.115554.74460@.y43g2000cwc.googlegroups.com...
>
> Is it a good practice to use identty columns as primary keys? or should
> we generate our own keys using a seed table?
> Does using Identity give any performance advantages?
> Thank you,
>|||There is a slight performance gain on large tables.
But I don't use them anymore due to re-design issues on upgrades and third
party ware and a number of other integrations.
"Uri Dimant" wrote:

> Hi
>
> If you don't care about gaps you can use an IDENTITY propertry as a
> PFRIMARY KEY , just thinking about your business requirements which I don
't
> know , actuallty there was lots of discussions about this in this newsgpo
up
>
>
> Yes, it is , especially if it has a CLUSTERED INDEX .
>
>
> "S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
> news:1149591940.115554.74460@.y43g2000cwc.googlegroups.com...
>
>|||This is an altered version of an older response on the same subject, with
emphasis on fundamentals:
A key is an attribute or set of attributes that can uniquely identify an
entity in the conceptual model. At the corresponding logical level, a column
or set of columns that can uniquely identify a row in a table is defined as
a key. In reality, an entity may have more than one candidate that provides
such identification ( hence are called candidate keys ), however due to
obvious reasons one of those candidate keys should be treated as primal (
hence called primary key ) based on practical considerations. Such
considerations should include:
i. Familiarity - meaningful to the user.
ii. Stability - non-volatile, should not be altered frequently.
iii. Simplicity - so that queries are easy to express and optimize.
iv. Irreducibility - no proper subset of the key should uniquely identify a
row in the table. ( some treat this also as a derivation to 2NF, which
prohibits partial key dependencies )
Having stated the above guidelines, regardless of the complexity in your
business model & data requirements, an intelligent database design can find
a middle ground by trading off certain characteristics without compromising
the integrity of the data. And one such compromise is the logical surrogate
key which usually defies the characteristic of familiarity but provides
excellent stability and simplicity. Simple and stable keys are often
recommended over composite/volatile keys esp. when they are used in
referential integrity constraints.
http://www.datraverse.com/technology/sql.php
One of the fundamental principles guiding relational design, is a concept
called physical data independence, which can be simply put as: the users
must be presented with a logical view of the data & need not deal with the
physical implementation and storage details. That is primarily why we deal
with tables, columns, keys etc ( which is logical) instead of files, disk
indices, row positions etc (which is physical). Thus you will see why and
how a surrogate in a clean logical model, should also conform to physical
data independence.
Features like identity, GUID etc provide uniqueness, but there are questions
regarding whether they provide sufficient data independence esp. with
ordering of identity column values at the time of generation. Some aspects
of this issue were addressed here:
http://blogs.msdn.com/sqltips/default.aspx?p=2
The non-updateability of identity columns arguably invalidates relational
assignments and therefore the information principle. Since the internal
mechanism that generates identity values are mostly unknown to the user,
practical data design and quality guidelines involving data verification and
validation cannot be applied to such DBMS generated values either.
At the logical level, performance should be least of your considerations in
key selection, since the primary goal of keys is entity identification that
preserves data integrity. Performance is purely dependent upon the physical
implementation of a database. Obviously values generated and tied closely
and directly to the physical model tend to be better performing. Also values
that are smaller in physical size can improve certain query performance due
to faster disk access i/o etc. Given that identity values are of numeric
datatypes and often smaller in physical size, queries utilizing identity
columns tend to have the same performance benefits.
Anith|||I have read ("i have never tried it out myself") -- Identity values cannot
be used in merge replication.
Best Regards
Vadivel
http://vadivel.blogspot.com
"S Chapman" wrote:

>
> Is it a good practice to use identty columns as primary keys? or should
> we generate our own keys using a seed table?
> Does using Identity give any performance advantages?
> Thank you,
>|||If you have no natural primary key then using the IDENTITY property is
significantly better than roling your own because you won't get the same
contention (blocking and serialisation) you get with other roled methods.
IDENTITY does give a performance advantage - there is no locking to get the
next number.
The one divantage is that you can get gaps because the calculation for
next number is not transaction aware but that doesn't matter in most cases
i've seen in business.
You really need to PRIMARY KEY using your natural key and then use a
surrogate key (the IDENTIITY) everywhere else - in sps, joins etc... the
primary key is then just meta data.
eg.
create table sector (
sector_name nvarchar(100) not null constraint pk_sector primary
key nonclustered,
id int not null constraint sk_sector unique clustered
)
create table sector_coverage (
sector_id int not null references sector( id ),
individual_id int not null references individual( id )
)
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1149591940.115554.74460@.y43g2000cwc.googlegroups.com...
>
> Is it a good practice to use identty columns as primary keys? or should
> we generate our own keys using a seed table?
> Does using Identity give any performance advantages?
> Thank you,
>|||Agreed Tony
"Tony Rogerson" wrote:

> If you have no natural primary key then using the IDENTITY property is
> significantly better than roling your own because you won't get the same
> contention (blocking and serialisation) you get with other roled methods.
> IDENTITY does give a performance advantage - there is no locking to get th
e
> next number.
> The one divantage is that you can get gaps because the calculation for
> next number is not transaction aware but that doesn't matter in most cases
> i've seen in business.
> You really need to PRIMARY KEY using your natural key and then use a
> surrogate key (the IDENTIITY) everywhere else - in sps, joins etc... the
> primary key is then just meta data.
> eg.
> create table sector (
> sector_name nvarchar(100) not null constraint pk_sector primary
> key nonclustered,
> id int not null constraint sk_sector unique clustered
> )
> create table sector_coverage (
> sector_id int not null references sector( id ),
> individual_id int not null references individual( id )
> )
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a S
QL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
> news:1149591940.115554.74460@.y43g2000cwc.googlegroups.com...
>
>

Identity columns

Can anyone describe how SQLServer calculates identitycolumns? Does it use some internal counter when generating the next identity,or something a little more mundane such as gets the highest existing identityvalue at the point of the insertion and increments it by the IDENT_INCR valueof the identity column?

I'm not worrying about reliability or gaps in values, but iam wondering if it would be less efficient for me to manually manage theidentity/primary key in the form of a counter in another table used to generatethe new identity, or simply let the DB do it for me. I dont mind if there aregaps in the sequence etc. so would it be less efficient for me to calculate thefield than SQLServer itself? Basically, is the overhead to the DB of me doingit greater than the overhead of the app doing it...

Thanks

Here's how the values are calculated:

http://blogs.msdn.com/sqlprogrammability/archive/2006/04/04/567724.aspx

It would take MUCH MORE time for you to do it than letting SQL Server manage it for you. By several orders of magnitude.

|||Thanks for that. how are the 'generated' in memory though without accessing the table?

Identity columns

Hello there,

I am using sql 2000 dts package to migrate a databse to SQL Server 2005, everything works except for identity columns, SQL Server reorder the columns, and this breaks the referential integrity. Is there a way to stop that? Your help is appreciated.

Hi,

hard to tell why this happens, becasue you didn′t provide any information about the settings of your dts package. One thing would to not migrate the identity columns and

-treat them like integer columns / turn on the identity property later (With creating a new column, because there is no "identity" property) OR
-Allow identity insert on those columns and include them in the import of the tables.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de