Using Results from one column to calculate another column

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Using Results from one column to calculate...

12 posts, Page 2 of 2

««1 2

Using Results from one column to calculate another column

Rate Topic

Display Mode

Topic Options

Author

Message

CELKO

Posted Monday, January 07, 2013 6:20 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

Not that it's a particularly useful example of a sort but, just to prove how wrong virtually everything in the statement above can actually be in T-SQL, please consider the following...

Yes, T-SQL dialect still shows its 1970's UNIX/Sybase roots in places. I always
to talk Standard SQL and not a local dialect, especially when the dialect is that awful. Try using a table not in the FROM clause in an ORDER BY, so the sort order depends on some column in this table that was never in the query.

T-SQL is slowly trying to escape its polluted past. DATE and TIME, *= removal, semi-colons, MERGE, BIT as numeric, etc. It takes awhile to correct legacy (aka “family curse”) and get up to standards.

Effectively materialize the CTEs in the optional WITH clause. CTE's come into existence in the order they are declared so only backward references are all lowed... As you can see, things happen "all at once" in SQL, not "from left to right" as they would in a sequential file/procedural language model.

Not quite. The CTE model is mapped as shorthand into a VIEW creation model. A VIEW cannot exist before there is a base table or a base view for it. It is the same model we used for derived table scoping. Temporal logic, not syntax.

As you can see in your own writing, "It Depends". Some things don't actually happen "all at once" in SQL. In fact, even an update on a single column doesn't happen all at once. It happens just like procedural code... one row at a time, one page at a time.

Nope. Look at SQL implementation that do an update in passes on parallel hardware.

1. Mark the candidate rows.
2. Create set of new rows from the candidate set. If column is not in the SET clause, then use “SET x = x”, so it is effectively not change. You must build a set of rows. The word “effectively” has special meaning in Standard-speak.
3. Insert the new rows as uncommitted.
4. If the uncommitted data violates any constraint, raise an error and do a rollback.
5. If the uncommitted data does not violates any constraint, delete the marked candidate rows and insert the new rows.

In many columnar databases, the entire new column is fully built, the old one is deleted as unit and new one is renamed as a unit. You are confusing the abstract model with with one kind of implementation.

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Post #1403939

ChrisM@Work

Posted Tuesday, January 08, 2013 4:03 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:51 AM
Points: 5,608, Visits: 10,970

ScottPletcher (1/7/2013)

If all the columns are from a single table, you definitely should created a computed column that has the computation: that way, the definition is in only one place. ...[/code]

+1

Otherwise, use CROSS APPLY, keeping the definition in only one place in the query. If the calculation is used in many queries, consider constructing an inline "row-valued" function.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps

Post #1404116

« Prev Topic | Next Topic »

12 posts, Page 2 of 2

««1 2

Permissions