Iulian -207023 (11/23/2015)
Is there a naming convention that you can recommend ?
i.e. for user tables tbl_
for user's views vw_
for user's functions f_
for procedures p_
or something like this ...
If you are working in an existing environment, I would stick to whatever naming convention is already in place (except that I would still not use sp_ and try to rename existing procedures that use that prefix).
When you start on a new project and have the luxury of proposing the ideal naming convention, I would firmly recommend not using any prefixes at all. For most object names, the context of their usage is sufficient to determine the type (e.g. "EXEC dbo.Xyz;" - in this context, I know that Xyz is a stored procedure and I do not need a p_ or usp_ prefix to be reminded of that).
The only place where there is confusion is in a FROM clause: "SELECT something FROM dbo.Abc WHERE (...)" - in this case, Abc can be either a table or a view (not a function, they always have parentheses). Some people say that using a prefix saves them time when looking up the definition of Abc in the object explorer. There is some merit to that, but not very much - how much time can it take to look in two places? I personally gladly accept this tiny bit of extra work for the flexibility it gives me.
To understand this flexibility, one of the great things you can do with a view is to make changes to the database without affecting existing code. Let's say we have a table "Employees" with a column "HireDate". One day we find that we occassionally rehire someone, and we need to keep botht he first and the second hire date - and the third if we ever fire and then rehire the same person again. So we remove HireDate from the Employees table, ad we create a new table EmploymentPeriods with EmployeeId, HireDate and (nullable) TerminationDate, with a foreign key to the Employees table. But now, a lot of the existing code does not work anymore - all the code has to be changed, and all the changed code has to be tested.
Instead, we could also decide that 99% of the application logic only requires the HireDate of the current employment period, leave that column in the table, and set up logic to try to prevent inconsistencies between the HireDate in the Employees table and the data in the EmploymentPeriods table. My experience is that sooner or later, you will find an inconsistency and need to figure out which version of the truth to use.
So my choice would be to go about it differently. I would rename the Employees table to, e.g. EmployeeData (okay, not the best name). I would then still create the normalized EmploymentPeriods table and remove the HireDate from the EmployeeData table. And I would create a view that joins EmployeeData and EmploymentPeriods to present the user with the data that was in the original Employees table, with the HireDate of the most recent employment period. The benefit of this is that the 99% of the logic that does not need to use the older employment periods can continue to run unchanged - they still reference Employees, which was a table and now is a view; and they receive the same data as before. The change is completely masked. If needed/wanted, they can be scheduled for later change, or they can simply keep using the view indefinitely.
Now imagine a naming standard that uses t_ of tbl_ for tables and v_ or vw_ for views. Using the same method as described above, I now have to choose between either creating a view called t_Employee (which defies the purpose of the prefixes and in fact introduces a heinous layer of complexity), or changing the name from tbl_Employee to vw_Employee (but then I still need to touch that extra 99% of the existing code, and hence need to re-test it as well).
So long story short, if I can choose a naming standard my rules (in random order) would be:
1. No prefixes. (But: See below)
2. Do not use spaces, reserved words, special characters, or anything else that results in having to escape object names with  or "".
3. For table and view names, use a name that describes the contents. And since a table/view is a set of data, use plural (that also avoids most conflicts with reserved words). (In some cases, a word for the set is okay - e.g. Personnel instead of Employees).
4. I prefer to start with an upper case letter and have the rest in lower case; when an object name combines words, I prefer "PascalCase" - e.g. EmploymentPeriods. I make an exception for standard abbreviations, so I would use "ISOStandards" instead of "IsoStandards".
However, full lowercase and underscores (employment_periods) is also okay. Not sure how to handle abbreviations for this, though.
5. No artificial names created by smashing other names together, or by describing the relationship between two tables. So if you have tables "Employees" and "Projects", the many-to-many relationship between them would not be "EmployeeProjects" or "ProjectEmployees" or "ProjectXrefEmployees" (Yes, I have seen this). Instead, think of what rea-world entities are described by the entries in this table, then use that as the name - so it would probably be "WorkAssignments" or "ProjectAssignments".
6. Adapt to your environment. If you are used to having a table "Employees" but everyone in the organization prefers to call employees "workers", then call the table "Workers". That makes it easier for others in the organization to understand the table and the data.
There is one exception to the "no prefixes" rule. For constraints and indexes I like to use standard prefixes. The primary reason for this is that indexes created for a primary key or unique constraint get the name of the constraint, and when I look at the indexes of a table it helps to know that indexes starting with UQ_ or PK_ are automatically created for a constraint (and hence non-negotiable), whereas indexes starting with ix_ are created for performance and should be periodically reconsidered. Some people like to use different prefixes for different index types, e.g. cix/ncix for clustered/nonclustered, csix for columnstore index, etc. I am still undecided on that one.
There is actually not much reason to also use prefixes FK_, CK_ and DF_ for foreign key constraints, check constraints and defaults - other than consistency between all constraint types.