Naming convention for Objects in MS SQL and .net

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Naming convention for Objects in MS SQL and .net

Author

Topic

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-05-26 : 07:38:28

hi,
We are working on a new project and i have a colleague in the office, who says we should name our objects in the db in a certain fashion ie

1. All tables should start with tbl_tablename
2. All SP should start with SP_storedproceedurename
3. All views should start with veiw_viewname

I really think this is funny, but he's reasons are
"My reason is that the conventions help me group all related members of my class easily."

Is this necessary at all?

and is this the right forum to post this or should i do a cross post in the MS SQL server section ?

thanks
Afrika

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-26 : 07:46:19

1. No
2. Definitely not. Anything other than sp_ (read bol)
3. Might be worth naming views so that people know that they aren't accessing tables.

I use
s_ for SPs
vw_ for views
Tables are just the name.

None of this is necessary from a database point of view as you can easily get the object type.
I use s_ for SPs so that it is easy to search for database access in applications - you only allow access via SPs I assume - no embeddded sql.

Repeat - don't use sp_ for SPs

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-05-26 : 07:50:28

thanks nr,
i know you cant use sp_ for SPs, i argued it was simply because MS SQL native SPs, start with SP, he's argument is that its ok, if not we should use an alternative name e.g. s_ or something else.

I really dont see the point of any of these.

Am still new to .net though

thanks for your advice
Afrika

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-05-26 : 07:51:45

i know how to get the object type in QA, but how do you get the object type on the fly ?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-05-26 : 08:07:05

Look at the OBJECTPROPERTY function in Books Online. All of the property names beginning with "Is" (IsTable, IsTrigger, etc.) would tell you the type of object. Here's an example:

SELECT name,
CASE WHEN OBJECTPROPERTY(id, 'IsSystemTable')=1 THEN 'System Table'
WHEN OBJECTPROPERTY(id, 'IsTable')=1 THEN 'Table'
WHEN OBJECTPROPERTY(id, 'IsView')=1 THEN 'View'
WHEN OBJECTPROPERTY(id, 'IsProcedure')=1 THEN 'Stored Procedure'
WHEN OBJECTPROPERTY(id, 'IsTrigger')=1 THEN 'Trigger'
ELSE 'Other' END
FROM sysobjects

You can also figure out what the type codes mean in sysobjects, they are documented in Books Online. However, system table accessed will be restricted or removed in SQL Server 2005, so you should get into the habit of not querying them directly.

The INFORMATION_SCHEMA views will also tell you what type an object is (view, base table, foreign key, primary key, etc.)

jhermiz

3564 Posts

Posted - 2005-05-26 : 08:36:50

I think tbl is mainly access...for some reason I cant stand seeing tbl in sql server...not sure why i cant stand it i just cant :). Nopers on the sp..I usually use rsp_ for reporting service sprocs and s_ for non rs sprocs.

I do use vw for views...
I still like to use naming conventions for .net even though it is really depreacited like the old hungarian notation.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-05-26 : 09:06:08

thanks for your advice.
I guess we would settle for vw_ for views, and s_ for SPs and leave the tables with our naming convention.

However (must say), i marvell, when i see your number of posts ? :-)

jhermiz

3564 Posts

Posted - 2005-05-26 : 09:39:54

Me ? I only have 2k...you think that's bad look at tara's :)

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2005-05-26 : 10:22:28

I personally dislike object type prefixes and almost never use them. I've never accidentally inserted into a stored procedure or executed a table yet. The place I think it may be appropriate is to differentiate a view from a table. Since Enterprise Manager, Query Analyzer and 2005's Management Studio all break out objects by type I don't see why I should further break them out by type.

I much prefer to name my tables based on what they represent. For example, "Invoice". (And I prefer them singular.) Any stored procedures for the Invoice table I'll name something like "Invoice_Insert". That groups my stored procedures around the table they work on. For stored sprocs that aren't table specific I'll come up with something similar.

Just my two cents.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-05-26 : 12:24:18

