Database

ps_prakash024-Aug-09 17:44

4-Aug-09 17:44

Hi,

I could not understand what you have said.
Could me please explain me deeply.

Thanks & Regards,
P.Prakash

ps_prakash024-Aug-09 17:39

4-Aug-09 17:39

Hi,

thanks for your answer,
we are using sql server 2005 i'll look for those two options you have specified.

regards,
P.Prakash

ps_prakash025-Aug-09 2:09

5-Aug-09 2:09

Hi David Skelly,

for this problem can we use table partition method. will it help me to solve the problem.

Thanks & Regards,
Prakash

Blue_Boy3-Aug-09 23:24

Blue_Boy

3-Aug-09 23:24

Try this

select master.masterdesc <br />
from master ,linecontent<br />
where master.masterdesc = linecontent.content<br />

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

www.aktualiteti.com

ps_prakash024-Aug-09 0:28

4-Aug-09 0:28

Hi,

Thanks for your reply.

i tried for your solution but still i'm having same performance

just give me some solutions

thanks & Regards
Prakash

Blue_Boy4-Aug-09 4:46

Blue_Boy

4-Aug-09 4:46

Perhaps you should check this result.[^]

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

www.aktualiteti.com

ps_prakash024-Aug-09 21:52

How to check for a never populated table on sql server 2005

4-Aug-09 21:52

Hi,

thank u i'm looking for it

regards
Prakash

Roberto Mazzone3-Aug-09 6:34

Re: How to check for a never populated table on sql server 2005

3-Aug-09 6:34

