Problem to write Special Function

Question

Problem to write Special Function

Golden_Behzad

SSC Journeyman

Points: 79
More actions
March 3, 2012 at 8:23 am

#75649

HI
How to write Function that after execute it this result appear
(fast algorithm i need)
exapmle fnInc()
select fnInc() ===>1
select fnInc() ===>2
select fnInc() ===>3

Viewing 15 posts - 1 through 15 (of 19 total)

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

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 1

That would be a "sequence" function and, in SQL Server 2000, it's just about (I try to not say "NOT" or "NEVER" :-D) impossible to do because it would also require you to update a "sequence table" for the last value used and you can't update tables from within a function.

If you'd let us know a bit more about how you would have used such a function, perhaps there's a viable work around that we could help you with.

--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)

Golden_Behzad SSC Journeyman Points: 79 More actions · Answer 2

Thank for read my problem (Jeff Moden)

i write more description for my problem

i need function that generate sequence uniqueidentifier

i write function that usefull but not 100% sequence

(data type of pk in my db was uniqueidentifier .after action in rows (insert delete ) fragmention in my table 100% and execute query slowly

for this problem i think if pk id is sequnce ,fragmention is very low and increase speed of query and action (insert,...)

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

Golden_Behzad (3/3/2012)
Thank for read my problem (Jeff Moden)
i write more description for my problem
i need function that generate sequence uniqueidentifier
i write function that usefull but not 100% sequence
(data type of pk in my db was uniqueidentifier .after action in rows (insert delete ) fragmention in my table 100% and execute query slowly
for this problem i think if pk id is sequnce ,fragmention is very low and increase speed of query and action (insert,...)

In that case, I don't recommend a "sequence function". Instead, I recommend the use of an auto-numbering IDENTITY column. Look it up in Books Online (the "help" system that comes with SQL Server).

--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)

Golden_Behzad SSC Journeyman Points: 79 More actions · Answer 4

i know identity column is very good but my database is more than 250 table and relation between table is set.

for change pk and fk datatype is very heavy for cost , time , ...

i think for solve this problem need this function

now how to create this function ?

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 5

Golden_Behzad (3/3/2012)
i know identity column is very good but my database is more than 250 table and relation between table is set.
for change pk and fk datatype is very heavy for cost , time , ...
i think for solve this problem need this function
now how to create this function ?

You've just confused the heck out of me. You want a sequence function which implies at least the INT datatype. Use of such a function would require you to change all of your tables, anyway. If you made an IDENTITY column on each, you could turn identity insert on, re-establish the relationships, drop the old GUIDs and you'd never have to work about speed or fragmentation again.

Hmmmm.... of course, you could get around all of this. Leave your PK as a GUID but remove the clustered index and replace it with a non-clustered index (note that the PK does NOT have to be the clustered index). Then you could add an IDENTITY column to each table and put the clustered index on that or, perhaps on an ever increasing datetime column if one is availalble to help keep the fragmentation in check. Just don't forget that the clustered index works better if it's also unique. In the event of tied dates, you could use the PK column as the second column of the clustered index.

--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)

Golden_Behzad SSC Journeyman Points: 79 More actions · Answer 6

Hello.Sorry For This Delay.

This is Very good suggest .

but i connote used this suggest

because my application have about 20 user

and for do this action i must be recreate my db .

if you found way for that function ,thanks for send me

Lowell SSC Guru Points: 323738 More actions · Answer 7

there's no easy button or magic function that you can use to fix your issue without any work on your part, sorry.

As Jeff already identified, you'll have to fix all your tables, and that will take time.

you can(and should be) prototyping all the changes in a dev database and then testing; when you have it working correctly, then you can apply the same changes to your production database.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 8

Golden_Behzad (3/6/2012)
Hello.Sorry For This Delay.
This is Very good suggest .
but i connote used this suggest
because my application have about 20 user
and for do this action i must be recreate my db .
if you found way for that function ,thanks for send me

I'm a bit confused. Why does having 20 users prevent you from making the suggested changes?

--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)

Golden_Behzad SSC Journeyman Points: 79 More actions · Answer 9

because after enter data into db speed of query is reduce

i used join in query between two,three,.. table .and guid is generate no sequence id .

example tblSale(ID Uniqueidentifier,...)

tblSaleArticle(ID uniqueidentifier,SaleID uniqueidentifier,...) and relation between two table

after insert into tblsaleArticle id of tblSaleArticle not sequence and article of tblSalearticle Are scattered in table and join is heavy

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 10

We have the ol' language barrier thing going on here. I know you want to change the GUI to a "sequence" and that you think you need a function for it. I'm suggesting you use an IDENTITY type of column instead.

--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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

The only other thing to realize is that you aren't going to be able to generate guids (uniqueidentifiers) in a sequence using SQL Server 2000.

If you need a sequence, you really need to look at what Jeff is suggesting.

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 12

It's a long thread so you've probably missed it, Lynn. He's trying to get rid of the current GUIDs and he thinks he want's to do it using an "integer sequence" function (see the OPs first post). Even with a properly built "NextID" sequence table and some deadlock-proof code, he won't be able to do it in a function because he won't be able to update the sequence table from the function. With IDENTITY columns being available (and I'm not sure the OP even knows what that is), even if the function could update a sequence table, I'd still consider the use of a sequence function a very bad idea. I don't even like it for 2012 and they have an Oracle-like sequence function built in!

--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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

My bad. Probably did, but the OP seemed to keep talking about the uniqueidentifiers in later posts.

Golden_Behzad SSC Journeyman Points: 79 More actions · Answer 14

For Create Function For SequenceID YouCan Use This Function But This Function Is Very Bad Performance and If multi user call this in some time generate duplicate id .

to generate almost sequence id i can use guid as binary in last code to generate unique id but not sequence

create Function NewID()

AS

Begin

DECLARE @d AS DATETIME

SELECT @d = GETDATE()

DECLARE @year INT

DECLARE @month INT

DECLARE @day INT

DECLARE @hour INT

DECLARE @minute INT

DECLARE @secon INT

DECLARE @milisecond INT

DECLARE @i INT

SELECT @d = GETDATE()

SELECT @year = YEAR(@d)

SELECT @month = MONTH(@d)

SELECT @day = MONTH(@d)

SELECT @hour = datepart(hh, @d)

SELECT @minute = datepart(mi, @d)

SELECT @secon = datepart(ss, @d)

SELECT @milisecond= DatePart(ms, @d)

WHILE (@milisecond = DatePart(ms, GETDATE())) // wait until next time receive

BEGIN

SET @i = 1

END

return CAST(CAST(@yearAS BINARY(2))

+CAST(@yearAS BINARY(2))

+CAST(@monthAS BINARY(1))

+CAST(@dayAS BINARY(1))

+CAST(@hourAS BINARY(1))

+CAST(@minuteAS BINARY(1))

+CAST(@seconAS BINARY(2))

--+CAST(@seconAS BINARY(1))

--+CAST(@milisecondAS BINARY(1))

+CAST(@milisecondAS BINARY(2)) AS UNIQUEIDENTIFIER)

End