I agree strongly with Graz on this one. I still don't understand what benefit somebody gets from having the object type as a prefix of the name (except possibly views vs. tables because they have similar behavior, but some tricky implications underneath). In my current environment, they already had application or usage specific prefixes, and so I have followed that pattern such as prefixing sprocs used exclusively for Reporting Services with RS_, but even that makes me a bit uncomfortable, because it suggests it won't be re-used for anything else, which is questionable.

As for .NET naming guidelines, here's Microsoft's suggestions.

---------------------------
EmeraldCityDomains.com

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-05-26 : 12:29:00

quote:
Originally posted by afrika

I really think this is funny, but he's reasons are
"My reason is that the conventions help me group all related members of my class easily."

This doesn't make any sense to me. The names of the members of your class do not need to have any relation to the object names in SQL Server, and certainly not an exact match.

Oh, and as others have hinted at but not explicitly stated, the problem with using sp_ for your stored procedures is that SQL Server always checks the Master database first for any procedure whose name starts with sp_. Then, if it does not find the procedure, it will check the current database. At the best this is a performance issue, and at the worst, you happened to pick a procedure name that matches one that Microsoft created, and theirs gets executed instead of yours.

---------------------------
EmeraldCityDomains.com

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-05-26 : 14:25:27

I personally like to use Sql Key Words to name my objects:


set nocount on
create table [From] 
	([Select]	varchar(15)
	,[group by]	varchar(15)
	,[Where]	varchar(15)
	,[Having]	varchar(15))
GO

insert [From] values ('Select','From','Group by','Having')
go

Select [Select],[Group by]
[From],max([having])[Having]
From [From] 
Where [Where]='Group by' Group by [Select],[Group by]
Having max([Having])='Having'

GO
drop table [From]

My insert procedure for my From table is called: sp_renamedb

Be One with the Optimizer
TG

jhermiz

3564 Posts

Posted - 2005-05-26 : 14:30:39

bahahahaa

thats like obfuscated code :-)

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2005-05-26 : 17:01:21

TG,

That's one of the funniest things I've read in a looong time!

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-26 : 17:38:27

Here is something for You TG:

