Upvotes: 278

<=-=Feb 1 2008 7:18PM=-=>

Thanks for the valuable suggestion.

This seems more like adding the sequence support which we’re seriously considering for the next major release.

Meanwhile, would using identity column help?

<=-=Feb 2 2008 2:11AM=-=>

It does not seem that you understood the request. This definitely has nothing to do with
IDENTITY. I am less versed about sequences, but I don’t think they will cut it either. If you think
it does, maybe you could provide an example? Take this problem: For the Orders table in
Northwind, write a query that lists the number of orders for all days in 1997. The result set should
include all 365 days, and list zero for days without a number.

This is a typical problem where you need a table of of numbers (or dates). While it’s easy to
create such a table, I argue in this request that there would be a number of advantages of
having such a table (function) builtin. For more details on the optimization problems, see my
article on Arrays and Lists in SQL Server, http://www.sommarskog.se/arrays-in-sql.html.

<=-=Feb 27 2008 11:40AM=-=>

Where do you stop with the content of the numbers table?

1-100? 1-1000000? What if I need 10billion? What if I need numbers up 2^64?? More?

Seems like an awful waste of space for something that may or may not be used.

As per workaround, use a function. (would be nice if that function was built-in..maybe that’s what they mean by ‘sequence’?)

<=-=Jul 3 2008 6:11AM=-=>

I would say 100000 by default
Another option would be if that 100000 is not good enough for you there would be a built in proc that will generate this table for you, you would have to pass in the start and end value and the proc would generate this table

<=-=Jul 3 2008 8:58AM=-=>

This would be a great asset and I believe it to be worth the storage requirement. 100,000 is a good number to default to and if more numbers are needed; I am sure some sort of system sp that builds the table dynamically. The bottom line is a lot of people are not acclimated with the concept of a numbers table and are missing out of all the performance gains from using one. Having MS built-in numbers table not only exposes the numbers table, but also allows those who do not want to manage an additional table to reap the benefits.

<=-=Jul 7 2008 5:11AM=-=>

Adding a built in table of numbers from 1 to 2 billion would certainly take up a lot of disk space especially if you consider the fact that most people never use one with a count over 8000 and those that do almost never use one over a million.

What I’d rather see is a whole number “sequence generator” where you could identify the StartValue, EndValue, and optional Increment which would default to 1. And, it should be able to order the values in an ascending or descending fashion as if it had a Clustered Primary Key doing so.

<=-=Jul 15 2008 9:59AM=-=>

Why would anything need to be stored on disk? Make the query engine intelligently produce the numbers on demand. It would be an in-memory operation, perhaps not even needing all rows to be materialized, as in the case of splitting a string on a delimiter where the majority of the numbers are discarded.

The optimizer could pay attention to how the numbers are used and do an in-memory loop through the string rather than creating a table, temp or otherwise. The table is uniform and regular, and re-generating it each time in a tight +1 loop is probably faster than building the whole thing. It’s like a kind of native index where you know in advance where everything is because the lookup index is exactly equal to the lookup value.

Look at it this way. What would the query engine have to do to satisfy this query:

SELECT Num FROM Numbers WHERE Num = 123

It wouldn’t have to hit the Numbers table at all: the input IS the result. The answer can be given at parse time. Similarly,

SELECT Num FROM Numbers WHERE Num BETWEEN 1 AND 100000

Again, why hit the table at all? It already knows the desired result, the numbers 1- 100000. It would be far more efficient to just generate the values in memory than go read them somewhere. (give me 1st value: okay, got 1. Give me 2nd value, okay, got 2. …)

Many problems are easily solvable with a numbers table that are difficult or impossible otherwise.

<=-=Aug 5 2008 12:58PM=-=>

Indeed, the table would only be logical, it would not be stored on disk.

<=-=Nov 25 2010 11:53PM=-=>

A little bit off-topic, but could I also get the same thing for dates? It just seems so stupid to create table that has dates so that you can join it with other tables to get one row for each day even if data doesn’t exist for that day.

<=-=Dec 14 2010 2:54PM=-=>

Please read feedback id 347442. Such a built-in function would have (potentially) significant performance impacts, lacking in user defined functions/solutions (mentioned feedback id has examples).

<=-=Jan 1 2011 6:00PM=-=>

Glad to see this suggestion. This functionality would help with many of the perennial problems that come up on news groups and forums.

<=-=Mar 14 2012 4:17PM=-=>

I see Postgres has the “generate_series” function for this need.

<=-=Jun 11 2012 1:19PM=-=>

Neither identity nor sequence will help here. It is hard to believe you even read the question.

+1

<=-=Nov 27 2012 12:04PM=-=>

Having had the “joy” of splitting a multi-XML-document proprietary 3rd-party supplied file of over 3MB, my Numbers table was 12M+ rows, just in case a heavy ordering day bumped the file size…

On SQL2012, using page-level compression, it consumes over 130MB.

If, as current best practices appear to recommend, our SQL Servers are stacked with huge quantities of RAM, 130MB in, say 120GB isn’t going to matter a great deal…

If Microsoft gave us a “standard set” of Numbers that is configurable, but which could be bumped on demand and shrunken automatically to the standard-set-size, memory pressure would be reduced while still delivering high-speed data-access.

My Dates table stretches from 2008 (birth of system) to 2099, comprising 33K rows of 32 columns (at present) and 16MB compressed, with alternate indexes. Again, a triflingly small memory “hog”.

I’m less persuaded that a Microsoft-supplied Dates table is a “need” simply because the slices and representations of dates in short/long/week/month/quarter, etc. are really site-specific (hence my 32 columns!).

+1 for the Numbers table…

<=-=Mar 19 2013 1:14PM=-=>

Please, please, please.

<=-=Nov 24 2014 9:27PM=-=>

Howdy there, Microsoft,

This suggestion is going to be 8 years old in 3 months. It would be wonderful to celebrate that anniversary with a CU for both 2012 and 2014 that contains this wonderful suggested tool. It shouldn’t take you long.

I’ll also suggest that it take two parameters so that folks can start it “0” if they need to (which is more common than you’d think).

Thank you for your time.

<=-=Nov 26 2014 4:42AM=-=>

Personally, I think this relatively simple addition is long overdue. The Tally table has been called the Swiss Army Knife of SQL and I have to agree. It can be used for everything from splitting strings to date generation. An ITVF that’s built into the product would allow for optimization that’s probably beyond what DBAs can do. I’ve been using a table of numbers for years and consider it completely normal, as do many others in the SQL Server community. Please consider the addition of this feature to the product.

<=-=Nov 26 2014 6:31AM=-=>

This would be handy even if it it were no larger than a single record and additional functionality were added to SQL Server such as “Connect BY” and so forth to allow for larger virtual numbers tables could be accessed. Many uses are out there for such a table and if there is any doubt, take a look at the DUAL table in Oracle.

<=-=Dec 1 2014 7:55AM=-=>

This is fairly trivial to develop, but also the issues raised above (integrity) are potentially a problem in app code. Think SQL Injection or some other tampering that wouldn’t be the case in a native numbers table.

While this would be a nice backport, I would argue this is fairly minor work for SQL Server 2014+ (in an SP) and giving us a table with an integer’s worth of numbers, would be very handy. I would suggest that the table be a DMV visible in every database.

<=-=Apr 2 2015 7:56AM=-=>

CREATE FUNCTION [dbo].[fn_Nums](@n AS BIGINT)
RETURNS TABLE
WITH SCHEMABINDING,ENCRYPTION
AS
RETURN
WITH
L0 AS,
L1 AS,
L2 AS,
L3 AS,
L4 AS,
L5 AS,
Nums AS OVER AS n FROM L5)
SELECT n FROM Nums WHERE n <= @n;
GO

<=-=Nov 4 2015 1:00AM=-=>

The easiest trick to generate table of numbers with OPENJSON:

create function
dbo.fn_gen_numbers(count int) returns table return (select cast([key] as int) as number from OPENJSON( '[1' + replicate(',1',count-1)+’]’))

You will need SQL Server 2016 CTP3 or higher for this.

<=-=Mar 5 2016 8:30AM=-=>

This should not be a real table with a fixed number of rows. This should be a generator TVP that can provide any number range, e.g.

select Number from Builtin_Numbers(1, 10000000)

It would be a pity if the built in numbers table would be restricted to some range, e.g. [1, 1M].

We also need such a generator for dates (all dates in a given range).

<=-=Mar 16 2016 12:15AM=-=>

I really love to have a number sequence generator build into SQL Server as it removed a lo

Upvotes: 289

<=-=Aug 9 2007 9:12AM=-=>

Benefits:
Improved Readability, therefore Maintainablity, therefore reduced manpower $ and time expenditure.

<=-=Aug 27 2007 6:13PM=-=>

I definitely see the value of this. Thanks for proposing it. We’ll try to squeeze it in to SQL Server 2008 but things are really tight in terms of room for changes like this. It has to compete with many other things, including a bunch that have a larger impact on query performance, or that don’t have an easy workaround. This issue has a workaround, though it is not pretty and programmability would be enhanced a lot with the proposed enhancement. I’ll see what I can do.

Best regards,
Eric

<=-=Oct 17 2007 2:06PM=-=>

Things do not look good for this enhancement for Katmai. It probably will not make it into the release. We’ll make a final assessment in a couple of weeks. Before we can consider this, we have to finish other commitments.

<=-=Mar 11 2011 6:41AM=-=>

What is the Root Duplicate for this please? This links back to 307476 which in turn links back to here but both have been closed as duplicates.

<=-=May 25 2011 3:46AM=-=>

Surely both items cannot be closed as duplicates. This makes no sense whatsoever. Please can someone at MS sort this out.

<=-=Nov 15 2011 11:28AM=-=>

This must not be just a syntax shortcut and be difficult to implement. Perhaps 2012?

<=-=Apr 23 2012 4:27PM=-=>

Hello Steve and others,
Due to a problem in our bug databases, there is some issue with several connect items not reflecting the status correctly. For this particular item, it is not possible to see the duplicate or the parent item.
But please be assured that the requests for IS DISTINCT predicate is our list of things to do for future & we are tracking it.

Thanks
Umachandar, SQL Programmability Team

<=-=Aug 15 2012 12:23AM=-=>

Yes, we could use this keyword.
On MERGE statements, we only want existing rows updated, if they actually changed.
The current syntax is very long and almost unreadable.

<=-=Jul 28 2015 8:46AM=-=>

Still waiting Microsoft. It’s been 8 years and five releases (2008, 2008R2, 2012, 2014, 2016) and still no sign of this. Do you even review Connect at all?

<=-=Jul 12 2016 6:36AM=-=>

We are using 2016 and we are still writing our Merge statements with MS’s crippled approach

<=-=Sep 22 2016 9:30AM=-=>

How do you add temporal tables, JSON data-types and the ability to dynamically stretch tables into the world’s most advanced Cloud RDBMS, and yet not find the time to implement a basic logical operator from the 17-year-old ANSI standard?

<=-=Sep 22 2016 9:34AM=-=>

It’s not just the aesthetics of the code at stake here, it’s performance. The workarounds involve either OR statements or the ISNULL function, both of which de-optimize queries in profound ways. A declarative syntax for doing this would improve execution plans around the world, saving trillions of CPU cycles and disk reads, and ultimately saving mankind. WON’T SOMEBODY THINK OF THE CHILDREN?

<=-=May 18 2017 11:14AM=-=>

Don’t you love how useful M$ Connect is for raising issues? Almost 10 years go by after someone at Microsoft says this is a “Good Idea” and yet, no results ….

Upvotes: 176

<=-=Nov 13 2007 12:37AM=-=>

Hello

Thank you for your feedback. We’re certainly considering row value constructors for a future release of SQL Server.

- Sara Tahir
Microsoft SQL Server

<=-=Aug 11 2010 8:03AM=-=>

I think row constructors would be a great and important addition to T-SQL. Just wanted to point out a few more cases that I’d love to see implemented:

---

- Assignment
-—————————————————————————————————-

UPDATE dbo.T1
SET (c1, c2, c3) = (@p1, @p2, @p3)
WHERE keycol = @key;

— Logically equivalent to:

UPDATE dbo.T1
SET c1 = @p1,
c2 = @p2,
c3 = @p3
WHERE keycol = @key;

— Or with a subquery:

UPDATE dbo.T1
SET (c1, c2, c3) = (SELECT T2.c1, T2.c2, T2.c3
FROM T2
WHERE T2.keycol = T1.keycol)
WHERE keycol = @key;

— Logically equivalent to:
UPDATE dbo.T1
SET c1 = (SELECT T2.c1
FROM T2
WHERE T2.keycol = T1.keycol),
c2 = (SELECT T2.c2
FROM T2
WHERE T2.keycol = T1.keycol),
c3 = (SELECT T2.c3
FROM T2
WHERE T2.keycol = T1.keycol)
WHERE keycol = @key;

---

- Comparison Predicates
-—————————————————————————————————-

— The operators: AND follow the rules for Equality operations in the standard.

— equals operator:

SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1, c2, c3) = (@p1, @p2, @p3);

— Logically equivalent to:

SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE c1 = @p1
AND c2 = @p2
AND c3 = @p3;

- Consider NULL comparison as TRUE
- For details see: https://connect.microsoft.com/SQLServer/feedback/details/286422/add-language-and-optimizer-support-for-iso-distinct-predicate?wa=wsignin1.0
WHERE (c1, c2, c3) IS NOT DISTINCT FROM (@p1, @p2, @p3);

— With composite joins:

SELECT T1.keycol AS T1_key, T2.keycol AS T2_key
FROM dbo.T1 JOIN dbo.T2
ON (T1.c1, T1.c2, T1.c3) = (T2.c1, T2.c2, T2.c3);

— not equals operator:

SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1, c2, c3) <> (@p1, @p2, @p3);

— Consider NULL vs. non-NULL comparison as TRUE
WHERE (c1, c2, c3) IS DISTINCT FROM (@p1, @p2, @p3);

