January 23, 2009 at 9:57 am
Jeff Moden (1/23/2009)
Another set of useful functions would be some good ol' Lat/Lon functions for calculating great circle distances between two points using Lat/Lon.
Oh cool! Then I could use GPS coordinates for all the bins in a warehouse and print my pick lists in snaking order without having to use expensive auxiliary software. I could also do truck routing as a simple join.
ATBCharles Kincaid
January 23, 2009 at 10:06 am
JJ B (1/23/2009)
I appreciate all the ideas posted here. They have re-awakened some of my own itches.At the same time, looking at these posts has depressed me. I hope we will get another Friday poll some time where people get to say what they love about SQL Server - especially over other products.
I mean, while I know SQL Server is not perfect and this is an appropriate and important topic, I generally think SQL Server is great. Now I wonder why. 🙂
I'm good with that... I've used both Oracle and SQL Server... I have to admit that I "cut my teeth" on SQL Server so there's some predjudice but, despite some of it's short comings, I like SQL Server a whole lot better than I do Oracle.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 23, 2009 at 10:14 am
I think all of my wish list has already been covered but I'll reiterate it since it's small.
Working in education an age function would be really nice and so would robust csv import.
I know it's JET and not really part of SQL server but can we PLEASE get an excel import that doesn't null out "inconvenient" values?!? At least can they make the IMEX option a property checkbox rather than manual edit of the connection string.
Most of all though I'd really really like the ability to FTP from VMS with the SSIS FTP task (No the directory structure doesn't use '/' or '\' so please stop trying to add it to the path). The Windows command line FTP works so it can't be that hard.
I really like SSIS, I think it's a great tool but some of the design decisions make me glad I have a brick wall handy to bang my head on. CLR is nice but I can't help wondering how long anything I build will be good for.
-D
January 23, 2009 at 10:14 am
Jeff Moden (1/23/2009)
I'm good with that... I've used both Oracle and SQL Server... I have to admit that I "cut my teeth" on SQL Server so there's some predjudice but, despite some of it's short comings, I like SQL Server a whole lot better than I do Oracle.
Good to know! I haven't hear much about Oracle that has made me really interested in it. Just after reading this list, especially your nice and long one, made me want to know why people like SQL Server. Thanks for the response!
FYI: I suppose I cut my teeth on Sybase. Sybase is so much like SQL Server (or at least it was, now it's way behind) that I think I'm pretty biased too. I've never touched Oracle, only read about it.
January 23, 2009 at 10:24 am
Oracle has better date functions. 😛
January 23, 2009 at 11:00 am
Jeff Moden (1/22/2009)
Heh... ya just hit a sweet spot, Steve...Some functionality like we used to be able to get from sp_GetFileDetails except for a whole directory. Think "hibrid" between xp_DirTree and sp_GetFileDetails so we don't have to use sp_OA.
Create a new class library with your favorite .NET language:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
namespace Enterprise.SqlServer.Server
{
public partial class GetFileDetails
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void getfiledetails(string filePath)
{
try
{
FileInfo myFile = new FileInfo(filePath);
SqlMetaData colAlternateName = new SqlMetaData("Alternate Name", SqlDbType.NVarChar, 4000);
SqlMetaData colSize = new SqlMetaData("Size", SqlDbType.BigInt);
SqlMetaData colCreationDate = new SqlMetaData("Creation Date", SqlDbType.NChar, 8);
SqlMetaData colCreationTime = new SqlMetaData("Creation Time", SqlDbType.NChar, 6);
SqlMetaData colLastWrittenDate = new SqlMetaData("Last Written Date", SqlDbType.NChar, 8);
SqlMetaData colLastWrittenTime = new SqlMetaData("Last Written Time", SqlDbType.NChar, 6);
SqlMetaData colLastAccessedDate = new SqlMetaData("Last Accessed Date", SqlDbType.NChar, 8);
SqlMetaData colLastAccessedTime = new SqlMetaData("Last Accessed Time", SqlDbType.NChar, 6);
SqlMetaData colAttributes = new SqlMetaData("Attributes", SqlDbType.Int);
SqlDataRecord record = new SqlDataRecord(new SqlMetaData[] {
colAlternateName,
colSize,
colCreationDate,
colCreationTime,
colLastWrittenDate,
colLastWrittenTime,
colLastAccessedDate,
colLastAccessedTime,
colAttributes});
record.SetInt64(1, myFile.Length);
record.SetString(2, myFile.CreationTime.ToString("yyyyMMdd"));
record.SetString(3, myFile.CreationTime.ToString("HHmmss"));
record.SetString(4, myFile.LastWriteTime.ToString("yyyyMMdd"));
record.SetString(5, myFile.LastWriteTime.ToString("HHmmss"));
record.SetString(6, myFile.LastAccessTime.ToString("yyyyMMdd"));
record.SetString(7, myFile.LastAccessTime.ToString("HHmmss"));
char[] splitter = { ',' };
string[] attributes = myFile.Attributes.ToString().Split(splitter);
int attributesint = 0;
foreach (string attributesstring in attributes)
{
FileAttributes fileattributes = (FileAttributes)Enum.Parse(typeof(FileAttributes), attributesstring);
attributesint += (int)fileattributes;
}
record.SetInt32(8, attributesint);
record.SetInt32(8, (int)myFile.Attributes);
SqlContext.Pipe.Send(record);
}
catch (Exception myexception)
{
throw (myexception);
}
}
};
}
Then load it as a new assembly and create a new stored procedure:
ALTER DATABASE master SET trustworthy ON
IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'usp_get_file_details')
DROP PROCEDURE usp_get_file_details
GO
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'Enterprise.SqlServer.Server.GetFileDetails')
DROP ASSEMBLY [Enterprise.SqlServer.Server.GetFileDetails]
GO
CREATE ASSEMBLY [Enterprise.SqlServer.Server.GetFileDetails]
FROM 'E:\Enterprise.SqlServer.Server.GetFileDetails.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO
SELECT * FROM sys.assemblies
SELECT * FROM sys.assembly_files
GO
CREATE PROCEDURE dbo.usp_get_file_details( @pFileName nvarchar(4000) )
AS EXTERNAL NAME [Enterprise.SqlServer.Server.GetFileDetails].[Enterprise.SqlServer.Server.GetFileDetails].getfiledetails
You could add easliy add functionality to enumerate all the files in a directory and loop through them.
Joshua Perry
http://www.greenarrow.net
January 23, 2009 at 11:40 am
I think this may be a more general database technology problem than a specific feature lacking in SQL Server, but I wish there were some way to load balance databases.
Clustering, as I understand it, is more of a hardware redundancy feature, and mirroring is a data redundancy feature, but I don't know of anything in SQL Server that is comparable to web server load balancing. It would be great if there were some way where multiple servers could be combined to run as one database server where requests would be directed to spread the load evenly across them.
Maybe this is impossible, or maybe it has already been done in Oracle, or maybe it has already been done to some extent in SQL. Honestly, I don't know. Perhaps someone else who knows more can comment.
Thanks!
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
January 23, 2009 at 12:22 pm
I would like to have ability to create sub-folders in the Jobs area of SSMS so that i can organized jobs rather than have all jobs in one long list. For that matter, better organization capabilities in Tables, Views, etc would also be helpful. Anyone else feel this way??
January 23, 2009 at 1:23 pm
Making it so IMEX doesn't require a registry edit to really use (scanning the first 8 rows is just stupid)
Age calculation (as others mentioned)
I like the idea of a Users database (I have one called Common that I use that way)
A Numbers function that would produce a joinable/queriable table of integers in a defined range
Definitely going to agree with a better import/export system for various file structures
A variable type that allowed for delimited text, which could be used in IN statements straight from an input parameter
An object variable type that allowed table/column/etc names which could be used in place of complex dynamic SQL solutions, and could be used as an input parameter (death to injection attacks!)
The ability to set Dev edition so it would emulate lower levels of SQL than Enterprise (important!), just like compatibility levels, in the database properties (Of the ones I want and that I've read so far, this one would be the most important to me)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 23, 2009 at 2:01 pm
Concerning the lack of statistical function in SQL Server:
I think MS has done ok with the basics, but it would be helpful to have some of the other more complex statistical functions, even just those available in Excel. In my primary application, I calculate several statistics functions using SAS because SQL can't do the math. This is cumbersome, as I must process some of the data in SQL, then run SAS programs against it, then finish processing in SQL again. It's all of a batch nature since it would be hard to do it any other way. It's very slow getting the data to and from SAS, although once SAS has it, it can do thousands of calculations in a second or two. Would be nice to have this performance from calculations within SQL!
In other cases, I have figured out how to approximate statistical functions using UDFs and lookup tables. For example, I created normal and T distributions by creating UDFs that access several tables loaded with thousands of distribution values (generated by Excel and SAS!) It's just like using those tables in my old stats book, but SQL does it faster. The F distribution would be helpful as well.
(Note: I'm not a stats expert. I have a real statistician on my staff to advise on this, and I just figure out how to do the programming! Good luck to those who don't have their own live expert on site.)
I've tried to do other operations within UDFs, but there 's always a limit. For example, values quickly exceed the numeric datatype limits with large factorials. Also, these UDFs can get large as it is necessary to program for every possible error that could arise from bad input data (divide by zero, square root of a negative number, etc.) It is also important to carefully consider the precision of every variable in these functions in order to get an accurate result.
Other specific suggestions include: hypergeometric distribution, Fisher's test, Poisson, factorials, chi-squared distribution, binomial distribution, and confidence intervals.
I'm not suggesting that they try to provide every kind of statistics, but a casual walk through the list of statistical functions in Excel (Insert Function from the Formulas menu in Excel 2007) would provide all sorts of opportunities. I've looked around for alternatives, but they either don't exist or are expensive. I don't have the expertise to attempt this with CLRs (not to mention that I'm still on 2000.)
So MS, how about surprising us with an improved set of statistical and math functions in the next service pack? I might even be able to get the funding to upgrade to 2008 to get these!
Steve Brokaw
January 23, 2009 at 2:27 pm
JJ B (1/23/2009)
Jeff Moden (1/23/2009)
I'm good with that... I've used both Oracle and SQL Server... I have to admit that I "cut my teeth" on SQL Server so there's some predjudice but, despite some of it's short comings, I like SQL Server a whole lot better than I do Oracle.Good to know! I haven't hear much about Oracle that has made me really interested in it. Just after reading this list, especially your nice and long one, made me want to know why people like SQL Server. Thanks for the response!
FYI: I suppose I cut my teeth on Sybase. Sybase is so much like SQL Server (or at least it was, now it's way behind) that I think I'm pretty biased too. I've never touched Oracle, only read about it.
Then, I don't believe you'll like Oracle at all. BIG paradigm shift.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 23, 2009 at 2:32 pm
In my opinion, the negatives for SQL Server are far outweighed by the positive aspects. It's simply that perfection is unattainable, so taking something great, you can always find ways to make it better!
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 23, 2009 at 2:36 pm
Jeff Moden (1/23/2009)
I'll add another wish... there's a lot of things (not just running totals) that require "looking" at a range of previous or next rows. It would be handy to have a set of functions and aggregations that do that easily.
You've already asked for this (in the "properly windowed functions" category)....:)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 23, 2009 at 2:41 pm
As for me, I would settle for actual, complete, documentation of the new features added as they are added. Functionality changes, "this doesn't work in x version anymore", some GOOD examples.
But hey - full XML support, ordered sets and windowed functions, and the ability to manage committed transactions would be awesome too....:)
And Santa, when you read this - I could REALLY use that home Cray. I PROMISE I'll behave....:)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 23, 2009 at 3:57 pm
JJ B (1/23/2009)
Good to know! I haven't hear much about Oracle that has made me really interested in it. Just after reading this list, especially your nice and long one, made me want to know why people like SQL Server. Thanks for the response!
It's not that I don't like using SQL Server, it's just that the easiest way for me to think about what is missing in SQL Server is to compare it to other databases I've worked with over the years. SQL Server, especially with the advancements made in 2000 and 2005 versions, has become an enterprise class database. The tools and features that come with it provide so much more value than Oracle does. In Oracle, everything's an add on that costs more. Oracle has the advantages of being an enterprise class database before SQL Server, having clustering capabilities beyond SQL Server, and running on more operating systems than just Windows.
Viewing 15 posts - 31 through 45 (of 81 total)
You must be logged in to reply to this topic. Login to reply