See recent comments. RSS feed of all comments.
ImageMagick book
MythTV book
|
 |
|
 |
|
| RE: [SQLDownUnder] [SQLStyle] #2: Prefixes on Table/View names |
|
Title: RE: [SQLDownUnder] [SQLStyle] #2: Prefixes on Table/View names
Hi Jeremy,
Yes, but also implemented quite differently. That’s why I find a
lot of the guidance I’d seen for Oracle schemas, etc. not apply.
From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx
[mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx] On Behalf Of Huppatz,
Jeremy B
Sent: Monday, 9 July 2007 10:29 PM
To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
Subject: RE: [SQLDownUnder] [SQLStyle] #2: Prefixes on Table/View names
*Grin* Don't forget that SQL Server was late to the party with
Schemas. Most of the other majors had them already.
Jeremy Huppatz
| EDS | Australia ADU | Data Engineering SME (SQL Server/Data Warehousing) |
| Level 10, 108 North Terrace, Adelaide, SA, 5000 | Office: +61 8 8464 1739 | Fax: +61 8 8464 2138 | Email: jeremy.huppatz@xxxxxxx |
From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx on
behalf of Greg Low
Sent: Mon 9/07/2007 9:42 PM
To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
Subject: RE: [SQLDownUnder] [SQLStyle] #2: Prefixes on Table/View names
Hi Michael,
The other point I should have made is that I'm chasing standards for those
using SQL Server. Given the breadth of what I'd like to cover, if it's limited
to what would run on any database, you'd end up with a very lowest common
denominator approach and have to leave out an enormous number of options and
aspects of the product. There are plenty of sets of guidance around for generic
databases but not so much for those who are specifically using SQL Server. As
an example, if schemas fix a problem that you'd have to use naming patterns
and/or prefixes for in a generic context, I'm heading down the schema path.
Regards,
Greg
Dr Greg Low
Readify | Senior Consultant
Suite 206 Nolan Tower | 29 Rakaia Way | Docklands | VIC 3008 | Australia
M: +61 419 201 410 | E: mailto:greg.low@xxxxxxxxxxx
| W: www.readify.net
-----Original Message-----
From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx [mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx]
On Behalf Of Michael Silk
Sent: Monday, 9 July 2007 8:20 PM
To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
Subject: Re: [SQLDownUnder] [SQLStyle] #2: Prefixes on Table/View names
On 7/9/07, Rob Sanders <mailing@xxxxxxxxxxxxxxxxxxxxx> wrote:
> I'm glad that someone has addressed schemas. I agree on both points
as
> well.
don't forget that 'naming standards' are nice if they help regardless
of tools. i.e. in all types of databases and when viewed from many
different tools (as databases tend to be). just because one db server
has some features doesn't mean it solves all our problems :)
i still think there is a place for prefixes for tables, stored
procedures, functions, views, etc; because they identify the items
without the need for reviewing all the context you have around you.
i guess i also don't exactly like the idea of silently replacing
tables with views. i'd prefer that to be an open operation. but maybe
i'm wrong on that.
> Can we extend the notion further, could/should schemas be used also for
> managing permissions for Stored Procs, UDFs etc?
>
> Would that have any effect on prefix naming convention for those objects?
>
>
>
> From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx
> [mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx]
On Behalf Of Greg Linwood
> Sent: Monday, 9 July 2007 4:23 PM
> To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
> Subject: RE: [SQLDownUnder] [SQLStyle] #2: Prefixes on Table/View names
>
>
>
> I agree with both points.
>
>
>
> The naming prefix tbl... can become counter-intuitive if the table is
> replaced with a view, which is a fairly common database administration
> technique. Tables & Views are *supposed* to be inter-changeable so
having
> any sort of typed name prefix which differs for these two isn't a good
idea.
>
>
>
> Re SSMS, I don't know why MS doesn't provide for categorisation of SPs.
> Schemas are ok, but hierarchical categories / directories would be better
&
> easier to navigate than using filters. Both Schemas & object
categorisation
> would be useful imo.
>
>
>
> Cheers,
>
> Greg Linwood
>
>
>
> _____
>
> From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx on behalf of Greg Low
> Sent: Mon 9/07/2007 4:08 PM
> To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
> Subject: [SQLDownUnder] [SQLStyle] #2: Prefixes on Table/View names
>
> #2: Avoid using prefixes on table/view names.
>
>
>
> Some people like prefixes like vw or v on view names. Some even like tbl
on
> table names. There are several problems with this.
>
>
>
> First up, it's common in production systems for views to replace tables
and
> (occasionally) vice-versa. From a user's perspective, the two should be
> identical. As a related example, Microsoft have learned this lesson the
hard
> way even in SQL Server with their stored procedure naming. SQL Server
system
> stored procedures are prefixed with sp_ and extended stored procedures
with
> xp_. However, if you now look at the list of each, you'll find system
stored
> procedures with xp_ prefixes and extended stored procedures with sp_.
That's
> worse than if they'd never have any scheme because now it's very
confusing.
>
>
>
> The second common version of this is where tables or views are prefixed
with
> something that relates to where they are used in the system. The reason
> typically given for this is to avoid having to look through massive lists
of
> tables in management tools, etc. However, SQL Server 2005 has introduced
> schemas and invariably, these are a better replacement for a prefix naming
> convention. Schemas have the added advantage of also being a security
> boundary and rather than granting SELECT permission on a series of tables
> named:
>
>
>
> HR_Table1
>
> HR_Table2
>
> Etc
>
>
>
> You'd often be far better off granting SELECT permission on a
HumanResources
> schema that contained tables named: Table1, Table2, etc.
>
>
>
> In the example Michael gave the other day of using tmpCustomers as a
staging
> table for Customers and tmpOrders as a staging table for Orders, even in
> this case, I think it would be preferable to have a Staging schema that
> contained these tables and that they'd become:
>
>
>
> Staging.Customers
>
> Staging.Orders
>
>
>
> And the "real" tables would be something like:
>
>
>
> Sales.Customers
>
> Sales.Orders
>
>
>
> It's highly likely that all the staging tables would also need the same
> permission settings and again, these could likely be best done at the
schema
> level.
>
>
>
> Regards,
>
> Greg
>
>
>
> Dr Greg Low
> Readify | Senior Consultant
>
> Suite 206 Nolan Tower | 29 Rakaia Way | Docklands | VIC 3008 | Australia
>
> M: +61 419 201 410 | E: <mailto:greg.low@xxxxxxxxxxx>
> mailto:greg.low@xxxxxxxxxxx | W:
<http://www.readify.net/>
www.readify.net
>
>
>
> to unsubscribe to this list, please send a message back to the list with
> 'unsubscribe' as the subject. Powered by mailenable.com - List managed by
> http://www.readify.net
>
> to unsubscribe to this list, please send a message back to the list with
> 'unsubscribe' as the subject. Powered by mailenable.com - List managed by
> http://www.readify.net
>
>
>
>
> to unsubscribe to this list, please send a message back to the list with
'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net
>
>
--
mike
http://lets.coozi.com.au/
to unsubscribe to this list, please send a message back to the list with
'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net
to unsubscribe to this list, please send a message back to the list with
'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net
to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net
|
( Click here for more information on the sqldownunder mailling list)
|
|
|
|
|
|
