November 11, 2015 at 6:07 am
Hi Experts,
I need to change the default schema from dbo to abc. My aim is that whenever i select an object under abc schema it should come up wiithout prefixing the schema name.
I tried
alter authorization on schema::abc to rkn
alter user rkn set default_schema =abc
no success ,please help
TIA
November 11, 2015 at 6:57 am
Ratheesh.K.Nair (11/11/2015)
Hi Experts,I need to change the default schema from dbo to abc. My aim is that whenever i select an object under abc schema it should come up wiithout prefixing the schema name.
I tried
alter authorization on schema::abc to rkn
alter user rkn set default_schema =abc
no success ,please help
TIA
alter user [rkn] with default_schema = abc;
However, you really should use the two-part naming convention (schema.object) in all your queries. It make it so SQL Server doesn't have to perform checks to determine which schema the object is in before executing the query.
November 11, 2015 at 8:01 am
I agree, you should use two part name [schema].[object_name].
why you should use 2 part name ?
Consider a scenario where you are searching for a table that doesn't exist in abc schema but do exist in dbo schema. Now since sql doesn't finds the object in default schema it will look under dbo schema. However, if you are using 2 part name sql know that it only needs to look into schema matching "first part" of the name.
November 11, 2015 at 11:13 am
have2much (11/11/2015)
I agree, you should use two part name [schema].[object_name].why you should use 2 part name ?
Consider a scenario where you are searching for a table that doesn't exist in abc schema but do exist in dbo schema. Now since sql doesn't finds the object in default schema it will look under dbo schema. However, if you are using 2 part name sql know that it only needs to look into schema matching "first part" of the name.
I put together a test quite some time ago to measure the performance difference of one-part names to two-part names over multiple executions. It ran right around 8% better for two-part names for short procedures executed many times. Well worth it, in my opinion.
November 12, 2015 at 1:34 am
Resolved. Had to remove sysadmin.
November 12, 2015 at 2:24 pm
Ratheesh,
I am confused, how sysadmin is related to default schema ? can you please elaborate.
November 13, 2015 at 1:24 am
Members of sysadmin get their default schema overridden by whatever is set at the user mapping level to DBO.
The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin
fixed server role. All members of the sysadmin fixed server role have a default
schema of dbo.
November 13, 2015 at 7:13 am
Cool, I was not aware of it. Thanks Anthony.
November 13, 2015 at 9:44 am
No matter what server-level permissions a user has, you should still use two-part naming conventions. It's just good practice and avoids the engine having to do the extra work of figuring out what schema should be there. Granted, it isn't much work, but every little bit helps. After a while, it just becomes second nature to include it all the time. It's a good habit to have.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply