1

I am in the process of transitioning us from an MSAccess backend to a SQL Server back end.

Without really considering keywords our plan has an Admin, Order and Address schema.I have always read and been taught that you should never use keywords as schema, function,stored procedure, etc. names and that doing so will really hose you.

If I plan to make it standard practice to always explicitely define my schema (E.G. [Admin].CompanyInformation) then is using a keyword an issue?

Elias
  • 2,602
  • 5
  • 28
  • 57

2 Answers2

1

Once again, qualifying your object names with the schema is NOT related to the use of reserved words as identifiers. You will still encounter a problem using a reserved word as a name even if you qualify it with the schema name. Example:

set nocount on;
use tempdb;
go

create table dbo.[table] (id int not null); 
print 'creating dbo.table as a table';
go

-- the next two statements fail
select * from table;
select * from dbo.table;
go

print '';
print 'select from dbo.[table] works**';
select * from dbo.[table];

if object_id('dbo.table') is not null 
   drop table dbo.[table];
go

So - yes you should use the schema name. And yes - you should avoid the use of reserved words as object names. Doing the former does not negate the need to do the latter. And there are additional rules for object names that you should know - the rules for regular identifiers are https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-identifiers.

And even if you choose to NOT follow the rules, you will probably use software that you did not develop and that was not written carefully - which will fail to work correctly when it encounters an object name that is not a regular identifier. And THAT reason is the best reason for adhering to the rules for regular identifiers (one of which is to avoid using reserved words as names).

SMor
  • 2,830
  • 4
  • 11
  • 14
  • So pretty much the bottom line is that if I plan to connect to it through some third party software I should err on the side of caution and avoid using reserved words, but otherwise if it is qualified there is no problem. Seems like the only argument against it is that Microsoft says don't do it, because it might be annoying. I really don't want to use a reserved word, and am trying to come up with an alternative, but the names I have describe my schema very well. Do you feel similarly about the argument against using reserved words?? – Elias May 22 '17 at 19:47
0

No, this not an issue if you write [Admin] (not Admin).

P.S. Anyway you should always excplicetly define your schema because default schema is usually dbo

Mikhail Lobanov
  • 2,976
  • 9
  • 24
  • I completely agree. I meant qualify the schema instead of just doing 'SELECT * FROM CompanyInformation'. I will always explicitly qualify the schema. – Elias May 18 '17 at 18:32
  • I just read everywhere that shouldn't use keywords but besides being a pain in the butt, using them doesn't matter if they are delimited. – Elias May 18 '17 at 18:32
  • If you qoute your names there are no problem. in my company some table s have names like `[Accounts->Types?Get]` and some people like it – Mikhail Lobanov May 18 '17 at 18:34
  • @Elias I don't think you are using the work "keyword" correctly here. It appears that you have made some sort of connection between "keyword" and "schema". It is best practice to qualify any object (table, procedure, function etc.) names with the appropriate schema. This is regardless of whether you are using just one schema (like dbo) or many. – SMor May 18 '17 at 19:04
  • @SMor, I mean [https://learn.microsoft.com/en-us/sql/t-sql/language-elements/reserved-keywords-transact-sql](Keywords as defined by Microsoft) that are being used as [http://stackoverflow.com/a/529149/1504882](schema) names. – Elias May 18 '17 at 19:55
  • @Elias 404 on the first link. But your usage is still confusing. You should never intentionally used keywords or reserved words as identifiers. And you should qualify your object names with the appropriate schema. But those are 2 different concepts. It is a terrible idea to create a table with the name "table". Whether you do has no direct relationship to the idea that you should qualify your references to that with its schema name. – SMor May 18 '17 at 20:01
  • @SMor I don't disagree, but that is the question. If I am qualifying no matter what, what is the issue with using a keyword as an object name E.G. a schema? – Elias May 18 '17 at 20:08