Jeff Moden

Forum Replies Created

Viewing 15 posts - 51,031 through 51,045 (of 59,098 total)

RE: query to find space usage of each column in a table

Then, try this on a couple of the VARCHAR(255) columns...
SELECT *
FROM T12
WHERE DATALENGTH(columnname)-LEN(columnname) <> 0
If you get any returns, it may be because data...
--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)

April 20, 2008 at 9:47 pm

#804751
RE: query to find space usage of each column in a table

Try this first... check Books Online for how to read the output...
DBCC SHOWCONTIG (T12) WITH TABLERESULTS, ALL_INDEXES
--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)

April 20, 2008 at 9:40 pm

#804750
RE: Altering Column Values

Are the columns defined as CHAR() instead of VARCHAR()? Depending on system settings, that could be the problem.
--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)

April 20, 2008 at 9:27 pm

#804748
RE: Problem with calculated column formula

This is how you could do it in a view... but a view is not needed. This also works directly in code. It works because there is only...
--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)

April 20, 2008 at 9:25 pm

#804747
RE: Order by Numeric value of a Varchar column Need a better solution

mail2dinesh.sharma (4/20/2008)
333233343MVF is such example. Until now I have seen only alphabets and numbers but not any special characters. Beside the value I receive is from an external source so...
--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)

April 20, 2008 at 9:16 pm

#804744
RE: Order by Numeric value of a Varchar column Need a better solution

Here's why I say NOT to use ISNUMERIC as an "IsAllDigits" function...
[font="Courier New"] SELECT ISNUMERIC('143563d21'),
ISNUMERIC('143563e35')
----------- -----------
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)

April 20, 2008 at 9:15 pm

#804743
RE: Create and Populate Time Dimension

colin Robinson (4/20/2008)
i would also probably drop the table rather than truncate in this instance. You would then get the same Identity value for each date each time you ran...
--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)

April 20, 2008 at 4:55 pm

#804726
RE: Altering Column Values

It would appear that the value for the "val" column is incorrect thoughout the table. It should not have trailing spaces. I'd recommend finding the root cause for...
--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)

April 20, 2008 at 4:51 pm

#804725
RE: Problem with calculated column formula

SELECTs aren't allowed in calculated columns. Nothing you can do about it. Use a view, instead. Be careful when you write the view... improperly written views can...
--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)

April 20, 2008 at 4:47 pm

#804724
RE: Substitute NULL value on converted date field?

john.arnott (4/18/2008)
It's been suggested that "COALESCE" is preferable to ISNULL.
It allows for a list of arguments, the first of which evaluates to not null being returned.
Coalesce is ANSI standard; ...
--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)

April 20, 2008 at 4:45 pm

#804723
RE: Order by Numeric value of a Varchar column Need a better solution

Dinesh,
How about providing some additional samples where the TradeReference column contains the Alpha characters you're talking about?
--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)

April 20, 2008 at 4:42 pm

#804722
RE: Order by Numeric value of a Varchar column Need a better solution

Nope... ISNUMERIC treats $ signs, decimal points, and a whole bunch of other non-digit characters as if they were numeric. Never use ISNUMERIC to try to create an IsAllDigits...
--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)

April 20, 2008 at 4:40 pm

#804721
RE: pretty basic code question

Wow... I wonder what the real intent of the code is...
The following part of the code is poorly written...
[font="Courier New"]IF @Center = '' OR @Center IS NULL
...
--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)

April 20, 2008 at 4:36 pm

#804720
RE: The Skill Set of the Exceptional DBA

James_DBA (4/20/2008)
This further illuatrates my point. An exceptional DBA that can answer 'why' must be able to research the subject. They must know what the right question is to ask,...
--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)

April 20, 2008 at 12:34 pm

#804709
RE: The Skill Set of the Exceptional DBA

David.Poole (4/20/2008)
If you are in the position where people will voluntarily use you as the first point of contact for database information rather than the last then you are probably...
--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)

April 20, 2008 at 12:32 pm

#804708

Viewing 15 posts - 51,031 through 51,045 (of 59,098 total)