Inquery about Best Practices for SQL
-
I know that many are very passionate about how they write SQL code and that there are many styles to write SQl, each with its pluses and minuses. What I'm looking for is any documentation (preferably from Microsoft) that says 'This is the way you should use SQL on SQL Server (7/2000/2005)'. I am currently working as a contractor and the firm I work for is asking me to alter how I structure my SQL code so as to write it exactly like they (meaning management) wants everyone to. My concern is that while some of the changes are cosmetic and have no effect on performance, some are in my opinion risky. For example I believe that all fields should be aliased and have felt this way since SQL Server 7 (when I forst started using SQL). My former employer did not practice aliasing (except when 2 or more tables in the query had columns with identical names) because it was faster to write code without it. The product we wrote SQL for wouold sometimes use third party apps like Crystal Reports. AT the time we were using Crystal Reports version 8. When version 9 came around and we started using it the 'not using aliases' became a big problem because Crystal 9 made aliasing mandatory. Everything I had done was fine because I always used aliasing in production code.
Now with my new employer I believe they are engaging in some practices for writing SQL that are not in the best interest of the client but are done because that is the way the magament likes to use SQL. If there were some way to show them that using SQL this way verses another is less effecient/counter productive then I could get some changes made.
Does anyone know if there are any items (Docs, article, web pages, ect), preferably from Microsoft, on how to use T-SQL?
Thanks
Ed
Kindest Regards,
Just say No to Facebook! -
I know of no hard in stone from MS doc like you want. As for aliasing thou I as well never alias a column name unless there are collisions like you stated in column names. I have used Crystal 7.5-10 and never had any issues with the code regarding this so not sure what you ran into. The only key is column name have to be unique in your output. I will say thou I almost never use * except in audit triggers.
-
The only time I would not use aliases for column names is in queries selecting from a single table. Once you start joining tables, it makes sense to alias the columns even if they are unique. You never know when a schema change may make a column name that was once unique, non-unique and start breaking code.
-
I think you could ask 20 different SQL Server professionals their "best practices" built on experience and get 20 different answers. There was a series at SQLTeam.com (I think) called "Ten Things I Wish Developers Knew About SQL Server" or something to that effect... I rather liked it but I think it dealt with more than just programming IIRC.
The only reason I'd actually side with the cosmetic changes to the way you write SQL is really for maintenance programming down the line. A consistent look and feel across an entire code base means that future individuals will only have to learn the way one person does things. One of my largest problems with contracting has been coming into a situation where there were several people who went before me and trying to decipher how they did things. Though most people have said that my way of coding, while slower than others', is the easiest to read and follow... so it's nice when everyone has to conform to my standard.
-
Hi there,
I have yet to come across a standard way of formatting SQL (or even one I really really like) however I do use SQL Prompt and SQL Refactor (14 day free trial - http://www.red-gate.com/products/SQL_Refactor/index.htm) pretty much daily to format my SQL code and they do a pretty good job of it and make it nice and readable and are also pretty configurable.
(Yes I work for Red Gate and dont want this to seem too much like a shill but they help me out day to day and also make my life easier when I get some SQL from someone else as it will format it for me so I can begin to decypher what it does.)
- James
--
James Moore
Red Gate Software Ltd -
The Instant SQL Formatter does a fairly good job, at:
http://www.wangz.net/gsqlparser/sqlpp/sqlformat.htm
I think I picked up that link from one of the forums here.
-
James,
Kindest Regards,
Just say No to Facebook! -
William.
Thanks for providng the link to Instant SQL Formatter. I had the URL a year or so back and completely forgot all about it and when I got my new laptop about 6 months back I did not copy over all my Favorites.
I see that there is now a downloadable version of this called SQL Pretty Printer.
Thanks
Ed
Kindest Regards,
Just say No to Facebook! -
If you are running SQL 2005, SSMS knows how to print in color, which can really help when working on a procedure.
There is also a tool called NotePad2 from flo's freeware:
that can print any T-SQL file in color. In fact, it looks at the file extension to determine the appropriate color scheme, whether it is sql, vb, asp or whatever.
-
I'm so anal that I use both table and column aliases, even when it's just one table, and when the final column name is the same as the source. I truly have code that looks something like:
SELECT
CustID = c.CustID
,LastName = c.LastName
,FirstName = c.FirstName
FROM
database.dbo.Customers AS c
While it might seem overly anal (and it probably is), I have very few stored procs that don't evolve. Suddenly, that single table becomes a join, and CustID needs to become CustomerID, for some reason, or it might become a calculation instead of just a source column. After years of dealing with that, I just figured that it's easier for me to make a habit of assuming that all stored procedures will not only change, but will change greatly.
-
Take a look at Joe Celko's SQL Programming Style. There is a "look inside" that allows a preview.
Regarding " For example I believe that all fields should be aliased "
Agree totally that column name must be unique.
I also prefer to qualify each column with the full table name and diagree with the convention of always using single character aliases for tables names. Single character aliasing is ok when coding, but before releasing, a simple "replace all" to the full table name required very little effort and makes the SQL much more readable.
SQL = Scarcely Qualifies as a Language
-
Assuming that the column names selected are unique to begin with, I don't understand why you would alias the _column names. You can't use the alias in a WHERE, GROUP BY, ORDER BY etc. so it sounds like just more typing, with limited benefit.
I do agree with alias _table names, but not just with single characters. For example, I've worked on a third-party app with table names like:
FM_MEMBER_PAYABLES
FM_MEMBER_PAYABLES_INVOICE
FM_MEMBER_PAYMENTS
FM_MEMBER_PAYMENT_DETAILS
...which I alias as mp, mpi, mpy, and mpd respectively. This makes for much more readable code IMHO.
-
William, the reason I alias column names even when the source columns are unique is because the alias is often later needed, so if I have it now, everything lines up well, and is easier for me to read. Even using my simple example, for instance, the requirements for LastName and FirstName might change such that I need to add some code to make it proper case, or CustID might need to have some formatting applied. In our environment, we try to put almost all data "prettifying" into stored procs, instead of the app or report that uses them. It's also not that much extra typing in the grand scheme of things. As noted in an early post in this thread, ask 20 programmers, you'll get 20 answers. We all have our own styles that work for us.
Regarding the OP, I really try not force coding styles on our SQL coders, but I do request a few basics:
No a,b,c,d,e,f aliases. With few tables, I don't have problem with single letters, but at least have them match the table, "c" for Customer, "i" for Invoice, etc, so I don't have to keep referring back to the FROM clause. With many tables, or where you have many similarly named tables, I also prefer multiple character aliases, usually the first character in each word of the table name, as many others do.
ANSI Joins.
Consistency. I don't care if you code like me, but at least always code like you.
I've seen people switch keyword case completely, back and forth, several times in the same stored proc. The eyes, they burn.Descriptive in-line documentation. Yes Billy Bob, it's really cool how you used that self-join offset to compare one row to the next, but I don't want Jimmy Joe spending two hours trying to figure out why your INNER JOIN said "b1.ID = b2.ID + 1".
INFORMATION_SCHEMA views, in cases where they have everything that you need. Maybe one day they'll actually expose everything. Not that I'm holding my breath.
A row per column name, table name, etc. Sorry trees, but it's a lot easier to debug Sarah Sue's INSERT/SELECT problem when I can line up the columns in two windows in Textpad, for comparison. In my defense, it actually makes it where I often don't need to print something to figure it out, whereas using the other style, I often printed it just so I could cross things out as I found matching columns.
Finally, we have the following list of things we avoid in our environment, some of which are perfectly fine in other environments, but not so much in our's:
Cursors
Triggers
UDFs
Since I hate it when companies say things like "Never use cursors under any circumstances", for instance, we have a simple rule. If you wish to use one of the above, make sure it's defensible, because odds are decent that you'll be asked to defend their usage in your code.
-
David, you said
"A row per column name, table name, etc. "
Amen to that!
-
> I also prefer to qualify each column with the full table name and diagree with the convention of always using single character aliases for tables names.
It does not work at least in the case when there is a self-join.
So, if it does not work at least in one case it cannot be accepted as a standard.
But single character (why single? 2 or 3 characters are perfectly acceptable) approach work every time.
So, it could be taken as a standard.
As for me I do use aliases for table names in every single query I write. And never faced any trouble with it.
And when aliases are meaningful it makes queries easier to read and understand.
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply