Random values

Question

Post reply

Random values

Viewing 15 posts - 31 through 45 (of 47 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736222 More actions · Answer 1

Yes, I have Prompt set to build aliases. It works well for me and I have custom ones for certain tables.

I neglected to put it in for the "id" column (though it's there now).

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

Steve Jones - SSC Editor (9/25/2014)
edwardwill (9/25/2014)
Can you explain why you aliased the sys.syscolumns table?
SELECT TOP 10
RAND( ROW_NUMBER() OVER (ORDER BY id))
FROM sys.syscolumns
is functionally identical, as far as I am aware.
habit

And a good habit at that. 🙂 Makes it easier on the next poor slob that has to add a table to the query.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

edwardwill SSCertifiable Points: 5851 More actions · Answer 3

Hugo Kornelis (9/26/2014)
... my personal coding standard is to always use aliases for all tables in queries that use two or more tables, but use no alias in single-query tables - so for those queries I have to manually remove the alias that SQL Prompt adds.

I'm interested to know how or why you came up with this coding standard (I did my degree dissertation on coding standards). My coding standard is never to alias tables (at least not in databases that I designed, because I use descriptive, self-commenting table names) unless it's absolutely essential (where a table needs to be referenced more than once in a query).

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

edwardwill (9/29/2014)
Hugo Kornelis (9/26/2014)
... my personal coding standard is to always use aliases for all tables in queries that use two or more tables, but use no alias in single-query tables - so for those queries I have to manually remove the alias that SQL Prompt adds.
I'm interested to know how or why you came up with this coding standard (I did my degree dissertation on coding standards). My coding standard is never to alias tables (at least not in databases that I designed, because I use descriptive, self-commenting table names) unless it's absolutely essential (where a table needs to be referenced more than once in a query).

Heh... I'm interested to know how or why you came up with this coding standard of generally avoiding aliases. I did my degree in the school of hard-knocks 😀 and it's a rare thing that I won't use aliases on production code even on single table queries. There's nothing worse than having to add aliases later if you decide that you need to add another table to the query.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

edwardwill SSCertifiable Points: 5851 More actions · Answer 5

Jeff Moden (9/29/2014)
Heh... I'm interested to know how or why you came up with this coding standard of generally avoiding aliases. I did my degree in the school of hard-knocks 😀 and it's a rare thing that I won't use aliases on production code even on single table queries. There's nothing worse than having to add aliases later if you decide that you need to add another table to the query.

Why would you alias a single table query? How is

SELECT

Name,

Address,

...

FROM

Customer x

an improvement upon

SELECT

Name,

Address,

...

FROM

Customer

There's nothing worse than having to add aliases later if you decide that you need to add another table to the query

Unless you want to add another instance of the Customer table, why would you have to add an alias anyway? Just do the JOIN on the unaliased table

SELECT

Name,

Address,

LastOrderDate

FROM

Customer INNER JOIN

CustomerOrder ON Customer.ID = CustomerOrder.FKCustomerID

Even if you want to fully qualify the columns, surely

SELECT

Customer.Name,

Customer.Address,

CustomerOrder.LastOrderDate

FROM

Customer INNER JOIN

CustomerOrder ON Customer.ID = CustomerOrder.FKCustomerID

is more readable (and therefore more maintainable, and less prone to error) than

SELECT

x.Name,

x.Address,

p.LastOrderDate

FROM

Customer x INNER JOIN

CustomerOrder p ON x.ID = p.FKCustomerID

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 6

akljfhnlaflkj

SSC Guru

Points: 76202

September 29, 2014 at 7:15 am

#1748799

Thanks for the question.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

edwardwill (9/29/2014)
Jeff Moden (9/29/2014)
Heh... I'm interested to know how or why you came up with this coding standard of generally avoiding aliases. I did my degree in the school of hard-knocks 😀 and it's a rare thing that I won't use aliases on production code even on single table queries. There's nothing worse than having to add aliases later if you decide that you need to add another table to the query.
Why would you alias a single table query? How is
SELECT
Name,
Address,
...
FROM
Customer x
an improvement upon
SELECT
Name,
Address,
...
FROM
Customer
There's nothing worse than having to add aliases later if you decide that you need to add another table to the query

That's not how I do it. This is how I do it. Once you get used to it, it takes no extra time at all and saves time down the road. I will admit that it's not for everyone.

SELECT c.Name

,c.Address

...

FROM dbo.Customer c

;

I'm also absolutely religious about using the 2 part naming convention because we do use multiple schemas and synonyms and it does help a bit with performance for high hit ratio procs, functions, etc, etc.

Unless you want to add another instance of the Customer table, why would you have to add an alias anyway? Just do the JOIN on the unaliased table
SELECT
Name,
Address,
LastOrderDate
FROM
Customer INNER JOIN
CustomerOrder ON Customer.ID = CustomerOrder.FKCustomerID
Even if you want to fully qualify the columns, surely
SELECT
Customer.Name,
Customer.Address,
CustomerOrder.LastOrderDate
FROM
Customer INNER JOIN
CustomerOrder ON Customer.ID = CustomerOrder.FKCustomerID
is more readable (and therefore more maintainable, and less prone to error) than
SELECT
x.Name,
x.Address,
p.LastOrderDate
FROM
Customer x INNER JOIN
CustomerOrder p ON x.ID = p.FKCustomerID

For something like a customer table (relatively short name), it might be more readable but not for some of the long table names that show up in many databases. I also enforce the standard that if there's a join in the table, all column references must be two part to keep people from having to figure out which table has which columns in it. I personally don't make an exception for single table queries because they could someday be made to suffer a join and, once in the habit (and as Hugo pointed out, many tools do it), it's just not difficult or time consuming to do especially if you keep the aliases short but still meaningful.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

edwardwill SSCertifiable Points: 5851 More actions · Answer 8

Thanks Jeff. I'll ponder and see if I prefer your way to my way. As someone once remarked, for each expert there's an equal and opposite expert (not that I'm setting myelf up as an expert, and though I have been doing this stuff a while I still want to get better).

Steve Jones - SSC Editor SSC Guru Points: 736222 More actions · Answer 9

I'm with Jeff. If I had

SELECT

CustomerID

,Name

,Address

...

FROM

Customer

I like the alias. Not because I'd self join to Customer, but because at some point I'd do this:

SELECT

CustomerID

,Name

,Address

...

FROM

Customer

inner join Orders

on Customer.CustomerID = Orders.CustomerID

or

SELECT

CustomerID

,Name

,Address

...

FROM

Customer

inner join CustomerDetails

on Customer.CustomerID = CustomerDetails.CustomerID

and I'd definitely need to alias in the column list since CustomerID will be in both. Or Name will be, or something else.

Plus Prompt from Red Gate makes it easy by adding the alias's for me.

I have found across 5-8 developers, we can easily agree on some standard aliases for tables and we use them everywhere. Then we can read and understand code quickly because we know o = orders and od = orderdetails and ca = customeraddress

edwardwill SSCertifiable Points: 5851 More actions · Answer 10

Steve Jones - SSC Editor (9/29/2014)
I'm with Jeff. If I had
...at some point I'd do this:
SELECT
CustomerID
,Name
,Address
...
FROM
Customer
inner join Orders
on Customer.CustomerID = Orders.CustomerID
or
SELECT
CustomerID
,Name
,Address
...
FROM
Customer
inner join CustomerDetails
on Customer.CustomerID = CustomerDetails.CustomerID
and I'd definitely need to alias in the column list since CustomerID will be in both. Or Name will be, or something else.
Plus Prompt from Red Gate makes it easy by adding the alias's for me.
I have found across 5-8 developers, we can easily agree on some standard aliases for tables and we use them everywhere. Then we can read and understand code quickly because we know o = orders and od = orderdetails and ca = customeraddress

and I'd definitely need to alias in the column list since CustomerID will be in both. Or Name will be, or something else.

.... hmmm. Nope. Just don't get it.

If there are ambiguous columns in the column list just reference them with the table name.

SELECT

Customer.CustomerID

,CustomerDetails.Name

,CustomerDetails.Address

...

FROM

Customer

inner join CustomerDetails

on Customer.CustomerID = CustomerDetails.CustomerID

I don't use the Redgate tool so I can't comment on that - I tend to write my code directly into a Management Studio window (or, if I know I have a lot of tables to join, will use the horrible Design Query in Editor option). But in terms of readability, and making code self-documenting, then I can't see any advantages that accrue from aliasing and only disadvantages. I don't count "saving time because table names can be long" because a) tools should take care of the grunt work of building up the query, and Intellisense of the qualfication, and b) every self-respecting programmer should be a touch typist 😀 in which event the typing isn't an issue.

TomThomson SSC Guru Points: 104773 More actions · Answer 11

edwardwill (9/29/2014)
I don't use the Redgate tool so I can't comment on that - I tend to write my code directly into a Management Studio window (or, if I know I have a lot of tables to join, will use the horrible Design Query in Editor option). But in terms of readability, and making code self-documenting, then I can't see any advantages that accrue from aliasing and only disadvantages. I don't count "saving time because table names can be long" because a) tools should take care of the grunt work of building up the query, and Intellisense of the qualfication, and b) every self-respecting programmer should be a touch typist 😀 in which event the typing isn't an issue.

Space is the issue, not typing time. Saving space on the page is often important for queries; ideally a query fits onto one page - and one page is whatever fits both onto the developers screen and onto the display that the poor support guy is stuck with two years later when something goes wrong. Having everything fit on one page significantly reduces both development time (including unit and system testing) and maintenance costs. Short aliases in place of table names can make a big difference to what fits conveiently so that one can see it all at once. They can also make it easier to use a clean layout - keeping the whole of a join condition on one line, for example. In my experience using aliases properly greatly enhances readability of query text for non-trivial queries.

Actually I found the example you gave in an earlier comment quite horrific, not because it avoided aliases but because it used two different names for the same attribute just because it occurred in two different tables. That is against a very obvious common sense standard.

Tom

Steve Jones - SSC Editor SSC Guru Points: 736222 More actions · Answer 12

You can qualify with the full table name.

I find that to be incredibly annoying to read in a query, especially when I have tables like

- CustomerAddress

- InstantForum_MessageTopics

Having long names in front of columns, to me, makes it hard to read.

There is no right or wrong. It's preference.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

edwardwill (9/29/2014)
Thanks Jeff. I'll ponder and see if I prefer your way to my way. As someone once remarked, for each expert there's an equal and opposite expert (not that I'm setting myelf up as an expert, and though I have been doing this stuff a while I still want to get better).

By no means should you have the perception that I'm trying to sway anyone into a standard. I'm just expressing an opinion of what works very well for me and some of things that I get compliments on for SQL Saturday presentations and in real life. I've actually had people ask me what "formatter" I use (and I don't use one).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 14

