Editorial

The English Query of Katmai

Remember English Query in SQL Server 2000? It was supposed to make it easy to build applications that your users could use to query the database. Users could ask questions like "How many products have we sold?" and the database would translate that into a query and run it. Well, I guess they wouldn't ask, though that would have been cool. Not sure TSAPI or whatever speech API they have was ready, but they could type in the question.

The problem was that it didn't work well. People had to phrase questions a certain way, and if you require things like that, you might be better off teaching them SQL.

On inventing some easy to use reporting tool that DBAs could use to churn out reports quickly. Something like, say, Reporting Services. That worked out nicely and English Query got quietly dropped. Can't say I blame them. I'm not sure I'd make an announcement that I failed at something I tried. In any case, in the blogs this week, as the July CTP was released, there was an interesting note in the README. I was kind of surprised so many people picked up on it since it didn't seem to be a popular technology, but they did.

The README announced we had one less acronym to remember!

Actually that's not what it said, but it amounted to that. Notification Services is no longer being distributed with SQL Server and won't be in Katmai. There's a mention that some of the features or scenarios will be included in Reporting Services, probably some subscription based thing, but the overall product is gone.

I'm think this was a good move as it was a somewhat complicated product. And it didn't seem to be that hard a thing to build yourself. I'm not sure the code you saved wasn't lost trying to learn the ins and out of SSNS and working within their model. Much better to deploy those people on another part of SQL Server.

Going Green

If you read this regularly, you know that I'm a big supporter of green technologies and have forayed into the area myself with the purchase of a Prius. I'm also considering setting up some sort of alternative energy at my home to both offset future costs and reduce my personal carbon footprint. I don't see fossil fuels are "evil", but I certainly think that we need to find renewable sources of energy, especially as more and more of the world moves into the digital age and our demand for electricity increases.

Apparently I'm not alone and I was pleased to see that more and more companies are looking to go green. Especially those companies, like Xerox, that can affect how green the rest of us are. It's more than just energy, since we can deal with waste better, recycle, reduce travel and unnecessary trips, etc.

I hate to sound like a late 70's Jimmy Carter commercial, but there are things we can do that don't seriously impact our lifestyles or our businesses, just by thinking about them a little. I'm not sure we're in a crisis, and I don't know how big much humans can really affect the world, but it isn't worth a huge chance and it would be nice to have us all doing a bit to limit our impact on the planet wherever we can.

Steve Jones

	SQL Compare slows the ageing process!
	The best-selling database comparison/synchronization tool, SQL Compare, costs the same as a few hours of your valuable time. And it starts reducing your workload as soon as you launch it. For improving the speed and quality of your database change management process SQL Compare has no comparison. It's faster, easier and it's around 90% more cost effective than the alternatives. Why not download a free 14-day trial from Red Gate Software?
	©Red Gate® Software Ltd 2007

Please support us by visiting our sponsors

All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit. These headlines are gathered throughout the week and are posted in real time at the website. Check there for information throughout the week or enjoy this weekly summary of the SQL Server world.

Tech News

EPA: U.S. needs more power plants to support data centers - The rate at which computers and data centers are using power will double in five years, a rate so rapid that the U.S. will need 10 more electric power plants over that period just to keep up, the U.S. Environmental Protection Agency (EPA) said in a report released today. ...(more)

Security

Black Hat tilts toward business users - According to a survey conducted by anti-virus giant Symantec, more business users than ever before attended the Black Hat 2007 security conference held this week in Las Vegas....(more)

Tech News

The realities of green computing - More and more lately, IT vendors trumpet their electronic-waste recycling and "takeback" programs where manufacturers accept responsibility for the full lifecycle of goods they produce. Better yet, we're told, is the rapid trend toward "green computing" with electronics being pro...(more)

Data Mining/Warehousing

Wal-Mart Plans for Its 4PB Data Warehouse - When the world's largest retailer struggles with a database issue, the numbers can be a bit daunting. Wal-Mart's data warehouse, for example, is larger than four petabytes. That's more than 4,096TB, give or take a few million bits. The chain has more than 6,000 stores, with some having almost a half...(more)

Tech News

Web 2.0 Tools Still Scarce in IT Recruiting - New steps are needed to attract entry-level IT workers and retain retirement-age staffers, but IT managers are slow to try Web 2.0 tools that could help....(more)

Corporate Blogs Take On an Edge - NetQoS Inc. is a vendor of network performance management software. But you?d be hard-pressed to figure that out from some of the online posts written by Brian Boyko, the Austin-based company?s designated corporate blogger....(more)

Data Mining/Warehousing

Google Analytics in data blackout since Saturday - Google's Analytics service stopped delivering data to users on Saturday, another in a series of recent performance and availability problems affecting this popular Web site traffic-monitoring service. The latest problem remains unsolved and is apparently affecting all Google Analytics accounts, acco...(more)

Data analysis opens retail doors - Judged on the basis of U.S. Census data, swaths of Detroit would seem to be too poor to support much in the way of retail development -- a situation repeated in cities across the country. Social Compact, a 16-year-old nonprofit organization has been steadily working to correct such misperceptions, a...(more)

Tech News

The IT Job Market Is More Competitive, Says Gartner - According to a new report from IT industry watcher Gartner, chief information officers are having a hard time filling vacant positions in their shops for IT professionals. Specifically, because some IT skills are in high demand, positions are being left open for longer than they would like them to b...(more)

Companies Test on Windows, Deploy on Linux - In one of the more interesting stories that has come out during this relative quiet summer season in IT, a survey done by Alfresco Software, a provider of open source content management software and paid-for support for the product, shows what could be some major shifts in the ways that Linux and Wi...(more)

Security

Romanians Lighten Up Harry Potter Game - In the mobile phone version of the "Harry Potter and the Order of the Phoenix" video game, the torches hanging along the dark walls of Hogwarts glow in an eerily realistic fashion. "We invented the technology, it's called 'dynamic lighting'," said Mihai Pohontu, general manager o...(more)

Debate Breaks Out over Breakable Forensics Software Charges - The fur is flying over a presentation, planned for Black Hat in Las Vegas Aug. 1, that security firm iSEC says will demonstrate how easy it is to break forensics software. Forensics tools such as Guidance Software's EnCase are used by law enforcement, enterprises and national security agencies for ...(more)

Microsoft News

Microsoft breaks ground on San Antonio datacenter - Microsoft is relying on green technologies in its newest datacenters, including one in San Antonio where it is breaking ground on Monday. The San Antonio building, one of several in the works at various locations, will be 500,000 square feet and contain tens of thousands of servers, said Michael Man...(more)

Microsoft Summer Interns Party at Bill's - What's it like to have a job where you have regular roundtable discussions with executives and have a barbeque in the backyard of the richest man in the world? Is it possible to work with a company that is so eager to invest in your ideas you feel that as big as you can dream they will back you up, ...(more)

Behind Microsoft's Popfly - Q&A: John Montgomery, the man behind Microsoft's Popfly, tells where it all came from....(more)

Hardware News

That sound you hear? The next data center problem - The more servers that are added to a data center, the more cooling that center is likely to need. And the more cooling those servers require, the more "whoosh" is generated. Whoosh, for the uninitiated, is the annoying noise of fans and humming power supplies that can feel like a pressure ...(more)

Performance and Tuning

SQL Server: Automated database object recompiling after cleaning plan cache with FMTONLY - DBCC FREEPROCCACHE; and DBCC DROPCLEANBUFFERS; are commands that clean the cached execution plans from memory. The consequence of running those statements is that all your objects (sprocs, functions, views) must be recompiled which can be quite costly.
...(more)

SQL Server 2008: Locking with MERGE statement compared to Update/Insert in SQL Server 2005 - In a previous post I've shown what locks are held when doing "update if exists, insert if not" problem in 2 different ways. Of course i had to see how MERGE statement (new in SQL Server 2008) behaves in this matter....(more)

