Slack SQL Server

Question

Post reply

Slack SQL Server

Viewing 15 posts - 16 through 30 (of 81 total)

You must be logged in to reply to this topic. Login to reply

StarNamer SSCrazy Eights Points: 8746 More actions · Answer 1

majorbloodnock (1/23/2009)
Excellent. Good article. Pity it was too late for me to have used.....

A couple of years too late for me too, but mine was a bit simpler 🙂

My 'wish list' seems to have been covered by others (mostly Jeff!) 🙂

Derek

MelS-512196 SSC Eights! Points: 842 More actions · Answer 2

Add a lightning-fast way to encrypt and decrypt data. Even following Microsoft's suggestions to combine symmetric and asymmetric functions, performance isn't there. And what about indexing on encrypted data? Storing hash values is a lot of overhead. Also, key management... Is any of this improved in SQL 2008? Not an Oracle fan, but Oracle does this better.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 3

I agree with Jeff Moden on a number of the items he mentioned, especially better date functions and the ability to more easily read in delimited files such as CSV that has optional quotes arround only character fields that have a comma in them, and write to delimited files inline without having to do a xp_cmdshell to BCP. It's amazing to me that FoxPro in the early 90's had easier to work with full functioned external file processing than SQL Server does in this decade.

Some additional things I miss from my days working with Oracle:

- BEFORE or AFTER triggers on tables instead of just AFTER triggers

- ROW level with NEW and OLD context variables, or STATEMENT level triggers instead of just STATEMENT level triggers (yeah I know you can do simmilar functionality with the INSERTED and DELETED pseudo-tables but for a typical OLTP system there are a large number of operations that are only on 1 record)

- PACKAGE functionality, to be able to group multiple stored procedures / UDFs into a single module like structure.

Some other items that come to mind:

- A more modern string comparison function than SOUNDEX. Maybe METAPHONE, double METAPHONE, or something?

- Better error messages from maintenance plans, etc, that actually tell you what went wrong instead of just a couple of generic errors.

- I miss the simplicity of DTS packages. Sure, they made SSIS very powerfull, but building a SSIS package without the Management Studio Import or Export wizard feels clunky and awkward for simple tasks.

Lori Daly-451960 SSC-Addicted Points: 416 More actions · Answer 4

I have a lot of trouble using SSIS when porting data over from DB2. It seems like DTS was much easier to use in that aspect.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

Chris Harshman (1/23/2009)
I agree with Jeff Moden on a number of the items he mentioned, especially better date functions and the ability to more easily read in delimited files such as CSV that has optional quotes arround only character fields that have a comma in them, and write to delimited files inline without having to do a xp_cmdshell to BCP. It's amazing to me that FoxPro in the early 90's had easier to work with full functioned external file processing than SQL Server does in this decade.
Some additional things I miss from my days working with Oracle:
- BEFORE or AFTER triggers on tables instead of just AFTER triggers
- ROW level with NEW and OLD context variables, or STATEMENT level triggers instead of just STATEMENT level triggers (yeah I know you can do simmilar functionality with the INSERTED and DELETED pseudo-tables but for a typical OLTP system there are a large number of operations that are only on 1 record)
- PACKAGE functionality, to be able to group multiple stored procedures / UDFs into a single module like structure.
Some other items that come to mind:
- A more modern string comparison function than SOUNDEX. Maybe METAPHONE, double METAPHONE, or something?
- Better error messages from maintenance plans, etc, that actually tell you what went wrong instead of just a couple of generic errors.
- I miss the simplicity of DTS packages. Sure, they made SSIS very powerfull, but building a SSIS package without the Management Studio Import or Export wizard feels clunky and awkward for simple tasks.

Spot on, Chris. I forgot about my extreme desire to have decent BEFORE triggers instead "INSTEAD OF" triggers. I didn't care for the RBAR nature of Oracle triggers, but I really loved the simplicity of the BEFORE triggers.

Package funtionality would also be great so long as it never requires the use of a "Reference Cursor".

--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)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

michael vessey (1/23/2009)
PCI compliance for credit and bank data out of the box

Man, I forgot about that. Add a nasty fast LUHN-10 checksum and the bank account number checksum as financial functions and now you're really cooking with gas.