Steve Jones - SSC Editor (9/29/2014)
You can qualify with the full table name.
I find that to be incredibly annoying to read in a query, especially when I have tables like
- CustomerAddress
- InstantForum_MessageTopics
Having long names in front of columns, to me, makes it hard to read.
There is no right or wrong. It's preference.

During code reviews in my shop, it's not actually a preference. I'll actually turn back code that's not properly and consistently aliased. I won't turn back single table queries that have no aliases but, if there's a join or correlated sub-query, it must follow the written spec of all tables having a meaningful alias and a column references using 2 part naming only. It took the Developers no time to get into the habit.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 15

TomThomson (9/29/2014)
edwardwill (9/29/2014)
I don't use the Redgate tool so I can't comment on that - I tend to write my code directly into a Management Studio window (or, if I know I have a lot of tables to join, will use the horrible Design Query in Editor option). But in terms of readability, and making code self-documenting, then I can't see any advantages that accrue from aliasing and only disadvantages. I don't count "saving time because table names can be long" because a) tools should take care of the grunt work of building up the query, and Intellisense of the qualfication, and b) every self-respecting programmer should be a touch typist 😀 in which event the typing isn't an issue.
Space is the issue, not typing time. Saving space on the page is often important for queries; ideally a query fits onto one page - and one page is whatever fits both onto the developers screen and onto the display that the poor support guy is stuck with two years later when something goes wrong. Having everything fit on one page significantly reduces both development time (including unit and system testing) and maintenance costs. Short aliases in place of table names can make a big difference to what fits conveiently so that one can see it all at once. They can also make it easier to use a clean layout - keeping the whole of a join condition on one line, for example. In my experience using aliases properly greatly enhances readability of query text for non-trivial queries.
Actually I found the example you gave in an earlier comment quite horrific, not because it avoided aliases but because it used two different names for the same attribute just because it occurred in two different tables. That is against a very obvious common sense standard.

To that end, I also despise code that requires horizontal scrolling at all or requires unnecessary vertical scrolling. The spec I wrote for work requires no code be longer than 119 characters on a single line (stop typing when the cursor column indicator hits 120). It also makes printing of code a whole lot nicer. With a 10pt font, half inch margins in the landscape orientation, you can fit exactly 119 characters in Courier New or one of the other standard width fixed spaced character sets.

The only exceptions I allow are for one time scripts where code generated by the system may exceed the 119 characters. If such code is going to be in a stored procedure, then I make them wrap it. Folks used to complain about that with long string literals until I showed them how "Sloshing" works.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)