July 14, 2022 at 12:40 pm
it gives error , is there any way that i can call it with out a schema. i have made the schema default also ,
but still it says unidentified object. so is there any work around that i can call the scalar function with out schema. in sqlserver 2019
July 14, 2022 at 1:50 pm
Just piling on with @ratbak
Every object in your queries should be qualified with the schema owner. If for no other reason, it reduces, in ever so tiny an amount, the overhead of running queries. Otherwise, the optimizer has to go and find the objects you're referencing, adding to the resources it uses, and reducing the time & effort it could be spending in optimizing your queries.
Not to mention, clarity of code, accuracy, added functionality, all the rest of the reasons why you should be qualifying your object ownership.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 14, 2022 at 4:23 pm
No: "Scalar functions must be invoked by using at least the two-part name of the function (<schema>.<function>)"
Why are you even trying to work around this? You should preface all database object references with the schema to avoid ambiguity.
+1 Million!
--Jeff Moden
Change is inevitable... Change for the better is not.
July 14, 2022 at 6:55 pm
One more pile-on. Specifying the schema means that the function is part of the specific database, and not a function in the T-SQL language. It's just required, besides being good practice.
July 14, 2022 at 7:55 pm
Another reason to always use the schema is that the default schema can change depending on schema scope. I stumbled across this recently and I was going to describe it, but this article does a better job.
Mainly I think this is interesting, but it's another argument for always specifying the schema.
July 14, 2022 at 10:34 pm
Just piling on with @ratbak
Every object in your queries should be qualified with the schema owner. If for no other reason, it reduces, in ever so tiny an amount, the overhead of running queries. Otherwise, the optimizer has to go and find the objects you're referencing, adding to the resources it uses, and reducing the time & effort it could be spending in optimizing your queries.
Not to mention, clarity of code, accuracy, added functionality, all the rest of the reasons why you should be qualifying your object ownership.
There was also an issue that would cause multiple execution plans to be generated based on the default schema of the user executing the code. If we assume the objects are all in the 'dbo' schema - and each user has their own schema, the each user that called that code would generate a separate plan.
Not sure if that issue was ever addressed - or if it could even be addressed, but it definitely makes it much harder to troubleshoot.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 15, 2022 at 6:33 am
By now, you know the solution.
Want to make it even better, do not use a SCALAR value function, but turn it into a (in-line)Table valued function !
In most cases, performance will be better ( by far ! ) as TVFs are handled in SQL Servers SET handling department 🙂
ref "Four ways to improve scalar function performance in SQL Server"
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 15, 2022 at 3:44 pm
By now, you know the solution.
Want to make it even better, do not use a SCALAR value function, but turn it into a (in-line)Table valued function ! In most cases, performance will be better ( by far ! ) as TVFs are handled in SQL Servers SET handling department 🙂 ref "Four ways to improve scalar function performance in SQL Server"
+1 Million again!
For even more by one of the masters of SQL Server, do the following search. You'll be amazed at how many ways scalar and muti-statement (mTVF for short) functions just absolutely screw your code to the floor when it comes to performance. He also has some recommended fixes (like converting to inline Table Valued Functions or "iTVF" for short).
https://duckduckgo.com/?q=Scalar+Site%3ABrentozar.com
--Jeff Moden
Change is inevitable... Change for the better is not.
July 15, 2022 at 4:41 pm
Understanding that is, schema name is required for calling scalar functions. i have changed my requirement.
i made three schemas s1 s2 s3, then i made s1 as public , so that when i use my logins2 whose default schema is s2 I should get s1 schema objects also, similarly when i use my logins3 whose default schema is s3, I should get s1 schema objects also.
i called the function s1.udfxyz which resides in s1 schema from logins2 it executed properly, but when I put the tables of s2 schema in s1 schema functon it throws error. What i want is that is, s1.udfxyz should be able to get to the tables of s2 schema when i login using logins2 and when I log in using logins3 then the s1.udfxyz should get the tables of s3 schema because table names are same in s2 and s3 schema. and s1.udfxyz function i want to keep comman.
July 15, 2022 at 7:24 pm
Understanding that is, schema name is required for calling scalar functions. i have changed my requirement.
i made three schemas s1 s2 s3, then i made s1 as public , so that when i use my logins2 whose default schema is s2 I should get s1 schema objects also, similarly when i use my logins3 whose default schema is s3, I should get s1 schema objects also.
Did you read this? https://nisorness.com/data/2017/12/7/schema
It is about stored procedures, not functions, but it explains the concept of schema scope, which you may be dealing with. (I can't follow your description so I am not sure).
I think that the default schema of the function overrides the default schema of the user. When the s2 user executes the s1 function, it first looks for the table in s1, if it doesn't find it, it looks for in in s2. If the table does exist in s1, then it will use the s1 table which may explain why you got a different result when you created the s2 table in s1. This is how it works for procedures. I haven't tried it with functions.
I think it is best not to try to write a function with tables references without a schema. The "always use the schema" applies to everything, not just functions.
July 17, 2022 at 12:29 pm
i understood in case of storedporcedure , but in case of the function.
I logged in using the user "login s2" then executed the function "s1.xyz" which is in schema "s1" and "s1" does not have any tables now.
Then it showed the table does not exist, that means it overrides the default schema of "logins2" which is schema "s2" and uses schema "s1". but the problem is it must search for tables in shcema "s2" which is default of "logins2", that is what it is not doing , and i want to get that.
pls see if it is possible.
July 17, 2022 at 9:46 pm
i understood in case of storedporcedure , but in case of the function.
I logged in using the user "login s2" then executed the function "s1.xyz" which is in schema "s1" and "s1" does not have any tables now.
Then it showed the table does not exist, that means it overrides the default schema of "logins2" which is schema "s2" and uses schema "s1". but the problem is it must search for tables in shcema "s2" which is default of "logins2", that is what it is not doing , and i want to get that.
pls see if it is possible.
Please post the code for the function. Also, why would the user for the S2 Schema be calling functions that live in the S1 schema?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 18, 2022 at 4:27 am
Because I have one product whose code is common for all projects which come to us, so we have to keep one database for one project.
and now we want to keep one database for all projects with different schema. With this every thing went well.
But the scalar function needs two part names. So we decided to keep all scalar functions in one schema(common schema) so that we do not have to change the two part names because we have common code for a product.
July 18, 2022 at 4:21 pm
but the problem is it must search for tables in shcema "s2" which is default of "logins2", that is what it is not doing , and i want to get that.
It seems that if the table from the same schema as the function does not exist, it then looks for a dbo table, regardless of the user's default schema.
I created a login called User1 and ran the following test. I thought it would revert to the user default schema, but that only works if the default schema is dbo. When I changed my default schema I found the same thing.
SET NOCOUNT ON
GO
-- Confirm my user name (I was previously connected as dbo so the test didn't work)
--User1
SELECT CONCAT('User Name: ', USER_NAME());
GO
ALTER USER USER1 WITH DEFAULT_SCHEMA = [DBO]
GO
DROP FUNCTION IF EXISTS User2.GetSchema;
DROP TABLE IF EXISTS User2.WhichSchema;
DROP TABLE IF EXISTS User1.WhichSchema;
DROP TABLE IF EXISTS dbo.WhichSchema;
GO
DROP SCHEMA IF EXISTS User2
DROP SCHEMA IF EXISTS User1
GO
CREATE SCHEMA User1
GO
CREATE SCHEMA User2
GO
ALTER USER User1 WITH DEFAULT_SCHEMA = User1
GO
CREATE TABLE user1.WhichSchema
(SchemaID INT,
SchemaName VARCHAR(10)
);
INSERT user1.WhichSchema
VALUES (1, 'User1');
CREATE TABLE user2.WhichSchema
(SchemaID INT,
SchemaName VARCHAR(10)
);
INSERT user2.WhichSchema
VALUES (1, 'User2')
GO
CREATE TABLE dbo.WhichSchema
(SchemaID INT,
SchemaName VARCHAR(10)
);
INSERT dbo.WhichSchema
VALUES (1, 'dbo')
GO
CREATE FUNCTION user2.GetSchema ( @ID INT )
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @SchemaName VARCHAR(10);
SELECT @SchemaName = SchemaName
FROM WhichSchema
WHERE SchemaID = @ID
RETURN @SchemaName
END;
GO
-- SELECTS FROM THE FUNCTION DEFAULT SCHEMA TABLE
SELECT user2.GetSchema(1);
GO
-- DROP THE DEFAULT SCHEMA TABLE OF THE FUNCTION
DROP TABLE User2.WhichSchema;
GO
-- REVERTS TO DBO
SELECT User2.GetSchema(1);
-- DROP THE DB0 TABLE
DROP TABLE IF EXISTS dbo.WhichSchema;
GO
-- FUNCTION FAILS TO FIND user2 or dbo table and fails
SELECT User2.GetSchema(1);
-- SELECT QUERY RETURNS DATA FROM User1 table
SELECT *
FROM WhichSchema
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply