T-Sql rant

Question

T-Sql rant

Viewing 15 posts - 316 through 330 (of 465 total)

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

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

foxjazz (3/19/2009)
I think this is the query I arrived at for getting the next PK value.
select @currentval = max(Convert(int,substring(GenLevelAddFieldID,len(@@servername) + 1,10))) from tblLevelAddFieldRel where GenLevelAddFieldID like @@servername + '%'

Ok you guys... you wanted me to kind of wait on you... look at this code and then save his life because that's death by SQL and all of you know it.

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

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

Bruce W Cassidy (3/19/2009)
foxjazz (3/19/2009)
As far as the varchar(50) pk numbers, there is no other way to do it.
We do it by computername for a reason, our sync processes depend on that data to get uploaded to our server and synced back down to all the other clients. Our sync architecture requires it be this way.
[font="Verdana"]Have you considered using GUIDs? (Uniqueidentifier in SQL Server parlance).[/font]

If being able to isolate rows by identifying the server is necessary, GUID's will not do the job by themselves. Here's 10 generated on the same computer within nan-seconds of each other...

2BFAC20F-BB66-4E1E-8236-496A342ECFE8

3AFB45B4-568C-423F-9BBC-C7155A06F5F7

7366E4EA-C29E-4569-BE28-4D7FEDCABBD1

8DD5FA30-92C4-4BDE-AF2C-FA21C0C10190

73CAE775-FAD1-43DD-B087-76476ED7006F

6B4EB894-D1B0-4BA0-932B-690373C8899C

CCD478D0-4193-456B-ABFD-69017EBFA1FC

D020D635-9F32-472D-A92B-1CF0715EFBF5

E6EBE7D0-8BD3-4110-9CD5-E8B7F189B92A

27F6D6EA-465B-4CCD-B42F-4518C0B4E1C1

... there's nothing there to identify which server it came from and "J" already mentioned what it'll do to an index... especially if it's clustered.

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

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

foxjazz (3/19/2009)
Sergiy, as far as the bands. How could I use identity and keep with the rule of source bands.

If it must be done that way, then multiple tables with a partioned view (this is 2k) would do. Read about them in Books Online. Sounds like what you want to do and then you don't have to every worry about the "bands" in a single table colliding.

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

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 4

Jeff Moden (3/19/2009)
If being able to isolate rows by identifying the server is necessary, GUID's will not do the job by themselves.

[font="Verdana"]True. But neither will server names.

As for what they do to a clustered index... tell me about it! Our CRM uses them as primary (clustered) index keys. Ouch.

[/font]

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

J (3/19/2009)
And as was mentioned before, a LineNo is the normal way of forcing a specific order. Using the script already posted to increment the LineNo to be consecutive allows to do this in one, efficient fell swoop.

Heh... which of the dozens of scripts on this thread do you speak of?

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

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

Bruce W Cassidy (3/19/2009)
Jeff Moden (3/19/2009)
If being able to isolate rows by identifying the server is necessary, GUID's will not do the job by themselves.
True. But neither will server names.

Except for the obvious mistake of duplicating server names across domains, why not?

As for what they do to a clustered index... tell me about it! Our CRM uses them as primary (clustered) index keys. Ouch.

Then, I have to ask because I'm curious, why did you recommend them? Other than being totally unique, is there some advantage to them?

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

Sergiy SSC Guru Points: 110208 More actions · Answer 7

J (3/19/2009)
Also, you should use the GUID as a last resort. I saw something about that being more prone to index fragmentation requiring the comparison of 38 characters instead of a 4-byte int.

One note:

GUID is not 38-character string but 16-byte binary string.

Same as datetime is not whatever length character string (depending on representation format) but 8-byte numeric value.

_____________
Code for TallyGenerator

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 8

[font="Verdana"]Why did I recommend GUIDs?

A few years back I was involved in a project that had to synchronise data from multiple sources into one source. The sources were mobile hand-held units. The data volumes for transactions on the hand held units were low (as in several hundred rows at most.)

All we needed was some sort of unique identifier for the transactions they generated so that they could be merged back to the master. When they were collated on the master, they were given an identity generated key, so once on the server the GUID just became an attribute.

Simple, easy to do, worked well.

So from experience, depending on transaction volumes, GUIDs can be a perfectly valid solution to merging multiple sources.

[/font]

Sergiy SSC Guru Points: 110208 More actions · Answer 9

Bruce W Cassidy (3/19/2009)
[font="Verdana"]Why did I recommend GUIDs?
A few years back I was involved in a project that had to synchronise data from multiple sources into one source. The sources were mobile hand-held units. The data volumes for transactions on the hand held units were low (as in several hundred rows at most.)
All we needed was some sort of unique identifier for the transactions they generated so that they could be merged back to the master. When they were collated on the master, they were given an identity generated key, so once on the server the GUID just became an attribute.
Simple, easy to do, worked well.
So from experience, depending on transaction volumes, GUIDs can be a perfectly valid solution to merging multiple sources.
[/font]

Simple mapping like

LocalIdentity int IDENTITY(...) PRIMARY KEY

SourceID int,

SourceIdentity int -- received from source system

would resolve it as well.

But using of 32-bit int in joins requires about 6 times less CPU resources than using of 128-bit uniqueidentifier (for 32-bit systems).

_____________
Code for TallyGenerator

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

Jeff Moden (3/19/2009)
foxjazz (3/19/2009)
I think this is the query I arrived at for getting the next PK value.
select @currentval = max(Convert(int,substring(GenLevelAddFieldID,len(@@servername) + 1,10))) from tblLevelAddFieldRel where GenLevelAddFieldID like @@servername + '%'
Ok you guys... you wanted me to kind of wait on you... look at this code and then save his life because that's death by SQL and all of you know it.

Jeff, you no longer have to wait. He acknowledge the power of set based solutions of cursor based solutions, and has said he plans to find set based solutions in the future. I feel the Bob and I have succeeded where we needed to here. Based on the following, its time for the heavy hitters to step in and assist in furthering his education in mastering T-SQL, or at least getting a better understanding of it.

foxjazz (3/19/2009)
STUFF
There is a lot of stuff I have read, and want to address them for those that are curious.
I have had extensive experience with set based queries in the past, and currently. Having experience with sql server since 1995, table locking and such things and multi-user server is not new to me at all. Although sometimes I struggle with certain concepts that I have infrequently used, (sub queries) for one. I have rarely had the need to use them.
Business case for identity insert not being in a couple of my important tables. The reason is that I have used bands of identities, and certain types of data sources that will have identeties between 1 and 10million , 10 million and 20 million, 20 and 30. This is just one scenario.
Another scenario is where my primary key is computername+'rowcount'. The reason I use this scenario is that I have 300 users that sync data back to the server (up and down). And so I have a function that figures out what the next rowcount that the next ID will be as they insert records into the database.
I will address the rest in a bit.
I finally have my computer back, and have been installing stuff all day. When I come accross the problem with updating multiple items (where sql was complaining) I will examine it and send it to the forum for those that are interested.

Have fun, we are here on the sidelines if you need us!! 😉

John Rowan SSC Guru Points: 56440 More actions · Answer 11

Good to see things going in the right direction finally. Man, keeping up with this post has turned into a part-time job!!

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

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

Lynn Pettis (3/19/2009)
Jeff Moden (3/19/2009)
foxjazz (3/19/2009)
I think this is the query I arrived at for getting the next PK value.
select @currentval = max(Convert(int,substring(GenLevelAddFieldID,len(@@servername) + 1,10))) from tblLevelAddFieldRel where GenLevelAddFieldID like @@servername + '%'
Ok you guys... you wanted me to kind of wait on you... look at this code and then save his life because that's death by SQL and all of you know it.
Jeff, you no longer have to wait. He acknowledge the power of set based solutions of cursor based solutions, and has said he plans to find set based solutions in the future. I feel the Bob and I have succeeded where we needed to here. Based on the following, its time for the heavy hitters to step in and assist in furthering his education in mastering T-SQL, or at least getting a better understanding of it.

You guys started it... finish it up... tell him how to fix this terrible mistake he's about to make.

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

From what I can tell the mistake is already made. The question is CAN they fix it up or would it require a rewrite of their software system. I'm not sure that is something we can really help him out with completing.

It is actually in his court as to what the next step is regarding the system.

J-440512 SSCertifiable Points: 6285 More actions · Answer 14

Jeff Moden (3/19/2009)
J (3/19/2009)
And as was mentioned before, a LineNo is the normal way of forcing a specific order. Using the script already posted to increment the LineNo to be consecutive allows to do this in one, efficient fell swoop.
Heh... which of the dozens of scripts on this thread do you speak of?

Ah! I lost it too in this marathon, took me some time to retrieve it. See page 26

JacekO

Posted 2 days ago @ 6:05 PM

Just
UPDATE
sequence = sequence + 1
WHERE sequence >= @Sequence
and then insert your new record.

And I lerarned it from one of your articles. And NO! I don't remember which one.:cool:

J-440512 SSCertifiable Points: 6285 More actions · Answer 15

Manju (3/19/2009)
Hey, Forum! Anyone out there with experience in performing surgery on a live production database ?
Absolutely!!! The operation was successful, but the patient died!

Meaning that noone was ever faced with a new or modified business requirement ?