question

Håkan Winther avatar image
Håkan Winther asked

Feature request

This is a little bit of topic, but I think it is worth asking. Microsoft are using "connect" to get feadback on the SQL server product, and they try to implement features that has many requests. Don't users of SSC have an opertunity to affect the future of SQL server? If we gather our feature requests here at SSC, add them in "connect" and vote for them, Microsoft might listen.

What feature or improvement would you like to see implemented in next version of SQL server? How do you want it to be implemented?

I suggest that we submit the requests to "connect" and put a link in the answer to this question so everyone can vote in "connect".

sql-serverfeature-request
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

8 Answers

·
Matt Whitfield avatar image
Matt Whitfield answered

Thought of another one - and guys please comment on this because I'd love to know what your thoughts on this are...

I'd like to see better support vertical table partitioning. Right now if you want to do a vertical partition, you basically do it pretty much manually. So, you might have a table which stores details of, say, cards. In that table you have the card number, expiry date, account balance, name on the card, logo, signature image snapshot (just for an example). However, the logo, name on the card and signature image are rarely used, but widen the rows significantly. So you might separate that out into two tables, one which contains the primary, often used information, and one which is keyed the same, containing the less often used values.

I'd like to define this as:

CREATE TABLE [Customer].[Cards] (
  [ID] [int] IDENTITY (1,1) NOT NULL,
  [CardNumber] [varchar] (20) NOT NULL,
  [ExpiryDate] [date] NOT NULL,
  [AccountBalance] [money] NOT NULL,
  [NameOnCard] [nvarchar] (255) NOT NULL,
  [CardLogo] [varbinary] (MAX) NOT NULL,
  [SignatureImage] [varbinary] (MAX) NULL,
  CONSTRAINT [PK_Cards] PRIMARY KEY CLUSTERED ([CardNumber]) ON [CUSTOMER_DATA]
) ON [CUSTOMER_DATA]
WITH SECONDARY PARTITION 
 FOR ([NameOnCard], [CardLogo], [SignatureImage]) 
  ON [CUSTOMER_EXTENDED_DATA]

This would then have the benefits associated with vertical partitions, while keeping the code that uses the table clean from concerns about how that partitioning is done. It would also mean that tables could be partitioned later, without the need for changes to referencing code.

What do you think?

2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image Håkan Winther commented ·
I think that would be a nice feature and to make it even nicer you should be able to add columns from both of the partitions in a index. But have you tried sparse columns? It sounds like you are looking for something like that, but i can see some other benfits with vertical partititions, like spreading them over disk groups.
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
No it's not really related to sparse columns - in this scenario, the columns in the secondary partition are generally not null - but the data in the secondary partition makes the row width a lot higher - so partitioning them out means you get a faster lookup for the rows you want when you're not looking for the extra data...
0 Likes 0 ·
Matt Whitfield avatar image
Matt Whitfield answered

I want to see scalar UDF's able to be marked as inline - much like you could in C/C++. I have only just started my blog, so it's embarrassingly empty at the moment, but my first blog entry is on that very topic.

I don't really see that it would be that hard, either, and would be a step towards closing the gap between elegance and efficiency that exists in SQL today, in so many areas...

Edit -> I found the connect item for this. Worth upvoting that one!

5 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dave ballantyne avatar image dave ballantyne commented ·
If your testing is on 2005, try with ' Set Statistics Time Off' , see my blog for the effects http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/11/24/the-observer-effect-in-action.aspx
1 Like 1 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
*very* interesting...
0 Likes 0 ·
RBarryYoung avatar image RBarryYoung commented ·
this is a popular MS-Connect request, but it still needs all the votes it can get, as it appears that MS just does not understand how incredibly important this is.
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Welcome to 10K. Well done.
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
@Grant - thank you very much sir!
0 Likes 0 ·
dave ballantyne avatar image
dave ballantyne answered

Aaron Bertrand does a weekly(ish) round-up of Connect issues here.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scot Hauder avatar image
Scot Hauder answered

allow MERGE to update a row more than once. There's probably a better way to do this, but I run into this error often when using MERGE. Consider:

;WITH cte AS (
SELECT 1 AS Month_Num, 'Steve' AS Customer, 50.00 AS Purchase_Amt, 10.00 AS Discount_Amt
UNION
SELECT 1, 'Mary', 45.00, 9.00
UNION
SELECT 1, 'Mary', 15.00, 0.00
UNION
SELECT 2, 'Mary', 5.00, 0.00
UNION
SELECT 2, 'Mark', 40.00, 5.00
)
MERGE #RunningTotals rt
USING cte
ON (cte.Customer = rt.Customer AND cte.Month_Num = rt.Month_Num)
WHEN MATCHED
UPDATE SET rt.Purchase_Amt = rt.Purchase_Amt + cte.Purchase_Amt
           ,rt.Discount_Amt = rt.Discount_Amt + cte.Discount_Amt
WHEN NOT MATCHED
INSERT (Month_Num, Customer, Purchase_Amt, Discount_Amt)
VALUES (cte.Month_Num,cte.Customer,cte.Purchase_Amt,cte.Discount_Amt)
OUTPUT INSERTED.*

Also, CAST('' as decimal(4,2)) should = 0 just as CAST('' as int) = 0

2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image Håkan Winther commented ·
Hm, I thought MERGE was implemented to prevent unexpected multiple updates of the same row using UPDATE clause?
0 Likes 0 ·
Scot Hauder avatar image Scot Hauder commented ·
It was--that's why it needs to change :)
0 Likes 0 ·
Matt Whitfield avatar image
Matt Whitfield answered

Another one - I'd like to see SQL Server become more intelligent about cascading foreign key constraints... SQL Server is incredibly easily confused, either by multiple paths, or by self-referencing tables. PostgreSQL manages it just fine, so I definitely think it's somewhere that could be improved, because having to implement triggers for referential integrity in these cases sucks.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scot Hauder avatar image
Scot Hauder answered 
ISNUMERIC should take a type parameter eg

ISNUMERIC('$5.0', money)

So errors like this will not occur:

IF ISNUMERIC('$5.0') = 1
BEGIN
   SELECT CAST('$5.0' AS numeric) -- Error converting data type varchar to numeric WTF??
END

...also as I mentioned above
SELECT CAST('' as decimal(4,2)) -- Error converting data type varchar to numeric

should = 0 just as SELECT CAST('' as int) = 0
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Cyborg avatar image
Cyborg answered
SMS or Call Notification! There should be feature to send notification through phone call or SMS!
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image sp_lock commented ·
@Cyborg... there are many companies that provide email2sms and email2phone... Our Dv/Comms provider offer an excellent service where we just purchase credit and let SQL/SSIS/Job do their worst.
0 Likes 0 ·
Steve Jones Editor avatar image
Steve Jones Editor answered
I think this is a great idea. We ought to try and get some votes in there. What I'd suggest is that we tag ideas a "Connect" and then let people debate/vote here as well as have a link to Connect. I get the Connect feed, and there are a lot of things in there that seem like user issues, and not really bugs (or suggestions). I feel sorry for the triage guys.
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.