Jeff Moden

Forum Replies Created

Viewing 15 posts - 17,671 through 17,685 (of 59,069 total)

RE: only select, view definition on schema, but otherwise grant alter on database

Hey! As a bit of a sidebar, something like this would make a great "SQL SPACKLE" article. You should take up the pen and submit an article.
--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)

May 11, 2016 at 8:38 pm

#1878423
RE: only select, view definition on schema, but otherwise grant alter on database

Very cool, John. That's awesome. Thanks for posting what you tried and done. Really appreciate 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)

May 11, 2016 at 8:37 pm

#1878422
RE: Are the posted questions getting worse?

I'll be there. We normally head over to shields afterwards. They've got a decent bit o' beer on tap, good food, and it's just a hop-skip-and-jump away.
Shifting gears,...
--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)

May 11, 2016 at 3:00 pm

#1878398
RE: RANDom traps for the unwary

wojciech.muszynski (5/11/2016)
Jeff Moden (5/10/2016)
Heh... absolutely correct. It doesn't happen often but it does happen.
Scientists have calculated that the chance of anything so patently absurd actually existing are...
--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)

May 11, 2016 at 4:57 am

#1878103
RE: only select, view definition on schema, but otherwise grant alter on database

Looks like those may work. Have you tried them, yet?
--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)

May 10, 2016 at 11:58 pm

#1878043
RE: Are the posted questions getting worse?

Grumpy DBA (5/10/2016)
jasona.work (5/10/2016)
Dang, now I want sliders for lunch...
6 sliders, order of fries, order of deep-fried mozzarella sticks, large Coke...
Not sure where in the dirty mitten you reside but...
--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)

May 10, 2016 at 9:33 pm

#1878035
RE: Are the posted questions getting worse?

Lynn Pettis (5/10/2016)
Why are people still using SQL-89 style joins?
Heh... because you can't do anything else in a correlated sub-query including WHERE EXISTS? Because they're a real nice shortcut...
--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)

May 10, 2016 at 9:26 pm

#1878033
RE: RANDom traps for the unwary

kiwood (5/10/2016)
If you bothered to read the documentation you would know that for a given seed, RAND will produce the same sequence. This is by design. And as for .NET...
--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)

May 10, 2016 at 9:20 pm

#1878031
RE: RANDom traps for the unwary

If you want INTEGER values that are guaranteed not to run into a bitter end, then you might do something like the following because NEWID() is guaranteed to never have...
--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)

May 10, 2016 at 6:54 am

#1877749
RE: RANDom traps for the unwary

wojciech.muszynski (5/9/2016)
SELECT * FROM Person.StateProvince
WHERE (ABS(CAST((BINARY_CHECKSUM(*) * RAND()) AS INT)) % 100) < 10
What happens if CAST((BINARY_CHECKSUM(*) * RAND()) AS INT = - 2^31 ?
Int Range is <-2 147...
--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)

May 10, 2016 at 5:15 am

#1877714
RE: RANDom traps for the unwary

@david-2 Poole,
Awesome job on demonstrating the RAND() function of SQL Server and the "binning" and not so random nature of sequential seeds. Thanks for taking the time to put...
--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)

May 9, 2016 at 7:29 pm

#1877646
RE: RANDom traps for the unwary

For those interested in the generation of non uniform random numbers (again and still pseudo random), the late, great Dwain Camps wrote a very nice article on the subject at...
--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)

May 9, 2016 at 7:27 pm

#1877645
RE: RANDom traps for the unwary

Andrew Morgan (5/9/2016)
However beware, when used in a select, it is only evaluated once, eg
SELECT RAND(),*
FROM sys.objects
GO
will output the SAME value.
That and a workaround substantial enough for the generation 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)

May 9, 2016 at 6:33 pm

#1877644
RE: RANDom traps for the unwary

robert.bruhin (5/9/2016)
Thank you! I was planning on writing a similar response. The problem here is not with the Random function, but with the author's confusion about the proper use and...
--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)

May 9, 2016 at 6:14 pm

#1877643
RE: Padding a string with extra 0's

tarr94 (5/6/2016)
Two of you have chimed in with a preference for keeping this sort of logic out of SQL. Could you please clarify why you feel this way? ...
--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)

May 9, 2016 at 4:50 pm

#1877639

Viewing 15 posts - 17,671 through 17,685 (of 59,069 total)