Jeff Moden

Forum Replies Created

Viewing 15 posts - 22,486 through 22,500 (of 59,099 total)

RE: Simple Update Query from SQL Newbie

gcook 48147 (11/14/2014)
I thought the SET required a subquery as that is how the examples were formatted that I found online. This is much simpler and more direct. ...
--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)

November 14, 2014 at 5:57 pm

#1758767
RE: can you have too many rows in a table

sukai_ndure (11/14/2014)
How many rows would you consider to be too many for a single table and how would you re-arrange the data if asked?
any answers?
Considering that your other question was...
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)

November 14, 2014 at 5:50 pm

#1758765
RE: Group by question

The following does it in a single scan rather than 2 and does not require a JOIN. And, no... it doesn't need a GROUP BY either. The COUNT(*)...
--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)

November 14, 2014 at 5:26 pm

#1758761
RE: converting datetime to 112 type

KoldCoffee (11/14/2014)
I moved the conversion into the query, instead of trying to do it at time of setting the param and now the conversion works.
The problem is that DATETIME cannot...
--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)

November 14, 2014 at 4:36 pm

#1758754
RE: Is there a rule of thumb for number of rows in the partition for a very large table

sql_er (11/14/2014)
We don't have any indexes on this Fact table.
Sounds like we need to have a conversation with our DBA 🙂
Oh my, YES! That's absolutely incredible! I don't...
--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)

November 14, 2014 at 4:14 pm

#1758750
RE: XML Columns in a table

sukai_ndure (11/14/2014)
What methods could you use to extract specific bits of data from an XML column in a table and return the data in alphanumeric format?
any answers?
Sounds like a homework,...
--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)

November 14, 2014 at 12:00 pm

#1758709
RE: Should I create an SP

I like your second method better because it does stand a chance of short circuiting one hit on the database if the row already exists and won't hit the log...
--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)

November 14, 2014 at 11:51 am

#1758706
RE: SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds

Understood and appreciated. There are sometimes those problems. Just to be sure, though, none of the systems that I've encountered this on had replication or anything similar running.
--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)

November 14, 2014 at 11:41 am

#1758702
RE: Unpivoting multiple columns.

Thank you both for the kind feedback.
For those looking to modernize (even though a bit less obvious to read, IMHO), we can change the multiple SELECT/UNION ALLs to VALUES, thusly.
...
--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)

November 14, 2014 at 11:18 am

#1758698
RE: Error creating temp table based on IF logic

Why not just create a couple of views that use OPENROWSET or OPENQUERY to create the same number of columns and conditionally flop a synonym between the views?
--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)

November 14, 2014 at 10:42 am

#1758693
RE: SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds

Part of the reason why this problem is so difficult to nail down is because, in my humble opinion, people don't look in the right spot. Instead, they go...
--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)

November 14, 2014 at 10:36 am

#1758690
RE: new 2014 implementation

Always shoot big on memory. As has already been stated, 8GB is shockingly low for a 600GB DB. You can't just think user, connections, and queries... you also...
--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)

November 14, 2014 at 10:09 am

#1758679
RE: Data Purge benifits

Heh... every time that I've deleted legacy data, even stuff over 5 years, someone finds a reason to "need it back NOW!". I don't delete. I "archive" on...
--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)

November 14, 2014 at 10:00 am

#1758673
RE: Time issue with 70-458 exam

dan-572483 (7/14/2014)
I seem to recall in previous exams having at least 100 minutes to answer 45 questions - well over two minutes per question.
]]
2 minutes and 13 seconds doesn'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)

November 14, 2014 at 9:57 am

#1758670
RE: Execute SQL statement via OLEDB connection inside Script Task

I believe that there's an SQL Task, isn't there? Why not just skip the proverbial tower of Babel and call a stored procedure?
--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)

November 14, 2014 at 9:55 am

#1758668

Viewing 15 posts - 22,486 through 22,500 (of 59,099 total)