I use IDENT_CURRENT to predict the next identity on a table (I know I have to be careful).
IDENT_CURRENT return the SEED value (1) for a never populated table
(it's a bug...https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=297781[^])
and the same value for an empty table with 1 rows inserted and deleted.
so when IDENT_CURRENT return 1 I don't know if the new identity value will be 1 or 2.
I have to exec:

DBCC CHECKIDENT ('table_name', RESEED, 1) --if a table was empty and never populated

DBCC CHECKIDENT ('table_name', RESEED, 0) --if a table was empty and just  populated

in this way IDENT_CURRENT will return always 1.

I'm searching for a more elegant solution than, for an empty table, insert a dummy row, get the IDENT_CURRENT and delete the dummy row!

Many thanks to all

David Mujica3-Aug-09 8:04

David Mujica

3-Aug-09 8:04

Why don't you use a "select count(*)" statement to determine if the table has any rows in it ? I must be missing something. Confused | :confused:

Re: How to check for a never populated table on sql server 2005

Roberto Mazzone3-Aug-09 20:00

Re: How to check for a never populated table on sql server 2005

3-Aug-09 20:00

Ok, I try to explain better.
I want to predict what will be the next identity value for a table.
If a table have 1 row inserted and deleted, the table is empty and populated.
I this case IDENT_CURRENT return 1 and the next id value will be 2. Correct.
If a table is empty and never populated IDENT_CURRENT return 1 again (it's a bug) and the next id value will be 1. In this particular circumstance i can't predict the next id value and i would like to kown a way to check if a table was populated or not.
"Select count(*)" don't solve the problem.
Anyway many thanks!

leckey3-Aug-09 8:54

leckey

3-Aug-09 8:54

If your identity field is called something like myIndex, why not just do something like

DECLARE myCounter INT
SET @myCounter = SELECT MAX(myIndex) FROM myTableName
DELETE FROM myTableName WHERE myIndex = @myCounter

Back in the blog beatch!
http://CraptasticNation.blogspot.com/[^]

Re: How to check for a never populated table on sql server 2005

Roberto Mazzone3-Aug-09 21:25

Re: How to check for a never populated table on sql server 2005

3-Aug-09 21:25

Thanks for replay leckey.

I try to explain better.
I want to predict what will be the next identity value for a table.
If a table has 1 row inserted and deleted, the table is empty and populated.
I this case IDENT_CURRENT return 1 and the next id value will be 2. Correct.
If a table is empty and never populated IDENT_CURRENT return 1 again (it's a bug) and the next id value will be 1. In this particular circumstance i can't predict the next id value and i would like to know a way to check if a table was populated to achieve my goal.

With your code I can't predict the next identity (autonumber) value in the scenario above explained.

Anyway, thanks again.

David Skelly3-Aug-09 22:43

David Skelly

3-Aug-09 22:43

IDENT_CURRENT is not a reliable way of predicting the next identity. It can give you a probable value, but you can never be sure of what the next value will be in practice because of concurrent inserts, rollbacks, etc. It will only give you a "best guess" at what the next identity value is likely to be. The bottom line is that you can't predict the next identity, not reliably.

If you need to work around this bug, you could use an insert trigger to update a counter value in another table. That way you can tell how many inserts there have been on your main table. If the counter is 0, your next identity is 1. Otherwise, go and look at IDENT_CURRENT. It's a bit clunky but it's better than doing an insert then a delete.

If you really need 100% predictability of the next identity value, you will probably be better off not using IDENTITY but rolling your own solution (it's not hard - people used to do it all the time before IDENTITY came on the scene). That way you can be completely in control of what value gets allocated to each new insert.

Build your own identity value

David Mujica4-Aug-09 2:58

David Mujica

4-Aug-09 2:58

I agree 100% that the most reliable way to solve this would be to build your own sequence generator where you are in control of the "identity" values. Wrap the "getNextSequence" function call and your insert record logic in a transaction to guarantee things stay in synch.

Before you go and build something, I would re-think the algorithm that needs to know the "next" identity. Is this really necessary ? The whole idea behind an identity column is that it provides a unique number for the row and you shouldn't care what that value is until the row is actually created. Give it some thought. I like to keep things simple. Smile | :)

Re: Build your own identity value [modified]

Roberto Mazzone4-Aug-09 22:48

Re: Build your own identity value

4-Aug-09 22:48

First of all many thanks to David Skelly and David Mujica for the work-around about the IDENT_CURRENT bug.

Second, i'm an sql script beginner.

I know that about IDENT_CURRENT Microsoft report "Be cautious about using IDENT_CURRENT to predict the next generated identity value. The actual generated value may be different from IDENT_CURRENT plus IDENTITY_INCR
because of insertions performed by other sessions."

The job I have to do is to move rows from DB_SRC to DB_DST having the same schema.
DB_DST can be empty or not.

---------------------------
TAB. tA
---------------------------
a_id int identity not null,
b_id int not null
---------------------------

alter table tA
add constraint tB_tA_FK1 foreign key (b_id)
	 references tB (b_id)

---------------------------
TAB. tB
---------------------------
b_id int identity not null
---------------------------

this was my first implementation...

USE [DB_SRC]

CREATE TABLE #corr_id
(
   b_id_src  INT NULL,
   b_id_dst  INT NULL
}

DECLARE @IdNext AS INT
SET @IdNext = IDENT_CURRENT('[DB_DST].dbo.tB')

-- Set the correspondence
INSERT INTO #corr_id (b_id_src, b_id_dst)
	SELECT 	b_id, ROW_NUMBER() OVER (order by b_id) + @IdNext 
		FROM tB WHERE b_id IN .....code depending on user choise

-- Use the correspondence
INSERT INTO [DB_DST].dbo.tA
	SELECT	#corr_id.b_id_src FROM tA INNER JOIN #corr_id ON tA.b_id = #corr_id.b_id_src

but the IDENT_CURRENT bug (explained in previous post) occured and than...

USE [DB_DST]
ALTER TABLE tB	ADD id_src INT NULL
GO

USE [DB_SRC]

-- Set the correspondence
INSERT INTO [DB_DST].dbo.tB (id_src)
	SELECT	b_id FROM tB WHERE b_id IN .....code depending on user choise

-- Use the correspondence
INSERT INTO [DB_DST].dbo.tA
	SELECT	tBDST.b_id FROM tA INNER JOIN [DB_DST].dbo.tB AS tBDST 
			ON tA.b_id = tBDST.id_src

USE [DB_DST]
ALTER TABLE tB	DROP COLUMN id_src
GO

BTW, turning back to the post object, is there a built-in function or sp to know if a table was never populated?

modified on Wednesday, August 5, 2009 8:26 AM

David Mujica5-Aug-09 2:59

David Mujica

5-Aug-09 2:59

Is DB_DST a read-only database ? Will records every be inserted into this database ? If not, then maybe for table, tB, you can drop the "Identity" qualifier and just do a brute force copy from tA to tB. This will guarantee that the ID values are the same between the two tables.

David

Re: Build your own identity value

Roberto Mazzone5-Aug-09 22:11

Re: How to check for a never populated table on sql server 2005

5-Aug-09 22:11

David, see my reply to Mike...

Mike Ellison4-Aug-09 3:13

Mike Ellison

4-Aug-09 3:13

Why do you need to predict the next identity value on a table? I'm just curious.

www.MishaInTheCloud.com

Re: How to check for a never populated table on sql server 2005 [modified]

Roberto Mazzone4-Aug-09 22:51

Re: How to check for a never populated table on sql server 2005

4-Aug-09 22:51

Mike, anyway i'm an sql script beginner.

I know that about IDENT_CURRENT Microsoft report "Be cautious about using IDENT_CURRENT to predict the next generated identity value. The actual generated value may be different from IDENT_CURRENT plus IDENTITY_INCR
because of insertions performed by other sessions."

The job I have to do is to move rows from DB_SRC to DB_DST having the same schema.
DB_DST can be empty or not.

----------------------------
TAB. tA
----------------------------
a_id int identity not null,
b_id int not null
----------------------------

alter table tA
add constraint tB_tA_FK1 foreign key (b_id)
	 references tB (b_id)

----------------------------
TAB. tB
----------------------------
b_id int identity not null
----------------------------

this was my first implementation...

USE [DB_SRC]

CREATE TABLE #corr_id
(
   b_id_src  INT NULL,
   b_id_dst  INT NULL
}

DECLARE @IdNext AS INT
SET @IdNext = IDENT_CURRENT('[DB_DST].dbo.tB')

-- Set the correspondence
INSERT INTO #corr_id (b_id_src, b_id_dst)
	SELECT 	b_id, ROW_NUMBER() OVER (order by b_id) + @IdNext 
		FROM tB WHERE b_id IN .....code depending on user choise

-- Use the correspondence
INSERT INTO [DB_DST].dbo.tA
	SELECT	#corr_id.b_id_src FROM tA INNER JOIN #corr_id ON tA.b_id = #corr_id.b_id_src

but the IDENT_CURRENT bug (explained in previous post) occured and than...

USE [DB_DST]
ALTER TABLE tB	ADD id_src INT NULL
GO

USE [DB_SRC]

-- Set the correspondence
INSERT INTO [DB_DST].dbo.tB (id_src)
	SELECT	b_id FROM tB WHERE b_id IN .....code depending on user choise

-- Use the correspondence
INSERT INTO [DB_DST].dbo.tA
	SELECT	tBDST.b_id FROM tA INNER JOIN [DB_DST].dbo.tB AS tBDST 
			ON tA.b_id = tBDST.id_src

USE [DB_DST]
ALTER TABLE tB	DROP COLUMN id_src
GO

BTW, turning back to the post object, is there a built-in function or sp to know if a table was never populated?

modified on Wednesday, August 5, 2009 8:36 AM

Mike Ellison5-Aug-09 6:28

Mike Ellison

5-Aug-09 6:28

It may be that my intuition is off, but I am getting the sense that you are making something much more difficult than it needs to be. If I understand your post, you are simply moving data from a source database to a destination? And your destination needs to have an identity column? Why not just let it auto-increment itself the normal way?

I think I must be missing something here.

www.MishaInTheCloud.com

Re: How to check for a never populated table on sql server 2005

Roberto Mazzone5-Aug-09 22:08