--DROP VIEW [OBJECT_NAME()]
--SELECT * FROM [OBJECT_NAME()]
CREATE VIEW [OBJECT_NAME()] AS
SELECT TOP 1 QUOTENAME(kw) AS [OBJECT_NAME()] FROM(
SELECT 'ADD' AS kw UNION ALL SELECT 'EXCEPT' UNION ALL SELECT 'PERCENT'
UNION ALL SELECT 'ALL' UNION ALL SELECT 'EXEC' UNION ALL SELECT 'PLAN'
UNION ALL SELECT 'ALTER' UNION ALL SELECT 'EXECUTE' UNION ALL SELECT 'PRECISION'
UNION ALL SELECT 'AND' UNION ALL SELECT 'EXISTS' UNION ALL SELECT 'PRIMARY'
UNION ALL SELECT 'ANY' UNION ALL SELECT 'EXIT' UNION ALL SELECT 'PRINT'
UNION ALL SELECT 'AS' UNION ALL SELECT 'FETCH' UNION ALL SELECT 'PROC'
UNION ALL SELECT 'ASC' UNION ALL SELECT 'FILE' UNION ALL SELECT 'PROCEDURE'
UNION ALL SELECT 'AUTHORIZATION' UNION ALL SELECT 'FILLFACTOR' UNION ALL SELECT 'PUBLIC'
UNION ALL SELECT 'BACKUP' UNION ALL SELECT 'FOR' UNION ALL SELECT 'RAISERROR'
UNION ALL SELECT 'BEGIN' UNION ALL SELECT 'FOREIGN' UNION ALL SELECT 'READ'
UNION ALL SELECT 'BETWEEN' UNION ALL SELECT 'FREETEXT' UNION ALL SELECT 'READTEXT'
UNION ALL SELECT 'BREAK' UNION ALL SELECT 'FREETEXTTABLE' UNION ALL SELECT 'RECONFIGURE'
UNION ALL SELECT 'BROWSE' UNION ALL SELECT 'FROM' UNION ALL SELECT 'REFERENCES'
UNION ALL SELECT 'BULK' UNION ALL SELECT 'FULL' UNION ALL SELECT 'REPLICATION'
UNION ALL SELECT 'BY' UNION ALL SELECT 'FUNCTION' UNION ALL SELECT 'RESTORE'
UNION ALL SELECT 'CASCADE' UNION ALL SELECT 'GOTO' UNION ALL SELECT 'RESTRICT'
UNION ALL SELECT 'CASE' UNION ALL SELECT 'GRANT' UNION ALL SELECT 'RETURN'
UNION ALL SELECT 'CHECK' UNION ALL SELECT 'GROUP' UNION ALL SELECT 'REVOKE'
UNION ALL SELECT 'CHECKPOINT' UNION ALL SELECT 'HAVING' UNION ALL SELECT 'RIGHT'
UNION ALL SELECT 'CLOSE' UNION ALL SELECT 'HOLDLOCK' UNION ALL SELECT 'ROLLBACK'
UNION ALL SELECT 'CLUSTERED' UNION ALL SELECT 'IDENTITY' UNION ALL SELECT 'ROWCOUNT'
UNION ALL SELECT 'COALESCE' UNION ALL SELECT 'IDENTITY_INSERT' UNION ALL SELECT 'ROWGUIDCOL'
UNION ALL SELECT 'COLLATE' UNION ALL SELECT 'IDENTITYCOL' UNION ALL SELECT 'RULE'
UNION ALL SELECT 'COLUMN' UNION ALL SELECT 'IF' UNION ALL SELECT 'SAVE'
UNION ALL SELECT 'COMMIT' UNION ALL SELECT 'IN' UNION ALL SELECT 'SCHEMA'
UNION ALL SELECT 'COMPUTE' UNION ALL SELECT 'INDEX' UNION ALL SELECT 'SELECT'
UNION ALL SELECT 'CONSTRAINT' UNION ALL SELECT 'INNER' UNION ALL SELECT 'SESSION_USER'
UNION ALL SELECT 'CONTAINS' UNION ALL SELECT 'INSERT' UNION ALL SELECT 'SET'
UNION ALL SELECT 'CONTAINSTABLE' UNION ALL SELECT 'INTERSECT' UNION ALL SELECT 'SETUSER'
UNION ALL SELECT 'CONTINUE' UNION ALL SELECT 'INTO' UNION ALL SELECT 'SHUTDOWN'
UNION ALL SELECT 'CONVERT' UNION ALL SELECT 'IS' UNION ALL SELECT 'SOME'
UNION ALL SELECT 'CREATE' UNION ALL SELECT 'JOIN' UNION ALL SELECT 'STATISTICS'
UNION ALL SELECT 'CROSS' UNION ALL SELECT 'KEY' UNION ALL SELECT 'SYSTEM_USER'
UNION ALL SELECT 'CURRENT' UNION ALL SELECT 'KILL' UNION ALL SELECT 'TABLE'
UNION ALL SELECT 'CURRENT_DATE' UNION ALL SELECT 'LEFT' UNION ALL SELECT 'TEXTSIZE'
UNION ALL SELECT 'CURRENT_TIME' UNION ALL SELECT 'LIKE' UNION ALL SELECT 'THEN'
UNION ALL SELECT 'CURRENT_TIMESTAMP' UNION ALL SELECT 'LINENO' UNION ALL SELECT 'TO'
UNION ALL SELECT 'CURRENT_USER' UNION ALL SELECT 'LOAD' UNION ALL SELECT 'TOP'
UNION ALL SELECT 'CURSOR' UNION ALL SELECT 'NATIONAL' UNION ALL SELECT 'TRAN'
UNION ALL SELECT 'DATABASE' UNION ALL SELECT 'NOCHECK' UNION ALL SELECT 'TRANSACTION'
UNION ALL SELECT 'DBCC' UNION ALL SELECT 'NONCLUSTERED' UNION ALL SELECT 'TRIGGER'
UNION ALL SELECT 'DEALLOCATE' UNION ALL SELECT 'NOT' UNION ALL SELECT 'TRUNCATE'
UNION ALL SELECT 'DECLARE' UNION ALL SELECT 'NULL' UNION ALL SELECT 'TSEQUAL'
UNION ALL SELECT 'DEFAULT' UNION ALL SELECT 'NULLIF' UNION ALL SELECT 'UNION'
UNION ALL SELECT 'DELETE' UNION ALL SELECT 'OF' UNION ALL SELECT 'UNIQUE'
UNION ALL SELECT 'DENY' UNION ALL SELECT 'OFF' UNION ALL SELECT 'UPDATE'
UNION ALL SELECT 'DESC' UNION ALL SELECT 'OFFSETS' UNION ALL SELECT 'UPDATETEXT'
UNION ALL SELECT 'DISK' UNION ALL SELECT 'ON' UNION ALL SELECT 'USE'
UNION ALL SELECT 'DISTINCT' UNION ALL SELECT 'OPEN' UNION ALL SELECT 'USER'
UNION ALL SELECT 'DISTRIBUTED' UNION ALL SELECT 'OPENDATASOURCE' UNION ALL SELECT 'VALUES'
UNION ALL SELECT 'DOUBLE' UNION ALL SELECT 'OPENQUERY' UNION ALL SELECT 'VARYING'
UNION ALL SELECT 'DROP' UNION ALL SELECT 'OPENROWSET' UNION ALL SELECT 'VIEW'
UNION ALL SELECT 'DUMMY' UNION ALL SELECT 'OPENXML' UNION ALL SELECT 'WAITFOR'
UNION ALL SELECT 'DUMP' UNION ALL SELECT 'OPTION' UNION ALL SELECT 'WHEN'
UNION ALL SELECT 'ELSE' UNION ALL SELECT 'OR' UNION ALL SELECT 'WHERE'
UNION ALL SELECT 'END' UNION ALL SELECT 'ORDER' UNION ALL SELECT 'WHILE'
UNION ALL SELECT 'ERRLVL' UNION ALL SELECT 'OUTER' UNION ALL SELECT 'WITH'
UNION ALL SELECT 'ESCAPE' UNION ALL SELECT 'OVER' UNION ALL SELECT 'WRITETEXT') AS kw
ORDER BY NEWID()