— Logically equivalent to:

SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE c1 <> @p1
OR c2 <> @p2
OR c3 <> @p3;

— The operators: , , AND follow the rules for Ordering operations in the standard.

— less than operator:

SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1, c2, c3) < (@p1, @p2, @p3);

— Logically equivalent to:

SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1 < @p1)
OR (c1 = @p1 AND c2 < @p2)
OR (c1 = @p1 AND c2 = @p2 AND c3 < @p3);

— greater than operator:

SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1, c2, c3) > (@p1, @p2, @p3);

— Logically equivalent to:

SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1 > @p1)
OR (c1 = @p1 AND c2 > @p2)
OR (c1 = @p1 AND c2 = @p2 AND c3 > @p3);

— less than or equals operator:

SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1, c2, c3) <= (@p1, @p2, @p3);

— Logically equivalent to:
SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1 = @p1 AND c2 = @p2 AND c3 <= @p3)
OR (c1 = @p1 AND c2 < @p2)
OR (c1 < @p1);

— greater than or equals operator:

SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1, c2, c3) >= (@p1, @p2, @p3);

— Logically equivalent to:

SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1 = @p1 AND c2 = @p2 AND c3 >= @p3)
OR (c1 = @p1 AND c2 > @p2)
OR (c1 > @p1);

---

- IN AND BETWEEN Predicates
-—————————————————————————————————-

— IN:

SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1, c2, c3) IN (SELECT c1, c2, c3
FROM T2);

— if the subquery returns the rows R1, R2, �, Rn, the above query is then equivalent to:

SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1, c2, c3) = R1
OR (c1, c2, c3) = R2
OR …
OR (c1, c2, c3) = Rn;

— BETWEEN:

SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1, c2, c3) BETWEEN (pl1, @pl2, @pl3) AND (pr1, @pr2, @pr3) ;

— Logically equivalent to:

SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1, c2, c3) >= (pl1, @pl2, @pl3) AND (c1, c2, c3) <= (pr1, @pr2, @pr3) ;

---

- MIN/MAX Aggregates
-—————————————————————————————————-

SELECT grpcol, MAX
FROM dbo.T1
GROUP BY grp;

— Logically equivalent to:

WITH C AS
(
SELECT grpcol, c1, c2, c3,
ROW_NUMBER() OVER AS rownum
FROM dbo.T1
)
SELECT grpcol, c1, c2, c3
FROM C
WHERE rownum = 1;

— with explicit ROW keyword
SELECT grpcol, MAX)
FROM dbo.T1
GROUP BY grpcol;

<=-=Aug 11 2010 8:23AM=-=>

I like Itzik’s ideas, all but the MIN/MAX construct.

To me MIN would behave similar to COALESCE in that it perhaps aggregates c1 & c2 & c3, then chooses the min result of the three.

I’m also not sure how column aliases would work when row constructs are used in the SELECT statement.

<=-=Aug 11 2010 9:15AM=-=>

There are platforms that support scalar MIN/MAX-like aggregates called LEAST/GREATEST. Actually, it would be nice to see those in SQL Server too. But I think that the potential confusion can be resolved with an explicit use of the ROW keyword: MAX). As for aliasing, normally the construct would return the original column name, but could be nice to see such a feature allows assigning our own aliases. Something like: MAX) AS (myc1, myc2, myc3).

<=-=Jan 1 2011 6:30PM=-=>

There is a bit of a clunky way currently in which aggregates can be used across columns.

SELECT [name]
,[number]
,(SELECT MAX FROM (SELECT low AS n UNION ALL SELECT high) d) n
FROM [master].[dbo].[spt_values]

<=-=Mar 12 2012 8:51AM=-=>

Is this still a feature under consideration? It’s been 4.5 years since this request was created, and there are duplicates of this request too.

<=-=Nov 8 2012 11:34AM=-=>

Having used these in ORACLE over EIGHT YEARS AGO it’s stunning that this has been on the very distant back-burner for OVER FIVE YEARS.

Would Microsoft care to update loyal community members on the status of this request, please?

(So many highly useful T-SQL functions could have been added at the expense of ruining SSMS in SQL2012 – just observing…)

<=-=Jan 4 2013 8:17AM=-=>

Any idea if we will get this in SQL 2012 or any of the SP of that?

<=-=Feb 20 2013 9:14PM=-=>

Can we have an update on this suggestion please?

Echo Phil Brammer, really like Itzik’s suggestions below. Although rather than messing with the well established MIN/MAX aggregate functions, I would suggest adding a new functions for evaluating the MIN or MAX over a value list (not set) e.g. MIN_VAL(val1, val2, val3), whereas MIN would work as per Itzik’s suggestion.

<=-=Apr 2 2015 2:25PM=-=>

What is the status of this suggestion?

Upvotes: 407

<=-=Oct 11 2007 4:28AM=-=>

ANS SQL 99 actualy defines a syntax (section 11.49) for function definition that can be used for an inline scalar function (body consisting of a single RETURN statement) – inlining being an implementation rather than a definitional issue. There are other database products (e.g. IBM DB2 UDB) that already support this, so going the ANS way would assist portability.

The enhancement is clearly necessary.

<=-=Oct 11 2007 8:45AM=-=>

In other programming languages, this might be a macro expansion, where an include file contains the macro, which expands into inline code at compile time. (I believe that this was discussed as a possibility in the very early days of Yukon.) In any case, however implemented, Inline Scalar UDF would be a major improvement over the speed-bump that exists now.

I was going to post this myself, but happily add my vote to the existing suggestion.

<=-=Oct 15 2007 11:17AM=-=>

I echo the desire for SQL-invoked routine and understand one of the elements of the CLR implementation is to satisfy this requirement. The rub is CLR functionality is quite convoluted compared Andrew�s suggestion. The substitution aspect is particularly appealing to this diehard Transact-SQL coder.

<=-=Dec 11 2007 11:19AM=-=>

Hello,

Thank you for sending your feedback to us. We will consider this enhancement in a future release of SQL Server.

Thanks,
-Vineet Rao.

<=-=Mar 23 2010 8:58PM=-=>

Why the microsoft leave this problem so long —almost 4 years, It is very simple to resove it for those UDF that has no data access.

<=-=Mar 25 2010 2:47PM=-=>

Yep. It would be pretty useful to get this problem solved cause writing table function instead of naturally scalar function is not what I want to do. It’s a kind of trick but not a right way obviously. Thanks.

<=-=May 1 2010 12:06AM=-=>

I did not think this would take all these years to resolve, i think MS should look for the features that affect its big product and make them more efficient.

<=-=Jun 3 2010 3:03PM=-=>

As a DBA I see alot of developers who aren’t big sql guys that say hey we can create functions in SQL? Cool now I can wrap this small little piece of reusable code in a function and call it from my queries. It feels native to them and then I have to reel them in and let them know the downsides of it. I’d love to see this fixed!

<=-=Dec 24 2010 7:14AM=-=>

