August 28, 2012 at 1:59 am
hi all,
i want to generate id column values on the basis of every day.
suppose table name like test and
table have column id then value of id column are
today is 28-aug-2012 then values are increased after each insert like this
:-
table name test
column name ID
and values are
id
2012082800001
2012082800002
....
....
2012082800002
and next day it will be on new insert :-
2012082900001
2012082900002
.......
........
and next day it will be on new insert :-
2012083000001
2012083000002
.......
........
August 28, 2012 at 3:18 am
Generating your own identity values isn't as straightforward as it might at first appear, the code for preventing concurrency issues is fairly complex. Why don't you use the identity column supported by SQL Server and generate this pseudokey on the fly?
;WITH SampleData AS (
SELECT ID, InsertDateDT = CAST(InsertDate AS DATETIME)
FROM (VALUES (1,'20120828'), (2,'20120828'), (3,'20120829'),(4,'20120829')) d (ID, InsertDate)
)
SELECT
ID,
InsertDateDT,
MyBigintCompositeID = CAST(
CONVERT(VARCHAR(8),InsertDateDT,112)
+ RIGHT('00000'+CAST(ROW_NUMBER() OVER(PARTITION BY InsertDateDT ORDER BY ID) AS VARCHAR(5)),5)
AS BIGINT)
FROM SampleData
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 28, 2012 at 3:34 am
This will work for adding one at a time:
--------------------------------------------------------------
drop table #test;
create table #test ( id varchar(13) );
--------------------------------------------------------------
declare @id varchar(13), @nextid varchar(13);
select @id = max(id) from #test;
select @nextid = case when left(@id, 8) = convert(varchar(8), getdate(), 112)
then convert(varchar(8), getdate(), 112) +
right('0000' + cast(cast(substring(@id, 9, 5) as int) +1 as varchar(5)),5)
else convert(varchar(8), getdate(), 112) + '00001'
end;
insert into #test values ( @nextid );
--------------------------------------------------------------
select * from #test;
--------------------------------------------------------------
August 28, 2012 at 9:32 am
HI ALL
PLEASE PROVIDE SOME EASY WAY IF YOU KNOW
THANKS
August 28, 2012 at 9:46 am
anuj12paritosh (8/28/2012)
HI ALLPLEASE PROVIDE SOME EASY WAY IF YOU KNOW
THANKS
The easiest way to achieve that will be switching to SQL2012 and using sequences.
All others ways will not be easy at all and will always have some issue with concurrency.
August 28, 2012 at 9:50 am
Eugene Elutin (8/28/2012)
anuj12paritosh (8/28/2012)
HI ALLPLEASE PROVIDE SOME EASY WAY IF YOU KNOW
THANKS
The easiest way to achieve that will be switching to SQL2012 and using sequences.
All others ways will not be easy at all and will always have some issue with concurrency.
+1
August 28, 2012 at 7:41 pm
I am intrigued by ChrisM@Work's comment about possible concurrency issues, so I will submit this suggestion so that he may shoot holes in it based on his statement:
CREATE TABLE #t (ID BIGINT IDENTITY(201208280001, 1), value INT)
INSERT INTO #t SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
IF LEFT(IDENT_CURRENT('#t'), 8) <> CONVERT(VARCHAR(8), GETDATE(), 112)
BEGIN
DECLARE @newseed BIGINT = CAST(CONVERT(VARCHAR(8), GETDATE(), 112)+'0000' AS BIGINT)
DBCC CHECKIDENT('#t', RESEED, @newseed)
END
INSERT INTO #t SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
SELECT * FROM #t
DROP TABLE #t
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 28, 2012 at 8:50 pm
laurie-789651 (8/28/2012)
This will work for adding one at a time:
--------------------------------------------------------------
drop table #test;
create table #test ( id varchar(13) );
--------------------------------------------------------------
declare @id varchar(13), @nextid varchar(13);
select @id = max(id) from #test;
select @nextid = case when left(@id, 8) = convert(varchar(8), getdate(), 112)
then convert(varchar(8), getdate(), 112) +
right('0000' + cast(cast(substring(@id, 9, 5) as int) +1 as varchar(5)),5)
else convert(varchar(8), getdate(), 112) + '00001'
end;
insert into #test values ( @nextid );
--------------------------------------------------------------
select * from #test;
--------------------------------------------------------------
Ohhhh.... be careful Laurie. A whole lot can happen between the time you get the max id and the time you use it. On high usage systems (or "unlucky" times on low usage systems) you'll end up with duplicate ID's (and dupe errors if the column is unique) with that code. The "obvious fix' of using an explicit transaction will lead to hundreds of deadlocks per day.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 28, 2012 at 9:27 pm
dwain.c (8/28/2012)
I am intrigued by ChrisM@Work's comment about possible concurrency issues, so I will submit this suggestion so that he may shoot holes in it based on his statement:
CREATE TABLE #t (ID BIGINT IDENTITY(201208280001, 1), value INT)
INSERT INTO #t SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
IF LEFT(IDENT_CURRENT('#t'), 8) <> CONVERT(VARCHAR(8), GETDATE(), 112)
BEGIN
DECLARE @newseed BIGINT = CAST(CONVERT(VARCHAR(8), GETDATE(), 112)+'0000' AS BIGINT)
DBCC CHECKIDENT('#t', RESEED, @newseed)
END
INSERT INTO #t SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
SELECT * FROM #t
DROP TABLE #t
That's seriously clever especially since it solves the multi-row insert problem that most people experience with custom daily dequences but what happens if the number of inserts exceeds 99,999 rows in a day? It won't even warn you that it did a wrap around. And, as fast as it is, there's still the possibility of someone getting in between the DECLARE and the DBCC.
What about inserts that last from slightly before midnight to slightly after midnight? If you want them to accurately reflect the date they were inserted into the table, you'll be disappointed. Of course, the same would be true of you had a default of GETDATE() on the table.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 28, 2012 at 9:46 pm
anuj12paritosh (8/28/2012)
hi all,i want to generate id column values on the basis of every day.
suppose table name like test and
table have column id then value of id column are
AA
today is 28-aug-2012 then values are increased after each insert like this
:-
table name test
column name ID
and values are
id
2012082800001
2012082800002
....
....
2012082800002
and next day it will be on new insert :-
2012082900001
2012082900002
.......
........
and next day it will be on new insert :-
2012083000001
2012083000002
.......
........
I REALLY hope I can talk you and the people you work for out of this idea. At the root of the problem, it's a violation of normal form because you have a column that contains two distinct values... the date of insertion and a very dependent daily sequence number. You also have a growth problem. Yeah... I know you'll say that you'll never go over 99,999 rows in a day but a lot of consultants make a lot of money off of people who thought the same thing.
And, let me ask... what do you want to do if someone deletes a row?
I implore you not to use this column for anything except display purposes which also means don't store it in your database. Calculate it at display time like ChrisM did with the understanding that if someone does a delete, the sequence numbers will change.
If I can't talk you out of it, then wait until the end of each day and calculate the column once for the previous day using code like ChrisM used. Dwain's code is pretty decent and there's a low probability of someone getting in out of sequence but that's not quite the same as saying that it's guaranteed to never dupe a row during the change at midnight.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 28, 2012 at 11:16 pm
Jeff Moden (8/28/2012)
dwain.c (8/28/2012)
I am intrigued by ChrisM@Work's comment about possible concurrency issues, so I will submit this suggestion so that he may shoot holes in it based on his statement:
CREATE TABLE #t (ID BIGINT IDENTITY(201208280001, 1), value INT)
INSERT INTO #t SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
IF LEFT(IDENT_CURRENT('#t'), 8) <> CONVERT(VARCHAR(8), GETDATE(), 112)
BEGIN
DECLARE @newseed BIGINT = CAST(CONVERT(VARCHAR(8), GETDATE(), 112)+'0000' AS BIGINT)
DBCC CHECKIDENT('#t', RESEED, @newseed)
END
INSERT INTO #t SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
SELECT * FROM #t
DROP TABLE #t
That's seriously clever especially since it solves the multi-row insert problem that most people experience with custom daily dequences but what happens if the number of inserts exceeds 99,999 rows in a day? It won't even warn you that it did a wrap around. And, as fast as it is, there's still the possibility of someone getting in between the DECLARE and the DBCC.
Hehe. True about something slipping in between the DECLARE and DBCC. But considering you're only expectin 9999 inserts (or hopefully less) per day I'd say it's unlikely. And you can also do this to help avoidance.
CREATE TABLE #t (ID BIGINT IDENTITY(201208280001, 1), value INT)
INSERT INTO #t SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
DECLARE @newseed BIGINT = CAST(CONVERT(VARCHAR(8), GETDATE(), 112)+'0000' AS BIGINT)
,@TodayAsChar VARCHAR(8) = CONVERT(VARCHAR(8), GETDATE(), 112)
IF LEFT(IDENT_CURRENT('#t'), 8) <> @TodayAsChar
DBCC CHECKIDENT('#t', RESEED, @newseed)
INSERT INTO #t SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
SELECT * FROM #t
DROP TABLE #t
Thanks Jeff for saying it is clever! I just hope I don't let that go to my head.
As to >9999 inserts per day - well, we should only allow systems that insert less than that don't you think? 😀 Anything more is probably a waste of disk space.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 29, 2012 at 12:28 am
So let's really try to get clever (or really obnoxious depending on your perspective :-D) and allow for up to 9,999,999 INSERTs per day. Try this:
CREATE TABLE #t (ID BIGINT IDENTITY(411470000001, 1)
,value INT)
INSERT INTO #t SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
DECLARE @TodayMidnight INT = CAST(DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) AS INT)
DECLARE @newseed BIGINT = CAST(CAST(@TodayMidnight AS VARCHAR(5))+'0000000' AS BIGINT)
IF LEFT(IDENT_CURRENT('#t'), 5) < @TodayMidnight
DBCC CHECKIDENT('#t', RESEED, @newseed)
INSERT INTO #t SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
SELECT ID=CONVERT(VARCHAR(8), CAST(CAST(LEFT(ID, 5) AS INT) AS DATETIME), 112) + RIGHT(ID,7)
,value
FROM #t
DROP TABLE #t
I ran into some really odd anomalies in trying to set this up that I'm at a loss to even explain what they are.
Edit: Had to make a slight correction and add this:
What the heck is going on here?
SELECT CAST(GETDATE() AS INT)
,CAST(DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) AS INT)
,GETDATE(), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 29, 2012 at 2:10 am
dwain.c (8/28/2012)
I am intrigued by ChrisM@Work's comment about possible concurrency issues, so I will submit this suggestion so that he may shoot holes in it based on his statement:
CREATE TABLE #t (ID BIGINT IDENTITY(201208280001, 1), value INT)
INSERT INTO #t SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
IF LEFT(IDENT_CURRENT('#t'), 8) <> CONVERT(VARCHAR(8), GETDATE(), 112)
BEGIN
DECLARE @newseed BIGINT = CAST(CONVERT(VARCHAR(8), GETDATE(), 112)+'0000' AS BIGINT)
DBCC CHECKIDENT('#t', RESEED, @newseed)
END
INSERT INTO #t SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
SELECT * FROM #t
DROP TABLE #t
Very creative Dwain! It looks great ... except you have to run a date check every time you insert. I wonder how long RESEED takes on large tables? Might look into that later.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 29, 2012 at 2:20 am
ChrisM@Work (8/29/2012)
dwain.c (8/28/2012)
I am intrigued by ChrisM@Work's comment about possible concurrency issues, so I will submit this suggestion so that he may shoot holes in it based on his statement:
CREATE TABLE #t (ID BIGINT IDENTITY(201208280001, 1), value INT)
INSERT INTO #t SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
IF LEFT(IDENT_CURRENT('#t'), 8) <> CONVERT(VARCHAR(8), GETDATE(), 112)
BEGIN
DECLARE @newseed BIGINT = CAST(CONVERT(VARCHAR(8), GETDATE(), 112)+'0000' AS BIGINT)
DBCC CHECKIDENT('#t', RESEED, @newseed)
END
INSERT INTO #t SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
SELECT * FROM #t
DROP TABLE #t
Very creative Dwain! It looks great ... except you have to run a date check every time you insert. I wonder how long RESEED takes on large tables? Might look into that later.
Thanks Chris!
I was a little concerned about the speed of IDENT_CURRENT actually, but Jeff seems to suggest it should be pretty quick.
I have to confess that I needed to look up the syntax for this as I rarely work with IDENTITY columns. It's good to know what can be done though, so then Google is your friend. 😀
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 29, 2012 at 5:52 am
Had to make a slight correction to my prior post and add a question.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply