Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««6162636465

Tally OH! An Improved SQL 8K “CSV Splitter” Function Expand / Collapse
Author
Message
Posted Tuesday, June 10, 2014 2:45 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:21 PM
Points: 13,083, Visits: 11,918
timgapinski-841846 (6/10/2014)
CREATE FUNCTION permission denied in database 'tempdb'.

not sure why i do not have permission over the tempdb but i could not get started with these much needed tools because of this error above.

i have permission over all other tables.

any suggestions?



Create a new database and work in there.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1579418
Posted Wednesday, June 25, 2014 10:50 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 36,749, Visits: 31,197
Paul White (5/15/2014)
Jeff Moden (5/15/2014)
I'd not seen that happen in my testing but know that such a thing could happen. Thanks for the heads up and the patch. When I get my head above water, I'll see if I can incorporate it. Thanks!

Yes it was always a calculated risk. It seems the new cardinality estimator in 2014 results in a plan favouring a table spool, which effectively caches a single NEWID call. The semantics of NEWID are a bit of a mess, but that's a whole other discussion.

Please don't incorporate the hack fix into your fine script - it is undocumented and unsupported after all. No doubt some revision to the script will be necessary, but please try to avoid 8690. Cheers.


I've finally had a bit of time to spend on this. I can't get the symptoms you spoke of to repeat themselves. Not sure why you may be having 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1586223
Posted Friday, June 27, 2014 12:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:13 AM
Points: 11,192, Visits: 11,092
Jeff Moden (6/25/2014)
I've finally had a bit of time to spend on this. I can't get the symptoms you spoke of to repeat themselves. Not sure why you may be having the problem.

Make sure you're on SQL Server 2014 and using the 120 model cardinality estimator.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1586724
Posted Wednesday, July 9, 2014 2:46 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 1:02 PM
Points: 19, Visits: 281
It seems that Paul is right, which explains why it appears that the memory optimized tally tables splitter beat CLR functions at first blush. (This isn't true sadly). Adding the traceflag removes the caching advantage.
Post #1590950
« Prev Topic | Next Topic »

Add to briefcase «««6162636465

Permissions Expand / Collapse