I like this proposed solution. However, I’d prefer another solution, or maybe a combination of the two. I propose that the use of deterministic functions is cached.

With a scalar UDF cache in place, if the query selects a lot of rows with (probably) many duplicate input values, the optimizer might choose to transform the query with the scalar UDF into a selfjoined query (spool) that will fetch the value from cache instead of computing the output value. Even with a poor cache hit rate, the deduplicating can potentially speed up the query a lot (depending on the cost of the UDF).

I expected caching of scalar UDF output as early as SQL Server 2005, but it is still lacking.

<=-=Dec 24 2010 12:12PM=-=>

I’m a little confused. It isn’t a problem specifically with user defined functions. It’s ANY scalar function used in the SELECT list or WHERE clause including built-in functions like ISNULL and COALESCE. If a value in a column has to be processed by a function (regardless of the source of the function, i.e. UDF or built-in) in order to determine whether the row needs to be filtered from the result set then every row must be processed. This isn’t an engine problem, it’s a design problem.

If you have to process a specific column of every row through some kind of filter then I’m not sure how the proposal is going to resolve that. Typically, I’ve found that this is an architectural issue. For example, if you don’t allow NULL’s you won’t need to use ISNULL or COALESCE. Of course, that isn’t always possible and may not always be advisable. The point is that a little foresight during the data architecture phase can help alleviate some of these issues before they happen.

The two workarounds already proposed are excellent choices where these scenarios already exist.

<=-=Jun 23 2011 3:04AM=-=>

What is Microsofts view on this as it is now one of the most requested improvements?

<=-=Nov 3 2011 12:45PM=-=>

I’d like to be able to declare an inline scalar function (above a query). The compiler could desugar it if it wanted to. It would make things more readable in certain cases.

<=-=Jan 25 2012 12:42PM=-=>

It’s ludicrous that after so many years we still only have such a useless implementation of scalar functions and not an inline version. I wonder how many total man-hours have been wasted on this, when an inline scalar UDF seems like such a staggeringly simple thing to implement in the database engine compared to the other UDF variants. The existing ones are almost guaranteed to screw anyone who comes across them, at least at first, since there’s no indication that they slow your performance down by orders of magnitude.

<=-=Sep 14 2012 3:25PM=-=>

This is also an obstacle for using scalar functions for constant values:

http://devplanet.com/blogs/brianr/archive/2008/02/13/sql-server-constants-the-constant-struggle.aspx

<=-=Oct 2 2012 8:29AM=-=>

I would also like to see this fixed. Using scalar funciton is sometimes very straightforward solution, but if this compromise performace they are quite useless.

<=-=Feb 5 2013 8:04AM=-=>

This issue has been “in the oven” for a while; where exactly does this stand? Is this being looked at for the next iteration of SQL Server?

<=-=Apr 27 2013 4:56PM=-=>

An excellent idea. We’ve all been waiting for the deterministic/non-deterministic difference to be worked correctly since it was announced for SQL 2000!

Like the inline TVF solution, just treat them like macros.

Or hey, implement nearly the same function AS a macro – except that I suppose we then want some way to make the macros global.

BTW I just (re)posted “constants for TSQL” as another suggestion. Using even scalar functions may or may not allow them to be used for constant values, unless either a constant keyword is introduced or the compiler gets a lot smarter.

<=-=Nov 15 2013 6:23AM=-=>

Microsoft has not updated this for almost 6 years – please can we have an update? If the inline scalar UDF is simply expanded in the same way as C macros it should hopefully not be too complicated to implement. Many keen and enthusiastic SQL Server users and evangelists would love to see this idea, so please can Microsoft show their continued committment to the core SQL engine by implementing this idea.

<=-=Nov 15 2013 7:02AM=-=>

Hi Will,
This is still a useful idea. I use the inline functions workaround all the time but it makes the query difficult to read.
And, yes it would be similar to C macros and the same, or similar to using an inline table workaround, posibly without the joins and other SELECT specific capabilities, just the expression.
So I’m Looking forward to it.
Regards,
Andy

�He that can have patience can have what he will.�
? Benjamin Franklin

<=-=Mar 2 2015 2:18PM=-=>

It really is time to fix the UDF. However, for those cases where we just don’t want to repeat an expression in a single query, a UDF is still a bad idea. I don’t want to reference an external object just to avoid repeating the expression. Would it not be better to allow an inline variable – query or statement scoped – of some sort to reference the expression? The implementation would be much easier – replace variable with expression and compile. It might not take 15 years.

https://connect.microsoft.com/SQLServer/Feedback/Details/1157688

<=-=Apr 3 2015 4:10AM=-=>

This can be used to improve code readability, if for example you have a certain value for a status or something, its better to write “where status=ActiveStatus()” than “where status=123456789”
Those two expressions should produce the same execution plan, if the only thing that the expression “ActiveStatus” does is to return a fixed integer. If you implement this now by using scalar value functions, you fall into the trap of the performance problems they have. in short, i would like to have the sql server equivelant of the public static const used in programming languages. I know i could implement this by table value functions but i would like to have something even cheaper to just gain code readability in certain cases

<=-=Jun 28 2016 6:01AM=-=>

Cmon its 2016… In memory doesn’t help. Inline is only a 25% bump. This would be awesome for building dimensions. For example Select ProductNum, GetProdDesc(ProductNum), GetProdAttrib(Productnum, Attrib1) as Color, getProdAttrib…. etc etc.

I don’t want to create 30 joins to get these attribs on the dimension and I could go etl less. This is a mess in SSIS…

I tried In Memory(Made it slower?) and In line (Select attrib from GetPRodDescIL(ProductNum, Attrib1) ) as Color, … and its not really any faster and certainly much worse for readability…

<=-=Feb 3 2017 1:34AM=-=>

It will soon be the 10th birthday of this idea. Please can Microsoft implement it?…..

<=-=Nov 2 2017 11:27AM=-=> Joe Sack mentioned in-lining scalar UDFs is being actively worked on, possibly for vNext.

This was mentioned during a PASS Summit 2017 session on Adaptive Query Processing.

Upvotes: 54

<=-=Sep 4 2007 12:29PM=-=>

If this is considered for a future version of SQL Server, please implement it according to the ISO SQL standard. In other words, implement the SQL Standard , which provides regular expression support through the operator [NOT] SIMILAR TO, which in the standard remains separate from LIKE.

<=-=Sep 5 2007 7:05AM=-=>

Like Steve, I would also like to stress that any implementation should be as per ANSI (SIMILAR predicate). The CLR functionality is not a relevant design consideration.

<=-=Oct 15 2007 8:43AM=-=>

This is a request we’ve heard a lot, and it’s of obvious value. I can’t promise when we’ll get to it—-it’s doubtful for this release—-but this is certainly on our radar.

Cheers,
-Isaac

<=-=Mar 24 2010 2:54PM=-=>

Hi,
I have resolved this request as duplicate of feedback item below:

https://connect.microsoft.com/SQLServer/feedback/details/261342/regex-functionality-in-pattern-matching?wa=wsignin1.0

—
Umachandar, SQL Programmability Team

