Jeff Moden

Forum Replies Created

Viewing 15 posts - 43,216 through 43,230 (of 59,063 total)

RE: User-defined function not recognized

Let's see if it actually made it from one server to another. What do you get when you run the following?
SELECT *
FROM sys.Objects
WHERE Name = 'fn_Split'
If the answer...
--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 25, 2009 at 3:01 pm

#1015640
RE: Split string using XML

jo stovall (6/25/2009)
We are converting SS2000 DTS packages to SSIS 2005.
I need help using a User Defined Function within a query on a table.
The function performs correctly.
select * from...
--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 25, 2009 at 1:17 pm

#1015568
RE: Split string using XML

Senthilnathan.Karunakaran (6/25/2009)
We did performance and scaling test to split the comma separated string value using XML query and SQL function.
The plan was better with XML split than SQL function; however...
--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 25, 2009 at 12:37 pm

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

Sinshith S Anand (6/23/2009)
Please contribute ur ideas...I have got only one correct answer....
Heh... the correct answer is "Anything that doesn't resort to RBAR or hidden RBAR."
--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 8:25 am

#1014682
RE: Converting non-clustered index to clustered index

Eswin (6/23/2009)
Since there is already a non-clustered index for the primary key constraint on emp_id i need to convert this non-clustered index "PK__Employees__1D4655FB" to a clustered index.
CREATE UNIQUE 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)

June 24, 2009 at 5:59 am

#1014564
RE: Converting non-clustered index to clustered index

When you specify the PK for a table, unless you explicitly ask for a non-clustered PK, it will automatically create a clustered index on the PK.
--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:12 am

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

Viewing 15 posts - 43,216 through 43,230 (of 59,063 total)