Integration Services

SSIS: new enhancements in Katmai CTP4 - The fourth Community Technology Preview (CTP4) of SQL Server 2008 (nee Katmai) has been released. ...(more)

DMO/SMO

SQL-DMO Removed from SQL Server 2008 Express - I've installed the new July CTP of SQL Server 2008 (Katmai) and found this in the Readme:...(more)

Integration Services

SSIS Design Pattern - ETL Instrumentation, Part 1 - SSIS is a fantastic ETL engine. When I build an ETL solution in SSIS, I like to collect runtime metrics. Why? I use the data initially to determine a baseline for performance and to determine, in some cases, if I'm loading within a defined window....(more)

SQL Server 2008 (Katmai)

No Notification Services in SQL Server 2008 - If you have been wondering where Notification Services (NS) is in the prior CTPs of SQL Server 2008, you now have the answer. NS will not be carried forward to SQL Server 2008. Here's a quote from July CTP readme:...(more)

SQL Server 2005

Does BACKUP utilize pages in cache? - I'm having a conversation with Tony Rogerson (see his great blog at: http://sqlblogcasts.com/blogs/tonyrogerson/) about whether or not BACKUP will grab paged from cache (Buffer Pool, BP) or always read them from disk. ...(more)

Performance and Tuning

How to speed up partition merging - The key to performance with partition management is to deal with empty partitions. That way all you have is meta data changes and no data has to be copied. We recently encountered a situation where we had two partitions and wanted to merge them...(more)

Security

Conversation with Kip Hawley, TSA Administrator (Part 5) - This is Part 5 of a five-part series. Link to whole thing. BS: So far, we've only talked about passengers. What about airport workers?...(more)

Conversation with Kip Hawley, TSA Administrator (Part 4) - This is Part 4 of a five-part series. Like to whole thing. BS: What about Registered Traveler? ...(more)

Conversation with Kip Hawley, TSA Administrator (Part 3) - This is Part 3 of a five-part series. Like to whole thing. BS: Let's talk about ID checks...(more)

Analysis Services / BI

It?s Good to Be in Business Intelligence and Data Warehousing - It?s good to be in business intelligence (BI) and data warehousing (DW). That?s straight from the horse?s mouth, so to speak: the horse (or horses) in question being BI and DW pros. These professionals cite the unique challenges associated with working in BI and DW, ready opportunities for career ad...(more)

Reporting Services

Monitoring Subcription Status - New reports! - A while ago I wrote a blog post to explain how to monitor subscription status. You can read it here: http://blogs.msdn.com/lukaszp/archive/2005/12/30/monitoring-subscription-status-also-calling-rs-soap-methods-from-inside-reports.aspx. Today, I updated the Subscriptions List report contained in tha...(more)

Performance and Tuning

Performance Impact: Profiler Tracing vs. Server Side SQL Tracing - Adam Machanic asked me whether I had done any tests to compare the performance impact of using SQL Profiler on the client side and that of using SQL Trace on the server side. This is an interesting question because the frequently heard recommendation is to prefer SQL Trace on the server side, but th...(more)

Security

Who's more secure, Oracle or SQL Server? - UK SQL Server MVP Jasper Smith pointed out a really useful resource if you work with or are concerned about both Oracle and SQL Server. The white paper, by David Litchfield of NGS Software, comparesthe numbers of security flaws identified by external security researchers and subsequently fixed by O...(more)

SQL Server 2000

Tracking Log File Growth on SQL Server 2000 - Gert Drapers, whom I consider to be a super-genius of the SQL Server world and owner of the very useful website http://www.sqldev.net, wrote a useful little script a few years ago that I thought I'd share. This is a SQL query that will show who and what is causing the log of a database, in this cas...(more)

T-SQL

Which DMV Shows Plan Handles? - Sometimes, knowing which Dynamic Management View (DMV) to use is your biggest challenge when attempting to figure out SQL Server 2005 behavior. For example, what if you want the cached execution plans in XML format, similar to the output of SET SHOWPLAN_XML? As it turns out, Gert Draper pointed ou...(more)

