42

This is a bug with the inlining of Scalar UDFs (or perhaps a bug with the query optimiser that is being exposed more by scalar UDF inlining). You can use WITH INLINE = OFF to turn off inlining for that function. Using a variable instead of a constant shows a bit more detail declare @myYear int = 0 SELECT dbo.Repro(@myYear); Node 5 defines Expr1000 = CASE ...


11

Cause SQL Server is trying to inline the function but failing due to the complexity. Using so much memory while doing so is unexpected and almost certainly a bug. A definition for the nested function dbo.IstFeiertag would be needed for a full repro. Workaround Add WITH INLINE = OFF to the function(s) definition. Once this issue is resolved, you should ...


10

Instead of using xp_cmdshell to execute queries against SQL Server, change your code to use stored procedures, user-defined functions, or even dynamic T-SQL called using sp_executesql. If you need to execute code against a different server, use a linked server. Finally, consider rewriting your code not to use a cursor if possible. Set-based operations in ...


10

This is a bug with SQL Server inline functions in 2019 RTM. DECLARE @Salary MONEY = 100440; SELECT dbo.ufn_GetSalaryLevel(@Salary) The expression in the constant scan (with CONVERT_IMPLICIT changed to CONVERT to make it runnable) is SELECT CONVERT(VARCHAR(10), CASE WHEN CASE WHEN CASE ...


9

You're encountering a known bug with scalar UDF inlining. You can disable inlining using one of the methods here (or by using a lower compat level, as you've discovered yourself): Disabling Scalar UDF Inlining without changing the compatibility level Or install CU2 for the permanent fix. ...even running a simple scalar function (which internally uses ...


6

Yes, an in-lined function can show different results than its out-lined (!?) counterpart. The following reliably reproduces the situation on my machine (Windows 10, 4 cores + HT @ 2GHz, 16GB RAM, SSD). Configure the database and session to use Read Committed Snapshot Isolation (RCSI): alter database Sandpit set read_committed_snapshot on with rollback ...


5

The link by @Ross will definitely help you determine syntax and what all of your options are, but for a little more guidance: There are two database roles that will probably be useful to you, they are db_datawriter and db_datareader. The two of them will probably handle the majority of your CRUD (Create, Read, Update, Delete) operations. ALTER ROLE ...


3

It sounds like it thinks DateColumn is already being used in a computed column, but that's not true. I checked the sys.columns system view for this table, and it has no computed columns at the moment. I realize that you checked sys.columns to verify whether or not "DateColumn" was a computed column and it doesn't show as being one, so I am going to ...


3

That will not work like that. test_table is a table of test_row objects, not 2 columns, so you need to create test_row objects from the 2 columns, like below: create or replace FUNCTION test_function ( p_year_start_in integer, p_year_end_in integer ) RETURN test_table AS v_ret test_table; p_start date; p_end date; BEGIN ...


3

Not really, however in Postgres and many other DBMS vendors you can comment on various database objects like: COMMENT ON FUNCTION my_function (timestamp) IS 'Returns Roman Numeral'; As far as I can tell there is no way you can comment on individual arguments for a function. For the presentation of the documentation, you may want to have a look at: http:/...


3

Consider this simplified equivalent: CREATE OR REPLACE FUNCTION ext.uf_converte_numerico(_input varchar(30)) RETURNS bigint LANGUAGE sql IMMUTABLE PARALLEL SAFE AS $func$ SELECT NULLIF(regexp_replace(_input, '[^0-9]+', '', 'g'), '')::bigint; $func$; IMMUTABLE, because it is, and for the reasons Laurenz explained. PARALLEL SAFE in Postgres 10 or later, ...


3

Since the function is VOLATILE (by default), PostgreSQL doesn't know that it will return the same value for every row in central.cliente, so it is evaluated repeatedly. Set the volatility to IMMUTABLE and PostgreSQL knows that it has to be evaluated only once: ALTER FUNCTION ext.uf_converte_numerico(varchar(30)) IMMUTABLE;


3

You can create a function and use it as the default for the column: create function f() returns text language sql as $$ SELECT string_agg (substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', ceil (random() * 62)::integer, 1), '') FROM generate_series(1, 20) $$; create table foo(id serial primary key, url_prefix text not null ...


3

I think you need: for _tbl in(select DISTINCT child_table from not_deferrable_constraints) loop If a table has more than one constraints, your code will try to recreate each one of them many times. Or just use a single for loop, I don't think you need the nesting. --Recreate not_deferrable_constraints for add_constraint in (select constraint_name, ...


3

You should definitely find a better way than doing DML through a function. You don't need to attach this process to a SELECT statement. You can just dump the results of the query to a temp table and use a CURSOR to iterate over that (just like you are doing in this function), calling a stored procedure to do what you are currently doing in this function. ...


3

Initially, it seems like this was a bug related to the new Scalar UDF Inlining feature added in SQL Server 2019, since you mentioned that disabling inlining resolved the problem. On further inspection, the function cannot be inlined due to the presence of a CTE in the function definition. Here's my (failed) attempt to reproduce the issue: USE [master]; GO ...


3

Procedure are a new thing in Postgresql as of version 11, there are two big differences between Procedures and Functions One Procedures can issue a commit or rollback and keep processing, Functions can not issue a commit or rollback. Functions create an implicit transaction any exception that occurs will cause a rollback, unless there is an ...


2

Kathi Kellenberger did a comparison of FIFO costing for Redgate in 2010. I used the winning entry method to calculate monthly inventory quantities and values for our Mexico operations. You can search for Set based speed PHreakery if this link does not work. https://www.red-gate.com/simple-talk/sql/performance/set-based-speed-phreakery-the-fifo-stock-...


2

Try something like: to_timestamp('21/12/2008 12:34:23','dd/mm/yyyy HH:MI:SS') I assume you want to catch any exception and return null instead, so something like: create or replace function my_to_timestamp(arg text) returns timestamp language plpgsql as $$ begin begin return to_timestamp(arg, 'dd/mm/yyyy HH:MI:SS'); exception when others ...


2

I suggest this alternative design: -- setup CREATE TABLE example (content_id serial PRIMARY KEY, content text); CREATE TABLE test (id serial PRIMARY KEY, tags TEXT, content_id int REFERENCES example); CREATE UNIQUE INDEX ON example ((md5(content)::uuid)) INCLUDE (content_id); -- ! -- usage INSERT INTO example(content) VALUES (NULL); -- allowed ...


2

In the case of an INSERT, there is no previous version of the row, so the OLD variable is unassigned. See the documentation: OLD    Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is null in statement-level triggers and for INSERT operations.


2

You must use a function, not a procedure: CREATE FUNCTION pro_update_last_read() RETURNS trigger ... The trigger must be defined FOR EACH ROW, and you cannot pass columns to the trigger function. The way you access the columns in the trigger function is via the NEW variable: NEW.sensor_id and NEW.report_time.


2

You can replace your TVF with a view (or keep the TVF, but use the view for your performance-critical sproc): CREATE VIEW CAOT_AllApplicationStatuses AS SELECT fkApplication, ev.pkApplicationEvent AS pkApplicationEvent, ev.CreateDate AS EventCreatedDate, CONVERT(VARCHAR(12), evt.fkApplicationStatus) As 'KEY', evt.EventDescription AS '...


2

Based on a quick and simple test: ALL_ARGUMENTS select * from all_arguments where position = 0 and data_level = 0 and argument_name is null and data_type = 'REF CURSOR'; position 0 is the first argument of a program unit. If it does not have a name/level (data_level = 0 and argument_name is null), then it is the return argument of the function, and you ...


2

Two big problems with using functions in a query are that cardinalities are not accurate and that the function code is called once per row. When setting a variable neither of these are a concern. So I would think, generally speaking, there would be no measurable performance impact.


2

A function may not see the same state of the database depending on whether it's VOLATILE or not, when this state is changing during the execution of the SQL statement it's called from, either because of the statement itself or because of another transaction if the isolation level is Read Committed. This is documented in https://www.postgresql.org/docs/...


2

There is a simple and a complicated way. The complicated way is to join with pg_roles and get the user name from there. But since PostgreSQL hackers don't want to type more than necessary, they came up with something else: Internally, every object is not identified by its name (which is mutable) but by its “object ID”. This is the number that ...


2

No, 'query optimizer fixes' only affects optimizer bug fixes that may result in unexpected query plan changes. It does not enable new features. The only way to enable scalar function inlining is to set the database compatibility level to 150: Enabling Scalar UDF Inlining You can make workloads automatically eligible for Scalar UDF Inlining by ...


2

One solution uses recursion: Setup CREATE TABLE dbo.Test ( instno integer PRIMARY KEY, amount money NOT NULL, duedate date NULL ); GO INSERT dbo.Test (instno, amount, duedate) VALUES (1, 1000.00, '20200130'), (2, 1000.00, NULL), (3, 1000.00, NULL), (4, 1000.00, NULL), (5, 1000.00, NULL); CREATE FUNCTION dbo....


2

You need to remove the clause STRICT according to the documentation : ...RETURNS NULL ON NULL INPUT or STRICT indicates that the function always returns null whenever any of its arguments are null. If this parameter is specified, the function is not executed when there are null arguments; instead a null result is assumed automatically... Use your code ...


Only top voted, non community-wiki answers of a minimum length are eligible