Creating Integer Primary key value using the following query

Question

Creating Integer Primary key value using the following query

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

carriet SSC-Addicted Points: 477 More actions · Answer 1

Matt Miller (#4) (6/27/2014)
A tally table - whether a physical one or one that you would build on the fly using ROW_NUMBER() - can churn through 1M records in under 2 seconds. You will not get any gaps that way.
Not sure what failure you're trying to account for with the loop (you're not actually checking that the insert happened within the loop, so failures can happen and you'd know nothing about it), but in my experience, a process running 20 minutes is a lot more likely to fail that a single,just about instantaneous, query.

Can you post a sample tally table code. thanks

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

carrieathomer (6/27/2014)
Matt Miller (#4) (6/27/2014)
A tally table - whether a physical one or one that you would build on the fly using ROW_NUMBER() - can churn through 1M records in under 2 seconds. You will not get any gaps that way.
Not sure what failure you're trying to account for with the loop (you're not actually checking that the insert happened within the loop, so failures can happen and you'd know nothing about it), but in my experience, a process running 20 minutes is a lot more likely to fail that a single,just about instantaneous, query.
Can you post a sample tally table code. thanks

Since this still looks a lot like homework let's go with the pieces first. Whether homework or not - it's important you understand it so I'd rather point you in the right direction.

First off - the best sample I can give you is to review Jeff's article on Tally tables. The link is here[/url]. If you were to simply put that phrase into the search box, you will find copious uses of this tool.

Once you have a tally table - you now have an unbroken sequence of numbers running from 1 to, say, 1M. You've already determined you starting ID, so if you added that @ID to each of those values, you now have a sequence going from @id+1 to @id+1M. Simply INSERT that into your table along with your 'Page name' value to go along with it using standard SQL syntax and voila - you have a solution.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 3

carrieathomer (6/26/2014)
do you see the url of this page, the id used inthe URL.
jumping 10000 everytime for server restart (happens in shared hosting environment) creates 7 digit or 8 digit id value in the url, it is kind of weird for a total of 5000 records my last entered primary key value is some 2345990.
ofcource for my 50 tables i need atleast 50x3 = 150 sproc's for adding deleting and updating.
Along with that i have to create another 50 sequences that is reason i don't want to use sequences.
purpose is to keep the id length minimal in the URL, just for the 200th record if i have to use 345344 as the primary key value looks very craze.
this problem was not there in sql server 2008, i used to use straightforward auto increment (1,1)

'

CREATE TABLE [dbo].[SitePage](
[PageID] [bigint] NOT NULL,
[PageName] [nchar](50) NOT NULL,
CONSTRAINT [PK_SitePage] PRIMARY KEY CLUSTERED
(
[PageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Ok... let's "discuss" this.

First, it shouldn't matter how big the number gets whether it feels "weird" or not. You're making a huge amount of work for yourself by not using an IDENTITY column on each of the tables. Lilke you said, you'd have to write the code for 50 SEQUENCEs (actually not... you could write a small bit of code that would take care of that almost instantly), take a chance on a roll-your-own solution causing deadlocks or duplicate sequence numbers, etc, etc.

To wit, if you were so worried about the number of digits in a sequence number, why do you insist on using BIGINT for the PageID column? And are you serious? You're adding a million rows in your tests and, according to what you've stated, these PageIDs are supposted to represent an internet page? Just one such insert will take you up to 7 digits from the git.

Also, restarts of the service should, at the worst, be a VERY rare thing... maybe 2 to 4 times a year at the worst. Adding 10,000 to the number would hardly be noticeable in the presence of 7 digit numbers never mind a number that BIGINT would be needed for.

Ok... I'll get off the soapbox for now. Here's one way that Matt was talking about using a "Tally Table" for. It's based on the same setup that all of your WHILE loop examples are based on. I think that setup is wrong (you should be using an IDENTITY column) but it's your post. It uses the power of SQL Server to form a "Psuedo-Cursor" instead of the RBAR of a WHILE loop or Recursive CTE (which is worse than a While loop, in this case).

DECLARE @DesiredNumberOfRows BIGINT;

SELECT @DesiredNumberOfRows = 1000000;

WITH

cteMaxPageID AS

( --=== Get the current max PageID

SELECT MaxPageID = ISNULL(MAX(PageID),0)

FROM dbo.SitePage WITH (TABLOCK)

),

cteGenPageIDs AS

( --=== Generate the necessary row data (one form of on-the-fly Tally Table)

SELECT TOP (@DesiredNumberOfRows)

PageID = mpid.MaxPageID + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM cteMaxPageID mpid

CROSS JOIN master.sys.all_columns ac1

CROSS JOIN master.sys.all_columns ac2

)

INSERT INTO dbo.SitePage

(PageID, PageName)

SELECT PageID

,PageName = N'Page ID : ' + CAST(PageID AS NVARCHAR(10))

FROM cteGenPageIDs

;

Now... here's another funny part about your request (and another reason why I think you may be getting ready to take a very long walk down the Primrose Path)... you complained about having to setup 50 Sequences (which could easily be avoided by using an IDENTITY column). When then, are you comfortable with writing something like the above for each of 50 tables? 😉

If you don't even intend to have more than 2.14 BILLION entries in the table, then change that BIGINT PageID to INT. Disk space and network traffic aren't as cheap as you might think. 😉

Remove the WITH (TABLOCK) at your own risk. I put it there for two reasons... to guarantee that no one else is inserting while this code is, which would screw up the numbering, and to make the code run a fair bit faster (19 seconds on a single 32 bit processor with 1GB of RAM on a 12 year old desktop).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

carriet SSC-Addicted Points: 477 More actions · Answer 4

carriet

SSC-Addicted

Points: 477

June 29, 2014 at 9:07 am

#1725717

Thanks all for the discussing the issue