SQL Server 2008 (Katmai)

SQL Server 2008 Has Nanosecond Precision? - It looks like SQL Server 2008 has nanosecond precision for the time datatype. I just looked at BOL and yes nanoseconds = ns, microsecond = mcs when used in dateadd.
...(more)

Database Design, Theory and Development

ORM Part 3 ? CSDP - I like Object-Role Modeling very much. Some ORM gurus even say with ORM, you can forget about Normalization. Of course, normalization is built in the ORM procedure. However, I do not agree with such extreme views. Why would I forget something I know? In my age, with all the beer I drink, I keep on f...(more)

T-SQL

More on TOP - Last week I wrote about a special case of the TOP operator known as ROWCOUNT TOP. This week I'll take a look at some other interesting TOP scenarios. In general, TOP is a fairly mundane operator. It simply counts and returns the specified number of rows. SQL Server 2005 does include two enhancem...(more)

Analysis Services / BI

What's new in AS2008? - There are a whole bunch of new white papers detailing new features in SQL2008 (you can see the complete list here) but I thought I'd highlight the BI paper:...(more)

T-SQL

Script of the day - Finding Foreign Objects in master - Have you ever forgot to set the database when you ran a query, filling the master system database with your user objects? And I'll bet you used the dbo user too, didn't you? Doh! Here's a quick way to ferret those out so you can clean them up:...(more)

SQL Server 2008 (Katmai)

Katmai: Using Table-Valued Parameters with ADO.NET - 've been trying out table-valued parameters along with ADO.NET support in Orcas and came across an interesting dilemma. It centers around INSERTs using TVP against a table with an identity column. ADO.NET can use DataTable, IDataReader or IList<SqlDataRecord> to represent a TVP parameter. Let'...(more)

New SQL Server 2008 Date/Time-related types and ADO.NET - I've been looking at the mapping of the new Katmai date/time data types to .NET types, both from the point of view of SQL Server ADO.NET clients and of SQLCLR procedures/functions/triggers. There are a couple of things that "interesting", if I'm not misunderstanding something obvious. ...(more)

Career

SQL SERVER - Data Warehousing Interview Questions and Answers - Part 3 - What are slowly changing dimensions (SCD)?
SCD is abbreviation of Slowly changing dimensions. SCD applies to cases where the attribute for a record varies over time....(more)

Service Broker / SOA

Dynamic Routing Service - So how does one deploy Service Broker services in a large enterprise? Hundreds of services that change location every now and then (just enough to create a major outage exactly at the wrong moment!), and each database requiring a route to any service it wishes to interact with. Creating hundreds and...(more)

SQL Server 2005

McLaren using SQL 2005 and Vista - I was on a BI course with the guys from McLaren. Fascinating how much data they need REAL TIME for the F1! We are talking milli, milli, seconds, is that right?...(more)

Security

Conversation with Kip Hawley, TSA Administrator (Part 1) - In April, Kip Hawley, the head of the Transportation Security Administration (TSA), invited me to Washington for a meeting. Despite some serious trepidation, I accepted. And it was a good meeting. Most of it was off the record, but he asked me how the TSA could overcome its negative image. I told hi...(more)

Conversation with Kip Hawley, TSA Administrator (Part 2) - This is Part 2 of a five-part series. ...(more)

California Voting Machine Audit Results - The state of California conducted a security review of their electronic voting machines earlier this year. This was a serious review, with real security researchers getting access to the source code. The report was issued last week, and the researchers were able to compromise all three machines -- b...(more)

T-SQL

More on GROUP BY; Examining SUM(Distinct) - I've written a two part article on using SQL GROUP BY clauses over at SQLTeam.com. It's always a common topic of discussion and confusion amongst beginner and intermediate SQL programmers alike, so I thought I'd write a fairly long and hopefully comprehensive piece that takes a common summary repor...(more)

