Sponsored White Papers, Webcasts, and Downloads
TechRepublic Resources
- Optimize SQL Server queries with these advanced tuning techniques
- More good inputThanks again Tim. I've been following this line of posts because the topic has been speed. Thanks for pointing out the STATISTICS IO option. This is one of those "nuggets" that I always look forward to discovering and now that I know about it, I...
- Tags: thisisfutile, technique, advanced tuning technique, Optimize SQL Server, subquery, server
- Discussion threads 2007-09-18
- Optimize SQL Server queries with these advanced tuning techniques
- Now that you know how to speed up your SQL Server database queries, you can start delving into some of the more advanced tuning options. The best way to tune performance is to try to write your queries in a number of different ways and compare their...
- Tags: Technique, Database, Transaction, Query, Microsoft SQL Server, Statement, Server, Tim, Tim Chapman
- Blog posts 2007-09-17
- Working with nested subqueries in SQL Server
- Arthur Fuller provides an example that demonstrates the power of nested subqueries. He notes that nested subqueries can help you find the answers you need when the pieces are scattered across a number of tables. Many SQL Server programmers are confused about the use of subqueries, especially nested subqueries...
- Tags: Microsoft SQL Server, Arthur Fuller, Databases, server, subquery, nested subquery, SQL Server Newsletter, TechRepublic Inc., Table, Enterprise Software, Software, Data Management
- Technical articles 2007-01-29
- Working with nested subqueries in SQL Server
- Example: Bad! Subqueries: WORSE!Firstly, the example given here is an extremely poor one because Using IN creates a temp table for every item in the IN list. If each of these subqueries only returns 10 records, this query will create 1000 temp tables. Very inefficient. SQL is not designed to...
- Tags: subquery, Jeff_D_Programmer, sql scripting, software, programming, queries, sql, sql query, query, join, Microsoft SQL Server, server
- Discussion threads 2007-01-30
- Build molecular queries from atoms in SQL Server
- Flexible but at a priceCombining several subqueries (hidden in objects such as views, UDF,...) may introduce overhead forcing SQL server to retrieve temporary results, store them in tempdb and only then return final results. Using such a technique on a big databases one should be careful enough to...
- Tags: Aliux, Microsoft SQL Server, server, software, sql, subquery
- Discussion threads 2006-09-19
- Scalar subqueries simplify complex SQL statements
- Scalar subqueries are yet another great enhancement to Oracle9i. They can combine multiple queries into a single query, greatly simplifying complex SQL statements.Scalar subqueries are a powerful enhancement to Oracle9i SQL. They allow for quick formulation of extremely complex SQL statements. Oracle’s introduction of scalar subquery support is another example...
- Tags: Programming languages, Databases, Oracle Corp., Listing B, Donald Burleson, subquery, SCALAR, Scalar subquery, SQL
- Technical articles 2002-08-13
Additional Resources
- Why isn't my subquery updateable?
- The best of intentions often fail when writing a subquery. If you can't update your data via a subquery, there's an alternative. by Susan Harkins
- Tags: Query, Susan Harkins
- Blog posts 2008-07-18
- Oracle Tip: Understand how NULLs affect IN and EXISTS
- On the surface, it may appear that the SQLclauses IN and EXISTS are interchangeable. However, they're quitedifferent in how they handle NULL values and may give differentresults. The problem comes from the fact that, in an Oracledatabase, a NULL value means unknown, so any comparison oroperation against a NULL value...
- Tags: Databases, Oracle Corp., IS NULL, clause
- Technical articles 2004-08-22
- I need a help with sql nested sub query please.
- Hi,I can create this view;CREATE VIEW view1 ASSELECT p.bedno, p.refno, p.surenameFROM patient pWHERE p.bedno = (SELECT bednoFROM bed WHERE bedstatus = 'USED');But when I "SELECT * FROM view1", I get this error message it says " ERROR at line 2: ORA-01427: single-row subquery returns more than one row".I need count...
- Tags: alan.sarchnar@..., SQL
- Discussion threads 2007-03-21
- Compute the percentage of a total with Oracle's RATIO_TO_REPORT
- Many reports include a percentage of the report total on each line, in addition to the detail data being reported. For example, what percentage does each customer contribute to all of the orders placed in a month, or what percentage does each sales rep contribute to the total sales?The traditional...
- Tags: oracle, databases
- Blog posts 2007-07-17
- Using a PL/SQL Table in SQL
- A friend of mine called a fewdays ago with a question. He wanted to populate a PL/SQL table and useit to compare to some other data via a NOT IN subquery. He was having aproblem getting the data into the nested table and wasn't sure how toaccess it from SQL...
- Tags: PL/SQL, PL/SQL Table, SQL
- Blog posts 2006-08-10
- running sum in SQL
- I have a sorted table and I want to create another one that would contain a field that is a running sum. I cannot figure it out in SQL, I know it's possible in Excel, Access ...day total for day ...
- Tags: Programming languages, Databases, Microsoft Office, highlander718, SQL
- Discussion threads 2007-07-20
- Put SQL Server's EXISTS construct to good use
- The next time you are writing your own queries, remember SQL Server's EXISTS construct. It is typically fast and easy to use, and it can prove very useful in programming situations. One thing I have noticed over the years is how seldom developers use SQL Server's very useful...
- Tags: Microsoft SQL Server, Tim Chapman, SalesHistory, Listing C, SalePrice, Databases, server, SQL Server Newsletter, Construct, Enterprise Software, Software, Data Management
- Technical articles 2007-04-30
- Subquery Needed?
- Using the sql script below in SQL Query Analyzer to determine the member’s primary care physician, the physician who provided the service and the medical group IPA of the primary care physician.Select t.MEMBER_NBR, pr.PROV_ID as SvcProviderID, pr.PROV_RELATION_PROV_ID as PCPProvID,pti.PROVIDER_TAX_ID -- Is this the tax id of the PCP?,case...
- Tags: lacwill@..., physician, pti.PROVIDER_TAX_ID, medical group, pr.PROV_RELATION_PROV_ID, provider_id
- Discussion threads 2007-08-03
- Find sequence runs using Oracle's analytic functions
- Bob Watkins responds to TechRepublic member Sachin Jayashekhar's question about detecting sequence runs within a group of rows, and provides a sample script that selects the first and last value in each run. In a previous tip, I described how to find gaps in sequences using Oracle's powerful...
- Tags: Oracle Corp., Bob Watkins, analytic function, Sachin Jayashekhar, Oracle Tips Newsletter, Row, Sequence, Corporate Communications, Storage, Databases, Marketing, Hardware, Enterprise Software, Software, Data Management
- Technical articles 2006-11-20
- Copy items from one table to another
- I have 2 tables. Main Serial Number Solution_IDThey both have the following 2 fieldsLog_IDSoln_IDBoth tables contain the same information as far as LOG_ID goes.I want to that the information in the Solution ID table Field Soln_ID and replace the number that is in the Main Serial Number table Field Soln_IDSo...
- Tags: rheal.dugas@..., Log_ID Soln_ID, Main Serial Number
- Discussion threads 2007-01-11
- Using SQL Server 2008's MERGE statement
- SQL Server 2008's new MERGEÂ statement allows you to insert, update, or delete data based on certain join conditions in the same statement. In previous versions of SQL Server, you have to create separate statements if you need to insert, update, or delete data in one table based on certain conditions...
- Tags: Microsoft SQL Server, Data, Statement, Table, SQL Server 2008, MERGE, MERGE Statement, Tim, Sales Strategy, Sales Force Management, Databases, Enterprise Software, Sales, Software, Data Management, Tim Chapman
- Blog posts 2007-09-24
- 10+ tips for working efficiently in Access? Query Design window
- This article is also available as a PDF download.To create an Access query, you use the Query Design window. Youspecify a few data sources and fields, add a criteria expression or two,and perhaps specify a sort order. What you might not realize is that there'smore to working in this window...
- Tags: Databases, Susan Harkins, QBE, QBE grid, Query Design window, Query Design, grid, window, Microsoft Access
- Technical articles 2007-04-03
- Date Format and correlated subquery
- I need to take a month and day field in a table, and update a new date field, based upon the month and year. I tried this query, which says it updates the records, but none of the date fields are updated. Can someone tell me what is...
- Tags: dateTime, FETCH NEXT, programming, Randy.L.Kemp@...
- Discussion threads 2004-11-05
- Incorporate SQL Server UDFs in your .NET applications
- The article shows a misuse of UDFsIt’s nice to see UDFs get some attention. But the author exhibits a common misuse of UDFs. Take a look at this procedure:CREATE PROCEDURE dbo.ReturnAllEmployees AS BEGIN SELECT dbo.GetEmployeeName(e.EmployeeID) As FullName, Address, City, PostalCode FROM Employees e END The SELECT statement...
- Tags: anovick@..., create function, Incorporate SQL Server UDF, Microsoft .NET, Microsoft SQL Server, programming, sql server udf, udf, UDF dbo.GetEmployeeName, user-defined function
- Discussion threads 2005-10-20
- << Previous
- page 1 of 1
- Next >>
