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: 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: 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: 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: 174

<=-=Oct 12 2006 2:21PM=-=>

Dear Adam,

Thanks for your feedback. I think this idea has quite a bit of merit. You would like a way to tell the query processor to evaluate query subexpressions in a constrained order. There is no perfect workaround right now. You could use a multi-statement TVF, but that is hard to program and hard to read. Your workaround requires an extra sort. We’ll consider this as an improvement for a future release.

Regards,
Eric

<=-=Feb 4 2010 8:40AM=-=>

SELECT x.EmployeeID,
(
SELECT COUNT_BIG(*)
FROM HumanResources.Employee AS e
WHERE e.ManagerId = x.ManagerID
) AS theCount
FROM HumanResources.Employee AS x
WHERE x.ManagerID IS NOT NULL;

is also 4 logical reads – no materialization needed.

In general, I’m not so sure about the need for this. If I truly need to materialize an intermediate result (which is typically quite small) I’m happy using a table variable or temporary table. A materialized CTE or derived table feature would probably just be abused, and some would no doubt go on to decry the lack of statistics or indexability…

I quite like the CTE/derived table as a sort of inline view, with the optimizer free to do clever stuff with the overall plan. While this facility might result in better plans on a few occasions, we have a workaround, and the change might affect the wider quality of plans produced.

I may well be wrong though ;c)

<=-=Jan 2 2012 3:59PM=-=>

Having this kind of hint would be very helpful for tuning complex CTE queries (especially self-joins on a CTE) where SQL Server today always chooses sub-optimal plans with huge I/O cost.

@SQL_Kiwi – the temp table workaround is usually much slower (in my experience, at least 30% slower) than an inner query that’s been materialized. Table variables may be faster, but they prevent SQL Server from using a parallel query plan which negates any speed benefit.

Of course, in addition to a hint, it’d be ideal if SQL’s optimizer also got smarter about the I/O cost involved in repetitive evaluation of an inner query, to ensure that there were fewer cases where the hint was needed.

<=-=Jul 27 2012 9:22PM=-=>

In my experience, some sort of intermediate materialization (I generally force it by using temp tables), has been the trick to solving executions plans generated by sql server — usually the problem arises with multiple ctes or table functions in joins with each other. (One query took over 48 hrs, but with intermediate materialization, took 18 seconds.)
But perhaps it could be solved by better optimization (or an option to let sql server take a longer time to optimize? as in computer chess)

<=-=Nov 15 2012 5:54PM=-=>

I found this “bug” when using various grouping sets over aggregated data from a base set, all within CTEs. What should have been a few seconds became 40 minutes when I added the 3rd level CTE. In my case, 100,000 rows aggregated to just 120 rows within the 2nd-level CTE and 8 rows in the final CTE. The final CTE was used twice in the SELECT, once as the left side of an OUTER Join, then once more for a reason I don’t quite recollect. Rewrote the CTE’s into Table-variables and seconds later was done…

From inspection of the Explain Plan, the base query was being executed repeatedly, despite the fact that nothing warranted it. The optimizer should be smart enough to see when each CTE needs reevaluation or can be executed, then materialized internally and presented as a stable set of data to the next level CTE.

Definitely +1.

<=-=Dec 27 2012 1:55PM=-=>

I ran into this issue today and was able to use the “TOP (2147483647)” trick to reduce the run time of a complex query from hours down to seconds. I could not have used a temp table or table variable here – this needed to be a single query due to an application restriction.
I believe this hint would be extremely useful in a limited but significant number of situations. I agree with Paul that it would likely be abused/misused by some people (as many hints are), but I don’t think that’s a good reason to not implement it.
+1

<=-=Apr 4 2013 4:46PM=-=>

Please consider the ability to specify the materialized table’s PK with the materialization hint else I’m still out of luck for some cases. I am constantly using temp tables to work around this issue (e.g. no good for use within a view). Thanks!

<=-=Apr 11 2013 10:13PM=-=>