<=-=Mar 24 2010 3:59PM=-=>

Resolved as duplicate.

—
Umachandar

<=-=Mar 25 2010 11:10AM=-=>

Resolved as duplicate.

—
Umachandar

Upvotes: 173

<=-=Sep 27 2010 2:23PM=-=>

Hi Itzik,
Thanks for your feedback on WINDOW clause. We will consider it for a future version of SQL Server.

—
Umachandar, SQL Programmability Team

<=-=Mar 21 2011 8:27PM=-=>

I’m currently migrating an established application from another database platform to SQL Server. There’s a big, powerful chunk of the app missing due to the absence of the WINDOW clause functionality.

I wouldn’t even want to think of how to implement this type of processing in SQLCLR, if that’s even possible.

And if WINDOW is standard SQL, shouldn’t it be included in the Microsoft product? Competitors have it.

<=-=Mar 2 2012 1:18PM=-=>

Reuse of Window Definitions with WINDOW Clause
This has been rolled up into our “Window Aggregates Enhancements” DCR for future consideration. Thank you for reporting it. All the information you provided has been captured for future reference.

Thanks,
Marc Friedman

<=-=Nov 14 2017 3:33PM=-=>

This is particularly useful for me when I have to update a window definition in a complex query with reused windows.
It reduces the chance for error.

Upvotes: 25

<=-=Jun 15 2010 3:54AM=-=>

I completely agree.

I’ve googled and can’t even find a work around to create a professional style report with a table of contents.

Would be extremely useful.

<=-=May 26 2011 8:15AM=-=>

Agreed. This needs to be built into the product.

In addition to what was said above, I would request that this table of contents page end up as page 1 when you export to PDF and end up as page 1 when you print. (The document map doesn’t print, and it only appears in the bookmarks tab in Adobe Reader.)

A hack of a workaround is mentioned here. I�m not wild about it. We have to deploy a custom assembly� we have to store this info in a database table� we have to account for storing the page count per report per parameter combination per user (in case there�s data level user security). So it would be a day or two of effort� hardly in the plan.
http://blogs.msdn.com/echarran/archive/2005/11/07/489797.aspx

<=-=Oct 15 2011 11:02PM=-=>

Thank you for providing this feedback. We will consider adding support for table of contents in a future release.

<=-=Jan 17 2013 8:07AM=-=>

Any date/realease or SP by which we can have this feature?

<=-=May 13 2013 1:17AM=-=>

This is a really important feature that is totally missing in Report Builder :/

<=-=Feb 1 2014 12:06AM=-=>

Has a TOC functionality been developed yet? If so or not, where can I find a solution for creating a table of contents from an access 2013 report? Many thanks

<=-=Jul 23 2015 1:50AM=-=>

MS support tell me – “The TOC is a significant piece of work so would likely require a lot (of votes)” so I’m not holding out much hope that we’re going to get this feature.

<=-=Feb 4 2016 6:46PM=-=>

Raaimund,
I am new to SSRS and have been searching for a work around to create TOC. Please share the code.

Upvotes: 527

<=-=Jan 27 2009 11:47AM=-=>

Thank you for providing this feedback. We will consider adding this functionality in a future release of SQL Server. If there are other rich formatting requirement that you have, please do add details to this item for those additional features also.

<=-=Dec 3 2009 11:21AM=-=>

When is this justify text going to be out? You commented on 1/27/2009 and it’s nearly a year since this has been resolved. How come the Silverlight team and accommodate the top requests within a few month but SQL team cannot do the same?

<=-=Mar 23 2010 12:37PM=-=>

In many cases a reporting tool is the best option to provide formatted data to clients, shareholders, and other individuals where the appearance of the report is as important as the accuracy of the data. It has to look good, not just be correct.

This is a serious deficiency, and while SSRS “isn’t a word processor” exporting reports out to a word processor or page layout tool for re-formatting is unacceptable.

For this reason, I’m loathe to reccommend SSRS to my clients where any such formatting is needed. At least two of my clients in the last two years decided on other tools simply because of this missing feature in an otherwise great reporting system.

<=-=Sep 4 2010 11:23PM=-=>

desperate need of this feature for legal documents

<=-=Oct 18 2010 6:08AM=-=>

I Think that is very important that Reporting Services have this option. For example We returned to Crystal Reports with a number of hours lost by our programmers. It is unacceptable as other functions have priority over an issue that can be considered basic.

<=-=Dec 7 2010 6:11AM=-=>

We would like to design our reports with SSRS but as long as Full Justify text is not possible we’ll still use Crystal.

<=-=Dec 17 2010 2:02PM=-=>

I need Full Justify Text for my mutual fund client’s sales brochures published from SQLServer. I’m trying to keep them from going to Crystal, but they have a serious need for the Justify feature and may have to Crystalize. That would be such a shame.

Alan

<=-=Jan 31 2011 7:21PM=-=>

Any news on this bug? it’s really inconvenient….

<=-=Feb 2 2011 3:28PM=-=>

Kindly update on this bug. This is something which is really important to be fixed asap.

<=-=Mar 6 2011 1:59AM=-=>

It has been there for more than two years now and no comment from Microsoft since been posted, so I really want to know what is the status of this suggestion and is it really so hard to take such long period?

<=-=May 9 2011 3:52PM=-=>

Thanks for all the additional feedback. To shed some light on why the feature is not as trivial as it may seem, we need to consider it across the various rendering extensions (HTML, GDI, PDF, and so on), among other considerations. The high vote count does help us prioritize this feature for a future release.

<=-=Jul 25 2011 10:50AM=-=>

Will this be included in Denali? It is critical and even if it is not trivial to implement, try telling a client that a basic feature such as this is too difficult to implement.

<=-=Aug 26 2011 1:50PM=-=>

I second Brian’s comment. Clients don’t necessarily care how complicated the feature is to implement…they just know that it’s possible in Microsoft Word, so it can’t be THAT hard. :-)

Any light at the end of the tunnel for this one? Denali perhaps?

<=-=Sep 4 2011 8:56AM=-=>

This is a major issue! It is hard to explain to our customers that it can’t be done in this reporting tool. Usually the reply is “then let’s use another reporting tool”.

<=-=Oct 7 2011 2:27AM=-=>

Is there any solution now ?
Todays, we can make fire with an IPHONE but we can’t justify a text in a report.
Then Go Back to Crystal Report

<=-=Nov 24 2011 4:56AM=-=>

As far as I can see today, using Denali RC0, Reporting Services are basicly the same and NO, there’s no fully justify feature.
What’s more it does not work the “align=”justify" HTML option (in which I hoped).

<=-=Dec 1 2011 3:58PM=-=>

Even if it is not easy to implement, it is a BASIC function requested for years! I cannot understand why it is still no available. Many users (and me too) have to use another reporting tool because of this missing feature… Have the project manager some Crystal reports Equities? ;-)

<=-=Dec 14 2011 6:14AM=-=>

I also need this feature for legal documents. When this bug will be fixed?

<=-=Dec 16 2011 9:17AM=-=>

