[ACCEPTED]-How can I remove the dbo prefix from my table names when I write my sql statements in SQL Server?-sql-server

Accepted answer
Score: 33

Actually you should leave those dbo. statements 3 because you SQL will be faster since the 2 optimizer doesn't have to lookup the schema

Check 1 out this link also Performance Impact of Procedure Calls without Owner Qualification

Score: 6

It's actually beneficial to leave the dbo. prefix 11 in place - after all, in SQL Server, you 10 could have several schemas (the "dbo." thingie) with 9 the same table name, e.g. dbo.MyTable, joe.MyTable, frank.MyTable.

If 8 you then issue a SELECT (list of fields) FROM 7 MyTable, SQL Server has to first figure 6 out which of the "MyTable" tables you really 5 mean --> this costs time, specifying right 4 off the bat you want "dbo.MyTable" will 3 SAVE you time.

OK, not a lot on a single 2 query - but SELECT queries are QUITE frequent 1 and it all adds up!

Marc

Score: 3

The bounty question isn't exactly the original 7 question.

The current answers do not contain 6 enough detail.

None of the answers actually 5 address the question of how to remove the dbo 4 prefix or change it to something else.

dbo is 3 the schema. You can't remove the schema 2 from a table. You can alter it. There are 1 many examples. Here is one: How do I move a table into a schema in T-SQL.

Score: 3

just like this, if all your table are in 2 dbo schema

use mydatabase
go
select * from mytable

if you have multiple databases 1 you can do it too:

select * from mydatabase..mytable
Score: 1

Unfortunately if you want to get best use 22 out of Microsoft SQL Server you need to 21 put the dbo. in many places. When creating 20 a view you can choose to make it a schema-bound view 19 which means the object names will be checked 18 at compile time, and as long as the view 17 exists the objects it references can't be 16 dropped. (You would have to explicitly 15 drop the view first.) If a view is schema-bound 14 it can also become an indexed view. Newer 13 MSSQL versions also support schema-bound 12 stored procedures, which have other advantages.

If 11 you want you can create other schemas apart 10 from dbo. But you still need to reference 9 objects explitly, as myschema.mytable and not just mytable.

You 8 also need the dbo. for user-defined functions.

Personally, I 7 would like to tell MSSQL that I don't intend 6 to use schemas, I am quite happy with the 5 classical SQL way of a single top-level 4 namespace for tables (at least within a 3 given database), and could you please just 2 assume dbo. if not specified otherwise. But 1 I haven't found a way.

More Related questions