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

Upvotes: 54

<=-=Nov 29 2010 8:49AM=-=>

Hi,

First of all, thank you for your feedback!

This is a great suggestion to improve the SQL Server Audit experience and we will try to consider this in our future release planning.

Thank you and regards,
Don Pinto
SQL Server Engine

<=-=Jul 29 2011 9:58AM=-=>

I can’t believe there aren’t more comments on this subject. I assume it’s simply because not many are currently using the auditing system, likely one reason is due to the limitation described here.

It seems like a logical step to include parameters within the additional_information field (which appears completely unused with regard to table level events). Perhaps this was overlooked due to performance issues?

While I’m on the topic, and understanding that CDC is NOT necessarily to be used for auditing, it seems odd to me that the information regarding WHO did something is not included with the data tracked.

You’d think that since there is no mechanism to determine who did something in CDC there would be a common way to stitch auditing information together with CDC to create a more complete picture.

Now, I’ve worked around that issue by creating a signed procedure, a SQL Server Agent job and a trigger (much simpler than previous manual change capture needs but still onerous) and a couple of sprocs.

I mean, this is CRAZY :)

We can see DML executed against a table, but not the parameters that indicate some kind of clue as to WHAT might have been “seen”. So the Audit system answers who did it, and when it was done, but not what was done.

CDC answers the question (not related to SELECT!) of what was done, and when it was done but not WHO did it.

Neither gives you a way to easily combine the results to get a good idea of what is happening.

And change tracking….really? The only way to get WHO did something there is to create a maintenance nightmare of INSTEAD OF triggers.

My take is this:

Audit: To be truly useful it MUST have the parameters used in the query somewhere and if it’s a performance hit then maybe parameter tracking is something that can be disabled.
CDC: To be truly useful it needs to pull the who from the logs during it’s log parsing (or give you a way to marry the data with the Audit)

NICE TO HAVE: For all three data specific event systems it would be of great use (I think) to have some way of (more or less) definitively stitching the data from the three systems together with some kind of common identifier.

<=-=Aug 3 2011 3:33AM=-=>

For SQL Server 2008 Microsoft released a bugfix:
http://support.microsoft.com/kb/971491/LN

DOES Microsoft think about releasing a bugfix for SQL SERVER 2008 R2 RTM too?

<=-=Aug 3 2011 3:35AM=-=>

SQL Bug #316148 (KB 967552): FIX: SQL Server 2008 Database Auditing shows query criteria as a parameterized value instead of the actual record.

<=-=Sep 16 2011 10:38PM=-=>

I second the request for a fix to this in SQL 2008 R2. This will be an extremely helpful feature, but only if it includes the actual data along with the queries.

<=-=Oct 26 2011 1:27AM=-=>

This is a serious problem for tracability of actions. We need to know what happened and who did it. To work around this we now use SQL Audit and CDC in unison, exporting to a separate audit database. It would be preferable to have just one source however.

<=-=Feb 10 2012 2:58AM=-=>

Team, any update here?

<=-=Feb 10 2012 2:59AM=-=>

This would be for SQL Server 2008 R2.

<=-=Feb 29 2012 1:39PM=-=>

Ditto what Atif said.

Any news about this? Are there any service packs to resolve this in SQL2008 R2?

We are using R2 and would like/need the audit to capture the parameter values.

<=-=Mar 14 2012 11:00AM=-=>

We are also using Microsoft SQL Server 2008 (SP2) – 10.0.4266.0 (X64)

Any news on this?

We would also like to get this fixed ASAP or is there already a fix which we can apply?

<=-=Apr 24 2012 11:51AM=-=>

Yes, is there any news on this?

I’m working on a project for Cook County, Illinois and this bug in the auditing is a major issue. We’re running SQL Server 2008 R2 v10.50.1600.1 (X64) Enterprise Edition (64-bit).

I have to say, Microsoft is catching some bad press in the office on this one. I mean it was fixed for SSE Ed 2008 back in 2009. Why is it still an issue in SSE Ed 2008 R2?

I have been looking for others having the same problem with the audit in R2 for months and finally found this. I keep getting told on other forums this isn’t a problem with R2 and yet there it is.

<=-=May 4 2012 12:39AM=-=>

I have heard from some reliable sources that this problem has been extended to “SQL 2012” too.
Still waiting for any response from microsoft team, Yes or No, anything so that we can plan accordingly.

(please provide any other link if I am posting on wrong thread.)

Thanks,

Atif.

<=-=Jun 10 2012 11:00AM=-=>

We’re on SQL 2008 R2 SP1 + CU5 and suffering this same issue. SQL Audit should extract the query’s param values – wether from autoparametrized text or even from user binds (ODBC/OLEDB, whatever). Our customer must pass a formal security certification review, and frankly this is really annoying, since this makes auditing on DML unusable, ihmo.

This is really critical for anyone trying to reach standards compliance.

For updates, we tried linking CDC with database_transaction_end extended events via transaction_id, but whoa.. that “tran_id” inside that lsn_time_mappings cdc table is not the transacion_id recorded in xevents or traces. So, this is a big stop.

<=-=Feb 9 2013 3:19PM=-=>

Any news about this? I need it in MSSQL 2008R2! It is crazy that it is fixed in 2008 and not in 2008 R2. 2012 will works?

<=-=Feb 11 2013 3:00PM=-=>

Thank you for your feedback. The auto-parameterized text defect should be resolved in all 2008+ releases with the latest SPs – please contact CSS if you have continue to have issues. For explicitly parameterized queries, it’s an item we are looking at when planning future releases of SQL Server. Thanks, Jack Richins

<=-=Dec 6 2013 7:55AM=-=>

I’ve the same error on SQL Server Enterprise with Service Pack 2 and i don’t have any alternative solution for this case.

<=-=Jul 29 2014 11:42AM=-=>

I’m having the same issue with SQL Server 2012 (specifically 11.0.3128). We need this for HIPAA compliance before we can move databases from SQL2005.

<=-=Sep 29 2014 2:10PM=-=>

We are running SQL Server 2008 (not R2) v10.0.5520 with Service Pack 3 and yet this issue is not corrected. Since SP1 documents a fix for this issue, is there a possible stand-alone hotfix that will work with 2008?

<=-=Jan 6 2015 11:21AM=-=>

We are running SQL Server 2008 R2 SP3 10.50.6000. This fix would be much appreciated as the Audit is not useful when it could be omitting critical information. Most of your customers have HIPAA/SOX auditing requirements that need this detail. The alternatives are much less efficient and troublesome.

<=-=Jan 8 2015 5:34AM=-=>

It’s absolutely useless for an audit functionality to not audit correctly what values are queried/modified… When will this be solved? How come there’s a hotfix for SQL Server 2008 but not latest versions? Is it ever going to be fixed?

<=-=May 4 2017 1:57PM=-=>

Auditing should report the parameter values even for explicitly parameterized queries. Otherwise it’s an incomplete solution.

<=-=Jan 4 2018 10:51AM=-=>

Dear MS, please fix 8-yr old bugs instead of releasing new features I will never use such as Operations Studio