Jeff Moden

Forum Replies Created

Viewing 15 posts - 43,261 through 43,275 (of 59,102 total)

RE: Need help on set-based expression

You've already written it. Look at the SELECT statement you built for the cursor. Just put the display in the text mode and let just that SELECT do...
--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)

June 24, 2009 at 1:06 am

#1014461
RE: printing 1 to 100 in SQL Server as a batch....

Of course, there's my favorite...
[font="Courier New"] SELECT t.N
FROM dbo.Tally t
WHERE t.N <= 100[/font]
You should Google the term "Tally Table" or click on the following link.
http://www.sqlservercentral.com/articles/T-SQL/62867/
--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)

June 23, 2009 at 11:50 pm

#1014436
RE: printing 1 to 100 in SQL Server as a batch....

Of course, there's always the Itzik method...
[font="Courier New"] CREATE FUNCTION dbo.fnItzekNumsTest(@N AS BIGINT)
RETURNS TABLE
     AS
RETURN
   WITH
        L0   AS (SELECT 1 AS C UNION ALL SELECT 1),   --2 rows
        L1   AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows
        L2   AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows
        L3   AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows
        L4   AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rows
        L5   AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),--4294967296 rows
        Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY C) AS n FROM L5)
         SELECT N FROM Nums WHERE n <= 100[/font]
--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)

June 23, 2009 at 11:43 pm

#1014433
RE: printing 1 to 100 in SQL Server as a batch....

hi_abhay78 (6/23/2009)
try this my friend :
WITH Numbers(Number) AS (
SELECT 1
UNION ALL
SELECT Number + 1
FROM Numbers
WHERE Number <= 99
)
SELECT *...
--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)

June 23, 2009 at 11:36 pm

#1014432
RE: The maximum number of result sets that can be displayed in the results grid

It sounds like you're using a cursor or while loop. What is it that you're doing that requires one?
As a side bar, you can get around the limitations 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)

June 23, 2009 at 11:12 pm

#1014425
RE: Formatting data from two rows

Will there ever be 3 rows?
--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)

June 23, 2009 at 8:38 pm

#1014396
RE: Are the posted questions getting worse?

Steve Jones - Editor (6/23/2009)
Note that I've received a complaint on this thread.
I don't think anyone has crossed a line, but I see a few posts that are close. Please...
--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)

June 23, 2009 at 7:48 pm

#1014384
RE: Are the posted questions getting worse?

Paul White (6/23/2009)
Oh boy. So now "standard SQL" has been invoked.
Jeff's gonna absolutely love this - I just hope Carl likes pork.
http://www.sqlservercentral.com/Forums/FindPost740591.aspx
Heh... I never thought I'd tell someone to...
--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)

June 23, 2009 at 7:45 pm

#1014383
RE: Going Native

Charles Kincaid (6/22/2009)
Microsoft gives you a foundation and framework. Weather tight and dry. You want light? Go buy lamps. Want central heat. That's an option....
--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)

June 23, 2009 at 7:39 pm

#1014382
RE: Common questions asked in SQL Server DBA Interview

Never mind... wrong post. 😉
--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)

June 23, 2009 at 6:57 pm

#1014370
RE: Slack SQL Server

Tom.Thomson (6/23/2009)
the foreign key constraint - but that only works if the target is (a) unique and (b) the primary key.
I'm pretty sure that only half of that is true....
--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)

June 23, 2009 at 6:24 pm

#1014360
RE: Help with SQL algorithm to process transactions

Eichpeel (6/23/2009)
"Any fool can write code that a computer can understand. Good programmers write
code that humans can understand." -Martin Fowler et al, Refactoring: Improving the Design of Existing Code, 1999...
--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)

June 23, 2009 at 4:21 pm

#1014336
RE: Help with SQL algorithm to process transactions

For this reason, I do not use these types of undocumented features
Look up UPDATE in BOL... the SET @variable = column = expression IS documented.
and also try to avoid...
--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)

June 23, 2009 at 4:16 pm

#1014331
RE: Help with SQL algorithm to process transactions

Paul White (6/23/2009)
Jeff,
Using my preferred measure of total_worker_time as reported by sys.dm_exec_query_stats:
Triangular join: 10,218,749µs (~10.2 sec)
Your method:
Receipts running total: 41,015µs (~0.04 sec)
Shipments running total: 240,234µs (~0.24 sec)
Final SELECT: 4,425,781µs...
--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)

June 23, 2009 at 4:09 pm

#1014328
RE: Any Utility available to convert Sql Server Stored Procedures into user defined functions

suneel (6/22/2009)
Hi,
As I mentioned, we import the resultset of these SPs into Excel. But the columns that we display in each and every worksheet are dynamic. So, we are trying...
--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)

June 23, 2009 at 9:44 am

#1014052

Viewing 15 posts - 43,261 through 43,275 (of 59,102 total)