--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)

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 7

Automatic column compression based on cardinality/data type similar to Sybase IQ. Think about the space savings - anywhere from 30-70% !

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

Helen Trim (1/23/2009)
I would appreciate an Age function.
It is only a small bit of code to calculate, I know, but I bet a lot of people would find it useful as a built-in function.

See? Another good idea... in fact, an age function that could take yy, mm, and wk as parameters would be good. To add to that, a function that would properly calculate the ISO week, month, and year would be very cool, as well!

--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)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

Jim Russell (1/23/2009)
Error messages from BULK IMPORT or BCP that provide some useful information about the nature of the error.
Native ability to import fixed format flat files with sign over punches and implied decimals.
FTP ability to work with mainframe paths (not all paths start with a "\" Microsoft)!
Ability to programmatically define SSIS tasks, specifically eliminate the need to define input fields by hand!
Not the fault of MS, but SQL as a language SUCKS!!
CTEs that are not re-executed every time referenced (#Temp tables are much faster!!)
Accurate dependencies (has that been fixed in 2008?)
Either eliminate Solution Manager, or support multilevel directory hierarchy.
Solution Manager Save As should work as Save As does in the rest of the world; save a copy under a new name, not simply rename the existing object.
And everything Jeff said 🙂

You rock! The CTE recalculation thingy is a big one that I forgot about. And, I like the suggestions about BCP and Bulk Insert and accurate dependicies alot!

--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)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

majorbloodnock (1/23/2009)
michael vessey (1/23/2009)
bank holidays and non working days functions
Agreed. Has anyone else looked at the calculation necessary to work out Easter? I had to write one a few years back and, whilst it's accurate, the code is disproportionately horrible.

Very much agreed on that! Another set of useful functions would be some good ol' Lat/Lon functions for calculating great circle distances between two points using Lat/Lon.

--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)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

sing4you (1/23/2009)
My big beef is with the date functions. I often have to calculate how many years, months and days between two dates. SQL will tell you that there is one month between 5/15 and 6/01 or one year between 1/31/08 and 1/05/09. Therefore, we've had to write functions to calculate valid time spans.
The date functions seem a lot like string functions to me. I wish there was more of a relationship between them and the calendar.

Agreed on that... I don't want them to change the original functions because they have their uses, but a new category of date functions would be the berries.

--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)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 12

MelS (1/23/2009)
Add a lightning-fast way to encrypt and decrypt data. Even following Microsoft's suggestions to combine symmetric and asymmetric functions, performance isn't there. And what about indexing on encrypted data? Storing hash values is a lot of overhead. Also, key management... Is any of this improved in SQL 2008? Not an Oracle fan, but Oracle does this better.

Man, would I ever like that. PCI compliance would become a breeze. Like you said, I'm not an Oracle fan, but their encryption/decryption functions are nasty fast and easy to use for most all of the more secure encryption techniques.

--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)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 13

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.

--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)

JJ B SSCertifiable Points: 5130 More actions · Answer 14

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. 🙂

Ben Holcombe-270296 Ten Centuries Points: 1016 More actions · Answer 15

My beef lately is with SSRS and SSAS datasets. It’s as if writing a report off a SSAS dataset was an afterthought. If you want to write a simple report no problem but adding any level of complexity requires you to jump through hoops. Since there is no support for drill through, to simulate that functionality creates an explosion of redundant datasets. If you put them all in one report you bloat the report, if you separate them you have all those reports to maintain. Using OpenQuery with the SSAS provider as Martin Cremer described in his "Read Data from SSAS and SQL Server in One Dataset" article adds significant complexity to achieve something seemingly basic.

It would also be nice to export SSRS reports to Excel 2007 as well as allow for a sub report to export to Excel as well.

SSRS integrated with MOSS produces inconsistent use of Global variables such as Globals!ReportName. In Native Mode the variable strips off the file extension and recognizes the case of the filename. In Integrated Mode the variable leaves the file extension and puts everything in lowercase.

A provider for MOSS libraries I think would close the loop on two separate worlds.

I am hoping Kilimanjaro addresses some of these limits in functionality.