I really, really need of this feature to ! Some clients don’t understand why their generated documents are not justify whereas they have it on MS Word, they think i’m stupid !!

<=-=Jan 25 2012 11:22AM=-=>

I am a report developer for one of the largest oil producing companies in the county. I just returned from a meeting in which I had to explain that SSRS could not produce a report with fully justified text. I assure you this is not a trivial request. Please move this up on the list of priorities and give it the attention it deserves.

<=-=Jan 30 2012 2:22AM=-=>

Please make this feature. :)

<=-=Mar 1 2012 7:47AM=-=>

No text justification in such a product? That’s a shame ! Even Access can do this !

<=-=Mar 21 2012 9:10AM=-=>

Please provide this feature – we are about to lose a large contract to convert to SQL Server Reporting Services because this basic feature does not exist. Even trying to include HTML tags with the justify attribute does not work in SQL RS. I really, really hope that this feature has been added to DENALI or somebody has missed the boat.

<=-=Mar 21 2012 11:35AM=-=>

Just confirmed…this is NOT part of Denali (SQL Server 2012 Reporting Services) – so much for including the feature in a “later release”. Are we now going to wait for SQL Server 2016? Please put this is a service pack release for SQL Server 2012!

<=-=Mar 22 2012 3:14AM=-=>

I just tried a before reading Shardenazar’s comment and got disappointed. I forgot the html markup type deliberately cripples almost everything – security has priority. Still, fixing html markup would be an easy solution.

A control IMHO can’t be a solution – didn’t its output need to be a bitmap by design?

I’ve often thought the sources of some of the SSRS should be public – would offer an alternative in so many cases – extensibility documentation – unsortable excel exports – etc etc…

Sorry, can’t resist: [quote] Even Access can do this ! [/quote] Hey – no insults below the belt please!

<=-=Mar 22 2012 5:26AM=-=>

Sorry – I didn’t post that I tried that as well – to no avail…..I tried the

and html tags – none work with justify.

<=-=Apr 2 2012 8:01AM=-=>

Hello,

I was waiting for full Rich Text support in Denali but disappointed it does not support.
Please add this functionality I need following features like :

1. Render tabular data
2. Render Image with the data
3. Format Text with different colors and fonts

In short I would say it should be complete Rich Text box.

Thanks.

<=-=Oct 4 2012 4:53AM=-=>

Sorry, Shardenzar or somebody else can help me how make custom control with this hint?
http://www.fyireporting.com/forum/viewtopic.php?t=552
TIA,
marco.

<=-=Feb 18 2013 7:05AM=-=>

Pretty poor show that this is still missing in 2013, it was reported in SSRS 2005…

<=-=Mar 5 2013 6:35AM=-=>

Could someone @Microsoft tell us why this very basic feature is still missing after more than seven years??? I really hope it will be fixed soon in an upcoming SP…

<=-=Mar 20 2013 4:22PM=-=>

Full justify for text is something essential that is missed in SSRS.

<=-=Jul 25 2013 7:16PM=-=>

Please out clients ask for this feature

<=-=Aug 2 2013 4:18AM=-=>

We work in financial services providing BI solutions – we have built a solution on top of MS SQL Server and SSRS. It all works great EXCEPT NO TEXT JUSTIFY.
We have a potential new client and they are insisting on text justification which we cannot offer them. Even if this is only supported in PDF it would be a great help for us. Please consider this for an upcoming release or patch….

<=-=Nov 20 2013 1:10PM=-=>

We do need it really.

<=-=Feb 7 2014 1:55AM=-=>

Can’t believe this still hasn’t been addressed?

<=-=Feb 14 2014 4:45AM=-=>

Can Microsoft at least provide a workaround for this urgently.

<=-=Mar 6 2014 4:53AM=-=>

It’s a must!! Even most of old reporting systems has this feature (nor SSRS)

<=-=Mar 10 2014 2:21AM=-=>

Tengo el proyecto a la mitad de desarrollo. Y ahora resulta que los informes no pueden justificar. Es una verguenza

<=-=Mar 10 2014 9:56PM=-=>

+1
I was embarrassed explaining to our client that SSRS can’t justify text, despite devs asking for this feature for 5 years.
None of the workarounds here or on the web are tidy enough to be taken seriously.
MS – please at least let us know – do you plan to implement this or not?

<=-=Jun 13 2014 5:48AM=-=>

Just export to Word and then justify

<=-=Aug 13 2014 10:05PM=-=>

In case anyone was wonder/hoping, this feature did not make it into SQL 2014. The only item on the “what’s new” page is Chrome support. My interpretation is that was the end of the road for core SSRS enhancements, so I’m not holding my breath for this one. As awful as Ernesto’s suggestion is, that’s the best we can say …

<=-=Apr 9 2015 11:23PM=-=>

Dear Sql Server Team

We have many large clients that require this feature – just this one enhancement would make SSRS 1000x more useable and enhance our sales and yours. I know that you have stated that it wont be done unless you can roll it out across all formats (pdf, excel, word, etc) but just supporting pdf as

Upvotes: 577

<=-=Dec 13 2007 3:33PM=-=>

Dear Itzik.

Thanks a lot for your feedback… and the strong voting support for this feature. Indeed, as you know, I am very much in favor of extending our functionality in this area. For a variety of reasons we did not get this into SQL Server 2008, but we are certainly looking into it for a future release.

Keep the votes and comments coming…
Michael

PS: My apologies for the late official reply…

<=-=Mar 10 2009 4:37PM=-=>

I am a big fan of the ranking functions and partition by clause. I used them extensively. It was a fantastic inclusion in 2005. However, I can’t tell the number of times I wished DISTINCT worked with the count function. I can’t believe it wasn’t included in 2008!! So I’ve added my vote to get this in ASAP.

<=-=Sep 15 2009 7:08AM=-=>

yes this would be very helpful. I’m sitting here frustrated over my inability to do this.

<=-=Dec 14 2009 5:53PM=-=>

Count(DISTINCT Col1) OVER (PARTITION BY Col2) would solve my problem perfectly! I need to know if the number of items is different than the number of distinct items. Solving this another way will be tricky.

<=-=Dec 14 2009 6:05PM=-=>

If we could nest ranking functions this would do the same thing (of course, we can’t so this code doesn’t work):

SELECT
X,
Y,
Rnk = Max(Dense_Rank() OVER (PARTITION BY X ORDER BY Y)) OVER (PARTITION BY X)
FROM (
SELECT ‘a’ x, 1 y
UNION ALL SELECT ‘a’, 1
UNION ALL SELECT ‘a’, 2
UNION ALL SELECT ‘b’, 1
UNION ALL SELECT ‘b’, 1
) Z

<=-=May 6 2011 8:21AM=-=>

I use the over() clause quite a bit and it’s been very useful, however only now am I really wanting more functionality from it, so figured I’d bump this thread. I’m good with workarounds, and not entirely sure I, “need” additional functionality, however it would surely lend itself to more elegant solutions.

- sql 2008 r2, mainly using over() for data warehousing, or tweaky ad hoc reports,
- DBA, New Belgium Brewery