rockmoose

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-05-26 : 22:52:29

Thanks Rockmoose! This will come in very handy for my next database development project. Its kind of like a Pez Dispenser for object names. I actually hate coming up with object names. I spend as much time deciding on names as I do the actual development. You can always change code but once something is named and in use, its there for life.

Glad you got a laugh, Graz. Your naming conventions (above) is right out of my playbook.

Be One with the Optimizer
TG

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-27 : 01:12:53

"My reason is that the conventions help me group all related members of my class easily."

I think naming convention should be like this:

For Tables (just the name itself (noun)):
1) Customer
2) Orders
3) CustomerProfile

Stored Procedure
1) CustomerSelProc - sp for selecting all records in Customer Table
2) CustomerUpdProc - sp for updating
3) CustomerDelProc - sp for deleting
4) CustomerInsProc - sp for inserting

Views:

1) CustomerListView - list of customer
2) CustomerProfileListView - list of profile

UDF (use verb)

1) GetLastName
2) GetFirstName
3) GetTotalSales
4) ComputeSales

"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "

raclede™

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-27 : 01:15:38

so that when you are going to check all udf,views that are related to Customer you can Easily navigate thru it, can easily remembered and can be easily now what the UDF or Views do..

"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "

raclede™

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-27 : 02:02:24

Pretty good raclede!
To be picky:
Why Order[b]s[/s] when the other are singular ?

rockmoose

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-05-27 : 07:01:36

Thanks a lot for all the advice

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-05-27 : 17:01:29

Rockmoose, I was going to say the same thing. I developed the habit long ago to make table names singular. Some people like plurals, thinking that its a collection of singular items, and I could see that, except some tables just don't make sense in plural, especially junction tables. Either way, it's very frustrating when a system bounces back and forth between singular and plural. Pick one and stick with it.

---------------------------
EmeraldCityDomains.com

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources