The T-SQL Paradigm
-
but I don't foresee the day when "one language to rule them all" will ever come into being. quote]
Isn't that what ADA was supposed to be? :w00t:
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them. -
andrew gothard (4/11/2009)
but I don't foresee the day when "one language to rule them all" will ever come into being. quote]
Isn't that what ADA was supposed to be? :w00t:
Yep and for posting that you have dated yourself and no I did not write ADA.
:Whistling:
Kind regards,
Gift Peddie -
andrew gothard (4/11/2009)
Bit of a typing error there - meant to write "I always". Now you know my issues with more typing than required. True - what you say though, not only in this case, but in most "Always" is a word that should be considered before you use it. Then nearly always discarded.I tend to use the following in my trunc func as well - it's a bit slippier than using the date functions.
CREATE FUNCTION dbo.Trunc
(@datDate DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN CAST(
FLOOR( CAST( @datDate AS FLOAT ) )
AS DATETIME
)
END
Heh... absolutely understood. We've all made those types of typing errors.
Still, the very act of even putting some of those very slippery methods into a UDF will cause 7 or 8 times more CPU time. For example, here's the results of your good function as straight code and as the UDF you've posted....
Even the slipperiest of all the trunction methods still takes the same kind of hit due to being encapsulated in a function...
I do agree that encapsulating the code to reduce typing to maybe prevent some error and to make it easy on new or some GUI developers so they're all doing it the same way is a great idea... just know what the cost to the CPU is going to be. I know that a million rows seems like a lot but, if the function becomes a "company standard" or part of a code library, some well intended but uniformed heavy duty batch developer may use it on something that contains millions of interim result rows with some pretty serious consequences to CPU resourses.
--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) -
Sorry... I forgot... here's the code I used for the INT truncation which is the fastest method for truncating a date that I know of.
CREATE FUNCTION dbo.jbmTrunc--===== Function to return a "whole" date
(@DateTime DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN CAST(CAST(@DateTime-.5 AS INT) AS DATETIME)
END
And, here's the test code I used against the previously listed JBMTest table for that test...
--===== Direct INT functionsDECLARE @Bitbucket DATETIME
SELECT @Bitbucket = CAST(CAST(SomeDate-.5 AS INT) AS DATETIME)
FROM dbo.JBMTest
GO
--===== Function
DECLARE @Bitbucket DATETIME
SELECT @Bitbucket = dbo.jbmTrunc(SomeDate)
FROM dbo.JBMTest
GO
--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) -
Gift Peddie (4/11/2009)
andrew gothard (4/11/2009)
but I don't foresee the day when "one language to rule them all" will ever come into being. quote]
Isn't that what ADA was supposed to be? :w00t:
Yep and for posting that you have dated yourself and no I did not write ADA.
:Whistling:
If *that* dated me, good job I didn't mention Smartware on an IBM 8080. Let alone writing a wordprocessor in BASIC for a 16k commodore ....... oops:doze:
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them. -
andrew gothard (4/13/2009)
Gift Peddie (4/11/2009)
andrew gothard (4/11/2009)
but I don't foresee the day when "one language to rule them all" will ever come into being. quote]
Isn't that what ADA was supposed to be? :w00t:
Yep and for posting that you have dated yourself and no I did not write ADA.
:Whistling:
If *that* dated me, good job I didn't mention Smartware on an IBM 8080. Let alone writing a wordprocessor in BASIC for a 16k commodore ....... oops:doze:
Heh... I'm just glad I'm not the only old fart on this site. My first "program" was on unit record equipment that used a "peg board" module that you plugged banana-plug wires into. The only way to sort data was to run it through the card sorter and the only thing that came close to a real program was the special punched card you could put into the keypunch to automatically skip white space and force character type for the fields on the cards.
--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 (4/13/2009)
Heh... I'm just glad I'm not the only old fart on this site. My first "program" was on unit record equipment that used a "peg board" module that you plugged banana-plug wires into. The only way to sort data was to run it through the card sorter and the only thing that came close to a real program was the special punched card you could put into the keypunch to automatically skip white space and force character type for the fields on the cards.Just when I thought I had put behind me the memories of tripping and spilling my box of cards all over the floor and having to put the entire deck back together manually!:crazy:
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com -
Aaron N. Cutshall (4/13/2009)
Jeff Moden (4/13/2009)
Heh... I'm just glad I'm not the only old fart on this site. My first "program" was on unit record equipment that used a "peg board" module that you plugged banana-plug wires into. The only way to sort data was to run it through the card sorter and the only thing that came close to a real program was the special punched card you could put into the keypunch to automatically skip white space and force character type for the fields on the cards.Just when I thought I had put behind me the memories of tripping and spilling my box of cards all over the floor and having to put the entire deck back together manually!:crazy:
BWAA-HAA! You had a box? We were always fighting over those. 😀
--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) -
Speaking of boards, one of the ones I kept hidden away was one that would dup a stack of cards and punch sequential numbers into the appropriate columns. Once you got your COBOL program working this was a requirement to put it into production. Pink cards with right hand corner cuts. The only cards the programmers had access to were white with left corner cuts. Makes spotting changed decks quite easy. The operators were on the lookout and would not run your job to update production libraries.
OK, who remembers splicing punched paper tape? My title at work, COF. That's Certified Old Fart.
ATBCharles Kincaid
-
Charles Kincaid (4/13/2009)
Speaking of boards, one of the ones I kept hidden away was one that would dup a stack of cards and punch sequential numbers into the appropriate columns. Once you got your COBOL program working this was a requirement to put it into production. Pink cards with right hand corner cuts. The only cards the programmers had access to were white with left corner cuts. Makes spotting changed decks quite easy. The operators were on the lookout and would not run your job to update production libraries.OK, who remembers splicing punched paper tape? My title at work, COF. That's Certified Old Fart.
Heh.... that and the "new high speed" 110 baud tele-types?
--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 (4/13/2009)
Charles Kincaid (4/13/2009)
Speaking of boards, one of the ones I kept hidden away was one that would dup a stack of cards and punch sequential numbers into the appropriate columns. Once you got your COBOL program working this was a requirement to put it into production. Pink cards with right hand corner cuts. The only cards the programmers had access to were white with left corner cuts. Makes spotting changed decks quite easy. The operators were on the lookout and would not run your job to update production libraries.OK, who remembers splicing punched paper tape? My title at work, COF. That's Certified Old Fart.
Heh.... that and the "new high speed" 110 baud tele-types?
I can't say that I had to splice paper tapes, for what little I did with them I was pretty lucky. However, I wound up developing and testing applications on the teletype. Boy, did they go through the paper!!
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com -
110 baud? Yep, that IS high speed. 8-level too. My first word processor was a teletype with a reader and punch. Editing was a bear. You watch the document get down to the misspelled word, stop the tape, type the correct word, turn off the punch, single character advance through the misspelled word, turn the punch back on, and let it rip. This was 45-baud 5-level.
ATBCharles Kincaid
-
Charles Kincaid (4/13/2009)
110 baud? Yep, that IS high speed. 8-level too. My first word processor was a teletype with a reader and punch. Editing was a bear. You watch the document get down to the misspelled word, stop the tape, type the correct word, turn off the punch, single character advance through the misspelled word, turn the punch back on, and let it rip. This was 45-baud 5-level.OK, you win that one! I'm not quite THAT old!! 😛
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com -
Charles Kincaid (4/13/2009)
OK, who remembers splicing punched paper tape?And the cut fingers from pulling the reel of tape through your hand while searching for the point to splice on the new section, didn't you read straight off the tape too?
And the -look- from the senior operator when the tape was presented complete with red splotches after careful drying in front of the air conditioner duct.
🙂
Peter Edmunds ex-Geek
-
Aaron N. Cutshall (4/13/2009)
Charles Kincaid (4/13/2009)
110 baud? Yep, that IS high speed. 8-level too. My first word processor was a teletype with a reader and punch. Editing was a bear. You watch the document get down to the misspelled word, stop the tape, type the correct word, turn off the punch, single character advance through the misspelled word, turn the punch back on, and let it rip. This was 45-baud 5-level.OK, you win that one! I'm not quite THAT old!! 😛
I remember those teletypes as well. What fun, what noise, vibrated all over the place.
Viewing 15 posts - 151 through 165 (of 266 total)
You must be logged in to reply to this topic. Login to reply