<=-=Nov 3 2011 12:38PM=-=>

It would be nice to be able to use the result of an OVER clause in a where clause too. I’m always having to make wrappers just to filter the result of an OVER clause.

<=-=Oct 31 2012 10:23AM=-=>

Say you are reporting on a price comparison engine.

In a given time period, you want a count of how many users clicked through to each website whose prices you compare.

On the assumption that users make at most one purchase per website per day, you want to dedupe the cases where users click through many times.

The Referrals table contains one row per click-through.

To get this deduped count for yesterday, you should be able to write

SELECT
COUNT OVER (PARTITION BY Website) AS DedupedUserCount
FROM Referrals
WHERE
ReferralTime >= ‘2012-10-30’ AND
ReferralTime < ‘2012-10-31’

This fails with the disappointing message

Msg 10759, Level 15, State 1, Line 11 Use of DISTINCT is not allowed with the OVER clause.

Plamen Ratchev provides a workaround (see Workarounds) using the DENSE_RANK function in a subquery and the MAX function.

But we should be able to do it as declaratively as the SQL grammar allows!

<=-=Jan 10 2013 12:47PM=-=>

I really hope this feature is a part of SQL Server 2012 because I just tried to use it in 2008 and came here to find it’s not supported. I’m using something like the code below to get the result.

SELECT COUNT AS DedupedUserCount
FROM Referrals AS R
GROUP BY R.Website
HAVING ReferralTime >= ‘2012-10-30’ AND ReferralTime < ‘2012-10-31’;

<=-=Mar 5 2013 7:04AM=-=>

It’s really frustrating that this feature is not included in SQL Server 2012 also !

<=-=Aug 14 2015 1:40PM=-=>

I know this is a much newer comment on this but we are currently on 2008 R2 for SQL Server due to business constraints and having this functionality would make solving my current problem so incredibly easy.

<=-=Nov 30 2015 2:39AM=-=>

You should fix this. It is not working in SQL Server 2014 also. This limitation was reported almost 9 years ago.

<=-=Jun 29 2016 5:51AM=-=>

This is many useful place being able to do a Select count distinct x Over… would make things so easy. Typical workarounds for this lack of functionality are counter intuitive.

Upvotes: 337

<=-=Oct 1 2007 2:40AM=-=>

I could not agree less.This is more of ‘SHOULD HAVE’ and every T-SQL developer would love SQL server even more should this be implemented.

<=-=Oct 4 2007 2:22PM=-=>

I agree. Microsoft, if you’re listening, please implement this!

<=-=Oct 10 2007 7:33AM=-=>

I think it is very important.

Alex Kuznetsov,
SQL Server MVP

<=-=Oct 22 2007 2:25AM=-=>

I agree. It would be a very nice and powerfull feature in SQL server 2008.

<=-=Oct 22 2007 5:57AM=-=>

Unless there is a very good reason not to, I urge you to implement it, it would be very useful.

<=-=Nov 4 2007 11:40AM=-=>

TVPs should definitely not be limited to being read-only. Without updateability, they will be perceived as an “impaired” new feature. As lead author in the upcoming Programming Microsoft SQL Server 2008 book by MS Press, I urge the SQL team to support updateable table value parameters.

<=-=Nov 14 2007 4:25PM=-=>

I strongly agree that this is much needed to help solve/improve many business problems. Implementing this functionality will lead to building better applications.

<=-=Dec 10 2007 3:45PM=-=>

Hi Erland,
Thanks for the feedback on this. We have recieved similar feedback from a large number of customers. Allowing table valued parameters to be read/write involves quite a bit of work on the SQL Engine side as well as client protocols. Due to time/resource constraints as well as other priorirites, we will not be able to take up this work as part of SQL Server 2008 release. However, we have investigated this issue and have this firmly in our radar to address as part of the next release of SQL Server. We appreciate and welcome the feedback here.

Srini Acharya
Senior Program Manager
SQL Server Relational Engine

<=-=Dec 10 2007 11:38PM=-=>

Please note that it was an explicit limitation in my suggestion that read-write
table parameters would only be available when a stored procedure calls
another. The READONLY limitation would still apply to client communication,
at least for SQL 2008. Surely that would reduce the amount of work needed
considerably, wouldn’t it?

<=-=Dec 19 2007 11:27PM=-=>

Well i really dont see huge benefits of having only a READONLY limitation in SQL 2008, like Erland rightly says at least have a READWRITE on the server side. Client sides are not as much a priority as much as server side is.They are good to have but server sides are must have.

<=-=Jan 30 2008 9:33PM=-=>

Yes I also whoud like it for similar reason
why I like collections in Oracle.
/Bjorn

<=-=Sep 25 2008 2:23PM=-=>

I just ran into this. It would definitely be helpful to call a stored proc from another stored proc and be able to pass a resultset from the inner stored proc to the calling stored proc.

<=-=Nov 11 2008 8:39AM=-=>

Contrarily to what other people said, I see strong benefits to read-only table-valued parameters. First and foremost being that they are convenient and fast as hell (if you tried doing the equivalent of an SqlBulkCopy with them, you know what I’m talking about).

On the other hand, I only see advantages to having them be read/write.

<=-=Dec 15 2008 10:49AM=-=>

If people want the choice of readonly or not, make this an option in the declaration, that way any efficiency benefits of readonly can be enjoyed when wanted, and the flexibilty to write to the table can be had the rest of the time

<=-=May 15 2009 4:09PM=-=>

Any possibility this can be implemented in sp2, before Kilimanjaro? (could be option to add readwrite, or require readwrite if want readwrite, so would not break or change any existing code). readwrite table parameter feature is extremely useful—remember how dependent server side programming is on what t-sql makes available, and the particular weaknesses of t-sql versus general purpose programming languages. readwrite tvps would be a huge benefit b/c compensating for a major weakness in t-sql.

<=-=Jan 19 2010 5:28AM=-=>

Totally agree. This would be very handy for consuming applications such as SSIS & SSRS. I have run into problems with sprocs on numerous occasions that do not adhere to an output contract and hence do not behave as expected. Please make sure that if you implement read/write OUTPUT parameters that the SSIS team is aware of them and can implement an OLE DB Source component that can use them.

<=-=Jan 30 2010 7:04AM=-=>

I agree with this. But there is a simpler change that would make life a lot simpler:
allow this
select into #temptable exec(@sql var)
Which means: capture the output if dynamic sql into a table, be a “normal” table or a temp table.

<=-=Jul 9 2010 3:53AM=-=>

i would like be able to chooose if the params will be read only (perhaps favoring performance) or not (favoring programability for sure)

<=-=Dec 3 2010 9:48AM=-=>

This is a fantastic suggestion. I especially like Erland’s argument that a status flag indicating success or failure for each record, which might be useful. And the fact that you might want to use the table as a work table without the overhead of copying the whole parameter table to a local table first, which is just a waste of resources.

On another note, anything that Erland Sommarskog recommends for SQL Server needs to be taken with great seriousness. If he has identified a weakness, we should believe him!

