Jeff Moden

Forum Replies Created

Viewing 15 posts - 27,421 through 27,435 (of 59,101 total)

RE: Hierarchy example - Ken Henderson's book - not working

polkadot (2/23/2013)
I’m working on the hierarchy lesson in Ken Henderson’s The Guru’s Guide to Transact-SQL.
I'd like to recommend a different approach to hierarchies. Please see the following 2 articles....
--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)

February 25, 2013 at 11:38 pm

#1591030
RE: get the first and last day of any Year/Month

You can also cheat for performance with a little integer math. The 22800 is the year (1900*12). The "0" in the BOM forumula is 1900-01-01. The "-1"...
--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)

February 25, 2013 at 10:38 pm

#1591025
RE: UDF Help

Alan.B (2/25/2013)
If i am understand right. Function can return only one value. am i right?
Scalar functions return one value, table valued functions return a table variable.
Inline Table Valued Functions...
--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)

February 25, 2013 at 10:17 pm

#1591024
RE: Fibonacci numbers using a function.

Who's got a CLR arbitrary precision library for SQL Server?
BWAAAA-HAAAA!!! SQL Server is a very small park for these types of things. That brings me back to what...
--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)

February 25, 2013 at 5:13 pm

#1590982
RE: Using the While loop to create temp tables based on a list table

Debbie Edwards (2/25/2013)
Eugene Elutin (2/25/2013)
Jeff Moden (2/25/2013)
I guess I just don't understand why you need separate tables for each code to begin with.
That is exactly the thing which makes me...
--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)

February 25, 2013 at 5:09 pm

#1590981
RE: Using the While loop to create temp tables based on a list table

Debbie Edwards (2/25/2013)
Eugene Elutin (2/25/2013)
Debbie Edwards (2/25/2013)
Eugene Elutin (2/25/2013)
Jeff Moden (2/25/2013)
I guess I just don't understand why you need separate tables for each code to begin with.
That is exactly the...
--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)

February 25, 2013 at 5:07 pm

#1590980
RE: Using the While loop to create temp tables based on a list table

Debbie Edwards (2/25/2013)
I do for things happening later in the process.
It would take ages to explain that bit and I don't need to because Im Ok with that part 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)

February 25, 2013 at 5:00 pm

#1590978
RE: NEWID() - How does it work?

Michael Valentine Jones (2/23/2013)
Strictly speaking, a NEWID() would be converted to 2 BIGINTs, since a UNIQUEIDENTIFIER is 16 bytes, and a BIGINT is 8 bytes.
Not sure what I was thinking,...
--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)

February 25, 2013 at 4:51 pm

#1590977
RE: NEWID() - How does it work?

Lynn Pettis (2/22/2013)
Jeff Moden (2/22/2013)
Lynn Pettis (2/22/2013)
I haven't tried, but are you sure you can convert a uniqueidentifier to a bigint?
Yes, but not directly.
SELECT CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)
Cool. ...
--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)

February 25, 2013 at 4:50 pm

#1590976
RE: How to shrink TempDb

mpdillon (2/24/2013)
I have read a few posts on how to shrink TempDb but I am not even sure if "Shrink" is what I need to do.
Here is what is...
--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)

February 25, 2013 at 5:56 am

#1590776
RE: Using the While loop to create temp tables based on a list table

I guess I just don't understand why you need separate tables for each code to begin 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)

February 25, 2013 at 5:48 am

#1590772
RE: NEWID() - How does it work?

Lynn Pettis (2/22/2013)
I haven't tried, but are you sure you can convert a uniqueidentifier to a bigint?
Yes, but not directly.
SELECT CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)
--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)

February 22, 2013 at 9:40 pm

#1590577
RE: Transpose/Pivot Help

ajames 36824 (2/22/2013)
Jeff Moden (2/22/2013)
ajames 36824 (2/22/2013)
You ROCK! 😀
Do you understand how and why it works?
Well it looks like it says that for each question in the QuestionID column, return...
--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)

February 22, 2013 at 8:33 pm

#1590575
RE: Better Way of XQuerying

I'm still absolutely amazed that HTML and XML ever made it out of the starting blocks. Both are horribly tag-bloated which unnecessarily clogs the pipe, bottlenecks I/O, and uses...
--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)

February 22, 2013 at 8:15 pm

#1590574
RE: Display on Month and Year from date formula ??

dwain.c (2/21/2013)
The only reason I went to DATETIME2 was thinking that the extra precision would take care of the 1 millisecond issue that arises when DATETIME is used with accuracy...
--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)

February 22, 2013 at 6:53 pm

#1590570

Viewing 15 posts - 27,421 through 27,435 (of 59,101 total)