Jeff Moden

Forum Replies Created

Viewing 15 posts - 21,841 through 21,855 (of 59,098 total)

RE: Performance problem when joining to a view.

Daniel Forrester 123 (1/19/2015)
Hi Guys,
I found a work around which seems to trick the query optimizer into working properly by comparing the Age_Band View to other dimensions that don't seem...
--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)

January 19, 2015 at 8:49 am

#1771148
RE: Looking for an elegant way to tranpose my query result

To add to that, you're grouping by the wrong column. You need to group by the BOX TYPE column.
--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)

January 19, 2015 at 8:26 am

#1771138
RE: Looking for an elegant way to tranpose my query result

Two methods in the following article. For performance reasons and readability (even though the code can be a bit longer), I prefer the "CROSSTAB" method.
http://www.sqlservercentral.com/articles/T-SQL/63681/
--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)

January 19, 2015 at 8:22 am

#1771135
RE: Login Failed

The Windows Login still has to be present on the SQL Server or the AD Group that the login belongs to has to be present on the SQL Server.
--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)

January 19, 2015 at 8:18 am

#1771133
RE: Recursive data but increasing date by one day

@chris-2,
Agreed. You're going with what was given and I'm trying to read minds. 😛
--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)

January 19, 2015 at 8:15 am

#1771131
RE: Alter table performance

Estimates aren't going to work here. You have to know how long this is going to take and you'll likely need to schedule some downtime.
--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)

January 19, 2015 at 8:13 am

#1771129
RE: Alter table performance

Mauricio_ (1/19/2015)
Yes, test is the best, I agree. The only problem is that I underestimated the size of the table, it has 3 billion rows.
That's gonna leave a mark....
--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)

January 19, 2015 at 8:09 am

#1771126
RE: Alter table performance

Grant Fritchey (1/19/2015)
Test, test, test, test, test.
I agree that keeping it simple and using ALTER TABLE is the right answer most of the time. I also agree that you...
--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)

January 19, 2015 at 7:52 am

#1771117
RE: Recursive data but increasing date by one day

Although Chris' code does what you want, I'm not sure it does what you actually need. Yes, it works correctly (haven't run it but looks correct). If I'm reading...
--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)

January 19, 2015 at 7:41 am

#1771115
RE: Performance problem when joining to a view.

How long does it take to get a return if you run the following, making the appropriate change in the FROM clause, of course?
SELECT TOP 10 *
...
--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)

January 19, 2015 at 7:26 am

#1771110
RE: Rebuilding Indexes - Clustered v Non-Clustered

The clustered index is based on RowID which, ostensibly, is an ever-increasing value. That means that new data is added to the logical end of the table (the 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)

January 19, 2015 at 1:18 am

#1771048
RE: Describe & v$views in SQL Server

halmuslim12 (1/19/2015)
Dears,
In oracle DB from command line to describe the table columns / definition you can use : describe "Table Name " 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)

January 19, 2015 at 12:52 am

#1771040
RE: Are the posted questions getting worse?

GilaMonster (1/15/2015)
Sean Lange (1/15/2015)
I am having a discussion with somebody who swears that using
EXISTS(select 1
is way faster than just select *. I know Gail has an article on her...
--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)

January 18, 2015 at 5:32 pm

#1771016
RE: Improving Performance for Some LIKE “%string%” Searches (SQL Spackle)

dwain.c (1/18/2015)
The thing I still haven't figured out is why I couldn't find anything on this topic in my searches. It seemed so obvious when I hit upon 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)

January 18, 2015 at 5:26 pm

#1771015
RE: Insert Statement with IF Statement

Meatloaf (1/18/2015)
Thanks guys! I have it working in SQL. However, when I try to run it in my .Net app, I get a message saying "Error: Must declare...
--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)

January 18, 2015 at 3:57 pm

#1771010

Viewing 15 posts - 21,841 through 21,855 (of 59,098 total)