I spend so much time working around the optimiser when it is doing the wrong thing that I would like a -RULE BASED- hint. When applied, the optimiser would attack the query based on the order of the tables, joins and predicates. Oracle used to have such a thing and it was almost always preferable to the Cost based optimiser because it led to predictable query plans that wouldn’t suddenly stop performing as your data changed or grew.

I know that MS has gone to the trouble of writing a cost based optimiser for people who can’t write sensible SQL but it thinks it is so smart that when it does the wrong thing, it can be very hard to persuade it to change its mind.

<=-=Apr 12 2013 1:23AM=-=>

For example; I have a table with bill details in. About 200,000,000 records. I loaded a new bill into it. The bill had 2,000,000. After I load it, I check that the correct dollar value is loaded by running something like this

select sum(Dollars) from BigTable where BillID = 123456

This actually performs quite well, it correctly uses the index on BillID to identify the appropriate records even though the stats have no information about the Bill that I just loaded.

But then a few days later, after the stat refresh job that I run has been around and done its work, the same query performs appallingly. The query plan knows that there are 2,000,000 records to return and decides to scan the primary key which is on a whole different unrelated field. This takes FOREVER. Now I have to persuade SQL Server to use the index on BillID for a very simple query that looks for a single BillID.

(One way to do this is to parameterise with a variable @BillID in the where clause then the optimiser does not know which BillID it is getting and optimises for an average bill. The average bill is much smaller than 2,000,000 records).

A simple rule based optimiser would use the index on BillID when searching for a single bill. Every time! No queries sometimes taking 10 seconds and sometimes taking 20 minutes.

<=-=Mar 25 2014 6:51AM=-=>

It’s a shame MS haven’t decided to go with this. You could go with Paul White’s argument that the places for materialisation are TVFs and temp tables but that condemns us to a world in which Views can never go beyond a certain complexity without fracturing their definition into a mess of over-verbose TVFs.

Views, with their server centrality and set based efficiencies are potentially a great place to program a lot of detailed business logic that’s often done at the app layer at the moment, but refusing to allow key tuning options like this sells them short.

<=-=Dec 11 2014 6:18AM=-=>

Same thing here. I have the following ;with mainData (column1,…)
as
( SELECT col1, …
FROM tableA
JOIN tableB
ON ..
JOIN tableC
blah blah )
select col1, …
from mainData CTE

LEFT JOIN vw_tableX rtg
ON rtg.col1 = CTE.col1 and rtg.col2 = CTE.col2

When I execute the first CTE as a temp table and then do a separate statement table join with the vw_tableX then it runs in 50secs. Running the statement as is takes close to 1h. I need to a way to have the sql run faster instead of splitting into 2 statements. I have tried options with TOP (2147483647) but doesn’t do the trick. Pls provide as an optimiser option. The optimiser is very smart but not always…

<=-=Apr 17 2015 4:48AM=-=>

If MS do provide a hint to force materialization of CTEs, it would provide a simple workaround for the problem with non-deterministic functions in CTEs raised by Itzik Ben-Gan in Connect Bug #350485.
Ideally, the optimizer should detect the use of potentially side-effecting functions in a CTE and materialize the intermediate results automatically.

<=-=May 12 2015 7:05PM=-=>

I work a lot with SSRS where you tend to need to load a ton of logic into a single SELECT, so I run into this issue a lot. As others have noticed the TOP workaround is not always effective.

The only reliable solution I’ve found is to convert the CTE to a Multi-Step Table Valued function with a Primary Key. I got this idea from Bogdan Sahlean’s answer:

http://stackoverflow.com/a/13098687/1787137

It request a schema change, involves a lot of repetitive coding and leaves you with an inflexible architecture, so I resent every time I have to churn through this technique. But it does work with stunning performance benefits.

From my testing, the Primary Key component is critical to performance. If I cant see an obvious one I create one through concatenating columns (yet more code …).

It’s all insanely frustrating – while I hope that it does get resolved in some future “vNext”, after almost a decade I’m not holding my breath. Whatever – the slowing pace of peoples SQL upgrades means I’m resigned to slogging through this for years to come…

HTH

<=-=May 12 2015 7:06PM=-=>

Typo in para #3: “It request …” should read “It requires …”