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: 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: 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: 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: 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 …”

Upvotes: 82

<=-=Mar 3 2010 10:44AM=-=>

Hi Erland,
Thanks for your feedback. We are aware of this request and hope to address it in a future version of SQL Server.

—
Umachandar, SQL Programmability Team

<=-=Jul 21 2010 5:36PM=-=>

This bug cost me and others about 8 hours of troubleshooting this week. And the query was working until statistics were updated. That’s the spooky part, that a new join order can cause an error in a previously-working query. This was compounded by the fact that the query was in a UDF and 1) error messages don’t list the line in the UDF but rather the calling SP and 2) I know of no way to see the execution plan of a UDF, which would have helped because I could have seen in the plan the conversion to float before the JOIN to the filtering table.

<=-=Mar 9 2011 12:08PM=-=>

Are you sure this is a bug emtucifor? SQL is a standard, and it has very specific rules about when expressions can be evaluated in a SQL expression. Do we know for a fact where it is specified that the optimizer is not allowed to evaluate expressions in the Select clause before those in the where clause?

<=-=Mar 11 2011 4:11AM=-=>

I agree that there is room for improvement here, though I don’t necessarily agree that less errors should be raised.

I once had to analyze a report that turned out to rely on a custom to only use integers in a de facto varchar(9) key field. It worked when the query was designed and simply stopped working after someone mistyped, and immediately marked the wrongly keyed record for deletion. The author contends that a query that works once should not throw errors unless the schema actually changes. I’d say the query shouldn’t have worked in the first place.

My suggestion: Throw warnings on any implicit char-int conversion (including some trace flag to turn them off). Throw warnings for char literals used as date literals unless they conform to iso8601 (was that the number?). Optionally define hints to allow specifying one way (“pester me until the query is safe against any data”) or the other ("if it works now, please ignore future data-driven complications) per-query. Potentially do these things differently under WITH SCHEMABINDING.

<=-=May 11 2011 2:30AM=-=>

I’d just like to add that this “bug” has also cost me countless hours of development time, systems that work fine for years suddenly decide (i presume after statistics have been updated) to invoke this behaviour and trigger a 4 hour session of digging through SQL procs

<=-=May 12 2011 9:20AM=-=>

Peter,

I am sure it’s a bug because it’s inconsistent. SQL is a mathematical tool that denotes the expected final result of a particular combination of particular data. The internal workings of the engine are supposed to be transparent. For example, if I use an INNER JOIN, it doesn’t matter to me whether the engine does that internally through a loop, hash, or merge join. It also doesn’t matter what order it chooses (which could be completely backwards from the order of the tables listed in the query).

If the engine’s internal choices about join order suddenly change the validity of an identical query on identical data, then there is a disconnect, because now the correctness of the logical/abstract SQL has become dependent on the internals that the query writer should not have to consider. What if your query worked with LOOP joins but broke when a HASH join was chosen? That would be incorrect.

The solution is to either throw an error every time or none of the time. I’d be happy to get an error at development time that forces me to convert to int explicitly. I’m happy for my query to work as Erland has suggested. But I am NOT happy for my query to sometimes work and sometimes not, when the query and the data haven’t changed.

<=-=Jul 31 2011 9:13AM=-=>

Comment to _ed. Your “workaround” doesn’t work for precisely the reasons being complained about here! There is no guarantee that the Where clause will be processed before the Select list is computed.

<=-=May 2 2012 2:43PM=-=>

I was doing running total and run into the same issue. Has this issue been addressed?
Running : SQL Server 2008 R2
I wounder what the next version of SQL Server would be 2012

<=-=Oct 18 2012 5:35PM=-=>

And it is AWFUL when the SELECT list is prepared from an XML element-value extract where the WHERE clause has excluded all possible string values and only integers are to be CAST. Cost me quite some time to fudge around and nest queries just to get around this. Until I found this, I thought it was my XML queries that were throwing off the WHERE…

Definitely a BUG when SELECT list comes before WHERE filters.

<=-=May 13 2014 5:46AM=-=>

I don’t think this can be classified as bug.
Firstly SQL Engine is design to work in most efficient manner. In the query above if server evaluates where clause (or join condition) first then it will need to store the result in some temporary space, then do type conversion to this result set. What will happen if there are millions or rows?

Secondly server can not pinpoint!! :) exact rows to select has to go through a table scan. If we add index to column A then in some cases it will quickly be able to apply cast only on selected items.

<=-=May 16 2014 7:44AM=-=>

To Tausif Ahmed – yes, it should be classified as a bug. There is a logical processing order that SQL engines should follow, and indeed Microsoft themselves include it in their documentation under http://technet.microsoft.com/en-us/library/ms189499.aspx. The WHERE clause should logically be processed before the SELECT clause.

SQL engines are allowed to rearrange operations to achieve better efficiency, provided that the results they produce do not differ from the results that would be produced if the query had been processed in the logical processing order. Clearly, there is a vast difference between producing a correct set of results (had the logical processing order been followed) and producing an error and refusing to provide any results.

<=-=Sep 28 2015 10:59AM=-=>

This is certainly not a bug. Stop relying on implicit conversion of data types! For even scenarios where it does work, it is often more expensive than it’s worth. I’d much rather have a traceflag that I can set that warns or even prevents all implicit conversions from happening entirely (is this possible query optimizer team?). Implicit conversion is a nice feature but not always worth the false-positives it produces like in the case described here, and in the case where large scans are required to convert data before joining or filtering.

<=-=Nov 24 2015 7:52AM=-=>

To Jonathan MacCollum – you seem to have completely missed the point of this bug report. The bug report is effectively saying “If SQL Server behaves correctly, it shouldn’t even be attempting to perform a conversion – so the fact that it’s raising a conversion error is indicative of the fact that SQL Server doesn’t behave correctly”.

The specific error here isn’t relevant (it could be, for example the use of a negative length specification to SUBSTRING). The point is, the database engine is performing an evaluation that is irrelevant to the final result set and, if the product was correctly following the logical processing order, it would never have attempted the evaluation.

<=-=Feb 24 2016 11:06AM=-=>

@Jonathan MacCollum, it most certainly is. See ErikEckhardt’s comment. He is dead on.

I have inherited a data warehouse. Some of the rows (out of millions) have this problem.

If I do a select * from table_with_bad rows, it works just fine. (note, no WHERE clause)

If I then take one of the rows from the above result set – remember – it did not error, and do:

select * from table_with_bad_rows WHERE row = bad_row, I get an error. I expect that, because I have “*” and probably one of the columns is “bad”.

But, if I do:

select varchar_column from table_with_bad_rows where row=bad_row – I get the error – even though I am selecting ONLY a varchar column! There is NO conversion.

As ErikEckhardt points out below, this is exposing the inner workings of the SQL Server engine which we should not have to worry about.

So, we have three things:

1. Different behavior if a WHERE clause is present or not.

2. An Error thrown, even though the SELECT does NOT contain the offending column.

3. No way to find the offending data – the result set does not appear in SSMS, and in other tools, like the excellent DB Solo, I can see the data, but, cannot find the column with the offending value. It is probably being converted by the tool. But, the error message from SQL Server doesn’t even bother to tell us WHICH column has the error.

<=-=Jul 12 2016 7:39AM=-=>

I am getting this exact same error when I put my query into a table valued function. I can run the query outside the function with no issue, but once I call the function supplying the exact same data, I get this issue. This bug was reported in 2010, it is now 2016. I think we can safely say MS isn’t doing anything about this. This is the very reason so many people are looking for non-MS solutions.