David Walker

<=-=Apr 8 2011 10:14AM=-=>

Extremely important feature for sql developers- please schedule for implementation as soon as possible.
Agree with fotis12- please allow specifying readonly | out.

<=-=Jun 15 2011 8:54AM=-=>

Yes, this is sorely needed due to the considerable limitations of UDFs. There are many times that I’ved wanted the tabular output of something that cannot be written as a UDF (most recently, restore headeronly), but in order to do so have to pollute the code with the declaration of a temp table or table variable… with 51 columns in this case, everywhere you want to be able to use it.

Not being able to encapsulate that SOMEHOW makes code both ugly and difficult to maintain.

At the very least, it would be nice to be able to create a table type and then reuse that type in code, taking the pain and maintainability problems out of insert-exec. For example:

create type MyTableType(i int, j int, k int)

declare @T table(MyTableType) = exec (string | procedure)

or, if relation assignment is never going to happen (another thing that really should be in the language…)

declare @T table(MyTableType)
insert @T exec (string | procedure)

<=-=Aug 23 2011 3:40PM=-=>

When the performance of parsing XML in SQL Server is well documented plus the added development effort in turning this into more primitive data types, I’m surprised that the work around proposed uses this approach.

<=-=Jan 23 2013 2:00PM=-=>

Your suggestion made my list.

I was thinking like this….

looser coupling for existing proc to proc calls:

Wouldn’t it be nice if there was a TSQL command that could return result sets from a called proc as an array where each member could be assigned to a table variable or each one could be accessed as one directly?

tighter coupling:

allow me to update a passed table variable and make it possible to return a table variable as an output

something in between:

allow for both approaches to work together somehow.

I also would like the ability to model a udt after an existing table and have it keep pace with that table as it is altered.

Anyway, you get my vote.

<=-=Jun 27 2013 3:03PM=-=>

This is a very useful idea and I would fully welcome modifiable table parameters. This would also work around the problem that Insert Into Exec cannot be nested (see “Cannot have nested INSERT … EXEC”). However Microsoft’s last feedback is in 2007 – can they update this suggestion with progress details?

<=-=Jul 29 2013 3:43AM=-=>

Please can this idea be included in SQL Server 2014 – it would be very useful.

<=-=Feb 20 2015 1:35PM=-=>

removing the READONLY restriction would definitely enhance the usefulness of table parameters. yes please!

<=-=Apr 15 2015 3:33PM=-=>

I cannot believe it is 2015 and this has not been implemented yet. I’m working on reports for the 2016 Summer Olympics and this feature would be incredibly useful!

<=-=Feb 2 2016 2:26AM=-=>

This idea (ID 299296) has now been open for NINE years, that should be enough development time to allow implementation. Please can this idea be included in SQL Server 2016, and please can Microsoft update the status. All the comments here are from experienced SQL Server users who are strong supports of the product, and it would be great if Microsoft could respect this support by providing regular updates.

<=-=Feb 3 2017 1:48AM=-=>

This idea is approaching its 10th birthday and it’s time it was implemented! I have spent many hours scripting work arounds using varchar(max) or XML, and implementing this idea would greatly improve the usability of SQL Server. Erland Sommarskog’s suggestion on 10/12/2007 means no client protocol work is required, so the required work is in SQL Server core itself. Please can Microsoft follow the suggestions and pleas of the experienced SQL Server users who have posted below. Will Rayer MCSE

Upvotes: 146

<=-=Oct 16 2006 11:24AM=-=>

Hi Erland,

Thanks for bringing this suggestion to our attention. This would definitly be a useful feature and we are considering implementing it in the next version of SQL Server.

Thanks,
Tomer Verona
SQL Engine Development

<=-=Oct 19 2007 2:15PM=-=>

Any update on this? I happened to see this in the event Blocked Process Report:

That is, here the call stack is available. Through the sqlhandle it is possible to retrieve
the objectids of the calling procedures.

If if it’s available this way, it can’t be too difficult to expose this in a DMV. Only exposing the
sqlhandle would not match with my observation with permissions above, but since you probably
only want this code in a few places, this could be dealt with adding extra privs with
certificate signing.

<=-=Apr 7 2008 12:18PM=-=>

This would be of use in the scenario I describe here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=189211

-Jamie

<=-=Oct 5 2008 3:28AM=-=>

yes, it would be really helpfull if we could do things like
using dbms_stack or dbms_trace in Oracle PL/SQL.
/B. D. Jensen

<=-=Mar 18 2009 10:03AM=-=>

Not sure if you have added this feature to SQL 2008, but please do add it to next release of SQL 2005.
Thanks!

Aamir

<=-=Jun 26 2009 12:33AM=-=>

It is a very useful feature, especially for debugging and tech supporting

<=-=Aug 5 2009 4:44AM=-=>

I have to agree with Erland. A callstack would be awesome. Someway of logging it outto an eventlog or table. I have been trying to figure out how to create such a thing to show the where the error occured back up through to the originating call.

<=-=Oct 15 2009 6:32AM=-=>

I’d find it useful for debugging purpose. Also, debugging option in SQL 2008 meets my need to some degree.

<=-=Feb 23 2010 8:19PM=-=>

In addition to a standard T-SQL callstack of functions/stored procedures, it would also be useful if the “call stack” could also “magically” indicate which if any *.NET methods triggered the stored procedure.

Suppose I have a VB.NET function like
Public Function GetDataSource() As DataView
’ return result of T-SQL Stored Called sp_makeInventoryDataSource
End Function

Then suppose sp_makeInventoryDataSource called a T-SQL function called fn_getItemName(itemID).

Then the calls stack for fn_getItemName should not only contain sp_makeInventoryDataSource but it should also show the VB.NET function GetDataSource and might look like:
[T-SQL CAll] fn_getItemName
[T-SQL Call] sp_makeInventoryDataSource
[VB Call] GetDataSource

<=-=Jun 4 2013 4:12AM=-=>

Guys, this feature is unavailable for us more then ten years. Please add it to SQL Server 2012.

<=-=Jun 4 2013 12:14PM=-=>

June 4th, 2013: “Voting is no longer enabled on this topic” – so is Microsoft about to give us this feature in SQL 2014 (2012 R2)?

+1 in lieu of the inactive voting button.

<=-=Apr 5 2016 11:28AM=-=>

Yes. Please. Pretty please. With a cherry on top?

We have an issue right now, where certain emails are going out from a sproc, but we cant find any reason why this sproc would be getting run.
It would be SOOOOO incredibly helpful, to be able to see the stack trace and see what the heck is calling the sproc.

<=-=Jul 23 2016 12:15AM=-=>

Yes, still a valid issue, needed it today, we have tons of existing code where we do it the hard way, passing in names from callers.

<=-=May 4 2017 10:34AM=-=>

Twelve years now, and nothing but crickets?

How many votes does something need before MSFT will seriously consider implementing it?

<=-=Sep 13 2017 11:42AM=-=>

If you wanted to vote, you can vote on this one instead:
https://connect.microsoft.com/SQLServer/feedback/details/2781373