Posts tagged sql
Most of you would have at some point use this query…
IF (SELECT COUNT(*) FROM Table1 WHERE … ) > 0
It’s understandable though. Logically it’s the easiest way to write “if there are any rows matching my condition do this”. But it’s also wrong. Plainly and simply wrong!
Because when you do a count(*) there is no way to get around an index range scan or a full table scan. For a large result set this will be a huge resource hog.
So how to do this is better? Use EXISTS!
IF EXISTS(SELECT * FROM Table1 WHERE …)
Exists stops the execution as soon as it reads the first row which matches your condition, compared to Count which goes through the whole result set matching our condition.
This may seem as such a trivial tip but EXISTS is so underused even by experienced database developers.
And don’t forget the NOT EXISTS to check if rows matching the condition don’t exist.
SQL Server Product team recognized this problem so in SQL Server 2005 those 2 statements produce same execution plans whenever possible. But it still safe to query “properly” from your end 🙂
Here’s a handy query for finding duplicates in a table. Suppose you want to find all names in a table that exist more than once:
SELECT name, COUNT(name) AS namecount FROM users GROUP BY name HAVING ( namecount > 1 )
You could also use this technique to find rows that occur exactly once:
SELECT name FROM users GROUP BY name HAVING ( COUNT(name) = 1 )
Also see How to remove duplicate rows from a table – Microsoft Knowledge base article.
Microsoft has again hinted at changes in the next major release of Visual Studio allowing developers to spread out across different monitors.
Noah Coad, Microsoft program manager for the Visual Studio platform, has confirmed changes are in the works that will help developers build applications in some kind of split-screen mode on different monitors.
Coad was speaking almost a year after he first raised the subject, when he began taking feedback on improvements to the next major Visual Studio release, version 10.
While Coad made no specific commitments, it does seem Microsoft has taken an early round of feedback seriously enough to factor it into Visual Studio 10.
The changes are part of an expected package that’ll see major changes to the interface. Also expected: another round of improvements to IntelliSence, support for SQL Server Compact databases, and – as ever – tighter integration with Visual Studio Team Server. There’s no release date yet for Visual Studio 10.
A new whitepaper that Microsoft researchers are set to present at a conference next month sheds more light on Microsoft’s back-end cloud infrastructure.
The paper, entitled, “SCOPE: Easy and Efficient Parallel Processing of Massive Data Sets,” details a new declarative scripting language that is optimized for storing and analyzing massive data sets (like search logs and click streams) that are key to cloud-scale service architectures. SCOPE, or Structure Computations Optimized for Parallel Execution, is the name of the language.
According to the paper — which Microsoft is on tap to present at the VLDB 2008 conference in late August — SCOPE doesn’t require explicit parallelism, but it will be “amenable to efficient parallel execution” across large clusters. SCOPE is like SQL, but with C# extensions, the paper says.