Jump to content

Alias


kenneth

Recommended Posts

There are two types of aliases that are used most frequently: column alias and table alias. Column aliases exist to help organizing output where table aliases are used to distinguish one table from another when obtaining information from two separate tables.For columns, you would use it to give the name a more readable name. For instance, I prefix all my colums with three letters (here's why). So I might forma statement like this:

SELECT ind_firstname [First Name], ind_lastname [Last Name]FROM ind_customers [individuals]
You will only need to worry about table name aliases when you get into JOIN statements - where you combine/query mroe than one table at a time.I will note that, if you abopt a good naming convention, most of need for table aliases can be avoided. A good convention will also keep your statements easier to read and understand. A three letter prefix will always make sure you know what "ID" you're looking at.
Link to comment
Share on other sites

Skemcin has given a great description of what aliases are and how to use them.I personally use them a lot at work when performing joins because the tables I am often joining are in different databases, so without aliases here is my query:SELECT database1.dbo.table1.address, database2.dbo.table2.cityFROM database1.dbo.table1, database2.dbo.table2WHERE database1.dbo.table1.idnum = database2.dbo.table2.idnumWith aliases:SELECT a.address, b.cityFROM database1.dbo.table1 a, database2.dbo.table2 bWHERE a.idnum = b.idnumNow, you which do you think is easier to read/type out? (And keep in mind that the more columns/where clauses you use the bigger the difference between the two). Obviously if my queries are going to be used longer than the one search I try to come up with short descriptive aliases, but I think you get the point.

Link to comment
Share on other sites

here's one I had to write recently. It shows how a naming convention can avoid the use of table aliases but how the colun alias make sense. this is an example for pulling data on one server - Kcarson makes an excellent point for table aliases across servers.

select prd_key,doc_url [image URL],prd_description [Product Short Description],prd_online_display_name_ext [Product Long Description]from oe_product (nolock) join oe_product_ext (nolock) on prd_key_ext = prd_key join oe_price (nolock) on prc_prd_key = prd_key join oe_inventory_x_warehouse (nolock) on ivw_prd_key = prd_key join oe_product_type (nolock) on prd_ptp_key = ptp_key join oe_product_category (nolock) ON ptc_ptp_key = ptp_key join co_document (nolock) on prd_thumbnail_doc_key = doc_keywhere ptp_key = 'xxxxxx'and prd_ptc_key = 'xxxxxx'and prd_delete_flag = 0and ptc_delete_flag = 0and ptc_delete_flag = 0and prd_sell_online = 1and prc_sell_online = 1
SQL ia fun!:)
Link to comment
Share on other sites

Yeah it is....by the way were you a DBA in another life.....that is a monster query.....I have seen worse, just not many :(

nope, just stayed at a holiday inn last night!lol - actually just learning more and more from the legacy code I have to deal with on a daily basis.:)You should see some of the Stored Procedures.:)
Link to comment
Share on other sites

nope, just stayed at a holiday inn last night!lol - actually just learning more and more from the legacy code I have to deal with on a daily basis.:)You should see some of the Stored Procedures.:)

Oh, I can imagine....we have 27 databases on one of our servers....pretty ridiculous actually....but its hard to fix now, although we are in the process. All the developers have been working to get rid of all in-line SQL and use Stored Procedures so the DBA could 1. Optimize all SQL Queries, 2. Whenever we begin migrating to a new Data Model (a much more efficient one, mind you) the changes can be made in as few places as possible.But yeah, working with the stuff has taught me a lot. I had a database class when I was in school but learned nothing....within a short time of being here, I went through the SQL tutorial here on w3schools, started learning where everything was stored and then went through a book and now it at least feels like I could do whatever I want within a database :( Of course I know there is still much to learn, but that is what I am in this forum for, if somebody asks a question I can answer great, if I can't answer it then I either find the answer to help them, or learn from when other people post answers. Either way its a win-win for me :D
Link to comment
Share on other sites

I here ya - and I'm in a very similar situation. I am working with some legacy code that had over 500 inline queries. For the same reasons you mention it is advantagous to move them into an SP, I'm doing also to increase server efficiency. Running the SQL statements on the DB server is much more efficient and less taxing on the web server.Forums, especially this one, are definitely a win-win situation for everyone. :)

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...