SQL Server 2005

Smart Index Defragmentation for an ONLINE World - One of the many reasons I why I love consulting is the constant evolution and challenge. You may have something that works ?just fine? until a new customer simple states ?This is great. Can you make it do XYZ too?? I love those ?could you just? statements, because they challenge the status quo an...(more)

Software Development

Iteration = Maturity - I was recently reminded that iteration matures software....(more)

SQL Server Compact Edition

SQL Server Compact 3.5 B2 released - For those that wish to download and use SQL Server Compact 3.5 B2, or Sync Services for ADO.Net with Visual Studio 2005, you can simply download this ~2mb package and get started today. For the designer support, you can download the larger, but more functional Visual Studio 2008 B2...(more)

Integration Services

SSIS: A response from Microsoft to some growing criticism - Last week was an interesting week in the SSIS community instigated fairly and squarely by Oren Eini's blog entry entitled SSIS' 15 faults. The discussion really reached critical mass when Steve Jones highlighted it in his weekly newsletter Database Daily....(more)

Analysis Services / BI

Renaming Olap Databases Issues within Analysis Services - If you are view the XMLA script that is generated for an Analysis Services database, you will notice that there are both ID and NAME attributes. The reason Analysis Services has this differentiation is so one could rename an Olap database (which you could not do officially within Analysis Services ...(more)

Database Design, Theory and Development

Geek City: Total Paralysis - As I wrote that title, which was supposed to be a cute reference to parallelization, I realized it could also be the title for a blog post about my most recent trip, with delays, cancellations, missed connections and lost luggage both coming and going. But that will be another blog post that I will ...(more)

Did You Know? Estimated vs Actual Plans - In my previous post, I mentioned that it is important to understand the difference between estimated and actual query plans, so I decided to go into a few more details regarding the differences. Optimization takes place before execution, so in one sense, any query plan is an estimated plan. But when...(more)

Analysis Services / BI

Panorama: One Year On - After the Proclarity acquisition last year many people, myself included, thought the writing was on the wall for Panorama: after all the two companies' product lines were very similar and if Proclarity was going to be supplied by Microsoft then there wasn't going to be much point in looking at Panor...(more)

SQL Server 2005

SQL Server - TCP/IP Ports - Personally I think networks are a black art, I think there are probably less network admins that there are DBAs in the world. As for diagnostics and tools for network configuration, as a SQL Server person I feel I now know what its like configuring and running Oracle. My latest venture into networks...(more)

Analysis Services / BI

Red color for negative numbers in Analysis Services 2005 - Sometimes there is a simple way to solve an issue. For example, if you want to color all measures (including calculated measures) in red when the value is negative, you can simply write:...(more)

Database Design, Theory and Development

Programmatic Concurrency Control: Do Simultaneous Updates to Different Columns Constitute a Collision? - In college, as part of my philosophy degree, I took a course on metaphysics. I can still vividly remember the first class. The professor presented us with a simple enough scenario: Consider a wooden boat, whose construction has just finished. Now fast-forward several months. The boat has been well u...(more)

High Availability

Both - Michael may ask himself whether mirroring or log shipping is better... Database mirroring and log shipping. Which is better? (SharePoint Hosting and Development) But I wonder why. Why ask? Why not do both?...(more)

T-SQL

Be careful with Open Table - Yes, you were already aware of 40 or 50 reasons not to use Open Table. I have one more. Say you are creating a table with a composite key that includes a binary column. An example I came across recently was a table that captures additional data not covered by CDC (e.g. username). So, I created t...(more)

Below is a list of sources that we use in compiling this newsletter. If you have any ideas on other places to check, please feel free to let us know:

To be removed from this list, send a blank email. Many people have issues with sending plain text emails or their from address is slightly different than their reply to address. If you have any problems leaving the list, please contact the webmaster@databasedaily.com.

Note: This is not the SQLServerCentral.com daily newsletter list. If you want to be removed from that list, you can change your profile or follow the instructions on that list.