September 20, 2010 at 9:18 pm
Trey Staker (9/20/2010)
Steve Jones - Editor (9/20/2010)
BTW, I got an appeal from Itzik that Window functions might not make SQL 11 and he's appealing people to upvote connect items.I have to admit that I'm not sure these are a huge win, or a hugely important add to SQL Server, but I'd like to know more. I think that the article makes a good case in the abstract, but it's hard to read and understand and I'd like to see more practical uses here that make a convincing case. Anyone got a good set of common things that they see, which would be improved with Window functions?
I don't know about these additional window function but I have really appreciated the window functions they added in sql 2005. When I first saw this note I thought you meant they were removing what they've already added in. Are the new proposed ones that important? I'm not sure but if you had asked me that about the ones they put in 2005 pre 2005 I probably would of said we don't need them. I'd rather see them further optimize what they currently have put into 2005 like making CTE's run better.
I took a look, and the additional functions he is advocating certainly look useful to me, provided they performed well. They would make coding things like running totals much cleaner, and would eliminate the need for first storing rows in a table variable indexed on Row_Number().
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 20, 2010 at 10:20 pm
Steve Jones - Editor (9/20/2010)
BTW, I got an appeal from Itzik that Window functions might not make SQL 11 and he's appealing people to upvote connect items.I have to admit that I'm not sure these are a huge win, or a hugely important add to SQL Server, but I'd like to know more. I think that the article makes a good case in the abstract, but it's hard to read and understand and I'd like to see more practical uses here that make a convincing case. Anyone got a good set of common things that they see, which would be improved with Window functions?
I don't think the additions are that important either, but my main worry is that they would be implemented poorly. Previous efforts with common logical features have used worktables or spools in various modes (recursive CTEs, PARTITION BY aggregates) and none have been sparkling performers, in general. Their other recent similar effort was PIVOT - enough said.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 20, 2010 at 10:34 pm
Hey Threadizens...
Can someone do a flyby on this post and make sure I haven't missed something that ends up with me blowing smoke and sunshine, and sending this soul off on a wild goose chase? It's been a little bit since I went digging through tracelogs on transaction events, and would prefer to have another set of eyes with experience double check me.
http://www.sqlservercentral.com/Forums/Topic989837-360-1.aspx
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 21, 2010 at 1:40 am
Paul White NZ (9/20/2010)
Steve Jones - Editor (9/20/2010)
BTW, I got an appeal from Itzik that Window functions might not make SQL 11 and he's appealing people to upvote connect items.I have to admit that I'm not sure these are a huge win, or a hugely important add to SQL Server, but I'd like to know more. I think that the article makes a good case in the abstract, but it's hard to read and understand and I'd like to see more practical uses here that make a convincing case. Anyone got a good set of common things that they see, which would be improved with Window functions?
I don't think the additions are that important either, but my main worry is that they would be implemented poorly. Previous efforts with common logical features have used worktables or spools in various modes (recursive CTEs, PARTITION BY aggregates) and none have been sparkling performers, in general. Their other recent similar effort was PIVOT - enough said.
I agree that some of the new features have been implemented poorly, but I really would like to see those window functions in the next release. Once they're in the product I'm sure MS won't take away new features. If they end up with a poor implementation, Service Packs and CUs could be a way to deliver better algorithms.
I'm not sure Jeff's going to vote for ROWS/RANGE, for it would make the quirky update quite useless... 😛
-- Gianluca Sartori
September 21, 2010 at 3:31 am
Gianluca Sartori (9/21/2010)
I agree that some of the new features have been implemented poorly, but I really would like to see those window functions in the next release. Once they're in the product I'm sure MS won't take away new features. If they end up with a poor implementation, Service Packs and CUs could be a way to deliver better algorithms.
I can think of many other things I'd like to see added or improved first. Maybe my experience is unusual, but I don't find myself needing this sort of functionality very often. On the rare occasions that I do, there are a number of fine alternatives already - including Hugo Kornelis' set-based iteration method, and SQLCLR.
I may be in a glass half-empty mood today, but MS rarely go back and improve stuff after first release.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 21, 2010 at 6:46 am
Finally! The perfect database tool! :w00t:
http://spreadsheets.about.com/od/datamanagementinexcel/ss/080608_database.htm
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
September 21, 2010 at 6:48 am
The Dixie Flatline (9/20/2010)
Trey Staker (9/20/2010)
Steve Jones - Editor (9/20/2010)
BTW, I got an appeal from Itzik that Window functions might not make SQL 11 and he's appealing people to upvote connect items.I have to admit that I'm not sure these are a huge win, or a hugely important add to SQL Server, but I'd like to know more. I think that the article makes a good case in the abstract, but it's hard to read and understand and I'd like to see more practical uses here that make a convincing case. Anyone got a good set of common things that they see, which would be improved with Window functions?
I don't know about these additional window function but I have really appreciated the window functions they added in sql 2005. When I first saw this note I thought you meant they were removing what they've already added in. Are the new proposed ones that important? I'm not sure but if you had asked me that about the ones they put in 2005 pre 2005 I probably would of said we don't need them. I'd rather see them further optimize what they currently have put into 2005 like making CTE's run better.
I took a look, and the additional functions he is advocating certainly look useful to me, provided they performed well. They would make coding things like running totals much cleaner, and would eliminate the need for first storing rows in a table variable indexed on Row_Number().
I'm with Bob on this one, not sure I fully understand all the requests, but the ones I got look useful to me.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
September 21, 2010 at 6:56 am
jcrawf02 (9/21/2010)
Finally! The perfect database tool! :w00t:http://spreadsheets.about.com/od/datamanagementinexcel/ss/080608_database.htm
If they find your body stuff into a Microsoft Office box, they'll know I was the guilty party...
@THWACK!
😛
September 21, 2010 at 7:12 am
jcrawf02 (9/21/2010)
Finally! The perfect database tool! :w00t:http://spreadsheets.about.com/od/datamanagementinexcel/ss/080608_database.htm
Excel can do anyhing, even word processing! 😛
It's kind of like I can record a macro, so I can repeat anything.
Greg E
September 21, 2010 at 7:16 am
Greg Edwards-268690 (9/21/2010)
jcrawf02 (9/21/2010)
Finally! The perfect database tool! :w00t:http://spreadsheets.about.com/od/datamanagementinexcel/ss/080608_database.htm
Excel can do anyhing, even word processing! 😛
It's kind of like I can record a macro, so I can repeat anything.
Greg E
WAY back in the day... I was working at Merril Lynch and we had a couple of analysts that used Excel for absolutely everything... including... coloring cells to create pictures that they wanted used in presentations. It was pretty cool, if incredibly stupid.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 21, 2010 at 7:21 am
Greg Edwards-268690 (9/21/2010)
jcrawf02 (9/21/2010)
Finally! The perfect database tool! :w00t:http://spreadsheets.about.com/od/datamanagementinexcel/ss/080608_database.htm
Excel can do anyhing, even word processing! 😛
It's kind of like I can record a macro, so I can repeat anything.
Greg E
I agree that Excel can do a lot of things and, many of them, very well. There is one use for Excel that I wish people would stop. Stop using it as a data source for uploading data into SQL Server!!! That has to be one of the leading causes of headaches for ETL processes.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 21, 2010 at 7:34 am
Paul White NZ (9/21/2010)
Gianluca Sartori (9/21/2010)
I agree that some of the new features have been implemented poorly, but I really would like to see those window functions in the next release. Once they're in the product I'm sure MS won't take away new features. If they end up with a poor implementation, Service Packs and CUs could be a way to deliver better algorithms.I can think of many other things I'd like to see added or improved first. Maybe my experience is unusual, but I don't find myself needing this sort of functionality very often. On the rare occasions that I do, there are a number of fine alternatives already - including Hugo Kornelis' set-based iteration method, and SQLCLR.
I may be in a glass half-empty mood today, but MS rarely go back and improve stuff after first release.
Really? I'm guessing you mean features and not actual products 'cause I've always seen that as Microsoft's business model. Release something into the market that is crap (The first versions of Access, Word, Excel, etc...) just to have something in the market and then improve it constantly until you have a world beater.
I know they've improved SQL Server with every release as well, have they really not improved various features within the product? I'm asking honestly as I haven't done rigorous cross-platform testing myself. I'm usually more interested in testing various ways to do the same task as I'm stuck with whatever version of SQL Server my employer has on any given contract.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
September 21, 2010 at 8:08 am
Alvin Ramard (9/21/2010)
Greg Edwards-268690 (9/21/2010)
jcrawf02 (9/21/2010)
Finally! The perfect database tool! :w00t:http://spreadsheets.about.com/od/datamanagementinexcel/ss/080608_database.htm
Excel can do anyhing, even word processing! 😛
It's kind of like I can record a macro, so I can repeat anything.
Greg E
I agree that Excel can do a lot of things and, many of them, very well. There is one use for Excel that I wish people would stop. Stop using it as a data source for uploading data into SQL Server!!! That has to be one of the leading causes of headaches for ETL processes.
I agree with you on that one. Doing uploads of events with dates and times. Working out the correction code to handle all the different formats that Excel can do a date in is a big headache. Add in all the different ways humans can put together a date format and you have a lot of correction code for one measly field.
Admittedly, with date formats, text files have a bit of the same problem, you have to figure out what format the date has been put in, but Excel throws a few monkey wrenches into the works when SQL tries to figure out what the field is supposed to be.
-- Kit
September 21, 2010 at 8:15 am
Kit G (9/21/2010)
Alvin Ramard (9/21/2010)
Greg Edwards-268690 (9/21/2010)
jcrawf02 (9/21/2010)
Finally! The perfect database tool! :w00t:http://spreadsheets.about.com/od/datamanagementinexcel/ss/080608_database.htm
Excel can do anyhing, even word processing! 😛
It's kind of like I can record a macro, so I can repeat anything.
Greg E
I agree that Excel can do a lot of things and, many of them, very well. There is one use for Excel that I wish people would stop. Stop using it as a data source for uploading data into SQL Server!!! That has to be one of the leading causes of headaches for ETL processes.
I agree with you on that one. Doing uploads of events with dates and times. Working out the correction code to handle all the different formats that Excel can do a date in is a big headache. Add in all the different ways humans can put together a date format and you have a lot of correction code for one measly field.
Admittedly, with date formats, text files have a bit of the same problem, you have to figure out what format the date has been put in, but Excel throws a few monkey wrenches into the works when SQL tries to figure out what the field is supposed to be.
I don't think the problem is really "... when SQL tries to figure out what the field is supposed to be". I think the issue is on the Excel side, in the driver. I'd like someone to develop a new driver, one that gives you exactly what's in the cell, without trying to convert it in any way.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 21, 2010 at 8:18 am
Alvin Ramard (9/21/2010)
Kit G (9/21/2010)
Admittedly, with date formats, text files have a bit of the same problem, you have to figure out what format the date has been put in, but Excel throws a few monkey wrenches into the works when SQL tries to figure out what the field is supposed to be.I don't think the problem is really "... when SQL tries to figure out what the field is supposed to be". I think the issue is on the Excel side, in the driver. I'd like someone to develop a new driver, one that gives you exactly what's in the cell, without trying to convert it in any way.
I must admit, I often save-as .csv to import an excel sheet into SQL.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 19,036 through 19,050 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply